pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump not correctly saving schema with partitioned tables?
4+ messages / 2 participants
[nested] [flat]
* pg_dump not correctly saving schema with partitioned tables?
@ 2012-01-31 13:17 chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: chester c young @ 2012-01-31 13:17 UTC (permalink / raw)
To: pgsql-sql
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!)
is there any way I can reliably backup and restore the db?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_dump not correctly saving schema with partitioned tables?
@ 2012-01-31 21:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2012-01-31 21:04 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
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.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Fw: Re: pg_dump not correctly saving schema with partitioned tables?
@ 2012-02-01 00:42 chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: chester c young @ 2012-02-01 00:42 UTC (permalink / raw)
To: 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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fw: Re: [SQL] pg_dump not correctly saving schema with partitioned tables?
@ 2012-02-01 04:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2012-02-01 04:47 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql; pgsql-bugs@postgresql.org
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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-02-01 04:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-31 13:17 pg_dump not correctly saving schema with partitioned tables? chester c young <chestercyoung@yahoo.com>
2012-01-31 21:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-02-01 00:42 Fw: Re: pg_dump not correctly saving schema with partitioned tables? chester c young <chestercyoung@yahoo.com>
2012-02-01 04:47 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox