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
|
--
-- 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;
DROP SCHEMA test_ext CASCADE;
NOTICE: drop cascades to extension test_ext5
|
