Re: [GENERAL] Getting the currently used sequence for a SERIAL column

[Thomas Kellerer]

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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

[Hanne Moa]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

[Thomas Kellerer]

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.





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

[Hanne Moa]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

[Thomas Kellerer]

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.







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

[Tom Lane]

Thomas Kellerer  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

[Thomas Kellerer]

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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

[Tom Lane]

Hanne Moa  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

[Thomas Kellerer]

> 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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general