[ 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
