Paul Boddie <paul@xxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
