From cb1ca4d800621dcae67ca6c799006de99fa4f0a5 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 22 Mar 2015 13:53:11 -0400 Subject: Allow foreign tables to participate in inheritance. Foreign tables can now be inheritance children, or parents. Much of the system was already ready for this, but we had to fix a few things of course, mostly in the area of planner and executor handling of row locks. As side effects of this, allow foreign tables to have NOT VALID CHECK constraints (and hence to accept ALTER ... VALIDATE CONSTRAINT), and to accept ALTER SET STORAGE and ALTER SET WITH/WITHOUT OIDS. Continuing to disallow these things would've required bizarre and inconsistent special cases in inheritance behavior. Since foreign tables don't enforce CHECK constraints anyway, a NOT VALID one is a complete no-op, but that doesn't mean we shouldn't allow it. And it's possible that some FDWs might have use for SET STORAGE or SET WITH OIDS, though doubtless they will be no-ops for most. An additional change in support of this is that when a ModifyTable node has multiple target tables, they will all now be explicitly identified in EXPLAIN output, for example: Update on pt1 (cost=0.00..321.05 rows=3541 width=46) Update on pt1 Foreign Update on ft1 Foreign Update on ft2 Update on child3 -> Seq Scan on pt1 (cost=0.00..0.00 rows=1 width=46) -> Foreign Scan on ft1 (cost=100.00..148.03 rows=1170 width=46) -> Foreign Scan on ft2 (cost=100.00..148.03 rows=1170 width=46) -> Seq Scan on child3 (cost=0.00..25.00 rows=1200 width=46) This was done mainly to provide an unambiguous place to attach "Remote SQL" fields, but it is useful for inherited updates even when no foreign tables are involved. Shigeru Hanada and Etsuro Fujita, reviewed by Ashutosh Bapat and Kyotaro Horiguchi, some additional hacking by me --- doc/src/sgml/ddl.sgml | 24 +++++- doc/src/sgml/perform.sgml | 32 ++++++++ doc/src/sgml/ref/alter_foreign_table.sgml | 123 ++++++++++++++++++++++++++--- doc/src/sgml/ref/analyze.sgml | 6 ++ doc/src/sgml/ref/create_foreign_table.sgml | 51 +++++++++++- doc/src/sgml/ref/create_table.sgml | 5 +- doc/src/sgml/ref/truncate.sgml | 6 ++ 7 files changed, 230 insertions(+), 17 deletions(-) (limited to 'doc/src') 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 ( 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 partial index. @@ -2408,6 +2408,17 @@ WHERE c.altitude > 500 AND c.tableoid = p.oid; + + Another way to get the same effect is to use the regclass + pseudo-type, which will print the table OID symbolically: + + +SELECT c.tableoid::regclass, c.name, c.altitude +FROM cities c +WHERE c.altitude > 500; + + + Inheritance does not automatically propagate data from INSERT or COPY commands to @@ -2503,6 +2514,14 @@ VALUES ('Albany', NULL, NULL, 'NY'); further privileges to be granted. + + Foreign tables (see ) 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. + + Caveats @@ -2714,7 +2733,8 @@ VALUES ('Albany', NULL, NULL, 'NY'); We will refer to the child tables as partitions, though they - are in every way normal PostgreSQL tables. + are in every way normal PostgreSQL tables + (or, possibly, foreign tables). 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 @@ -761,6 +761,38 @@ ROLLBACK; decisions. + + When an UPDATE or DELETE command affects an + inheritance hierarchy, the output might look like this: + + +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) + + + 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 PostgreSQL 9.5; in prior versions the reader had to + intuit the target tables by inspecting the subplans.) + + The Planning time shown by EXPLAIN ANALYZE is the time it took to generate the query plan from the 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 -ALTER FOREIGN TABLE [ IF EXISTS ] name +ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ] action [, ... ] -ALTER FOREIGN TABLE [ IF EXISTS ] name +ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ] RENAME [ COLUMN ] column_name TO new_column_name ALTER FOREIGN TABLE [ IF EXISTS ] name RENAME TO new_name @@ -34,20 +34,26 @@ ALTER FOREIGN TABLE [ IF EXISTS ] namecolumn_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ] - ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type + ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] ALTER [ COLUMN ] column_name SET DEFAULT expression ALTER [ COLUMN ] column_name DROP DEFAULT ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL ALTER [ COLUMN ] column_name SET STATISTICS integer ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] ) + ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) - ADD table_constraint + ADD table_constraint [ NOT VALID ] + VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name ENABLE ALWAYS TRIGGER trigger_name + SET WITH OIDS + SET WITHOUT OIDS + INHERIT parent_table + NO INHERIT parent_table OWNER TO { new_owner | CURRENT_USER | SESSION_USER } OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) @@ -94,6 +100,9 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name 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 PostgreSQL believes the column to + have. @@ -145,7 +154,22 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name - ADD table_constraint + + SET STORAGE + + + + This form sets the storage mode for a column. + See the similar form of + for more details. + Note that the storage mode has no effect unless the table's + foreign-data wrapper chooses to pay attention to it. + + + + + + ADD table_constraint [ NOT VALID ] This form adds a new constraint to a foreign table, using the same @@ -156,10 +180,22 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name 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 .) - Note that constraints on foreign tables cannot be marked - 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 .) + If the constraint is marked NOT VALID, then it isn't + assumed to hold, but is only recorded for possible future use. + + + + + + VALIDATE CONSTRAINT + + + This form marks as valid a constraint that was previously marked + as NOT VALID. No action is taken to verify the + constraint, but future queries will assume that it holds. @@ -187,6 +223,60 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name + + SET WITH OIDS + + + This form adds an oid system column to the + table (see ). + 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. + + + + Note that this is not equivalent to ADD COLUMN oid oid; + that would add a normal column that happened to be named + oid, not a system column. + + + + + + SET WITHOUT OIDS + + + This form removes the oid system column from the + table. This is exactly equivalent to + DROP COLUMN oid RESTRICT, + except that it will not complain if there is already no + oid column. + + + + + + INHERIT parent_table + + + This form adds the target foreign table as a new child of the specified + parent table. + See the similar form of + for more details. + + + + + + NO INHERIT parent_table + + + This form removes the target foreign table from the list of children of + the specified parent table. + + + + OWNER @@ -272,7 +362,11 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name The name (possibly schema-qualified) of an existing foreign table to - alter. + alter. If ONLY is specified before the table name, only + that table is altered. If ONLY is not specified, the table + and all its descendant tables (if any) are altered. Optionally, + * can be specified after the table name to explicitly + indicate that descendant tables are included. @@ -383,6 +477,15 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name + + parent_table + + + A parent table to associate or de-associate with this foreign table. + + + + new_owner 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 @@ -199,6 +199,12 @@ ANALYZE [ VERBOSE ] [ table_name [ run ANALYZE manually. + + If any of the child tables are foreign tables whose foreign data wrappers + do not support ANALYZE, those child tables are ignored while + gathering inheritance statistics. + + If the table being analyzed is completely empty, ANALYZE will not record new statistics for that table. Any existing statistics 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 ] table_name | table_constraint } [, ... ] ] ) +[ INHERITS ( parent_table [, ... ] ) ] SERVER server_name [ OPTIONS ( option 'value' [, ... ] ) ] @@ -120,6 +121,44 @@ CHECK ( expression ) + + COLLATE collation + + + The 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. + + + + + + INHERITS ( parent_table [, ... ] ) + + + The optional 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 + for more details. + + + + + + CONSTRAINT constraint_name + + + 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 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. + + + + NOT NULL @@ -145,7 +184,7 @@ CHECK ( expression ) - CHECK ( expression ) + CHECK ( expression ) [ NO INHERIT ] The CHECK clause specifies an expression producing a @@ -163,6 +202,11 @@ CHECK ( expression ) current row. The system column tableoid may be referenced, but not any other system column. + + + A constraint marked with NO INHERIT will not propagate to + child tables. + @@ -280,8 +324,9 @@ SERVER film_server; SQL standard; however, much as with CREATE TABLE, NULL constraints and zero-column foreign tables are permitted. - The ability to specify a default value is also a PostgreSQL - extension. + The ability to specify column default values is also + a PostgreSQL extension. Table inheritance, in the form + defined by PostgreSQL, is nonstandard. 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 The optional 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. @@ -294,7 +294,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI error will be reported. - CHECK constraints are merged in essentially the same way as + + CHECK constraints are merged in essentially the same way as columns: if multiple parent tables and/or the new table definition contain identically-named 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 ] name [ This is similar to the usual behavior of currval() after a failed transaction. + + + 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. + -- cgit v1.2.3