pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Paquier <michael@paquier.xyz>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Manuel Rigger <rigger.manuel@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: REINDEX CONCURRENTLY unexpectedly fails
Date: Wed, 15 Jan 2020 10:39:03 +0900
Message-ID: <20200115013824.GA2243@paquier.xyz> (raw)
In-Reply-To: <d62659d5-8860-075f-1e86-f17834f26495@iki.fi>
References: <20191115080706.GI1849@paquier.xyz>
	<20191120013649.2xfr2dnznyjgluch@alap3.anarazel.de>
	<20191120035408.GA4243@paquier.xyz>
	<20191212213709.neopqccvdo724eha@alap3.anarazel.de>
	<20191213034536.GD1942@paquier.xyz>
	<20191223015956.GB34339@paquier.xyz>
	<8f0bde7b-2d96-fb5c-cd0e-553694c1d376@iki.fi>
	<20200108081930.GF3413@paquier.xyz>
	<20200109030619.GC2251@paquier.xyz>
	<d62659d5-8860-075f-1e86-f17834f26495@iki.fi>

On Tue, Jan 14, 2020 at 11:41:11PM +0200, Heikki Linnakangas wrote:
> I'm not a fan of all those changes in RangeVarCallbackForDropRelation() to
> ensure that you get an AccessExclusiveLock to begin with. It gets pretty
> complicated, and it feels like you need to special-case temporary tables in
> dozen different places. I liked the v3 of this patch better. It's true that
> you're upgrading the ShareUpdateExclusiveLock to AccessExclusiveLock, but
> since it's a temporary table, there really should be no other backend
> holding a lock on it.

Thanks for taking the time to share your opinion.  That was as well my
feeling with the peanut and the sledgehammer.  I liked the peanuts,
but not the hammer part.

There are still some parts I liked about v4 (doc wording, tweaks about
the shape of RelationSupportsConcurrentIndexing and its use in
assertions, setting up the concurrent flag in RemoveRelation and use an
assert in index_drop is also cleaner), so I kept a good portion of
v4.  Attached is an updated patch, v5, that removes the parts
enforcing the lock when looking at the relation OID based on its
RangeVar.

Any thoughts?
--
Michael

Attachments:

