I have been using PostgreSQL for a year now and have seen a lot of people run
into the same problem that I have, deadlock caused by foreign keys. For me,
this problem is to the point that if my customer grows much more they won't
be able to use PostgreSQL any more. That not withstanding, I am concerned
about the fix that has been proposed by at least one developer.
The terms "dirty read" and "magic" came up during the description of the fix.
The term "dirty read" is a dirty phrase when your using proper
transactioning. The term "magic" is not what you want to hear when the
database is supposed to be the rock that everything else depends on.
Other databases have tackled this issue without the above terms. From what I
can tell, there is a standard and non-standard way this can be fixed in
PostgreSQL. The standard way would be to implement FK as a part of the
schema and create the hooks to allow read locks on records by FK only. The
non-standard way would be to expand the SQL interface with a non standard FOR
READ statement (or something similar) and then continue to use triggers.
Only the developers can decide which method will be easier, work better, or
is more in line with the overall goals of PostgreSQL.
I've even tried to get an estimate from pgsql.com for this issue, but they
just ignored me. I figure that the alternative is to get Oracle which has a
price tag equivalent to at least 20 man weeks of effort (minimum). I'd much
rather see this kind of money go toward making PostgreSQL better. I don't
know if I can actually get the money, but I would at least like to know what
to shoot for. Maybe I can get multiple customers to split the fee.