Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit99867e7

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

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
@@ -366,6 +366,7 @@ SELECT * FROM dropped_objects WHERE type = 'schema';
366366
DROP ROLE regress_evt_user;
367367
DROP EVENT TRIGGER regress_event_trigger_drop_objects;
368368
DROP EVENT TRIGGER undroppable;
369+
-- Event triggers on relations.
369370
CREATE OR REPLACE FUNCTION event_trigger_report_dropped()
370371
RETURNS event_trigger
371372
LANGUAGE plpgsql
@@ -384,41 +385,92 @@ BEGIN
384385
END; $$;
385386
CREATE EVENT TRIGGER regress_event_trigger_report_dropped ON sql_drop
386387
EXECUTE PROCEDURE event_trigger_report_dropped();
388+
CREATE OR REPLACE FUNCTION event_trigger_report_end()
389+
RETURNS event_trigger
390+
LANGUAGE plpgsql
391+
AS $$
392+
DECLARE r RECORD;
393+
BEGIN
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;
399+
EXCEPTION WHEN SQLSTATE 'XX000' THEN
400+
RAISE NOTICE 'END: got internal exception';
401+
END; $$;
402+
CREATE EVENT TRIGGER regress_event_trigger_report_end ON ddl_command_end
403+
EXECUTE PROCEDURE event_trigger_report_end();
387404
CREATE SCHEMA evttrig
388-
CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two')
405+
CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two', col_c SERIAL)
389406
CREATE INDEX one_idx ON one (col_b)
390-
CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42);
407+
CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42)
408+
CREATE TABLE id (col_d int NOT NULL GENERATED ALWAYS AS IDENTITY);
409+
NOTICE: END: command_tag=CREATE SCHEMA type=schema identity=evttrig
410+
NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_a_seq
411+
NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_c_seq
412+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.one
413+
NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_pkey
414+
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_a_seq
415+
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_c_seq
416+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.two
417+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two
418+
NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.id_col_d_seq
419+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.id
420+
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq
421+
NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx
391422
-- Partitioned tables with a partitioned index
392423
CREATE TABLE evttrig.parted (
393424
id int PRIMARY KEY)
394425
PARTITION BY RANGE (id);
426+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.parted
427+
NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.parted_pkey
395428
CREATE TABLE evttrig.part_1_10 PARTITION OF evttrig.parted (id)
396429
FOR VALUES FROM (1) TO (10);
430+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_1_10
397431
CREATE TABLE evttrig.part_10_20 PARTITION OF evttrig.parted (id)
398432
FOR VALUES FROM (10) TO (20) PARTITION BY RANGE (id);
433+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_10_20
399434
CREATE TABLE evttrig.part_10_15 PARTITION OF evttrig.part_10_20 (id)
400435
FOR VALUES FROM (10) TO (15);
436+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_10_15
401437
CREATE TABLE evttrig.part_15_20 PARTITION OF evttrig.part_10_20 (id)
402438
FOR VALUES FROM (15) TO (20);
439+
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.part_15_20
403440
ALTER TABLE evttrig.two DROP COLUMN col_c;
404441
NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.two.col_c name={evttrig,two,col_c} args={}
405442
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={}
443+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two
406444
ALTER TABLE evttrig.one ALTER COLUMN col_b DROP DEFAULT;
407445
NOTICE: NORMAL: orig=t normal=f istemp=f type=default value identity=for evttrig.one.col_b name={evttrig,one,col_b} args={}
446+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one
408447
ALTER TABLE evttrig.one DROP CONSTRAINT one_pkey;
409448
NOTICE: NORMAL: orig=t normal=f istemp=f type=table constraint identity=one_pkey on evttrig.one name={evttrig,one,one_pkey} args={}
449+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one
450+
ALTER TABLE evttrig.one DROP COLUMN col_c;
451+
NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.one.col_c name={evttrig,one,col_c} args={}
452+
NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_c name={evttrig,one,col_c} args={}
453+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one
454+
ALTER TABLE evttrig.id ALTER COLUMN col_d SET DATA TYPE bigint;
455+
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq
456+
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.id
457+
ALTER TABLE evttrig.id ALTER COLUMN col_d DROP IDENTITY,
458+
ALTER COLUMN col_d SET DATA TYPE int;
459+
NOTICE: END: got internal exception
410460
DROP INDEX evttrig.one_idx;
411461
NOTICE: NORMAL: orig=t normal=f istemp=f type=index identity=evttrig.one_idx name={evttrig,one_idx} args={}
412462
DROP SCHEMA evttrig CASCADE;
413-
NOTICE: drop cascades to3 other objects
463+
NOTICE: drop cascades to4 other objects
414464
DETAIL: drop cascades to table evttrig.one
415465
drop cascades to table evttrig.two
466+
drop cascades to table evttrig.id
416467
drop cascades to table evttrig.parted
417468
NOTICE: NORMAL: orig=t normal=f istemp=f type=schema identity=evttrig name={evttrig} args={}
418469
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.one name={evttrig,one} args={}
419470
NOTICE: NORMAL: orig=f normal=t istemp=f type=sequence identity=evttrig.one_col_a_seq name={evttrig,one_col_a_seq} args={}
420471
NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_a name={evttrig,one,col_a} args={}
421472
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.two name={evttrig,two} args={}
473+
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.id name={evttrig,id} args={}
422474
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.parted name={evttrig,parted} args={}
423475
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_1_10 name={evttrig,part_1_10} args={}
424476
NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_20 name={evttrig,part_10_20} args={}
@@ -427,6 +479,7 @@ NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_15_20
427479
DROP TABLE a_temp_tbl;
428480
NOTICE: NORMAL: orig=t normal=f istemp=t type=table identity=pg_temp.a_temp_tbl name={pg_temp,a_temp_tbl} args={}
429481
DROP EVENT TRIGGER regress_event_trigger_report_dropped;
482+
DROP EVENT TRIGGER regress_event_trigger_report_end;
430483
-- only allowed from within an event trigger function, should fail
431484
select pg_event_trigger_table_rewrite_oid();
432485
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
@@ -273,6 +273,7 @@ DROP ROLE regress_evt_user;
273273
DROP EVENT TRIGGER regress_event_trigger_drop_objects;
274274
DROP EVENT TRIGGER undroppable;
275275

