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

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