1-- should fail, return type mismatch 2create event trigger regress_event_trigger 3 on ddl_command_start 4 execute procedure pg_backend_pid(); 5 6-- OK 7create function test_event_trigger() returns event_trigger as $$ 8BEGIN 9 RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag; 10END 11$$ language plpgsql; 12 13-- should fail, can't call it as a plain function 14SELECT test_event_trigger(); 15 16-- should fail, event triggers cannot have declared arguments 17create function test_event_trigger_arg(name text) 18returns event_trigger as $$ BEGIN RETURN 1; END $$ language plpgsql; 19 20-- should fail, SQL functions cannot be event triggers 21create function test_event_trigger_sql() returns event_trigger as $$ 22SELECT 1 $$ language sql; 23 24-- should fail, no elephant_bootstrap entry point 25create event trigger regress_event_trigger on elephant_bootstrap 26 execute procedure test_event_trigger(); 27 28-- OK 29create event trigger regress_event_trigger on ddl_command_start 30 execute procedure test_event_trigger(); 31 32-- OK 33create event trigger regress_event_trigger_end on ddl_command_end 34 execute function test_event_trigger(); 35 36-- should fail, food is not a valid filter variable 37create event trigger regress_event_trigger2 on ddl_command_start 38 when food in ('sandwich') 39 execute procedure test_event_trigger(); 40 41-- should fail, sandwich is not a valid command tag 42create event trigger regress_event_trigger2 on ddl_command_start 43 when tag in ('sandwich') 44 execute procedure test_event_trigger(); 45 46-- should fail, create skunkcabbage is not a valid command tag 47create event trigger regress_event_trigger2 on ddl_command_start 48 when tag in ('create table', 'create skunkcabbage') 49 execute procedure test_event_trigger(); 50 51-- should fail, can't have event triggers on event triggers 52create event trigger regress_event_trigger2 on ddl_command_start 53 when tag in ('DROP EVENT TRIGGER') 54 execute procedure test_event_trigger(); 55 56-- should fail, can't have event triggers on global objects 57create event trigger regress_event_trigger2 on ddl_command_start 58 when tag in ('CREATE ROLE') 59 execute procedure test_event_trigger(); 60 61-- should fail, can't have event triggers on global objects 62create event trigger regress_event_trigger2 on ddl_command_start 63 when tag in ('CREATE DATABASE') 64 execute procedure test_event_trigger(); 65 66-- should fail, can't have event triggers on global objects 67create event trigger regress_event_trigger2 on ddl_command_start 68 when tag in ('CREATE TABLESPACE') 69 execute procedure test_event_trigger(); 70 71-- should fail, can't have same filter variable twice 72create event trigger regress_event_trigger2 on ddl_command_start 73 when tag in ('create table') and tag in ('CREATE FUNCTION') 74 execute procedure test_event_trigger(); 75 76-- should fail, can't have arguments 77create event trigger regress_event_trigger2 on ddl_command_start 78 execute procedure test_event_trigger('argument not allowed'); 79 80-- OK 81create event trigger regress_event_trigger2 on ddl_command_start 82 when tag in ('create table', 'CREATE FUNCTION') 83 execute procedure test_event_trigger(); 84 85-- OK 86comment on event trigger regress_event_trigger is 'test comment'; 87 88-- drop as non-superuser should fail 89create role regress_evt_user; 90set role regress_evt_user; 91create event trigger regress_event_trigger_noperms on ddl_command_start 92 execute procedure test_event_trigger(); 93reset role; 94 95-- test enabling and disabling 96alter event trigger regress_event_trigger disable; 97-- fires _trigger2 and _trigger_end should fire, but not _trigger 98create table event_trigger_fire1 (a int); 99alter event trigger regress_event_trigger enable; 100set session_replication_role = replica; 101-- fires nothing 102create table event_trigger_fire2 (a int); 103alter event trigger regress_event_trigger enable replica; 104-- fires only _trigger 105create table event_trigger_fire3 (a int); 106alter event trigger regress_event_trigger enable always; 107-- fires only _trigger 108create table event_trigger_fire4 (a int); 109reset session_replication_role; 110-- fires all three 111create table event_trigger_fire5 (a int); 112-- non-top-level command 113create function f1() returns int 114language plpgsql 115as $$ 116begin 117 create table event_trigger_fire6 (a int); 118 return 0; 119end $$; 120select f1(); 121-- non-top-level command 122create procedure p1() 123language plpgsql 124as $$ 125begin 126 create table event_trigger_fire7 (a int); 127end $$; 128call p1(); 129 130-- clean up 131alter event trigger regress_event_trigger disable; 132drop table event_trigger_fire2, event_trigger_fire3, event_trigger_fire4, event_trigger_fire5, event_trigger_fire6, event_trigger_fire7; 133drop routine f1(), p1(); 134 135-- regress_event_trigger_end should fire on these commands 136grant all on table event_trigger_fire1 to public; 137comment on table event_trigger_fire1 is 'here is a comment'; 138revoke all on table event_trigger_fire1 from public; 139drop table event_trigger_fire1; 140create foreign data wrapper useless; 141create server useless_server foreign data wrapper useless; 142create user mapping for regress_evt_user server useless_server; 143alter default privileges for role regress_evt_user 144 revoke delete on tables from regress_evt_user; 145 146-- alter owner to non-superuser should fail 147alter event trigger regress_event_trigger owner to regress_evt_user; 148 149-- alter owner to superuser should work 150alter role regress_evt_user superuser; 151alter event trigger regress_event_trigger owner to regress_evt_user; 152 153-- should fail, name collision 154alter event trigger regress_event_trigger rename to regress_event_trigger2; 155 156-- OK 157alter event trigger regress_event_trigger rename to regress_event_trigger3; 158 159-- should fail, doesn't exist any more 160drop event trigger regress_event_trigger; 161 162-- should fail, regress_evt_user owns some objects 163drop role regress_evt_user; 164 165-- cleanup before next test 166-- these are all OK; the second one should emit a NOTICE 167drop event trigger if exists regress_event_trigger2; 168drop event trigger if exists regress_event_trigger2; 169drop event trigger regress_event_trigger3; 170drop event trigger regress_event_trigger_end; 171 172-- test support for dropped objects 173CREATE SCHEMA schema_one authorization regress_evt_user; 174CREATE SCHEMA schema_two authorization regress_evt_user; 175CREATE SCHEMA audit_tbls authorization regress_evt_user; 176CREATE TEMP TABLE a_temp_tbl (); 177SET SESSION AUTHORIZATION regress_evt_user; 178 179CREATE TABLE schema_one.table_one(a int); 180CREATE TABLE schema_one."table two"(a int); 181CREATE TABLE schema_one.table_three(a int); 182CREATE TABLE audit_tbls.schema_one_table_two(the_value text); 183 184CREATE TABLE schema_two.table_two(a int); 185CREATE TABLE schema_two.table_three(a int, b text); 186CREATE TABLE audit_tbls.schema_two_table_three(the_value text); 187 188CREATE OR REPLACE FUNCTION schema_two.add(int, int) RETURNS int LANGUAGE plpgsql 189 CALLED ON NULL INPUT 190 AS $$ BEGIN RETURN coalesce($1,0) + coalesce($2,0); END; $$; 191CREATE AGGREGATE schema_two.newton 192 (BASETYPE = int, SFUNC = schema_two.add, STYPE = int); 193 194RESET SESSION AUTHORIZATION; 195 196CREATE TABLE undroppable_objs ( 197 object_type text, 198 object_identity text 199); 200INSERT INTO undroppable_objs VALUES 201('table', 'schema_one.table_three'), 202('table', 'audit_tbls.schema_two_table_three'); 203 204CREATE TABLE dropped_objects ( 205 type text, 206 schema text, 207 object text 208); 209 210-- This tests errors raised within event triggers; the one in audit_tbls 211-- uses 2nd-level recursive invocation via test_evtrig_dropped_objects(). 212CREATE OR REPLACE FUNCTION undroppable() RETURNS event_trigger 213LANGUAGE plpgsql AS $$ 214DECLARE 215 obj record; 216BEGIN 217 PERFORM 1 FROM pg_tables WHERE tablename = 'undroppable_objs'; 218 IF NOT FOUND THEN 219 RAISE NOTICE 'table undroppable_objs not found, skipping'; 220 RETURN; 221 END IF; 222 FOR obj IN 223 SELECT * FROM pg_event_trigger_dropped_objects() JOIN 224 undroppable_objs USING (object_type, object_identity) 225 LOOP 226 RAISE EXCEPTION 'object % of type % cannot be dropped', 227 obj.object_identity, obj.object_type; 228 END LOOP; 229END; 230$$; 231 232CREATE EVENT TRIGGER undroppable ON sql_drop 233 EXECUTE PROCEDURE undroppable(); 234 235CREATE OR REPLACE FUNCTION test_evtrig_dropped_objects() RETURNS event_trigger 236LANGUAGE plpgsql AS $$ 237DECLARE 238 obj record; 239BEGIN 240 FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() 241 LOOP 242 IF obj.object_type = 'table' THEN 243 EXECUTE format('DROP TABLE IF EXISTS audit_tbls.%I', 244 format('%s_%s', obj.schema_name, obj.object_name)); 245 END IF; 246 247 INSERT INTO dropped_objects 248 (type, schema, object) VALUES 249 (obj.object_type, obj.schema_name, obj.object_identity); 250 END LOOP; 251END 252$$; 253 254CREATE EVENT TRIGGER regress_event_trigger_drop_objects ON sql_drop 255 WHEN TAG IN ('drop table', 'drop function', 'drop view', 256 'drop owned', 'drop schema', 'alter table') 257 EXECUTE PROCEDURE test_evtrig_dropped_objects(); 258 259ALTER TABLE schema_one.table_one DROP COLUMN a; 260DROP SCHEMA schema_one, schema_two CASCADE; 261DELETE FROM undroppable_objs WHERE object_identity = 'audit_tbls.schema_two_table_three'; 262DROP SCHEMA schema_one, schema_two CASCADE; 263DELETE FROM undroppable_objs WHERE object_identity = 'schema_one.table_three'; 264DROP SCHEMA schema_one, schema_two CASCADE; 265 266SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast'; 267 268DROP OWNED BY regress_evt_user; 269SELECT * FROM dropped_objects WHERE type = 'schema'; 270 271DROP ROLE regress_evt_user; 272 273DROP EVENT TRIGGER regress_event_trigger_drop_objects; 274DROP EVENT TRIGGER undroppable; 275 276-- Event triggers on relations. 277CREATE OR REPLACE FUNCTION event_trigger_report_dropped() 278 RETURNS event_trigger 279 LANGUAGE plpgsql 280AS $$ 281DECLARE r record; 282BEGIN 283 FOR r IN SELECT * from pg_event_trigger_dropped_objects() 284 LOOP 285 IF NOT r.normal AND NOT r.original THEN 286 CONTINUE; 287 END IF; 288 RAISE NOTICE 'NORMAL: orig=% normal=% istemp=% type=% identity=% name=% args=%', 289 r.original, r.normal, r.is_temporary, r.object_type, 290 r.object_identity, r.address_names, r.address_args; 291 END LOOP; 292END; $$; 293CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop 294 EXECUTE PROCEDURE event_trigger_report_dropped(); 295CREATE OR REPLACE FUNCTION event_trigger_report_end() 296 RETURNS event_trigger 297 LANGUAGE plpgsql 298AS $$ 299DECLARE r RECORD; 300BEGIN 301 FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() 302 LOOP 303 RAISE NOTICE 'END: command_tag=% type=% identity=%', 304 r.command_tag, r.object_type, r.object_identity; 305 END LOOP; 306END; $$; 307CREATE EVENT TRIGGER regress_event_trigger_report_end ON ddl_command_end 308 EXECUTE PROCEDURE event_trigger_report_end(); 309 310CREATE SCHEMA evttrig 311 CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two', col_c SERIAL) 312 CREATE INDEX one_idx ON one (col_b) 313 CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42) 314 CREATE TABLE id (col_d int NOT NULL GENERATED ALWAYS AS IDENTITY); 315 316-- Partitioned tables with a partitioned index 317CREATE TABLE evttrig.parted ( 318 id int PRIMARY KEY) 319 PARTITION BY RANGE (id); 320CREATE TABLE evttrig.part_1_10 PARTITION OF evttrig.parted (id) 321 FOR VALUES FROM (1) TO (10); 322CREATE TABLE evttrig.part_10_20 PARTITION OF evttrig.parted (id) 323 FOR VALUES FROM (10) TO (20) PARTITION BY RANGE (id); 324CREATE TABLE evttrig.part_10_15 PARTITION OF evttrig.part_10_20 (id) 325 FOR VALUES FROM (10) TO (15); 326CREATE TABLE evttrig.part_15_20 PARTITION OF evttrig.part_10_20 (id) 327 FOR VALUES FROM (15) TO (20); 328 329ALTER TABLE evttrig.two DROP COLUMN col_c; 330ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT; 331ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey; 332ALTER TABLE evttrig.one DROP COLUMN col_c; 333ALTER TABLE evttrig.id ALTER COLUMN col_d SET DATA TYPE bigint; 334ALTER TABLE evttrig.id ALTER COLUMN col_d DROP IDENTITY, 335 ALTER COLUMN col_d SET DATA TYPE int; 336DROP INDEX evttrig.one_idx; 337DROP SCHEMA evttrig CASCADE; 338DROP TABLE a_temp_tbl; 339 340DROP EVENT TRIGGER regress_event_trigger_report_dropped; 341DROP EVENT TRIGGER regress_event_trigger_report_end; 342 343-- only allowed from within an event trigger function, should fail 344select pg_event_trigger_table_rewrite_oid(); 345 346-- test Table Rewrite Event Trigger 347CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 348LANGUAGE plpgsql AS $$ 349BEGIN 350 RAISE EXCEPTION 'rewrites not allowed'; 351END; 352$$; 353 354create event trigger no_rewrite_allowed on table_rewrite 355 execute procedure test_evtrig_no_rewrite(); 356 357create table rewriteme (id serial primary key, foo float, bar timestamptz); 358insert into rewriteme 359 select x * 1.001 from generate_series(1, 500) as t(x); 360alter table rewriteme alter column foo type numeric; 361alter table rewriteme add column baz int default 0; 362 363-- test with more than one reason to rewrite a single table 364CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 365LANGUAGE plpgsql AS $$ 366BEGIN 367 RAISE NOTICE 'Table ''%'' is being rewritten (reason = %)', 368 pg_event_trigger_table_rewrite_oid()::regclass, 369 pg_event_trigger_table_rewrite_reason(); 370END; 371$$; 372 373alter table rewriteme 374 add column onemore int default 0, 375 add column another int default -1, 376 alter column foo type numeric(10,4); 377 378-- shouldn't trigger a table_rewrite event 379alter table rewriteme alter column foo type numeric(12,4); 380begin; 381set timezone to 'UTC'; 382alter table rewriteme alter column bar type timestamp; 383set timezone to '0'; 384alter table rewriteme alter column bar type timestamptz; 385set timezone to 'Europe/London'; 386alter table rewriteme alter column bar type timestamp; -- does rewrite 387rollback; 388 389-- typed tables are rewritten when their type changes. Don't emit table 390-- name, because firing order is not stable. 391CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 392LANGUAGE plpgsql AS $$ 393BEGIN 394 RAISE NOTICE 'Table is being rewritten (reason = %)', 395 pg_event_trigger_table_rewrite_reason(); 396END; 397$$; 398 399create type rewritetype as (a int); 400create table rewritemetoo1 of rewritetype; 401create table rewritemetoo2 of rewritetype; 402alter type rewritetype alter attribute a type text cascade; 403 404-- but this doesn't work 405create table rewritemetoo3 (a rewritetype); 406alter type rewritetype alter attribute a type varchar cascade; 407 408drop table rewriteme; 409drop event trigger no_rewrite_allowed; 410drop function test_evtrig_no_rewrite(); 411 412-- test Row Security Event Trigger 413RESET SESSION AUTHORIZATION; 414CREATE TABLE event_trigger_test (a integer, b text); 415 416CREATE OR REPLACE FUNCTION start_command() 417RETURNS event_trigger AS $$ 418BEGIN 419RAISE NOTICE '% - ddl_command_start', tg_tag; 420END; 421$$ LANGUAGE plpgsql; 422 423CREATE OR REPLACE FUNCTION end_command() 424RETURNS event_trigger AS $$ 425BEGIN 426RAISE NOTICE '% - ddl_command_end', tg_tag; 427END; 428$$ LANGUAGE plpgsql; 429 430CREATE OR REPLACE FUNCTION drop_sql_command() 431RETURNS event_trigger AS $$ 432BEGIN 433RAISE NOTICE '% - sql_drop', tg_tag; 434END; 435$$ LANGUAGE plpgsql; 436 437CREATE EVENT TRIGGER start_rls_command ON ddl_command_start 438 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE start_command(); 439 440CREATE EVENT TRIGGER end_rls_command ON ddl_command_end 441 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE end_command(); 442 443CREATE EVENT TRIGGER sql_drop_command ON sql_drop 444 WHEN TAG IN ('DROP POLICY') EXECUTE PROCEDURE drop_sql_command(); 445 446CREATE POLICY p1 ON event_trigger_test USING (FALSE); 447ALTER POLICY p1 ON event_trigger_test USING (TRUE); 448ALTER POLICY p1 ON event_trigger_test RENAME TO p2; 449DROP POLICY p2 ON event_trigger_test; 450 451-- Check the object addresses of all the event triggers. 452SELECT 453 e.evtname, 454 pg_describe_object('pg_event_trigger'::regclass, e.oid, 0) as descr, 455 b.type, b.object_names, b.object_args, 456 pg_identify_object(a.classid, a.objid, a.objsubid) as ident 457 FROM pg_event_trigger as e, 458 LATERAL pg_identify_object_as_address('pg_event_trigger'::regclass, e.oid, 0) as b, 459 LATERAL pg_get_object_address(b.type, b.object_names, b.object_args) as a 460 ORDER BY e.evtname; 461 462DROP EVENT TRIGGER start_rls_command; 463DROP EVENT TRIGGER end_rls_command; 464DROP EVENT TRIGGER sql_drop_command; 465