[YSQL] Support for CREATE SCHEMA with elements

[tedyu]

Jira Link: DB-1252

Description

Currently the following syntax is not supported:

CREATE SCHEMA foo
    CREATE TABLE foo.bar (id int)
    CREATE TABLE foo.baz (id int);

[bllewell]

See Issue #8863 «[YB-Docs] example on “create schema” page fails». It describes the same underlying product bug.

[bllewell]

The test that's described below was run after starting my YB cluster with this YSQL configuration parameter setting:

ysql_suppress_unsupported_error=true

The default for this parameter is false. And when the default setting is used, any 0A000 occurrence, that, below, is a warning and that I suppress by setting client_min_messages to error, is a genuine error—meaning that any statement that causes 0A000 rolls back and has no effect.

I did the test using YB-2.17.0.0 (in a Ubuntu 22/04 VM).

First save these metadata queries to a file:

-- Save as "metadata-qry.sql".
select n.nspname as schema_name, r.rolname as owner_name
from pg_namespace as n inner join pg_roles as r on n.nspowner = r.oid
where n.nspname ~ '^d0\$u';

select
  c.relname as name,
  case c.relkind when 'r' then 'table' when 'v' then 'view' end  as kind,
  n.nspname as schema_name,
  r.rolname as owner_name
from
  pg_class as c
  inner join pg_namespace as n
  on c.relnamespace = n.oid
  inner join
  pg_roles as r
  on c.relowner = r.oid
where c.relname = any(array['t', 'v'])
order by 1, 2, 3, 4;

Now do this:

/*
  Connect to a suitable sandbox database (e.g. "d0") as a superuser (e.g. "yugabyte")
  and ensure that two suitable roles (e.g. "d0$u0" and "d0$u1") exist and have
  "connect" and "create" on "d0". Make sure that the schemas "d0$u0" and "d0$u1"
  don't yet exist.
*/;
set client_min_messages = error;

create schema authorization d0$u0
  create table t(k serial primary key, v int not null)
  create view v(k, v) as select k, v from t where k > 10;

\ir metadata-qry.sql

set client_min_messages = warning;
alter schema d0$u0 rename to d0$u1;
alter schema d0$u1 owner  to d0$u1;

\ir metadata-qry.sql

Here are the results of the catalog queries. First:

 schema_name | owner_name 
-------------+------------
 d0$u0       | d0$u0

 name | kind  | schema_name | owner_name 
------+-------+-------------+------------
 t    | table | d0$u0       | d0$u0
 v    | view  | d0$u0       | d0$u0

Then:

 schema_name | owner_name 
-------------+------------
 d0$u1       | d0$u1

 name | kind  | schema_name | owner_name 
------+-------+-------------+------------
 t    | table | d0$u1       | d0$u0
 v    | view  | d0$u1       | d0$u0

[FranckPachot]

This error is raised by https://github.com/stripe/pg-schema-diff