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