reindex-conc-temp-v5.patch (text/x-diff, 15.8K) download diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index a2890c1314..51723a8d8d 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -115,6 +115,8 @@ extern bool CompareIndexInfo(IndexInfo *info1, IndexInfo *info2, extern void BuildSpeculativeIndexInfo(Relation index, IndexInfo *ii); +extern bool RelationSupportsConcurrentIndexing(Oid relid); + extern void FormIndexDatum(IndexInfo *indexInfo, TupleTableSlot *slot, EState *estate, diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 3e59e647e5..4139232b51 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -2016,6 +2016,13 @@ index_drop(Oid indexId, bool concurrent, bool concurrent_lock_mode) LOCKTAG heaplocktag; LOCKMODE lockmode; + /* + * A relation not supporting concurrent indexing should never do + * a concurrent index drop or try to use a concurrent lock mode. + */ + Assert(RelationSupportsConcurrentIndexing(indexId) || + (!concurrent && !concurrent_lock_mode)); + /* * To drop an index safely, we must grab exclusive lock on its parent * table. Exclusive lock on the index alone is insufficient because @@ -2108,6 +2115,9 @@ index_drop(Oid indexId, bool concurrent, bool concurrent_lock_mode) */ CacheInvalidateRelcache(userHeapRelation); + /* Relation had better support concurrent indexing */ + Assert(RelationSupportsConcurrentIndexing(indexId)); + /* save lockrelid and locktag for below, then close but keep locks */ heaprelid = userHeapRelation->rd_lockInfo.lockRelId; SET_LOCKTAG_RELATION(heaplocktag, heaprelid.dbId, heaprelid.relId); @@ -2490,6 +2500,30 @@ CompareIndexInfo(IndexInfo *info1, IndexInfo *info2, return true; } +/* + * RelationSupportsConcurrentIndexing + * + * Check if a relation supports concurrent builds or not. This is used + * before processing CREATE INDEX, DROP INDEX or REINDEX when using + * CONCURRENTLY to decide if the operation is supported. + */ +bool +RelationSupportsConcurrentIndexing(Oid relid) +{ + /* + * Build indexes non-concurrently for temporary relations. Such + * relations only work with the session assigned to them, so they are + * not subject to concurrent concerns, and a concurrent build would + * cause issues with ON COMMIT actions triggered by the transactions + * of the concurrent build. A non-concurrent reindex is also more + * efficient in this case. + */ + if (get_rel_persistence(relid) == RELPERSISTENCE_TEMP) + return false; + + return true; +} + /* ---------------- * BuildSpeculativeIndexInfo * Add extra state to IndexInfo record diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 52ce02f898..d63a885638 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -485,6 +485,13 @@ DefineIndex(Oid relationId, GUC_ACTION_SAVE, true, 0, false); } + /* + * Enforce non-concurrent build if the relation does not support this + * option. Do this before any use of the concurrent option is done. + */ + if (!RelationSupportsConcurrentIndexing(relationId)) + stmt->concurrent = false; + /* * Start progress report. If we're building a partition, this was already * done. @@ -2347,7 +2354,7 @@ ReindexIndex(RangeVar *indexRelation, int options, bool concurrent) persistence = irel->rd_rel->relpersistence; index_close(irel, NoLock); - if (concurrent) + if (concurrent && RelationSupportsConcurrentIndexing(indOid)) ReindexRelationConcurrently(indOid, options); else reindex_index(indOid, false, persistence, @@ -2440,7 +2447,8 @@ ReindexTable(RangeVar *relation, int options, bool concurrent) 0, RangeVarCallbackOwnsTable, NULL); - if (concurrent) + if (concurrent && + RelationSupportsConcurrentIndexing(heapOid)) { result = ReindexRelationConcurrently(heapOid, options); @@ -2646,7 +2654,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); - if (concurrent) + if (concurrent && + RelationSupportsConcurrentIndexing(relid)) { (void) ReindexRelationConcurrently(relid, options); /* ReindexRelationConcurrently() does the verbose output */ @@ -2769,6 +2778,9 @@ ReindexRelationConcurrently(Oid relationOid, int options) /* Open relation to get its indexes */ heapRelation = table_open(relationOid, ShareUpdateExclusiveLock); + /* Relation had better support concurrent indexing */ + Assert(RelationSupportsConcurrentIndexing(relationOid)); + /* Add all the valid indexes of relation to list */ foreach(lc, RelationGetIndexList(heapRelation)) { @@ -2862,6 +2874,9 @@ ReindexRelationConcurrently(Oid relationOid, int options) /* Save the list of relation OIDs in private context */ oldcontext = MemoryContextSwitchTo(private_context); + /* Relation had better support concurrent indexing */ + Assert(RelationSupportsConcurrentIndexing(heapId)); + /* Track the heap relation of this index for session locks */ heapRelationIds = list_make1_oid(heapId); @@ -2937,6 +2952,13 @@ ReindexRelationConcurrently(Oid relationOid, int options) heapRel = table_open(indexRel->rd_index->indrelid, ShareUpdateExclusiveLock); + /* + * 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/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 2ec3fc5014..7bcd08a479 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -1239,7 +1239,11 @@ RemoveRelations(DropStmt *drop) /* DROP CONCURRENTLY uses a weaker lock, and has some restrictions */ if (drop->concurrent) { - flags |= PERFORM_DELETION_CONCURRENTLY; + /* + * Note that for temporary relations this lock may get upgraded + * later on, but as a session is the only able to work on its + * temporary relations, this is actually fine. + */ lockmode = ShareUpdateExclusiveLock; Assert(drop->removeType == OBJECT_INDEX); if (list_length(drop->objects) != 1) @@ -1330,6 +1334,19 @@ RemoveRelations(DropStmt *drop) continue; } + /* + * Decide if concurrent mode needs to be used here or not. There + * is no way to know if the relation supports concurrent indexing or + * not without knowing its OID, so this is not done beforehand. + */ + if (drop->concurrent && + RelationSupportsConcurrentIndexing(relOid)) + { + Assert(list_length(drop->objects) == 1 && + drop->removeType == OBJECT_INDEX); + flags |= PERFORM_DELETION_CONCURRENTLY; + } + /* OK, we're ready to delete this one */ obj.classId = RelationRelationId; obj.objectId = relOid; diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 6446907a65..cf1a0ca2f2 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -1435,6 +1435,31 @@ Indexes: "concur_index5" btree (f2) WHERE f1 = 'x'::text "std_index" btree (f2) +-- Temporary tables with concurrent builds and on-commit actions +-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored. +-- PRESERVE ROWS, the default. +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP INDEX CONCURRENTLY concur_temp_ind; +DROP TABLE concur_temp; +-- ON COMMIT DROP +BEGIN; +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT DROP; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +-- Fails when running in a transaction. +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block +COMMIT; +-- ON COMMIT DELETE ROWS +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT DELETE ROWS; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP INDEX CONCURRENTLY concur_temp_ind; +DROP TABLE concur_temp; -- -- Try some concurrent index drops -- @@ -2418,6 +2443,55 @@ SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass); (1 row) DROP TABLE concur_exprs_tab; +-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored. +-- ON COMMIT PRESERVE ROWS, the default. +CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp_tab_1 VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX concur_temp_ind_1 ON concur_temp_tab_1(c2); +REINDEX TABLE CONCURRENTLY concur_temp_tab_1; +REINDEX INDEX CONCURRENTLY concur_temp_ind_1; +-- Still fails in transaction blocks +BEGIN; +REINDEX INDEX CONCURRENTLY concur_temp_ind_1; +ERROR: REINDEX CONCURRENTLY cannot run inside a transaction block +COMMIT; +-- ON COMMIT DELETE ROWS +CREATE TEMP TABLE concur_temp_tab_2 (c1 int, c2 text) + ON COMMIT DELETE ROWS; +CREATE INDEX concur_temp_ind_2 ON concur_temp_tab_2(c2); +REINDEX TABLE CONCURRENTLY concur_temp_tab_2; +REINDEX INDEX CONCURRENTLY concur_temp_ind_2; +-- ON COMMIT DROP +BEGIN; +CREATE TEMP TABLE concur_temp_tab_3 (c1 int, c2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp_tab_3 VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX concur_temp_ind_3 ON concur_temp_tab_3(c2); +-- Fails when running in a transaction +REINDEX INDEX CONCURRENTLY concur_temp_ind_3; +ERROR: REINDEX CONCURRENTLY cannot run inside a transaction block +COMMIT; +-- REINDEX SCHEMA processes all temporary relations +CREATE TABLE reindex_temp_before AS +SELECT oid, relname, relfilenode, relkind, reltoastrelid + FROM pg_class + WHERE relname IN ('concur_temp_ind_1', 'concur_temp_ind_2'); +SELECT pg_my_temp_schema()::regnamespace as temp_schema_name \gset +REINDEX SCHEMA :temp_schema_name; +SELECT b.relname, + b.relkind, + CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged' + ELSE 'relfilenode has changed' END + FROM reindex_temp_before b JOIN pg_class a ON b.oid = a.oid + ORDER BY 1; + relname | relkind | case +-------------------+---------+------------------------- + concur_temp_ind_1 | i | relfilenode has changed + concur_temp_ind_2 | i | relfilenode has changed +(2 rows) + +DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before; -- -- REINDEX SCHEMA -- diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index 3c0c1cdc5e..5bc9da440e 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -501,6 +501,31 @@ VACUUM FULL concur_heap; REINDEX TABLE concur_heap; \d concur_heap +-- Temporary tables with concurrent builds and on-commit actions +-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored. +-- PRESERVE ROWS, the default. +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP INDEX CONCURRENTLY concur_temp_ind; +DROP TABLE concur_temp; +-- ON COMMIT DROP +BEGIN; +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT DROP; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +-- Fails when running in a transaction. +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +COMMIT; +-- ON COMMIT DELETE ROWS +CREATE TEMP TABLE concur_temp (f1 int, f2 text) + ON COMMIT DELETE ROWS; +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP INDEX CONCURRENTLY concur_temp_ind; +DROP TABLE concur_temp; + -- -- Try some concurrent index drops -- @@ -972,6 +997,48 @@ SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass); SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass); DROP TABLE concur_exprs_tab; +-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored. +-- ON COMMIT PRESERVE ROWS, the default. +CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp_tab_1 VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX concur_temp_ind_1 ON concur_temp_tab_1(c2); +REINDEX TABLE CONCURRENTLY concur_temp_tab_1; +REINDEX INDEX CONCURRENTLY concur_temp_ind_1; +-- Still fails in transaction blocks +BEGIN; +REINDEX INDEX CONCURRENTLY concur_temp_ind_1; +COMMIT; +-- ON COMMIT DELETE ROWS +CREATE TEMP TABLE concur_temp_tab_2 (c1 int, c2 text) + ON COMMIT DELETE ROWS; +CREATE INDEX concur_temp_ind_2 ON concur_temp_tab_2(c2); +REINDEX TABLE CONCURRENTLY concur_temp_tab_2; +REINDEX INDEX CONCURRENTLY concur_temp_ind_2; +-- ON COMMIT DROP +BEGIN; +CREATE TEMP TABLE concur_temp_tab_3 (c1 int, c2 text) + ON COMMIT PRESERVE ROWS; +INSERT INTO concur_temp_tab_3 VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX concur_temp_ind_3 ON concur_temp_tab_3(c2); +-- Fails when running in a transaction +REINDEX INDEX CONCURRENTLY concur_temp_ind_3; +COMMIT; +-- REINDEX SCHEMA processes all temporary relations +CREATE TABLE reindex_temp_before AS +SELECT oid, relname, relfilenode, relkind, reltoastrelid + FROM pg_class + WHERE relname IN ('concur_temp_ind_1', 'concur_temp_ind_2'); +SELECT pg_my_temp_schema()::regnamespace as temp_schema_name \gset +REINDEX SCHEMA :temp_schema_name; +SELECT b.relname, + b.relkind, + CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged' + ELSE 'relfilenode has changed' END + FROM reindex_temp_before b JOIN pg_class a ON b.oid = a.oid + ORDER BY 1; +DROP TABLE concur_temp_tab_1, concur_temp_tab_2, reindex_temp_before; + -- -- REINDEX SCHEMA -- diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 629a31ef79..ab362a0dc5 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -129,6 +129,11 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class= &mdash; see <xref linkend="sql-createindex-concurrently" endterm="sql-createindex-concurrently-title"/>. </para> + <para> + For temporary tables, <command>CREATE INDEX</command> is always + non-concurrent, as no other session can access them, and + non-concurrent index creation is cheaper. + </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 2a8ca5bf68..0aedd71bd6 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -58,6 +58,11 @@ DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] <replaceable class="parameter">name</r performed within a transaction block, but <command>DROP INDEX CONCURRENTLY</command> cannot. </para> + <para> + For temporary tables, <command>DROP INDEX</command> is always + non-concurrent, as no other session can access them, and + non-concurrent index drop is cheaper. + </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 5aa59d3b75..0cc19b86ee 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -166,6 +166,11 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN &mdash; see <xref linkend="sql-reindex-concurrently" endterm="sql-reindex-concurrently-title"/>. </para> + <para> + For temporary tables, <command>REINDEX</command> is always + non-concurrent, as no other session can access them, and + non-concurrent index creation is cheaper. + </para> </listitem> </varlistentry>
signature.asc (application/pgp-signature, 832B) download

view thread (32+ messages)  latest in thread

Message-ID: <20200115013824.GA2243@paquier.xyz>
Permalink:  ../20200115013824.GA2243@paquier.xyz/
Also on:    postgresql.org/message-id/20200115013824.GA2243@paquier.xyz

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: michael@paquier.xyz, hlinnaka@iki.fi, andres@anarazel.de, tgl@sss.pgh.pa.us, rigger.manuel@gmail.com
  Subject: Re: REINDEX CONCURRENTLY unexpectedly fails
  In-Reply-To: <20200115013824.GA2243@paquier.xyz>
  References: <20191115080706.GI1849@paquier.xyz> <20191120013649.2xfr2dnznyjgluch@alap3.anarazel.de> <20191120035408.GA4243@paquier.xyz> <20191212213709.neopqccvdo724eha@alap3.anarazel.de> <20191213034536.GD1942@paquier.xyz> <20191223015956.GB34339@paquier.xyz> <8f0bde7b-2d96-fb5c-cd0e-553694c1d376@iki.fi> <20200108081930.GF3413@paquier.xyz> <20200109030619.GC2251@paquier.xyz> <d62659d5-8860-075f-1e86-f17834f26495@iki.fi> <20200115013824.GA2243@paquier.xyz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox