pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Paquier <michael@paquier.xyz>
To: 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: Fri, 15 Nov 2019 17:07:06 +0900
Message-ID: <20191115080706.GI1849@paquier.xyz> (raw)
In-Reply-To: <20191115032141.GH1849@paquier.xyz>
References: <CA+u7OA6gP7YAeCguyseusYcc=uR8+ypjCcgDDCTzjQ+k6S9ksQ@mail.gmail.com>
	<20191113154837.mgz2rnl6d7k6tas7@alap3.anarazel.de>
	<12383.1573660748@sss.pgh.pa.us>
	<20191113163851.e2enkdpvd3nrobst@alap3.anarazel.de>
	<19198.1573663534@sss.pgh.pa.us>
	<20191114080855.GG1910@paquier.xyz>
	<3808.1573754033@sss.pgh.pa.us>
	<20191115024512.GE1849@paquier.xyz>
	<20191115025412.p3beiflrd6bx6wgf@alap3.anarazel.de>
	<20191115032141.GH1849@paquier.xyz>

On Fri, Nov 15, 2019 at 12:21:41PM +0900, Michael Paquier wrote:
> On Thu, Nov 14, 2019 at 06:54:12PM -0800, Andres Freund wrote:
>> I think it'd be really user hostile if ReindexMultipleTables() suddenly
>> started to error out if there were any temp tables. That clearly can't
>> be an option.
> 
> Okay.

So, here is a patch to address all that.  I have also added a test for
REINDEX SCHEMA on a temporary schema.  While looking at the problem, I
have found a crash if trying to reindex concurrently an index or a
table using a temporary relation from a different session because we
have been lacking checks with RELATION_IS_OTHER_TEMP() in the
concurrent code paths.  For a schema or database reindex this was not
happening as these are filtered out using isTempNamespace().  Please
note that I have not changed index_drop() for the concurrent mode.
Per my checks this does not actually cause any direct bugs as this
code path just takes care of dropping the dependencies with the index.
There could be an argument for changing that on HEAD though, but I am
not sure that this is worth the complication either.
--
Michael

Attachments:

