From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Umut TEKİN <umuttechin@gmail.com>
Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Subject: Re: Sequence Dependency
Date: Mon, 12 Jun 2023 10:15:19 -0400
Message-ID: <1218524.1686579319@sss.pgh.pa.us> (raw)
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>
"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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, umuttechin@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: Sequence Dependency
In-Reply-To: <1218524.1686579319@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox