Sequence Dependency

From: Umut TEKİN <umuttechin(at)gmail(dot)com>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Subject: Sequence Dependency
Date: 2023-06-10 14:32:45
Message-ID: CAPZcZR=jfFF6b6+ZQi+qBzv_+QD7GBFFRGyir=qJoXucPBf41A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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
);

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!

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message PG Doc comments form 2023-06-12 13:48:23 Further clarification in documentation: No deletion of unreferenced large objects
Previous Message PG Doc comments form 2023-06-09 03:21:14 Pattern matching also includes Full-text search, Trigram and Fuzzysearch