Postgresql object ID and tuples

Question

I sometimes see messages like

Process 12990 waits for ExclusiveLock on tuple (889,66) of relation 17720 of database 17607; blocked by process 12992.

So of course the 'process' part is quite clear, but I don't know how to correlate between the relation ID and a human readable name. I also don't really know what to make of the tuple bit.

Anyone know how to read these messages and how to glean useful data from them?

Thanks!

Answer 1

A "relation" is a table and a "tuple" is a row.

Here's a nice shortcut for getting the name of the table from the table id (you can also query the pg_class table):

=> select 17720::regclass; ┌──────────┐ │ regclass │ ├──────────┤ │ my_table │ └──────────┘ (1 row) 

Now how about the row? The "tuple bit" is a tuple identifier, and every table in your database has a special system column called ctid where those identifiers are stored. Now that we know the table in question, we can do:

=> select * from my_table where ctid='(889,66)'; 

However! From the system column docs (emphasis added): "[A]lthough the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier." In other words, if you're quick enough you can probably trust that the row returned is the one involved in the deadlock, but that info won't be available forever.

Answer 2

You can look this up the system tables : the one of interest here is pg_class.

Doing a query like

SELECT OID, relname FROM pg_class  oid  |              relname                -------+------------------------------------   1247 | pg_type  11550 | user_mapping_options  11554 | user_mappings  11494 | triggered_update_columns  11497 | triggers 

or rather

SELECT relname FROM pg_class WHERE OID=17720 

might shed light on the locks.