On Sun, 1 Dec 2024 at 04:33, Tom Lane <t...@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
v2-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-S.patch
Description: Binary data
