Обсуждение: SQLSTATE when PostgreSQL crashes during COMMIT statement

Hi,


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?


Regards,
Ryohei Takahashi

On Mon, Apr 15, 2019 at 4:11 AM Takahashi, Ryohei
<r.takahashi_2@jp.fujitsu.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
isreturned.
 
> 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.

Am 15.04.19 um 12:41 schrieb Francisco Olarte:
> On Mon, Apr 15, 2019 at 4:11 AM Takahashi, Ryohei
> <r.takahashi_2@jp.fujitsu.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
isreturned.
 
>> 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

Hi Francisco and Andreas,


Thank you for your reply.

> 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.

Yes.
I want to know what error does library give to the application on such case.
I supposed it is certain SQLSTATE.


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

Thank you for the information.
According to the page and the PostgreSQL documentation of txid_status(),
the application should look up the transaction's completion status when the connection is lost,
not when certain SQLSTATE is given, right?


Regards,
Ryohei Takahashi

"Takahashi, Ryohei" <r.takahashi_2@jp.fujitsu.com> writes:
>> 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.

> Yes.
> I want to know what error does library give to the application on such case.
> I supposed it is certain SQLSTATE.

libpq, at least, doesn't produce SQLSTATEs for errors that it detects.
(It probably should, but that's been a TODO item for a couple of
decades now ... so don't hold your breath.)  So the best you can do here
is to check the error message spelling.

A crude but possibly sufficient approach is to assume that once you've
got a working connection, *any* error that lacks a SQLSTATE is indicative
of connection loss.  This idea could fail for cases like out-of-memory,
but if your app is at the edge of OOM then giving up might be wise anyway.

            regards, tom lane