Unit-testing PostgreSQL row-level locks

Question

I am currently adding unit tests to a rather large quantity of PostgreSQL stored procedures, using pgTap.

Some of the procedures perform operations which lock rows explicitly. These locks are critical to the application.

How do I write tests that check that the rows that need to be locked have been, and that rows which shouldn't be locked aren't?

The only "clue" I have at the moment is the pgrowlocks extension, which allows a transaction to check for rows locked by another transaction. However, the current transaction doesn't seem to see its own locks, so I'd have to use something to synchronise two transaction, and unless I am quite mistaken, there's no way to do that using pgTap.

(note: using PostgreSQL 9.1)

Answer 1

If you can identify the ctid of the rows in question, and know which transaction should have the rows locked, maybe you could use the pageinspect extension and look at the tuple info flags and xmax? The info flags should indicate the row is locked, and xmax be set to the transaction id holding it.

Answer 2

How do I write tests that check that the rows that need to be locked have been, and that rows which shouldn't be locked aren't?

Open separate transation, try to lock the same row with NOWAIT, and catch the exception.

PostgreSQL has no support for autonomous transactions, so - to open separate transaction from within PgTAP test, you will have to resort to dblink or other similar extension.

PS. I found this link, where Robert Haas explains, why row-level tuples are not tracked in pg_locks:

(...) ungranted tuple locks show up in pg_locks, but they disappear once granted. (PostgreSQL would run out of lock table space on even a medium-sized SELECT FOR UPDATE query if we didn't do this.)

On the other hand - I quite don't understand why you want to test for lock existence - it's guaranteed after succesful LOCK command.