Sequence Dependency

Поиск
Список
Период
Сортировка
От Umut TEKİN
Тема Sequence Dependency
Дата
Msg-id CAPZcZR=jfFF6b6+ZQi+qBzv_+QD7GBFFRGyir=qJoXucPBf41A@mail.gmail.com
обсуждение исходный текст
Ответы Re: Sequence Dependency  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список 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!








В списке pgsql-docs по дате отправления:

Предыдущее
От: PG Doc comments form
Дата:
Сообщение: Pattern matching also includes Full-text search, Trigram and Fuzzysearch
Следующее
От: PG Doc comments form
Дата:
Сообщение: Further clarification in documentation: No deletion of unreferenced large objects