Re: SQLSTATE when PostgreSQL crashes during COMMIT statement

On Mon, Apr 15, 2019 at 4:11 AM Takahashi, Ryohei
<r(dot)takahashi_2(at)jp(dot)fujitsu(dot)com> wrote:
> If application executes COMMIT statement and COMMIT failes because of PostgreSQL crash,
> it is unknown whether the transaction is really committed.
> Therefore, I think application should check the transaction is really committed after a while when certain SQLSTATE is returned.
> Which SQLSTATE should application check? Or, is there any document which is written about this?
> In my observation, ecpg returns '57P02' and libpq returns 'null' when PostgreSQL crashes during COMMIT statement.
> Any other SQLSTATE?

My math is rusty, but I doubt you can reliably detect wheter a commit
failed. Detecting full success is easy, you get a correct code. Commit
failed without server crash is easy to, you get fail code. But if the
library has sent the commit message but not received the ok/fail code,
as the server has to 1st persist the query to disk and 2nd send the
result (OK/FAIL) back you never know what has happened. So, if the
library gives you an error ( "I have not received the commit
confirmation" , not something like "commit failed" ), you will still
need to test the data, if you can, to know how the server come back
up.

Francisco Olarte.