Re: CREATE SCHEMA ... CREATE DOMAIN support

Kirill Reshke <reshkekirill(at)gmail(dot)com> writes:
> On Wed, 4 Dec 2024 at 01:07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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