Thread

Commits

Don't try to re-order the subcommands of CREATE SCHEMA.
- a9c350d9ee66 19 (unreleased) landed
Execute foreign key constraints in CREATE SCHEMA at the end.
- 404db8f9edbb 19 (unreleased) landed
Support more object types within CREATE SCHEMA.
- d516974840f4 19 (unreleased) landed

CREATE SCHEMA ... CREATE DOMAIN support

Kirill Reshke <reshkekirill@gmail.com> — 2024-11-12T12:54:46Z

Hi hackers!

This copy of my reply mail from pgsql-general[0], & [1] which was held
for moderation for some reason.

Here it goes as-is :

== begin
Hi Álvaro, thanks for the detailed explanation.

So, IIUC you are suggesting to support SQL standard features before
any work with PostgreSQL extension.
Ok, I will try to go this way. PFA patch implementing CREATE DOMAIN
support for CREATE SCHEMA statement.
Of all other options, CREATE DOMAIN support looks like the most stranfoward one.

Patch obviously leaks doc & regression tests, but I'm posting it to
see if this contribution is needed in PostgreSQL

== end


[0] https://www.postgresql.org/message-id/CALdSSPgxcRkooZ2iQ5A7XhYoexVAdbiT6znZDqJTE8hxUVjz_A%40mail.gmail.com

[1] https://www.postgresql.org/message-id/202411111009.ckna4vp7ahyk%40alvherre.pgsql
-- 
Best regards,
Kirill Reshke

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2024-11-23T05:19:27Z

On Tue, Nov 12, 2024 at 8:55 PM Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> Patch obviously leaks doc & regression tests, but I'm posting it to
> see if this contribution is needed in PostgreSQL

the following two statement should fail:
CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
  CREATE table t(a ss)
  create domain public.ss as text not null default 'hello' constraint
nn check (value <> 'hello');

CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
  CREATE table t(a ss)
  create domain postgres.public.ss as text not null default 'hello'
constraint nn check (value <> 'hello');

we aslo need to consider the dependency issue. like the following should be ok.
CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE
  create view test as select 'hello'::ss as test
  CREATE table t(a ss)
  create domain ss as text not null;

i fixed these two issues, and add the above example as tests in
src/test/regress/sql/create_schema.sql

I didn't add a doc entry. I will do it later.

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2024-11-27T03:42:12Z

On Sat, Nov 23, 2024 at 1:19 PM jian he <jian.universality@gmail.com> wrote:
>
> I didn't add a doc entry. I will do it later.
hi
attached patch with thorough tests and documentation.

one issue i still have is:
CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
  create domain ss1 as ss
  create domain ss as text;
ERROR:  type "ss" does not exist

the error message seems not that OK,
if we can point out the error position, that would be great.
like what we did with create schema create table:

CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
    create table t(a int, b x);
ERROR:  type "x" does not exist
LINE 2:     create table t(a int, b x);
                                    ^

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-27T03:47:24Z

jian he <jian.universality@gmail.com> writes:
> one issue i still have is:
> CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
>   create domain ss1 as ss
>   create domain ss as text;
> ERROR:  type "ss" does not exist
> the error message seems not that OK,
> if we can point out the error position, that would be great.

That doesn't happen in the base case either:

regression=# create domain ss1 as ss;
ERROR:  type "ss" does not exist

I doubt that fixing it should be part of this patch.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Kirill Reshke <reshkekirill@gmail.com> — 2024-11-27T06:38:20Z

On Wed, 27 Nov 2024 at 08:42, jian he <jian.universality@gmail.com> wrote:
>
> On Sat, Nov 23, 2024 at 1:19 PM jian he <jian.universality@gmail.com> wrote:
> >
> > I didn't add a doc entry. I will do it later.
> hi
> attached patch with thorough tests and documentation.
>

Hi! Thanks for pushing this further.

> one issue i still have is:
> CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
>   create domain ss1 as ss
>   create domain ss as text;
> ERROR:  type "ss" does not exist
>
> the error message seems not that OK,
> if we can point out the error position, that would be great.
> like what we did with create schema create table:
>
> CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
>     create table t(a int, b x);
> ERROR:  type "x" does not exist
> LINE 2:     create table t(a int, b x);
>                                     ^

To implement this, we need to include `ParseLoc location` to the
`CreateDomainStmt` struct, which is doubtful, because I don't see any
other type of create *something* that does this.

`make check` on v3 runs successfully. Test & doc seems fine to me.

PFA v4. The only change I made is for a commit message, and pg indent
run on this diff.

-- 
Best regards,
Kirill Reshke

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-27T18:39:40Z

Kirill Reshke <reshkekirill@gmail.com> writes:
> On Wed, 27 Nov 2024 at 08:42, jian he <jian.universality@gmail.com> wrote:
>> CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
>> create domain ss1 as ss
>> create domain ss as text;
>> ERROR:  type "ss" does not exist
>> 
>> the error message seems not that OK,
>> if we can point out the error position, that would be great.

> To implement this, we need to include `ParseLoc location` to the
> `CreateDomainStmt` struct, which is doubtful, because I don't see any
> other type of create *something* that does this.

No, that error is thrown from typenameType(), which has a perfectly
good location in the TypeName.  What it's lacking is a ParseState
containing the source query string.

Breakpoint 1, typenameType (pstate=pstate@entry=0x0, typeName=0x25d6b58, 
    typmod_p=typmod_p@entry=0x7ffe7dcd641c) at parse_type.c:268
268             tup = LookupTypeName(pstate, typeName, typmod_p, false);
(gdb) p pstate
$2 = (ParseState *) 0x0
(gdb) p typeName->location
$3 = 21

We've fixed a few utility statements so that they can receive
a passed-down ParseState, but not DefineDomain.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Kirill Reshke <reshkekirill@gmail.com> — 2024-11-28T05:27:00Z

On Wed, 27 Nov 2024 at 23:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Kirill Reshke <reshkekirill@gmail.com> writes:
> > On Wed, 27 Nov 2024 at 08:42, jian he <jian.universality@gmail.com> wrote:
> >> CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
> >> create domain ss1 as ss
> >> create domain ss as text;
> >> ERROR:  type "ss" does not exist
> >>
> >> the error message seems not that OK,
> >> if we can point out the error position, that would be great.
>
> > To implement this, we need to include `ParseLoc location` to the
> > `CreateDomainStmt` struct, which is doubtful, because I don't see any
> > other type of create *something* that does this.
>
> No, that error is thrown from typenameType(), which has a perfectly
> good location in the TypeName.  What it's lacking is a ParseState
> containing the source query string.
>
> Breakpoint 1, typenameType (pstate=pstate@entry=0x0, typeName=0x25d6b58,
>     typmod_p=typmod_p@entry=0x7ffe7dcd641c) at parse_type.c:268
> 268             tup = LookupTypeName(pstate, typeName, typmod_p, false);
> (gdb) p pstate
> $2 = (ParseState *) 0x0
> (gdb) p typeName->location
> $3 = 21
>
> We've fixed a few utility statements so that they can receive
> a passed-down ParseState, but not DefineDomain.
>
>                         regards, tom lane

