Re: CREATE SCHEMA ... CREATE DOMAIN support

On Sun, 1 Dec 2024 at 04:33, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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