Marc Mamin <m.ma...@intershop.de> writes:
> > Yes, I undersand that serial is just a hint at table creation time, but is 
> > there a place in catalog where we can see if the table was created using 
> > 'serial' ?
> 
> No.  Where the docs say "these are equivalent", they mean that very literally.
> 
> > The difference we see between the source and target database is that a 
> > schema prefix is displayed with the sequence on one side, and not on the 
> > other..
> 
> This likely has to do with the search_path settings being different in the 
> sessions inspecting the two DBs.  I do not think it is related to serial-ness 
> at all, it's just the normal behavior of regclass_out for the OID constant 
> that's the argument of nextval().
> 
>                       regards, tom lane

Hello,
it seems that our problem had nothing to do with serial, but with the way 
schema prefixes are handled in column default values.

pg_attrdef.adsrc:
  filled when the defaut value is defined. contains a schema prefix only when 
required at this creation time. Is constant afterwards.

pg_get_expr(adbin, adrelid)
   the returned expession is dynamic: the schema prefix is returned only  when 
the sequence schema is not part of the current search_path.
   
This behavior is understandable but it make it uncomfortable to compare table 
definitions between different sources.
Moreover a pg_dump->restore might in some cases modify the value of 
pg_attrdef.adsrc

best regards,

Marc Mamin

   
as test:   

     set search_path='admin';

     create table foo1 (n1 serial);

     set search_path='oms';

     create table admin.foo2 (n2 serial);

     select   a.attname, ad.adsrc, pg_get_expr(adbin, adrelid)
     FROM pg_attribute a
     JOIN  pg_attrdef ad ON (a.attnum=ad.adnum and a.attrelid=ad.adrelid)
     WHERE a.attrelid IN (Select oid from pg_class where relname 
in('foo1','foo2'));

     n1   nextval('foo1_n1_seq'::regclass)          
nextval('admin.foo1_n1_seq'::regclass)
     n2   nextval('admin.foo2_n2_seq'::regclass)     
nextval('admin.foo2_n2_seq'::regclass)                
 

     set search_path='admin';

     select   a.attname, ad.adsrc, pg_get_expr(adbin, adrelid)
     FROM pg_attribute a
     JOIN  pg_attrdef ad ON (a.attnum=ad.adnum and a.attrelid=ad.adrelid)
     WHERE a.attrelid IN (Select oid from pg_class where relname 
in('foo1','foo2'));
 
     n1   nextval('foo1_n1_seq'::regclass)          
nextval('foo1_n1_seq'::regclass)
     n2   nextval('admin.foo2_n2_seq'::regclass)    
nextval('foo2_n2_seq'::regclass)


Reply via email to