Indeed, my analysis is wrong.

Turns out passing parsestate to DefineDomain is itself enhancement.

Before this patch:
```
db1=# create domain ss1 as ss;
ERROR:  type "ss" does not exist
```

after:

```
db1=# create domain ss1 as ss;
ERROR:  type "ss" does not exist
LINE 1: create domain ss1 as ss;
                             ^
```
PFA as an independent patch then. Or should we combine these two into one?

-- 
Best regards,
Kirill Reshke

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-28T05:52:54Z

Kirill Reshke <reshkekirill@gmail.com> writes:
> On Wed, 27 Nov 2024 at 23:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> We've fixed a few utility statements so that they can receive
>> a passed-down ParseState, but not DefineDomain.

> PFA as an independent patch then. Or should we combine these two into one?

No, I don't think this should be part of the patch discussed in this
thread.

It feels rather random to me to be fixing only DefineDomain;
I'm sure there's more in the same vein.  I'd like to see a
patch with a scope along the lines of "fix everything reachable
within CREATE SCHEMA" or perhaps "fix all calls of typenameType".
(A quick grep shows that an outright majority of the callers of that
are passing null ParseState.  I didn't look to see if any of them
have a good excuse beyond "we didn't do the plumbing work".)

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Kirill Reshke <reshkekirill@gmail.com> — 2024-11-28T09:58:41Z

On Thu, 28 Nov 2024 at 10:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> No, I don't think this should be part of the patch discussed in this
> thread.

Ok, I created a separate thread for this.

How about this one? Do you think the suggested idea is good? Is it
worthwhile to do this, in your opinion?

-- 
Best regards,
Kirill Reshke

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2024-11-29T13:47:45Z
```
new patch, add tab complete for it.
```

Re: CREATE SCHEMA ... CREATE DOMAIN support

Kirill Reshke <reshkekirill@gmail.com> — 2024-11-29T14:00:37Z

On Fri, 29 Nov 2024 at 18:47, jian he <jian.universality@gmail.com> wrote:
>
> new patch, add tab complete for it.

Thank you. You may also be interested in reviewing [0].

[0] https://www.postgresql.org/message-id/CALdSSPhqfvKbDwqJaY%3DyEePi_aq61GmMpW88i6ZH7CMG_2Z4Cg%40mail.gmail.com

-- 
Best regards,
Kirill Reshke

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-30T19:08:08Z

[ Looping in Peter E. for commentary on SQL-spec compatibility ]

I spent some time looking at this patch, and came away with
two main thoughts:

1. It doesn't make any sense to me to support CREATE DOMAIN within
CREATE SCHEMA but not any of our other commands for creating types.
It's not a consistent feature this way, and there's no support for
it in the SQL standard either, because the spec calls out both
<domain definition> and <user-defined type definition> as permissible
schema elements.  So I think we need a bit more ambition in the scope
of the patch: it should allow every variant of CREATE TYPE too.
(Since the spec also lists <schema routine>, I'd personally like to
see us cover functions/procedures as well as types.  But functions
could be left for later I guess.)

2. transformCreateSchemaStmtElements is of the opinion that it's
responsible for ordering the schema elements in a way that will work,
but it just about completely fails at that task.  Ordering the objects
by kind is surely not sufficient, and adding CREATE DOMAIN will make
that worse.  (Example: a domain could be used in a table definition,
but we also allow domains to be created over tables' composite types.)
Yet we have no infrastructure that would allow us to discover the real
dependencies between unparsed DDL commands, nor is it likely that
anyone will ever undertake building such.  I think we ought to nuke
that concept from orbit and just execute the schema elements in the
order presented.  I looked at several iterations of the SQL standard
and cannot find any support for the idea that CREATE SCHEMA needs to
be any smarter than that.  I'd also argue that doing anything else is
a POLA violation.  It's especially a POLA violation if the code
rearranges a valid user-written command order into an invalid order,
which is inevitable if we stick with the current approach.

The notion that we ought to sort the objects by kind appears to go
all the way back to 95ef6a344 of 2002-03-21, which I guess makes it
my fault.  There must have been some prior mailing list discussion,
but I couldn't find much.  There is a predecessor of the committed
patch in
https://www.postgresql.org/message-id/flat/3C7F8A49.CC4EF0BE%40redhat.com
but no discussion of why sorting by kind is a good idea.  (The last
message in the thread suggests that there was more discussion among
the Red Hat RHDB team, but if so it's lost to history now.)

Thoughts?

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-30T23:33:24Z

I wrote:
> 2. transformCreateSchemaStmtElements is of the opinion that it's
> responsible for ordering the schema elements in a way that will work,
> but it just about completely fails at that task.  Ordering the objects
> by kind is surely not sufficient, and adding CREATE DOMAIN will make
> that worse.  (Example: a domain could be used in a table definition,
> but we also allow domains to be created over tables' composite types.)
> Yet we have no infrastructure that would allow us to discover the real
> dependencies between unparsed DDL commands, nor is it likely that
> anyone will ever undertake building such.  I think we ought to nuke
> that concept from orbit and just execute the schema elements in the
> order presented.  I looked at several iterations of the SQL standard
> and cannot find any support for the idea that CREATE SCHEMA needs to
> be any smarter than that.  I'd also argue that doing anything else is
> a POLA violation.  It's especially a POLA violation if the code
> rearranges a valid user-written command order into an invalid order,
> which is inevitable if we stick with the current approach.

Further to this: I don't think "re-order into a safe order" is even
a well-defined requirement.  What should happen with

    CREATE VIEW public.v1 AS SELECT * FROM foo;

    CREATE SCHEMA s1

        CREATE VIEW v0 AS SELECT * FROM v1;

        CREATE VIEW v1 AS SELECT * FROM bar;

If we re-order the CREATE VIEW subcommands, this means something
different than if we don't.  Maybe the user meant us to re-order,
but it's hardly an open-and-shut argument.  And historically we
have not re-ordered CREATE VIEW subcommands, so there's a hazard
of compatibility problems if we did ever try to do that.

So attached is a draft patch that simplifies the rule to "do the
subcommands in the order written".  I took the opportunity to clean up
some other small infelicities about transformCreateSchemaStmtElements,
too.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Kirill Reshke <reshkekirill@gmail.com> — 2024-12-01T05:39:20Z

On Sun, 1 Dec 2024 at 04:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I looked at several iterations of the SQL standard
> and cannot find any support for the idea that CREATE SCHEMA needs to
> be any smarter than that.  I'd also argue that doing anything else is
> a POLA violation.  It's especially a POLA violation if the code
> rearranges a valid user-written command order into an invalid order,
> which is inevitable if we stick with the current approach.

Agreed.

> So attached is a draft patch that simplifies the rule to "do the
> subcommands in the order written".

+1 on this idea.

Here is my 2c to this draft:
1) Report error position on newly added check for temp relation in
non-temp schema.

> +                 errmsg("cannot create temporary relation in non-temporary schema"),
> +                 parser_errposition(pstate, relation->location)));

