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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
|
--
-- test ALTER THING name DEPENDS ON EXTENSION
--
-- Common setup for all tests
CREATE TABLE test_extdep_commands (command text);
COPY test_extdep_commands FROM stdin;
SELECT * FROM test_extdep_commands;
command
-------------------------------------------------------------------------
CREATE SCHEMA test_ext
CREATE EXTENSION test_ext5 SCHEMA test_ext
SET search_path TO test_ext
CREATE TABLE a (a1 int)
CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS +
$$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
CREATE MATERIALIZED VIEW d AS SELECT * FROM a
ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
CREATE INDEX e ON a (a1)
ALTER INDEX e DEPENDS ON EXTENSION test_ext5
RESET search_path
(17 rows)
-- First, test that dependent objects go away when the extension is dropped.
SELECT * FROM test_extdep_commands \gexec
CREATE SCHEMA test_ext
CREATE EXTENSION test_ext5 SCHEMA test_ext
SET search_path TO test_ext
CREATE TABLE a (a1 int)
CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS
$$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
CREATE MATERIALIZED VIEW d AS SELECT * FROM a
ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
CREATE INDEX e ON a (a1)
ALTER INDEX e DEPENDS ON EXTENSION test_ext5
RESET search_path
-- A dependent object made dependent again has no effect
ALTER FUNCTION test_ext.b() DEPENDS ON EXTENSION test_ext5;
-- make sure we have the right dependencies on the extension
SELECT deptype, p.*
FROM pg_depend, pg_identify_object(classid, objid, objsubid) AS p
WHERE refclassid = 'pg_extension'::regclass AND
refobjid = (SELECT oid FROM pg_extension WHERE extname = 'test_ext5')
ORDER BY type;
deptype | type | schema | name | identity
---------+-------------------+----------+------+-----------------
x | function | test_ext | | test_ext.b()
x | index | test_ext | e | test_ext.e
x | materialized view | test_ext | d | test_ext.d
x | trigger | | | c on test_ext.a
(4 rows)
DROP EXTENSION test_ext5;
-- anything still depending on the table?
SELECT deptype, i.*
FROM pg_catalog.pg_depend, pg_identify_object(classid, objid, objsubid) i
WHERE refclassid='pg_class'::regclass AND
refobjid='test_ext.a'::regclass AND NOT deptype IN ('i', 'a');
deptype | type | schema | name | identity
---------+------+--------+------+----------
(0 rows)
DROP SCHEMA test_ext CASCADE;
NOTICE: drop cascades to table test_ext.a
-- Second test: If we drop the table, the objects are dropped too and no
-- vestige remains in pg_depend.
SELECT * FROM test_extdep_commands \gexec
CREATE SCHEMA test_ext
CREATE EXTENSION test_ext5 SCHEMA test_ext
SET search_path TO test_ext
CREATE TABLE a (a1 int)
CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS
$$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
CREATE MATERIALIZED VIEW d AS SELECT * FROM a
ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
CREATE INDEX e ON a (a1)
ALTER INDEX e DEPENDS ON EXTENSION test_ext5
RESET search_path
DROP TABLE test_ext.a; -- should fail, require cascade
ERROR: cannot drop table test_ext.a because other objects depend on it
DETAIL: materialized view test_ext.d depends on table test_ext.a
HINT: Use DROP ... CASCADE to drop the dependent objects too.
DROP TABLE test_ext.a CASCADE;
NOTICE: drop cascades to materialized view test_ext.d
-- anything still depending on the extension? Should be only function b()
SELECT deptype, i.*
FROM pg_catalog.pg_depend, pg_identify_object(classid, objid, objsubid) i
WHERE refclassid='pg_extension'::regclass AND
refobjid=(SELECT oid FROM pg_extension WHERE extname='test_ext5');
deptype | type | schema | name | identity
---------+----------+----------+------+--------------
x | function | test_ext | | test_ext.b()
(1 row)
DROP EXTENSION test_ext5;
DROP SCHEMA test_ext CASCADE;
-- Third test: we can drop the objects individually
SELECT * FROM test_extdep_commands \gexec
CREATE SCHEMA test_ext
CREATE EXTENSION test_ext5 SCHEMA test_ext
SET search_path TO test_ext
CREATE TABLE a (a1 int)
CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS
$$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
CREATE MATERIALIZED VIEW d AS SELECT * FROM a
ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
CREATE INDEX e ON a (a1)
ALTER INDEX e DEPENDS ON EXTENSION test_ext5
RESET search_path
SET search_path TO test_ext;
DROP TRIGGER c ON a;
DROP FUNCTION b();
DROP MATERIALIZED VIEW d;
DROP INDEX e;
SELECT deptype, i.*
FROM pg_catalog.pg_depend, pg_identify_object(classid, objid, objsubid) i
WHERE (refclassid='pg_extension'::regclass AND
refobjid=(SELECT oid FROM pg_extension WHERE extname='test_ext5'))
OR (refclassid='pg_class'::regclass AND refobjid='test_ext.a'::regclass)
AND NOT deptype IN ('i', 'a');
deptype | type | schema | name | identity
---------+------+--------+------+----------
(0 rows)
DROP TABLE a;
RESET search_path;
DROP SCHEMA test_ext CASCADE;
NOTICE: drop cascades to extension test_ext5
-- Fourth test: we can mark the objects as dependent, then unmark; then the
-- drop of the extension does nothing
SELECT * FROM test_extdep_commands \gexec
CREATE SCHEMA test_ext
CREATE EXTENSION test_ext5 SCHEMA test_ext
SET search_path TO test_ext
CREATE TABLE a (a1 int)
CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS
$$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
CREATE MATERIALIZED VIEW d AS SELECT * FROM a
ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
CREATE INDEX e ON a (a1)
ALTER INDEX e DEPENDS ON EXTENSION test_ext5
RESET search_path
SET search_path TO test_ext;
ALTER FUNCTION b() NO DEPENDS ON EXTENSION test_ext5;
ALTER TRIGGER c ON a NO DEPENDS ON EXTENSION test_ext5;
ALTER MATERIALIZED VIEW d NO DEPENDS ON EXTENSION test_ext5;
ALTER INDEX e NO DEPENDS ON EXTENSION test_ext5;
DROP EXTENSION test_ext5;
DROP TRIGGER c ON a;
DROP FUNCTION b();
DROP MATERIALIZED VIEW d;
DROP INDEX e;
DROP SCHEMA test_ext CASCADE;
NOTICE: drop cascades to table a
|
