There's more…

If you're not sure what the prepared transaction actually did, you can go and look, though that is time-consuming. The pg_locks view shows locks that are held by prepared transactions. You can get a full report of what is being locked by using the following query:

postgres=# SELECT l.locktype, x.database, l.relation, l.page, l.tuple,l.classid, l.objid, l.objsubid, l.mode, x.transaction, x.gid, x.prepared, x.owner
FROM pg_locks l JOIN pg_prepared_xacts x ON l.virtualtransaction = ‘-1/’ || x.transaction::text;

The documents mention that you can join pg_locks to pg_prepared_xacts, but they don't mention that if you join directly on the transaction ID, all it tells you is that there is a transaction lock, unless there are some row-level locks. The table locks are listed as being held by a virtual transaction. A simpler query is the following:

postgres=# SELECT DISTINCT x.database, l.relation FROM pg_locks l JOIN pg_prepared_xacts x ON l.virtualtransaction = ‘-1/’ || x.transaction::text WHERE l.locktype != ‘transactionid’; database | relation
---------+----------
postgres |    16390
postgres |    16401
(2 rows)

This tells you which relations in which databases have been touched by the remaining prepared transactions. We can't tell the names because we'd need to connect to those databases to check.

Finally, we can inspect which rows have been changed by the transaction. We will use xmin, which is a hidden column in each table. For more details on that, refer to the Identifying and fixing bloated tables and indexes recipe in this chapter.

You can then fully scan each of those tables, looking for changes like the following:

SELECT * FROM table WHERE xmax = 121083;

This query will show you all the rows in that table that will be deleted or updated by transaction 121083, taken from the transaction column of pg_prepared_xacts.

Not all rows touched by the transaction can be displayed, however. Newly inserted rows and new versions of updated rows will not be accessible in this way, for the very good reason that they must be invisible before the transaction is committed.

As you might expect, the PostgreSQL developers did their homework properly. Say that you have some prepared transactions and you change max_prepared_transactions to zero, which requires a restart to come into effect. No prepared transaction will sneak into your database unnoticed. When starting, PostgreSQL will try to recover every prepared transaction, and refuse to start unless max_prepared_transactions is large enough.