Re: Fw: Re: [SQL] pg_dump not correctly saving schema with partitioned tables?

chester c young <chestercyoung(at)yahoo(dot)com> writes:
>> here is a test case:

[ slightly less messed-up test case for anyone who wants to duplicate this: ]

create schema s1;
create schema s2;
create table s1.t1(
c1 bigserial primary key,
c2 text );
create table s2.t1(
c1 bigserial primary key
) inherits( s1.t1 );

In the original database, s1.t1.c1 has a default referring to sequence
s1.t1_c1_seq, while s2.t1.c1 has a different default referring to
sequence s2.t1_c1_seq. However, pg_dump examines the database roughly
like this:

1. Set search_path to s1.
2. Examine s1.t1's default expression; it looks like
nextval('t1_c1_seq'::regclass)
3. Set search_path to s2.
4. Examine s2.t1's default expression; it looks like
nextval('t1_c1_seq'::regclass)
5. Textually compare these defaults, find that they appear textually
identical, conclude that s2.t1's default is inherited from s1.t1.
Which it is not --- but after a dump and restore, it will be,
because pg_dump set it up that way.

The only near-term fix I can see for that is for pg_dump to stop
trafficking in inherited defaults at all. That is, always install
defaults with ALTER TABLE ONLY tab ALTER COLUMN col SET DEFAULT, and
do that over again explicitly for each child table. Since (AFAICT)
the system catalogs don't explicitly record inherited-ness of defaults,
this should produce the correct state even when the default really was
inherited, and it dodges this problem of search-path-sensitive
printouts, or indeed the whole idea of trying to compare text
representations of default expressions at all (which is surely a kluge
from the get-go). The code in pg_dump is jumping through hoops to
use inherited creation of defaults whenever possible, but I'm not sure
I see much point in that.

In the longer term it might be nicer if the system catalogs did record
inherited-ness of defaults (and then pg_dump could rely on that info
instead of guessing); but that would be a far more invasive change.

regards, tom lane