How do you view new sequence ownership information in Postgres after using ALTER SEQUENCE?

Question

I'm using "ALTER SEQUENCE sequence OWNED BY table.column" to change sequence association in Postgres. Is there a way to view this new information using \ds or something similar? \ds still shows the table owner as the sequence owner.

Thermochemistry

Answer 1

OWNED BY table.column changes the associated column; OWNER TO newowner changes the role which owns the sequence. The owner role is what is displayed in \ds. I don't know a psql command to see the linked column of a sequence. OWNED BY is primary used to cascade a delete of the associated sequence when the referent column is removed.

You can get visibility into the owned by column by constructing a query against the system catalog tables pg_class, pg_depend, and pg_attribute. I'm not sure of all the exact semantics of pg_depend, but this query should show you a sequence's column dependency:

select tab.relname as tabname, attr.attname as column
from pg_class as seq
join pg_depend as dep on (seq.relfilenode = dep.objid)
join pg_class as tab on (dep.refobjid = tab.relfilenode)
join pg_attribute as attr on (attr.attnum = dep.refobjsubid and attr.attrelid = dep.refobjid)
where seq.relname = 'sequence';

Strohl

this query is not 100% correct - there should be appended a constraint - dep.classid = 1259 – Wilbanks

Answer 2

SELECT c.relname,u.usename 
  FROM pg_class c, pg_user u
 WHERE c.relowner = u.usesysid and c.relkind = 'S'
   AND relnamespace IN (
                        SELECT oid
                          FROM pg_namespace
                         WHERE nspname NOT LIKE 'pg_%'
                           AND nspname != 'information_schema'
                        );

Slipover

Answer 3

Yuri Levinsky's answer modified to include the namespace column:

SELECT n.nspname, c.relname, u.usename
FROM pg_class c
INNER JOIN pg_user u ON c.relowner = u.usesysid
INNER JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'S'
  AND n.nspname NOT LIKE 'pg_%'
  AND n.nspname != 'information_schema'
ORDER BY 1, 2, 3;

Phonetist

Answer 4

Would you also be able to change the owner on the sequences to zeus_eucus where the owner is postgres ? Otherwise I’m unable to modify them.

--list all the database sequences:

SELECT * FROM pg_sequences;

--Changing sequenceowner of a sequence:

$ ALTER SEQUENCE <schema_name>.<sequence_name> OWNERTO <new_name>;

Taradiddle

pg_sequences does not exist on older postgresql. – Tisdale