Re: BUG #19635: Missing entry in information_schema.sequences when creating a Tale with auto increment

пт, 21 авг. 2026 г. в 16:45, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> When running the following code I expect that a entry is generated in
> information_schema.sequences, however this is not the case.
>
> CREATE TABLE users (
> id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
> );
>
>
Hi Jonas!

This is expected. IDENTITY does create a sequence, but
information_schema.sequences is written to hide it.
GENERATED … AS IDENTITY marks the sequence as an internal dependency,
unlike SERIAL (sequence.c):
```
deptype = for_identity ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO;
```
information_schema.sequences then drops any sequence with deptype = 'i'
(information_schema.sql):
```
FROM pg_namespace nc, pg_class c, pg_sequence s
WHERE c.relnamespace = nc.oid
AND c.relkind = 'S'
AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid =
'pg_class'::regclass AND objid = c.oid AND deptype = 'i')
```

The regress test states the same contract (identity.sql):
```
-- internal sequences should not be shown here
SELECT sequence_name FROM information_schema.sequences WHERE sequence_name
LIKE 'itest%';
```
Expected output is zero rows. The sequence still exists (users_id_seq,
pg_get_serial_sequence(), pg_sequences).

The SQL-standard place for this metadata is information_schema.columns
(is_identity, identity_generation, identity_start, …). That view joins the
same internal sequence on deptype = 'i' (information_schema.sql):
```
LEFT JOIN (pg_depend dep JOIN pg_sequence seq ON (dep.classid =
'pg_class'::regclass AND dep.objid = seq.seqrelid AND dep.deptype = 'i'))
ON (dep.refclassid = 'pg_class'::regclass AND dep.refobjid = c.oid AND
dep.refobjsubid = a.attnum)
```

--
Regards,
Rachitskiy Andrey