diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 24 | ||||
| -rw-r--r-- | doc/src/sgml/perform.sgml | 32 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_foreign_table.sgml | 123 | ||||
| -rw-r--r-- | doc/src/sgml/ref/analyze.sgml | 6 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_foreign_table.sgml | 51 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 5 | ||||
| -rw-r--r-- | doc/src/sgml/ref/truncate.sgml | 6 |
7 files changed, 230 insertions, 17 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 570a003e4a9..1c56f162de3 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -546,7 +546,7 @@ CREATE TABLE products ( <para> Adding a unique constraint will automatically create a unique btree - index on the column or group of columns used in the constraint. + index on the column or group of columns used in the constraint. A uniqueness constraint on only some rows can be enforced by creating a <link linkend="indexes-partial">partial index</link>. </para> @@ -2409,6 +2409,17 @@ WHERE c.altitude > 500 AND c.tableoid = p.oid; </para> <para> + Another way to get the same effect is to use the <type>regclass</> + pseudo-type, which will print the table OID symbolically: + +<programlisting> +SELECT c.tableoid::regclass, c.name, c.altitude +FROM cities c +WHERE c.altitude > 500; +</programlisting> + </para> + + <para> Inheritance does not automatically propagate data from <command>INSERT</command> or <command>COPY</command> commands to other tables in the inheritance hierarchy. In our example, the @@ -2503,6 +2514,14 @@ VALUES ('Albany', NULL, NULL, 'NY'); further privileges to be granted. </para> + <para> + Foreign tables (see <xref linkend="ddl-foreign-data">) can also + be part of inheritance hierarchies, either as parent or child + tables, just as regular tables can be. If a foreign table is part + of an inheritance hierarchy then any operations not supported by + the foreign table are not supported on the whole hierarchy either. + </para> + <sect2 id="ddl-inherit-caveats"> <title>Caveats</title> @@ -2714,7 +2733,8 @@ VALUES ('Albany', NULL, NULL, 'NY'); <para> We will refer to the child tables as partitions, though they - are in every way normal <productname>PostgreSQL</> tables. + are in every way normal <productname>PostgreSQL</> tables + (or, possibly, foreign tables). </para> </listitem> diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index c73580ed460..7bcbfa76112 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -762,6 +762,38 @@ ROLLBACK; </para> <para> + When an <command>UPDATE</> or <command>DELETE</> command affects an + inheritance hierarchy, the output might look like this: + +<screen> +EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101; + QUERY PLAN +----------------------------------------------------------------------------------- + Update on parent (cost=0.00..24.53 rows=4 width=14) + Update on parent + Update on child1 + Update on child2 + Update on child3 + -> Seq Scan on parent (cost=0.00..0.00 rows=1 width=14) + Filter: (f1 = 101) + -> Index Scan using child1_f1_key on child1 (cost=0.15..8.17 rows=1 width=14) + Index Cond: (f1 = 101) + -> Index Scan using child2_f1_key on child2 (cost=0.15..8.17 rows=1 width=14) + Index Cond: (f1 = 101) + -> Index Scan using child3_f1_key on child3 (cost=0.15..8.17 rows=1 width=14) + Index Cond: (f1 = 101) +</screen> + + In this example the Update node needs to consider three child tables as + well as the originally-mentioned parent table. So there are four input + scanning subplans, one per table. For clarity, the Update node is + annotated to show the specific target tables that will be updated, in the + same order as the corresponding subplans. (These annotations are new as + of <productname>PostgreSQL</> 9.5; in prior versions the reader had to + intuit the target tables by inspecting the subplans.) + </para> + + <para> The <literal>Planning time</literal> shown by <command>EXPLAIN ANALYZE</command> is the time it took to generate the query plan from the parsed query and optimize it. It does not include parsing or rewriting. diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml index 93f8743071b..ace0040a9b7 100644 --- a/doc/src/sgml/ref/alter_foreign_table.sgml +++ b/doc/src/sgml/ref/alter_foreign_table.sgml @@ -21,9 +21,9 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> +ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] <replaceable class="PARAMETER">action</replaceable> [, ... ] -ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> +ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] RENAME [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> TO <replaceable class="PARAMETER">new_column_name</replaceable> ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable> @@ -34,20 +34,26 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab ADD [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ] DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="PARAMETER">column_name</replaceable> [ RESTRICT | CASCADE ] - ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">data_type</replaceable> + ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> DROP DEFAULT ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> { SET | DROP } NOT NULL ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] ) + ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ]) - ADD <replaceable class="PARAMETER">table_constraint</replaceable> + ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ] + VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ] DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ] ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ] ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable> ENABLE ALWAYS TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable> + SET WITH OIDS + SET WITHOUT OIDS + INHERIT <replaceable class="PARAMETER">parent_table</replaceable> + NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable> OWNER TO { <replaceable class="PARAMETER">new_owner</replaceable> | CURRENT_USER | SESSION_USER } OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ]) </synopsis> @@ -94,6 +100,9 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab <listitem> <para> This form changes the type of a column of a foreign table. + Again, this has no effect on any underlying storage: this action simply + changes the type that <productname>PostgreSQL</> believes the column to + have. </para> </listitem> </varlistentry> @@ -145,7 +154,22 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab </varlistentry> <varlistentry> - <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term> + <term> + <literal>SET STORAGE</literal> + </term> + <listitem> + <para> + This form sets the storage mode for a column. + See the similar form of <xref linkend="sql-altertable"> + for more details. + Note that the storage mode has no effect unless the table's + foreign-data wrapper chooses to pay attention to it. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal> [ NOT VALID ]</term> <listitem> <para> This form adds a new constraint to a foreign table, using the same @@ -156,10 +180,22 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab <para> Unlike the case when adding a constraint to a regular table, nothing is done to verify the constraint is correct; rather, this action simply - declares that some new condition holds for all rows in the foreign - table. (See the discussion in <xref linkend="SQL-CREATEFOREIGNTABLE">.) - Note that constraints on foreign tables cannot be marked - <literal>NOT VALID</> since such constraints are simply declarative. + declares that some new condition should be assumed to hold for all rows + in the foreign table. (See the discussion + in <xref linkend="SQL-CREATEFOREIGNTABLE">.) + If the constraint is marked <literal>NOT VALID</>, then it isn't + assumed to hold, but is only recorded for possible future use. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>VALIDATE CONSTRAINT</literal></term> + <listitem> + <para> + This form marks as valid a constraint that was previously marked + as <literal>NOT VALID</literal>. No action is taken to verify the + constraint, but future queries will assume that it holds. </para> </listitem> </varlistentry> @@ -188,6 +224,60 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab </varlistentry> <varlistentry> + <term><literal>SET WITH OIDS</literal></term> + <listitem> + <para> + This form adds an <literal>oid</literal> system column to the + table (see <xref linkend="ddl-system-columns">). + It does nothing if the table already has OIDs. + Unless the table's foreign-data wrapper supports OIDs, this column + will simply read as zeroes. + </para> + + <para> + Note that this is not equivalent to <literal>ADD COLUMN oid oid</>; + that would add a normal column that happened to be named + <literal>oid</>, not a system column. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>SET WITHOUT OIDS</literal></term> + <listitem> + <para> + This form removes the <literal>oid</literal> system column from the + table. This is exactly equivalent to + <literal>DROP COLUMN oid RESTRICT</literal>, + except that it will not complain if there is already no + <literal>oid</literal> column. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term> + <listitem> + <para> + This form adds the target foreign table as a new child of the specified + parent table. + See the similar form of <xref linkend="sql-altertable"> + for more details. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term> + <listitem> + <para> + This form removes the target foreign table from the list of children of + the specified parent table. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>OWNER</literal></term> <listitem> <para> @@ -272,7 +362,11 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab <listitem> <para> The name (possibly schema-qualified) of an existing foreign table to - alter. + alter. If <literal>ONLY</> is specified before the table name, only + that table is altered. If <literal>ONLY</> is not specified, the table + and all its descendant tables (if any) are altered. Optionally, + <literal>*</> can be specified after the table name to explicitly + indicate that descendant tables are included. </para> </listitem> </varlistentry> @@ -384,6 +478,15 @@ ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab </varlistentry> <varlistentry> + <term><replaceable class="PARAMETER">parent_table</replaceable></term> + <listitem> + <para> + A parent table to associate or de-associate with this foreign table. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><replaceable class="PARAMETER">new_owner</replaceable></term> <listitem> <para> diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index 08d316a9e43..27ab4fca42c 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -200,6 +200,12 @@ ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table_name</replaceable> [ </para> <para> + If any of the child tables are foreign tables whose foreign data wrappers + do not support <command>ANALYZE</>, those child tables are ignored while + gathering inheritance statistics. + </para> + + <para> If the table being analyzed is completely empty, <command>ANALYZE</command> will not record new statistics for that table. Any existing statistics will be retained. diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml index a24aa6e6d1d..abadd83fc38 100644 --- a/doc/src/sgml/ref/create_foreign_table.sgml +++ b/doc/src/sgml/ref/create_foreign_table.sgml @@ -23,6 +23,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name | <replaceable>table_constraint</replaceable> } [, ... ] ] ) +[ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ] SERVER <replaceable class="parameter">server_name</replaceable> [ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ] @@ -121,6 +122,44 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) </varlistentry> <varlistentry> + <term><literal>COLLATE <replaceable>collation</replaceable></literal></term> + <listitem> + <para> + The <literal>COLLATE</> clause assigns a collation to + the column (which must be of a collatable data type). + If not specified, the column data type's default collation is used. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term> + <listitem> + <para> + The optional <literal>INHERITS</> clause specifies a list of + tables from which the new foreign table automatically inherits + all columns. Parent tables can be plain tables or foreign tables. + See the similar form of + <xref linkend="sql-createtable"> for more details. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term> + <listitem> + <para> + An optional name for a column or table constraint. If the + constraint is violated, the constraint name is present in error messages, + so constraint names like <literal>col must be positive</> can be used + to communicate helpful constraint information to client applications. + (Double-quotes are needed to specify constraint names that contain spaces.) + If a constraint name is not specified, the system generates a name. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>NOT NULL</></term> <listitem> <para> @@ -145,7 +184,7 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) </varlistentry> <varlistentry> - <term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> )</literal></term> + <term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] </literal></term> <listitem> <para> The <literal>CHECK</> clause specifies an expression producing a @@ -163,6 +202,11 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) current row. The system column <literal>tableoid</literal> may be referenced, but not any other system column. </para> + + <para> + A constraint marked with <literal>NO INHERIT</> will not propagate to + child tables. + </para> </listitem> </varlistentry> @@ -280,8 +324,9 @@ SERVER film_server; <acronym>SQL</acronym> standard; however, much as with <link linkend="sql-createtable"><command>CREATE TABLE</></link>, <literal>NULL</> constraints and zero-column foreign tables are permitted. - The ability to specify a default value is also a <productname>PostgreSQL</> - extension. + The ability to specify column default values is also + a <productname>PostgreSQL</> extension. Table inheritance, in the form + defined by <productname>PostgreSQL</productname>, is nonstandard. </para> </refsect1> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index bc6db457a2c..3e6246da668 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -267,7 +267,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI <para> The optional <literal>INHERITS</> clause specifies a list of tables from which the new table automatically inherits all - columns. + columns. Parent tables can be plain tables or foreign tables. </para> <para> @@ -294,7 +294,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI error will be reported. </para> - <para><literal>CHECK</> constraints are merged in essentially the same way as + <para> + <literal>CHECK</> constraints are merged in essentially the same way as columns: if multiple parent tables and/or the new table definition contain identically-named <literal>CHECK</> constraints, these constraints must all have the same check expression, or an error will be diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml index 87516c9cc36..10c4fdd359a 100644 --- a/doc/src/sgml/ref/truncate.sgml +++ b/doc/src/sgml/ref/truncate.sgml @@ -179,6 +179,12 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ This is similar to the usual behavior of <function>currval()</> after a failed transaction. </para> + + <para> + <command>TRUNCATE</> is not currently supported for foreign tables. + This implies that if a specified table has any descendant tables that are + foreign, the command will fail. + </para> </refsect1> <refsect1> |