Обсуждение: schema-only pg_dump inconsistently dumps some view definitions
Ran into a weird issue with pg_dump outputting an odd definition for a view. First, the view definition https://gist.github.com/keithf4/24298f7327b4a27054fd If a pg_dump -s or pg_dumpall -s for the entire database is used, it outputs the view definition as a table with an ALTER TABLE statement related to logical replication followed by the "_RETURN" rule later on in the dump file. I've come to find out that this is how views are done internally, but seeing it in the pg_dump was odd. https://gist.github.com/keithf4/549779a468a76e5f81d6 However, if just the table (using -t) or just the schema containing the table (using -n) is used with pg_dump, the normal view definition is output https://gist.github.com/keithf4/24589c0a3456fc847523 Reason we ran into this is because the client has a process that splits the full plaintext schema dump up into separate steps for some internal processes, and the "table" format of the dump breaks it. This is the only view in the entire database that has this issue. Below are some catalog entries but from what I can tell, things seem normal. And restoring the whole dump file using psql works fine and recreates it as a normal view. We've also tried recreating the view from scratch and it still does this. https://gist.github.com/keithf4/2026971df875584d5c0e So, while this does not break a normal dump/restore of a database, it does appear to be inconsistent with how a view definition is normally dumped out. -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. http://www.keithf4.com
On Mon, Nov 30, 2015 at 4:31 PM, Keith Fiske <keith@omniti.com> wrote: > Ran into a weird issue with pg_dump outputting an odd definition for a > view. First, the view definition > > https://gist.github.com/keithf4/24298f7327b4a27054fd > > If a pg_dump -s or pg_dumpall -s for the entire database is used, it > outputs the view definition as a table with an ALTER TABLE statement > related to logical replication followed by the "_RETURN" rule later on in > the dump file. I've come to find out that this is how views are done > internally, but seeing it in the pg_dump was odd. > > https://gist.github.com/keithf4/549779a468a76e5f81d6 > > However, if just the table (using -t) or just the schema containing the > table (using -n) is used with pg_dump, the normal view definition is output > > https://gist.github.com/keithf4/24589c0a3456fc847523 > > Reason we ran into this is because the client has a process that splits > the full plaintext schema dump up into separate steps for some internal > processes, and the "table" format of the dump breaks it. This is the only > view in the entire database that has this issue. Below are some catalog > entries but from what I can tell, things seem normal. And restoring the > whole dump file using psql works fine and recreates it as a normal view. > We've also tried recreating the view from scratch and it still does this. > > https://gist.github.com/keithf4/2026971df875584d5c0e > > So, while this does not break a normal dump/restore of a database, it does > appear to be inconsistent with how a view definition is normally dumped out. > > -- > Keith Fiske > Database Administrator > OmniTI Computer Consulting, Inc. > http://www.keithf4.com > Sorry, forgot to mention this is PostgreSQL 9.4.5 -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. http://www.keithf4.com
Keith Fiske <keith@omniti.com> writes: > Ran into a weird issue with pg_dump outputting an odd definition for a > view. First, the view definition > https://gist.github.com/keithf4/24298f7327b4a27054fd > If a pg_dump -s or pg_dumpall -s for the entire database is used, it > outputs the view definition as a table with an ALTER TABLE statement > related to logical replication followed by the "_RETURN" rule later on in > the dump file. pg_dump does that when it's trying to break a circular dependency; that is, there is something referenced in the view definition that cannot be defined before the view exists. The typical thing I've seen is a view defined in terms of a function that takes or returns the view's named rowtype. It's not real clear why it's doing it here, though if you can submit a self-contained test case we could take a look. I suspect though that the answer will be "not a bug". There's been some speculation about revising pg_dump so that what it puts out in such cases involves a dummy view definition followed later by CREATE OR REPLACE VIEW. No one's gotten around to writing a patch for that though, and I rather imagine that it would break unsuspecting tools anyway :-( regards, tom lane