Re: SQLSTATE when PostgreSQL crashes during COMMIT statement

Am 15.04.19 um 12:41 schrieb Francisco Olarte:
> 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.
>
>

since pg10 you can use check the status of a transaction, here is a
explanation:
https://blog.2ndquadrant.com/postgresql-10-transaction-traceability/

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com