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.