Hi. Rebase only.
-- jian https://www.enterprisedb.com/
From 3136cddd6a8762aacbf6c9cbce84db2300ab0ebc Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 10 Mar 2026 17:56:16 +0800 Subject: [PATCH v10 4/5] CREATE SCHEMA CREATE TYPE The SQL standard allows types to be specified in a CREATE SCHEMA statement. This adds support for that capability. For example: CREATE SCHEMA schema_name CREATE TYPE ss; The type will be created within the to be created schema. The type name can be schema-qualified or database-qualified, however it's not allowed to let type create within a different schema. Discussion: https://postgr.es/m/caldssph4jusdswu3k58hjo60wntrr0duo4ckrcwa8evuosf...@mail.gmail.com Commitfest: https://commitfest.postgresql.org/patch/5985 --- doc/src/sgml/ref/create_schema.sgml | 3 +- src/backend/parser/parse_utilcmd.c | 52 ++++++++++-- .../expected/create_schema.out | 16 +++- .../test_ddl_deparse/sql/create_schema.sql | 6 +- src/test/regress/expected/create_schema.out | 83 +++++++++++++++++++ src/test/regress/expected/create_type.out | 12 +++ src/test/regress/sql/create_schema.sql | 39 +++++++++ src/test/regress/sql/create_type.sql | 12 +++ 8 files changed, 211 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index d8273bb2d0c..905e966e30e 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -104,7 +104,8 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_sp <command>CREATE DOMAIN</command> <command>CREATE TABLE</command>, <command>CREATE VIEW</command>, <command>CREATE INDEX</command>, <command>CREATE SEQUENCE</command>, <command>CREATE - TRIGGER</command> and <command>GRANT</command> are accepted as clauses + TRIGGER</command>, <command>CREATE TYPE</command> and + <command>GRANT</command> are accepted as clauses within <command>CREATE SCHEMA</command>. Other kinds of objects may be created in separate commands after the schema is created. </para> diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 723a017a4cb..b6ee8342fde 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -4502,7 +4502,7 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts, obj_type = asc_toupper(stringify_objtype(stmt->kind), strlen(stringify_objtype(stmt->kind))); - if (stmt->kind != OBJECT_COLLATION) + if (stmt->kind != OBJECT_COLLATION && stmt->kind != OBJECT_TYPE) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("CREATE SCHEMA ... CREATE %s currently not supported", obj_type)); @@ -4519,16 +4519,52 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts, } break; - /* - * gram.y classifies these as DefineStmt as well; therefore, - * we must explicitly raise an error for these cases. - */ case T_CompositeTypeStmt: + { + CompositeTypeStmt *stmt = castNode(CompositeTypeStmt, element); + + checkSchemaName(pstate, schemaName, stmt->typevar); + + elements = lappend(elements, element); + } + break; + case T_CreateEnumStmt: + { + char *typschema = NULL; + char *typname; + + CreateEnumStmt *stmt = castNode(CreateEnumStmt, element); + + DeconstructQualifiedName(stmt->typeName, &typschema, &typname); + + if (typschema && strcmp(typschema, schemaName) != 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_SCHEMA_DEFINITION), + errmsg("CREATE TYPE specifies a schema (%s) different from the one being created (%s)", + typschema, schemaName)); + + elements = lappend(elements, element); + } + break; + case T_CreateRangeStmt: - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("CREATE SCHEMA ... CREATE TYPE currently not supported")); + { + char *typschema = NULL; + char *typname; + + CreateRangeStmt *stmt = castNode(CreateRangeStmt, element); + + DeconstructQualifiedName(stmt->typeName, &typschema, &typname); + + if (typschema && strcmp(typschema, schemaName) != 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_SCHEMA_DEFINITION), + errmsg("CREATE TYPE specifies a schema (%s) different from the one being created (%s)", + schemaName, typschema)); + + elements = lappend(elements, element); + } break; default: elog(ERROR, "unrecognized node type: %d", diff --git a/src/test/modules/test_ddl_deparse/expected/create_schema.out b/src/test/modules/test_ddl_deparse/expected/create_schema.out index e3c197af118..92da27c813b 100644 --- a/src/test/modules/test_ddl_deparse/expected/create_schema.out +++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out @@ -15,15 +15,27 @@ CREATE SCHEMA element_test CREATE TABLE foo (id int) CREATE VIEW bar AS SELECT * FROM foo CREATE DOMAIN d1 AS INT - CREATE COLLATION coll (LOCALE="C"); + CREATE COLLATION coll (LOCALE="C") + CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi) + CREATE TYPE ss AS (a int) + CREATE TYPE sss + CREATE TYPE rainbow AS ENUM ('red', 'orange'); NOTICE: DDL test: type simple, tag CREATE SCHEMA NOTICE: DDL test: type simple, tag CREATE TABLE NOTICE: DDL test: type simple, tag CREATE VIEW NOTICE: DDL test: type simple, tag CREATE DOMAIN NOTICE: DDL test: type simple, tag CREATE COLLATION +NOTICE: DDL test: type simple, tag CREATE TYPE +NOTICE: DDL test: type simple, tag CREATE TYPE +NOTICE: DDL test: type simple, tag CREATE TYPE +NOTICE: DDL test: type simple, tag CREATE TYPE DROP SCHEMA element_test CASCADE; -NOTICE: drop cascades to 4 other objects +NOTICE: drop cascades to 8 other objects DETAIL: drop cascades to table element_test.foo drop cascades to view element_test.bar drop cascades to type element_test.d1 drop cascades to collation element_test.coll +drop cascades to type element_test.floatrange +drop cascades to type element_test.ss +drop cascades to type element_test.sss +drop cascades to type element_test.rainbow diff --git a/src/test/modules/test_ddl_deparse/sql/create_schema.sql b/src/test/modules/test_ddl_deparse/sql/create_schema.sql index 0c61db13935..abe0637c415 100644 --- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql +++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql @@ -16,6 +16,10 @@ CREATE SCHEMA element_test CREATE TABLE foo (id int) CREATE VIEW bar AS SELECT * FROM foo CREATE DOMAIN d1 AS INT - CREATE COLLATION coll (LOCALE="C"); + CREATE COLLATION coll (LOCALE="C") + CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi) + CREATE TYPE ss AS (a int) + CREATE TYPE sss + CREATE TYPE rainbow AS ENUM ('red', 'orange'); DROP SCHEMA element_test CASCADE; diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out index 3e061c7a0ef..a33cbd0c0d9 100644 --- a/src/test/regress/expected/create_schema.out +++ b/src/test/regress/expected/create_schema.out @@ -212,6 +212,76 @@ CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE regress_schema_5 | coll | libc | C | C | | | yes (1 row) +-----CREATE SCHEMA CREATE TYPE +--fail. can not CREATE TYPE to other schema +CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss AS (a int); +ERROR: CREATE specifies a schema (public) different from the one being created (regress_schema_6) +LINE 1: CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss AS (a i... + ^ +CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss; +ERROR: CREATE TYPE specifies a schema (public) different from the one being created (regress_schema_6) +CREATE SCHEMA regress_schema_6 CREATE TYPE public.rainbow AS ENUM ('red', 'orange'); +ERROR: CREATE TYPE specifies a schema (public) different from the one being created (regress_schema_6) +CREATE SCHEMA regress_schema_6 CREATE TYPE public.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi); +ERROR: CREATE TYPE specifies a schema (regress_schema_6) different from the one being created (public) +--fail. improper qualified name +CREATE SCHEMA regress_schema_6 AUTHORIZATION CURRENT_ROLE + CREATE TYPE postgres.public.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi); +ERROR: cross-database references are not implemented: postgres.public.floatrange +--the following tests should error, because we execute subcommands in order +CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE TYPE ss; +ERROR: type "ss" does not exist +LINE 1: CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE... + ^ +CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE TYPE ss AS (a int); +ERROR: type "ss" does not exist +LINE 1: CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE... + ^ +CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a rainbow) CREATE TYPE rainbow AS ENUM ('red', 'orange'); +ERROR: type "rainbow" does not exist +LINE 1: CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a rainbow) C... + ^ +CREATE SCHEMA regress_schema_6 + CREATE TABLE tts(a floatrange) + CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi); +ERROR: type "floatrange" does not exist +LINE 2: CREATE TABLE tts(a floatrange) + ^ +--ok +CREATE SCHEMA regress_schema_6 + CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi) + CREATE TYPE ss AS (a int) + CREATE TYPE sss + CREATE TYPE rainbow AS ENUM ('red', 'orange'); +\dT regress_schema_6.* + List of data types + Schema | Name | Description +------------------+----------------------------------+------------- + regress_schema_6 | regress_schema_6.floatmultirange | + regress_schema_6 | regress_schema_6.floatrange | + regress_schema_6 | regress_schema_6.rainbow | + regress_schema_6 | regress_schema_6.ss | + regress_schema_6 | regress_schema_6.sss | +(5 rows) + +--schema qualified, ok +CREATE SCHEMA regress_schema_7 + CREATE TYPE regress_schema_7.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi) + CREATE TYPE regress_schema_7.ss AS (a int) + CREATE TYPE regress_schema_7.sss + CREATE TYPE regress_schema_7.rainbow AS ENUM ('red', 'orange') + CREATE TABLE t(a floatrange, b ss, c rainbow); +\dT regress_schema_7.* + List of data types + Schema | Name | Description +------------------+----------------------------------+------------- + regress_schema_7 | regress_schema_7.floatmultirange | + regress_schema_7 | regress_schema_7.floatrange | + regress_schema_7 | regress_schema_7.rainbow | + regress_schema_7 | regress_schema_7.ss | + regress_schema_7 | regress_schema_7.sss | +(5 rows) + DROP SCHEMA regress_schema_2 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to type regress_schema_2.ss @@ -230,5 +300,18 @@ DROP SCHEMA regress_schema_5 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to collation regress_schema_5.coll drop cascades to table regress_schema_5.t +DROP SCHEMA regress_schema_6 CASCADE; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to type regress_schema_6.floatrange +drop cascades to type regress_schema_6.ss +drop cascades to type regress_schema_6.sss +drop cascades to type regress_schema_6.rainbow +DROP SCHEMA regress_schema_7 CASCADE; +NOTICE: drop cascades to 5 other objects +DETAIL: drop cascades to type regress_schema_7.floatrange +drop cascades to type regress_schema_7.ss +drop cascades to type regress_schema_7.sss +drop cascades to type regress_schema_7.rainbow +drop cascades to table regress_schema_7.t -- Clean up DROP ROLE regress_create_schema_role; diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out index 5181c4290b4..83f63e5c8cc 100644 --- a/src/test/regress/expected/create_type.out +++ b/src/test/regress/expected/create_type.out @@ -35,6 +35,18 @@ CREATE FUNCTION int44out(city_budget) NOTICE: argument type city_budget is only a shell LINE 1: CREATE FUNCTION int44out(city_budget) ^ +----- CREATE SCHEMA CREATE TYPE +--error, cannot CREATE TYPE to other schema +CREATE SCHEMA regress_schema_x + CREATE TYPE public.widget ( + internallength = 24, + input = widget_in, + output = widget_out, + typmod_in = numerictypmodin, + typmod_out = numerictypmodout, + alignment = double + ); +ERROR: CREATE TYPE specifies a schema (public) different from the one being created (regress_schema_x) CREATE TYPE widget ( internallength = 24, input = widget_in, diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql index 0f802bcaffe..75fd928eacb 100644 --- a/src/test/regress/sql/create_schema.sql +++ b/src/test/regress/sql/create_schema.sql @@ -131,10 +131,49 @@ CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE \dO regress_schema_5.* + +-----CREATE SCHEMA CREATE TYPE +--fail. can not CREATE TYPE to other schema +CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss AS (a int); +CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss; +CREATE SCHEMA regress_schema_6 CREATE TYPE public.rainbow AS ENUM ('red', 'orange'); +CREATE SCHEMA regress_schema_6 CREATE TYPE public.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi); + +--fail. improper qualified name +CREATE SCHEMA regress_schema_6 AUTHORIZATION CURRENT_ROLE + CREATE TYPE postgres.public.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi); + +--the following tests should error, because we execute subcommands in order +CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE TYPE ss; +CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE TYPE ss AS (a int); +CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a rainbow) CREATE TYPE rainbow AS ENUM ('red', 'orange'); +CREATE SCHEMA regress_schema_6 + CREATE TABLE tts(a floatrange) + CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi); + +--ok +CREATE SCHEMA regress_schema_6 + CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi) + CREATE TYPE ss AS (a int) + CREATE TYPE sss + CREATE TYPE rainbow AS ENUM ('red', 'orange'); +\dT regress_schema_6.* + +--schema qualified, ok +CREATE SCHEMA regress_schema_7 + CREATE TYPE regress_schema_7.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi) + CREATE TYPE regress_schema_7.ss AS (a int) + CREATE TYPE regress_schema_7.sss + CREATE TYPE regress_schema_7.rainbow AS ENUM ('red', 'orange') + CREATE TABLE t(a floatrange, b ss, c rainbow); +\dT regress_schema_7.* + DROP SCHEMA regress_schema_2 CASCADE; DROP SCHEMA regress_schema_3 CASCADE; DROP SCHEMA regress_schema_4 CASCADE; DROP SCHEMA regress_schema_5 CASCADE; +DROP SCHEMA regress_schema_6 CASCADE; +DROP SCHEMA regress_schema_7 CASCADE; -- Clean up DROP ROLE regress_create_schema_role; diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql index c25018029c2..69efd069f58 100644 --- a/src/test/regress/sql/create_type.sql +++ b/src/test/regress/sql/create_type.sql @@ -32,6 +32,18 @@ CREATE FUNCTION int44out(city_budget) AS :'regresslib' LANGUAGE C STRICT IMMUTABLE; +----- CREATE SCHEMA CREATE TYPE +--error, cannot CREATE TYPE to other schema +CREATE SCHEMA regress_schema_x + CREATE TYPE public.widget ( + internallength = 24, + input = widget_in, + output = widget_out, + typmod_in = numerictypmodin, + typmod_out = numerictypmodout, + alignment = double + ); + CREATE TYPE widget ( internallength = 24, input = widget_in, -- 2.34.1
From 1a255ed67ae9a020c20ad0c3297b91e40ec7ddd0 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 10 Mar 2026 17:53:47 +0800 Subject: [PATCH v10 3/5] CREATE SCHEMA CREATE COLLATION The SQL standard allows collation to be specified in a CREATE SCHEMA statement. This adds support for that capability. For example: CREATE SCHEMA schema_name CREATE COLLATION coll_icu_und FROM "und-x-icu"; The collation will be created within the to be created schema. The collation name can be schema-qualified or database-qualified, however it's not allowed to let collation create within a different schema. Discussion: https://postgr.es/m/caldssph4jusdswu3k58hjo60wntrr0duo4ckrcwa8evuosf...@mail.gmail.com Commitfest: https://commitfest.postgresql.org/patch/5985 --- doc/src/sgml/ref/create_schema.sgml | 3 +- src/backend/catalog/objectaddress.c | 18 ++++++++ src/backend/parser/gram.y | 1 + src/backend/parser/parse_utilcmd.c | 40 +++++++++++++++++ src/include/catalog/objectaddress.h | 1 + .../expected/create_schema.out | 7 ++- .../test_ddl_deparse/sql/create_schema.sql | 3 +- .../regress/expected/collate.icu.utf8.out | 15 +++++++ src/test/regress/expected/create_schema.out | 43 +++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 7 +++ src/test/regress/sql/create_schema.sql | 29 +++++++++++++ 11 files changed, 163 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index 79186d2b936..d8273bb2d0c 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -100,7 +100,8 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_sp <listitem> <para> An SQL statement defining an object to be created within the - schema. Currently, only <command>CREATE DOMAIN</command> + schema. Currently, only <command>CREATE COLLATION</command>, + <command>CREATE DOMAIN</command> <command>CREATE TABLE</command>, <command>CREATE VIEW</command>, <command>CREATE INDEX</command>, <command>CREATE SEQUENCE</command>, <command>CREATE TRIGGER</command> and <command>GRANT</command> are accepted as clauses diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index d32aaff2821..9bd5c76c016 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -2622,6 +2622,24 @@ read_objtype_from_string(const char *objtype) return -1; /* keep compiler quiet */ } +/* get the ObjectType name */ +const char * +stringify_objtype(ObjectType objtype) +{ + for (int i = 0; i < lengthof(ObjectTypeMap); i++) + { + if (ObjectTypeMap[i].tm_type == objtype) + return ObjectTypeMap[i].tm_name; + } + + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("unrecognized object type %d", objtype)); + + return NULL; /* keep compiler quiet */ +} + + /* * Interfaces to reference fields of ObjectPropertyType */ diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 1a9b3759376..e867d1d5661 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -1641,6 +1641,7 @@ schema_stmt: | GrantStmt | ViewStmt | CreateDomainStmt + | DefineStmt ; diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 9b4a209378d..723a017a4cb 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -64,6 +64,7 @@ #include "rewrite/rewriteManip.h" #include "utils/acl.h" #include "utils/builtins.h" +#include "utils/formatting.h" #include "utils/lsyscache.h" #include "utils/partcache.h" #include "utils/rel.h" @@ -4490,6 +4491,45 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts, } break; + case T_DefineStmt: + { + char *coll_schema = NULL; + char *collName; + char *obj_type; + + DefineStmt *stmt = castNode(DefineStmt, element); + + obj_type = asc_toupper(stringify_objtype(stmt->kind), + strlen(stringify_objtype(stmt->kind))); + + if (stmt->kind != OBJECT_COLLATION) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("CREATE SCHEMA ... CREATE %s currently not supported", obj_type)); + + DeconstructQualifiedName(stmt->defnames, &coll_schema, &collName); + + if (coll_schema && strcmp(coll_schema, schemaName) != 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_SCHEMA_DEFINITION), + errmsg("CREATE %s specifies a schema (%s) different from the one being created (%s)", + obj_type, coll_schema, schemaName)); + + elements = lappend(elements, element); + } + break; + + /* + * gram.y classifies these as DefineStmt as well; therefore, + * we must explicitly raise an error for these cases. + */ + case T_CompositeTypeStmt: + case T_CreateEnumStmt: + case T_CreateRangeStmt: + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("CREATE SCHEMA ... CREATE TYPE currently not supported")); + break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(element)); diff --git a/src/include/catalog/objectaddress.h b/src/include/catalog/objectaddress.h index b549be2d523..20d52ee589a 100644 --- a/src/include/catalog/objectaddress.h +++ b/src/include/catalog/objectaddress.h @@ -80,6 +80,7 @@ extern char *getObjectDescription(const ObjectAddress *object, extern char *getObjectDescriptionOids(Oid classid, Oid objid); extern int read_objtype_from_string(const char *objtype); +extern const char *stringify_objtype(ObjectType objtype); extern char *getObjectTypeDescription(const ObjectAddress *object, bool missing_ok); extern char *getObjectIdentity(const ObjectAddress *object, diff --git a/src/test/modules/test_ddl_deparse/expected/create_schema.out b/src/test/modules/test_ddl_deparse/expected/create_schema.out index 634900d8456..e3c197af118 100644 --- a/src/test/modules/test_ddl_deparse/expected/create_schema.out +++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out @@ -14,13 +14,16 @@ NOTICE: schema "baz" already exists, skipping CREATE SCHEMA element_test CREATE TABLE foo (id int) CREATE VIEW bar AS SELECT * FROM foo - CREATE DOMAIN d1 AS INT; + CREATE DOMAIN d1 AS INT + CREATE COLLATION coll (LOCALE="C"); NOTICE: DDL test: type simple, tag CREATE SCHEMA NOTICE: DDL test: type simple, tag CREATE TABLE NOTICE: DDL test: type simple, tag CREATE VIEW NOTICE: DDL test: type simple, tag CREATE DOMAIN +NOTICE: DDL test: type simple, tag CREATE COLLATION DROP SCHEMA element_test CASCADE; -NOTICE: drop cascades to 3 other objects +NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to table element_test.foo drop cascades to view element_test.bar drop cascades to type element_test.d1 +drop cascades to collation element_test.coll diff --git a/src/test/modules/test_ddl_deparse/sql/create_schema.sql b/src/test/modules/test_ddl_deparse/sql/create_schema.sql index b822517b64d..0c61db13935 100644 --- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql +++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql @@ -15,6 +15,7 @@ CREATE SCHEMA IF NOT EXISTS baz; CREATE SCHEMA element_test CREATE TABLE foo (id int) CREATE VIEW bar AS SELECT * FROM foo - CREATE DOMAIN d1 AS INT; + CREATE DOMAIN d1 AS INT + CREATE COLLATION coll (LOCALE="C"); DROP SCHEMA element_test CASCADE; diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index 1325e123877..fdd4439674f 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -1297,6 +1297,21 @@ DROP TABLE test7; CREATE COLLATION testcoll_rulesx (provider = icu, locale = '', rules = '!!wrong!!'); NOTICE: using standard form "und" for ICU locale "" ERROR: could not open collator for locale "und" with rules "!!wrong!!": U_INVALID_FORMAT_ERROR +--CREATE SCHEMA CREATE COLLATION +CREATE SCHEMA regress_schema_4 + CREATE COLLATION coll_icu_und FROM "und-x-icu" + CREATE TABLE tts(a TEXT COLLATE coll_icu_und); +\dO regress_schema_4.* + List of collations + Schema | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic? +------------------+--------------+----------+---------+-------+--------+-----------+---------------- + regress_schema_4 | coll_icu_und | icu | | | und | | yes +(1 row) + +DROP SCHEMA regress_schema_4 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to collation regress_schema_4.coll_icu_und +drop cascades to table regress_schema_4.tts -- nondeterministic collations CREATE COLLATION ctest_det (provider = icu, locale = '', deterministic = true); NOTICE: using standard form "und" for ICU locale "" diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out index 0533c29a311..3e061c7a0ef 100644 --- a/src/test/regress/expected/create_schema.out +++ b/src/test/regress/expected/create_schema.out @@ -177,6 +177,41 @@ CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE regress_schema_3 | ss1 | regress_schema_3.ss | | | 'hello'::text | (2 rows) +-- Cases where the schema creation with collations +--fail. can not CREATE COLLATION to other schema +CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE + CREATE COLLATION public.coll_icu_und FROM "und-x-icu"; +ERROR: CREATE COLLATION specifies a schema (public) different from the one being created (regress_schema_4) +--fail. improper qualified name +CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE + CREATE COLLATION postgres.public.coll_icu_und FROM "und-x-icu"; +ERROR: cross-database references are not implemented: postgres.public.coll_icu_und +--fail. only support collation object for DefineStmt node +CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE + CREATE AGGREGATE balk(int4) (SFUNC = int4_sum(int8, int4), STYPE = int8, PARALLEL = SAFE, INITCOND = '0'); +ERROR: CREATE SCHEMA ... CREATE AGGREGATE currently not supported +--ok, qualified schema name for collation should be same as the created schema +CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE + CREATE COLLATION regress_schema_4.coll (LOCALE="C") + CREATE TABLE t(a TEXT COLLATE regress_schema_4.coll); +\dO regress_schema_4.* + List of collations + Schema | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic? +------------------+------+----------+---------+-------+--------+-----------+---------------- + regress_schema_4 | coll | libc | C | C | | | yes +(1 row) + +--ok, no qualified schema name for collation +CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE + CREATE COLLATION coll (LOCALE="C") + CREATE TABLE t(a TEXT COLLATE coll); +\dO regress_schema_5.* + List of collations + Schema | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic? +------------------+------+----------+---------+-------+--------+-----------+---------------- + regress_schema_5 | coll | libc | C | C | | | yes +(1 row) + DROP SCHEMA regress_schema_2 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to type regress_schema_2.ss @@ -187,5 +222,13 @@ DETAIL: drop cascades to type regress_schema_3.ss drop cascades to type regress_schema_3.ss1 drop cascades to view regress_schema_3.test drop cascades to table regress_schema_3.t +DROP SCHEMA regress_schema_4 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to collation regress_schema_4.coll +drop cascades to table regress_schema_4.t +DROP SCHEMA regress_schema_5 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to collation regress_schema_5.coll +drop cascades to table regress_schema_5.t -- Clean up DROP ROLE regress_create_schema_role; diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index b6c54503d21..243d69e4d32 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -513,6 +513,13 @@ DROP TABLE test7; CREATE COLLATION testcoll_rulesx (provider = icu, locale = '', rules = '!!wrong!!'); +--CREATE SCHEMA CREATE COLLATION +CREATE SCHEMA regress_schema_4 + CREATE COLLATION coll_icu_und FROM "und-x-icu" + CREATE TABLE tts(a TEXT COLLATE coll_icu_und); +\dO regress_schema_4.* +DROP SCHEMA regress_schema_4 CASCADE; + -- nondeterministic collations diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql index 54a07054767..0f802bcaffe 100644 --- a/src/test/regress/sql/create_schema.sql +++ b/src/test/regress/sql/create_schema.sql @@ -104,8 +104,37 @@ CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE CREATE TABLE t(a ss1); \dD regress_schema_3.* +-- Cases where the schema creation with collations +--fail. can not CREATE COLLATION to other schema +CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE + CREATE COLLATION public.coll_icu_und FROM "und-x-icu"; + +--fail. improper qualified name +CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE + CREATE COLLATION postgres.public.coll_icu_und FROM "und-x-icu"; + +--fail. only support collation object for DefineStmt node +CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE + CREATE AGGREGATE balk(int4) (SFUNC = int4_sum(int8, int4), STYPE = int8, PARALLEL = SAFE, INITCOND = '0'); + +--ok, qualified schema name for collation should be same as the created schema +CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE + CREATE COLLATION regress_schema_4.coll (LOCALE="C") + CREATE TABLE t(a TEXT COLLATE regress_schema_4.coll); + +\dO regress_schema_4.* + +--ok, no qualified schema name for collation +CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE + CREATE COLLATION coll (LOCALE="C") + CREATE TABLE t(a TEXT COLLATE coll); +\dO regress_schema_5.* + + DROP SCHEMA regress_schema_2 CASCADE; DROP SCHEMA regress_schema_3 CASCADE; +DROP SCHEMA regress_schema_4 CASCADE; +DROP SCHEMA regress_schema_5 CASCADE; -- Clean up DROP ROLE regress_create_schema_role; -- 2.34.1
From 42c983e59f77197d99a584f7f7d63446fffa0752 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 10 Mar 2026 17:51:02 +0800 Subject: [PATCH v10 2/5] CREATE SCHEMA CREATE DOMAIN The SQL standard allows domains to be specified in a CREATE SCHEMA statement. This adds support for that capability. For example: CREATE SCHEMA schema_name AUTHORIZATION CURRENT_ROLE create domain ss as text not null; The domain will be created within the to be created schema. The domain name can be schema-qualified or database-qualified, however it's not allowed to let domain create within a different schema. Author: Kirill Reshke <[email protected]> Author: Jian He <[email protected]> Reviewed-by: Alvaro Herrera <[email protected]> Reviewed-by: Tom Lane <[email protected]> Discussion: https://postgr.es/m/caldssph4jusdswu3k58hjo60wntrr0duo4ckrcwa8evuosf...@mail.gmail.com Commitfest: https://commitfest.postgresql.org/patch/5985 --- doc/src/sgml/ref/create_schema.sgml | 4 +- src/backend/parser/gram.y | 1 + src/backend/parser/parse_utilcmd.c | 26 +++++++++ src/bin/psql/tab-complete.in.c | 12 ++-- .../expected/create_schema.out | 9 ++- .../test_ddl_deparse/sql/create_schema.sql | 5 +- src/test/regress/expected/create_schema.out | 56 +++++++++++++++++++ src/test/regress/sql/create_schema.sql | 36 ++++++++++++ 8 files changed, 139 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index 625793a6b67..79186d2b936 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -100,8 +100,8 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_sp <listitem> <para> An SQL statement defining an object to be created within the - schema. Currently, only <command>CREATE - TABLE</command>, <command>CREATE VIEW</command>, <command>CREATE + schema. Currently, only <command>CREATE DOMAIN</command> + <command>CREATE TABLE</command>, <command>CREATE VIEW</command>, <command>CREATE INDEX</command>, <command>CREATE SEQUENCE</command>, <command>CREATE TRIGGER</command> and <command>GRANT</command> are accepted as clauses within <command>CREATE SCHEMA</command>. Other kinds of objects may diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 9cbe8eafc45..1a9b3759376 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -1640,6 +1640,7 @@ schema_stmt: | CreateTrigStmt | GrantStmt | ViewStmt + | CreateDomainStmt ; diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 806ccc63145..9b4a209378d 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -4464,6 +4464,32 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts, elements = lappend(elements, element); break; + case T_CreateDomainStmt: + { + char *domain_schema = NULL; + + CreateDomainStmt *elp = castNode(CreateDomainStmt, element); + + /* + * The schema of the DOMAIN must match the schema being + * created. If the domain name length exceeds 3, it will + * fail in DeconstructQualifiedName. + */ + if (list_length(elp->domainname) == 2) + domain_schema = strVal(list_nth(elp->domainname, 0)); + else if (list_length(elp->domainname) == 3) + domain_schema = strVal(list_nth(elp->domainname, 1)); + + if (domain_schema != NULL && strcmp(domain_schema, schemaName) != 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_SCHEMA_DEFINITION), + errmsg("CREATE DOMAIN specifies a schema (%s) different from the one being created (%s)", + domain_schema, schemaName)); + + elements = lappend(elements, element); + } + break; + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(element)); diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 6484c6a3dd4..f62a4b28380 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2201,7 +2201,7 @@ match_previous_words(int pattern_id, { /* only some object types can be created as part of CREATE SCHEMA */ if (HeadMatches("CREATE", "SCHEMA")) - COMPLETE_WITH("TABLE", "VIEW", "INDEX", "SEQUENCE", "TRIGGER", + COMPLETE_WITH("TABLE", "VIEW", "INDEX", "SEQUENCE", "TRIGGER", "DOMAIN", /* for INDEX and TABLE/SEQUENCE, respectively */ "UNIQUE", "UNLOGGED"); else @@ -3492,15 +3492,15 @@ match_previous_words(int pattern_id, else if (Matches("CREATE", "DATABASE", MatchAny, "STRATEGY")) COMPLETE_WITH("WAL_LOG", "FILE_COPY"); - /* CREATE DOMAIN */ - else if (Matches("CREATE", "DOMAIN", MatchAny)) + /* CREATE DOMAIN --- is allowed inside CREATE SCHEMA, so use TailMatches */ + else if (TailMatches("CREATE", "DOMAIN", MatchAny)) COMPLETE_WITH("AS"); - else if (Matches("CREATE", "DOMAIN", MatchAny, "AS")) + else if (TailMatches("CREATE", "DOMAIN", MatchAny, "AS")) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes); - else if (Matches("CREATE", "DOMAIN", MatchAny, "AS", MatchAny)) + else if (TailMatches("CREATE", "DOMAIN", MatchAny, "AS", MatchAny)) COMPLETE_WITH("COLLATE", "DEFAULT", "CONSTRAINT", "NOT NULL", "NULL", "CHECK ("); - else if (Matches("CREATE", "DOMAIN", MatchAny, "COLLATE")) + else if (TailMatches("CREATE", "DOMAIN", MatchAny, "COLLATE")) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_collations); /* CREATE EXTENSION */ diff --git a/src/test/modules/test_ddl_deparse/expected/create_schema.out b/src/test/modules/test_ddl_deparse/expected/create_schema.out index 8ab4eb03385..634900d8456 100644 --- a/src/test/modules/test_ddl_deparse/expected/create_schema.out +++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out @@ -13,7 +13,14 @@ CREATE SCHEMA IF NOT EXISTS baz; NOTICE: schema "baz" already exists, skipping CREATE SCHEMA element_test CREATE TABLE foo (id int) - CREATE VIEW bar AS SELECT * FROM foo; + CREATE VIEW bar AS SELECT * FROM foo + CREATE DOMAIN d1 AS INT; NOTICE: DDL test: type simple, tag CREATE SCHEMA NOTICE: DDL test: type simple, tag CREATE TABLE NOTICE: DDL test: type simple, tag CREATE VIEW +NOTICE: DDL test: type simple, tag CREATE DOMAIN +DROP SCHEMA element_test CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table element_test.foo +drop cascades to view element_test.bar +drop cascades to type element_test.d1 diff --git a/src/test/modules/test_ddl_deparse/sql/create_schema.sql b/src/test/modules/test_ddl_deparse/sql/create_schema.sql index f314dc2b840..b822517b64d 100644 --- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql +++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql @@ -14,4 +14,7 @@ CREATE SCHEMA IF NOT EXISTS baz; CREATE SCHEMA element_test CREATE TABLE foo (id int) - CREATE VIEW bar AS SELECT * FROM foo; + CREATE VIEW bar AS SELECT * FROM foo + CREATE DOMAIN d1 AS INT; + +DROP SCHEMA element_test CASCADE; diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out index 38530c282a9..0533c29a311 100644 --- a/src/test/regress/expected/create_schema.out +++ b/src/test/regress/expected/create_schema.out @@ -131,5 +131,61 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE DROP SCHEMA regress_schema_1 CASCADE; NOTICE: drop cascades to table regress_schema_1.tab RESET ROLE; +-- Cases where the schema creation with domain. +--fail. cannot CREATE DOMAIN to other schema +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN public.ss AS TEXT NOT NULL DEFAULT 'hello' CONSTRAINT nn CHECK (VALUE <> 'hello') + CREATE TABLE t(a ss); +ERROR: CREATE DOMAIN specifies a schema (public) different from the one being created (regress_schema_2) +--fail. cannot CREATE DOMAIN to other schema +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN postgres.public.ss AS TEXT NOT NULL CONSTRAINT nn CHECK (VALUE <> 'hello') DEFAULT 'hello' + CREATE TABLE t(a ss); +ERROR: CREATE DOMAIN specifies a schema (public) different from the one being created (regress_schema_2) +--fail. improper qualified name +CREATE SCHEMA regress_schema_2 CREATE DOMAIN ss.postgres.regress_schema_2.ss AS TEXT; +ERROR: improper qualified name (too many dotted names): ss.postgres.regress_schema_2.ss +--fail. Execute subcommands in order; we do not implicitly reorder them. +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN ss1 AS ss + CREATE DOMAIN ss AS TEXT; +ERROR: type "ss" does not exist +LINE 2: CREATE DOMAIN ss1 AS ss + ^ +--ok, qualified schema name for domain should be same as the created schema. +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN regress_schema_2.ss AS TEXT NOT NULL CONSTRAINT nn CHECK (VALUE <> 'hello') DEFAULT 'hello' COLLATE "C" + CREATE TABLE t(a regress_schema_2.ss); +\dD regress_schema_2.* + List of domains + Schema | Name | Type | Collation | Nullable | Default | Check +------------------+------+------+-----------+----------+---------------+-------------------------------- + regress_schema_2 | ss | text | C | not null | 'hello'::text | CHECK (VALUE <> 'hello'::text) +(1 row) + +--ok, no qualified schema name for domain. +CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN ss AS TEXT CONSTRAINT nn CHECK (VALUE <> 'hello') NOT NULL DEFAULT 'hello' + CREATE DOMAIN ss1 AS ss + CREATE VIEW test AS SELECT 'hello'::ss AS test + CREATE TABLE t(a ss1); +\dD regress_schema_3.* + List of domains + Schema | Name | Type | Collation | Nullable | Default | Check +------------------+------+---------------------+-----------+----------+---------------+-------------------------------- + regress_schema_3 | ss | text | | not null | 'hello'::text | CHECK (VALUE <> 'hello'::text) + regress_schema_3 | ss1 | regress_schema_3.ss | | | 'hello'::text | +(2 rows) + +DROP SCHEMA regress_schema_2 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to type regress_schema_2.ss +drop cascades to table regress_schema_2.t +DROP SCHEMA regress_schema_3 CASCADE; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to type regress_schema_3.ss +drop cascades to type regress_schema_3.ss1 +drop cascades to view regress_schema_3.test +drop cascades to table regress_schema_3.t -- Clean up DROP ROLE regress_create_schema_role; diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql index b3dc1cfd758..54a07054767 100644 --- a/src/test/regress/sql/create_schema.sql +++ b/src/test/regress/sql/create_schema.sql @@ -71,5 +71,41 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE DROP SCHEMA regress_schema_1 CASCADE; RESET ROLE; +-- Cases where the schema creation with domain. +--fail. cannot CREATE DOMAIN to other schema +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN public.ss AS TEXT NOT NULL DEFAULT 'hello' CONSTRAINT nn CHECK (VALUE <> 'hello') + CREATE TABLE t(a ss); + +--fail. cannot CREATE DOMAIN to other schema +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN postgres.public.ss AS TEXT NOT NULL CONSTRAINT nn CHECK (VALUE <> 'hello') DEFAULT 'hello' + CREATE TABLE t(a ss); + +--fail. improper qualified name +CREATE SCHEMA regress_schema_2 CREATE DOMAIN ss.postgres.regress_schema_2.ss AS TEXT; + +--fail. Execute subcommands in order; we do not implicitly reorder them. +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN ss1 AS ss + CREATE DOMAIN ss AS TEXT; + +--ok, qualified schema name for domain should be same as the created schema. +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN regress_schema_2.ss AS TEXT NOT NULL CONSTRAINT nn CHECK (VALUE <> 'hello') DEFAULT 'hello' COLLATE "C" + CREATE TABLE t(a regress_schema_2.ss); +\dD regress_schema_2.* + +--ok, no qualified schema name for domain. +CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE + CREATE DOMAIN ss AS TEXT CONSTRAINT nn CHECK (VALUE <> 'hello') NOT NULL DEFAULT 'hello' + CREATE DOMAIN ss1 AS ss + CREATE VIEW test AS SELECT 'hello'::ss AS test + CREATE TABLE t(a ss1); +\dD regress_schema_3.* + +DROP SCHEMA regress_schema_2 CASCADE; +DROP SCHEMA regress_schema_3 CASCADE; + -- Clean up DROP ROLE regress_create_schema_role; -- 2.34.1
From c5ac517b14ef4fee2d67f0bf59d8eca1a35d9fab Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 9 Mar 2026 16:29:39 +0800 Subject: [PATCH v10 1/5] Don't try to re-order the subcommands of CREATE SCHEMA MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit transformCreateSchemaStmtElements has always believed that it is supposed to re-order the subcommands of CREATE SCHEMA into a safe execution order. However, it is nowhere near being capable of doing that correctly. Nor is there reason to think that it ever will be, or that that is a well-defined requirement, or that there's any basis in the SQL standard for it. Moreover, the problem will get worse as we add more subcommand types. Let's just drop the whole idea and execute the commands in the order given, which seems like a much less astonishment-prone definition anyway. Along the way, pass down a ParseState so that we can provide an error cursor for the "wrong schema name" error, and fix transformCreateSchemaStmtElements so that it doesn't scribble on the parsetree passed to it. Note: This will cause compatibility issue, for example: CREATE SCHEMA regress_schema_2 CREATE VIEW abcd_view AS SELECT a FROM abcd CREATE TABLE abcd (a int); With the patch, it will throw an error, whereas on HEAD it won’t. Author: Tom Lane <[email protected]> Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/ref/create_schema.sgml | 10 +- src/backend/commands/extension.c | 7 +- src/backend/commands/schemacmds.c | 15 ++- src/backend/parser/parse_utilcmd.c | 136 ++++++++------------ src/backend/tcop/utility.c | 3 +- src/include/commands/schemacmds.h | 7 +- src/include/parser/parse_utilcmd.h | 3 +- src/test/regress/expected/create_schema.out | 37 ++++++ src/test/regress/expected/create_view.out | 2 + src/test/regress/expected/event_trigger.out | 2 +- src/test/regress/expected/namespace.out | 7 +- src/test/regress/sql/create_schema.sql | 5 + src/test/regress/sql/namespace.sql | 8 +- src/tools/pgindent/typedefs.list | 1 - 14 files changed, 125 insertions(+), 118 deletions(-) diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index ed69298ccc6..625793a6b67 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -193,12 +193,10 @@ CREATE VIEW hollywood.winners AS </para> <para> - The SQL standard specifies that the subcommands in <command>CREATE - SCHEMA</command> can appear in any order. The present - <productname>PostgreSQL</productname> implementation does not - handle all cases of forward references in subcommands; it might - sometimes be necessary to reorder the subcommands in order to avoid - forward references. + <productname>PostgreSQL</productname> executes the subcommands + in <command>CREATE SCHEMA</command> in the order given. Other + implementations may try to rearrange the subcommands into dependency + order, but that is hard if not impossible to do correctly. </para> <para> diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c index 963618a64c4..7eed07df69f 100644 --- a/src/backend/commands/extension.c +++ b/src/backend/commands/extension.c @@ -1951,14 +1951,17 @@ CreateExtensionInternal(char *extensionName, if (!OidIsValid(schemaOid)) { + ParseState *pstate = make_parsestate(NULL); CreateSchemaStmt *csstmt = makeNode(CreateSchemaStmt); + pstate->p_sourcetext = "(generated CREATE SCHEMA command)"; + csstmt->schemaname = schemaName; csstmt->authrole = NULL; /* will be created by current user */ csstmt->schemaElts = NIL; csstmt->if_not_exists = false; - CreateSchemaCommand(csstmt, "(generated CREATE SCHEMA command)", - -1, -1); + + CreateSchemaCommand(pstate, csstmt, -1, -1); /* * CreateSchemaCommand includes CommandCounterIncrement, so new diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c index 4c51e920626..d52093c3d9d 100644 --- a/src/backend/commands/schemacmds.c +++ b/src/backend/commands/schemacmds.c @@ -49,7 +49,7 @@ static void AlterSchemaOwner_internal(HeapTuple tup, Relation rel, Oid newOwnerI * a subquery. */ Oid -CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString, +CreateSchemaCommand(ParseState *pstate, CreateSchemaStmt *stmt, int stmt_location, int stmt_len) { const char *schemaName = stmt->schemaname; @@ -189,12 +189,13 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString, /* * Examine the list of commands embedded in the CREATE SCHEMA command, and - * reorganize them into a sequentially executable order with no forward - * references. Note that the result is still a list of raw parsetrees --- - * we cannot, in general, run parse analysis on one statement until we - * have actually executed the prior ones. + * do preliminary transformations (mostly, verify that none are trying to + * create objects outside the new schema). Note that the result is still + * a list of raw parsetrees --- we cannot, in general, run parse analysis + * on one statement until we have actually executed the prior ones. */ - parsetree_list = transformCreateSchemaStmtElements(stmt->schemaElts, + parsetree_list = transformCreateSchemaStmtElements(pstate, + stmt->schemaElts, schemaName); /* @@ -219,7 +220,7 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString, /* do this step */ ProcessUtility(wrapper, - queryString, + pstate->p_sourcetext, false, PROCESS_UTILITY_SUBCOMMAND, NULL, diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index cc244c49e9e..806ccc63145 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -98,18 +98,6 @@ typedef struct bool ofType; /* true if statement contains OF typename */ } CreateStmtContext; -/* State shared by transformCreateSchemaStmtElements and its subroutines */ -typedef struct -{ - const char *schemaname; /* name of schema */ - List *sequences; /* CREATE SEQUENCE items */ - List *tables; /* CREATE TABLE items */ - List *views; /* CREATE VIEW items */ - List *indexes; /* CREATE INDEX items */ - List *triggers; /* CREATE TRIGGER items */ - List *grants; /* GRANT items */ -} CreateSchemaStmtContext; - static void transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column); @@ -136,7 +124,8 @@ static void transformCheckConstraints(CreateStmtContext *cxt, static void transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList); static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column); -static void setSchemaName(const char *context_schema, char **stmt_schema_name); +static void checkSchemaName(ParseState *pstate, const char *context_schema, + RangeVar *relation); static void transformPartitionCmd(CreateStmtContext *cxt, PartitionBoundSpec *bound); static List *transformPartitionRangeBounds(ParseState *pstate, List *blist, Relation parent); @@ -4394,51 +4383,35 @@ transformColumnType(CreateStmtContext *cxt, ColumnDef *column) * transformCreateSchemaStmtElements - * analyzes the elements of a CREATE SCHEMA statement * - * Split the schema element list from a CREATE SCHEMA statement into - * individual commands and place them in the result list in an order - * such that there are no forward references (e.g. GRANT to a table - * created later in the list). Note that the logic we use for determining - * forward references is presently quite incomplete. + * This is now somewhat vestigial: its only real responsibility is to complain + * if any of the elements are trying to create objects outside the new schema. + * We used to try to re-order the commands in a way that would work even if + * the user-written order would not, but that's too hard (perhaps impossible) + * to do correctly with not-yet-parse-analyzed commands. Now we'll just + * execute the elements in the order given. * * "schemaName" is the name of the schema that will be used for the creation - * of the objects listed, that may be compiled from the schema name defined + * of the objects listed. It may be obtained from the schema name defined * in the statement or a role specification. * - * SQL also allows constraints to make forward references, so thumb through - * the table columns and move forward references to a posterior alter-table - * command. - * * The result is a list of parse nodes that still need to be analyzed --- * but we can't analyze the later commands until we've executed the earlier * ones, because of possible inter-object references. - * - * Note: this breaks the rules a little bit by modifying schema-name fields - * within passed-in structs. However, the transformation would be the same - * if done over, so it should be all right to scribble on the input to this - * extent. */ List * -transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName) +transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts, + const char *schemaName) { - CreateSchemaStmtContext cxt; - List *result; - ListCell *elements; - - cxt.schemaname = schemaName; - cxt.sequences = NIL; - cxt.tables = NIL; - cxt.views = NIL; - cxt.indexes = NIL; - cxt.triggers = NIL; - cxt.grants = NIL; + List *elements = NIL; + ListCell *lc; /* - * Run through each schema element in the schema element list. Separate - * statements by type, and do preliminary analysis. + * Run through each schema element in the schema element list. Check + * target schema names, and collect the list of actions to be done. */ - foreach(elements, schemaElts) + foreach(lc, schemaElts) { - Node *element = lfirst(elements); + Node *element = lfirst(lc); switch (nodeTag(element)) { @@ -4446,8 +4419,8 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName) { CreateSeqStmt *elp = (CreateSeqStmt *) element; - setSchemaName(cxt.schemaname, &elp->sequence->schemaname); - cxt.sequences = lappend(cxt.sequences, element); + checkSchemaName(pstate, schemaName, elp->sequence); + elements = lappend(elements, element); } break; @@ -4455,12 +4428,8 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName) { CreateStmt *elp = (CreateStmt *) element; - setSchemaName(cxt.schemaname, &elp->relation->schemaname); - - /* - * XXX todo: deal with constraints - */ - cxt.tables = lappend(cxt.tables, element); + checkSchemaName(pstate, schemaName, elp->relation); + elements = lappend(elements, element); } break; @@ -4468,12 +4437,8 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName) { ViewStmt *elp = (ViewStmt *) element; - setSchemaName(cxt.schemaname, &elp->view->schemaname); - - /* - * XXX todo: deal with references between views - */ - cxt.views = lappend(cxt.views, element); + checkSchemaName(pstate, schemaName, elp->view); + elements = lappend(elements, element); } break; @@ -4481,8 +4446,8 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName) { IndexStmt *elp = (IndexStmt *) element; - setSchemaName(cxt.schemaname, &elp->relation->schemaname); - cxt.indexes = lappend(cxt.indexes, element); + checkSchemaName(pstate, schemaName, elp->relation); + elements = lappend(elements, element); } break; @@ -4490,13 +4455,13 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName) { CreateTrigStmt *elp = (CreateTrigStmt *) element; - setSchemaName(cxt.schemaname, &elp->relation->schemaname); - cxt.triggers = lappend(cxt.triggers, element); + checkSchemaName(pstate, schemaName, elp->relation); + elements = lappend(elements, element); } break; case T_GrantStmt: - cxt.grants = lappend(cxt.grants, element); + elements = lappend(elements, element); break; default: @@ -4505,32 +4470,39 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName) } } - result = NIL; - result = list_concat(result, cxt.sequences); - result = list_concat(result, cxt.tables); - result = list_concat(result, cxt.views); - result = list_concat(result, cxt.indexes); - result = list_concat(result, cxt.triggers); - result = list_concat(result, cxt.grants); - - return result; + return elements; } /* - * setSchemaName - * Set or check schema name in an element of a CREATE SCHEMA command + * checkSchemaName + * Check schema name in an element of a CREATE SCHEMA command + * + * It's okay if the command doesn't specify a target schema name, because + * CreateSchemaCommand will set up the default creation schema to be the + * new schema. But if a target schema name is given, it had better match. + * We also have to check that the command doesn't say CREATE TEMP, since + * that would likewise put the object into the wrong schema. */ static void -setSchemaName(const char *context_schema, char **stmt_schema_name) +checkSchemaName(ParseState *pstate, const char *context_schema, + RangeVar *relation) { - if (*stmt_schema_name == NULL) - *stmt_schema_name = unconstify(char *, context_schema); - else if (strcmp(context_schema, *stmt_schema_name) != 0) + if (relation->schemaname != NULL && + strcmp(context_schema, relation->schemaname) != 0) ereport(ERROR, - (errcode(ERRCODE_INVALID_SCHEMA_DEFINITION), - errmsg("CREATE specifies a schema (%s) " - "different from the one being created (%s)", - *stmt_schema_name, context_schema))); + errcode(ERRCODE_INVALID_SCHEMA_DEFINITION), + errmsg("CREATE specifies a schema (%s) different from the one being created (%s)", + relation->schemaname, context_schema), + parser_errposition(pstate, relation->location)); + + if (relation->relpersistence == RELPERSISTENCE_TEMP) + { + /* spell this error the same as in RangeVarAdjustRelationPersistence */ + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot create temporary relation in non-temporary schema"), + parser_errposition(pstate, relation->location)); + } } /* diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index bf707f2d57f..8603bb2e58c 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -1119,8 +1119,7 @@ ProcessUtilitySlow(ParseState *pstate, * relation and attribute manipulation */ case T_CreateSchemaStmt: - CreateSchemaCommand((CreateSchemaStmt *) parsetree, - queryString, + CreateSchemaCommand(pstate, castNode(CreateSchemaStmt, parsetree), pstmt->stmt_location, pstmt->stmt_len); diff --git a/src/include/commands/schemacmds.h b/src/include/commands/schemacmds.h index d100e4691b3..559727be5de 100644 --- a/src/include/commands/schemacmds.h +++ b/src/include/commands/schemacmds.h @@ -16,12 +16,9 @@ #define SCHEMACMDS_H #include "catalog/objectaddress.h" -#include "nodes/parsenodes.h" - -extern Oid CreateSchemaCommand(CreateSchemaStmt *stmt, - const char *queryString, - int stmt_location, int stmt_len); +#include "parser/parse_node.h" +extern Oid CreateSchemaCommand(ParseState *pstate, CreateSchemaStmt *stmt, int stmt_location, int stmt_len); extern ObjectAddress RenameSchema(const char *oldname, const char *newname); extern ObjectAddress AlterSchemaOwner(const char *name, Oid newOwnerId); extern void AlterSchemaOwner_oid(Oid schemaoid, Oid newOwnerId); diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h index 53f282f77ec..34c98e5122f 100644 --- a/src/include/parser/parse_utilcmd.h +++ b/src/include/parser/parse_utilcmd.h @@ -30,7 +30,8 @@ extern CreateStatsStmt *transformStatsStmt(Oid relid, CreateStatsStmt *stmt, const char *queryString); extern void transformRuleStmt(RuleStmt *stmt, const char *queryString, List **actions, Node **whereClause); -extern List *transformCreateSchemaStmtElements(List *schemaElts, +extern List *transformCreateSchemaStmtElements(ParseState *pstate, + List *schemaElts, const char *schemaName); extern PartitionBoundSpec *transformPartitionBound(ParseState *pstate, Relation parent, PartitionBoundSpec *spec); diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out index 93302a07efc..38530c282a9 100644 --- a/src/test/regress/expected/create_schema.out +++ b/src/test/regress/expected/create_schema.out @@ -9,55 +9,92 @@ CREATE ROLE regress_create_schema_role SUPERUSER; CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE SEQUENCE schema_not_existing.seq; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE SEQUENCE schema_not_existing.seq; + ^ CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE TABLE schema_not_existing.tab (id int); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE TABLE schema_not_existing.tab (id int); + ^ CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE VIEW schema_not_existing.view AS SELECT 1; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE VIEW schema_not_existing.view AS SELECT 1; + ^ CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE INDEX ON schema_not_existing.tab (id); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE INDEX ON schema_not_existing.tab (id); + ^ CREATE SCHEMA AUTHORIZATION regress_create_schema_role CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab EXECUTE FUNCTION schema_trig.no_func(); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_exi... + ^ -- Again, with a role specification and no schema names. SET ROLE regress_create_schema_role; CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE SEQUENCE schema_not_existing.seq; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE SEQUENCE schema_not_existing.seq; + ^ CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE TABLE schema_not_existing.tab (id int); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE TABLE schema_not_existing.tab (id int); + ^ CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE VIEW schema_not_existing.view AS SELECT 1; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE VIEW schema_not_existing.view AS SELECT 1; + ^ CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE INDEX ON schema_not_existing.tab (id); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE INDEX ON schema_not_existing.tab (id); + ^ CREATE SCHEMA AUTHORIZATION CURRENT_ROLE CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab EXECUTE FUNCTION schema_trig.no_func(); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_create_schema_role) +LINE 2: CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_exi... + ^ -- Again, with a schema name and a role specification. CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE SEQUENCE schema_not_existing.seq; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) +LINE 2: CREATE SEQUENCE schema_not_existing.seq; + ^ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE TABLE schema_not_existing.tab (id int); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) +LINE 2: CREATE TABLE schema_not_existing.tab (id int); + ^ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE VIEW schema_not_existing.view AS SELECT 1; ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) +LINE 2: CREATE VIEW schema_not_existing.view AS SELECT 1; + ^ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE INDEX ON schema_not_existing.tab (id); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) +LINE 2: CREATE INDEX ON schema_not_existing.tab (id); + ^ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab EXECUTE FUNCTION schema_trig.no_func(); ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1) +LINE 2: CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_exi... + ^ RESET ROLE; +--error. Forward reference is not allowed. +CREATE SCHEMA regress_schema_2 + CREATE VIEW abcd_view AS SELECT a FROM abcd + CREATE TABLE abcd (a int); +ERROR: relation "abcd" does not exist +LINE 2: CREATE VIEW abcd_view AS SELECT a FROM abcd + ^ -- Cases where the schema creation succeeds. -- The schema created matches the role name. CREATE SCHEMA AUTHORIZATION regress_create_schema_role diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index bc4f79938b3..63cf4b4371d 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -128,6 +128,8 @@ ERROR: cannot create temporary relation in non-temporary schema CREATE SCHEMA test_view_schema CREATE TEMP VIEW testview AS SELECT 1; ERROR: cannot create temporary relation in non-temporary schema +LINE 2: CREATE TEMP VIEW testview AS SELECT 1; + ^ -- joins: if any of the join relations are temporary, the view -- should also be temporary -- should be non-temp diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out index f897b079e67..4c32e1dcaf5 100644 --- a/src/test/regress/expected/event_trigger.out +++ b/src/test/regress/expected/event_trigger.out @@ -425,13 +425,13 @@ NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.one NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_pkey NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_a_seq NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_c_seq +NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.two NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.id_col_d_seq NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.id NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view -NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx -- View with column additions CREATE OR REPLACE VIEW evttrig.one_view AS SELECT * FROM evttrig.two, evttrig.id; NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out index dbbda72d395..d02f3fd67d7 100644 --- a/src/test/regress/expected/namespace.out +++ b/src/test/regress/expected/namespace.out @@ -10,13 +10,10 @@ SELECT pg_catalog.set_config('search_path', ' ', false); (1 row) CREATE SCHEMA test_ns_schema_1 + CREATE TABLE abc (a serial, b int UNIQUE) CREATE UNIQUE INDEX abc_a_idx ON abc (a) CREATE VIEW abc_view AS - SELECT a+1 AS a, b+1 AS b FROM abc - CREATE TABLE abc ( - a serial, - b int UNIQUE - ); + SELECT a+1 AS a, b+1 AS b FROM abc; -- verify that the correct search_path restored on abort SET search_path to public; BEGIN; diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql index 1b7064247a1..b3dc1cfd758 100644 --- a/src/test/regress/sql/create_schema.sql +++ b/src/test/regress/sql/create_schema.sql @@ -47,6 +47,11 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE EXECUTE FUNCTION schema_trig.no_func(); RESET ROLE; +--error. Forward reference is not allowed. +CREATE SCHEMA regress_schema_2 + CREATE VIEW abcd_view AS SELECT a FROM abcd + CREATE TABLE abcd (a int); + -- Cases where the schema creation succeeds. -- The schema created matches the role name. CREATE SCHEMA AUTHORIZATION regress_create_schema_role diff --git a/src/test/regress/sql/namespace.sql b/src/test/regress/sql/namespace.sql index 306cdc2d8c6..9433eb3c15c 100644 --- a/src/test/regress/sql/namespace.sql +++ b/src/test/regress/sql/namespace.sql @@ -7,15 +7,11 @@ SELECT pg_catalog.set_config('search_path', ' ', false); CREATE SCHEMA test_ns_schema_1 + CREATE TABLE abc (a serial, b int UNIQUE) CREATE UNIQUE INDEX abc_a_idx ON abc (a) CREATE VIEW abc_view AS - SELECT a+1 AS a, b+1 AS b FROM abc - - CREATE TABLE abc ( - a serial, - b int UNIQUE - ); + SELECT a+1 AS a, b+1 AS b FROM abc; -- verify that the correct search_path restored on abort SET search_path to public; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 3250564d4ff..370c83be5d3 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -572,7 +572,6 @@ CreateRangeStmt CreateReplicationSlotCmd CreateRoleStmt CreateSchemaStmt -CreateSchemaStmtContext CreateSeqStmt CreateStatsStmt CreateStmt -- 2.34.1
From 981fa017d6d71921586e09bf49f208e2667497f0 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 10 Mar 2026 22:03:40 +0800 Subject: [PATCH v10 5/5] CREATE SCHEMA foreign key executed at the end For CREATE SCHEMA, all subcommands are executed in the order they are specified. However, to support foreign key cross referencing, we need transform FOREIGN KEY clause into ALTER TABLE ... ADD FOREIGN KEY command and append them to the end of the CREATE SCHEMA execution queue. All CREATE TABLE commands are executed first, so subsequent ALTER TABLE ... ADD FOREIGN KEY statements will not encounter any conflicts. Discussion: https://postgr.es/m/[email protected] Commitfest: https://commitfest.postgresql.org/patch/5985 --- src/backend/commands/schemacmds.c | 130 ++++++++++++++++++ src/backend/parser/parse_utilcmd.c | 36 +++-- src/include/parser/parse_utilcmd.h | 1 + .../expected/create_schema.out | 25 ++++ .../test_ddl_deparse/sql/create_schema.sql | 11 ++ src/test/regress/expected/create_schema.out | 83 +++++++++++ src/test/regress/expected/event_trigger.out | 2 +- src/test/regress/sql/create_schema.sql | 36 +++++ 8 files changed, 304 insertions(+), 20 deletions(-) diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c index d52093c3d9d..621f3806772 100644 --- a/src/backend/commands/schemacmds.c +++ b/src/backend/commands/schemacmds.c @@ -198,6 +198,136 @@ CreateSchemaCommand(ParseState *pstate, CreateSchemaStmt *stmt, stmt->schemaElts, schemaName); + /* + * Reorder the list of commands embedded in the CREATE SCHEMA statement. + * We only do this for foreign keys. Foreign key constraints are + * transformed into ALTER TABLE ... ADD FOREIGN KEY commands and moved to + * the end of the CREATE SCHEMA to-do list (parsetree_list). + * + * At the same time, the original foreign key Constraint node must removed + * from CreateStmt, since they have already been converted into + * AlterTableCmds. No need to do parse analysis for the original foreign + * key constraint nodes: because foreign key constraint only allow plain + * column references. + * + * See also transformFKConstraints and transformConstraintAttrs. + */ + foreach(parsetree_item, parsetree_list) + { + ListCell *elements; + CreateStmt *csstmt; + + Node *stmt = (Node *) lfirst(parsetree_item); + + if (!IsA(stmt, CreateStmt)) + continue; + + csstmt = castNode(CreateStmt, stmt); + + foreach(elements, csstmt->tableElts) + { + AlterTableStmt *alterstmt; + AlterTableCmd *altercmd; + + Node *element = lfirst(elements); + + if (IsA(element, Constraint)) + { + Constraint *constr = castNode(Constraint, element); + + if (constr->contype != CONSTR_FOREIGN) + continue; + + alterstmt = makeNode(AlterTableStmt); + alterstmt->relation = copyObject(csstmt->relation); + alterstmt->cmds = NIL; + alterstmt->objtype = OBJECT_TABLE; + + altercmd = makeNode(AlterTableCmd); + altercmd->subtype = AT_AddConstraint; + altercmd->name = NULL; + altercmd->def = (Node *) copyObject(constr); + + alterstmt->cmds = lappend(alterstmt->cmds, altercmd); + + csstmt->tableElts = foreach_delete_current(csstmt->tableElts, + elements); + + parsetree_list = lappend(parsetree_list, alterstmt); + } + else if (IsA(element, ColumnDef)) + { + ColumnDef *entry = castNode(ColumnDef, element); + + transformConstraintAttrs(pstate, entry->constraints); + + for (int constrpos = 0; constrpos < list_length(entry->constraints); constrpos++) + { + Constraint *colconstr = list_nth_node(Constraint, + entry->constraints, + constrpos); + + if (colconstr->contype != CONSTR_FOREIGN) + continue; + + alterstmt = makeNode(AlterTableStmt); + altercmd = makeNode(AlterTableCmd); + + colconstr->fk_attrs = list_make1(makeString(entry->colname)); + + alterstmt->relation = copyObject(csstmt->relation); + alterstmt->cmds = NIL; + alterstmt->objtype = OBJECT_TABLE; + + altercmd->subtype = AT_AddConstraint; + altercmd->name = NULL; + altercmd->def = (Node *) copyObject(colconstr); + alterstmt->cmds = lappend(alterstmt->cmds, altercmd); + + parsetree_list = lappend(parsetree_list, alterstmt); + + /* + * Column constraints separate the Constraint node from + * its attributes; a full column-level foreign key + * constraint may be represented by multiple Constraint + * nodes. After transformConstraintAttrs, the main foreign + * key Constraint node already contains all required + * information. Therefore, when the foreign key Constraint + * node is removed, the associated attribute (e.g. NOT + * ENFORCED) nodes (which are also Constraint nodes) must + * be removed as well. + */ + for (int restpos = constrpos + 1; restpos < list_length(entry->constraints);) + { + Constraint *nextcolconstr = list_nth_node(Constraint, + entry->constraints, + restpos); + + if (nextcolconstr->contype == CONSTR_ATTR_DEFERRABLE || + nextcolconstr->contype == CONSTR_ATTR_NOT_DEFERRABLE || + nextcolconstr->contype == CONSTR_ATTR_DEFERRED || + nextcolconstr->contype == CONSTR_ATTR_IMMEDIATE || + nextcolconstr->contype == CONSTR_ATTR_NOT_ENFORCED) + { + entry->constraints = list_delete_nth_cell(entry->constraints, + restpos); + } + else + break; + } + + entry->constraints = list_delete_nth_cell(entry->constraints, constrpos); + + /* + * We have already removed one FK Constraint node, update + * constrpos now. + */ + constrpos = constrpos - 1; + } + } + } + } + /* * Execute each command contained in the CREATE SCHEMA. Since the grammar * allows only utility commands in CREATE SCHEMA, there is no need to pass diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index b6ee8342fde..8eadfb65523 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -122,8 +122,6 @@ static void transformFKConstraints(CreateStmtContext *cxt, bool isAddConstraint); static void transformCheckConstraints(CreateStmtContext *cxt, bool skipValidation); -static void transformConstraintAttrs(CreateStmtContext *cxt, - List *constraintList); static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column); static void checkSchemaName(ParseState *pstate, const char *context_schema, RangeVar *relation); @@ -693,7 +691,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) } /* Process column constraints, if any... */ - transformConstraintAttrs(cxt, column->constraints); + transformConstraintAttrs(cxt->pstate, column->constraints); /* * First, scan the column's constraints to see if a not-null constraint @@ -4195,8 +4193,8 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, * EXCLUSION, and PRIMARY KEY constraints, but someday they ought to be * supported for other constraint types. */ -static void -transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) +void +transformConstraintAttrs(ParseState *pstate, List *constraintList) { Constraint *lastprimarycon = NULL; bool saw_deferrability = false; @@ -4225,12 +4223,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced DEFERRABLE clause"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); if (saw_deferrability) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); saw_deferrability = true; lastprimarycon->deferrable = true; break; @@ -4240,12 +4238,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced NOT DEFERRABLE clause"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); if (saw_deferrability) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); saw_deferrability = true; lastprimarycon->deferrable = false; if (saw_initially && @@ -4253,7 +4251,7 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); break; case CONSTR_ATTR_DEFERRED: @@ -4261,12 +4259,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced INITIALLY DEFERRED clause"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); if (saw_initially) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple INITIALLY IMMEDIATE/DEFERRED clauses not allowed"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); saw_initially = true; lastprimarycon->initdeferred = true; @@ -4279,7 +4277,7 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); break; case CONSTR_ATTR_IMMEDIATE: @@ -4287,12 +4285,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced INITIALLY IMMEDIATE clause"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); if (saw_initially) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple INITIALLY IMMEDIATE/DEFERRED clauses not allowed"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); saw_initially = true; lastprimarycon->initdeferred = false; break; @@ -4304,12 +4302,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced ENFORCED clause"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); if (saw_enforced) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple ENFORCED/NOT ENFORCED clauses not allowed"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); saw_enforced = true; lastprimarycon->is_enforced = true; break; @@ -4321,12 +4319,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced NOT ENFORCED clause"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); if (saw_enforced) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple ENFORCED/NOT ENFORCED clauses not allowed"), - parser_errposition(cxt->pstate, con->location))); + parser_errposition(pstate, con->location))); saw_enforced = true; lastprimarycon->is_enforced = false; diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h index 34c98e5122f..9afcd91c5db 100644 --- a/src/include/parser/parse_utilcmd.h +++ b/src/include/parser/parse_utilcmd.h @@ -20,6 +20,7 @@ typedef struct AttrMap AttrMap; /* avoid including attmap.h here */ extern List *transformCreateStmt(CreateStmt *stmt, const char *queryString); +extern void transformConstraintAttrs(ParseState *pstate, List *constraintList); extern AlterTableStmt *transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, const char *queryString, List **beforeStmts, diff --git a/src/test/modules/test_ddl_deparse/expected/create_schema.out b/src/test/modules/test_ddl_deparse/expected/create_schema.out index 92da27c813b..89b571fc4c9 100644 --- a/src/test/modules/test_ddl_deparse/expected/create_schema.out +++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out @@ -39,3 +39,28 @@ drop cascades to type element_test.floatrange drop cascades to type element_test.ss drop cascades to type element_test.sss drop cascades to type element_test.rainbow +CREATE SCHEMA regress_schema_1 +CREATE TABLE t4( + b INT, + a INT REFERENCES t5 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + REFERENCES t6 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED, + CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED) +CREATE TABLE t5 (a INT, b INT, PRIMARY KEY (a)) +CREATE TABLE t6 (a INT, b INT, PRIMARY KEY (a)); +NOTICE: DDL test: type simple, tag CREATE SCHEMA +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE INDEX +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE INDEX +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint t4_a_fkey on table regress_schema_1.t4 +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint t4_a_fkey1 on table regress_schema_1.t4 +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint fk on table regress_schema_1.t4 +DROP SCHEMA regress_schema_1 CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table regress_schema_1.t4 +drop cascades to table regress_schema_1.t5 +drop cascades to table regress_schema_1.t6 diff --git a/src/test/modules/test_ddl_deparse/sql/create_schema.sql b/src/test/modules/test_ddl_deparse/sql/create_schema.sql index abe0637c415..411b01bb640 100644 --- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql +++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql @@ -23,3 +23,14 @@ CREATE SCHEMA element_test CREATE TYPE rainbow AS ENUM ('red', 'orange'); DROP SCHEMA element_test CASCADE; + +CREATE SCHEMA regress_schema_1 +CREATE TABLE t4( + b INT, + a INT REFERENCES t5 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + REFERENCES t6 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED, + CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED) +CREATE TABLE t5 (a INT, b INT, PRIMARY KEY (a)) +CREATE TABLE t6 (a INT, b INT, PRIMARY KEY (a)); + +DROP SCHEMA regress_schema_1 CASCADE; diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out index a33cbd0c0d9..6df41f9a2f3 100644 --- a/src/test/regress/expected/create_schema.out +++ b/src/test/regress/expected/create_schema.out @@ -282,6 +282,73 @@ CREATE SCHEMA regress_schema_7 regress_schema_7 | regress_schema_7.sss | (5 rows) +CREATE SCHEMA regress_schema_8 + CREATE TABLE regress_schema_8.t2 ( + b int, + a int REFERENCES t1 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + REFERENCES t3 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED, + CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED) + CREATE TABLE regress_schema_8.t1 (a int PRIMARY KEY) + CREATE TABLE t3 (a int PRIMARY KEY) + CREATE TABLE t4(b int, + a int REFERENCES t5 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + REFERENCES t6 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED, + CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED) + CREATE TABLE t5 (a int, b int, PRIMARY KEY (a)) + CREATE TABLE t6 (a int, b int, PRIMARY KEY (a)); +\d regress_schema_8.t2 + Table "regress_schema_8.t2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + b | integer | | | + a | integer | | | +Foreign-key constraints: + "fk" FOREIGN KEY (a) REFERENCES regress_schema_8.t1(a) DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + "t2_a_fkey" FOREIGN KEY (a) REFERENCES regress_schema_8.t1(a) DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + "t2_a_fkey1" FOREIGN KEY (a) REFERENCES regress_schema_8.t3(a) DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + +\d regress_schema_8.t4 + Table "regress_schema_8.t4" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + b | integer | | | + a | integer | | | +Foreign-key constraints: + "fk" FOREIGN KEY (a) REFERENCES regress_schema_8.t6(a) DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + "t4_a_fkey" FOREIGN KEY (a) REFERENCES regress_schema_8.t5(a) DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + "t4_a_fkey1" FOREIGN KEY (a) REFERENCES regress_schema_8.t6(a) DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + +CREATE SCHEMA regress_schema_9 + CREATE TABLE t2 (a int, b int, + CONSTRAINT fk FOREIGN KEY (a, b) REFERENCES t1 MATCH FULL NOT DEFERRABLE INITIALLY IMMEDIATE NOT ENFORCED, + CONSTRAINT fk1 FOREIGN KEY (a, b) REFERENCES t3 MATCH FULL DEFERRABLE INITIALLY IMMEDIATE NOT ENFORCED) + CREATE TABLE t1 (a int, b int, PRIMARY KEY (b, a)) + CREATE TABLE t3 (a int, b int, PRIMARY KEY (b, a)) + CREATE TABLE t4 (a int, b int, + CONSTRAINT fk FOREIGN KEY (b) REFERENCES t6 MATCH FULL NOT DEFERRABLE INITIALLY IMMEDIATE NOT ENFORCED, + CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t5 MATCH FULL DEFERRABLE INITIALLY IMMEDIATE NOT ENFORCED) + CREATE TABLE t5 (a int, b int, PRIMARY KEY (a)) + CREATE TABLE t6 (a int, b int, PRIMARY KEY (b)); +\d regress_schema_9.t2 + Table "regress_schema_9.t2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | +Foreign-key constraints: + "fk" FOREIGN KEY (a, b) REFERENCES regress_schema_9.t1(b, a) MATCH FULL NOT ENFORCED + "fk1" FOREIGN KEY (a, b) REFERENCES regress_schema_9.t3(b, a) MATCH FULL DEFERRABLE NOT ENFORCED + +\d regress_schema_9.t4 + Table "regress_schema_9.t4" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | +Foreign-key constraints: + "fk" FOREIGN KEY (b) REFERENCES regress_schema_9.t6(b) MATCH FULL NOT ENFORCED + "fk1" FOREIGN KEY (a) REFERENCES regress_schema_9.t5(a) MATCH FULL DEFERRABLE NOT ENFORCED + DROP SCHEMA regress_schema_2 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to type regress_schema_2.ss @@ -313,5 +380,21 @@ drop cascades to type regress_schema_7.ss drop cascades to type regress_schema_7.sss drop cascades to type regress_schema_7.rainbow drop cascades to table regress_schema_7.t +DROP SCHEMA regress_schema_8 CASCADE; +NOTICE: drop cascades to 6 other objects +DETAIL: drop cascades to table regress_schema_8.t2 +drop cascades to table regress_schema_8.t1 +drop cascades to table regress_schema_8.t3 +drop cascades to table regress_schema_8.t4 +drop cascades to table regress_schema_8.t5 +drop cascades to table regress_schema_8.t6 +DROP SCHEMA regress_schema_9 CASCADE; +NOTICE: drop cascades to 6 other objects +DETAIL: drop cascades to table regress_schema_9.t2 +drop cascades to table regress_schema_9.t1 +drop cascades to table regress_schema_9.t3 +drop cascades to table regress_schema_9.t4 +drop cascades to table regress_schema_9.t5 +drop cascades to table regress_schema_9.t6 -- Clean up DROP ROLE regress_create_schema_role; diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out index 4c32e1dcaf5..065f586310f 100644 --- a/src/test/regress/expected/event_trigger.out +++ b/src/test/regress/expected/event_trigger.out @@ -427,11 +427,11 @@ NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_ NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_c_seq NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.two -NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.id_col_d_seq NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.id NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view +NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two -- View with column additions CREATE OR REPLACE VIEW evttrig.one_view AS SELECT * FROM evttrig.two, evttrig.id; NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql index 75fd928eacb..547016f54cd 100644 --- a/src/test/regress/sql/create_schema.sql +++ b/src/test/regress/sql/create_schema.sql @@ -168,12 +168,48 @@ CREATE SCHEMA regress_schema_7 CREATE TABLE t(a floatrange, b ss, c rainbow); \dT regress_schema_7.* +CREATE SCHEMA regress_schema_8 + CREATE TABLE regress_schema_8.t2 ( + b int, + a int REFERENCES t1 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + REFERENCES t3 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED, + CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED) + CREATE TABLE regress_schema_8.t1 (a int PRIMARY KEY) + CREATE TABLE t3 (a int PRIMARY KEY) + CREATE TABLE t4(b int, + a int REFERENCES t5 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED + REFERENCES t6 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED, + CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED) + CREATE TABLE t5 (a int, b int, PRIMARY KEY (a)) + CREATE TABLE t6 (a int, b int, PRIMARY KEY (a)); + +\d regress_schema_8.t2 +\d regress_schema_8.t4 + +CREATE SCHEMA regress_schema_9 + CREATE TABLE t2 (a int, b int, + CONSTRAINT fk FOREIGN KEY (a, b) REFERENCES t1 MATCH FULL NOT DEFERRABLE INITIALLY IMMEDIATE NOT ENFORCED, + CONSTRAINT fk1 FOREIGN KEY (a, b) REFERENCES t3 MATCH FULL DEFERRABLE INITIALLY IMMEDIATE NOT ENFORCED) + CREATE TABLE t1 (a int, b int, PRIMARY KEY (b, a)) + CREATE TABLE t3 (a int, b int, PRIMARY KEY (b, a)) + CREATE TABLE t4 (a int, b int, + CONSTRAINT fk FOREIGN KEY (b) REFERENCES t6 MATCH FULL NOT DEFERRABLE INITIALLY IMMEDIATE NOT ENFORCED, + CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t5 MATCH FULL DEFERRABLE INITIALLY IMMEDIATE NOT ENFORCED) + CREATE TABLE t5 (a int, b int, PRIMARY KEY (a)) + CREATE TABLE t6 (a int, b int, PRIMARY KEY (b)); + +\d regress_schema_9.t2 +\d regress_schema_9.t4 + + DROP SCHEMA regress_schema_2 CASCADE; DROP SCHEMA regress_schema_3 CASCADE; DROP SCHEMA regress_schema_4 CASCADE; DROP SCHEMA regress_schema_5 CASCADE; DROP SCHEMA regress_schema_6 CASCADE; DROP SCHEMA regress_schema_7 CASCADE; +DROP SCHEMA regress_schema_8 CASCADE; +DROP SCHEMA regress_schema_9 CASCADE; -- Clean up DROP ROLE regress_create_schema_role; -- 2.34.1
