I've been wondering what the behavior of postgres is when the server process 
stops, for whatever reason, at certain critical points 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 
client may 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 client sends a COMMIT command, but only 
after the COMMIT command has been processed on the server and just before the 
server send the close commend (wire protocol). I.e. the server has COMMITed the 
transaction, but the client just experiences that 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: 0001-exit-before-close-on-commit-command.patch
Description: Binary data

Reply via email to