Re: PATCH: Add REINDEX tag to event triggers

Thank you for this, jian he! I will include it in the next patch version.

P.S. Sorry for the double post. Sent from the wrong email address so I'm
resending so the mailing list gets the email. My apologies!

On Wed, Jul 26, 2023 at 4:30 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:

> 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;
> -----------------------
>