Hi all, As mentioned on the thread dealing with concurrent indexing for temporary relations, it sounds like a good thing to make the code more defensive if attempting to do a REINDEX CONCURRENTLY with a backend still holding references to the indexes worked on: https://www.postgresql.org/message-id/20191212213709.neopqccvdo724...@alap3.anarazel.de
One thing is that as REINDEX CONCURRENTLY cannot be used in transaction blocks, this cannot be triggered in the context of a function call, say with this patch: +ERROR: REINDEX CONCURRENTLY cannot be executed from a function +CONTEXT: SQL statement "REINDEX INDEX CONCURRENTLY reindex_ind_ref" +PL/pgSQL function reindex_func_ref() line 3 at EXECUTE Attached is a patch to do that. I am not sure if the test added in the patch has much additional value, but feel free to look at it. Any thoughts? -- Michael
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index ec20ba38d1..8127aa6319 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2978,6 +2978,12 @@ ReindexRelationConcurrently(Oid relationOid, int options) if (indexRel->rd_rel->relpersistence == RELPERSISTENCE_TEMP) elog(ERROR, "cannot reindex a temporary table concurrently"); + /* + * Also check for active uses of the relation in the current + * transaction, including open scans and pending AFTER trigger events. + */ + CheckTableNotInUse(indexRel, "REINDEX"); + pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX, RelationGetRelid(heapRel)); pgstat_progress_update_param(PROGRESS_CREATEIDX_COMMAND, diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 6ddf3a63c3..de51cae5f4 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2492,6 +2492,25 @@ SELECT b.relname, (2 rows) DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before; +-- Check that REINDEX CONCURRENTLY does not work if a relation is being +-- used. Note that this does not work as REINDEX CONCURRENTLY is not +-- allowed in a transaction block. +CREATE TABLE reindex_tab_ref (a int); +CREATE INDEX reindex_ind_ref ON reindex_tab_ref(a); +CREATE OR REPLACE FUNCTION reindex_func_ref() RETURNS trigger + LANGUAGE plpgsql AS $$ + BEGIN + EXECUTE 'REINDEX INDEX CONCURRENTLY reindex_ind_ref'; + RETURN NULL; + END $$; +CREATE TRIGGER reindex_trig_ref BEFORE INSERT ON reindex_tab_ref + FOR EACH STATEMENT EXECUTE PROCEDURE reindex_func_ref(); +INSERT INTO reindex_tab_ref VALUES (1); +ERROR: REINDEX CONCURRENTLY cannot be executed from a function +CONTEXT: SQL statement "REINDEX INDEX CONCURRENTLY reindex_ind_ref" +PL/pgSQL function reindex_func_ref() line 3 at EXECUTE +DROP TABLE reindex_tab_ref; +DROP FUNCTION reindex_func_ref; -- -- REINDEX SCHEMA -- diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index f7fd756189..cfbab0b287 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -1039,6 +1039,23 @@ SELECT b.relname, ORDER BY 1; DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before; +-- Check that REINDEX CONCURRENTLY does not work if a relation is being +-- used. Note that this does not work as REINDEX CONCURRENTLY is not +-- allowed in a transaction block. +CREATE TABLE reindex_tab_ref (a int); +CREATE INDEX reindex_ind_ref ON reindex_tab_ref(a); +CREATE OR REPLACE FUNCTION reindex_func_ref() RETURNS trigger + LANGUAGE plpgsql AS $$ + BEGIN + EXECUTE 'REINDEX INDEX CONCURRENTLY reindex_ind_ref'; + RETURN NULL; + END $$; +CREATE TRIGGER reindex_trig_ref BEFORE INSERT ON reindex_tab_ref + FOR EACH STATEMENT EXECUTE PROCEDURE reindex_func_ref(); +INSERT INTO reindex_tab_ref VALUES (1); +DROP TABLE reindex_tab_ref; +DROP FUNCTION reindex_func_ref; + -- -- REINDEX SCHEMA --
signature.asc
Description: PGP signature
