Skip site navigation (1) Skip section navigation (2)

Re: Fw: Re: [SQL] pg_dump not correctly saving schema with partitioned tables?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: chester c young <chestercyoung(at)yahoo(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Fw: Re: [SQL] pg_dump not correctly saving schema with partitioned tables?
Date: 2012-02-01 04:47:11
Message-ID: 17951.1328071631@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-sql
chester c young <chestercyoung(at)yahoo(dot)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

In response to

pgsql-bugs by date

Next:From: o.bouscheDate: 2012-02-01 14:15:02
Subject: BUG #6424: Possible error in time to seconds conversion
Previous:From: Bridget FreyDate: 2012-02-01 01:36:38
Subject: Re: BUG #6200: standby bad memory allocations on SELECT

pgsql-sql by date

Next:From: Jasen BettsDate: 2012-02-01 10:52:41
Subject: Re: MS-SQL Store Procedure to Postgresql Function
Previous:From: Adrian KlaverDate: 2012-02-01 04:13:53
Subject: Re: Display Length Between Var & Varchar

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group