Re: Getting the currently used sequence for a SERIAL column

Thomas Kellerer <spam_eater(at)gmx(dot)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 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
(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 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
(5 rows)

regards, tom lane