> From: chester c young <chestercyoung(at)yahoo(dot)com>
> Subject: Re: [SQL] pg_dump not correctly saving schema with partitioned tables?
> To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Date: Tuesday, January 31, 2012, 5:40 PM
> --- On Tue, 1/31/12, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> wrote:
>
> > From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> > Subject: Re: [SQL] pg_dump not correctly saving schema
> with partitioned tables?
> > To: "chester c young" <chestercyoung(at)yahoo(dot)com>
> > Cc: pgsql-sql(at)postgresql(dot)org
> > Date: Tuesday, January 31, 2012, 2:04 PM
> > chester c young <chestercyoung(at)yahoo(dot)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
>
>