The following bug has been logged on the website:
Bug reference: 9371
Logged by: Dean Rasheed
Email address: dean.a.rasheed@gmail.com
PostgreSQL version: 8.4.20
Operating system: Linux
Description:
[Spotted in 8.4, but also tested in 9.4devel]
On one of our production systems we are having a problem that some client
processes are being blocked by pg_dump, and therefore having to wait a long
time before they can run. The clients in question are attempting to obtain
an explicit SHARE lock on a table to prevent concurrent data changes, and it
turns out that pg_dump is obtaining a ROW EXCLUSIVE lock on those tables for
the duration of the dump.
Further digging revealed that pg_dump obtains a ROW EXCLUSIVE lock on any
table mentioned in an INSERT, UPDATE or DELETE RULE. Wondering how that can
happen, I tried the following test case, describing a relation:
CREATE TABLE t (a int);
CREATE VIEW v AS SELECT * FROM t;
CREATE RULE r AS ON INSERT TO v DO INSTEAD INSERT INTO t VALUES(NEW.a);
BEGIN;
\d+ v
SELECT mode FROM pg_locks where relation = 't'::regclass;
COMMIT;
Result:
mode
------------------
AccessShareLock
RowExclusiveLock
(2 rows)
whereas I would expect at most an ACCESS SHARE lock.
The reason is that in ruleutils.c, pg_get_ruledef()/make_ruledef() and
get_query_def() are calling AcquireRewriteLocks() on the rule query, which
acquires a ROW EXCLUSIVE lock on its target table, even though the query is
not about to be run in this context. The calls to AcquireRewriteLocks() do
seem to be necessary to handle dropped columns, but perhaps
AcquireRewriteLocks() could be told that the query is not about to be run in
this case, and that it should only acquire ACCESS SHARE locks on the
tables.
Regards,
Dean