2)
I added some regression tests that might be worth adding:
a) check for a temporary table created within a non-temporary schema
in create_table.sql, akin to existing check in create_view.sql.
b) Also explicitly check that old-style sql creation does not work.
That is, for example,
CREATE SCHEMA test_ns_schema_3
       CREATE VIEW abcd_view AS
              SELECT a FROM abcd
       CREATE TABLE abcd (
              a serial
       );

fails.

3) Why do we delete this in `create_schema.sgml`? Is this untrue? It
is about order of definition, not creation, isn't it?

> -   The SQL standard specifies that the subcommands in <command>CREATE
> -   SCHEMA</command> can appear in any order.


P.S.
This section in SQL-92 is the only information I could find about
order of creation.

```
         3) Those objects defined by <schema element>s (base tables, views,
            constraints, domains, assertions, character sets, translations,
            collations, privileges) and their associated descriptors are
            effectively created.
```
Look like we are 100% to do it in order of definition


-- 
Best regards,
Kirill Reshke

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-01T05:53:04Z

Kirill Reshke <reshkekirill@gmail.com> writes:
> 3) Why do we delete this in `create_schema.sgml`? Is this untrue? It
> is about order of definition, not creation, isn't it?

>> -   The SQL standard specifies that the subcommands in <command>CREATE
>> -   SCHEMA</command> can appear in any order.

In context with the following sentence, what that is really trying
to say is that the spec requires us to re-order the subcommands
to eliminate forward references.  After studying the text I cannot
find any such statement.  Maybe I missed something --- there's a
lot of text --- but it's sure not to be detected in any obvious
place like 11.1 <schema definition>.

(I'd be curious to know how other major implementations handle
this.  Are we the only implementation that ever read the spec
that way?)

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2024-12-01T09:59:08Z

On Sun, Dec 1, 2024 at 1:53 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Kirill Reshke <reshkekirill@gmail.com> writes:
> > 3) Why do we delete this in `create_schema.sgml`? Is this untrue? It
> > is about order of definition, not creation, isn't it?
>
> >> -   The SQL standard specifies that the subcommands in <command>CREATE
> >> -   SCHEMA</command> can appear in any order.
>
> In context with the following sentence, what that is really trying
> to say is that the spec requires us to re-order the subcommands
> to eliminate forward references.  After studying the text I cannot
> find any such statement.  Maybe I missed something --- there's a
> lot of text --- but it's sure not to be detected in any obvious
> place like 11.1 <schema definition>.
>

I checked,  you didn't miss anything
11.1  didn't mention "order" at all.

> (I'd be curious to know how other major implementations handle
> this.  Are we the only implementation that ever read the spec
> that way?)
>

quote from  https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-ver16
<<>>
CREATE SCHEMA can create a schema, the tables and views it contains, and GRANT,
REVOKE, or DENY permissions on any securable in a single statement. This
statement must be executed as a separate batch. Objects created by the CREATE
SCHEMA statement are created inside the schema that is being created.

Securables to be created by CREATE SCHEMA can be listed in any order, except for
views that reference other views. In that case, the referenced view must be
created before the view that references it.

Therefore, a GRANT statement can grant permission on an object before the object
itself is created, or a CREATE VIEW statement can appear before the CREATE TABLE
statements that create the tables referenced by the view. Also, CREATE TABLE
statements can declare foreign keys to tables that are defined later in the
CREATE SCHEMA statement.
<<>>

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-01T16:57:23Z

jian he <jian.universality@gmail.com> writes:
> On Sun, Dec 1, 2024 at 1:53 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> (I'd be curious to know how other major implementations handle
>> this.  Are we the only implementation that ever read the spec
>> that way?)

> quote from  https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-ver16
> <<>>
> CREATE SCHEMA can create a schema, the tables and views it contains, and GRANT,
> REVOKE, or DENY permissions on any securable in a single statement. This
> statement must be executed as a separate batch. Objects created by the CREATE
> SCHEMA statement are created inside the schema that is being created.

> Securables to be created by CREATE SCHEMA can be listed in any order, except for
> views that reference other views. In that case, the referenced view must be
> created before the view that references it.

> Therefore, a GRANT statement can grant permission on an object before the object
> itself is created, or a CREATE VIEW statement can appear before the CREATE TABLE
> statements that create the tables referenced by the view. Also, CREATE TABLE
> statements can declare foreign keys to tables that are defined later in the
> CREATE SCHEMA statement.
> <<>>

Interesting.  But I suspect this tells us more about SQL Server's
internal implementation of DDL actions than about spec requirements.

I looked at DB2's reference page:
https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-schema
It doesn't have much of anything explicit on this topic, but they do
give an example showing that you can create two tables with mutually
referencing foreign keys, which means they postpone FK constraint
creation till the end.  There's also this interesting tidbit:
"Unqualified object names in any SQL statement within the CREATE SCHEMA
statement are implicitly qualified by the name of the created schema."
which eliminates some of the is-that-an-external-reference-or-a-
forward-reference ambiguities I was concerned about yesterday.
That ship sailed decades ago for us, however.

I'm also interested to note that like SQL Server, DB2 has strict
limits on the types of objects that can be created, much narrower
than what the spec suggests.  For DB2 it's:

CREATE TABLE statement, excluding typed tables and materialized query tables
CREATE VIEW statement, excluding typed views
CREATE INDEX statement
COMMENT statement
GRANT statement

That suggests, even though they don't say so, that they're trying to
do forward-reference removal; there'd be little reason for the
restriction otherwise.

