Application crashed in middle of transaction

I had an issue where the Xojo Desktop application (2013r3.3) crashed in the middle of a transaction (Postgres 9.1). The application had loaded a Utility table which contains a single record (for each company using this database) that is used by many tables. (For example, it contains the next invoice number, payment number, customer number). Normally, this Utility record is modified and released in less than one second.

Unfortunately, the transaction was never closed and the Utility record remained locked.

What happened next was that when the next workstation went to create a new invoice record, it had to wait for the Utility record to be free (which was locked by this other (forever open) transaction) - therefore hanging in the middle of another transaction. The user became frustrated and force-quit the application, thus creating another transaction that was never closed properly.

When I went into pgAdmin, I saw 7 or 8 transactions all waiting for this Utility record, basically hanging the database for this user. I was able to manually stop each one of these transactions in pgAdmin and then all was well again.

Since every other company has their own Utility record, it didn’t affect any other users except the users at this one company. The database has been running well for quite a long period of time (about two and a half years) and this has never occurred before.

How is this normally handled? Is this just something we have to deal with every few years or so - or is there some way to gracefully close this hanging transaction?

I’m guessing you’d need superuser status to do anything about it, which you probably don’t want to grant to each user so they can have control over the locking.

Is there a setting in Postgres that can limit how long a transaction can be - or would this transaction exist forever until I aborted it?

I don’t understand why the user wasn’t killed when your software crashed which would end (rollback) his transaction.