8000 Backpatch regression tests added by 2d689babe3cb · postgres/postgres@daf015f · GitHub
[go: up one dir, main page]
Skip to content

Commit daf015f

Browse files
alvherrealvherre
committed
Backpatch regression tests added by 2d689ba
A new plpgsql test function was added in 14 and up to cover for a bugfix that was not backpatchable. We can add it to older versions as a way to cover other bits of DDL event triggers, with an exception clause to avoid the problematic corner case. Originally authored by Michaël Paquier. Backpatch: 10 through 13. Discussion: https://postgr.es/m/202205201523.7m5jbfvyanmj@alvherre.pgsql
1 parent 54271c6 commit daf015f

File tree

2 files changed

+82
-5
lines changed

2 files changed

+82
-5
lines changed

‎src/test/regress/expected/event_trigger.out

Lines changed: 56 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -331,6 +331,7 @@ SELECT * FROM dropped_objects WHERE type = 'schema';
331331
DROP ROLE regress_evt_user;
332332
DROP EVENT TRIGGER regress_event_trigger_drop_objects;
333333
DROP EVENT TRIGGER undroppable;
334+
-- Event triggers on relations.
334335
CREATE OR REPLACE FUNCTION event_trigger_report_dropped()
335336
RETURNS event_trigger
336337
LANGUAGE plpgsql
@@ -349,41 +350,92 @@ BEGIN
349350
END; $$;
350351
CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop
351352
EXECUTE PROCEDURE event_trigger_report_dropped();
353+
CREATE OR REPLACE FUNCTION event_trigger_report_end()
354+
RETURNS event_trigger
355+
LANGUAGE plpgsql
356+
AS $$
357+
DECLARE r RECORD;
358+
BEGIN
359+
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
360+
LOOP
361+
RAISE NOTICE 'END: command_tag=% type=% identity=%',
362+
r.command_tag, r.object_type, r.object_identity;
363+
END LOOP;
364+
EXCEPTION WHEN SQLSTATE 'XX000' THEN
365+
RAISE NOTICE 'END: got internal exception';
366+
END; $$;
367+
CREATE EVENT TRIGGER regress_event_trigger_report_end ON ddl_command_end
368+
EXECUTE PROCEDURE event_trigger_report_end();
352369
CREATE SCHEMA evttrig
353-
CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two')
370+
CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two', col_c SERIAL)
354371
CREATE INDEX one_idx ON one (col_b)
355-
CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42);
372+
CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42)
373+
CREATE TABLE id (col_d int NOT NULL GENERATED ALWAYS AS IDENTITY);
374+
NOTICE: END: command_tag=CREATE SCHEMA type=schema identity=evttrig
375+
NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_a_seq
376+
NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_c_seq
377+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.one
378+
NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_pkey
379+
NOTICE: END: command_tag=AL 8000 TER SEQUENCE type=sequence identity=evttrig.one_col_a_seq
380+
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_c_seq
381+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.two
382+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two
383+
NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.id_col_d_seq
384+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.id
385+
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq
386+
NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx
356387
-- Partitioned tables with a partitioned index
357388
CREATE TABLE evttrig.parted (
358389
id int PRIMARY KEY)
359390
PARTITION BY RANGE (id);
391+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.parted
392+
NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.parted_pkey
360393
CREATE TABLE evttrig.part_1_10 PARTITION OF evttrig.parted (id)
361394
FOR VALUES FROM (1) TO (10);
395+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_1_10
362396
CREATE TABLE evttrig.part_10_20 PARTITION OF evttrig.parted (id)
363397
FOR VALUES FROM (10) TO (20) PARTITION BY RANGE (id);
398+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_10_20
364399
CREATE TABLE evttrig.part_10_15 PARTITION OF evttrig.part_10_20 (id)
365400
FOR VALUES FROM (10) TO (15);
401+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_10_15
366402
CREATE TABLE evttrig.part_15_20 PARTITION OF evttrig.part_10_20 (id)
367403
FOR VALUES FROM (15) TO (20);
404+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_15_20
368405
ALTER TABLE evttrig.two DROP COLUMN col_c;
369406
NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.two.col_c name={evttrig,two,col_c} args={}
370407
NOTICE: 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={}
408+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two
371409
ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT;
372410
NOTICE: NORMAL: orig=t normal=f istemp=f type=default value identity=for evttrig.one.col_b name={evttrig,one,col_b} args={}
411+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one
373412
ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey;
374413
NOTICE: NORMAL: orig=t normal=f istemp=f type=table constraint identity=one_pkey on evttrig.one name={evttrig,one,one_pkey} args={}
414+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one
415+
ALTER TABLE evttrig.one DROP COLUMN col_c;
416+
NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.one.col_c name={evttrig,one,col_c} args={}
417+
NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_c name={evttrig,one,col_c} args={}
418+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one
419+
ALTER TABLE evttrig.id ALTER COLUMN col_d SET DATA TYPE bigint;
420+
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq
421+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.id
422+
ALTER TABLE evttrig.id ALTER COLUMN col_d DROP IDENTITY,
423+
ALTER COLUMN col_d SET DATA TYPE int;
424+
NOTICE: END: got internal exception
375425
DROP INDEX evttrig.one_idx;
376426
NOTICE: NORMAL: orig=t normal=f istemp=f type=index identity=evttrig.one_idx name={evttrig,one_idx} args={}
377427
DROP SCHEMA evttrig CASCADE;
378-
NOTICE: drop cascades to 3 other objects
428+
NOTICE: drop cascades to 4 other objects
379429
DETAIL: drop cascades to table evttrig.one
380430
drop cascades to table evttrig.two
431+
drop cascades to table evttrig.id
381432
drop cascades to table evttrig.parted
382433
NOTICE: NORMAL: orig=t normal=f istemp=f type=schema identity=evttrig name={evttrig} args={}
383434
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.one name={evttrig,one} args={}
384435
NOTICE: NORMAL: orig=f normal=t istemp=f type=sequence identity=evttrig.one_col_a_seq name={evttrig,one_col_a_seq} args={}
385436
NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_a name={evttrig,one,col_a} args={}
386437
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.two name={evttrig,two} args={}
438+
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.id name={evttrig,id} args={}
387439
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.parted name={evttrig,parted} args={}
388440
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_1_10 name={evttrig,part_1_10} args={}
389441
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_20 name={evttrig,part_10_20} args={}
@@ -392,6 +444,7 @@ NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_15_20
392444
DROP TABLE a_temp_tbl;
393445
NOTICE: NORMAL: orig=t normal=f istemp=t type=table identity=pg_temp.a_temp_tbl name={pg_temp,a_temp_tbl} args={}
394446
DROP EVENT TRIGGER regress_event_trigger_report_dropped;
447+
DROP EVENT TRIGGER regress_event_trigger_report_end;
395448
-- only allowed from within an event trigger function, should fail
396449
select pg_event_trigger_table_rewrite_oid();
397450
ERROR: pg_event_trigger_table_rewrite_oid() can only be called in a table_rewrite event trigger function

