Thread: schema prefixes in default values (was RE: removing "serial" from table definitions).
schema prefixes in default values (was RE: removing "serial" from table definitions).
From
Marc Mamin
Date:
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 seeif 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 onone 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 thinkit is related to serial-ness at all, it's just the normal behavior of regclass_out for the OID constant that's the argumentof 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 constantafterwards. 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 currentsearch_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)
Re: schema prefixes in default values (was RE: removing "serial" from table definitions).
From
Tom Lane
Date:
Marc Mamin <M.Mamin@intershop.de> writes: > it seems that our problem had nothing to do with serial, but with the way schema prefixes are handled in column defaultvalues. > pg_attrdef.adsrc: > filled when the defaut value is defined. contains a schema prefix only when required at this creation time. Is constantafterwards. We removed adsrc a couple of versions back, precisely because it doesn't track subsequent changes. Relying on it has been deprecated for a lot longer than that. > Moreover a pg_dump->restore might in some cases modify the value of pg_attrdef.adsrc pg_dump hasn't relied on adsrc since PG 7.2. regards, tom lane