Postgresql – What determines the time waited for Postgres Transaction Timeout

The next thing to check is probably that you have a line in your postgresql.conf that looks like this:

listen_addresses = '*'

You will have to restart after changing that line. Otherwise your pg_hba.conf looks fine.

Good luck! Getting started is a big hurdle, but once you get through it, you will probably find that compared to MySQL things are a lot simpler and more consistent.

Can READ COMMITTED be set to indefinitely retry with the same atomicity as SERIAZLIZABLE?

No.

READ COMMITTED doesn't retry. Neither does SERIALIZABLE. The application is expected to retry transactions that suffer deadlocks, serialization failures, etc.

That description in docs is very misleading, I'll raise it on the docs list. PostgreSQL doesn't "start over" in READ COMMITTED at all, like some other DBs (e.g. Oracle) do. Instead it waits until the row it's waiting for commits or rolls back. If the other tx commits PostgreSQL reads the updated row, checks that it still matches any WHERE clause or other predicate, and then continues execution. The details are a bit arcane, see EvalPlanQual in the sources.

In either READ COMMITTED or SERIALIZATION the application should be able to re-issue a transaction. READ COMMITTED transactions can fail in ways that will succeed on retry, including:

• Deadlock detection due to lock upgrades, lock ordering issues, etc
• Administrative query cancel requests
• Administrative shutdown/restart of the DB
• Unplanned crash/restart of the DB
• Connectivity interruption
• ... etc

SERIALIZABLE just adds some more failure cases.

Well written applications will cope with query failures and re-issue the transaction.