How to detect deadlocks in PostgreSQL when using PHP's pg_* functions?

Question

I use transactions with PostgreSQL. Transactions are a necessary part of serious databasing. Transactions inevitably lead to "deadlocks". Deadlocks are logged as errors, as they are. I don't want to ever have any errors logged, so I need to handle these deadlocks. Deadlocks are allegedly handled by detecting them, suppressing the error and retrying the query until it goes through without a deadlock.

So far, so good.

Now begin the problems. I use pg_query_params() and pg_query() to send queries to PostgreSQL. Both of these return FALSE on failure, or a "query result resource" upon success. When a deadlock is detected by PostgreSQL, these functions thus return a FALSE and my PHP error log gets a bunch of noise about deadlocks.

The PHP manual says this about pg_last_error():

Error messages may be overwritten by internal PostgreSQL (libpq) function calls. It may not return an appropriate error message if multiple errors occur inside a PostgreSQL module function.

It's thus not reliable and cannot be used. It goes on to state:

Use pg_result_error(), pg_result_error_field(), pg_result_status() and pg_connection_status() for better error handling.

Having looked up those functions, I realize to my horror that (as it says):

Because pg_query() returns FALSE if the query fails, you must use pg_send_query() and pg_get_result() to get the result handle.

pg_send_query() and pg_send_query_params(), in turn, are asynchronous. I have no need for, nor understanding of, such "async" SQL queries. I don't understand how that is possible or why anyone would want it.

The end result is that, once again, I find myself painted into a corner where seemingly the only way out is to crawl through the chimney and get myself all dirty and messy.

It appears I'm forced to entirely abandon pg_query_params() and pg_query() just to be able to detect deadlocks. Can this really be the case? I can only imagine what new errors will arise from the SQL queries being sent "asynchronously" instead of in a "blocking", orderly manner.

Why does this always happen? Every time I try to do anything, no matter how basic or common, it always seems to be considered a "weird edge-case" to everyone else. Surely there must be a way to detect deadlocks other than jeopardizing my entire application's integrity by using these strange "async" functions which I hadn't even heard of until yesterday?

And even if I were to use them, it's still very unclear how exactly I would detect a deadlock. Do they expect me to parse the error and look for the English strings such as "deadlock"? That also doesn't seem right at all; it feels like a weird hack.

Is there really no proper, clean way to detect deadlocks so that they can be handled properly?

Simply suppressing the PHP error (by using a custom error logger and checking for the string) would only solve the problem of error log noise, but would not actually make the deadlocked queries retry, and thus they would never do their job and just be silently ignored.

Answer 1

If anyone cares, I ended up "abusing" the pg_send_* queries in an blocking manner, just to be able to properly grab the deadlock (and potentially other, future) errors. It's the only solution, albeit it made me feel more "dirty" than satisfied with the solution. Maybe seeing this self-answer will help somebody in the future to realize that it's a complete waste of time to try it any other way, because I did, and failed, for a very long time.