Обсуждение: Sequence Dependency
Hi,
I did not find any relation after reading relevant documents so I need to ask you:
When we create a table like this;
Method - 1
CREATE TABLE fruits(
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL
);
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL
);
it automatically creates a sequence and for that sequence it also creates an entry in the pg_depend table with value "a" on the "deptype" column. For this kind of setup we can easily use following query(https://dba.stackexchange.com/questions/260975/postgresql-how-can-i-list-the-tables-to-which-a-sequence-belongs) to find sequence and related table pair:
SELECT t.oid::regclass AS table_name,
a.attname AS column_name,
s.relname AS sequence_name
FROM pg_class AS t
JOIN pg_attribute AS a
ON a.attrelid = t.oid
JOIN pg_depend AS d
ON d.refobjid = t.oid
AND d.refobjsubid = a.attnum
JOIN pg_class AS s
ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
AND d.refclassid = 'pg_catalog.pg_class'::regclass
AND d.deptype IN ('i', 'a')
AND t.relkind IN ('r', 'P')
AND s.relkind = 'S';
On the other hand, if we create table with sequence like this:
Method - 2
CREATE SEQUENCE public.actor_actor_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
CREATE TABLE public.actor (
actor_id integer DEFAULT nextval('public.actor_actor_id_seq'::regclass) NOT NULL,
first_name text NOT NULL,
last_name text NOT NULL,
last_update timestamp with time zone DEFAULT now() NOT NULL
);
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?
Thanks!
On Saturday, June 10, 2023, Umut TEKİN <umuttechin@gmail.com> wrote:
Parse the defaults and check for matching sequence names?
You can alter a manually created sequence to be owned by a table and thus get the dependency added even in the second case.
David J.
"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
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@sss.pgh.pa.us> wrote:
"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