Experts in Postgres and Open Source Infrastructure

24x7, 365 Enterprise services since 1997


Fixing foreign key deadlocks submitted

A week ago, I submitted my patch to fix the foreign key lock problem. What I propose is almost exactly what was described in my previous blog posts, with a couple of differences.

The first is that I decided to only check unique indexes, not all indexes. This is good because you can have other indexes for performance and they will not affect locking for foreign keys. I noticed that …

Automatically pulling into a committer's Git repository

I was a bit unhappy because I couldn't keep my "bare" Git repository up-to-date unattended — I needed to be at my workstation to be able to do a git fetch, because it needs my SSH passphrase.

I didn't have this problem with CVS because I kept an rsync copy of the anonymous CVS repository, from which my regular trees where checked out. (My committer's checkouts were separate, which …

Fixing foreign key deadlocks, part 2 revisited

While trying to implement SELECT FOR KEY LOCK at the lock manager level, I stumbled across the problem that I need to represent the lock faithfully in the lock manager's terms. And since I previously mentioned that FOR KEY LOCK would conflict with FOR SHARE, I get in trouble — it's not easy to see which lock mode to use (if there is one, which I doubt).

So I …

Fixing foreign key deadlocks, part 2

In the previous article, I explained the problem with foreign key checks checks obtaining too strong of a lock, and promised that we would be attempting to fix it.

Here is my proposal:

  1. Create a new SELECT locking clause. For now, we're calling it SELECT FOR KEY LOCK
  2. This will acquire a new type of lock in the tuple, dubbed a "keylock".
  3. This lock will conflict with DELETE, …

Fixing foreign key deadlocks

I've been commissioned to work on foreign keys. More specifically, on the problem that when foreign keys are verified, they sometimes obtain locks that are stronger than really needed. This causes some operations to block unnecessarily and, perhaps more aggravating, some other operations to deadlock.

This problem has been known for a very long time, and it affects many users to varying degrees. The most recent detailed discussion about this …

The old Berkeley Postgres code

Some days ago, I was reading some patch from the in-progress commitfest, and happened to notice this comment in src/include/tcopprot.h:

 *    This file was created so that other c files could get the two
 *    function prototypes without having to include tcop.h which single
 *    handedly includes the whole f*cking tree -- mer 5 Nov. 1991

The weird thing about this was that there's no tcop.h file on the …