reindex-conc-temp-v1.patch (text/x-diff, 9.5K) download diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 374e2d0efe..7de36ca7e2 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -550,6 +550,15 @@ DefineIndex(Oid relationId, lockmode = stmt->concurrent ? ShareUpdateExclusiveLock : ShareLock; rel = table_open(relationId, lockmode); + /* + * Ignore concurrent index creation for temporary tables. Such + * relations only work with the current session, so they are not + * subject to concurrency problems. Using a non-concurrent build + * is also more performant. + */ + if (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP) + stmt->concurrent = false; + namespaceId = RelationGetNamespace(rel); /* Ensure that it makes sense to index this kind of relation */ @@ -2349,7 +2358,12 @@ ReindexIndex(RangeVar *indexRelation, int options, bool concurrent) persistence = irel->rd_rel->relpersistence; index_close(irel, NoLock); - if (concurrent) + /* + * For temporary indexes, ignore concurrent reindexes. Such relations + * only work with the session assigned to them, so they are not subject + * to concurrent concerns, and a non-concurrent reindex is more efficient. + */ + if (concurrent && persistence != RELPERSISTENCE_TEMP) ReindexRelationConcurrently(indOid, options); else reindex_index(indOid, false, persistence, @@ -2442,7 +2456,12 @@ ReindexTable(RangeVar *relation, int options, bool concurrent) 0, RangeVarCallbackOwnsTable, NULL); - if (concurrent) + /* + * For temporary tables, ignore concurrent reindexes. Such relations + * only work with the session assigned to them, so they are not subject + * to concurrent concerns, and a non-concurrent reindex is more efficient. + */ + if (concurrent && get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP) { result = ReindexRelationConcurrently(heapOid, options); @@ -2648,7 +2667,14 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind, /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); - if (concurrent) + /* + * For temporary relations, ignore concurrent reindexes. Such + * relations only work with the session assigned to them, so they + * are not subject to concurrent concerns, and a non-concurrent + * reindex is more efficient. + */ + if (concurrent && + get_rel_persistence(relid) != RELPERSISTENCE_TEMP) { (void) ReindexRelationConcurrently(relid, options); /* ReindexRelationConcurrently() does the verbose output */ @@ -2771,6 +2797,9 @@ ReindexRelationConcurrently(Oid relationOid, int options) /* Open relation to get its indexes */ heapRelation = table_open(relationOid, ShareUpdateExclusiveLock); + /* Temporary tables are not processed concurrently */ + Assert(heapRelation->rd_rel->relpersistence != RELPERSISTENCE_TEMP); + /* Add all the valid indexes of relation to list */ foreach(lc, RelationGetIndexList(heapRelation)) { @@ -2864,6 +2893,9 @@ ReindexRelationConcurrently(Oid relationOid, int options) /* Save the list of relation OIDs in private context */ oldcontext = MemoryContextSwitchTo(private_context); + /* Temporary tables are not processed concurrently */ + Assert(get_rel_persistence(heapId) != RELPERSISTENCE_TEMP); + /* Track the heap relation of this index for session locks */ heapRelationIds = list_make1_oid(heapId); diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 1cdb7a9663..112188862b 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -1398,6 +1398,17 @@ Indexes: "concur_index5" btree (f2) WHERE f1 = 'x'::text "std_index" btree (f2) +-- Temporary tables with concurrent builds +CREATE TEMP TABLE concur_temp (f1 int, f2 text); +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP TABLE concur_temp; +-- On-commit actions +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 TABLE concur_temp; -- -- Try some concurrent index drops -- @@ -2368,6 +2379,43 @@ SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass); (1 row) DROP TABLE concur_exprs_tab; +-- Temporary tables, where CONCURRENTLY is ignored. +CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text); +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 actions. PRESERVE ROWS is the default, tested above. +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; +-- 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 76598085f7..94764b832a 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -489,6 +489,18 @@ VACUUM FULL concur_heap; REINDEX TABLE concur_heap; \d concur_heap +-- Temporary tables with concurrent builds +CREATE TEMP TABLE concur_temp (f1 int, f2 text); +INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); +CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); +DROP TABLE concur_temp; +-- On-commit actions +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 TABLE concur_temp; + -- -- Try some concurrent index drops -- @@ -954,6 +966,37 @@ 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, where CONCURRENTLY is ignored. +CREATE TEMP TABLE concur_temp_tab_1 (c1 int, c2 text); +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 actions. PRESERVE ROWS is the default, tested above. +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; +-- 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..e26f450846 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -129,6 +129,9 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class= &mdash; see <xref linkend="sql-createindex-concurrently" endterm="sql-createindex-concurrently-title"/>. </para> + <para> + This option is ignored for temporary relations. + </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 10881ab03a..e5d2b1a06e 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -162,6 +162,9 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURR &mdash; see <xref linkend="sql-reindex-concurrently" endterm="sql-reindex-concurrently-title"/>. </para> + <para> + This option is ignored for temporary relations. + </para> </listitem> </varlistentry>
signature.asc (application/pgp-signature, 832B) download

view thread (32+ messages)  latest in thread

Message-ID: <20191115080706.GI1849@paquier.xyz>
Permalink:  ../20191115080706.GI1849@paquier.xyz/
Also on:    postgresql.org/message-id/20191115080706.GI1849@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, andres@anarazel.de, tgl@sss.pgh.pa.us, rigger.manuel@gmail.com
  Subject: Re: REINDEX CONCURRENTLY unexpectedly fails
  In-Reply-To: <20191115080706.GI1849@paquier.xyz>
  References: <CA+u7OA6gP7YAeCguyseusYcc=uR8+ypjCcgDDCTzjQ+k6S9ksQ@mail.gmail.com> <20191113154837.mgz2rnl6d7k6tas7@alap3.anarazel.de> <12383.1573660748@sss.pgh.pa.us> <20191113163851.e2enkdpvd3nrobst@alap3.anarazel.de> <19198.1573663534@sss.pgh.pa.us> <20191114080855.GG1910@paquier.xyz> <3808.1573754033@sss.pgh.pa.us> <20191115024512.GE1849@paquier.xyz> <20191115025412.p3beiflrd6bx6wgf@alap3.anarazel.de> <20191115032141.GH1849@paquier.xyz> <20191115080706.GI1849@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