From e358a61d769cf587403289ca4afc84f3d8d8eab5 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 25 Apr 2002 20:14:43 +0000 Subject: [PATCH] Updates for schema features. --- doc/src/sgml/datatype.sgml | 62 +++++++++- doc/src/sgml/queries.sgml | 5 +- doc/src/sgml/syntax.sgml | 290 +++++++++++++++++++++++++++++++++++---------- doc/src/sgml/typeconv.sgml | 75 ++++++++++-- 4 files changed, 352 insertions(+), 80 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 4f06cee503..45a13a5d30 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ @@ -2924,6 +2924,18 @@ SELECT SUBSTRING(b FROM 1 FOR 2) FROM test; regtype + + xid + + + + cid + + + + tid + + Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system @@ -3034,7 +3046,7 @@ SELECT SUBSTRING(b FROM 1 FOR 2) FROM test; - All of the alias types accept schema-qualified names, and will + All of the OID alias types accept schema-qualified names, and will display schema-qualified names on output if the object would not be found in the current search path without being qualified. The regproc and regoper alias types will only @@ -3045,6 +3057,52 @@ SELECT SUBSTRING(b FROM 1 FOR 2) FROM test; operand. + + OIDs are 32-bit quantities and are assigned from a single cluster-wide + counter. In a large or long-lived database, it is possible for the + counter to wrap around. Hence, it is bad practice to assume that OIDs + are unique, unless you take steps to ensure that they are unique. + Recommended practice when using OIDs for row identification is to create + a unique constraint on the OID column of each table for which the OID will + be used. Never assume that OIDs are unique across tables; use the + combination of tableoid and row OID if you need a + database-wide identifier. (Future releases of + PostgreSQL are likely to use a separate + OID counter for each table, so that tableoid + must be included to arrive at a globally unique identifier.) + + + + Another identifier type used by the system is xid, or transaction + (abbreviated xact) identifier. This is the datatype of the system columns + xmin and xmax. + Transaction identifiers are 32-bit quantities. In a long-lived + database it is possible for transaction IDs to wrap around. This + is not a fatal problem given appropriate maintenance procedures; + see the Administrator's Guide for details. However, it is + unwise to depend on uniqueness of transaction IDs over the long term + (more than one billion transactions). + + + + A third identifier type used by the system is cid, or command + identifier. This is the datatype of the system columns + cmin and cmax. + Command identifiers are also 32-bit quantities. This creates a hard + limit of 232 (4 billion) SQL commands within a single + transaction. + In practice this limit is not a problem --- note that the limit is on + number of SQL queries, not number of tuples processed. + + + + A final identifier type used by the system is tid, or tuple + identifier. This is the datatype of the system column + ctid. A tuple ID is a pair + (block number, tuple index within block) that identifies the + physical location of the tuple within its table. + + diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 5744bb7bdc..66bb25a3af 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1,4 +1,4 @@ - + Queries @@ -86,7 +86,8 @@ SELECT random(); FROM table_reference , table_reference , ... - A table reference may be a table name or a derived table such as a + A table reference may be a table name (possibly schema-qualified), + or a derived table such as a subquery, a table join, or complex combinations of these. If more than one table reference is listed in the FROM clause they are cross-joined (see below) to form the derived table that may then diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 828c3e8ce7..e1b1ac3596 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,5 +1,5 @@ @@ -623,14 +623,197 @@ CAST ( 'string' AS type ) + + Schemas and naming conventions - - Columns + + schemas + + + search path + + + + namespaces + + + + A PostgreSQL database cluster (installation) + contains one or more named databases. Users and groups of users are + shared across the entire cluster, but no other data is shared across + databases. Any given client connection to the server can access + only the data in a single database, the one specified in the connection + request. + + + - A column - is either a user-defined column of a given table or one of the - following system-defined columns: + Users of a cluster do not necessarily have the privilege to access every + database in the cluster. Sharing of user names means that there + cannot be different users named, say, joe in two databases + in the same cluster; but the system can be configured to allow + joe access to only some of the databases. + + + + + A database contains one or more named schemas, which + in turn contain tables. Schemas also contain other kinds of named + objects, including datatypes, functions, and operators. The same + object name can be used in different schemas without conflict; for + example, both schema1 and myschema may + contain tables named mytable. Unlike databases, schemas + are not rigidly separated: a user may access objects in any of the + schemas in the database he is connected to, if he has privileges + to do so. + + + + qualified names + + + + names + qualified + + + + To name a table precisely, write a qualified name consisting + of the schema name and table name separated by a dot: + + schema.table + + Actually, the even more general syntax + + database.schema.table + + can be used too, but at present this is just for pro-forma compliance + with the SQL standard; if you write a database name it must be the + same as the database you are connected to. + + + + unqualified names + + + + names + unqualified + + + + Qualified names are tedious to write, and it's often best not to + wire a particular schema name into applications anyway. Therefore + tables are often referred to by unqualified names, + which consist of just the table name. The system determines which table + is meant by following a search path, which is a list + of schemas to look in. The first matching table in the search path + is taken to be the one wanted. If there is no match in the search + path, an error is reported, even if matching table names exist + in other schemas in the database. + + + + The first schema named in the search path is called the current schema. + Aside from being the first schema searched, it is also the schema in + which new tables will be created if the CREATE TABLE + command does not specify a schema name. + + + + The search path works in the same way for datatype names, function names, + and operator names as it does for table names. Datatype and function + names can be qualified in exactly the same way as table names. If you + need to write a qualified operator name in an expression, there is a + special provision: you must write + + OPERATOR(schema.operator) + + This is needed to avoid syntactic ambiguity. An example is + + SELECT 3 OPERATOR(pg_catalog.+) 4; + + In practice one usually relies on the search path for operators, + so as not to have to write anything so ugly as that. + + + + The standard search path in PostgreSQL + contains first the schema having the same name as the session user + (if it exists), and second the schema named public + (if it exists, which it does by default). This arrangement allows + a flexible combination of private and shared tables. If no per-user + schemas are created then all user tables will exist in the shared + public schema, providing behavior that is backwards-compatible + with pre-7.3 PostgreSQL releases. + + + + + There is no concept of a public schema in the SQL standard. + To achieve closest conformance to the standard, the DBA should + create per-user schemas for every user, and not use (perhaps even + remove) the public schema. + + + + + In addition to public and user-created schemas, each database + contains a + pg_catalog schema, which contains the system tables + and all the built-in datatypes, functions, and operators. + pg_catalog is always effectively part of the search path. + If it is not named explicitly in the path then it is implicitly searched + before searching the path's schemas. This ensures that + built-in names will always be findable. However, you may explicitly + place pg_catalog at the end of your search path if you + prefer to have user-defined names override built-in names. + + + + Reserved names + + + reserved names + + + + names + reserved + + + + There are several restrictions on the names that can be chosen for + user-defined database objects. These restrictions vary depending + on the kind of object. (Note that these restrictions are + separate from whether the name is a key word or not; quoting a + name will not allow you to escape these restrictions.) + + + + Schema names beginning with pg_ are reserved for system + purposes and may not be created by users. + + + + In PostgreSQL versions before 7.3, table + names beginning with pg_ were reserved. This is no longer + true: you may create such a table name if you wish, in any non-system + schema. However, it's best to continue to avoid such names, + to ensure that you won't suffer a conflict if some future version + defines a system catalog named the same as your table. (With the + default search path, an unqualified reference to your table name + would be resolved as the system catalog instead.) System catalogs will + continue to follow the convention of having names beginning with + pg_, so that they will not conflict with unqualified + user-table names so long as users avoid the pg_ prefix. + + + + Every table has several system columns that are + implicitly defined by the system. Therefore, these names cannot + be used as names of user-defined columns: columns @@ -648,7 +831,7 @@ CAST ( 'string' AS type ) The object identifier (object ID) of a row. This is a serial number that is automatically added by PostgreSQL to all table rows (unless the table was created WITHOUT OIDS, in which case this column is - not present). + not present). See for more info. @@ -715,13 +898,13 @@ CAST ( 'string' AS type ) ctid - The tuple ID of the tuple within its table. This is a pair - (block number, tuple index within block) that identifies the - physical location of the tuple. Note that although the ctid - can be used to locate the tuple very quickly, a row's ctid - will change each time it is updated or moved by VACUUM - FULL. - Therefore ctid is useless as a long-term row identifier. + The physical location of the tuple within its table. + Note that although the ctid + can be used to locate the tuple very quickly, a row's + ctid will change each time it is updated + or moved by VACUUM FULL. + Therefore ctid is useless as a long-term + row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows. @@ -729,38 +912,8 @@ CAST ( 'string' AS type ) - - - OIDs are 32-bit quantities and are assigned from a single cluster-wide - counter. In a large or long-lived database, it is possible for the - counter to wrap around. Hence, it is bad practice to assume that OIDs - are unique, unless you take steps to ensure that they are unique. - Recommended practice when using OIDs for row identification is to create - a unique constraint on the OID column of each table for which the OID will be - used. Never assume that OIDs are unique across tables; use the - combination of tableoid and row OID if you need a database-wide - identifier. (Future releases of PostgreSQL are likely to use a separate - OID counter for each table, so that tableoid must be - included to arrive at a globally unique identifier.) - - - - Transaction identifiers are 32-bit quantities. In a long-lived - database it is possible for transaction IDs to wrap around. This - is not a fatal problem given appropriate maintenance procedures; - see the Administrator's Guide for details. However, it is - unwise to depend on uniqueness of transaction IDs over the long term - (more than one billion transactions). - - - - Command identifiers are also 32-bit quantities. This creates a hard - limit of 232 (4 billion) SQL commands within a single transaction. - In practice this limit is not a problem --- note that the limit is on - number of SQL queries, not number of tuples processed. - - - + + Value Expressions @@ -864,8 +1017,9 @@ CAST ( 'string' AS type ) correlation.columnname `['subscript`]' - correlation is either the name of a - table, an alias for a table defined by means of a FROM clause, or + correlation is the name of a + table (possibly qualified), or an alias for a table defined by means of a + FROM clause, or the key words NEW or OLD. (NEW and OLD can only appear in the action portion of a rule, while other correlation names can be used in any SQL statement.) @@ -918,9 +1072,13 @@ CREATE FUNCTION dept (text) RETURNS dept expression operator (unary postfix operator) where the operator token follows the syntax - rules of or is one of the - tokens AND, OR, and - NOT. Which particular operators exist and whether + rules of , or is one of the + keywords AND, OR, and + NOT, or is a qualified operator name + + OPERATOR(schema.operatorname) + + Which particular operators exist and whether they are unary or binary depends on what operators have been defined by the system or the user. describes the built-in operators. @@ -932,8 +1090,7 @@ CREATE FUNCTION dept (text) RETURNS dept The syntax for a function call is the name of a function - (which is subject to the syntax rules for identifiers of ), followed by its argument list + (possibly qualified with a schema name), followed by its argument list enclosed in parentheses: @@ -976,7 +1133,8 @@ sqrt(2) where aggregate_name is a previously - defined aggregate, and expression is + defined aggregate (possibly a qualified name), and + expression is any value expression that does not itself contain an aggregate expression. @@ -1044,10 +1202,14 @@ CAST ( expression AS type - An explicit type cast may be omitted if there is no ambiguity as to the - type that a value expression must produce (for example, when it is + An explicit type cast may usually be omitted if there is no ambiguity as + to the type that a value expression must produce (for example, when it is assigned to a table column); the system will automatically apply a - type cast in such cases. + type cast in such cases. However, automatic casting is only done for + cast functions that are marked okay to apply implicitly + in the system catalogs. Other cast functions must be invoked with + explicit casting syntax. This restriction is intended to prevent + surprising conversions from being applied silently. @@ -1061,7 +1223,7 @@ CAST ( expression AS type can't be used this way, but the equivalent float8 can. Also, the names interval, time, and timestamp can only be used in this fashion if they are - double-quoted, because of parser conflicts. Therefore, the use of + double-quoted, because of syntactic conflicts. Therefore, the use of the function-like cast syntax leads to inconsistencies and should probably be avoided in new applications. @@ -1143,21 +1305,21 @@ SELECT (5 !) - 6; - :: + . left - PostgreSQL-style typecast + table/column name separator - [ ] + :: left - array element selection + PostgreSQL-style typecast - . + [ ] left - table/column name separator + array element selection diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml index b53dbe4e75..58427d205d 100644 --- a/doc/src/sgml/typeconv.sgml +++ b/doc/src/sgml/typeconv.sgml @@ -228,7 +228,32 @@ should use this new function and will no longer do the implicit conversion using -Check for an exact match in the pg_operator system catalog. +Select the operators to be considered from the +pg_operator system catalog. If an unqualified +operator name is used (the usual case), the operators +considered are those of the right name and argument count that are +visible in the current search path (see ). +If a qualified operator name was given, only operators in the specified +schema are considered. + + + + + +If the search path finds multiple operators of identical argument types, +only the one appearing earliest in the path is considered. But operators of +different argument types are considered on an equal footing regardless of +search path position. + + + + + + + +Check for an operator accepting exactly the input argument types. +If one exists (there can be only one exact match in the set of +operators considered), use it. @@ -250,10 +275,11 @@ Look for the best match. -Make a list of all operators of the same name for which the input types -match or can be coerced to match. (unknown literals are -assumed to be coercible to anything for this purpose.) If there is only -one, use it; else continue to the next step. +Discard candidate operators for which the input types do not match +and cannot be coerced (using an implicit coercion function) to match. +unknown literals are +assumed to be coercible to anything for this purpose. If only one +candidate remains, use it; else continue to the next step. @@ -467,13 +493,38 @@ tgl=> select cast(text '20' as int8) ! as "factorial"; -Check for an exact match in the pg_proc system catalog. +Select the functions to be considered from the +pg_proc system catalog. If an unqualified +function name is used, the functions +considered are those of the right name and argument count that are +visible in the current search path (see ). +If a qualified function name was given, only functions in the specified +schema are considered. + + + + + +If the search path finds multiple functions of identical argument types, +only the one appearing earliest in the path is considered. But functions of +different argument types are considered on an equal footing regardless of +search path position. + + + + + + + +Check for a function accepting exactly the input argument types. +If one exists (there can be only one exact match in the set of +functions considered), use it. (Cases involving unknown will never find a match at this step.) -If no exact match appears in the catalog, see whether the function call appears +If no exact match is found, see whether the function call appears to be a trivial type coercion request. This happens if the function call has just one argument and the function name is the same as the (internal) name of some data type. Furthermore, the function argument must be either @@ -489,11 +540,11 @@ Look for the best match. -Make a list of all functions of the same name with the same number of -arguments for which the input types -match or can be coerced to match. (unknown literals are -assumed to be coercible to anything for this purpose.) If there is only -one, use it; else continue to the next step. +Discard candidate functions for which the input types do not match +and cannot be coerced (using an implicit coercion function) to match. +unknown literals are +assumed to be coercible to anything for this purpose. If only one +candidate remains, use it; else continue to the next step. -- 2.11.0