Обсуждение: Identifying Schema-Qualified Sequence References in Column Defaults
Identifying Schema-Qualified Sequence References in Column Defaults
I'd like to bring attention to a metadata visibility issue when multiple schemas contain sequences with identical names, and a table column references one of them via nextval(). Currently, there appears to be no reliable way to determine which schema's sequence is actually referenced through system catalogs or views.
Repro Steps:
1、Create same-named sequences in different schemas:
SQL:
CREATE SEQUENCE public.seq_xx_yy;
CREATE SEQUENCE schema_1.seq_xx_yy;
2、Create a table with a column defaulting to nextval('seq_xx_yy'):
SQL:
CREATE TABLE schema_1.test_tab_100 (c1 int DEFAULT nextval('seq_xx_yy'));
(At creation time, search_path resolves this to public.seq_xx_yy.)
3、Query metadata:SQL:
SELECT column_default FROM information_schema.columns
WHERE table_name = 'test_tab_100'; -- Shows: nextval('seq_xx_yy'::regclass)
Neither information_schema.columns nor pg_sequences reveals the actual schema of the referenced sequence.
Is this a known limitation?
JiaoShuntian
HighGo Inc.
jiaoshuntian@highgo.com.w.kunlunaq.com writes:
> I'd like to bring attention to a metadata visibility issue when multiple schemas contain sequences with identical
names,and a table column references one of them via nextval(). Currently, there appears to be no reliable way to
determinewhich schema's sequence is actually referenced through system catalogs or views.
The documented behavior of type regclass is that it schema-qualifies
the output name if the table/sequence/whatever would not be found by
searching the current search_path. If you want something more
predictable, you could "set search_path = pg_catalog" before
inspecting the system catalogs.
regards, tom lane