MySQL doesn't have CREATE SCHEMA (it's a synonym for CREATE DATABASE),
so nothing to be learned there.

Whether or not the standard has an opinion on this topic, it's pretty
clear that real implementations are all over the place and have plenty
of ad-hoc restrictions.  I'm still thinking that "let's forget all
that and do the subcommands in order" is a win for sanity and
explainability.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-01T22:30:20Z

I wrote:
> I looked at DB2's reference page:
> https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-schema

Oh, how did I forget Oracle?

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-SCHEMA.html

Theirs is restricted to CREATE TABLE, CREATE VIEW, and GRANT; also
this curious restriction: "The CREATE SCHEMA statement supports the
syntax of these statements only as defined by standard SQL, rather
than the complete syntax supported by Oracle Database."

But then they say:

"The order in which you list the CREATE TABLE, CREATE VIEW, and GRANT
statements is unimportant. The statements within a CREATE SCHEMA
statement can reference existing objects or objects you create in
other statements within the same CREATE SCHEMA statement."

Which certainly begs the question of how smart their re-ordering
algorithm is, or what they do about ambiguity between new and existing
objects.  But at any rate, it looks like everybody is at least trying
to do some amount of re-ordering, which makes me wonder what it is
that I'm missing in the spec.  That's an awful lot of effort to be
expending on something that the spec doesn't seem to require.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Michael Paquier <michael@paquier.xyz> — 2024-12-02T01:34:57Z

On Sun, Dec 01, 2024 at 05:30:20PM -0500, Tom Lane wrote:
> Which certainly begs the question of how smart their re-ordering
> algorithm is, or what they do about ambiguity between new and existing
> objects.

Perhaps because they are able to track efficiently all schema
references, like checking the internal of functions at creation time
rather than just at runtime?  The ambiguity between new and existing
objects may be tricky, indeed.

If I'm parsing the spec right, the doc mentions in its 5)~6) of the
syntax rules in CREATE SCHEMA that non-schema-qualified objects should
use the new schema name defined in the CREATE SCHEMA query.  So that
pretty much settles the rules to use when having a new object that has
a reference to a non-qualified object created in the same CREATE
SCHEMA query?

> But at any rate, it looks like everybody is at least trying
> to do some amount of re-ordering, which makes me wonder what it is
> that I'm missing in the spec.  That's an awful lot of effort to be
> expending on something that the spec doesn't seem to require.

As Jian has mentioned, 9075-2-2023 around 11.1 for CREATE SCHEMA does
not include any ordering assumptions when the elements are created, so
my guess is that this is left up to each implementation depending on
how they need to handle their dependencies with their meta-data
lookup?  The result would be the same once the query has finished
running, as long as the elements created are consistent with their
inner dependencies.
--
Michael

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-02T02:15:23Z

Michael Paquier <michael@paquier.xyz> writes:
> If I'm parsing the spec right, the doc mentions in its 5)~6) of the
> syntax rules in CREATE SCHEMA that non-schema-qualified objects should
> use the new schema name defined in the CREATE SCHEMA query.  So that
> pretty much settles the rules to use when having a new object that has
> a reference to a non-qualified object created in the same CREATE
> SCHEMA query?

I don't see where you're getting that from?  DB2 says that unqualified
reference names (not to be confused with unqualified creation-target
names) are taken to be in the new schema, but I don't see any
corresponding restriction in the spec.

What I do see (11.1 SR 6 in SQL:2021) is:

    If <schema path specification> is not specified, then a <schema
    path specification> containing an implementation-defined <schema
    name list> that contains the <schema name> contained in <schema
    name clause> is implicit.

What I read this as is that the "search path" during schema-element
creation must include at least the new schema, but can also include
some other schemas as defined by the implementation.  That makes
our behavior compliant, because we can define the other schemas
as those in the session's prevailing search_path.  (DB2's behavior
is also compliant, but they're defining the path as containing only
the new schema.)

Also, if SQL intended to constrain the search path for unqualified
identifiers to be only the new schema, they'd hardly need a concept
of <schema path specification> at all.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Vik Fearing <vik@postgresfriends.org> — 2024-12-02T16:08:26Z

On 02/12/2024 03:15, Tom Lane wrote:
> Michael Paquier <michael@paquier.xyz> writes:
>> If I'm parsing the spec right, the doc mentions in its 5)~6) of the
>> syntax rules in CREATE SCHEMA that non-schema-qualified objects should
>> use the new schema name defined in the CREATE SCHEMA query.  So that
>> pretty much settles the rules to use when having a new object that has
>> a reference to a non-qualified object created in the same CREATE
>> SCHEMA query?
> I don't see where you're getting that from?  DB2 says that unqualified
> reference names (not to be confused with unqualified creation-target
> names) are taken to be in the new schema, but I don't see any
> corresponding restriction in the spec.
>
> What I do see (11.1 SR 6 in SQL:2021) is:
>
>      If <schema path specification> is not specified, then a <schema
>      path specification> containing an implementation-defined <schema
>      name list> that contains the <schema name> contained in <schema
>      name clause> is implicit.
>
> What I read this as is that the "search path" during schema-element
> creation must include at least the new schema, but can also include
> some other schemas as defined by the implementation.  That makes
> our behavior compliant, because we can define the other schemas
> as those in the session's prevailing search_path.  (DB2's behavior
> is also compliant, but they're defining the path as containing only
> the new schema.)
>
> Also, if SQL intended to constrain the search path for unqualified
> identifiers to be only the new schema, they'd hardly need a concept
> of <schema path specification> at all.


I looked up the original paper (MUN-051) that introduced the <schema 
path specification> and it says, "The paper is proposing the use of 
paths only to resolve unqualified routine invocations."


That doesn't seem to have been explained much by the rest of the spec, 
but it is visible in the definition of <path specification> which says, 
"Specify an order for searching for an SQL-invoked routine."


I can find nowhere that says that the path can or cannot be used for 
other objects.

-- 

Vik Fearing

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-02T16:56:16Z

Vik Fearing <vik@postgresfriends.org> writes:
> On 02/12/2024 03:15, Tom Lane wrote:
>> Also, if SQL intended to constrain the search path for unqualified
>> identifiers to be only the new schema, they'd hardly need a concept
>> of <schema path specification> at all.

> I looked up the original paper (MUN-051) that introduced the <schema 
> path specification> and it says, "The paper is proposing the use of 
> paths only to resolve unqualified routine invocations."

