Re: CREATE SCHEMA ... CREATE DOMAIN support

On Fri, Sep 04, 2026 at 10:21:52AM -0400, Tom Lane wrote:
> Noah Misch <noah(at)leadboat(dot)com> writes:
> > On Thu, Sep 03, 2026 at 09:00:26PM -0400, Tom Lane wrote:
> >> This is not about that, it is about a9c350d's specific intent "Don't
> >> try to re-order the subcommands of CREATE SCHEMA". That is, we
> >> rejected the idea of allowing forward references between CREATE SCHEMA
> >> subcommands, therefore myview will not bind to a CREATE TABLE
> >> subcommand that comes after it.
>
> > I'm not saying that I want the server to reorder the commands or that I know
> > of a standard requiring reorder. I'm fine requiring the user to order things
> > for us. The syntax rule I cited[1] appears to require my original example to
> > have the same name resolution as this variant w/ more schema qualifications:
>
> > CREATE TABLE public.mytable (c int);
> > CREATE SCHEMA myschema
> > CREATE VIEW myschema.myview AS SELECT * FROM myschema.mytable
> > CREATE TABLE myschema.mytable (c int);
> > SET search_path = pg_catalog;
> > \d+ myschema.myview
> > RESET search_path;
>
> > That succeeds in v18 and fails in v19, which is fine. The problem is that my
> > original example (with less schema qualification) succeeds in both versions
> > with different meaning, and the v19 meaning is not standard-compliant.
>
> What would you have us do differently?

I recommend reverting the v19 CREATE SCHEMA changes, unfortunately. The
thread started with what smelled like low-hanging fruit: let users write
"CREATE SCHEMA myschema CREATE DOMAIN mydomain" instead of "CREATE SCHEMA
myschema; CREATE DOMAIN myschema.mydomain". That led to a9c350d silently
changing the meaning of CREATE SCHEMA statements that v18 already accepts.
Since the benefit of supporting "CREATE SCHEMA myschema CREATE DOMAIN
mydomain" is so modest, it doesn't take much of a problem to outweigh that
benefit.

> As near as I can tell, the spec's wording is untenable anyway: it
> appears to say that names within a CREATE SCHEMA can never refer to
> objects outside the new schema unless explicitly schema-qualified.
> Half a moment's thought shows that that's unworkable for Postgres:
>
> CREATE SCHEMA s1
> CREATE TABLE t1 (f1 int)
> CREATE VIEW v1 AS SELECT abs(f1) AS a, f1 + 1 AS b FROM t1;
>
> With that interpretation, both this reference to pg_catalog.abs()
> and this reference to operator(pg_catalog.+) must fail. I don't
> think anyone's going to consider that behavior better. But as
> soon as you admit that "oh, pg_catalog must be implicitly in the
> search path too", you might as well admit that our existing behavior
> where the whole search_path is implicitly available is okay.

It's a tarpit for sure. I don't think the benefit of supporting more object
types in CREATE SCHEMA warrants sorting all of that out. Let's just keep
writing multiple SQL statements.