1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
--
-- Regression tests for schemas (namespaces)
--
-- set the whitespace-only search_path to test that the
-- GUC list syntax is preserved during a schema creation
SELECT pg_catalog.set_config('search_path', ' ', false);
set_config
------------
(1 row)
CREATE SCHEMA test_ns_schema_1
CREATE UNIQUE INDEX abc_a_idx ON abc (a)
CREATE VIEW abc_view AS
SELECT a+1 AS a, b+1 AS b FROM abc
CREATE TABLE abc (
a serial,
b int UNIQUE
);
-- verify that the correct search_path restored on abort
SET search_path to public;
BEGIN;
SET search_path to public, test_ns_schema_1;
CREATE SCHEMA test_ns_schema_2
CREATE VIEW abc_view AS SELECT c FROM abc;
ERROR: column "c" does not exist
LINE 2: CREATE VIEW abc_view AS SELECT c FROM abc;
^
COMMIT;
SHOW search_path;
search_path
-------------
public
(1 row)
-- verify that the correct search_path preserved
-- after creating the schema and on commit
BEGIN;
SET search_path to public, test_ns_schema_1;
CREATE SCHEMA test_ns_schema_2
CREATE VIEW abc_view AS SELECT a FROM abc;
SHOW search_path;
search_path
--------------------------
public, test_ns_schema_1
(1 row)
COMMIT;
SHOW search_path;
search_path
--------------------------
public, test_ns_schema_1
(1 row)
DROP SCHEMA test_ns_schema_2 CASCADE;
NOTICE: drop cascades to view test_ns_schema_2.abc_view
-- verify that the objects were created
SELECT COUNT(*) FROM pg_class WHERE relnamespace =
(SELECT oid FROM pg_namespace WHERE nspname = 'test_ns_schema_1');
count
-------
5
(1 row)
INSERT INTO test_ns_schema_1.abc DEFAULT VALUES;
INSERT INTO test_ns_schema_1.abc DEFAULT VALUES;
INSERT INTO test_ns_schema_1.abc DEFAULT VALUES;
SELECT * FROM test_ns_schema_1.abc;
a | b
---+---
1 |
2 |
3 |
(3 rows)
SELECT * FROM test_ns_schema_1.abc_view;
a | b
---+---
2 |
3 |
4 |
(3 rows)
ALTER SCHEMA test_ns_schema_1 RENAME TO test_ns_schema_renamed;
SELECT COUNT(*) FROM pg_class WHERE relnamespace =
(SELECT oid FROM pg_namespace WHERE nspname = 'test_ns_schema_1');
count
-------
0
(1 row)
-- test IF NOT EXISTS cases
CREATE SCHEMA test_ns_schema_renamed; -- fail, already exists
ERROR: schema "test_ns_schema_renamed" already exists
CREATE SCHEMA IF NOT EXISTS test_ns_schema_renamed; -- ok with notice
NOTICE: schema "test_ns_schema_renamed" already exists, skipping
CREATE SCHEMA IF NOT EXISTS test_ns_schema_renamed -- fail, disallowed
CREATE TABLE abc (
a serial,
b int UNIQUE
);
ERROR: CREATE SCHEMA IF NOT EXISTS cannot include schema elements
LINE 2: CREATE TABLE abc (
^
DROP SCHEMA test_ns_schema_renamed CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table test_ns_schema_renamed.abc
drop cascades to view test_ns_schema_renamed.abc_view
-- verify that the objects were dropped
SELECT COUNT(*) FROM pg_class WHERE relnamespace =
(SELECT oid FROM pg_namespace WHERE nspname = 'test_ns_schema_renamed');
count
-------
0
(1 row)
--
-- Verify that search_path is set to a safe value during maintenance
-- commands.
--
CREATE SCHEMA test_maint_search_path;
SET search_path = test_maint_search_path;
CREATE FUNCTION fn(INT) RETURNS INT IMMUTABLE LANGUAGE plpgsql AS $$
BEGIN
RAISE NOTICE 'current search_path: %', current_setting('search_path');
RETURN $1;
END;
$$;
CREATE TABLE test_maint(i INT);
INSERT INTO test_maint VALUES (1), (2);
CREATE MATERIALIZED VIEW test_maint_mv AS SELECT fn(i) FROM test_maint;
NOTICE: current search_path: pg_catalog, pg_temp
NOTICE: current search_path: pg_catalog, pg_temp
-- the following commands should see search_path as pg_catalog, pg_temp
CREATE INDEX test_maint_idx ON test_maint_search_path.test_maint (fn(i));
NOTICE: current search_path: pg_catalog, pg_temp
NOTICE: current search_path: pg_catalog, pg_temp
REINDEX TABLE test_maint_search_path.test_maint;
NOTICE: current search_path: pg_catalog, pg_temp
NOTICE: current search_path: pg_catalog, pg_temp
ANALYZE test_maint_search_path.test_maint;
NOTICE: current search_path: pg_catalog, pg_temp
NOTICE: current search_path: pg_catalog, pg_temp
VACUUM FULL test_maint_search_path.test_maint;
NOTICE: current search_path: pg_catalog, pg_temp
NOTICE: current search_path: pg_catalog, pg_temp
CLUSTER test_maint_search_path.test_maint USING test_maint_idx;
NOTICE: current search_path: pg_catalog, pg_temp
NOTICE: current search_path: pg_catalog, pg_temp
NOTICE: current search_path: pg_catalog, pg_temp
NOTICE: current search_path: pg_catalog, pg_temp
REFRESH MATERIALIZED VIEW test_maint_search_path.test_maint_mv;
NOTICE: current search_path: pg_catalog, pg_temp
NOTICE: current search_path: pg_catalog, pg_temp
RESET search_path;
DROP SCHEMA test_maint_search_path CASCADE;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to function test_maint_search_path.fn(integer)
drop cascades to table test_maint_search_path.test_maint
drop cascades to materialized view test_maint_search_path.test_maint_mv
|