Interesting.  But still, the spec allows <schema routine> within
<schema definition>, so even that narrow interpretation opens them
to the is-this-an-external-reference-or-a-forward-reference problem.

For us, that's clouded further for functions by our overloading rules.
If foo(bigint) exists in the search path, and we have a view or
whatever that references foo() with an int argument, and there is a
CREATE FUNCTION for foo(float8) later in the <schema definition>, what
are we supposed to think is the user's intent?  (Just to save people
doing the experiment: we'd prefer foo(float8) if both are visible,
but foo(bigint) would be perfectly acceptable if not.  Other choices
of the argument types would yield different results, and none of them
seem especially open-and-shut to me.)  I don't know offhand if the
spec allows function overloading in the same way.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Vik Fearing <vik@postgresfriends.org> — 2024-12-02T17:26:33Z

On 02/12/2024 17:56, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.org> writes:
>> On 02/12/2024 03:15, Tom Lane wrote:
>>> Also, if SQL intended to constrain the search path for unqualified
>>> identifiers to be only the new schema, they'd hardly need a concept
>>> of <schema path specification> at all.
>> I looked up the original paper (MUN-051) that introduced the <schema
>> path specification> and it says, "The paper is proposing the use of
>> paths only to resolve unqualified routine invocations."
> Interesting.

The standard actually does say that that is what it is for.

Section 11.1 <schema definition> SR 8:

"The <schema name list> of the explicit or implicit <schema path 
specification> is used as the SQL- path of the schema. The SQL-path is 
used to effectively qualify unqualified <routine name>s that are 
immediately contained in <routine invocation>s that are contained in the 
<schema definition>."

> But still, the spec allows <schema routine> within
> <schema definition>, so even that narrow interpretation opens them
> to the is-this-an-external-reference-or-a-forward-reference problem.

Surely that is determined by the placement of the schema in its own 
SQL-path.

> For us, that's clouded further for functions by our overloading rules.
> If foo(bigint) exists in the search path, and we have a view or
> whatever that references foo() with an int argument, and there is a
> CREATE FUNCTION for foo(float8) later in the <schema definition>, what
> are we supposed to think is the user's intent?  (Just to save people
> doing the experiment: we'd prefer foo(float8) if both are visible,
> but foo(bigint) would be perfectly acceptable if not.  Other choices
> of the argument types would yield different results, and none of them
> seem especially open-and-shut to me.)

My answer is the same as above, for unqualified names.

However, since there is nothing that says anything either way about 
forward references, my preference is to just execute them all in the 
order written.  In your example, that would mean choosing 
otherschema.foo(bigint) over thisschema.foo(float8) if the latter hasn't 
been created yet.

> I don't know offhand if the
> spec allows function overloading in the same way.

Feature T-321 has a note saying, "Support for overloaded functions and 
procedures is not part of Core SQL."

-- 

Vik Fearing

Re: CREATE SCHEMA ... CREATE DOMAIN support

Peter Eisentraut <peter@eisentraut.org> — 2024-12-03T19:35:10Z

