pgsql: Fix pg_dump's handling of circular dependencies in views.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

pgsql: Fix pg_dump's handling of circular dependencies in views.

Tom Lane-2
Fix pg_dump's handling of circular dependencies in views.

This is a back-patch of the v10 commit d8c05aff5.  The motivation for
doing this now is that we received a complaint that a view with a
circular dependency is dumped with an extra bogus command "ALTER TABLE
ONLY myview REPLICA IDENTITY NOTHING", because pg_dump forgets that it's
a view not a table, and the relreplident value stored for a view is that.
So you'll get an error message during restore even if not using --clean;
this would break pg_upgrade for example.  While that could be handled
with a one-line patch, it seems better to back-patch d8c05aff5, since that
produces cleaner more future-proof output and fixes an additional bug.

Per gripe from Alex Williams.  Back-patch to 9.4-9.6 (even if 9.3 were
still in support, it hasn't got REPLICA IDENTITY so no bug).

Discussion: https://postgr.es/m/NFqxoEi7-8Rw9OW0f-GwHcjvS2I4YQXov4g9OoWv3i7lVOZdLWkAWl9jQQqwEaUq6WV0vdobromhW82e8y5I0_59yZTXcZnXsrmFuldlmZc=@protonmail.com

Original commit message follows:

pg_dump's traditional solution for breaking a circular dependency involving
a view was to create the view with CREATE TABLE and then later issue CREATE
RULE "_RETURN" ... to convert the table to a view, relying on the backend's
very very ancient code that supports making views that way.  We've wanted
to get rid of that kluge for a long time, but the thing that finally
motivates doing something about it is the recognition that this method
fails with the --clean option, because it leads to issuing DROP RULE
"_RETURN" followed by DROP TABLE --- and the backend won't let you drop a
view's _RETURN rule.

Instead, let's break circular dependencies by initially creating the view
using CREATE VIEW AS SELECT NULL::columntype AS columnname, ... (so that
it has the right column names and types to support external references,
but no dependencies beyond the column data types), and then later dumping
the ON SELECT rule using the spelling CREATE OR REPLACE VIEW.  This method
wasn't available when this code was originally written, but it's been
possible since PG 7.3, so it seems fine to start relying on it now.

To solve the --clean problem, make the dropStmt for an ON SELECT rule
be CREATE OR REPLACE VIEW with the same dummy target list as above.
In this way, during the DROP phase, we first reduce the view to have
no extra dependencies, and then we can drop it entirely when we've
gotten rid of whatever had a circular dependency on it.

(Note: this should work adequately well with the --if-exists option, since
the CREATE OR REPLACE VIEW will go through whether the view exists or not.
It could fail if the view exists with a conflicting column set, but we
don't really support --clean against a non-matching database anyway.)

This allows cleaning up some other kluges inside pg_dump, notably that
we don't need a notion of reloptions attached to a rule anymore.

Although this is a bug fix, commit to HEAD only for now.  The problem's
existed for a long time and we've had relatively few complaints, so it
doesn't really seem worth taking risks to fix it in the back branches.
We might revisit that choice if no problems emerge.

Discussion: <[hidden email]>

Branch
------
REL9_5_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/7fc50a8a76c048ad715840ee39cbf4ff26d2d357

Modified Files
--------------
src/bin/pg_dump/pg_dump.c      | 181 ++++++++++++++++++++++++++++++-----------
src/bin/pg_dump/pg_dump.h      |   3 +-
src/bin/pg_dump/pg_dump_sort.c |  15 +---
3 files changed, 137 insertions(+), 62 deletions(-)