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