Re: Sequence Dependency

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Saturday, June 10, 2023, Umut TEKİN <umuttechin@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
findthe 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'::regclassorder 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