Re: BUG #6497: Error sent to client, but data written anyway - Mailing list pgsql-bugs

Thanks for all the responses, but I think I'm being unclear here.  Let's
walk through this case step-by-step.  I start with a happy instance of
Postgres:

<pre>
% pstree -anpcul postgres
postgres,1944 -D /data/pgsql     # Main process
  =E2=94=9C=E2=94=80postgres,1948                      # Logger process
  =E2=94=9C=E2=94=80postgres,1950                     # Writer process
  =E2=94=9C=E2=94=80postgres,1952                     # WAL writer process
  =E2=94=9C=E2=94=80postgres,1953          # Auto vacuum launcher process
  =E2=94=9C=E2=94=80postgres,1954                 # Archiver process
  =E2=94=9C=E2=94=80postgres,1955              # Stats collector process
  =E2=94=9C=E2=94=80postgres,1963         # WAL sender process
</pre>

(formatted for readability)

To mimic the failure scenario I am describing, let's start by creating a
table and opening a transaction:

<pre>
postgres=3D# CREATE TABLE test.t1 (c1 int);
postgres=3D# BEGIN; INSERT INTO test.t1 (c1) VALUES (1);
</pre>

At this point (in another shell), let's mimic a crash of the main process:

<pre>
% kill -9 1944 # Note this was postgres -D /data/pgsql from the pstree above
<pre>

Now let's see what is running:

<pre>
% kill -9 1944
% pstree -anpcul postgres
postgres,1948    # logger process
postgres,1950    # writer process
postgres,1955    # stats collector process
</pre>

The processes have all been adopted by init (expected).  If we go back to
the original psql session after postgres is in the above state and complete
the transaction, this is what happens:

<pre>
postgres=3D# COMMIT;
The connection to the server was lost. Attempting reset: Failed.
</pre>

Horray.  We've received an error because the transaction couldn't be
committed, right?  Let's verify:

<pre>
% service postgresql-9.1 start
% psql
postgres=3D# SELECT * FROM test.t1;
 c1
------
    1
(1 row)
</pre>

Clearly this is not correct behavior.  There has been talk in this thread
that the application should simply always try and validate that its
transactions have in fact failed, but that is not a feasible solution (for
many reasons).  Thoughts?

-- Ryan Lowe


On Tue, Feb 28, 2012 at 2:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Huh?  If the backend dumped core before you sent it the commit,
> >> the data will certainly not be committed.  It might be physically
> >> present on disk, but it won't be considered valid.
>
> > I suppose that there is a window of time between the commit becoming
> > effective and the return to the application which, from a user
> > perspective, would be hard to distinguish from what the OP
> > described.  I don't really see how that can be avoided, though,
> > short of a transaction manager using 2PC.  Any time the server
> > crashes while a COMMIT is pending, one must check to see whether it
> > "took".
>
> Yeah, a post-commit crash leaves you in doubt about whether the commit
> "took", but you get into byzantine-generals issues as soon as you try to
> design that out.  Consider the possibility that the commit went fine but
> the network eats the reply packet saying so.  Really, any application
> that is constructed to reconnect and retry has got to have logic to test
> whether the commit occurred, not just assume that it did or didn't.
>
> (2PC is hardly a magic bullet for this, either.)
>
>                        regards, tom lane
>