DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=dHe6IKWFHhisfBdNnR0Y9v9hNSJrVR5mtA93NAqMGgpOm6TlXD6dUsTDnVpnu6jn6dgPr/7eF8RffHV93qwY9N+BwmQZnvQhKIp0E2eLuTVaLD7NB9vS8MVFlJwMhbpWe7PabGYSz5+VcDcBd/hXN0F0CKdqrFwcPbNQrhEmtMQ=;
Message-ID: <1328056930.13462.YahooMailClassic@web161406.mail.bf1.yahoo.com>
Date: Tue, 31 Jan 2012 16:42:10 -0800 (PST)
From: chester c young <chestercyoung@yahoo.com>
Subject: Fw: Re: pg_dump not correctly saving schema with partitioned tables?
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

> From: chester c young <chestercyoung@yahoo.com>=0A> Subject: Re: [SQL] pg=
_dump not correctly saving schema with partitioned tables?=0A> To: "Tom Lan=
e" <tgl@sss.pgh.pa.us>=0A> Date: Tuesday, January 31, 2012, 5:40 PM=0A> ---=
 On Tue, 1/31/12, Tom Lane <tgl@sss.pgh.pa.us>=0A> wrote:=0A> =0A> > From: =
Tom Lane <tgl@sss.pgh.pa.us>=0A> > Subject: Re: [SQL] pg_dump not correctly=
 saving schema=0A> with partitioned tables?=0A> > To: "chester c young" <ch=
estercyoung@yahoo.com>=0A> > Cc: pgsql-sql@postgresql.org=0A> > Date: Tuesd=
ay, January 31, 2012, 2:04 PM=0A> > chester c young <chestercyoung@yahoo.co=
m>=0A> > writes:=0A> > > have database with many partitions.=A0 each=0A> > =
partition table has its own primary key sequence.=0A> > >=A0 =A0 Column=A0 =
||=A0 =A0 =A0 =A0=0A> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> > =A0 Modifiers=
=A0 =A0 =A0 =A0 =A0 =A0=0A> > =A0 =A0 =A0 =A0 =A0 =A0=0A> > =A0=A0=A0=0A> >=
 >=0A> >=0A> -----------++-------------------------------------------------=
-------------=0A> > >=A0 uno_id=A0 =A0 || not null default=0A> > nextval('c=
mp0004.cmt_uno_id_seq'::regclass)=0A> > =0A> > > when dumped and then resor=
ted, the pk sequence is=0A> > changed to an inherited- from table:=0A> > =
=0A> > =0A> > >=A0 =A0=A0=A0Column=A0 =A0 ||=A0=0A> > =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0=0A> > =A0 =A0 Modifiers=A0 =A0 =A0 =A0 =A0=0A> > =A0 =A0 =A0 =
=A0 =A0 =A0=A0=A0=0A> > >=0A> >=0A> --------------++-----------------------=
-------------------------------=0A> > >=A0 uno_id=A0 =A0 =A0=A0=A0|| not nu=
ll=0A> > default nextval('uno_uno_id_seq'::regclass)=0A> > =0A> > > (anothe=
r error is that the pk sequence does not=0A> spec=0A> > the schema!)=0A> > =
=0A> > I see no reason to think there is a bug here; it's=0A> more=0A> > li=
kely that you=0A> > do not understand the display behavior of regclass=0A> =
> constants.=A0 They only=0A> > print a schema name if your current search_=
path is such=0A> that=0A> > the=0A> > relation wouldn't be found by writing=
 just the=0A> unqualified=0A> > name.=0A> > So the most likely explanation =
for the discrepancy=0A> above is=0A> > that you=0A> > executed the two \d c=
ommands under different=0A> search_path=0A> > settings.=0A> > =0A> > It's p=
ossible that you have actually found a pg_dump=0A> bug,=0A> > but if so=0A>=
 > you'll need to submit a complete test-case exhibiting=0A> the=0A> > bug.=
=0A> > =0A> here is a test case:=0A> =0A> =0A> create database test2;=0A> \=
c test2;=0A> =0A> create schema s1;=0A> create schema s2;=0A> =0A> create t=
able s1.t1(=0A> =A0 =A0 c1=A0 bigserial=A0=A0=A0primary=0A> key,=0A> =A0 =
=A0 c2 text );=0A> =0A> create table s2.t1(=0A> =A0 =A0 c1=A0 bigserial=A0=
=A0=A0primary=0A> key=0A> ) inherits( s1.t1 );=0A> =0A> test2=3D# \d s2.t2=
=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 Table "s2.=
t2"=0A>  Column |=A0 Type=A0 |=A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0=0A> =
=A0=A0=A0Modifiers=A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =0A> ----=
----+--------+----------------------------------------------------=0A>  c1=
=A0 =A0=A0=A0| bigint | not null default=0A> nextval('s2.t2_c1_seq'::regcla=
ss)=0A>  c2=A0 =A0=A0=A0| text=A0=A0=A0| =0A> =0A> -- pg_dump test2 > test2=
.dmp=0A> =0A> create database test3=0A> \c test3=0A> \i test2.dmp=0A> =0A> =
test3=3D# \d s2.t1=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =
=A0 =A0 Table "s2.t1"=0A>  Column |=A0 Type=A0 |=A0 =A0 =A0 =A0=0A> =A0 =A0=
 =A0 =A0 =A0=0A> =A0=A0=A0Modifiers=A0 =A0 =A0 =A0=0A> =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =0A> --------+--------+--------------------------------------------=
--------=0A>  c1=A0 =A0=A0=A0| bigint | not null default=0A> nextval('s1.t1=
_c1_seq'::regclass)=0A>  c2=A0 =A0=A0=A0| text=A0=A0=A0| =0A> =0A> ## in th=
e original db table s2.t1 pk is using s2.t1_c1_seq=0A> ## in the dumped db =
table s2.t1 pk is using s1.t1_c1_seq=0A> =0A>