Thread: Fw: Re: pg_dump not correctly saving schema with partitioned tables?
> From: chester c young <chestercyoung@yahoo.com>
> Subject: Re: [SQL] pg_dump not correctly saving schema with partitioned tables?
> To: "Tom Lane" <tgl@sss.pgh.pa.us>
> Date: Tuesday, January 31, 2012, 5:40 PM
> --- On Tue, 1/31/12, Tom Lane <tgl@sss.pgh.pa.us>
> wrote:
>
> > From: Tom Lane <tgl@sss.pgh.pa.us>
> > Subject: Re: [SQL] pg_dump not correctly saving schema
> with partitioned tables?
> > To: "chester c young" <chestercyoung@yahoo.com>
> > Cc: pgsql-sql@postgresql.org
> > Date: Tuesday, January 31, 2012, 2:04 PM
> > chester c young <chestercyoung@yahoo.com>
> > writes:
> > > have database with many partitions. each
> > partition table has its own primary key sequence.
> > > Column ||
> >
> > Modifiers
> >
> >
> > >
> >
> -----------++--------------------------------------------------------------
> > > uno_id || not null default
> > nextval('cmp0004.cmt_uno_id_seq'::regclass)
> >
> > > when dumped and then resorted, the pk sequence is
> > changed to an inherited- from table:
> >
> >
> > > Column ||
> >
> > Modifiers
> >
> > >
> >
> --------------++------------------------------------------------------
> > > uno_id || not null
> > default nextval('uno_uno_id_seq'::regclass)
> >
> > > (another error is that the pk sequence does not
> spec
> > the schema!)
> >
> > I see no reason to think there is a bug here; it's
> more
> > likely that you
> > do not understand the display behavior of regclass
> > constants. They only
> > print a schema name if your current search_path is such
> that
> > the
> > relation wouldn't be found by writing just the
> unqualified
> > name.
> > So the most likely explanation for the discrepancy
> above is
> > that you
> > executed the two \d commands under different
> search_path
> > settings.
> >
> > It's possible that you have actually found a pg_dump
> bug,
> > but if so
> > you'll need to submit a complete test-case exhibiting
> the
> > bug.
> >
> here is a test case:
>
>
> create database test2;
> \c test2;
>
> 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 );
>
> test2=# \d s2.t2
>
> Table "s2.t2"
> Column | Type |
>
> Modifiers
>
> --------+--------+----------------------------------------------------
> c1 | bigint | not null default
> nextval('s2.t2_c1_seq'::regclass)
> c2 | text |
>
> -- pg_dump test2 > test2.dmp
>
> create database test3
> \c test3
> \i test2.dmp
>
> test3=# \d s2.t1
>
> Table "s2.t1"
> Column | Type |
>
> Modifiers
>
> --------+--------+----------------------------------------------------
> c1 | bigint | not null default
> nextval('s1.t1_c1_seq'::regclass)
> c2 | text |
>
> ## in the original db table s2.t1 pk is using s2.t1_c1_seq
> ## in the dumped db table s2.t1 pk is using s1.t1_c1_seq
>
>
chester c young <chestercyoung@yahoo.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 likenextval('t1_c1_seq'::regclass)
3. Set search_path to s2.
4. Examine s2.t1's default expression; it looks likenextval('t1_c1_seq'::regclass)
5. Textually compare these defaults, find that they appear textually identical, conclude that s2.t1's default is
inheritedfrom 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