Re: PATCH: Add REINDEX tag to event triggers

On Wed, Jul 26, 2023 at 7:51 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Tue, Jul 25, 2023 at 04:34:47PM +0800, jian he wrote:
> > so T_ReindexStmt should only be in ProcessUtilitySlow, if you want
> > to create an event trigger on reindex?
> >
> > regression tests work fine. I even play with partitions.
>
> It would be an idea to have some regression tests for partitions,
> actually, so as some patterns around ReindexMultipleInternal() are
> checked. We could have a REINDEX DATABASE in a TAP test with an event
> trigger, as well, but I don't feel strongly about the need to do that
> much extra work in 090_reindexdb.pl or 091_reindexdb_all.pl if
> partitions cover the multi-table case.
> --
> Michael

quite verbose, copied from partition-info.sql. meet the expectation:
partitioned index will do nothing, partition index will trigger event
trigger.
------------------------------------------------
DROP EVENT TRIGGER IF EXISTS end_reindex_command CASCADE;
DROP EVENT TRIGGER IF EXISTS start_reindex_command CASCADE;

BEGIN;
CREATE OR REPLACE FUNCTION reindex_end_command()
RETURNS event_trigger AS $$
DECLARE
obj record;
BEGIN
raise notice 'begin of reindex_end_command';

FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
RAISE NOTICE
'obj.command_tag:% obj.object_type:% obj.schema_name:%
obj.object_identity:%'
,obj.command_tag, obj.object_type,obj.schema_name,obj.object_identity;
RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION start_reindex_command()
RETURNS event_trigger AS $$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
RAISE NOTICE
'obj.command_tag:% obj.object_type:% obj.schema_name:%
obj.object_identity:%'
, obj.command_tag, obj.object_type,obj.schema_name,obj.object_identity;
RAISE NOTICE 'ddl_start_command -- REINDEX: %',
pg_get_indexdef(obj.objid);
END LOOP;
raise notice 'end of start_reindex_command';
END;
$$ LANGUAGE plpgsql;

BEGIN;
CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_end_command();
CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE start_reindex_command();
COMMIT;

-- test Reindex Event Trigger
BEGIN;
drop table if EXISTS ptif_test CASCADE;
CREATE TABLE ptif_test (a int, b int) PARTITION BY range (a);
CREATE TABLE ptif_test0 PARTITION OF ptif_test
FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
CREATE TABLE ptif_test01 PARTITION OF ptif_test0 FOR VALUES IN (1);
CREATE TABLE ptif_test1 PARTITION OF ptif_test
FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
CREATE TABLE ptif_test11 PARTITION OF ptif_test1 FOR VALUES IN (1);

CREATE TABLE ptif_test2 PARTITION OF ptif_test
FOR VALUES FROM (100) TO (200);
-- This partitioned table should remain with no partitions.
CREATE TABLE ptif_test3 PARTITION OF ptif_test
FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);

-- Test index partition tree
CREATE INDEX ptif_test_index ON ONLY ptif_test (a);

CREATE INDEX ptif_test0_index ON ONLY ptif_test0 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test0_index;

CREATE INDEX ptif_test01_index ON ptif_test01 (a);
ALTER INDEX ptif_test0_index ATTACH PARTITION ptif_test01_index;

CREATE INDEX ptif_test1_index ON ONLY ptif_test1 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test1_index;

CREATE INDEX ptif_test11_index ON ptif_test11 (a);
ALTER INDEX ptif_test1_index ATTACH PARTITION ptif_test11_index;

CREATE INDEX ptif_test2_index ON ptif_test2 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test2_index;

CREATE INDEX ptif_test3_index ON ptif_test3 (a);
ALTER INDEX ptif_test_index ATTACH PARTITION ptif_test3_index;
COMMIT;

--top level partitioned index. will recurse to each partition index.
REINDEX INDEX CONCURRENTLY public.ptif_test_index;

--ptif_test0 is partitioned table. it will index partition: ptif_test01_index
-- event trigger will log ptif_test01_index
REINDEX INDEX CONCURRENTLY public.ptif_test0_index;

--ptif_test1_index is partitioned index. it will index partition:
ptif_test11_index
-- event trigger will effect on partion index:ptif_test11_index
REINDEX INDEX CONCURRENTLY public.ptif_test1_index;

--ptif_test2 is a partition. event trigger will log ptif_test2_index
REINDEX INDEX CONCURRENTLY public.ptif_test2_index;

--no partitions. event trigger won't do anything.
REINDEX INDEX CONCURRENTLY public.ptif_test3_index;

reindex table ptif_test; --top level. will recurse to each partition index.
reindex table ptif_test0; -- will direct to ptif_test01
reindex table ptif_test01; -- will index it's associtaed index
reindex table ptif_test11; -- will index it's associtaed index
reindex table ptif_test2; -- will index it's associtaed index
reindex table ptif_test3; -- no partion, index won't do anything.

DROP EVENT TRIGGER IF EXISTS end_reindex_command CASCADE;
DROP EVENT TRIGGER IF EXISTS start_reindex_command CASCADE;
DROP FUNCTION IF EXISTS reindex_start_command;
DROP FUNCTION IF EXISTS reindex_end_command;
DROP TABLE if EXISTS ptif_test CASCADE;
-----------------------