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