Re: Support for REINDEX CONCURRENTLY

On Thu, Feb 21, 2013 at 11:55 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> 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.

Thanks for updating the patch!

With updated patch, REINDEX CONCURRENTLY seems to fail even when
SharedUpdateExclusiveLock is taken by the command other than REINDEX
CONCURRENTLY, for example, VACUUM. Is this intentional? This behavior
should be avoided. Otherwise, users might need to disable autovacuum
whenever they run REINDEX CONCURRENTLY.

With updated patch, unfortunately, I got the similar deadlock error when I
ran REINDEX CONCURRENTLY in session1 and ANALYZE in session2.

ERROR: deadlock detected
DETAIL: Process 70551 waits for ShareLock on virtual transaction
3/745; blocked by process 70652.
Process 70652 waits for ShareUpdateExclusiveLock on relation 17460 of
database 12293; blocked by process 70551.
Process 70551: REINDEX TABLE CONCURRENTLY pgbench_accounts;
Process 70652: ANALYZE pgbench_accounts;
HINT: See server log for query details.
STATEMENT: REINDEX TABLE CONCURRENTLY pgbench_accounts;

Like original problem that I reported, temporary index created by REINDEX
CONCURRENTLY was NOT marked as INVALID.

=# \di pgbench_accounts*
List of relations
Schema | Name | Type | Owner | Table
--------+---------------------------+-------+----------+------------------
public | pgbench_accounts_pkey | index | postgres | pgbench_accounts
public | pgbench_accounts_pkey_cct | index | postgres | pgbench_accounts
(2 rows)

Regards,

--
Fujii Masao