Allow foreign tables to participate in inheritance

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 22 March 2015 12:53 UTC

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

cb1ca4d Allow foreign tables to participate in inheritance.
contrib/file_fdw/input/file_fdw.source | 16 +-
contrib/file_fdw/output/file_fdw.source | 42 +-
contrib/postgres_fdw/expected/postgres_fdw.out | 380 ++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 110 ++++++
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 +
src/backend/catalog/heap.c | 7 -
src/backend/commands/analyze.c | 85 +++-
src/backend/commands/explain.c | 114 +++++-
src/backend/commands/tablecmds.c | 36 +-
src/backend/executor/execCurrent.c | 2 +-
src/backend/executor/execMain.c | 79 ++--
src/backend/executor/nodeLockRows.c | 8 +-
src/backend/optimizer/plan/planner.c | 80 ++--
src/backend/optimizer/prep/prepunion.c | 10 +-
src/backend/parser/gram.y | 30 +-
src/bin/pg_dump/pg_dump.c | 16 +-
src/include/nodes/execnodes.h | 11 +-
src/include/optimizer/planner.h | 3 +
src/test/regress/expected/foreign_data.out | 505 +++++++++++++++++++++++-
src/test/regress/expected/rowsecurity.out | 10 +-
src/test/regress/expected/updatable_views.out | 12 +-
src/test/regress/expected/with.out | 6 +-
src/test/regress/sql/foreign_data.sql | 137 ++++++-
29 files changed, 1761 insertions(+), 185 deletions(-)

Upstream: git.postgresql.org


  • Share