On Thu, Dec 12, 2024 at 1:08 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> The problem is not too awful right now, because of the very limited
> set of object types that CREATE SCHEMA supports. The only case
> I can think of offhand is a table referencing a view's rowtype,
> for example
>
> create schema s1
> create view v1 as select ...
> create table t1 (compositecol v1, ...);
>
> Since transformCreateSchemaStmtElements re-orders views after
> tables, this'll fail, and there is no way to fix that except
> by giving up use of the elements-in-CREATE-SCHEMA feature.
> Admittedly it's a strange usage, and probably no one has tried it.
>
> However, once we start adding in data types and functions,
> the hazard grows substantially, because there are more usage
> patterns and they can't all be satisfied by a simple object-type
> ordering. For example, domains are already enough to cause
> trouble, because we allow domains over composites:
>
> create schema s1
> create table t1 (...)
> create domain d1 as t1 check(...);
>
> Re-ordering domains before tables would break this case, but
> the other order has other problems. Looking a bit further
> down the road, how would you handle creation of a base type
> within CREATE SCHEMA?
>
> create schema s1
> create type myscalar
> create function myscalar_in(cstring) returns myscalar ...
> create function myscalar_out(myscalar) returns cstring ...
> create type myscalar (input = myscalar_in, ...);
>
> This cannot possibly work if an object-type-based re-ordering
> is done to it.
>
> So IMV, we have three possibilities:
>
> 1. CREATE SCHEMA's schema-element feature remains forevermore
> a sad joke that (a) doesn't cover nearly enough to be useful and
> (b) doesn't come close to doing what the spec says it should.
>
> 2. We invest an enormous amount of engineering effort on trying
> to extract dependencies from not-yet-analyzed parse trees, after
> which we invest a bunch more effort figuring out heuristics for
> ordering the subcommands in the face of circular dependencies.
> (Some of that could be stolen from pg_dump, but not all: pg_dump
> only has to resolve a limited set of cases.)
>
> 3. We bypass the need for #2 by decreeing that we'll execute
> the subcommands in order.
>
>
> >> PS: if we were really excited about allowing circular FKs to be
> >> made within CREATE SCHEMA, a possible though non-standard answer
> >> would be to allow ALTER TABLE ADD CONSTRAINT as a <schema element>.
>
> > That's a nice feature to have by itself?
>
> Not unless we abandon the idea of subcommand reordering, because
> where are you going to put the ALTER TABLE subcommands?
>
hi.
move this forward with option #3 (executing the subcommands in order).
pg_dump don't use CREATE SCHEMA ...CREATE ...
so if we error out
CREATE SCHEMA regress_schema_2 CREATE VIEW abcd_view AS SELECT a FROM
abcd CREATE TABLE abcd (a int);
it won't be a big compatibility issue?
Also this thread doesn’t show strong support for sorting the subcommands.
the full <schema definition> in 11.1 is:
11.1 <schema definition>
<schema element> ::=
<table definition>
| <view definition>
| <domain definition>
| <character set definition>
| <collation definition>
| <transliteration definition>
| <assertion definition>
| <trigger definition>
| <user-defined type definition>
| <user-defined cast definition>
| <user-defined ordering definition>
| <transform definition>
| <schema routine>
| <sequence generator definition>
| <grant statement>
| <role definition>
so I also add support for CREATE SCHEMA CREATE COLLATION.
v6-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch
v6-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
v6-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
v6-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch
is refactor/rebase based on
v1-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-S.patch
v6-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
for CREATE SCHEMA ... CREATE-DOMAIN
v6-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
for CREATE SCHEMA ... CREATE-COLLATION
From 162ca0e712a3bbe16193275104e7c52739633503 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Mon, 18 Aug 2025 16:28:37 +0800
Subject: [PATCH v6 2/3] CREATE SCHEMA CREATE DOMAIN
SQL standard allow domain to be specified with CREATE SCHEMA
statement. This patch adds support in PostgreSQL for that.
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 <reshkekir...@gmail.com>
Author: Jian He <jian.universal...@gmail.com>
Reviewed-by: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Reviewed-by: Tom Lane <t...@sss.pgh.pa.us>
Discussion: https://postgr.es/m/caldssph4jusdswu3k58hjo60wntrr0duo4ckrcwa8evuosf...@mail.gmail.com
---
doc/src/sgml/ref/create_schema.sgml | 4 +-
src/backend/parser/gram.y | 1 +
src/backend/parser/parse_utilcmd.c | 25 +++++++++
src/bin/psql/tab-complete.in.c | 12 ++--
.../expected/create_schema.out | 4 +-
.../test_ddl_deparse/sql/create_schema.sql | 3 +-
src/test/regress/expected/create_schema.out | 56 +++++++++++++++++++
src/test/regress/sql/create_schema.sql | 35 ++++++++++++
8 files changed, 130 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 db43034b9db..3aca508b08f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1626,6 +1626,7 @@ schema_stmt:
| CreateTrigStmt
| GrantStmt
| ViewStmt
+ | CreateDomainStmt
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 74672a458d5..218ec6f0982 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -4146,6 +4146,31 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
elements = lappend(elements, element);
break;
+ case T_CreateDomainStmt:
+ {
+ char *domain_schema = NULL;
+ CreateDomainStmt *elp = (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 specifies a schema (%s) "
+ "different from the one being created (%s)",
+ schemaName, domain_schema));
+ 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 8b10f2313f3..d7a8c769e35 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2178,7 +2178,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
@@ -3399,15 +3399,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..d73c4702051 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,9 @@ 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
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..57ada462070 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,5 @@ 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;
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index 38530c282a9..d6718a9f519 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 specifies a schema (regress_schema_2) different from the one being created (public)
+--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 specifies a schema (regress_schema_2) different from the one being created (public)
+--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..3028148e96b 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -71,5 +71,40 @@ 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 22bd3d92986b657d2a6a5dd0f8e4d6c2485796b0 Mon Sep 17 00:00:00 2001
From: Tom Lane <t...@sss.pgh.pa.us>
Date: Tue, 19 Aug 2025 10:52:05 +0800
Subject: [PATCH v6 1/3] 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.
Discussion: https://postgr.es/m/1075425.1732993...@sss.pgh.pa.us
---
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 | 135 ++++++++------------
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 | 9 +-
src/test/regress/sql/create_schema.sql | 5 +
src/test/regress/sql/namespace.sql | 11 +-
src/tools/pgindent/typedefs.list | 1 -
14 files changed, 132 insertions(+), 115 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 e6f9ab6dfd6..ad578bc76f7 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -1773,14 +1773,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 0f03d9743d2..b32935215f9 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 afcf54169c3..74672a458d5 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -95,18 +95,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);
@@ -133,7 +121,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, PartitionCmd *cmd);
static List *transformPartitionRangeBounds(ParseState *pstate, List *blist,
Relation parent);
@@ -4076,51 +4065,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))
{
@@ -4128,8 +4101,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;
@@ -4137,12 +4110,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;
@@ -4150,12 +4119,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;
@@ -4163,8 +4128,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;
@@ -4172,13 +4137,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:
@@ -4187,32 +4152,40 @@ 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) "
+ errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
+ errmsg("CREATE specifies a schema (%s) "
"different from the one being created (%s)",
- *stmt_schema_name, context_schema)));
+ 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 4f4191b0ea6..ec2bbe5587c 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1111,8 +1111,7 @@ ProcessUtilitySlow(ParseState *pstate,
* relation and attribute manipulation
*/
case T_CreateSchemaStmt:
- CreateSchemaCommand((CreateSchemaStmt *) parsetree,
- queryString,
+ CreateSchemaCommand(pstate, (CreateSchemaStmt *) parsetree,
pstmt->stmt_location,
pstmt->stmt_len);
diff --git a/src/include/commands/schemacmds.h b/src/include/commands/schemacmds.h
index 8557176b66a..835265bb67c 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 9f2b58de797..da514198ced 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 f551624afb3..b305ceea033 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -126,6 +126,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 7b2198eac6f..bafde8706fe 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -411,12 +411,12 @@ 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 INDEX type=index identity=evttrig.one_idx
-- Partitioned tables with a partitioned index
CREATE TABLE evttrig.parted (
id int PRIMARY KEY)
diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out
index dbbda72d395..2e582e783c2 100644
--- a/src/test/regress/expected/namespace.out
+++ b/src/test/regress/expected/namespace.out
@@ -10,13 +10,14 @@ 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
- );
+;
-- 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..a75d4f580d3 100644
--- a/src/test/regress/sql/namespace.sql
+++ b/src/test/regress/sql/namespace.sql
@@ -7,15 +7,16 @@
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
- );
+;
-- 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 e6f2e93b2d6..6a20bfa4b4d 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -558,7 +558,6 @@ CreateRangeStmt
CreateReplicationSlotCmd
CreateRoleStmt
CreateSchemaStmt
-CreateSchemaStmtContext
CreateSeqStmt
CreateStatsStmt
CreateStmt
--
2.34.1
From 9ff37e0923317cbce1722ea4c0260e72ce0db9c2 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Tue, 19 Aug 2025 11:31:08 +0800
Subject: [PATCH v6 3/3] CREATE SCHEMA CREATE COLLATION
SQL standard allow collation to be specified with CREATE SCHEMA
statement. This patch adds support in PostgreSQL for that.
For example:
CREATE SCHEMA schema_name AUTHORIZATION CURRENT_ROLE
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.
Note: src/bin/psql/tab-complete.in.c changes seems incorrect.
Discussion: https://postgr.es/m/caldssph4jusdswu3k58hjo60wntrr0duo4ckrcwa8evuosf...@mail.gmail.com
---
src/backend/catalog/objectaddress.c | 16 ++++++
src/backend/parser/gram.y | 1 +
src/backend/parser/parse_utilcmd.c | 23 +++++++++
src/bin/psql/tab-complete.in.c | 8 +--
src/include/catalog/objectaddress.h | 1 +
.../expected/create_schema.out | 4 +-
.../test_ddl_deparse/sql/create_schema.sql | 3 +-
src/test/regress/expected/create_schema.out | 50 +++++++++++++++++++
src/test/regress/sql/create_schema.sql | 32 ++++++++++++
9 files changed, 132 insertions(+), 6 deletions(-)
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 0102c9984e7..55b65dd08a3 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2622,6 +2622,22 @@ read_objtype_from_string(const char *objtype)
return -1; /* keep compiler quiet */
}
+const char *
+stringify_objtype(ObjectType objtype)
+{
+ int i;
+
+ for (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 3aca508b08f..30c4a567502 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1627,6 +1627,7 @@ schema_stmt:
| GrantStmt
| ViewStmt
| CreateDomainStmt
+ | DefineStmt
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 218ec6f0982..5fd6fc63ed6 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -4171,6 +4171,29 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
}
break;
+ case T_DefineStmt:
+ {
+ char *coll_schema = NULL;
+ char *collName;
+ DefineStmt *stmt = (DefineStmt *) element;
+
+ if (stmt->kind != OBJECT_COLLATION)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("CREATE SCHEMA ... CREATE OBJECT currently not support for %s",
+ stringify_objtype(stmt->kind)));
+
+ DeconstructQualifiedName(stmt->defnames, &coll_schema, &collName);
+ if (coll_schema && strcmp(coll_schema, schemaName) != 0)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
+ errmsg("CREATE specifies a schema (%s) "
+ "different from the one being created (%s)",
+ schemaName, coll_schema));
+
+ 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 d7a8c769e35..b6beb868eb2 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2178,7 +2178,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", "DOMAIN",
+ COMPLETE_WITH("TABLE", "VIEW", "INDEX", "SEQUENCE", "TRIGGER", "DOMAIN", "COLLATION",
/* for INDEX and TABLE/SEQUENCE, respectively */
"UNIQUE", "UNLOGGED");
else
@@ -3370,10 +3370,10 @@ match_previous_words(int pattern_id,
else if (Matches("CREATE", "ACCESS", "METHOD", MatchAny, "TYPE", MatchAny))
COMPLETE_WITH("HANDLER");
- /* CREATE COLLATION */
- else if (Matches("CREATE", "COLLATION", MatchAny))
+ /* CREATE COLLATION --- is allowed inside CREATE SCHEMA, so use TailMatches */
+ else if (TailMatches("CREATE", "COLLATION", MatchAny))
COMPLETE_WITH("(", "FROM");
- else if (Matches("CREATE", "COLLATION", MatchAny, "FROM"))
+ else if (TailMatches("CREATE", "COLLATION", MatchAny, "FROM"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_collations);
else if (HeadMatches("CREATE", "COLLATION", MatchAny, "(*"))
{
diff --git a/src/include/catalog/objectaddress.h b/src/include/catalog/objectaddress.h
index 630434b73cf..0070e77731e 100644
--- a/src/include/catalog/objectaddress.h
+++ b/src/include/catalog/objectaddress.h
@@ -79,6 +79,7 @@ extern char *getObjectDescription(const ObjectAddress *object,
extern char *getObjectDescriptionOids(Oid classid, Oid objid);
extern int read_objtype_from_string(const char *objtype);
+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 d73c4702051..19e0aad3cb0 100644
--- a/src/test/modules/test_ddl_deparse/expected/create_schema.out
+++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out
@@ -14,8 +14,10 @@ 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_icu_und FROM "und-x-icu";
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
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 57ada462070..c9a70a0862a 100644
--- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql
+++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql
@@ -15,4 +15,5 @@ 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_icu_und FROM "und-x-icu";
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index d6718a9f519..2669da3b15a 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -177,6 +177,48 @@ 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. cannot CREATE DOMAIN to other schema
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+ CREATE COLLATION public.coll_icu_und FROM "und-x-icu";
+ERROR: CREATE specifies a schema (regress_schema_4) different from the one being created (public)
+--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 objbect 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 OBJECT currently not support for aggregate
+--fail. Execute subcommands in order; we do not implicitly reorder them.
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+ CREATE TABLE tts(a TEXT COLLATE coll_icu_und)
+ CREATE COLLATION coll_icu_und FROM "und-x-icu";
+ERROR: collation "coll_icu_und" for encoding "UTF8" does not exist
+LINE 2: CREATE TABLE tts(a TEXT COLLATE coll_icu_und)
+ ^
+--ok, qualified schema name for domain should be same as the created schema
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+ CREATE COLLATION regress_schema_4.coll_icu_und FROM "und-x-icu"
+ CREATE TABLE t(a TEXT COLLATE regress_schema_4.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)
+
+--ok, no qualified schema name for collation
+CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE
+ CREATE COLLATION coll_icu_und FROM "und-x-icu"
+ CREATE TABLE t(a TEXT COLLATE regress_schema_5.coll_icu_und);
+\dO regress_schema_5.*
+ List of collations
+ Schema | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic?
+------------------+--------------+----------+---------+-------+--------+-----------+----------------
+ regress_schema_5 | coll_icu_und | icu | | | und | | 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 +229,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_icu_und
+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_icu_und
+drop cascades to table regress_schema_5.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 3028148e96b..e99915da383 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -103,8 +103,40 @@ 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. cannot CREATE DOMAIN 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 objbect 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');
+
+--fail. Execute subcommands in order; we do not implicitly reorder them.
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+ CREATE TABLE tts(a TEXT COLLATE coll_icu_und)
+ CREATE COLLATION coll_icu_und FROM "und-x-icu";
+
+--ok, qualified schema name for domain should be same as the created schema
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+ CREATE COLLATION regress_schema_4.coll_icu_und FROM "und-x-icu"
+ CREATE TABLE t(a TEXT COLLATE regress_schema_4.coll_icu_und);
+\dO regress_schema_4.*
+
+--ok, no qualified schema name for collation
+CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE
+ CREATE COLLATION coll_icu_und FROM "und-x-icu"
+ CREATE TABLE t(a TEXT COLLATE regress_schema_5.coll_icu_und);
+\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
