Re: Lock Detection (was: pg_dump failing on LinuxPPC)

Tom Lane wrote:
>
> Stu Coates <stu(at)StuCoates(dot)com> writes:
> > I come from an Oracle background where I can lock an item of data by
> > performing a "SELECT FOR UPDATE" on the row. This is also implemented
> > in PostgreSQL. A quite useful feature Oracle does have is the ability
> > to add a "NOWAIT" clause to the end of the command which will cause an
> > exception if the item of data already has a lock taken out on it.
> > AFAIK, this is not implemented in PostgreSQL. I did see a note that
> > lock timeouts are not implemented, but if "NOWAIT" is added the
> > application developer could implement/fudge timeouts him/herself. Would
> > this be relatively easy to add?
>
> It'd be possible, but not particularly easy; anything that involves
> changing parse/plan trees is tedious. Moreover, I doubt that it'd be
> especially useful given the lack of nested transactions in Postgres.
> You'll have to make a lot better case than the above if you want to
> get anyone excited about the idea.

I realize that PostgreSQL does not support nested transactions.

For the project I'm currently working on I'd like my unit of work to be:

begin transaction
lock all of the entities (rows) that I'll require in this transaction
if any lock fails then abort transaction (rollback)

perform operations

end transaction (commit)

...and then be able to perform this unit of work for a whole list of
work units as fast as I can without waiting for locks to be released by
other processes. If any of the units of work cannot be completed due to
existing locks then they will be retried at a later time. At the moment
I'd have to maintain my own lock lists and perform atomic operations on
that to get the behavior that I require. Can you suggest another way
using the PostgreSQL lock manager?

Regards,

Stu.

--
Stu Coates
Chelmsford, England U.K.
http://www.StuCoates.com/

The day Microsoft makes something that doesn't suck is probably the day
they start making vacuum cleaners.