src/test/regress/expected/namespace_gp.out

--
-- GPDB added regression tests for schemas (namespaces)
--
CREATE SCHEMA test_schema_1
       CREATE TABLE abc (
              a int,
              b int UNIQUE
       ) DISTRIBUTED BY (b);
-- Test GRANT/REVOKE
CREATE SCHEMA test_schema_2;
CREATE TABLE test_schema_2.abc as select * from test_schema_1.abc DISTRIBUTED BY (a);
create role tmp_test_schema_role RESOURCE QUEUE pg_default;
GRANT ALL ON SCHEMA test_schema_1 to tmp_test_schema_role;
SET SESSION AUTHORIZATION tmp_test_schema_role;
CREATE TABLE test_schema_1.grant_test(a int) DISTRIBUTED BY (a);
DROP TABLE test_schema_1.grant_test;
CREATE TABLE test_schema_2.grant_test(a int) DISTRIBUTED BY (a); -- no permissions on schema
ERROR:  permission denied for schema test_schema_2
LINE 1: CREATE TABLE test_schema_2.grant_test(a int) DISTRIBUTED BY ...
                     ^
SELECT * FROM test_schema_1.abc; -- no permissions on table
ERROR:  permission denied for table abc
SELECT * FROM test_schema_2.abc; -- no permissions on schema
ERROR:  permission denied for schema test_schema_2
LINE 1: SELECT * FROM test_schema_2.abc;
                      ^
ALTER SCHEMA test_schema_1 RENAME to myschema;  -- not the schema owner
ERROR:  must be owner of schema test_schema_1
RESET SESSION AUTHORIZATION;
DROP TABLE test_schema_2.abc;
DROP SCHEMA test_schema_2;
-- ALTER SCHEMA .. OWNER TO
ALTER SCHEMA pg_toast OWNER to tmp_test_schema_role; -- system schema
ERROR:  permission denied to ALTER SCHEMA "pg_toast"
DETAIL:  Schema pg_toast is reserved for system use.
alter schema test_schema_1 owner to tmp_test_schema_role;
select rolname from pg_authid a, pg_namespace n where a.oid = n.nspowner
  and nspname = 'test_schema_1';
       rolname        
----------------------
 tmp_test_schema_role
(1 row)

-- test CREATE SCHEMA/ALTER SCHEMA for reserved names
CREATE SCHEMA pg_schema; -- reserved name
ERROR:  unacceptable schema name "pg_schema"
DETAIL:  The prefix "pg_" is reserved for system schemas.
CREATE SCHEMA gp_schema; -- reserved name
ERROR:  unacceptable schema name "gp_schema"
DETAIL:  The prefix "gp_" is reserved for system schemas.
ALTER SCHEMA test_schema_1 RENAME to pg_schema; -- reseved name
ERROR:  unacceptable schema name "pg_schema"
DETAIL:  The prefix "pg_" is reserved for system schemas.
ALTER SCHEMA test_schema_1 RENAME to gp_schema; -- reserved name
ERROR:  unacceptable schema name "gp_schema"
DETAIL:  The prefix "gp_" is reserved for system schemas.
ALTER SCHEMA pg_toast RENAME to bread;  -- system schema
ERROR:  permission denied to ALTER SCHEMA "pg_toast"
DETAIL:  Schema pg_toast is reserved for system use.
-- RENAME to a valid new name
ALTER SCHEMA test_schema_1 RENAME to test_schema_2;
-- Check that ALTER statements dispatched correctly
select *
FROM gp_dist_random('pg_namespace') n1
  full outer join pg_namespace n2 on (n1.oid = n2.oid)
WHERE n1.nspname != n2.nspname or n1.nspowner != n2.nspowner or
      n1.nspname is null or n2.nspname is null;
 oid | nspname | nspowner | nspacl | oid | nspname | nspowner | nspacl 
-----+---------+----------+--------+-----+---------+----------+--------
(0 rows)

-- DROP SCHEMA
DROP SCHEMA pg_toast;       -- system schema
ERROR:  cannot drop schema pg_toast because it is required by the database system
DROP SCHEMA test_schema_1;  -- does not exist
ERROR:  schema "test_schema_1" does not exist
DROP SCHEMA test_schema_2;  -- contains objects
ERROR:  cannot drop schema test_schema_2 because other objects depend on it
DETAIL:  table test_schema_2.abc depends on schema test_schema_2
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP SCHEMA test_schema_2 CASCADE;
NOTICE:  drop cascades to table test_schema_2.abc
DROP ROLE tmp_test_schema_role;
-- verify that the objects were dropped
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname ~ 'test_schema_[12]';
 nspname | relname 
---------+---------
(0 rows)

SELECT nspname
FROM pg_namespace n
WHERE nspname ~ 'test_schema_[12]';
 nspname 
---------
(0 rows)

SELECT rolname
FROM pg_authid a
WHERE rolname ~ 'tmp_test_schema_role';
 rolname 
---------
(0 rows)

-- OIDs for these built-in namespaces should not change. pg_upgrade
-- fails if OID for these schemas mismatch across major versions.
-- Mainly preassigned oid lookup uses these oids in key hence object
-- lookup fails if it differs between source and destination cluster.
SELECT oid, nspname FROM pg_namespace
WHERE nspname IN ('pg_catalog', 'pg_toast', 'pg_aoseg', 'pg_bitmapindex')
ORDER BY oid;
 oid  |    nspname     
------+----------------
   11 | pg_catalog
   99 | pg_toast
 7012 | pg_bitmapindex
 7134 | pg_aoseg
(4 rows)