concurrent SELECT blocking ALTER?

From: Neil Harkins <nharkins(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: concurrent SELECT blocking ALTER?
Date: 2014-01-29 21:59:27
Message-ID: CAMtfGdUhrV6wNeG1EAObPUmW9US_WiyqFeWG+BTPpOo4FptSbQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all, I'm curious if anyone can explain or suggest some debugging to
explain some odd locking behavior I am able to reproduce on pg 9.2.1

I start a test program with 20 threads (autocommit=1), all
executing the same SELECT query with a LEFT OUTER JOIN,
1~2 per second. Then I execute an ALTER to drop a column
(one not referenced by the query, neither explicitly or by a *)
from the table referenced in the OUTER JOIN.

The ALTER blocks for many minutes:

2014-01-28 02:39:48.781
GMT,"postgres","pbs_production",49521,"[local]",52e713cb.c171,7,"ALTER
TABLE waiting",2014-01-28 02:19:55
GMT,18/59,241951078,LOG,00000,"process 49521 acquired
AccessExclusiveLock on relation 16637 of database 16409 after
932916.917 ms",,,,,,"alter table refunds drop column
external_refund_id;",,,"psql"

When I stop the test program doing the SELECTs,
the ALTER gets unblocked and completes very quickly
(the table contains 20k rows):

2014-01-28 02:39:48.803
GMT,"postgres","pbs_production",49521,"[local]",52e713cb.c171,8,"ALTER
TABLE",2014-01-28 02:19:55 GMT,18/0,0,LOG,00000,"duration: 932939.482
ms statement: alter table refunds drop column
external_refund_id;",,,,,,,,,"psql"

When the test program is running, I see:

# SELECT mode, COUNT(*) FROM pg_locks
WHERE pid != pg_backend_pid() GROUP BY 1;
mode | count
-----------------+-------
ExclusiveLock | 20
AccessShareLock | 440
(2 rows)

Why are those exclusive locks present?
Can't the database rely on mvcc for those reads
without locking? The autocommit should be
increasing the xid used for the reads, so the
ALTER should be able to slip in-between?

thanks in advance for any input,
-neil

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2014-01-29 22:32:39 Re: concurrent SELECT blocking ALTER?
Previous Message George Ant 2014-01-29 21:44:56 Re: Composite type