On 30.11.24 20:08, Tom Lane wrote:
> 2. transformCreateSchemaStmtElements is of the opinion that it's
> responsible for ordering the schema elements in a way that will work,
> but it just about completely fails at that task.  Ordering the objects
> by kind is surely not sufficient, and adding CREATE DOMAIN will make
> that worse.  (Example: a domain could be used in a table definition,
> but we also allow domains to be created over tables' composite types.)
> Yet we have no infrastructure that would allow us to discover the real
> dependencies between unparsed DDL commands, nor is it likely that
> anyone will ever undertake building such.  I think we ought to nuke
> that concept from orbit and just execute the schema elements in the
> order presented.  I looked at several iterations of the SQL standard
> and cannot find any support for the idea that CREATE SCHEMA needs to
> be any smarter than that.  I'd also argue that doing anything else is
> a POLA violation.  It's especially a POLA violation if the code
> rearranges a valid user-written command order into an invalid order,
> which is inevitable if we stick with the current approach.
> 
> The notion that we ought to sort the objects by kind appears to go
> all the way back to 95ef6a344 of 2002-03-21, which I guess makes it
> my fault.  There must have been some prior mailing list discussion,
> but I couldn't find much.  There is a predecessor of the committed
> patch in
> https://www.postgresql.org/message-id/flat/3C7F8A49.CC4EF0BE%40redhat.com
> but no discussion of why sorting by kind is a good idea.  (The last
> message in the thread suggests that there was more discussion among
> the Red Hat RHDB team, but if so it's lost to history now.)

SQL/Framework subclause "Descriptors" says:

"""
The execution of an SQL-statement may result in the creation of many 
descriptors. An SQL object that is created as a result of an 
SQL-statement may depend on other descriptors that are only created as a 
result of the execution of that SQL statement.

NOTE 8 — This is particularly relevant in the case of the <schema 
definition> SQL-statement. A <schema definition> can, for example, 
contain many <table definition>s that in turn contain <table 
constraint>s. A single <table constraint> in one <table definition> can 
reference a second table being created by a separate <table definition> 
which itself is able to contain a reference to the first table. The 
dependencies of each table on the descriptors of the other are valid 
provided that all necessary descriptors are created during the execution 
of the complete <schema definition>.
"""

So this says effectively that forward references are allowed.  Whether 
reordering the statements is a good way to implement that is dubious, as 
we are discovering.

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-03T20:07:38Z

Peter Eisentraut <peter@eisentraut.org> writes:
> On 30.11.24 20:08, Tom Lane wrote:
>> ... I think we ought to nuke
>> that concept from orbit and just execute the schema elements in the
>> order presented.  I looked at several iterations of the SQL standard
>> and cannot find any support for the idea that CREATE SCHEMA needs to
>> be any smarter than that.

> SQL/Framework subclause "Descriptors" says:

> """
> The execution of an SQL-statement may result in the creation of many 
> descriptors. An SQL object that is created as a result of an 
> SQL-statement may depend on other descriptors that are only created as a 
> result of the execution of that SQL statement.

> NOTE 8 — This is particularly relevant in the case of the <schema 
> definition> SQL-statement. A <schema definition> can, for example, 
> contain many <table definition>s that in turn contain <table 
> constraint>s. A single <table constraint> in one <table definition> can 
> reference a second table being created by a separate <table definition> 
> which itself is able to contain a reference to the first table. The 
> dependencies of each table on the descriptors of the other are valid 
> provided that all necessary descriptors are created during the execution 
> of the complete <schema definition>.
> """

Ah, thanks for the pointer.

> So this says effectively that forward references are allowed.  Whether 
> reordering the statements is a good way to implement that is dubious, as 
> we are discovering.

Yeah, I think it's a long way from this text to the conclusion that
the implementation is responsible for reordering the subcommands
to remove forward references.  And it really offers no help at all
for the ensuing problem of distinguishing forward references from
external references.

The one aspect of the spec's definition that seems useful to me in
practice is the ability to create quasi-circular foreign keys (that
is, t1 has an FK to t2 and t2 has an FK to t1).  But that is something
we have never implemented in 22 years and nobody has complained of
the lack.  I'm totally willing to throw that possibility overboard
permanently in order to expand the set of creatable object types
without introducing a ton of restrictions and weird behaviors.
What do you think?

			regards, tom lane

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>.

Re: CREATE SCHEMA ... CREATE DOMAIN support

Kirill Reshke <reshkekirill@gmail.com> — 2024-12-11T06:22:39Z

Looks like this thread does not move forward. So I'm posting my
thoughts to bump it.

On Wed, 4 Dec 2024 at 01:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm totally willing to throw that possibility overboard
> permanently in order to expand the set of creatable object types
> without introducing a ton of restrictions and weird behaviors.
> What do you think?

Im +1 on this, but can you please elaborate, which exact objects
cannot be created now? What will be expanded after
v2-0002-Dont_try-to-reoder....?

> 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?

-- 
Best regards,
Kirill Reshke

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2024-12-11T17:08:36Z

Kirill Reshke <reshkekirill@gmail.com> writes:
> On Wed, 4 Dec 2024 at 01:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm totally willing to throw that possibility overboard
>> permanently in order to expand the set of creatable object types
>> without introducing a ton of restrictions and weird behaviors.
>> What do you think?

> Im +1 on this, but can you please elaborate, which exact objects
> cannot be created now? What will be expanded after
> v2-0002-Dont_try-to-reoder....?

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?

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2025-08-19T03:36:49Z

On Thu, Dec 12, 2024 at 1:08 AM Tom Lane <tgl@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

Re: CREATE SCHEMA ... CREATE DOMAIN support

Kirill Reshke <reshkekirill@gmail.com> — 2025-08-22T08:59:37Z

On Tue, 19 Aug 2025 at 08:37, jian he <jian.universality@gmail.com> wrote:
>
> On Thu, Dec 12, 2024 at 1:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 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.

Hi!

> move this forward with option #3 (executing the subcommands in order).

Thank you. I am +1 on option #3.

> 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


With these patches applied:
```
reshke=# create schema  sh1 create type tp as  (i text);
ERROR:  unrecognized node type: 226
```
Without patches it will be a syntax error.



Also we need a better error message in this:
"CREATE SCHEMA ... CREATE OBJECT currently not support for..."

First of all, is it s/support/supported/ ? Also would vote for
something like "%s is not yet supported inside schema definition."
WDYT?


-- 
Best regards,
Kirill Reshke

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2025-08-26T04:53:07Z

On Fri, Aug 22, 2025 at 4:59 PM Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> >
> > 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>
> >
>

I also added CREATE SCHEMA CREATE TYPE.

> With these patches applied:
> ```
> reshke=# create schema  sh1 create type tp as  (i text);
> ERROR:  unrecognized node type: 226
> ```
> Without patches it will be a syntax error.
>

This issue is solved in V7.

>
> Also we need a better error message in this:
> "CREATE SCHEMA ... CREATE OBJECT currently not support for..."
>
> First of all, is it s/support/supported/ ? Also would vote for
> something like "%s is not yet supported inside schema definition."
> WDYT?
>

"%s is not yet supported inside schema definition." is good option,
but how about

ereport(ERROR,
    errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
    errmsg("CREATE SCHEMA ... CREATE %s currently not supported",
                asc_toupper(stringify_objtype(stmt->kind),
                strlen(stringify_objtype(stmt->kind)))))

stringify_objtype will produce the object type name, then capitalize it.
IMHO, now the error message would be more explicit.
+--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


not all CI test machine encoding is UTF8, CREATE COLLATION depends on encoding,
if fail, error message may different on different machines, So we have to put
some of the tests to collate.icu.utf8.sql.

Please check the latest attached.
v7-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch
v7-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
v7-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
v7-0004-CREATE-SCHEMA-CREATE-TYPE.patch

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2025-09-02T21:24:01Z

jian he <jian.universality@gmail.com> writes:
> Please check the latest attached.
> v7-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch
> v7-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
> v7-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
> v7-0004-CREATE-SCHEMA-CREATE-TYPE.patch

I think this is still kind of blocked, because it's not clear to me
whether we have consensus about it being okay to do 0001.

Re-reading the thread, the only real use-case for re-ordering that
anyone proposed is that foreign key references should be able to be
forward references to tables created later in the same CREATE SCHEMA.
I concede first that this is a somewhat-plausible use-case and
second that it is pretty clearly required by spec.  The fact remains
however that we have never supported that in two dozen years, and
the number of complaints about the omission could be counted without
running out of thumbs.  So, how about the following plan of action?

1. Rip out subcommand re-ordering as currently implemented, and do the
subcommands in the given order.

2. When a CREATE TABLE subcommand includes a FOREIGN KEY clause,
transform that clause into ALTER TABLE ADD FOREIGN KEY, and push
it to the back of the CREATE SCHEMA's to-do list.

#2 gives us at least pro-forma spec compliance, and AFAICS it does
not introduce any command re-ordering bugs.  Foreign key clauses
don't depend on each other, so shoving them to the end without any
further sorting should be fine.

Also ... we don't really have to do #2 until someone complains about
the lack of ability to do forward references, which going by history
is probably not going to be soon.  I certainly don't feel that it has
to be completed in this patchset.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2025-12-17T13:29:13Z

On Wed, Sep 3, 2025 at 5:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>

> jian he <jian.universality@gmail.com> writes:
> > Please check the latest attached.
> > v7-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch

> I think this is still kind of blocked, because it's not clear to me
> whether we have consensus about it being okay to do 0001.
>
> Re-reading the thread, the only real use-case for re-ordering that
> anyone proposed is that foreign key references should be able to be
> forward references to tables created later in the same CREATE SCHEMA.
> I concede first that this is a somewhat-plausible use-case and
> second that it is pretty clearly required by spec.  The fact remains
> however that we have never supported that in two dozen years, and
> the number of complaints about the omission could be counted without
> running out of thumbs.  So, how about the following plan of action?
>
> 1. Rip out subcommand re-ordering as currently implemented, and do the
> subcommands in the given order.
>
> 2. When a CREATE TABLE subcommand includes a FOREIGN KEY clause,
> transform that clause into ALTER TABLE ADD FOREIGN KEY, and push
> it to the back of the CREATE SCHEMA's to-do list.
>
> #2 gives us at least pro-forma spec compliance, and AFAICS it does
> not introduce any command re-ordering bugs.  Foreign key clauses
> don't depend on each other, so shoving them to the end without any
> further sorting should be fine.
>

Hi.

I just want to confirm we are on the same page. so you expect CREATE SCHEMA
OptSchemaEltList executed as the specified order given, also expect cross table
FOREIGN KEY referencing works just fine too?

If so, obviously It will be a hack, but it seems the hack is easier to
understand.

1. For CREATE TABLE, some case, we wrap the FK Constraint node in the form of an
ALTER TABLE ADD CONSTRAINT command, creating the FK constraint only after the
relation itself has been created. see transformFKConstraints comments.  For
CREATE SCHEMA, we can make it after multiple relations are created, then process
FK constraint too.

2. src/backend/parser/gram.y already processes most of the information for FK.
transformFKConstraints merely wraps the FK Constraint node into an
AlterTableCmd, which is then wrapped into an AlterTableStmt.

In the HEAD, CREATE SCHEMA will fail when you first mention FK then PK.
but with the attached, it will work fine.
for example, below two will error out in the HEAD, but it will works
fine with the attached
patch.

CREATE SCHEMA s2
  CREATE TABLE t2(a int, b int, c int, foreign key (a, b) references
t1 match full) partition by range (a, b, c)
  CREATE TABLE t1(a int, b int, primary key(b, a)) partition by range (a, b);

CREATE SCHEMA s2
  CREATE TABLE t2(a int, b int, c int, foreign key (a, b) references
t1 match full) partition by range (a, b, c)
  CREATE TABLE t1(a int, b int, primary key(b, a)) partition by range (a, b);

--
jian
https://www.enterprisedb.com/

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2025-12-31T01:40:49Z

> On Wed, Sep 3, 2025 at 5:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >

>
> > I think this is still kind of blocked, because it's not clear to me
> > whether we have consensus about it being okay to do 0001.
> >
> > Re-reading the thread, the only real use-case for re-ordering that
> > anyone proposed is that foreign key references should be able to be
> > forward references to tables created later in the same CREATE SCHEMA.
> > I concede first that this is a somewhat-plausible use-case and
> > second that it is pretty clearly required by spec.  The fact remains
> > however that we have never supported that in two dozen years, and
> > the number of complaints about the omission could be counted without
> > running out of thumbs.  So, how about the following plan of action?
> >
> > 1. Rip out subcommand re-ordering as currently implemented, and do the
> > subcommands in the given order.
> >
> > 2. When a CREATE TABLE subcommand includes a FOREIGN KEY clause,
> > transform that clause into ALTER TABLE ADD FOREIGN KEY, and push
> > it to the back of the CREATE SCHEMA's to-do list.
> >
> > #2 gives us at least pro-forma spec compliance, and AFAICS it does
> > not introduce any command re-ordering bugs.  Foreign key clauses
> > don't depend on each other, so shoving them to the end without any
> > further sorting should be fine.
> >

hi.

v8-0001 through v8-0004 are rebased from v7 with some adjustments.

v8-0005 transforms foreign key constraints into ALTER TABLE ... ADD FOREIGN KEY.
This works for both column constraint and table constraint. Below is a contrived
complex test case I came up with to verify correctness.

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));


