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
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
|
-- should fail, return type mismatch
create event trigger regress_event_trigger
on ddl_command_start
execute procedure pg_backend_pid();
-- OK
create function test_event_trigger() returns event_trigger as $$
BEGIN
RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
END
$$ language plpgsql;
-- should fail, no elephant_bootstrap entry point
create event trigger regress_event_trigger on elephant_bootstrap
execute procedure test_event_trigger();
-- OK
create event trigger regress_event_trigger on ddl_command_start
execute procedure test_event_trigger();
-- OK
create event trigger regress_event_trigger_end on ddl_command_end
execute procedure test_event_trigger();
-- should fail, food is not a valid filter variable
create event trigger regress_event_trigger2 on ddl_command_start
when food in ('sandwhich')
execute procedure test_event_trigger();
-- should fail, sandwhich is not a valid command tag
create event trigger regress_event_trigger2 on ddl_command_start
when tag in ('sandwhich')
execute procedure test_event_trigger();
-- should fail, create skunkcabbage is not a valid comand tag
create event trigger regress_event_trigger2 on ddl_command_start
when tag in ('create table', 'create skunkcabbage')
execute procedure test_event_trigger();
-- should fail, can't have event triggers on event triggers
create event trigger regress_event_trigger2 on ddl_command_start
when tag in ('DROP EVENT TRIGGER')
execute procedure test_event_trigger();
-- should fail, can't have same filter variable twice
create event trigger regress_event_trigger2 on ddl_command_start
when tag in ('create table') and tag in ('CREATE FUNCTION')
execute procedure test_event_trigger();
-- OK
create event trigger regress_event_trigger2 on ddl_command_start
when tag in ('create table', 'CREATE FUNCTION')
execute procedure test_event_trigger();
-- OK
comment on event trigger regress_event_trigger is 'test comment';
-- should fail, event triggers are not schema objects
comment on event trigger wrong.regress_event_trigger is 'test comment';
-- drop as non-superuser should fail
create role regression_bob;
set role regression_bob;
create event trigger regress_event_trigger_noperms on ddl_command_start
execute procedure test_event_trigger();
reset role;
-- all OK
alter event trigger regress_event_trigger enable replica;
alter event trigger regress_event_trigger enable always;
alter event trigger regress_event_trigger enable;
alter event trigger regress_event_trigger disable;
-- regress_event_trigger2 and regress_event_trigger_end should fire, but not
-- regress_event_trigger
create table event_trigger_fire1 (a int);
-- regress_event_trigger_end should fire here
drop table event_trigger_fire1;
-- alter owner to non-superuser should fail
alter event trigger regress_event_trigger owner to regression_bob;
-- alter owner to superuser should work
alter role regression_bob superuser;
alter event trigger regress_event_trigger owner to regression_bob;
-- should fail, name collision
alter event trigger regress_event_trigger rename to regress_event_trigger2;
-- OK
alter event trigger regress_event_trigger rename to regress_event_trigger3;
-- should fail, doesn't exist any more
drop event trigger regress_event_trigger;
-- should fail, regression_bob owns regress_event_trigger2/3
drop role regression_bob;
-- cleanup before next test
-- these are all OK; the second one should emit a NOTICE
drop event trigger if exists regress_event_trigger2;
drop event trigger if exists regress_event_trigger2;
drop event trigger regress_event_trigger3;
drop event trigger regress_event_trigger_end;
-- test support for dropped objects
CREATE SCHEMA schema_one authorization regression_bob;
CREATE SCHEMA schema_two authorization regression_bob;
CREATE SCHEMA audit_tbls authorization regression_bob;
SET SESSION AUTHORIZATION regression_bob;
CREATE TABLE schema_one.table_one(a int);
CREATE TABLE schema_one."table two"(a int);
CREATE TABLE schema_one.table_three(a int);
CREATE TABLE audit_tbls.schema_one_table_two(the_value text);
CREATE TABLE schema_two.table_two(a int);
CREATE TABLE schema_two.table_three(a int, b text);
CREATE TABLE audit_tbls.schema_two_table_three(the_value text);
CREATE OR REPLACE FUNCTION schema_two.add(int, int) RETURNS int LANGUAGE plpgsql
CALLED ON NULL INPUT
AS $$ BEGIN RETURN coalesce($1,0) + coalesce($2,0); END; $$;
CREATE AGGREGATE schema_two.newton
(BASETYPE = int, SFUNC = schema_two.add, STYPE = int);
RESET SESSION AUTHORIZATION;
CREATE TABLE undroppable_objs (
object_type text,
object_identity text
);
INSERT INTO undroppable_objs VALUES
('table', 'schema_one.table_three'),
('table', 'audit_tbls.schema_two_table_three');
CREATE TABLE dropped_objects (
type text,
schema text,
object text
);
-- This tests errors raised within event triggers; the one in audit_tbls
-- uses 2nd-level recursive invocation via test_evtrig_dropped_objects().
CREATE OR REPLACE FUNCTION undroppable() RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE
obj record;
BEGIN
PERFORM 1 FROM pg_tables WHERE tablename = 'undroppable_objs';
IF NOT FOUND THEN
RAISE NOTICE 'table undroppable_objs not found, skipping';
RETURN;
END IF;
FOR obj IN
SELECT * FROM pg_event_trigger_dropped_objects() JOIN
undroppable_objs USING (object_type, object_identity)
LOOP
RAISE EXCEPTION 'object % of type % cannot be dropped',
obj.object_identity, obj.object_type;
END LOOP;
END;
$$;
CREATE EVENT TRIGGER undroppable ON sql_drop
EXECUTE PROCEDURE undroppable();
CREATE OR REPLACE FUNCTION test_evtrig_dropped_objects() RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
IF obj.object_type = 'table' THEN
EXECUTE format('DROP TABLE IF EXISTS audit_tbls.%I',
format('%s_%s', obj.schema_name, obj.object_name));
END IF;
INSERT INTO dropped_objects
(type, schema, object) VALUES
(obj.object_type, obj.schema_name, obj.object_identity);
END LOOP;
END
$$;
CREATE EVENT TRIGGER regress_event_trigger_drop_objects ON sql_drop
WHEN TAG IN ('drop table', 'drop function', 'drop view',
'drop owned', 'drop schema', 'alter table')
EXECUTE PROCEDURE test_evtrig_dropped_objects();
ALTER TABLE schema_one.table_one DROP COLUMN a;
DROP SCHEMA schema_one, schema_two CASCADE;
DELETE FROM undroppable_objs WHERE object_identity = 'audit_tbls.schema_two_table_three';
DROP SCHEMA schema_one, schema_two CASCADE;
DELETE FROM undroppable_objs WHERE object_identity = 'schema_one.table_three';
DROP SCHEMA schema_one, schema_two CASCADE;
SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast';
DROP OWNED BY regression_bob;
SELECT * FROM dropped_objects WHERE type = 'schema';
DROP ROLE regression_bob;
DROP EVENT TRIGGER regress_event_trigger_drop_objects;
DROP EVENT TRIGGER undroppable;
|
