Update if no lock in postgres? Is there a way to issue a postgres update query that just does nothing if the row it wants to update is locked?  Or perhaps waits a very short time before giving up, just for this query?

I have a table with an updated_at field which is used for Russian doll caching.  Russian doll caching basically uses updated_at data in cache keys so you can invalid the cache by simply modifying the updated_at field. So for example if you have 2 tables:

books:
 id: int
 name: string
 updated_at: datetime


pages:
 id: int
 book_id: int
 text: text
 updated_at: datetime


You can now cache a book based on books.id and books.updated_at.  If a page changes you change the updated_at for the parent book and that book's cache is invalidated.

The issue is if a large number of pages for the same book are being modified at the same time, you end up with a flood of queries to update updated_at for the same book record.  As result, there is a ton of blocking while each update queries waits for a lock.  We actually don't need all the updates to happen since it is just being used to invalid that cache.  So if only one actual update happens per sec we are fine.

Any thoughts on how to achieve this?
I think what you want is NOWAIT


  To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table. Note that NOWAIT and SKIP LOCKED apply only to the row-level lock(s) — the required ROW SHARE table-level lock is still taken in the ordinary way (see Chapter 13). You can use LOCK with the NOWAIT option first, if you need to acquire the table-level lock without waiting.


That said, I'm not sure why you would want to do this. I've never had to do this. It sounds like a bad idea. It's quite normal to wait for a lock to update the rows. Retrying transactions is usually a longer-process. If you're afraid of issuing an update you don't want to a modified row, you don't have to worry about it. Dirty-writes are prevented.

UPDATE foo
SET baz = 1
WHERE bar = 2;


Will never update where a bar isn't 2. If it's waiting for a lock, after it gets the lock it'll check bar=2 and set baz=1 by rewriting the row.

I just can't see this as being more than trivia, or an extremely esoteric use-case.

I would keep it simple if you go the NOWAIT method becuase I don't think it matters anyway.

BEGIN;
  SELECT *
  FROM foo
  WHERE bar=2
  FOR UPDATE NOWAIT;

  UPDATE foo SET baz=1 WHERE bar=2;
END;


That whole transaction will rollback if it can't get the lock right away.