‎src/test/regress/sql/event_trigger.sql

Lines changed: 26 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -254,6 +254,7 @@ DROP ROLE regress_evt_user;
254254
DROP EVENT TRIGGER regress_event_trigger_drop_objects;
255255
DROP EVENT TRIGGER undroppable;
256256

257+
-- Event triggers on relations.
257258
CREATE OR REPLACE FUNCTION event_trigger_report_dropped()
258259
RETURNS event_trigger
259260
LANGUAGE plpgsql
@@ -272,10 +273,28 @@ BEGIN
272273
END; $$;
273274
CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop
274275
EXECUTE PROCEDURE event_trigger_report_dropped();
276+
CREATE OR REPLACE FUNCTION event_trigger_report_end()
277+
RETURNS event_trigger
278+
LANGUAGE plpgsql
279+
AS $$
280+
DECLARE r RECORD;
281+
BEGIN
282+
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
283+
LOOP
284+
RAISE NOTICE 'END: command_tag=% type=% identity=%',
285+
r.command_tag, r.object_type, r.object_identity;
286+
END LOOP;
287+
EXCEPTION WHEN SQLSTATE 'XX000' THEN
288+
RAISE NOTICE 'END: got internal exception';
289+
END; $$;
290+
CREATE EVENT TRIGGER regress_event_trigger_report_end ON ddl_command_end
291+
EXECUTE PROCEDURE event_trigger_report_end();
292+
275293
CREATE SCHEMA evttrig
276-
CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two')
294+
CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two', col_c SERIAL)
277295
CREATE INDEX one_idx ON one (col_b)
278-
CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42);
296+
CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42)
297+
CREATE TABLE id (col_d int NOT NULL GENERATED ALWAYS AS IDENTITY);
279298

280299
-- Partitioned tables with a partitioned index
281300
CREATE TABLE evttrig.parted (
@@ -293,11 +312,16 @@ CREATE TABLE evttrig.part_15_20 PARTITION OF evttrig.part_10_20 (id)
293312
ALTER TABLE evttrig.two DROP COLUMN col_c;
294313
ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT;
295314
ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey;
315+
ALTER TABLE evttrig.one DROP COLUMN col_c;
316+
ALTER TABLE evttrig.id ALTER COLUMN col_d SET DATA TYPE bigint;
317+
ALTER TABLE evttrig.id ALTER COLUMN col_d DROP IDENTITY,
318+
ALTER COLUMN col_d SET DATA TYPE int;
296319
DROP INDEX evttrig.one_idx;
297320
DROP SCHEMA evttrig CASCADE;
298321
DROP TABLE a_temp_tbl;
299322

300323
DROP EVENT TRIGGER regress_event_trigger_report_dropped;
324+
DROP EVENT TRIGGER regress_event_trigger_report_end;
301325

302326
-- only allowed from within an event trigger function, should fail
303327
select pg_event_trigger_table_rewrite_oid();

0 commit comments

Comments
 (0)
0