[ 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
