Skip to content

Commit cf589c9

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
Regression tests for SCHEMA commands
Hari Babu Kommi reviewed by David Rowley
1 parent b921a26 commit cf589c9

File tree

4 files changed

+81
-14
lines changed

4 files changed

+81
-14
lines changed

‎src/test/regress/expected/namespace.out

Lines changed: 17 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -36,26 +36,34 @@ SELECT * FROM test_schema_1.abc_view;
3636
4 |
3737
(3 rows)
3838

39+
ALTER SCHEMA test_schema_1 RENAME TO test_schema_renamed;
40+
SELECT COUNT(*) FROM pg_class WHERE relnamespace =
41+
(SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1');
42+
count
43+
-------
44+
0
45+
(1 row)
46+
3947
-- test IF NOT EXISTS cases
40-
CREATE SCHEMA test_schema_1; -- fail, already exists
41-
ERROR: schema "test_schema_1" already exists
42-
CREATE SCHEMA IF NOT EXISTS test_schema_1; -- ok with notice
43-
NOTICE: schema "test_schema_1" already exists, skipping
44-
CREATE SCHEMA IF NOT EXISTS test_schema_1 -- fail, disallowed
48+
CREATE SCHEMA test_schema_renamed; -- fail, already exists
49+
ERROR: schema "test_schema_renamed" already exists
50+
CREATE SCHEMA IF NOT EXISTS test_schema_renamed; -- ok with notice
51+
NOTICE: schema "test_schema_renamed" already exists, skipping
52+
CREATE SCHEMA IF NOT EXISTS test_schema_renamed -- fail, disallowed
4553
CREATE TABLE abc (
4654
a serial,
4755
b int UNIQUE
4856
);
4957
ERROR: CREATE SCHEMA IF NOT EXISTS cannot include schema elements
5058
LINE 2: CREATE TABLE abc (
5159
^
52-
DROP SCHEMA test_schema_1 CASCADE;
60+
DROP SCHEMA test_schema_renamed CASCADE;
5361
NOTICE: drop cascades to 2 other objects
54-
DETAIL: drop cascades to table test_schema_1.abc
55-
drop cascades to view test_schema_1.abc_view
62+
DETAIL: drop cascades to table test_schema_renamed.abc
63+
drop cascades to view test_schema_renamed.abc_view
5664
-- verify that the objects were dropped
5765
SELECT COUNT(*) FROM pg_class WHERE relnamespace =
58-
(SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1');
66+
(SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_renamed');
5967
count
6068
-------
6169
0

‎src/test/regress/expected/privileges.out

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1350,6 +1350,34 @@ SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE')
13501350
SET client_min_messages TO 'warning';
13511351
DROP SCHEMA testns CASCADE;
13521352
RESET client_min_messages;
1353+
-- Change owner of the schema & and rename of new schema owner
1354+
\c -
1355+
CREATE ROLE schemauser1 superuser login;
1356+
CREATE ROLE schemauser2 superuser login;
1357+
SET SESSION ROLE schemauser1;
1358+
CREATE SCHEMA testns;
1359+
SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
1360+
nspname | rolname
1361+
---------+-------------
1362+
testns | schemauser1
1363+
(1 row)
1364+
1365+
ALTER SCHEMA testns OWNER TO schemauser2;
1366+
ALTER ROLE schemauser2 RENAME TO schemauser_renamed;
1367+
SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
1368+
nspname | rolname
1369+
---------+--------------------
1370+
testns | schemauser_renamed
1371+
(1 row)
1372+
1373+
set session role schemauser_renamed;
1374+
SET client_min_messages TO 'warning';
1375+
DROP SCHEMA testns CASCADE;
1376+
RESET client_min_messages;
1377+
-- clean up
1378+
\c -
1379+
DROP ROLE schemauser1;
1380+
DROP ROLE schemauser_renamed;
13531381
-- test that dependent privileges are revoked (or not) properly
13541382
\c -
13551383
set session role regressuser1;

‎src/test/regress/sql/namespace.sql

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -24,17 +24,21 @@ INSERT INTO test_schema_1.abc DEFAULT VALUES;
2424
SELECT * FROM test_schema_1.abc;
2525
SELECT * FROM test_schema_1.abc_view;
2626

27+
ALTER SCHEMA test_schema_1 RENAME TO test_schema_renamed;
28+
SELECT COUNT(*) FROM pg_class WHERE relnamespace =
29+
(SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1');
30+
2731
-- test IF NOT EXISTS cases
28-
CREATE SCHEMA test_schema_1; -- fail, already exists
29-
CREATE SCHEMA IF NOT EXISTS test_schema_1; -- ok with notice
30-
CREATE SCHEMA IF NOT EXISTS test_schema_1 -- fail, disallowed
32+
CREATE SCHEMA test_schema_renamed; -- fail, already exists
33+
CREATE SCHEMA IF NOT EXISTS test_schema_renamed; -- ok with notice
34+
CREATE SCHEMA IF NOT EXISTS test_schema_renamed -- fail, disallowed
3135
CREATE TABLE abc (
3236
a serial,
3337
b int UNIQUE
3438
);
3539

36-
DROP SCHEMA test_schema_1 CASCADE;
40+
DROP SCHEMA test_schema_renamed CASCADE;
3741

3842
-- verify that the objects were dropped
3943
SELECT COUNT(*) FROM pg_class WHERE relnamespace =
40-
(SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1');
44+
(SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_renamed');

‎src/test/regress/sql/privileges.sql

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -813,6 +813,33 @@ DROP SCHEMA testns CASCADE;
813813
RESET client_min_messages;
814814

815815

816+
-- Change owner of the schema & and rename of new schema owner
817+
\c -
818+
819+
CREATE ROLE schemauser1 superuser login;
820+
CREATE ROLE schemauser2 superuser login;
821+
822+
SET SESSION ROLE schemauser1;
823+
CREATE SCHEMA testns;
824+
825+
SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
826+
827+
ALTER SCHEMA testns OWNER TO schemauser2;
828+
ALTER ROLE schemauser2 RENAME TO schemauser_renamed;
829+
SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
830+
831+
set session role schemauser_renamed;
832+
SET client_min_messages TO 'warning';
833+
DROP SCHEMA testns CASCADE;
834+
RESET client_min_messages;
835+
836+
-- clean up
837+
\c -
838+
839+
DROP ROLE schemauser1;
840+
DROP ROLE schemauser_renamed;
841+
842+
816843
-- test that dependent privileges are revoked (or not) properly
817844
\c -
818845

0 commit comments

Comments
 (0)