Fw: Re: pg_dump not correctly saving schema with partitioned tables? - Mailing list pgsql-sql

> 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
>
>