From 864f53c932cd2b692b5042bbfddc021d5e49ef0c Mon Sep 17 00:00:00 2001 From: pgaddict 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 @@ - + + REINDEX + X + X + - + - + + REVOKE X 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