Does this resolve your #2?


--
jian
https://www.enterprisedb.com/

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2026-01-19T15:06:57Z

hi.

https://api.cirrus-ci.com/v1/artifact/task/6697254683148288/testrun/build/testrun/regress/regress/regression.diffs
seems there is some concurrency issue in regress test.

I made minor adjustments to the regression tests and updated the comments;
otherwise, this is unchanged from v8.


--
jian
https://www.enterprisedb.com

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2026-03-10T14:11:28Z
```
Hi.
Rebase only.



--
jian
https://www.enterprisedb.com/
```
Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2026-03-30T02:44:10Z
```
Hi.
Rebase only.



--
jian
https://www.enterprisedb.com/
```

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-03T18:35:22Z

I wrote [ many months ago now ]:
>> I think this is still kind of blocked, because it's not clear to me
>> whether we have consensus about it being okay to do 0001.
>> 
>> Re-reading the thread, the only real use-case for re-ordering that
>> anyone proposed is that foreign key references should be able to be
>> forward references to tables created later in the same CREATE SCHEMA.
>> I concede first that this is a somewhat-plausible use-case and
>> second that it is pretty clearly required by spec.  The fact remains
>> however that we have never supported that in two dozen years, and
>> the number of complaints about the omission could be counted without
>> running out of thumbs.  So, how about the following plan of action?
>> 
>> 1. Rip out subcommand re-ordering as currently implemented, and do the
>> subcommands in the given order.
>> 
>> 2. When a CREATE TABLE subcommand includes a FOREIGN KEY clause,
>> transform that clause into ALTER TABLE ADD FOREIGN KEY, and push
>> it to the back of the CREATE SCHEMA's to-do list.

Nobody has complained about this approach since September, so unless
there's a complaint PDQ, I'm going to take silence as consent and
move forward with reviewing/applying Jian's patchset.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-05T18:42:26Z

Apropos of the question of how much rearrangement functionality the
SQL spec actually asks for, I was interested to come across this entry
in SQL:2021's Table 43 — Feature taxonomy and definition for mandatory
features:

Feature ID	Feature Name	Feature Description

F311-01		CREATE SCHEMA	— Subclause 11.1, “<schema definition>”:
				Support for circular references in that <refer-
				ential constraint definition>s in two different
				<table definition>s may reference columns in
				the other table

We already claim support for F311-01 in sql_features.txt, but based
on this entry I'd have to say that's a lie.  However, (a) the current
patchset fixes that, and (b) this seems like good evidence in support
of the idea that circular foreign keys are the only aspect of the
business that anyone cares about.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-05T20:15:03Z

Here's a revised patchset that I think is pretty close to committable.
I reorganized it some compared to v11: 0001 is still about the same,
but the business with allowing circular foreign keys is now in 0002,
because it seems like that bears directly on whether we should
consider 0001 acceptable.  And then I squashed all the object-type
additions into 0003, because dealing with them all at once seemed
simpler.

I rewrote 0002 rather heavily, mainly because it assumed it could
scribble on the input query tree which I don't think is okay.
The functionality is still the same though.

