I recently encountered an interesting situation with regard to
partitioned tables, concurrent updates and deadlocks which probably
has an obvious explanation, although I can't seem to find one in the
manual. Below, I explain the situation and provide some of my own
naive reasoning about what seems to be happening. Since I think I now
know how to avoid such matters, this message is mostly for the
purposes of sharing my recent experiences with those who may one day
encounter similar problems. I'd be grateful if anyone can explain what
must really be occurring and correct any erroneous conclusions,
however.
I have one process querying a table P with partitions P0, P1, P2, ...
Pn joined with table R as follows:
select * from R inner join P on R.id = P.id and P.section = 5
...where the column "section" determines which partition shall be
searched utilising the constraint exclusion support in PostgreSQL.
Here, I use the specific value of 5 to indicate that the involvement
of a specific partition is envisaged.
Now, each partition of P is created inheriting from P, and I also
include a rule which "redirects" inserts from P to the specific
partition of P depending on the value of "section". This latter detail
is, I believe, the principal contributing factor to the problems
subsequently experienced.
I have another process performing updates to individual partitions of
P - specifically "alter table" operations adding foreign key
constraints referencing R as follows:
alter table Pm add constraint Pm_fk_id foreign key(id) references
R(id)
...where "m" is the specific partition number, starting at 0,
increasing by 1, ending at n.
What seems to happen, by looking at pg_lock (and pg_class) is that the
following sequence of events occurs:
1. The query process acquires an AccessShareLock on R and P.
2. The update process acquires an AccessExclusiveLock on Pm and seeks
an AccessExclusiveLock on R.
3. The query process seeks an AccessShareLock on P0 ... Pn.
4. Deadlock is declared.
Since the query should only involve a single partition of P, one might
expect that the query process might immediately obtain an
AccessShareLock on P5, but what seems to occur is a race condition:
the update process is sometimes able to acquire a lock on P5 before
the query process is able to realise the involvement of P5 in the
query operation. Moreover, a deadlock occurs even when the update
process is adding the foreign key constraint to tables other than P5,
suggesting as I note above that all child tables are involved in the
query operation.
My initial conclusions were as follows:
1. A query on a partitioned table only initially causes lock
acquisition on the parent table.
2. Subsequent attempts to acquire locks on child tables conflict with
the locking done by the "alter table" operation.
3. The classic solution (ensure consistent lock acquisition order)
may not be readily applicable.
Intuitively, I understood that PostgreSQL may only resolve the child
tables involved in a query by using a mechanism specific to the
partitioning infrastructure. I then considered the role of the rules
(collectively redirecting inserts from P to P0 ... Pn), even though
they are concerned with insert statements. By dropping the rule
associated with a given child table before attempting the "alter
table" operation on that table, then recreating the rule, it would
appear that the issues with lock acquisition disappear.
It makes sense that, if operating on a specific child table, the links
to the parent should be broken temporarily in order to isolate it from
the parent and any operations which may involve all children (or even
the checking of the involvement of all children), and to not realise
this may have been an oversight on my part. Can anyone help me to
refine my thinking further on this matter?
Paul
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
