From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | sequence depends on many tables |
Date: | 2019-09-25 12:39:46 |
Message-ID: | CA+t6e1=UVWcQfn+am-P+_KgpuRk94+uNZkeAgfOvyDXJfU4ELA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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)
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)
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)
any idea how to handle this issue ? I checked this on both pg 9.6/12
versions and I got the same weird results.
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2019-09-25 19:19:22 | Re: sequence depends on many tables |
Previous Message | Daulat Ram | 2019-09-24 10:05:25 | RE: Monitor Postgres database status on Docker |