developer notes: PostgreSQL hanging after a restart

If PostgreSQL behaves strangely after a restart, e.g. SQL statements appear to hang or a table can't be truncated/dropped, chances are the database didn't close down cleanly and a transaction wasn't committed/rolled back.

If there are any transactions in the view view-pg-prepared-xacts roll them back and the problem will hopefully be resolved.

First login to Postgres

PGPASSWORD=password psql -d trade -U tradeadmin

See if there are any outstanding transactions

SELECT database, gid FROM pg_prepared_xacts;

database | gid
---------------+------------------------------------------------------------------------------
trade | 1096044365_bmV0cGFnZWNvbW1vbi50bTAwMDAxMDAwMDQ=_bmV0cGFnZWNvbW1vbi50bTE2OQ==
(1 row)

To rollbank the transaction enter ROLLBACK PREPARED and the gid .e.g.

ROLLBACK PREPARED '1096044365_bmV0cGFnZWNvbW1vbi50bTAwMDAxMDAwMDQ=_bmV0cGFnZWNvbW1vbi50bTE2OQ==';