Thread: Getting the currently used sequence for a SERIAL column
Hi,
Until now we've been using pg_get_serial_sequence() to discover
which sequence is in use, but can no longer do so due to two tables
needing to share the same sequence (prior to being properly merged. No
duplicate values, luckily). For one of the tables,
pg_get_serial_sequence() won't be returning anything useful since it
tracks which table *owns* a sequence and not which sequence is used
by which column.
The necessary information seems to be in the table
"information_schema.columns", in "column_default". Is this to be
regarded as internal API or is it safe to use this to find the correct
sequence? It works in all cases and on all the version of postgres
that are relevant to us. The production system is currently running
9.3 (I'm pining for 9.5...)
Furthermore, what's stored in the column seems to be a string of the
format "nextval('sequencename'::regclass)". Is there a function to
parse this, to return just the sequence name, or will the sequence
name always be without for instance a schema name so that a naive
parser of our own will do? Googling found no candidates.
HM
> Is this to be regarded as internal API or is it safe to use this to
> find the correct sequence?
I think it's safe to use.
> Furthermore, what's stored in the column seems to be a string of the
> format "nextval('sequencename'::regclass)". Is there a function to
> parse this, to return just the sequence name, or will the sequence
> name always be without for instance a schema name so that a naive
> parser of our own will do? Googling found no candidates.
In my experience, this could also be in the form "nextval('schema.sequencename'::regclass)"
if the sequence is not in the same schema as the table.
Thomas
Hanne Moa <hanne.moa@gmail.com> writes:
> Until now we've been using pg_get_serial_sequence() to discover
> which sequence is in use, but can no longer do so due to two tables
> needing to share the same sequence (prior to being properly merged. No
> duplicate values, luckily). For one of the tables,
> pg_get_serial_sequence() won't be returning anything useful since it
> tracks which table *owns* a sequence and not which sequence is used
> by which column.
> The necessary information seems to be in the table
> "information_schema.columns", in "column_default". Is this to be
> regarded as internal API or is it safe to use this to find the correct
> sequence? It works in all cases and on all the version of postgres
> that are relevant to us. The production system is currently running
> 9.3 (I'm pining for 9.5...)
> Furthermore, what's stored in the column seems to be a string of the
> format "nextval('sequencename'::regclass)". Is there a function to
> parse this, to return just the sequence name, or will the sequence
> name always be without for instance a schema name so that a naive
> parser of our own will do? Googling found no candidates.
Personally, I'd try looking in pg_depend to see if the column's default
expression has a dependency on a relation of type sequence. That avoids
all the fun of parsing the expression and turns it into a simple SQL
join problem.
regards, tom lane
Tom Lane schrieb am 18.10.2016 um 15:20:
>> Furthermore, what's stored in the column seems to be a string of the
>> format "nextval('sequencename'::regclass)". Is there a function to
>> parse this, to return just the sequence name, or will the sequence
>> name always be without for instance a schema name so that a naive
>> parser of our own will do? Googling found no candidates.
>
> Personally, I'd try looking in pg_depend to see if the column's default
> expression has a dependency on a relation of type sequence. That avoids
> all the fun of parsing the expression and turns it into a simple SQL
> join problem.
I thought pg_depend only stores the dependency if the the sequence was assigned
an owning column (through OWNED BY).
I don't see any entries in pg_depend for a simple "default nextval('some_sequence')" expression
but maybe I am just missing something.
Thomas
Thomas Kellerer <spam_eater@gmx.net> writes:
> Tom Lane schrieb am 18.10.2016 um 15:20:
>> Personally, I'd try looking in pg_depend to see if the column's default
>> expression has a dependency on a relation of type sequence. That avoids
>> all the fun of parsing the expression and turns it into a simple SQL
>> join problem.
> I thought pg_depend only stores the dependency if the the sequence was assigned
> an owning column (through OWNED BY).
No, there will be regular expression dependencies as well.
regression=# create table t1 (f1 serial);
CREATE TABLE
regression=# select * from pg_depend where objid = 't1_f1_seq'::regclass or refobjid = 't1_f1_seq'::regclass;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
1247 | 47198 | 0 | 1259 | 47197 | 0 | i
1259 | 47197 | 0 | 2615 | 2200 | 0 | n
2604 | 47202 | 0 | 1259 | 47197 | 0 | n
1259 | 47197 | 0 | 1259 | 47199 | 1 | a
(4 rows)
regression=# select pg_describe_object(classid,objid,objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid)as ref, deptype from pg_depend where objid = 't1_f1_seq'::regclass
orrefobjid = 't1_f1_seq'::regclass;
obj | ref | deptype
--------------------------------+--------------------+---------
type t1_f1_seq | sequence t1_f1_seq | i
sequence t1_f1_seq | schema public | n
default for table t1 column f1 | sequence t1_f1_seq | n
sequence t1_f1_seq | table t1 column f1 | a
(4 rows)
That 'a' dependency is the one that pg_get_serial_sequence() looks for,
but the default-to-sequence dependency will be there in any case.
regression=# create table t2(f2 int default nextval('t1_f1_seq'));
CREATE TABLE
regression=# select pg_describe_object(classid,objid,objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid)as ref, deptype from pg_depend where objid = 't1_f1_seq'::regclass
orrefobjid = 't1_f1_seq'::regclass;
obj | ref | deptype
--------------------------------+--------------------+---------
type t1_f1_seq | sequence t1_f1_seq | i
sequence t1_f1_seq | schema public | n
default for table t1 column f1 | sequence t1_f1_seq | n
sequence t1_f1_seq | table t1 column f1 | a
default for table t2 column f2 | sequence t1_f1_seq | n
(5 rows)
regards, tom lane
Tom Lane schrieb am 18.10.2016 um 16:11:
>> I thought pg_depend only stores the dependency if the the sequence was assigned
>> an owning column (through OWNED BY).
>
> No, there will be regular expression dependencies as well.
>
> That 'a' dependency is the one that pg_get_serial_sequence() looks for,
> but the default-to-sequence dependency will be there in any case.
>
> regression=# create table t2(f2 int default nextval('t1_f1_seq'));
> CREATE TABLE
> regression=# select pg_describe_object(classid,objid,objsubid) as
> obj, pg_describe_object(refclassid,refobjid,refobjsubid) as ref,
> deptype from pg_depend where objid = 't1_f1_seq'::regclass or
> refobjid = 't1_f1_seq'::regclass;
> obj | ref | deptype
> --------------------------------+--------------------+---------
> type t1_f1_seq | sequence t1_f1_seq | i
> sequence t1_f1_seq | schema public | n
> default for table t1 column f1 | sequence t1_f1_seq | n
> sequence t1_f1_seq | table t1 column f1 | a
> default for table t2 column f2 | sequence t1_f1_seq | n
Great, thanks. I meant to include that dependency in my SQL Workbench as well,
but could never find the correct way of joining the tables.
On 2016-10-18 16:11, Tom Lane wrote: > regression=# create table t1 (f1 serial); > CREATE TABLE > regression=# select * from pg_depend where objid = 't1_f1_seq'::regclass or refobjid = 't1_f1_seq'::regclass; > classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype > ---------+-------+----------+------------+----------+-------------+--------- > 1247 | 47198 | 0 | 1259 | 47197 | 0 | i > 1259 | 47197 | 0 | 2615 | 2200 | 0 | n > 2604 | 47202 | 0 | 1259 | 47197 | 0 | n > 1259 | 47197 | 0 | 1259 | 47199 | 1 | a > (4 rows) This seems to assume that I already know the name of the sequence? Looking at the sequence names that's already in use, I cannot safely assume anything about the format of their names. I start out knowing only the table and column, and I need a string with the sequence name to pass on to whatever wants it. As for pg_describe_object, SELECT pg_describe_object(classid,objid,objsubid) AS obj FROM pg_depend WHERE obj LIKE 'default%'; leads to "ERROR: column "obj" does not exist" on 9.5. Is the problem of restoring a database with sequences altered still a problem in 9.3+? https://www.postgresql.org/message-id/44D33E94.3010100%40list.za.net HM
Hanne Moa schrieb am 19.10.2016 um 09:06:
>> regression=# create table t1 (f1 serial);
>> CREATE TABLE
>> regression=# select * from pg_depend where objid = 't1_f1_seq'::regclass or refobjid = 't1_f1_seq'::regclass;
>> classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
>> ---------+-------+----------+------------+----------+-------------+---------
>> 1247 | 47198 | 0 | 1259 | 47197 | 0 | i
>> 1259 | 47197 | 0 | 2615 | 2200 | 0 | n
>> 2604 | 47202 | 0 | 1259 | 47197 | 0 | n
>> 1259 | 47197 | 0 | 1259 | 47199 | 1 | a
>> (4 rows)
>
> This seems to assume that I already know the name of the sequence?
> Looking at the sequence names that's already in use, I cannot safely
> assume anything about the format of their names. I start out knowing
> only the table and column, and I need a string with the sequence name to
> pass on to whatever wants it.
You can use the following statement to find the sequences that a table uses:
select sn.nspname as sequence_schema, s.relname as sequence_name
from pg_class s
join pg_namespace sn on sn.oid = s.relnamespace
join pg_depend d on d.refobjid = s.oid and d.refclassid='pg_class'::regclass
join pg_attrdef ad on ad.oid = d.objid and d.classid = 'pg_attrdef'::regclass
join pg_attribute col on col.attrelid = ad.adrelid and col.attnum = ad.adnum
join pg_class tbl on tbl.oid = ad.adrelid
join pg_namespace n on n.oid = tbl.relnamespace
where s.relkind = 'S'
and d.deptype in ('a', 'n')
and n.nspname = 'public'
and tbl.relname = 'foo'
Of course you can do that for multiple tables as well:
and (n.nspname, t.relname) in ( ('public', 'foo'), ('public'), ('bar') )
I am not entirely sure if that is the "shortest way" to do it, but it works for me.
On 2016-10-19 09:28, Thomas Kellerer wrote:
> You can use the following statement to find the sequences that a table uses:
>
> select sn.nspname as sequence_schema, s.relname as sequence_name
> from pg_class s
> join pg_namespace sn on sn.oid = s.relnamespace
> join pg_depend d on d.refobjid = s.oid and d.refclassid='pg_class'::regclass
> join pg_attrdef ad on ad.oid = d.objid and d.classid = 'pg_attrdef'::regclass
> join pg_attribute col on col.attrelid = ad.adrelid and col.attnum = ad.adnum
> join pg_class tbl on tbl.oid = ad.adrelid
> join pg_namespace n on n.oid = tbl.relnamespace
> where s.relkind = 'S'
> and d.deptype in ('a', 'n')
> and n.nspname = 'public'
> and tbl.relname = 'foo'
Thanks. This assumes that there is only one nextval per table though.
While this holds for the database we need this on right now, it's not a
sufficiently generic solution. How do I, for the sake of paranoia,
specify a column?
Btw, is there a site with recipes showing what's possible to do with the
pg_*-tables?
HM
Hanne Moa schrieb am 19.10.2016 um 10:31:
>> You can use the following statement to find the sequences that a table uses:
>>
> Thanks. This assumes that there is only one nextval per table though.
> While this holds for the database we need this on right now, it's not a
> sufficiently generic solution. How do I, for the sake of paranoia,
> specify a column?
No, it will return that for all columns in that table that have a sequence default
If you add "col.attname" to the select list you can see each column.
create sequence s1;
create sequence s2;
create table t1 (id1 integer default nextval('s1'), id2 integer default nextval('s2'), id3 integer default
nextval('s2'));
select sn.nspname as sequence_schema, s.relname as sequence_name, col.attname
from pg_class s
join pg_namespace sn on sn.oid = s.relnamespace
join pg_depend d on d.refobjid = s.oid and d.refclassid='pg_class'::regclass
join pg_attrdef ad on ad.oid = d.objid and d.classid = 'pg_attrdef'::regclass
join pg_attribute col on col.attrelid = ad.adrelid and col.attnum = ad.adnum
join pg_class tbl on tbl.oid = ad.adrelid
join pg_namespace n on n.oid = tbl.relnamespace
where s.relkind = 'S'
and d.deptype in ('a', 'n')
and n.nspname = 'public'
and tbl.relname = 't1'
Returns
sequence_schema | sequence_name | attname
----------------+---------------+--------
public | s1 | id1
public | s2 | id2
public | s2 | id3
Thomas