I was kind of sad that 0003 didn't support functions/procedures,
because (a) the SQL spec requires those in CREATE SCHEMA, and
(b) they are a flagship feature for Postgres, mainly because they
are such a critical aspect of extendability.  So I added that,
which only required adding CreateFunctionStmt to the set of
supported node types in transformCreateSchemaStmtElements.

I really didn't like the stringify_objtype() business in 0003:
it's messy and there is no guarantee that ObjectTypeMap will map
ObjectType codes the way you want them.  However, we can easily
get rid of that.  It was used for reporting "CREATE <objecttype>"
in error messages, but we don't really need that in the wrong-
schema-name message, as evidenced by the precedent of checkSchemaName
which has always just said "CREATE specifies a schema ...".
And as for the other usage of rejecting subclasses of DefineStmt,
let's just not reject them.  The existing other subclasses are
CREATE AGGREGATE and CREATE OPERATOR, which I think we should
support if we are supporting functions, plus text search objects.
I don't especially care whether CREATE SCHEMA allows CREATE TEXT
SEARCH commands, but I certainly don't see the value of adding
grungy code just to reject them.

While testing this I observed that including new-style SQL
functions/procedures in CREATE SCHEMA failed, because psql's heuristic
for determining when a semicolon ends the command didn't account for
this case.  So 0003 includes a patch for that, but it's even more
heuristic-y than before; I don't know if there are any plausible cases
where it'd misbehave.

psql's tab-complete support could stand to be improved more.
I fixed the CREATE TEXT SEARCH rules to change Matches to TailMatches,
but there are other places such as CREATE COLLATION that I didn't
touch because they have usages of HeadMatches plus TailMatches, and
it's not clear how to make those rules work.  I don't think that's
a commit-blocker, though.

As noted in the draft commit message for 0003, this puts us at
a point where we could reasonably claim to be feature-complete
for CREATE SCHEMA.  We support all the subcommand types required
by the spec, except for object types we don't implement at all
yet, plus CREATE CAST/TRANSFORM/ROLE.  Regarding those three
types of object as belonging to a schema seems problematic
for us, because they don't have schema-qualified names.
So I'm content to draw a line under this and say we're done.
Perhaps we should rewrite create_schema.sgml's Compatibility
section with that in mind, and with less apology for not
supporting command re-ordering.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-05T22:05:41Z

I wrote:
> Here's a revised patchset that I think is pretty close to committable.

The cfbot reminded me that I'd failed to check whether the regression
test outputs are stable across different collations.  Grumble.

Fixed that, and rewrote the changes in create_schema.sgml as I'd been
speculating about earlier.

			regards, tom lane

Re: CREATE SCHEMA ... CREATE DOMAIN support

jian he <jian.universality@gmail.com> — 2026-04-06T02:29:05Z

On Mon, Apr 6, 2026 at 4:15 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Here's a revised patchset that I think is pretty close to committable.
> I reorganized it some compared to v11: 0001 is still about the same,
> but the business with allowing circular foreign keys is now in 0002,
> because it seems like that bears directly on whether we should
> consider 0001 acceptable.  And then I squashed all the object-type
> additions into 0003, because dealing with them all at once seemed
> simpler.
>
> I rewrote 0002 rather heavily, mainly because it assumed it could
> scribble on the input query tree which I don't think is okay.
> The functionality is still the same though.
>

transformCreateSchemaStmtElements you mentioned

 * Note it's important that we not modify the input data structure.  We create
 * a new result List, and we copy any CREATE TABLE subcommands that we might
 * modify.

But there is still no explanation about why it's not ok to scribble on
the input query tree.
V13-0002 looks great, and it is way more intuitive than my approach!
Thanks!

+ DeconstructQualifiedName(qualified_name, &obj_schema, &obj_name);
+ if (obj_schema != NULL &&
+ strcmp(context_schema, obj_schema) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
+ errmsg("CREATE specifies a schema (%s) "
+ "different from the one being created (%s)",
+ obj_schema, context_schema)));

There is no tests for the above. It would be better to have one dummy
test verify the error message.

it would be better change to
+ errmsg("CREATE specifies a schema (%s) different from the one being
created (%s)",
It's better for regex pattern searching, for new line you need to know
how to escape.

+ (errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
The leading parenthesis is not needed.

> I was kind of sad that 0003 didn't support functions/procedures,
> because (a) the SQL spec requires those in CREATE SCHEMA, and
> (b) they are a flagship feature for Postgres, mainly because they
> are such a critical aspect of extendability.  So I added that,
> which only required adding CreateFunctionStmt to the set of
> supported node types in transformCreateSchemaStmtElements.
>

I guess at the time, I wasn't aggressive enough to consider CREATE
SCHEMA CREATE FUNCTION.

I saw the "Author" line in commit messages v13-0002 and v13-0003.
You should be added as an author on both v13-0002 and v13-0003.
Your changes simplified 0002 a lot, and you added the other CREATE
SCHEMA subcommands to 0003.

Except for the above issue, v13 all looks good to me.

--
jian
https://www.enterprisedb.com/

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-06T03:04:55Z

jian he <jian.universality@gmail.com> writes:
> On Mon, Apr 6, 2026 at 4:15 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I rewrote 0002 rather heavily, mainly because it assumed it could
>> scribble on the input query tree which I don't think is okay.

> But there is still no explanation about why it's not ok to scribble on
> the input query tree.

Because it might be in a cached plan tree and thus subject to being
re-used.  Now, you could sort of get away with that as long as
whatever changes you make are idempotent.  But palloc'ing new nodes
in the current memory context and then putting links to them into the
possibly-longer-lived source tree will not work.  On the whole I think
it'd be too fragile.

See also [1].  I didn't get a chance to make that happen during v19,
but it's still on the radar, and once it happens this sort of shortcut
would definitely fail.

> it would be better change to
> + errmsg("CREATE specifies a schema (%s) different from the one being
> created (%s)",
> It's better for regex pattern searching, for new line you need to know
> how to escape.

I made it look the same as the existing copy of the message.
I'm not too worried about the line break where it is; I doubt
anyone would be doing a code search that would fail because
of that.  Breaking in the middle of either phrase of the
message would be bad, agreed.

			regards, tom lane

[1] https://www.postgresql.org/message-id/2531459.1743871597%40sss.pgh.pa.us

Re: CREATE SCHEMA ... CREATE DOMAIN support

Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-06T19:19:48Z
```
I pushed v13 after a tiny bit of additional tweaking.

			regards, tom lane
```