276+
-- Event triggers on relations.
276277
CREATE OR REPLACEFUNCTIONevent_trigger_report_dropped()
277278
RETURNS event_trigger
278279
LANGUAGE plpgsql
@@ -291,10 +292,28 @@ BEGIN
291292
END; $$;
292293
CREATE EVENT TRIGGER regress_event_trigger_report_droppedON sql_drop
293294
EXECUTE PROCEDURE event_trigger_report_dropped();
295+
CREATE OR REPLACEFUNCTIONevent_trigger_report_end()
296+
RETURNS event_trigger
297+
LANGUAGE plpgsql
298+
AS $$
299+
DECLARE r RECORD;
300+
BEGIN
301+
FOR rINSELECT*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;
306+
EXCEPTION WHEN SQLSTATE'XX000' THEN
307+
RAISE NOTICE'END: got internal exception';
308+
END; $$;
309+
CREATE EVENT TRIGGER regress_event_trigger_report_endON ddl_command_end
310+
EXECUTE PROCEDURE event_trigger_report_end();
311+
294312
CREATESCHEMAevttrig
295-
CREATETABLEone (col_aSERIALPRIMARY KEY, col_btext DEFAULT'forty two')
313+
CREATETABLEone (col_aSERIALPRIMARY KEY, col_btext DEFAULT'forty two', col_cSERIAL)
296314
CREATEINDEXone_idxON one (col_b)
297-
CREATETABLEtwo (col_cINTEGERCHECK (col_c>0)REFERENCES one DEFAULT42);
315+
CREATETABLEtwo (col_cINTEGERCHECK (col_c>0)REFERENCES one DEFAULT42)
316+
CREATETABLEid (col_dintNOT NULL GENERATED ALWAYSAS IDENTITY);
298317

299318
-- Partitioned tables with a partitioned index
300319
CREATETABLEevttrig.parted (
@@ -312,11 +331,16 @@ CREATE TABLE evttrig.part_15_20 PARTITION OF evttrig.part_10_20 (id)
312331
ALTERTABLEevttrig.two DROP COLUMN col_c;
313332
ALTERTABLEevttrig.one ALTER COLUMN col_b DROP DEFAULT;
314333
ALTERTABLEevttrig.one DROPCONSTRAINT one_pkey;
334+
ALTERTABLEevttrig.one DROP COLUMN col_c;
335+
ALTERTABLEevttrig.id ALTER COLUMN col_dSET DATA TYPEbigint;
336+
ALTERTABLEevttrig.id ALTER COLUMN col_d DROP IDENTITY,
337+
ALTER COLUMN col_dSET DATA TYPEint;
315338
DROPINDEXevttrig.one_idx;
316339
DROPSCHEMA evttrig CASCADE;
317340
DROPTABLE a_temp_tbl;
318341

319342
DROP EVENT TRIGGER regress_event_trigger_report_dropped;
343+
DROP EVENT TRIGGER regress_event_trigger_report_end;
320344

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp