Re: CREATE SCHEMA ... CREATE DOMAIN support

Noah Misch <noah(at)leadboat(dot)com> writes:
> I had Opus 5 review d516974 "Support more object types within CREATE SCHEMA".

Thanks for doing that.

> Here's one that resonates more with me, adapted slightly from the a9c350d
> commit message:

> CREATE TABLE public.mytable (c int);
> CREATE SCHEMA myschema
> CREATE VIEW myview AS SELECT * FROM mytable
> CREATE TABLE mytable (c int);
> SET search_path = pg_catalog;
> \d+ myschema.myview
> RESET search_path;

> In v18, myview binds to myschema.mytable. In v19, it binds to public.mytable.
> I think the above-described syntax rule about <local or schema qualified name>
> doesn't allow v19's interpretation.

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. Frankly, I don't care about arguments
that this is not what the spec says: I think this behavior is more
useful, more intelligible, and less bug-prone than the spec's
semantics. Moreover we were never going to be able to do what the spec
says, as discussed in the thread leading up to that commit.

> Stepping back, I am concerned about the complexity v19 is incurring to extend
> CREATE SCHEMA ... CREATE. The syntax gives only modest benefits, since you
> can achieve the same object creation with freestanding SQL statements. The
> above example is indicative of the unusual semantic challenges. CREATE SCHEMA
> ... CREATE also handicaps the parsing of every command it supports, since
> parsing can't rely on semicolons to find command boundaries.

True, but the spec is constrained by that too, so they won't be
inventing syntax that breaks the ability to do it. We were somewhat
fortunate that we'd not yet invented any extension syntax that would
break that ability, but we haven't, and now bison will tell us if
we try.

> ... this is a variant of an older bug of psql detecting "begin" in the
> wrong contexts:
> -- psql wrongly concludes command is not yet terminated
> CREATE FUNCTION x1f() RETURNS int LANGUAGE sql SET search_path = begin AS 'select 1';
> select 'ended';

Yeah. TBH, I think the right answer to that is to make BEGIN a
reserved word. It is so in the standard, and has been since SQL92.
If we don't, there are probably always going to be ways to confuse
psql like this (and I rather imagine that many of them were reachable
before v19).

>> +### D2 — a `SET role` / `SET session_authorization` clause on a routine element is refused under `AUTHORIZATION`, naming a security-definer function that does not exist

> I would not act on this.

Hmm, that one seems like a genuine bug to me; why do you think it
shouldn't be acted on?

>> +### D3 — PL/pgSQL cannot contain a `CREATE SCHEMA` whose element is a `BEGIN ATOMIC` routine

> I think this does qualify as a bug in d516974, but it's not very concerning.

That probably has something to do with the fact that BEGIN *is* a
reserved word to plpgsql. I agree it's not high priority, but
maybe it's worth looking at.

>> +### D4 — psql offers `COLLATION` and `TYPE` as `CREATE SCHEMA` clauses but does not complete them

> I would not act on this.

Agreed; we don't have particularly useful completion for those
commands at outer level, either.

regards, tom lane