From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: =?UTF-8?Q?Umut_TEK=C4=B0N?= <umuttechin@gmail.com>,
        "pgsql-docs@lists.postgresql.org" <pgsql-docs@lists.postgresql.org>
Subject: Re: Sequence Dependency
In-reply-to: 
 <CAKFQuwb1s1M7q5ZbnHb-UtMuNLam2Wiam8_sUbY-ohX3S-g2ig@mail.gmail.com>
References: 
 <CAPZcZR=jfFF6b6+ZQi+qBzv_+QD7GBFFRGyir=qJoXucPBf41A@mail.gmail.com>
 <CAKFQuwb1s1M7q5ZbnHb-UtMuNLam2Wiam8_sUbY-ohX3S-g2ig@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Mon, 12 Jun 2023 07:01:11 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1218523.1686579319.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 12 Jun 2023 10:15:19 -0400
Message-ID: <1218524.1686579319@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1218524.1686579319%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Saturday, June 10, 2023, Umut TEK=C4=B0N <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 find the pairs. Is there any oth=
er 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 thu=
s
> 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=3D# select pg_describe_object(classid,objid,objsubid) as obj, p=
g_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from pg=
_depend where objid >=3D 'public.actor_actor_id_seq'::regclass order by ob=
jid, 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_acto=
r_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                     |                           r=
ef                           | 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 t=
able pg_toast.pg_toast_89182 | a
 index pg_toast.pg_toast_89182_index         | column chunk_id of toast ta=
ble pg_toast.pg_toast_89182  | a
 index fruits_pkey                           | constraint fruits_pkey on t=
able 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