v5-0004-Event-trigger-support-reindex-command.patch

From 864f53c932cd2b692b5042bbfddc021d5e49ef0c Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Sat, 25 Nov 2023 09:28:07 +0800
Subject: [PATCH v5 4/4] Event trigger support reindex command. this add
 documentation, regress test, static function pass reindex info to function
 EventTriggerCollectSimpleCommand.

---
 doc/src/sgml/event-trigger.sgml             |   8 ++
 src/backend/catalog/index.c                 |   5 +-
 src/backend/commands/indexcmds.c            |  17 ++++
 src/test/regress/expected/event_trigger.out | 103 ++++++++++++++++++++
 src/test/regress/sql/event_trigger.sql      |  93 ++++++++++++++++++
 5 files changed, 225 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 10b20f03..234b4ffd 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -1032,6 +1032,14 @@
         <entry align="center"><literal>-</literal></entry>
         <entry align="left"></entry>
        </row>
+       <row>
+        <entry align="left"><literal>REINDEX</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="left"></entry>
+       </row>
        <row>
         <entry align="left"><literal>REVOKE</literal></entry>
         <entry align="center"><literal>X</literal></entry>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index fc09191c..2afabdeb 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -136,7 +136,7 @@ static void SetReindexProcessing(Oid heapOid, Oid indexOid);
 static void ResetReindexProcessing(void);
 static void SetReindexPending(List *indexes);
 static void RemoveReindexPending(Oid indexOid);
-
+static void reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt);
 
 /*
  * relationHasPrimaryKey
@@ -3642,6 +3642,9 @@ reindex_index(const ReindexStmt *stmt, Oid indexId, bool skip_constraint_checks,
 	if (progress)
 		pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID,
 									 iRel->rd_rel->relam);
+	/* event trigger tracking REINDEX primary pointer */
+	if (stmt)
+		reindex_event_trigger_collect(indexId, stmt);
 
 	/*
 	 * Partitioned indexes should never get processed here, as they have no
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index f63647e3..cb31767d 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -3817,6 +3817,10 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
 
 		newIndexIds = lappend(newIndexIds, newidx);
 
+		/* Add the index to event trigger */
+		if (stmt)
+			reindex_event_trigger_collect(newIndexId, stmt);
+
 		/*
 		 * Save lockrelid to protect each relation from drop then close
 		 * relations. The lockrelid on parent relation is not taken here to
@@ -4420,3 +4424,16 @@ set_indexsafe_procflags(void)
 	ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
 	LWLockRelease(ProcArrayLock);
 }
+
+static void
+reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt)
+{
+	ObjectAddress address;
+
+	address.classId = RelationRelationId;
+	address.objectId = oid;
+	address.objectSubId = 0;
+
+	EventTriggerCollectSimpleCommand(address,
+									 InvalidObjectAddress, (Node *) stmt);
+}
\ No newline at end of file
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 0b87a42d..5dfb2ee1 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -556,6 +556,109 @@ ERROR:  cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
 drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
+--reindex command, event trigger test setup.
+DROP SCHEMA IF EXISTS schema_to_reindex CASCADE;
+NOTICE:  schema "schema_to_reindex" does not exist, skipping
+CREATE SCHEMA schema_to_reindex;
+SET search_path TO schema_to_reindex;
+CREATE TABLE concur_reindex_tab (c1 int,c2 text);
+CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab (c1);
+CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab (c2);
+CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab (c1, c1, c2);
+ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
+INSERT INTO concur_reindex_tab VALUES (1, 'a'),(2, 'a');
+CREATE TABLE parted_irreg_ancestor (b text,a int) PARTITION BY RANGE (b);
+CREATE TABLE parted_irreg (a int,b text) PARTITION BY RANGE (b);
+ALTER TABLE parted_irreg_ancestor ATTACH PARTITION parted_irreg
+FOR VALUES FROM ('aaaa') TO ('zzzz');
+CREATE TABLE parted1_irreg1 (b text NOT NULL,a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg1
+FOR VALUES FROM ('aaaa') TO ('jjjj');
+CREATE TABLE parted1_irreg2 (b text NOT NULL, a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg2
+FOR VALUES FROM ('jjjj') TO ('zzzz');
+INSERT INTO parted_irreg_ancestor (b)
+VALUES ('daasvog'),('asdhjksd'),('sssdjk'),('jssdjk');
+ALTER TABLE parted_irreg_ancestor ADD PRIMARY KEY (b);
+CREATE OR REPLACE FUNCTION public.reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+    RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_start_command();
+CREATE OR REPLACE FUNCTION public.reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+	obj record;
+	toast_main_table text;
+BEGIN
+FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+        IF  obj.schema_name = 'pg_toast' THEN
+            RAISE NOTICE 'reindexing toast related index! object_type: %, schema_name: %'
+                          ,obj.object_type, obj.schema_name;
+            /* get the toast table will be reindexed main table.
+             * toast table name auto generated, cannot use to test.
+            */
+            SELECT t1.relname into toast_main_table
+            FROM (
+                SELECT t1.oid
+                FROM pg_class t1 INNER JOIN pg_index t2 ON t1.oid = t2.indrelid
+                WHERE t2.indexrelid = obj.objid AND relkind = 't') sub
+                INNER JOIN pg_class t1 ON t1.reltoastrelid = sub.oid;
+
+            RAISE NOTICE 'toast table related main relation: %', toast_main_table;
+        ELSE
+            RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+        END IF;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_end_command();
+REINDEX (CONCURRENTLY) INDEX  parted_irreg_ancestor_pkey;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+ALTER EVENT TRIGGER end_reindex_command DISABLE;
+-- event trigger disabled.
+--so reindex_start_command part will invoke, reindex_end_command won't.
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+ALTER EVENT TRIGGER end_reindex_command ENABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg1
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg2
+/* REINDEX SCHEMA (CONCURRENTLY) generated test result order unstable,
+ * So just do normal reindex schema. should cover reindex statement most cases.
+*/
+REINDEX SCHEMA  schema_to_reindex;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX concur_reindex_ind1 ON schema_to_reindex.concur_reindex_tab USING btree (c1)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX concur_reindex_ind2 ON schema_to_reindex.concur_reindex_tab USING btree (c2)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX concur_reindex_ind4 ON schema_to_reindex.concur_reindex_tab USING btree (c1, c1, c2)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: concur_reindex_tab
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg1
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg2
+drop event trigger if exists  start_reindex_command,end_reindex_command;
+drop function if exists  public.reindex_end_command, public.reindex_start_command cascade;
+drop schema if exists schema_to_reindex cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to table concur_reindex_tab
+drop cascades to table parted_irreg_ancestor
+RESET search_path;
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 6f0933b9..ace58308 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -418,6 +418,99 @@ drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
 
