Re: Deadlock when updating table partitions (and presumed solution)

Paul Boddie <paul(at)boddie(dot)org(dot)uk> writes:
> 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
> ...
> 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)

Yeah, this is a problem. The SELECT will acquire AccessShareLock
on R and P, and subsequently try to acquire AccessShareLock on all
the inheritance children of P (and I don't think the order in which
these locks are acquired is very clear). Meanwhile the ALTER acquires
AccessExclusiveLock on Pm and R --- probably in that order, though
I'd not really want to promise that ordering either. So the potential
for deadlock is obvious.

You seem to be hoping that the SELECT would avoid acquiring lock
on child tables Pn that it didn't need to access, but this cannot be:
it has to get at least AccessShareLock on those tables before it can
even examine their constraints to find out that they don't need to be
scanned. And even if it could magically not take those locks, the
deadlock condition still exists with regard to the child table that
it *does* need to access.

I guess I'm wondering why you need to be adding foreign key constraints
during live operations.

regards, tom lane