My apologies for what may be an obvious answer to others but the documentation
(for v8.1) leaves me concerned (enough not to trust "expected behaviour" of
some commands).
Essentially I was thinking of using PERFORM (SELECT) ... FOR UPDATE within
PL/pgSQL - all indications are that this should be sufficient to prevent other
concurrent transactions from modifying selected rows until the current
transaction completes:
"This prevents them from being modified or deleted by other transactions
until the current transaction ends. That is, other transactions that
attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be blocked
until the current transaction ends."
See:
http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE
Okay so far - until you read this:
"ROW SHARE
Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes.
The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a lock of
this mode on the target table(s) [...]
"ROW EXCLUSIVE
Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS
EXCLUSIVE lock modes.
The commands UPDATE, DELETE, and INSERT acquire this lock mode on the
target table [...]"
See:
http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html#LOCKING-TABLES
The thing that worries me is that according to the documentation, "ROW SHARE"
and "ROW EXCLUSIVE" lock modes don't conflict which means (logical
progression, not considering actual behaviour) an UPDATE, DELETE or INSERT
could change data in an underlying table, despite another transaction already
having executed "SELECT ... FOR UPDATE".
Is the explicit locking documentation page just missing something or have I
completely misunderstood either the behaviour of "SELECT ... FOR UPDATE" or
the interaction of the various locking modes?
The workaround for the moment is just to explicitly LOCK the table but I'd
rather remove explicit locking statements if possible.
Thanks..