Re: CREATE SCHEMA ... CREATE DOMAIN support - Mailing list pgsql-hackers

On Thu, Dec 12, 2024 at 1:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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?
>

hi.
move this forward with option #3 (executing the subcommands in order).
pg_dump don't use CREATE SCHEMA ...CREATE ...
so if we error out
CREATE SCHEMA regress_schema_2 CREATE VIEW abcd_view AS SELECT a FROM
abcd CREATE TABLE abcd (a int);
it won't be a big compatibility issue?
Also this thread doesn’t show strong support for sorting the subcommands.

the full <schema definition> in 11.1 is:
11.1 <schema definition>

<schema element> ::=
<table definition>
| <view definition>
| <domain definition>
| <character set definition>
| <collation definition>
| <transliteration definition>
| <assertion definition>
| <trigger definition>
| <user-defined type definition>
| <user-defined cast definition>
| <user-defined ordering definition>
| <transform definition>
| <schema routine>
| <sequence generator definition>
| <grant statement>
| <role definition>

so I also add support for CREATE SCHEMA CREATE COLLATION.

v6-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch
v6-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
v6-0003-CREATE-SCHEMA-CREATE-COLLATION.patch

v6-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-SCHEMA.patch
is refactor/rebase based on
v1-0001-Don-t-try-to-re-order-the-subcommands-of-CREATE-S.patch

v6-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
for CREATE SCHEMA ... CREATE-DOMAIN

v6-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
for CREATE SCHEMA ... CREATE-COLLATION