Thread: How to handle failed COMMIT
I've been wondering what the behavior of postgres is when the server process stops, for whatever reason, at certain criticalpoints in the execution flow. In the following example the client will only regard the data as stored until the COMMIT command is successfully executed.But the the server, client or network may fail at any point during the execution and therefore the server and clientmay not be in sync of what the current state is. BEGIN; INSERT INTO ....; COMMIT; To experiment with this I inserted a stupid if statement (see patch) which will make the server process exit(1) if the clientsends a COMMIT command, but only after the COMMIT command has been processed on the server and just before the serversend the close commend (wire protocol). I.e. the server has COMMITed the transaction, but the client just experiencesthat the connection has been closed for some reason: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. So my question is, how should the client handle these situations? I think most systems will have some variation of the following: try execute transactional sql catch (commit failed) // regard data as not stored But, this doesn't seem to be enough, so I guess you would have to do something like: try execute transactional sql catch (commit failed) if (data is not stored) // regard data as not stored Thanks in advance. /Håvar Nøvik
Attachment
On Monday, July 18, 2022, Håvar Nøvik <havar@novik.email> wrote:
try
execute transactional sql
catch (commit failed)
if (data is not stored)
// regard data as not stored
Correct, the client did not get confirmation of commit success so it must operate as if it failed.
David J.
> Correct, the client did not get confirmation of commit success so it must operate as if it failed.
I mean that’s the point, the client can’t operate as if it failed. It must operate as the state is unknown. But maybe that’s the correct application behaviour, just that I haven’t thought this through previously.
/Håvar Nøvik
On Tue, Jul 19, 2022, at 16:12, David G. Johnston wrote:
On Monday, July 18, 2022, Håvar Nøvik <havar@novik.email> wrote:tryexecute transactional sqlcatch (commit failed)if (data is not stored)// regard data as not storedCorrect, the client did not get confirmation of commit success so it must operate as if it failed.David J.
On Tuesday, July 19, 2022, Håvar Nøvik <havar@novik.email> wrote:
> Correct, the client did not get confirmation of commit success so it must operate as if it failed.I mean that’s the point, the client can’t operate as if it failed. It must operate as the state is unknown. But maybe that’s the correct application behaviour, just that I haven’t thought this through previously.
Right, since you sent commit there is now a non-zero chance the data is committed but the client is unaware of that fact.
David J.