From: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Joe Conway <mail(at)joeconway(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | schema prefixes in default values (was RE: removing "serial" from table definitions). |
Date: | 2021-06-28 10:50:23 |
Message-ID: | 0fd15744721c4d64ae0373429e687e80@intershop.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Marc Mamin <M(dot)Mamin(at)intershop(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Yu Watanabe | 2021-06-28 11:41:07 | Memory activities to monitor in statistics collector? |
Previous Message | Ray O'Donnell | 2021-06-28 10:05:32 | Re: Overlapping timestamptz ranges with priority |