CODEWITHSUNDEEP
postgresql
Nathaniel Ford
Nathaniel Ford

Reputation: 21239

Determine what objects depend on a sequence?

I'm attempting to DROP SEQUENCE on sequence that should not exist in my table. The documentation is clear that if the CASCADE option is used, objects that depend on that sequence will be dropped. How do you determine what objects those might be (if they exist)?

Specifically, before running this in a production database I need to verify that nothing critical will be deleted.

I can determine if a sequence has been used:

dev=> SELECT sequence_name, last_value FROM mytable_columnname_seq;

sequence_name          | last_value 
-----------------------+------------
mytable_columnname_seq |          2
(1 row)

In the above we see that the last_value is 2, which means it has been used. But it doesn't indicate by what.

Resolution (and thanks to a_horse_with_no_name and kiln; I couldn't accept both answers, which is a shame):

The 'dependent' object is the table's default value for the column the sequence is assigned to. If it is safe to remove the default value of the column, it is similarly safe to remove the sequence: no rows or tables will be deleted, and their values will remain unchanged.

Upvotes: 1

Views: 1141

Answers (2)

klin
klin

Reputation: 121774

If a sequence is owned by a column, like in this case:

create table example_table (id serial);

You can use pg_depend to determine table name from sequence name:

select refobjid::regclass as table_name
from pg_depend
where objid = 'example_table_id_seq'::regclass
and deptype = 'a';

  table_name   
---------------
 example_table
(1 row)

Upvotes: 2

user330315
user330315

Reputation:

This works for me:

select n.nspname as table_schema, cl.relname as table_name
from pg_class s
  join pg_depend d on d.objid=s.oid and d.classid='pg_class'::regclass and d.refclassid='pg_class'::regclass
  join pg_class cl on cl.oid = d.refobjid 
  join pg_namespace n on n.oid = cl.relnamespace
  join pg_attribute a on a.attrelid = cl.oid and a.attnum=d.refobjsubid
where s.relkind='S' 
  and d.deptype='a'  
  and n.nspname = 'schema of the sequence' 
  and s.relname = 'sequence name'

Upvotes: 2

Related Questions