Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcf589c9

Browse files
Regression tests for SCHEMA commands
Hari Babu Kommi reviewed by David Rowley
1 parentb921a26 commitcf589c9

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 SCHEMAtest_schema_1; -- fail, already exists
41-
ERROR: schema "test_schema_1" already exists
42-
CREATE SCHEMA IF NOT EXISTStest_schema_1; -- ok with notice
43-
NOTICE: schema "test_schema_1" already exists, skipping
44-
CREATE SCHEMA IF NOT EXISTStest_schema_1 -- fail, disallowed
48+
CREATE SCHEMAtest_schema_renamed; -- fail, already exists
49+
ERROR: schema "test_schema_renamed" already exists
50+
CREATE SCHEMA IF NOT EXISTStest_schema_renamed; -- ok with notice
51+
NOTICE: schema "test_schema_renamed" already exists, skipping
52+
CREATE SCHEMA IF NOT EXISTStest_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 SCHEMAtest_schema_1 CASCADE;
60+
DROP SCHEMAtest_schema_renamed CASCADE;
5361
NOTICE: drop cascades to 2 other objects
54-
DETAIL: drop cascades to tabletest_schema_1.abc
55-
drop cascades to viewtest_schema_1.abc_view
62+
DETAIL: drop cascades to tabletest_schema_renamed.abc
63+
drop cascades to viewtest_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*FROMtest_schema_1.abc;
2525
SELECT*FROMtest_schema_1.abc_view;
2626

27+
ALTERSCHEMA test_schema_1 RENAME TO test_schema_renamed;
28+
SELECTCOUNT(*)FROM pg_classWHERE relnamespace=
29+
(SELECToidFROM pg_namespaceWHERE nspname='test_schema_1');
30+
2731
-- test IF NOT EXISTS cases
28-
CREATESCHEMAtest_schema_1;-- fail, already exists
29-
CREATESCHEMAIF NOT EXISTStest_schema_1;-- ok with notice
30-
CREATESCHEMAIF NOT EXISTStest_schema_1-- fail, disallowed
32+
CREATESCHEMAtest_schema_renamed;-- fail, already exists
33+
CREATESCHEMAIF NOT EXISTStest_schema_renamed;-- ok with notice
34+
CREATESCHEMAIF NOT EXISTStest_schema_renamed-- fail, disallowed
3135
CREATETABLEabc (
3236
aserial,
3337
bint UNIQUE
3438
);
3539

36-
DROPSCHEMAtest_schema_1 CASCADE;
40+
DROPSCHEMAtest_schema_renamed CASCADE;
3741

3842
-- verify that the objects were dropped
3943
SELECTCOUNT(*)FROM pg_classWHERE relnamespace=
40-
(SELECToidFROM pg_namespaceWHERE nspname='test_schema_1');
44+
(SELECToidFROM pg_namespaceWHERE 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+
CREATESCHEMAtestns;
824+
825+
SELECT nspname, rolnameFROM pg_namespace, pg_rolesWHEREpg_namespace.nspname='testns'ANDpg_namespace.nspowner=pg_roles.oid;
826+
827+
ALTERSCHEMA testns OWNER TO schemauser2;
828+
ALTER ROLE schemauser2 RENAME TO schemauser_renamed;
829+
SELECT nspname, rolnameFROM pg_namespace, pg_rolesWHEREpg_namespace.nspname='testns'ANDpg_namespace.nspowner=pg_roles.oid;
830+
831+
set session role schemauser_renamed;
832+
SET client_min_messages TO'warning';
833+
DROPSCHEMA 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)
[8]ページ先頭
©2009-2025 Movatter.jp