I have a situation that (I think) requires using 'LOCK TABLE', but I'm
having a little difficulty wrapping my head around the idea.
I have a fairly large table with a *lot* of access (the majority of all
queries in the app use this table). One of the fields in this table
is a path to a file on the filesystem. I want to change the contents
of the file, and thus not allow anyone to use the row which references
the file while the file is being updated. I then want to execute an
'UPDATE' on the table, remove the lock, and allow everything to
continue as normal.
The filesystem I/O (changing the file) is implemented outside of PgSQL -
it's done in the client app.
If at all possible, I'd like to only lock the 1 row that is being
updated. Any database activity should be allowed, unless it refers
to the row currently being updated. If it does (and even if it is
a SELECT), the query on the row should wait until the file has
been updated and the 'UPDATE' run on the DB.
Is this possible? Can I only lock 1 row, or do I have to lock the
entire table? Am I correct in assuming that if a query tries to
access a currently locked table/row, it will just poll until the
row/table is unlocked?
Tell me if you'd like more info - I hope I'm explaining myself clearly.
Thanks in advance,
Neil
--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
Vegetarians do not love animals... they hate plants.
