Re: Fix FK deadlock, but no magic please

On Thu, 16 Jan 2003, Jon Swinth wrote:

> 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.

Well, I used magic flippently as short for, I haven't worked out the
details yet because I'm in the sit down design and try things out stage.
I'll apologize for using the term then.

As for dirty reads, given that part of the part that was referred to as
magic involves doing waits on transactions so that it's very much like
the current row locks except with foreign key based knowledge embedded
so as to help avoid deadlocks.  Yes, it's seeing rows that haven't
been committed, but it's mostly seeing them to find out what transactions
it needs to wait 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.

Record read locks are not quite as good a solution as dirty reads from a
performance standpoint, which is why we've been aiming that direction
first. You'd need column locks pretty much to get equivalent behavior
afaict.  The issue is that with record read locks, you prevent updates to
rows that do not affect the key values.

> 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.

I personally can't (I have a full time job that has nothing to do with
PostgreSQL which is part of why this hasn't gotten done yet), but I
certainly wouldn't want that to stop someone who does have the time and
could take the money from doing it instead. :)