1-- should fail, return type mismatch 2create event trigger regress_event_trigger 3 on ddl_command_start 4 execute procedure pg_backend_pid(); 5ERROR: function pg_backend_pid must return type event_trigger 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-- should fail, can't call it as a plain function 13SELECT test_event_trigger(); 14ERROR: trigger functions can only be called as triggers 15CONTEXT: compilation of PL/pgSQL function "test_event_trigger" near line 1 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; 19ERROR: event trigger functions cannot have declared arguments 20CONTEXT: compilation of PL/pgSQL function "test_event_trigger_arg" near line 1 21-- should fail, SQL functions cannot be event triggers 22create function test_event_trigger_sql() returns event_trigger as $$ 23SELECT 1 $$ language sql; 24ERROR: SQL functions cannot return type event_trigger 25-- should fail, no elephant_bootstrap entry point 26create event trigger regress_event_trigger on elephant_bootstrap 27 execute procedure test_event_trigger(); 28ERROR: unrecognized event name "elephant_bootstrap" 29-- OK 30create event trigger regress_event_trigger on ddl_command_start 31 execute procedure test_event_trigger(); 32-- OK 33create event trigger regress_event_trigger_end on ddl_command_end 34 execute function test_event_trigger(); 35-- should fail, food is not a valid filter variable 36create event trigger regress_event_trigger2 on ddl_command_start 37 when food in ('sandwich') 38 execute procedure test_event_trigger(); 39ERROR: unrecognized filter variable "food" 40-- should fail, sandwich is not a valid command tag 41create event trigger regress_event_trigger2 on ddl_command_start 42 when tag in ('sandwich') 43 execute procedure test_event_trigger(); 44ERROR: filter value "sandwich" not recognized for filter variable "tag" 45-- should fail, create skunkcabbage is not a valid command tag 46create event trigger regress_event_trigger2 on ddl_command_start 47 when tag in ('create table', 'create skunkcabbage') 48 execute procedure test_event_trigger(); 49ERROR: filter value "create skunkcabbage" not recognized for filter variable "tag" 50-- should fail, can't have event triggers on event triggers 51create event trigger regress_event_trigger2 on ddl_command_start 52 when tag in ('DROP EVENT TRIGGER') 53 execute procedure test_event_trigger(); 54ERROR: event triggers are not supported for DROP EVENT TRIGGER 55-- should fail, can't have event triggers on global objects 56create event trigger regress_event_trigger2 on ddl_command_start 57 when tag in ('CREATE ROLE') 58 execute procedure test_event_trigger(); 59ERROR: event triggers are not supported for CREATE ROLE 60-- should fail, can't have event triggers on global objects 61create event trigger regress_event_trigger2 on ddl_command_start 62 when tag in ('CREATE DATABASE') 63 execute procedure test_event_trigger(); 64ERROR: event triggers are not supported for CREATE DATABASE 65-- should fail, can't have event triggers on global objects 66create event trigger regress_event_trigger2 on ddl_command_start 67 when tag in ('CREATE TABLESPACE') 68 execute procedure test_event_trigger(); 69ERROR: event triggers are not supported for CREATE TABLESPACE 70-- should fail, can't have same filter variable twice 71create event trigger regress_event_trigger2 on ddl_command_start 72 when tag in ('create table') and tag in ('CREATE FUNCTION') 73 execute procedure test_event_trigger(); 74ERROR: filter variable "tag" specified more than once 75-- should fail, can't have arguments 76create event trigger regress_event_trigger2 on ddl_command_start 77 execute procedure test_event_trigger('argument not allowed'); 78ERROR: syntax error at or near "'argument not allowed'" 79LINE 2: execute procedure test_event_trigger('argument not allowe... 80 ^ 81-- OK 82create event trigger regress_event_trigger2 on ddl_command_start 83 when tag in ('create table', 'CREATE FUNCTION') 84 execute procedure test_event_trigger(); 85-- OK 86comment on event trigger regress_event_trigger is 'test comment'; 87-- drop as non-superuser should fail 88create role regress_evt_user; 89set role regress_evt_user; 90create event trigger regress_event_trigger_noperms on ddl_command_start 91 execute procedure test_event_trigger(); 92ERROR: permission denied to create event trigger "regress_event_trigger_noperms" 93HINT: Must be superuser to create an event trigger. 94reset role; 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); 99NOTICE: test_event_trigger: ddl_command_start CREATE TABLE 100NOTICE: test_event_trigger: ddl_command_end CREATE TABLE 101alter event trigger regress_event_trigger enable; 102set session_replication_role = replica; 103-- fires nothing 104create table event_trigger_fire2 (a int); 105alter event trigger regress_event_trigger enable replica; 106-- fires only _trigger 107create table event_trigger_fire3 (a int); 108NOTICE: test_event_trigger: ddl_command_start CREATE TABLE 109alter event trigger regress_event_trigger enable always; 110-- fires only _trigger 111create table event_trigger_fire4 (a int); 112NOTICE: test_event_trigger: ddl_command_start CREATE TABLE 113reset session_replication_role; 114-- fires all three 115create table event_trigger_fire5 (a int); 116NOTICE: test_event_trigger: ddl_command_start CREATE TABLE 117NOTICE: test_event_trigger: ddl_command_start CREATE TABLE 118NOTICE: test_event_trigger: ddl_command_end CREATE TABLE 119-- non-top-level command 120create function f1() returns int 121language plpgsql 122as $$ 123begin 124 create table event_trigger_fire6 (a int); 125 return 0; 126end $$; 127NOTICE: test_event_trigger: ddl_command_start CREATE FUNCTION 128NOTICE: test_event_trigger: ddl_command_start CREATE FUNCTION 129NOTICE: test_event_trigger: ddl_command_end CREATE FUNCTION 130select f1(); 131NOTICE: test_event_trigger: ddl_command_start CREATE TABLE 132NOTICE: test_event_trigger: ddl_command_start CREATE TABLE 133NOTICE: test_event_trigger: ddl_command_end CREATE TABLE 134 f1 135---- 136 0 137(1 row) 138 139-- non-top-level command 140create procedure p1() 141language plpgsql 142as $$ 143begin 144 create table event_trigger_fire7 (a int); 145end $$; 146NOTICE: test_event_trigger: ddl_command_start CREATE PROCEDURE 147NOTICE: test_event_trigger: ddl_command_end CREATE PROCEDURE 148call p1(); 149NOTICE: test_event_trigger: ddl_command_start CREATE TABLE 150NOTICE: test_event_trigger: ddl_command_start CREATE TABLE 151NOTICE: test_event_trigger: ddl_command_end CREATE TABLE 152-- clean up 153alter event trigger regress_event_trigger disable; 154drop table event_trigger_fire2, event_trigger_fire3, event_trigger_fire4, event_trigger_fire5, event_trigger_fire6, event_trigger_fire7; 155NOTICE: test_event_trigger: ddl_command_end DROP TABLE 156drop routine f1(), p1(); 157NOTICE: test_event_trigger: ddl_command_end DROP ROUTINE 158-- regress_event_trigger_end should fire on these commands 159grant all on table event_trigger_fire1 to public; 160NOTICE: test_event_trigger: ddl_command_end GRANT 161comment on table event_trigger_fire1 is 'here is a comment'; 162NOTICE: test_event_trigger: ddl_command_end COMMENT 163revoke all on table event_trigger_fire1 from public; 164NOTICE: test_event_trigger: ddl_command_end REVOKE 165drop table event_trigger_fire1; 166NOTICE: test_event_trigger: ddl_command_end DROP TABLE 167create foreign data wrapper useless; 168NOTICE: test_event_trigger: ddl_command_end CREATE FOREIGN DATA WRAPPER 169create server useless_server foreign data wrapper useless; 170NOTICE: test_event_trigger: ddl_command_end CREATE SERVER 171create user mapping for regress_evt_user server useless_server; 172NOTICE: test_event_trigger: ddl_command_end CREATE USER MAPPING 173alter default privileges for role regress_evt_user 174 revoke delete on tables from regress_evt_user; 175NOTICE: test_event_trigger: ddl_command_end ALTER DEFAULT PRIVILEGES 176-- alter owner to non-superuser should fail 177alter event trigger regress_event_trigger owner to regress_evt_user; 178ERROR: permission denied to change owner of event trigger "regress_event_trigger" 179HINT: The owner of an event trigger must be a superuser. 180-- alter owner to superuser should work 181alter role regress_evt_user superuser; 182alter event trigger regress_event_trigger owner to regress_evt_user; 183-- should fail, name collision 184alter event trigger regress_event_trigger rename to regress_event_trigger2; 185ERROR: event trigger "regress_event_trigger2" already exists 186-- OK 187alter event trigger regress_event_trigger rename to regress_event_trigger3; 188-- should fail, doesn't exist any more 189drop event trigger regress_event_trigger; 190ERROR: event trigger "regress_event_trigger" does not exist 191-- should fail, regress_evt_user owns some objects 192drop role regress_evt_user; 193ERROR: role "regress_evt_user" cannot be dropped because some objects depend on it 194DETAIL: owner of event trigger regress_event_trigger3 195owner of user mapping for regress_evt_user on server useless_server 196owner of default privileges on new relations belonging to role regress_evt_user 197-- cleanup before next test 198-- these are all OK; the second one should emit a NOTICE 199drop event trigger if exists regress_event_trigger2; 200drop event trigger if exists regress_event_trigger2; 201NOTICE: event trigger "regress_event_trigger2" does not exist, skipping 202drop event trigger regress_event_trigger3; 203drop event trigger regress_event_trigger_end; 204-- test support for dropped objects 205CREATE SCHEMA schema_one authorization regress_evt_user; 206CREATE SCHEMA schema_two authorization regress_evt_user; 207CREATE SCHEMA audit_tbls authorization regress_evt_user; 208CREATE TEMP TABLE a_temp_tbl (); 209SET SESSION AUTHORIZATION regress_evt_user; 210CREATE TABLE schema_one.table_one(a int); 211CREATE TABLE schema_one."table two"(a int); 212CREATE TABLE schema_one.table_three(a int); 213CREATE TABLE audit_tbls.schema_one_table_two(the_value text); 214CREATE TABLE schema_two.table_two(a int); 215CREATE TABLE schema_two.table_three(a int, b text); 216CREATE TABLE audit_tbls.schema_two_table_three(the_value text); 217CREATE OR REPLACE FUNCTION schema_two.add(int, int) RETURNS int LANGUAGE plpgsql 218 CALLED ON NULL INPUT 219 AS $$ BEGIN RETURN coalesce($1,0) + coalesce($2,0); END; $$; 220CREATE AGGREGATE schema_two.newton 221 (BASETYPE = int, SFUNC = schema_two.add, STYPE = int); 222RESET SESSION AUTHORIZATION; 223CREATE TABLE undroppable_objs ( 224 object_type text, 225 object_identity text 226); 227INSERT INTO undroppable_objs VALUES 228('table', 'schema_one.table_three'), 229('table', 'audit_tbls.schema_two_table_three'); 230CREATE TABLE dropped_objects ( 231 type text, 232 schema text, 233 object text 234); 235-- This tests errors raised within event triggers; the one in audit_tbls 236-- uses 2nd-level recursive invocation via test_evtrig_dropped_objects(). 237CREATE OR REPLACE FUNCTION undroppable() RETURNS event_trigger 238LANGUAGE plpgsql AS $$ 239DECLARE 240 obj record; 241BEGIN 242 PERFORM 1 FROM pg_tables WHERE tablename = 'undroppable_objs'; 243 IF NOT FOUND THEN 244 RAISE NOTICE 'table undroppable_objs not found, skipping'; 245 RETURN; 246 END IF; 247 FOR obj IN 248 SELECT * FROM pg_event_trigger_dropped_objects() JOIN 249 undroppable_objs USING (object_type, object_identity) 250 LOOP 251 RAISE EXCEPTION 'object % of type % cannot be dropped', 252 obj.object_identity, obj.object_type; 253 END LOOP; 254END; 255$$; 256CREATE EVENT TRIGGER undroppable ON sql_drop 257 EXECUTE PROCEDURE undroppable(); 258CREATE OR REPLACE FUNCTION test_evtrig_dropped_objects() RETURNS event_trigger 259LANGUAGE plpgsql AS $$ 260DECLARE 261 obj record; 262BEGIN 263 FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() 264 LOOP 265 IF obj.object_type = 'table' THEN 266 EXECUTE format('DROP TABLE IF EXISTS audit_tbls.%I', 267 format('%s_%s', obj.schema_name, obj.object_name)); 268 END IF; 269 270 INSERT INTO dropped_objects 271 (type, schema, object) VALUES 272 (obj.object_type, obj.schema_name, obj.object_identity); 273 END LOOP; 274END 275$$; 276CREATE EVENT TRIGGER regress_event_trigger_drop_objects ON sql_drop 277 WHEN TAG IN ('drop table', 'drop function', 'drop view', 278 'drop owned', 'drop schema', 'alter table') 279 EXECUTE PROCEDURE test_evtrig_dropped_objects(); 280ALTER TABLE schema_one.table_one DROP COLUMN a; 281DROP SCHEMA schema_one, schema_two CASCADE; 282NOTICE: drop cascades to 7 other objects 283DETAIL: drop cascades to table schema_two.table_two 284drop cascades to table schema_two.table_three 285drop cascades to function schema_two.add(integer,integer) 286drop cascades to function schema_two.newton(integer) 287drop cascades to table schema_one.table_one 288drop cascades to table schema_one."table two" 289drop cascades to table schema_one.table_three 290NOTICE: table "schema_two_table_two" does not exist, skipping 291NOTICE: table "audit_tbls_schema_two_table_three" does not exist, skipping 292ERROR: object audit_tbls.schema_two_table_three of type table cannot be dropped 293CONTEXT: PL/pgSQL function undroppable() line 14 at RAISE 294SQL statement "DROP TABLE IF EXISTS audit_tbls.schema_two_table_three" 295PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE 296DELETE FROM undroppable_objs WHERE object_identity = 'audit_tbls.schema_two_table_three'; 297DROP SCHEMA schema_one, schema_two CASCADE; 298NOTICE: drop cascades to 7 other objects 299DETAIL: drop cascades to table schema_two.table_two 300drop cascades to table schema_two.table_three 301drop cascades to function schema_two.add(integer,integer) 302drop cascades to function schema_two.newton(integer) 303drop cascades to table schema_one.table_one 304drop cascades to table schema_one."table two" 305drop cascades to table schema_one.table_three 306NOTICE: table "schema_two_table_two" does not exist, skipping 307NOTICE: table "audit_tbls_schema_two_table_three" does not exist, skipping 308NOTICE: table "schema_one_table_one" does not exist, skipping 309NOTICE: table "schema_one_table two" does not exist, skipping 310NOTICE: table "schema_one_table_three" does not exist, skipping 311ERROR: object schema_one.table_three of type table cannot be dropped 312CONTEXT: PL/pgSQL function undroppable() line 14 at RAISE 313DELETE FROM undroppable_objs WHERE object_identity = 'schema_one.table_three'; 314DROP SCHEMA schema_one, schema_two CASCADE; 315NOTICE: drop cascades to 7 other objects 316DETAIL: drop cascades to table schema_two.table_two 317drop cascades to table schema_two.table_three 318drop cascades to function schema_two.add(integer,integer) 319drop cascades to function schema_two.newton(integer) 320drop cascades to table schema_one.table_one 321drop cascades to table schema_one."table two" 322drop cascades to table schema_one.table_three 323NOTICE: table "schema_two_table_two" does not exist, skipping 324NOTICE: table "audit_tbls_schema_two_table_three" does not exist, skipping 325NOTICE: table "schema_one_table_one" does not exist, skipping 326NOTICE: table "schema_one_table two" does not exist, skipping 327NOTICE: table "schema_one_table_three" does not exist, skipping 328SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast'; 329 type | schema | object 330--------------+------------+------------------------------------- 331 table column | schema_one | schema_one.table_one.a 332 schema | | schema_two 333 table | schema_two | schema_two.table_two 334 type | schema_two | schema_two.table_two 335 type | schema_two | schema_two.table_two[] 336 table | audit_tbls | audit_tbls.schema_two_table_three 337 type | audit_tbls | audit_tbls.schema_two_table_three 338 type | audit_tbls | audit_tbls.schema_two_table_three[] 339 table | schema_two | schema_two.table_three 340 type | schema_two | schema_two.table_three 341 type | schema_two | schema_two.table_three[] 342 function | schema_two | schema_two.add(integer,integer) 343 aggregate | schema_two | schema_two.newton(integer) 344 schema | | schema_one 345 table | schema_one | schema_one.table_one 346 type | schema_one | schema_one.table_one 347 type | schema_one | schema_one.table_one[] 348 table | schema_one | schema_one."table two" 349 type | schema_one | schema_one."table two" 350 type | schema_one | schema_one."table two"[] 351 table | schema_one | schema_one.table_three 352 type | schema_one | schema_one.table_three 353 type | schema_one | schema_one.table_three[] 354(23 rows) 355 356DROP OWNED BY regress_evt_user; 357NOTICE: schema "audit_tbls" does not exist, skipping 358SELECT * FROM dropped_objects WHERE type = 'schema'; 359 type | schema | object 360--------+--------+------------ 361 schema | | schema_two 362 schema | | schema_one 363 schema | | audit_tbls 364(3 rows) 365 366DROP ROLE regress_evt_user; 367DROP EVENT TRIGGER regress_event_trigger_drop_objects; 368DROP EVENT TRIGGER undroppable; 369-- Event triggers on relations. 370CREATE OR REPLACE FUNCTION event_trigger_report_dropped() 371 RETURNS event_trigger 372 LANGUAGE plpgsql 373AS $$ 374DECLARE r record; 375BEGIN 376 FOR r IN SELECT * from pg_event_trigger_dropped_objects() 377 LOOP 378 IF NOT r.normal AND NOT r.original THEN 379 CONTINUE; 380 END IF; 381 RAISE NOTICE 'NORMAL: orig=% normal=% istemp=% type=% identity=% name=% args=%', 382 r.original, r.normal, r.is_temporary, r.object_type, 383 r.object_identity, r.address_names, r.address_args; 384 END LOOP; 385END; $$; 386CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop 387 EXECUTE PROCEDURE event_trigger_report_dropped(); 388CREATE OR REPLACE FUNCTION event_trigger_report_end() 389 RETURNS event_trigger 390 LANGUAGE plpgsql 391AS $$ 392DECLARE r RECORD; 393BEGIN 394 FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() 395 LOOP 396 RAISE NOTICE 'END: command_tag=% type=% identity=%', 397 r.command_tag, r.object_type, r.object_identity; 398 END LOOP; 399END; $$; 400CREATE EVENT TRIGGER regress_event_trigger_report_end ON ddl_command_end 401 EXECUTE PROCEDURE event_trigger_report_end(); 402CREATE SCHEMA evttrig 403 CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two', col_c SERIAL) 404 CREATE INDEX one_idx ON one (col_b) 405 CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42) 406 CREATE TABLE id (col_d int NOT NULL GENERATED ALWAYS AS IDENTITY); 407NOTICE: END: command_tag=CREATE SCHEMA type=schema identity=evttrig 408NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_a_seq 409NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_c_seq 410NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.one 411NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_pkey 412NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_a_seq 413NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_c_seq 414NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.two 415NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two 416NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.id_col_d_seq 417NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.id 418NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq 419NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx 420-- Partitioned tables with a partitioned index 421CREATE TABLE evttrig.parted ( 422 id int PRIMARY KEY) 423 PARTITION BY RANGE (id); 424NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.parted 425NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.parted_pkey 426CREATE TABLE evttrig.part_1_10 PARTITION OF evttrig.parted (id) 427 FOR VALUES FROM (1) TO (10); 428NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_1_10 429CREATE TABLE evttrig.part_10_20 PARTITION OF evttrig.parted (id) 430 FOR VALUES FROM (10) TO (20) PARTITION BY RANGE (id); 431NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_10_20 432CREATE TABLE evttrig.part_10_15 PARTITION OF evttrig.part_10_20 (id) 433 FOR VALUES FROM (10) TO (15); 434NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_10_15 435CREATE TABLE evttrig.part_15_20 PARTITION OF evttrig.part_10_20 (id) 436 FOR VALUES FROM (15) TO (20); 437NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_15_20 438ALTER TABLE evttrig.two DROP COLUMN col_c; 439NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.two.col_c name={evttrig,two,col_c} args={} 440NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=two_col_c_check on evttrig.two name={evttrig,two,two_col_c_check} args={} 441NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two 442ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT; 443NOTICE: NORMAL: orig=t normal=f istemp=f type=default value identity=for evttrig.one.col_b name={evttrig,one,col_b} args={} 444NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one 445ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey; 446NOTICE: NORMAL: orig=t normal=f istemp=f type=table constraint identity=one_pkey on evttrig.one name={evttrig,one,one_pkey} args={} 447NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one 448ALTER TABLE evttrig.one DROP COLUMN col_c; 449NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.one.col_c name={evttrig,one,col_c} args={} 450NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_c name={evttrig,one,col_c} args={} 451NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one 452ALTER TABLE evttrig.id ALTER COLUMN col_d SET DATA TYPE bigint; 453NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq 454NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.id 455ALTER TABLE evttrig.id ALTER COLUMN col_d DROP IDENTITY, 456 ALTER COLUMN col_d SET DATA TYPE int; 457NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.id 458DROP INDEX evttrig.one_idx; 459NOTICE: NORMAL: orig=t normal=f istemp=f type=index identity=evttrig.one_idx name={evttrig,one_idx} args={} 460DROP SCHEMA evttrig CASCADE; 461NOTICE: drop cascades to 4 other objects 462DETAIL: drop cascades to table evttrig.one 463drop cascades to table evttrig.two 464drop cascades to table evttrig.id 465drop cascades to table evttrig.parted 466NOTICE: NORMAL: orig=t normal=f istemp=f type=schema identity=evttrig name={evttrig} args={} 467NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.one name={evttrig,one} args={} 468NOTICE: NORMAL: orig=f normal=t istemp=f type=sequence identity=evttrig.one_col_a_seq name={evttrig,one_col_a_seq} args={} 469NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_a name={evttrig,one,col_a} args={} 470NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.two name={evttrig,two} args={} 471NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.id name={evttrig,id} args={} 472NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.parted name={evttrig,parted} args={} 473NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_1_10 name={evttrig,part_1_10} args={} 474NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_20 name={evttrig,part_10_20} args={} 475NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_15 name={evttrig,part_10_15} args={} 476NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_15_20 name={evttrig,part_15_20} args={} 477DROP TABLE a_temp_tbl; 478NOTICE: NORMAL: orig=t normal=f istemp=t type=table identity=pg_temp.a_temp_tbl name={pg_temp,a_temp_tbl} args={} 479DROP EVENT TRIGGER regress_event_trigger_report_dropped; 480DROP EVENT TRIGGER regress_event_trigger_report_end; 481-- only allowed from within an event trigger function, should fail 482select pg_event_trigger_table_rewrite_oid(); 483ERROR: pg_event_trigger_table_rewrite_oid() can only be called in a table_rewrite event trigger function 484-- test Table Rewrite Event Trigger 485CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 486LANGUAGE plpgsql AS $$ 487BEGIN 488 RAISE EXCEPTION 'rewrites not allowed'; 489END; 490$$; 491create event trigger no_rewrite_allowed on table_rewrite 492 execute procedure test_evtrig_no_rewrite(); 493create table rewriteme (id serial primary key, foo float, bar timestamptz); 494insert into rewriteme 495 select x * 1.001 from generate_series(1, 500) as t(x); 496alter table rewriteme alter column foo type numeric; 497ERROR: rewrites not allowed 498CONTEXT: PL/pgSQL function test_evtrig_no_rewrite() line 3 at RAISE 499alter table rewriteme add column baz int default 0; 500-- test with more than one reason to rewrite a single table 501CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 502LANGUAGE plpgsql AS $$ 503BEGIN 504 RAISE NOTICE 'Table ''%'' is being rewritten (reason = %)', 505 pg_event_trigger_table_rewrite_oid()::regclass, 506 pg_event_trigger_table_rewrite_reason(); 507END; 508$$; 509alter table rewriteme 510 add column onemore int default 0, 511 add column another int default -1, 512 alter column foo type numeric(10,4); 513NOTICE: Table 'rewriteme' is being rewritten (reason = 4) 514-- shouldn't trigger a table_rewrite event 515alter table rewriteme alter column foo type numeric(12,4); 516begin; 517set timezone to 'UTC'; 518alter table rewriteme alter column bar type timestamp; 519set timezone to '0'; 520alter table rewriteme alter column bar type timestamptz; 521set timezone to 'Europe/London'; 522alter table rewriteme alter column bar type timestamp; -- does rewrite 523NOTICE: Table 'rewriteme' is being rewritten (reason = 4) 524rollback; 525-- typed tables are rewritten when their type changes. Don't emit table 526-- name, because firing order is not stable. 527CREATE OR REPLACE FUNCTION test_evtrig_no_rewrite() RETURNS event_trigger 528LANGUAGE plpgsql AS $$ 529BEGIN 530 RAISE NOTICE 'Table is being rewritten (reason = %)', 531 pg_event_trigger_table_rewrite_reason(); 532END; 533$$; 534create type rewritetype as (a int); 535create table rewritemetoo1 of rewritetype; 536create table rewritemetoo2 of rewritetype; 537alter type rewritetype alter attribute a type text cascade; 538NOTICE: Table is being rewritten (reason = 4) 539NOTICE: Table is being rewritten (reason = 4) 540-- but this doesn't work 541create table rewritemetoo3 (a rewritetype); 542alter type rewritetype alter attribute a type varchar cascade; 543ERROR: cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it 544drop table rewriteme; 545drop event trigger no_rewrite_allowed; 546drop function test_evtrig_no_rewrite(); 547-- test Row Security Event Trigger 548RESET SESSION AUTHORIZATION; 549CREATE TABLE event_trigger_test (a integer, b text); 550CREATE OR REPLACE FUNCTION start_command() 551RETURNS event_trigger AS $$ 552BEGIN 553RAISE NOTICE '% - ddl_command_start', tg_tag; 554END; 555$$ LANGUAGE plpgsql; 556CREATE OR REPLACE FUNCTION end_command() 557RETURNS event_trigger AS $$ 558BEGIN 559RAISE NOTICE '% - ddl_command_end', tg_tag; 560END; 561$$ LANGUAGE plpgsql; 562CREATE OR REPLACE FUNCTION drop_sql_command() 563RETURNS event_trigger AS $$ 564BEGIN 565RAISE NOTICE '% - sql_drop', tg_tag; 566END; 567$$ LANGUAGE plpgsql; 568CREATE EVENT TRIGGER start_rls_command ON ddl_command_start 569 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE start_command(); 570CREATE EVENT TRIGGER end_rls_command ON ddl_command_end 571 WHEN TAG IN ('CREATE POLICY', 'ALTER POLICY', 'DROP POLICY') EXECUTE PROCEDURE end_command(); 572CREATE EVENT TRIGGER sql_drop_command ON sql_drop 573 WHEN TAG IN ('DROP POLICY') EXECUTE PROCEDURE drop_sql_command(); 574CREATE POLICY p1 ON event_trigger_test USING (FALSE); 575NOTICE: CREATE POLICY - ddl_command_start 576NOTICE: CREATE POLICY - ddl_command_end 577ALTER POLICY p1 ON event_trigger_test USING (TRUE); 578NOTICE: ALTER POLICY - ddl_command_start 579NOTICE: ALTER POLICY - ddl_command_end 580ALTER POLICY p1 ON event_trigger_test RENAME TO p2; 581NOTICE: ALTER POLICY - ddl_command_start 582NOTICE: ALTER POLICY - ddl_command_end 583DROP POLICY p2 ON event_trigger_test; 584NOTICE: DROP POLICY - ddl_command_start 585NOTICE: DROP POLICY - sql_drop 586NOTICE: DROP POLICY - ddl_command_end 587-- Check the object addresses of all the event triggers. 588SELECT 589 e.evtname, 590 pg_describe_object('pg_event_trigger'::regclass, e.oid, 0) as descr, 591 b.type, b.object_names, b.object_args, 592 pg_identify_object(a.classid, a.objid, a.objsubid) as ident 593 FROM pg_event_trigger as e, 594 LATERAL pg_identify_object_as_address('pg_event_trigger'::regclass, e.oid, 0) as b, 595 LATERAL pg_get_object_address(b.type, b.object_names, b.object_args) as a 596 ORDER BY e.evtname; 597 evtname | descr | type | object_names | object_args | ident 598-------------------+---------------------------------+---------------+---------------------+-------------+-------------------------------------------------------- 599 end_rls_command | event trigger end_rls_command | event trigger | {end_rls_command} | {} | ("event trigger",,end_rls_command,end_rls_command) 600 sql_drop_command | event trigger sql_drop_command | event trigger | {sql_drop_command} | {} | ("event trigger",,sql_drop_command,sql_drop_command) 601 start_rls_command | event trigger start_rls_command | event trigger | {start_rls_command} | {} | ("event trigger",,start_rls_command,start_rls_command) 602(3 rows) 603 604DROP EVENT TRIGGER start_rls_command; 605DROP EVENT TRIGGER end_rls_command; 606DROP EVENT TRIGGER sql_drop_command; 607