From: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
---|---|
To: | Fujii Masao <masao(dot)fujii(at)gmail(dot)com> |
Cc: | Andres Freund <andres(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Simon Riggs <simon(at)2ndquadrant(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Support for REINDEX CONCURRENTLY |
Date: | 2013-02-21 02:55:54 |
Message-ID: | CAB7nPqR8OMq=z5wT6Zgbk7bS-TD=kXnkCrUtrGP6EofN6D1-qw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Thanks for your review!
On Wed, Feb 20, 2013 at 12:14 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> When I compiled the HEAD with the patches, I got the following warnings.
>
> index.c:1273: warning: unused variable 'parentRel'
> execUtils.c:1199: warning: 'return' with no value, in function
> returning non-void
>
Oops, corrected.
> When I ran REINDEX CONCURRENTLY for the same index from two different
> sessions, I got the deadlock. The error log is:
>
> ERROR: deadlock detected
> DETAIL: Process 37121 waits for ShareLock on virtual transaction
> 2/196; blocked by process 36413.
> Process 36413 waits for ShareUpdateExclusiveLock on relation 16457
> of
> database 12293; blocked by process 37121.
> Process 37121: REINDEX TABLE CONCURRENTLY pgbench_accounts;
> Process 36413: REINDEX TABLE CONCURRENTLY pgbench_accounts;
> HINT: See server log for query details.
> STATEMENT: REINDEX TABLE CONCURRENTLY pgbench_accounts;
>
> And, after the REINDEX CONCURRENTLY that survived the deadlock finished,
> I found that new index with another name was created. It was NOT marked as
> INVALID. Are these behaviors intentional?
>
This happens because of the following scenario:
- session 1: REINDEX CONCURRENTLY, that has not yet reached phase 3 where
indexes are validated. necessary ShareUpdateExclusiveLock locks are taken
on relations rebuilt.
- session 2: REINDEX CONCURRENTLY, waits for a ShareUpdateExclusiveLock
lock to be obtained, its transaction begins before session 1 reaches phase 3
- session 1: enters phase 3, and fails at WaitForOldSnapshots as session 2
has an older snapshot and is currently waiting for lock on session 1
- session 2: succeeds, but concurrent index created by session 1 still
exists
A ShareUpdateExclusiveLock is taken on index or table that is going to be
rebuilt just before calling ReindexRelationConcurrently. So the solution I
have here is to make REINDEX CONCURRENTLY fail for session 2. REINDEX
CONCURRENTLY is made to allow a table to run DML in parallel to the
operation so it doesn't look strange to me to make session 2 fail if
REINDEX CONCURRENTLY is done in parallel on the same relation.
This fixes the problem of the concurrent index *_cct appearing after
session 1 failed due to the deadlock in Masao's report.
The patch correcting this problem is attached.
Error message could be improved, here is what it is now when session 2
fails:
postgres=# reindex table concurrently aa;
ERROR: could not obtain lock on relation "aa"
Comments?
--
Michael
Attachment | Content-Type | Size |
---|---|---|
20130221_1_remove_reltoastidxid.patch | application/octet-stream | 37.0 KB |
20130221_2_reindex_concurrently_v12.patch | application/octet-stream | 80.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2013-02-21 03:48:14 | Re: sql_drop Event Trigger |
Previous Message | Joachim Wieland | 2013-02-21 02:49:24 | Re: posix_fadvise missing in the walsender |