Re: Sequence Dependency

Hi,

I am sorry for my late response and thanks now it is fine.

Thanks!

On Mon, Jun 12, 2023 at 4:15 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Saturday, June 10, 2023, Umut TEKİN <umuttechin(at)gmail(dot)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
>