"David G. Johnston" <david.g.johns...@gmail.com> writes:
> On Saturday, June 10, 2023, Umut TEKİN <umuttec...@gmail.com> wrote:
>> it does not create any pg_depend entry for this sequence and table pair. So,
>> it is not possible to track down to find the pairs. Is there any other way
>> to find the sequence and the table pairs created using method 2?
> You can alter a manually created sequence to be owned by a table and thus
> get the dependency added even in the second case.
Yeah, that would be the way to match what SERIAL does (see [1]).
In the quoted example, there is a dependency from the column's default
expression to the sequence, so you could still detect the connection
without the ownership dependency; it's just harder. You have
regression=# select pg_describe_object(classid,objid,objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from
pg_depend where objid >= 'public.actor_actor_id_seq'::regclass order by objid,
refobjid;
obj |
ref | deptype
-----------------------------------------------------+---------------------------------------------------------+---------
sequence actor_actor_id_seq | schema public
| n
table actor | schema public
| n
type actor[] | type actor
| i
type actor | table actor
| i
default value for column actor_id of table actor | sequence
actor_actor_id_seq | n
default value for column actor_id of table actor | column actor_id of table
actor | a
default value for column last_update of table actor | column last_update of
table actor | a
toast table pg_toast.pg_toast_89174 | table actor
| i
index pg_toast.pg_toast_89174_index | column chunk_id of toast
table pg_toast.pg_toast_89174 | a
index pg_toast.pg_toast_89174_index | column chunk_seq of
toast table pg_toast.pg_toast_89174 | a
(10 rows)
versus
obj | ref
| deptype
---------------------------------------------+---------------------------------------------------------+---------
sequence fruits_id_seq | schema public
| n
sequence fruits_id_seq | column id of table fruits
| a
table fruits | schema public
| n
type fruits[] | type fruits
| i
type fruits | table fruits
| i
default value for column id of table fruits | sequence fruits_id_seq
| n
default value for column id of table fruits | column id of table fruits
| a
toast table pg_toast.pg_toast_89182 | table fruits
| i
index pg_toast.pg_toast_89182_index | column chunk_seq of toast table
pg_toast.pg_toast_89182 | a
index pg_toast.pg_toast_89182_index | column chunk_id of toast table
pg_toast.pg_toast_89182 | a
index fruits_pkey | constraint fruits_pkey on table
fruits | i
constraint fruits_pkey on table fruits | column id of table fruits
| a
(12 rows)
regards, tom lane
[1]
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL
