schema prefixes in default values (was RE: removing "serial" from table definitions).

Marc Mamin <M.Mamin@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)