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
|
--
-- 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;
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\n $$ 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
\.
SELECT * FROM test_extdep_commands;
-- First, test that dependent objects go away when the extension is dropped.
SELECT * FROM test_extdep_commands \gexec
-- 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;
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');
DROP SCHEMA test_ext CASCADE;
-- 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
DROP TABLE test_ext.a; -- should fail, require cascade
DROP TABLE test_ext.a CASCADE;
-- 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');
DROP EXTENSION test_ext5;
DROP SCHEMA test_ext CASCADE;
-- Third test: we can drop the objects individually
SELECT * FROM test_extdep_commands \gexec
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');
DROP TABLE a;
DROP SCHEMA test_ext CASCADE;
|
