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 procedure 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-- all OK 96alter event trigger regress_event_trigger enable replica; 97alter event trigger regress_event_trigger enable always; 98alter event trigger regress_event_trigger enable; 99alter event trigger regress_event_trigger disable; 100 101-- regress_event_trigger2 and regress_event_trigger_end should fire, but not 102-- regress_event_trigger 103create table event_trigger_fire1 (a int); 104 105-- regress_event_trigger_end should fire on these commands 106grant all on table event_trigger_fire1 to public; 107comment on table event_trigger_fire1 is 'here is a comment'; 108revoke all on table event_trigger_fire1 from public; 109drop table event_trigger_fire1; 110create foreign data wrapper useless; 111create server useless_server foreign data wrapper useless; 112create user mapping for regress_evt_user server useless_server; 113alter default privileges for role regress_evt_user 114 revoke delete on tables from regress_evt_user; 115 116-- alter owner to non-superuser should fail 117alter event trigger regress_event_trigger owner to regress_evt_user; 118 119-- alter owner to superuser should work 120alter role regress_evt_user superuser; 121alter event trigger regress_event_trigger owner to regress_evt_user; 122 123-- should fail, name collision 124alter event trigger regress_event_trigger rename to regress_event_trigger2; 125 126-- OK 127alter event trigger regress_event_trigger rename to regress_event_trigger3; 128 129-- should fail, doesn't exist any more 130drop event trigger regress_event_trigger; 131 132-- should fail, regress_evt_user owns some objects 133drop role regress_evt_user; 134 135-- cleanup before next test 136-- these are all OK; the second one should emit a NOTICE 137drop event trigger if exists regress_event_trigger2; 138drop event trigger if exists regress_event_trigger2; 139drop event trigger regress_event_trigger3; 140drop event trigger regress_event_trigger_end; 141 142-- test support for dropped objects 143CREATE SCHEMA schema_one authorization regress_evt_user; 144CREATE SCHEMA schema_two authorization regress_evt_user; 145CREATE SCHEMA audit_tbls authorization regress_evt_user; 146CREATE TEMP TABLE a_temp_tbl (); 147SET SESSION AUTHORIZATION regress_evt_user; 148 149CREATE TABLE schema_one.table_one(a int); 150CREATE TABLE schema_one."table two"(a int); 151CREATE TABLE schema_one.table_three(a int); 152CREATE TABLE audit_tbls.schema_one_table_two(the_value text); 153 154CREATE TABLE schema_two.table_two(a int); 155CREATE TABLE schema_two.table_three(a int, b text); 156CREATE TABLE audit_tbls.schema_two_table_three(the_value text); 157 158CREATE OR REPLACE FUNCTION schema_two.add(int, int) RETURNS int LANGUAGE plpgsql 159 CALLED ON NULL INPUT 160 AS $$ BEGIN RETURN coalesce($1,0) + coalesce($2,0); END; $$; 161CREATE AGGREGATE schema_two.newton 162 (BASETYPE = int, SFUNC = schema_two.add, STYPE = int); 163 164RESET SESSION AUTHORIZATION; 165 166CREATE TABLE undroppable_objs ( 167 object_type text, 168 object_identity text 169); 170INSERT INTO undroppable_objs VALUES 171('table', 'schema_one.table_three'), 172('table', 'audit_tbls.schema_two_table_three'); 173 174CREATE TABLE dropped_objects ( 175 type text, 176 schema text, 177 object text 178); 179 180-- This tests errors raised within event triggers; the one in audit_tbls 181-- uses 2nd-level recursive invocation via test_evtrig_dropped_objects(). 182CREATE OR REPLACE FUNCTION undroppable() RETURNS event_trigger 183LANGUAGE plpgsql AS $$ 184DECLARE 185 obj record; 186BEGIN 187 PERFORM 1 FROM pg_tables WHERE tablename = 'undroppable_objs'; 188 IF NOT FOUND THEN 189 RAISE NOTICE 'table undroppable_objs not found, skipping'; 190 RETURN; 191 END IF; 192 FOR obj IN 193 SELECT * FROM pg_event_trigger_dropped_objects() JOIN 194 undroppable_objs USING (object_type, object_identity) 195 LOOP 196 RAISE EXCEPTION 'object % of type % cannot be dropped', 197 obj.object_identity, obj.object_type; 198 END LOOP; 199END; 200$$; 201 202CREATE EVENT TRIGGER undroppable ON sql_drop 203 EXECUTE PROCEDURE undroppable(); 204 205CREATE OR REPLACE FUNCTION test_evtrig_dropped_objects() RETURNS event_trigger 206LANGUAGE plpgsql AS $$ 207DECLARE 208 obj record; 209BEGIN 210 FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() 211 LOOP 212 IF obj.object_type = 'table' THEN 213 EXECUTE format('DROP TABLE IF EXISTS audit_tbls.%I', 214 format('%s_%s', obj.schema_name, obj.object_name)); 215 END IF; 216 217 INSERT INTO dropped_objects 218 (type, schema, object) VALUES 219 (obj.object_type, obj.schema_name, obj.object_identity); 220 END LOOP; 221END 222$$; 223 224CREATE EVENT TRIGGER regress_event_trigger_drop_objects ON sql_drop 225 WHEN TAG IN ('drop table', 'drop function', 'drop view', 226 'drop owned', 'drop schema', 'alter table') 227 EXECUTE PROCEDURE test_evtrig_dropped_objects(); 228 229ALTER TABLE schema_one.table_one DROP COLUMN a; 230DROP SCHEMA schema_one, schema_two CASCADE; 231DELETE FROM undroppable_objs WHERE object_identity = 'audit_tbls.schema_two_table_three'; 232DROP SCHEMA schema_one, schema_two CASCADE; 233DELETE FROM undroppable_objs WHERE object_identity = 'schema_one.table_three'; 234DROP SCHEMA schema_one, schema_two CASCADE; 235 236SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast'; 237 238DROP OWNED BY regress_evt_user; 239SELECT * FROM dropped_objects WHERE type = 'schema'; 240 241DROP ROLE regress_evt_user; 242 243DROP EVENT TRIGGER regress_event_trigger_drop_objects; 244DROP EVENT TRIGGER undroppable; 245 246CREATE OR REPLACE FUNCTION event_trigger_report_dropped() 247 RETURNS event_trigger 248 LANGUAGE plpgsql 249AS $$ 250DECLARE r record; 251BEGIN 252 FOR r IN SELECT * from pg_event_trigger_dropped_objects() 253 LOOP 254 IF NOT r.normal AND NOT r.original THEN 255 CONTINUE; 256 END IF; 257 RAISE NOTICE 'NORMAL: orig=% normal=% istemp=% type=% identity=% name=% args=%', 258 r.original, r.normal, r.is_temporary, r.object_type, 259 r.object_identity, r.address_names, r.address_args; 260 END LOOP; 261END; $$; 262CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop 263 EXECUTE PROCEDURE event_trigger_report_dropped(); 264CREATE SCHEMA evttrig 265 CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two') 266 CREATE INDEX one_idx ON one (col_b) 267 CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42); 268 269-- Partitioned tables 270CREATE TABLE evttrig.parted ( 271 id int) 272 PARTITION BY RANGE (id); 273CREATE TABLE evttrig.part_1_10 PARTITION OF evttrig.parted (id) 274 FOR VALUES FROM (1) TO (10); 275CREATE TABLE evttrig.part_10_20 PARTITION OF evttrig.parted (id) 276 FOR VALUES FROM (10) TO (20) PARTITION BY RANGE (id); 277CREATE TABLE evttrig.part_10_15 PARTITION OF evttrig.part_10_20 (id) 278 FOR VALUES FROM (10) TO (15); 279CREATE TABLE evttrig.part_15_20 PARTITION OF evttrig.part_10_20 (id) 280 FOR VALUES FROM (15) TO (20); 281 282ALTER TABLE evttrig.two DROP COLUMN col_c; 283ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT; 284ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey; 285DROP INDEX evttrig.one_idx; 286DROP SCHEMA evttrig CASCADE; 287DROP TABLE a_temp_tbl; 288 289DROP EVENT TRIGGER regress_event_trigger_report_dropped; 290 291-- only allowed from within an event trigger function, should fail 292select pg_event_trigger_table_rewrite_oid(); 293 294-- test Table Rewrite Event Trigger 295CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 296LANGUAGE plpgsql AS $$ 297BEGIN 298 RAISE EXCEPTION 'rewrites not allowed'; 299END; 300$$; 301 302create event trigger no_rewrite_allowed on table_rewrite 303 execute procedure test_evtrig_no_rewrite(); 304 305create table rewriteme (id serial primary key, foo float); 306insert into rewriteme 307 select x * 1.001 from generate_series(1, 500) as t(x); 308alter table rewriteme alter column foo type numeric; 309alter table rewriteme add column baz int default 0; 310 311-- test with more than one reason to rewrite a single table 312CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 313LANGUAGE plpgsql AS $$ 314BEGIN 315 RAISE NOTICE 'Table ''%'' is being rewritten (reason = %)', 316 pg_event_trigger_table_rewrite_oid()::regclass, 317 pg_event_trigger_table_rewrite_reason(); 318END; 319$$; 320 321alter table rewriteme 322 add column onemore int default 0, 323 add column another int default -1, 324 alter column foo type numeric(10,4); 325 326-- shouldn't trigger a table_rewrite event 327alter table rewriteme alter column foo type numeric(12,4); 328 329-- typed tables are rewritten when their type changes. Don't emit table 330-- name, because firing order is not stable. 331CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 332LANGUAGE plpgsql AS $$ 333BEGIN 334 RAISE NOTICE 'Table is being rewritten (reason = %)', 335 pg_event_trigger_table_rewrite_reason(); 336END; 337$$; 338 339create type rewritetype as (a int); 340create table rewritemetoo1 of rewritetype; 341create table rewritemetoo2 of rewritetype; 342alter type rewritetype alter attribute a type text cascade; 343 344-- but this doesn't work 345create table rewritemetoo3 (a rewritetype); 346alter type rewritetype alter attribute a type varchar cascade; 347 348drop table rewriteme; 349drop event trigger no_rewrite_allowed; 350drop function test_evtrig_no_rewrite(); 351 352-- test Row Security Event Trigger 353RESET SESSION AUTHORIZATION; 354CREATE TABLE event_trigger_test (a integer, b text); 355 356CREATE OR REPLACE FUNCTION start_command() 357RETURNS event_trigger AS $$ 358BEGIN 359RAISE NOTICE '% - ddl_command_start', tg_tag; 360END; 361$$ LANGUAGE plpgsql; 362 363CREATE OR REPLACE FUNCTION end_command() 364RETURNS event_trigger AS $$ 365BEGIN 366RAISE NOTICE '% - ddl_command_end', tg_tag; 367END; 368$$ LANGUAGE plpgsql; 369 370CREATE OR REPLACE FUNCTION drop_sql_command() 371RETURNS event_trigger AS $$ 372BEGIN 373RAISE NOTICE '% - sql_drop', tg_tag; 374END; 375$$ LANGUAGE plpgsql; 376 377CREATE EVENT TRIGGER start_rls_command ON ddl_command_start 378 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE start_command(); 379 380CREATE EVENT TRIGGER end_rls_command ON ddl_command_end 381 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE end_command(); 382 383CREATE EVENT TRIGGER sql_drop_command ON sql_drop 384 WHEN TAG IN ('DROP POLICY') EXECUTE PROCEDURE drop_sql_command(); 385 386CREATE POLICY p1 ON event_trigger_test USING (FALSE); 387ALTER POLICY p1 ON event_trigger_test USING (TRUE); 388ALTER POLICY p1 ON event_trigger_test RENAME TO p2; 389DROP POLICY p2 ON event_trigger_test; 390 391-- Check the object addresses of all the event triggers. 392SELECT 393 e.evtname, 394 pg_describe_object('pg_event_trigger'::regclass, e.oid, 0) as descr, 395 b.type, b.object_names, b.object_args, 396 pg_identify_object(a.classid, a.objid, a.objsubid) as ident 397 FROM pg_event_trigger as e, 398 LATERAL pg_identify_object_as_address('pg_event_trigger'::regclass, e.oid, 0) as b, 399 LATERAL pg_get_object_address(b.type, b.object_names, b.object_args) as a 400 ORDER BY e.evtname; 401 402DROP EVENT TRIGGER start_rls_command; 403DROP EVENT TRIGGER end_rls_command; 404DROP EVENT TRIGGER sql_drop_command; 405