1 -- should fail, return type mismatch
2 create event trigger regress_event_trigger
4 execute procedure pg_backend_pid();
5 ERROR: function "pg_backend_pid" must return type "event_trigger"
7 create function test_event_trigger() returns event_trigger as $$
9 RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
12 -- should fail, no elephant_bootstrap entry point
13 create event trigger regress_event_trigger on elephant_bootstrap
14 execute procedure test_event_trigger();
15 ERROR: unrecognized event name "elephant_bootstrap"
17 create event trigger regress_event_trigger on ddl_command_start
18 execute procedure test_event_trigger();
20 create event trigger regress_event_trigger_end on ddl_command_end
21 execute procedure test_event_trigger();
22 -- should fail, food is not a valid filter variable
23 create event trigger regress_event_trigger2 on ddl_command_start
24 when food in ('sandwhich')
25 execute procedure test_event_trigger();
26 ERROR: unrecognized filter variable "food"
27 -- should fail, sandwhich is not a valid command tag
28 create event trigger regress_event_trigger2 on ddl_command_start
29 when tag in ('sandwhich')
30 execute procedure test_event_trigger();
31 ERROR: filter value "sandwhich" not recognized for filter variable "tag"
32 -- should fail, create skunkcabbage is not a valid comand tag
33 create event trigger regress_event_trigger2 on ddl_command_start
34 when tag in ('create table', 'create skunkcabbage')
35 execute procedure test_event_trigger();
36 ERROR: filter value "create skunkcabbage" not recognized for filter variable "tag"
37 -- should fail, can't have event triggers on event triggers
38 create event trigger regress_event_trigger2 on ddl_command_start
39 when tag in ('DROP EVENT TRIGGER')
40 execute procedure test_event_trigger();
41 ERROR: event triggers are not supported for DROP EVENT TRIGGER
42 -- should fail, can't have same filter variable twice
43 create event trigger regress_event_trigger2 on ddl_command_start
44 when tag in ('create table') and tag in ('CREATE FUNCTION')
45 execute procedure test_event_trigger();
46 ERROR: filter variable "tag" specified more than once
48 create event trigger regress_event_trigger2 on ddl_command_start
49 when tag in ('create table', 'CREATE FUNCTION')
50 execute procedure test_event_trigger();
52 comment on event trigger regress_event_trigger is 'test comment';
53 -- should fail, event triggers are not schema objects
54 comment on event trigger wrong.regress_event_trigger is 'test comment';
55 ERROR: event trigger name cannot be qualified
56 -- drop as non-superuser should fail
57 create role regression_bob;
58 set role regression_bob;
59 create event trigger regress_event_trigger_noperms on ddl_command_start
60 execute procedure test_event_trigger();
61 ERROR: permission denied to create event trigger "regress_event_trigger_noperms"
62 HINT: Must be superuser to create an event trigger.
65 alter event trigger regress_event_trigger enable replica;
66 alter event trigger regress_event_trigger enable always;
67 alter event trigger regress_event_trigger enable;
68 alter event trigger regress_event_trigger disable;
69 -- regress_event_trigger2 and regress_event_trigger_end should fire, but not
70 -- regress_event_trigger
71 create table event_trigger_fire1 (a int);
72 NOTICE: test_event_trigger: ddl_command_start CREATE TABLE
73 NOTICE: test_event_trigger: ddl_command_end CREATE TABLE
74 -- regress_event_trigger_end should fire here
75 drop table event_trigger_fire1;
76 NOTICE: test_event_trigger: ddl_command_end DROP TABLE
77 -- alter owner to non-superuser should fail
78 alter event trigger regress_event_trigger owner to regression_bob;
79 ERROR: permission denied to change owner of event trigger "regress_event_trigger"
80 HINT: The owner of an event trigger must be a superuser.
81 -- alter owner to superuser should work
82 alter role regression_bob superuser;
83 alter event trigger regress_event_trigger owner to regression_bob;
84 -- should fail, name collision
85 alter event trigger regress_event_trigger rename to regress_event_trigger2;
86 ERROR: event trigger "regress_event_trigger2" already exists
88 alter event trigger regress_event_trigger rename to regress_event_trigger3;
89 -- should fail, doesn't exist any more
90 drop event trigger regress_event_trigger;
91 ERROR: event trigger "regress_event_trigger" does not exist
92 -- should fail, regression_bob owns regress_event_trigger2/3
93 drop role regression_bob;
94 ERROR: role "regression_bob" cannot be dropped because some objects depend on it
95 DETAIL: owner of event trigger regress_event_trigger3
96 -- cleanup before next test
97 -- these are all OK; the second one should emit a NOTICE
98 drop event trigger if exists regress_event_trigger2;
99 drop event trigger if exists regress_event_trigger2;
100 NOTICE: event trigger "regress_event_trigger2" does not exist, skipping
101 drop event trigger regress_event_trigger3;
102 drop event trigger regress_event_trigger_end;
103 -- test support for dropped objects
104 CREATE SCHEMA schema_one authorization regression_bob;
105 CREATE SCHEMA schema_two authorization regression_bob;
106 CREATE SCHEMA audit_tbls authorization regression_bob;
107 SET SESSION AUTHORIZATION regression_bob;
108 CREATE TABLE schema_one.table_one(a int);
109 CREATE TABLE schema_one."table two"(a int);
110 CREATE TABLE schema_one.table_three(a int);
111 CREATE TABLE audit_tbls.schema_one_table_two(the_value text);
112 CREATE TABLE schema_two.table_two(a int);
113 CREATE TABLE schema_two.table_three(a int, b text);
114 CREATE TABLE audit_tbls.schema_two_table_three(the_value text);
115 CREATE OR REPLACE FUNCTION schema_two.add(int, int) RETURNS int LANGUAGE plpgsql
117 AS $$ BEGIN RETURN coalesce($1,0) + coalesce($2,0); END; $$;
118 CREATE AGGREGATE schema_two.newton
119 (BASETYPE = int, SFUNC = schema_two.add, STYPE = int);
120 RESET SESSION AUTHORIZATION;
121 CREATE TABLE undroppable_objs (
125 INSERT INTO undroppable_objs VALUES
126 ('table', 'schema_one.table_three'),
127 ('table', 'audit_tbls.schema_two_table_three');
128 CREATE TABLE dropped_objects (
133 -- This tests errors raised within event triggers; the one in audit_tbls
134 -- uses 2nd-level recursive invocation via test_evtrig_dropped_objects().
135 CREATE OR REPLACE FUNCTION undroppable() RETURNS event_trigger
136 LANGUAGE plpgsql AS $$
140 PERFORM 1 FROM pg_tables WHERE tablename = 'undroppable_objs';
142 RAISE NOTICE 'table undroppable_objs not found, skipping';
146 SELECT * FROM pg_event_trigger_dropped_objects() JOIN
147 undroppable_objs USING (object_type, object_identity)
149 RAISE EXCEPTION 'object % of type % cannot be dropped',
150 obj.object_identity, obj.object_type;
154 CREATE EVENT TRIGGER undroppable ON sql_drop
155 EXECUTE PROCEDURE undroppable();
156 CREATE OR REPLACE FUNCTION test_evtrig_dropped_objects() RETURNS event_trigger
157 LANGUAGE plpgsql AS $$
161 FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
163 IF obj.object_type = 'table' THEN
164 EXECUTE format('DROP TABLE IF EXISTS audit_tbls.%I',
165 format('%s_%s', obj.schema_name, obj.object_name));
168 INSERT INTO dropped_objects
169 (type, schema, object) VALUES
170 (obj.object_type, obj.schema_name, obj.object_identity);
174 CREATE EVENT TRIGGER regress_event_trigger_drop_objects ON sql_drop
175 WHEN TAG IN ('drop table', 'drop function', 'drop view',
176 'drop owned', 'drop schema', 'alter table')
177 EXECUTE PROCEDURE test_evtrig_dropped_objects();
178 ALTER TABLE schema_one.table_one DROP COLUMN a;
179 DROP SCHEMA schema_one, schema_two CASCADE;
180 NOTICE: drop cascades to 7 other objects
181 DETAIL: drop cascades to table schema_two.table_two
182 drop cascades to table schema_two.table_three
183 drop cascades to function schema_two.add(integer,integer)
184 drop cascades to function schema_two.newton(integer)
185 drop cascades to table schema_one.table_one
186 drop cascades to table schema_one."table two"
187 drop cascades to table schema_one.table_three
188 NOTICE: table "schema_two_table_two" does not exist, skipping
189 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_two_table_two"
190 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
191 NOTICE: table "audit_tbls_schema_two_table_three" does not exist, skipping
192 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.audit_tbls_schema_two_table_three"
193 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
194 SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_two_table_three"
195 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
196 ERROR: object audit_tbls.schema_two_table_three of type table cannot be dropped
197 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_two_table_three"
198 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
199 DELETE FROM undroppable_objs WHERE object_identity = 'audit_tbls.schema_two_table_three';
200 DROP SCHEMA schema_one, schema_two CASCADE;
201 NOTICE: drop cascades to 7 other objects
202 DETAIL: drop cascades to table schema_two.table_two
203 drop cascades to table schema_two.table_three
204 drop cascades to function schema_two.add(integer,integer)
205 drop cascades to function schema_two.newton(integer)
206 drop cascades to table schema_one.table_one
207 drop cascades to table schema_one."table two"
208 drop cascades to table schema_one.table_three
209 NOTICE: table "schema_two_table_two" does not exist, skipping
210 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_two_table_two"
211 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
212 NOTICE: table "audit_tbls_schema_two_table_three" does not exist, skipping
213 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.audit_tbls_schema_two_table_three"
214 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
215 SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_two_table_three"
216 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
217 NOTICE: table "schema_one_table_one" does not exist, skipping
218 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_one_table_one"
219 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
220 NOTICE: table "schema_one_table two" does not exist, skipping
221 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls."schema_one_table two""
222 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
223 NOTICE: table "schema_one_table_three" does not exist, skipping
224 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_one_table_three"
225 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
226 ERROR: object schema_one.table_three of type table cannot be dropped
227 DELETE FROM undroppable_objs WHERE object_identity = 'schema_one.table_three';
228 DROP SCHEMA schema_one, schema_two CASCADE;
229 NOTICE: drop cascades to 7 other objects
230 DETAIL: drop cascades to table schema_two.table_two
231 drop cascades to table schema_two.table_three
232 drop cascades to function schema_two.add(integer,integer)
233 drop cascades to function schema_two.newton(integer)
234 drop cascades to table schema_one.table_one
235 drop cascades to table schema_one."table two"
236 drop cascades to table schema_one.table_three
237 NOTICE: table "schema_two_table_two" does not exist, skipping
238 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_two_table_two"
239 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
240 NOTICE: table "audit_tbls_schema_two_table_three" does not exist, skipping
241 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.audit_tbls_schema_two_table_three"
242 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
243 SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_two_table_three"
244 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
245 NOTICE: table "schema_one_table_one" does not exist, skipping
246 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_one_table_one"
247 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
248 NOTICE: table "schema_one_table two" does not exist, skipping
249 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls."schema_one_table two""
250 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
251 NOTICE: table "schema_one_table_three" does not exist, skipping
252 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_one_table_three"
253 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
254 SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast';
255 type | schema | object
256 --------------+------------+-------------------------------------
257 table column | schema_one | schema_one.table_one.a
258 schema | | schema_two
259 table | schema_two | schema_two.table_two
260 type | schema_two | schema_two.table_two
261 type | schema_two | schema_two.table_two[]
262 table | audit_tbls | audit_tbls.schema_two_table_three
263 type | audit_tbls | audit_tbls.schema_two_table_three
264 type | audit_tbls | audit_tbls.schema_two_table_three[]
265 table | schema_two | schema_two.table_three
266 type | schema_two | schema_two.table_three
267 type | schema_two | schema_two.table_three[]
268 function | schema_two | schema_two.add(integer,integer)
269 aggregate | schema_two | schema_two.newton(integer)
270 schema | | schema_one
271 table | schema_one | schema_one.table_one
272 type | schema_one | schema_one.table_one
273 type | schema_one | schema_one.table_one[]
274 table | schema_one | schema_one."table two"
275 type | schema_one | schema_one."table two"
276 type | schema_one | schema_one."table two"[]
277 table | schema_one | schema_one.table_three
278 type | schema_one | schema_one.table_three
279 type | schema_one | schema_one.table_three[]
282 DROP OWNED BY regression_bob;
283 NOTICE: schema "audit_tbls" does not exist, skipping
284 CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.audit_tbls_schema_one_table_two"
285 PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
286 SELECT * FROM dropped_objects WHERE type = 'schema';
287 type | schema | object
288 --------+--------+------------
289 schema | | schema_two
290 schema | | schema_one
291 schema | | audit_tbls
294 DROP ROLE regression_bob;
295 DROP EVENT TRIGGER regress_event_trigger_drop_objects;
296 DROP EVENT TRIGGER undroppable;
297 CREATE OR REPLACE FUNCTION event_trigger_report_dropped()
298 RETURNS event_trigger
303 FOR r IN SELECT * from pg_event_trigger_dropped_objects()
305 IF NOT r.normal AND NOT r.original THEN
308 RAISE NOTICE 'NORMAL: orig=% normal=% type=% identity=% name=% args=%',
309 r.original, r.normal, r.object_type, r.object_identity,
310 r.address_names, r.address_args;
313 CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop
314 EXECUTE PROCEDURE event_trigger_report_dropped();
315 CREATE SCHEMA evttrig
316 CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two')
317 CREATE INDEX one_idx ON one (col_b)
318 CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42);
319 ALTER TABLE evttrig.two DROP COLUMN col_c;
320 NOTICE: NORMAL: orig=t normal=f type=table column identity=evttrig.two.col_c name={evttrig,two,col_c} args={}
321 NOTICE: NORMAL: orig=f normal=t type=table constraint identity=two_col_c_check on evttrig.two name={evttrig,two,two_col_c_check} args={}
322 ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT;
323 NOTICE: NORMAL: orig=t normal=f type=default value identity=for evttrig.one.col_b name={evttrig,one,col_b} args={}
324 ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey;
325 NOTICE: NORMAL: orig=t normal=f type=table constraint identity=one_pkey on evttrig.one name={evttrig,one,one_pkey} args={}
326 DROP INDEX evttrig.one_idx;
327 NOTICE: NORMAL: orig=t normal=f type=index identity=evttrig.one_idx name={evttrig,one_idx} args={}
328 DROP SCHEMA evttrig CASCADE;
329 NOTICE: drop cascades to 2 other objects
330 DETAIL: drop cascades to table evttrig.one
331 drop cascades to table evttrig.two
332 NOTICE: NORMAL: orig=t normal=f type=schema identity=evttrig name={evttrig} args={}
333 NOTICE: NORMAL: orig=f normal=t type=table identity=evttrig.one name={evttrig,one} args={}
334 NOTICE: NORMAL: orig=f normal=t type=sequence identity=evttrig.one_col_a_seq name={evttrig,one_col_a_seq} args={}
335 NOTICE: NORMAL: orig=f normal=t type=default value identity=for evttrig.one.col_a name={evttrig,one,col_a} args={}
336 NOTICE: NORMAL: orig=f normal=t type=table identity=evttrig.two name={evttrig,two} args={}
337 DROP EVENT TRIGGER regress_event_trigger_report_dropped;
338 -- only allowed from within an event trigger function, should fail
339 select pg_event_trigger_table_rewrite_oid();
340 ERROR: pg_event_trigger_table_rewrite_oid() can only be called in a table_rewrite event trigger function
341 -- test Table Rewrite Event Trigger
342 CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger
343 LANGUAGE plpgsql AS $$
345 RAISE EXCEPTION 'I''m sorry Sir, No Rewrite Allowed.';
348 create event trigger no_rewrite_allowed on table_rewrite
349 execute procedure test_evtrig_no_rewrite();
350 create table rewriteme (id serial primary key, foo float);
351 insert into rewriteme
352 select x * 1.001 from generate_series(1, 500) as t(x);
353 alter table rewriteme alter column foo type numeric;
354 ERROR: I'm sorry Sir, No Rewrite Allowed.
355 alter table rewriteme add column baz int default 0;
356 ERROR: I'm sorry Sir, No Rewrite Allowed.
357 -- test with more than one reason to rewrite a single table
358 CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger
359 LANGUAGE plpgsql AS $$
361 RAISE NOTICE 'Table ''%'' is being rewritten (reason = %)',
362 pg_event_trigger_table_rewrite_oid()::regclass,
363 pg_event_trigger_table_rewrite_reason();
366 alter table rewriteme
367 add column onemore int default 0,
368 add column another int default -1,
369 alter column foo type numeric(10,4);
370 NOTICE: Table 'rewriteme' is being rewritten (reason = 6)
371 -- shouldn't trigger a table_rewrite event
372 alter table rewriteme alter column foo type numeric(12,4);
373 -- typed tables are rewritten when their type changes. Don't emit table
374 -- name, because firing order is not stable.
375 CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger
376 LANGUAGE plpgsql AS $$
378 RAISE NOTICE 'Table is being rewritten (reason = %)',
379 pg_event_trigger_table_rewrite_reason();
382 create type rewritetype as (a int);
383 create table rewritemetoo1 of rewritetype;
384 create table rewritemetoo2 of rewritetype;
385 alter type rewritetype alter attribute a type text cascade;
386 NOTICE: Table is being rewritten (reason = 4)
387 NOTICE: Table is being rewritten (reason = 4)
388 -- but this doesn't work
389 create table rewritemetoo3 (a rewritetype);
390 alter type rewritetype alter attribute a type varchar cascade;
391 ERROR: cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
392 drop table rewriteme;
393 drop event trigger no_rewrite_allowed;
394 drop function test_evtrig_no_rewrite();
395 -- test Row Security Event Trigger
396 RESET SESSION AUTHORIZATION;
397 CREATE TABLE event_trigger_test (a integer, b text);
398 CREATE OR REPLACE FUNCTION start_command()
399 RETURNS event_trigger AS $$
401 RAISE NOTICE '% - ddl_command_start', tg_tag;
404 CREATE OR REPLACE FUNCTION end_command()
405 RETURNS event_trigger AS $$
407 RAISE NOTICE '% - ddl_command_end', tg_tag;
410 CREATE OR REPLACE FUNCTION drop_sql_command()
411 RETURNS event_trigger AS $$
413 RAISE NOTICE '% - sql_drop', tg_tag;
416 CREATE EVENT TRIGGER start_rls_command ON ddl_command_start
417 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE start_command();
418 CREATE EVENT TRIGGER end_rls_command ON ddl_command_end
419 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE end_command();
420 CREATE EVENT TRIGGER sql_drop_command ON sql_drop
421 WHEN TAG IN ('DROP POLICY') EXECUTE PROCEDURE drop_sql_command();
422 CREATE POLICY p1 ON event_trigger_test USING (FALSE);
423 NOTICE: CREATE POLICY - ddl_command_start
424 NOTICE: CREATE POLICY - ddl_command_end
425 ALTER POLICY p1 ON event_trigger_test USING (TRUE);
426 NOTICE: ALTER POLICY - ddl_command_start
427 NOTICE: ALTER POLICY - ddl_command_end
428 ALTER POLICY p1 ON event_trigger_test RENAME TO p2;
429 NOTICE: ALTER POLICY - ddl_command_start
430 NOTICE: ALTER POLICY - ddl_command_end
431 DROP POLICY p2 ON event_trigger_test;
432 NOTICE: DROP POLICY - ddl_command_start
433 NOTICE: DROP POLICY - sql_drop
434 NOTICE: DROP POLICY - ddl_command_end
435 DROP EVENT TRIGGER start_rls_command;
436 DROP EVENT TRIGGER end_rls_command;
437 DROP EVENT TRIGGER sql_drop_command;