+--reindex command, event trigger test setup.
+DROP SCHEMA IF EXISTS schema_to_reindex CASCADE;
+CREATE SCHEMA schema_to_reindex;
+SET search_path TO schema_to_reindex;
+
+CREATE TABLE concur_reindex_tab (c1 int,c2 text);
+CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab (c1);
+CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab (c2);
+CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab (c1, c1, c2);
+ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
+INSERT INTO concur_reindex_tab VALUES (1, 'a'),(2, 'a');
+
+CREATE TABLE parted_irreg_ancestor (b text,a int) PARTITION BY RANGE (b);
+CREATE TABLE parted_irreg (a int,b text) PARTITION BY RANGE (b);
+
+ALTER TABLE parted_irreg_ancestor ATTACH PARTITION parted_irreg
+FOR VALUES FROM ('aaaa') TO ('zzzz');
+
+CREATE TABLE parted1_irreg1 (b text NOT NULL,a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg1
+FOR VALUES FROM ('aaaa') TO ('jjjj');
+
+CREATE TABLE parted1_irreg2 (b text NOT NULL, a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg2
+FOR VALUES FROM ('jjjj') TO ('zzzz');
+
+INSERT INTO parted_irreg_ancestor (b)
+VALUES ('daasvog'),('asdhjksd'),('sssdjk'),('jssdjk');
+
+ALTER TABLE parted_irreg_ancestor ADD PRIMARY KEY (b);
+
+CREATE OR REPLACE FUNCTION public.reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+    RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_start_command();
+
+CREATE OR REPLACE FUNCTION public.reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+	obj record;
+	toast_main_table text;
+BEGIN
+FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+        IF  obj.schema_name = 'pg_toast' THEN
+            RAISE NOTICE 'reindexing toast related index! object_type: %, schema_name: %'
+                          ,obj.object_type, obj.schema_name;
+            /* get the toast table will be reindexed main table.
+             * toast table name auto generated, cannot use to test.
+            */
+            SELECT t1.relname into toast_main_table
+            FROM (
+                SELECT t1.oid
+                FROM pg_class t1 INNER JOIN pg_index t2 ON t1.oid = t2.indrelid
+                WHERE t2.indexrelid = obj.objid AND relkind = 't') sub
+                INNER JOIN pg_class t1 ON t1.reltoastrelid = sub.oid;
+
+            RAISE NOTICE 'toast table related main relation: %', toast_main_table;
+        ELSE
+            RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+        END IF;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_end_command();
+
+REINDEX (CONCURRENTLY) INDEX  parted_irreg_ancestor_pkey;
+ALTER EVENT TRIGGER end_reindex_command DISABLE;
+
+-- event trigger disabled.
+--so reindex_start_command part will invoke, reindex_end_command won't.
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+
+ALTER EVENT TRIGGER end_reindex_command ENABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+
+/* REINDEX SCHEMA (CONCURRENTLY) generated test result order unstable,
+ * So just do normal reindex schema. should cover reindex statement most cases.
+*/
+REINDEX SCHEMA  schema_to_reindex;
+
+drop event trigger if exists  start_reindex_command,end_reindex_command;
+drop function if exists  public.reindex_end_command, public.reindex_start_command cascade;
+drop schema if exists schema_to_reindex cascade;
+RESET search_path;
+
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
-- 
2.34.1