Thread: sequence depends on many tables
Hey,
I'm handling a very weird situation. I tried to check which sequences belong to a specific table (table_A) with the following query :
WITH
sequences AS
(
SELECT oid,relname FROM pg_class WHERE relkind = 'S'
)
SELECT s.oid as seq_oid,d.objid as objid,d.refobjid
FROM pg_depend d,sequences s
where
s.oid = d.objid
and d.deptype = 'a' and d.refobjid::regclass::text='table_A';
seq_oid | objid | refobjid
---------+-------+----------
17188 | 17188 | 17190
16566 | 16566 | 17190
16704 | 16704 | 17190
16704 | 16704 | 17190
16704 | 16704 | 17190
(5 rows)
sequences AS
(
SELECT oid,relname FROM pg_class WHERE relkind = 'S'
)
SELECT s.oid as seq_oid,d.objid as objid,d.refobjid
FROM pg_depend d,sequences s
where
s.oid = d.objid
and d.deptype = 'a' and d.refobjid::regclass::text='table_A';
seq_oid | objid | refobjid
---------+-------+----------
17188 | 17188 | 17190
16566 | 16566 | 17190
16704 | 16704 | 17190
16704 | 16704 | 17190
16704 | 16704 | 17190
(5 rows)
17188 - The sequence of table_A(id)
16566 and 16704 are sequences that belong to different tables and arent used by table_A.
16566 - The sequence of table_c(id)
16704 - The sequence of tableB(id)
In all my environments I got exactly one rows (one seq owned by the id column (pk) of the table). In one specific environment I got a weird output(The one u see here). The output indicates that 2 other sequences belongs to the current table when one of them have two rows that indicate it.
The next step was checking why it happened. I run the following query :
select objid,refobjid::regclass from pg_depend where objid=16704;
objid | refobjid
-------+-------------------------
16704 | 2200
16704 | table_A
16704 | table_A
16704 | table_A
16704 | table_B
(5 rows)
objid | refobjid
-------+-------------------------
16704 | 2200
16704 | table_A
16704 | table_A
16704 | table_A
16704 | table_B
(5 rows)
for unclear reason, both table A and table B depends on the sequence. When I check table_A I dont see any column that might use it..
I also checked who else depends on the 16556 objid :
select objid,refobjid::regclass from pg_depend where objid=16566;
objid | refobjid
-------+-----------------------
16566 | 2200
16566 | table_C
16566 | table_A
16566 | table_A_seq
(4 rows)
objid | refobjid
-------+-----------------------
16566 | 2200
16566 | table_C
16566 | table_A
16566 | table_A_seq
(4 rows)
any idea how to handle this issue ? I checked this on both pg 9.6/12 versions and I got the same weird results.
On Wed, 2019-09-25 at 15:39 +0300, Mariel Cherkassky wrote: > select objid,refobjid::regclass from pg_depend where objid=16704; > objid | refobjid > -------+------------------------- > 16704 | 2200 > 16704 | table_A > 16704 | table_A > 16704 | table_A > 16704 | table_B > (5 rows) > > for unclear reason, both table A and table B depends on the sequence. > When I check table_A I dont see any column that might use it.. Could you select all rows from pg_depend so that it is easier to see what is going on? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
There are many rows, anything specific u want to see ?
On Wed, 2019-09-25 at 22:20 +0300, Mariel Cherkassky wrote: [problems with sequence dependencies] > There are many rows, anything specific u want to see ? Sorry, I didn't mean all of pg_depend, but your query with all *columns* of pg_depend. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hey,
This is the full output with all the columns :
WITH
sequences AS
(
SELECT oid,relname FROM pg_class WHERE relkind = 'S'
)
SELECT s.oid as seq_oid,d.*
FROM pg_depend d,sequences s
where
s.oid = d.objid
and d.deptype = 'a' and d.refobjid::regclass::text='table_A';
seq_oid | classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+---------+-------+----------+------------+----------+-------------+---------
17188 | 1259 | 17188 | 0 | 1259 | 17190 | 1 | a
16566 | 2604 | 16566 | 0 | 1259 | 17190 | 1 | a
16704 | 2606 | 16704 | 0 | 1259 | 17190 | 3 | a
16704 | 2606 | 16704 | 0 | 1259 | 17190 | 5 | a
16704 | 2606 | 16704 | 0 | 1259 | 17190 | 4 | a
(5 rows)
select *,refobjid::regclass from pg_depend where objid=16704;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype | refobjid
---------+-------+----------+------------+----------+-------------+---------+-------------------------
1259 | 16704 | 0 | 2615 | 2200 | 0 | n | 2200
2606 | 16704 | 0 | 1259 | 17190 | 3 | a | table_A
2606 | 16704 | 0 | 1259 | 17190 | 5 | a | table_A
2606 | 16704 | 0 | 1259 | 17190 | 4 | a | table_A
1259 | 16704 | 0 | 1259 | 16706 | 1 | a | table_B
(5 rows)
select *,refobjid::regclass from pg_depend where objid=16566;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype | refobjid
---------+-------+----------+------------+----------+-------------+---------+-----------------------
1259 | 16566 | 0 | 2615 | 2200 | 0 | n | 2200
1259 | 16566 | 0 | 1259 | 16568 | 2 | a | table_C
2604 | 16566 | 0 | 1259 | 17190 | 1 | a | table_A
2604 | 16566 | 0 | 1259 | 17188 | 0 | n | table_A_seq
(4 rows)
sequences AS
(
SELECT oid,relname FROM pg_class WHERE relkind = 'S'
)
SELECT s.oid as seq_oid,d.*
FROM pg_depend d,sequences s
where
s.oid = d.objid
and d.deptype = 'a' and d.refobjid::regclass::text='table_A';
seq_oid | classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+---------+-------+----------+------------+----------+-------------+---------
17188 | 1259 | 17188 | 0 | 1259 | 17190 | 1 | a
16566 | 2604 | 16566 | 0 | 1259 | 17190 | 1 | a
16704 | 2606 | 16704 | 0 | 1259 | 17190 | 3 | a
16704 | 2606 | 16704 | 0 | 1259 | 17190 | 5 | a
16704 | 2606 | 16704 | 0 | 1259 | 17190 | 4 | a
(5 rows)
select *,refobjid::regclass from pg_depend where objid=16704;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype | refobjid
---------+-------+----------+------------+----------+-------------+---------+-------------------------
1259 | 16704 | 0 | 2615 | 2200 | 0 | n | 2200
2606 | 16704 | 0 | 1259 | 17190 | 3 | a | table_A
2606 | 16704 | 0 | 1259 | 17190 | 5 | a | table_A
2606 | 16704 | 0 | 1259 | 17190 | 4 | a | table_A
1259 | 16704 | 0 | 1259 | 16706 | 1 | a | table_B
(5 rows)
select *,refobjid::regclass from pg_depend where objid=16566;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype | refobjid
---------+-------+----------+------------+----------+-------------+---------+-----------------------
1259 | 16566 | 0 | 2615 | 2200 | 0 | n | 2200
1259 | 16566 | 0 | 1259 | 16568 | 2 | a | table_C
2604 | 16566 | 0 | 1259 | 17190 | 1 | a | table_A
2604 | 16566 | 0 | 1259 | 17188 | 0 | n | table_A_seq
(4 rows)
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes: > seq_oid | classid | objid | objsubid | refclassid | refobjid | refobjsubid > | deptype > ---------+---------+-------+----------+------------+----------+-------------+--------- > 17188 | 1259 | 17188 | 0 | 1259 | 17190 | 1 > | a > 16566 | 2604 | 16566 | 0 | 1259 | 17190 | 1 > | a > 16704 | 2606 | 16704 | 0 | 1259 | 17190 | 3 > | a > 16704 | 2606 | 16704 | 0 | 1259 | 17190 | 5 > | a > 16704 | 2606 | 16704 | 0 | 1259 | 17190 | 4 > | a > (5 rows) Well, those entries with objid = 16566 and 16704 are not for sequences, because the classid is wrong: 2604 is pg_attrdef, and 2606 is pg_constraint, so the second row is for a default expression belonging to table 17190 column 1, and the rest are for some kind of constraint involving columns 3,4,5 (maybe a check constraint?) In itself there's nothing wrong with these pg_depend entries, but it is odd that you have different objects with identical OIDs. Normally I'd only expect that to be possible once the OID counter has wrapped around ... but all these OIDs are small, which makes it seem unlikely that you've consumed enough OIDs to reach wraparound. Maybe you had a system crash, or did something weird with backup/recovery, causing the counter to get reset? Anyway, the short answer here is that neither objid nor refobjid should be considered sufficient to identify an object by themselves. You need to also check classid (refclassid), because OIDs are only guaranteed unique within a given system catalog. regards, tom lane