Обсуждение: Yipes, I'm getting bit by duplicate tuples

I've got a production application (currently running on 6.3.2 + Btree
patch) in which several clients concurrently update the same set of
tables.  The client requests are pretty simple --- SELECTs using
btree-indexed fields, INSERTs, and UPDATEs.  Updates are always of the
form "UPDATE table SET some-fields WHERE id = constant", where id
is a unique identifier field with a btree index.  (Some of the tables
use OID for this, some have an ID field generated off a SEQUENCE
object.)  We never delete any tuples.  We use BEGIN TRANSACTION / LOCK /
END TRANSACTION to protect groups of related updates.

What we're seeing is that under load, the tables occasionally get
corrupted by duplication of tuples.  It's clearly a backend bug, because
the duplicates are duplicate right down to the OID.  There's no way that
a client could request creation of two tuples with the same OID, right?

I speculate that the backend is updating a tuple (or rearranging a page
to update another one) and neglecting to mark the old copy dead.
I don't know whether having multiple backends running in parallel is
necessary to cause the bug, but it seems likely --- if this kind of
thing happened with only one backend, surely it'd have been fixed long
ago.

The interesting thing is that both tuples are getting inserted into
the table's indexes, because the way we generally find out about it
is that a client fails with "Cannot insert a duplicate key into a unique
index" when it tries to UPDATE one of the duplicated tuples.  Since the
UPDATE is "... WHERE id = constant", presumably this indicates that both
tuples are getting found by the index search.  (But there are duplicate
tuples, not duplicate index records pointing at the same tuple, because
I see the duplicates if I just dump out the table with COPY.  Also,
once or twice I have seen near-duplicates in which the OID is the same
but one or two of the other fields disagree.  Possibly these were formed
at the instant of updating the original tuple to modify those fields,
with the original tuple not getting cleared?)

I rooted through the archives and found mention of this sort of thing
from last year sometime, as well as a current thread on pgsql-admin
that looks like it might be related.

Can anyone give me guidance on dealing with this?  Any chance that a
fix is already present in 6.4-beta?  I'm going to start out by trying
to develop a repeatable test case, but I don't really know enough about
the backend innards to debug it competently...

            regards, tom lane

> I've got a production application (currently running on 6.3.2 + Btree
> patch) in which several clients concurrently update the same set of
> tables.  The client requests are pretty simple --- SELECTs using
> btree-indexed fields, INSERTs, and UPDATEs.  Updates are always of the
> form "UPDATE table SET some-fields WHERE id = constant", where id
> is a unique identifier field with a btree index.  (Some of the tables
> use OID for this, some have an ID field generated off a SEQUENCE
> object.)  We never delete any tuples.  We use BEGIN TRANSACTION / LOCK /
> END TRANSACTION to protect groups of related updates.
>
> What we're seeing is that under load, the tables occasionally get
> corrupted by duplication of tuples.  It's clearly a backend bug, because
> the duplicates are duplicate right down to the OID.  There's no way that
> a client could request creation of two tuples with the same OID, right?
>
> I speculate that the backend is updating a tuple (or rearranging a page
> to update another one) and neglecting to mark the old copy dead.
> I don't know whether having multiple backends running in parallel is
> necessary to cause the bug, but it seems likely --- if this kind of
> thing happened with only one backend, surely it'd have been fixed long
> ago.
>
> The interesting thing is that both tuples are getting inserted into
> the table's indexes, because the way we generally find out about it
> is that a client fails with "Cannot insert a duplicate key into a unique
> index" when it tries to UPDATE one of the duplicated tuples.  Since the
> UPDATE is "... WHERE id = constant", presumably this indicates that both
> tuples are getting found by the index search.  (But there are duplicate
> tuples, not duplicate index records pointing at the same tuple, because
> I see the duplicates if I just dump out the table with COPY.  Also,
> once or twice I have seen near-duplicates in which the OID is the same
> but one or two of the other fields disagree.  Possibly these were formed
> at the instant of updating the original tuple to modify those fields,
> with the original tuple not getting cleared?)
>
> I rooted through the archives and found mention of this sort of thing
> from last year sometime, as well as a current thread on pgsql-admin
> that looks like it might be related.
>
> Can anyone give me guidance on dealing with this?  Any chance that a
> fix is already present in 6.4-beta?  I'm going to start out by trying
> to develop a repeatable test case, but I don't really know enough about
> the backend innards to debug it competently...

I fixed some very strange indexing code that could have been the cause.
It was one of those, "Gee, wonder why things are working with this
code."

It is part of 6.4, so it may already be fixed.  Also, the locking of
buffer pages in the pre-6.4 code was kind of messed up, so it is
possible locks were not being kept.  I believe to have fixed that too,
so running it though a test with 6.4 may show it is fixed.

--
  Bruce Momjian                        |  maillist@candle.pha.pa.us
  830 Blythe Avenue                    |  http://www.op.net/~candle
  Drexel Hill, Pennsylvania 19026      |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |

The good news: after much pain, I was able to develop a semi-repeatable
test for that duplicate-tuple problem.  (It's a script of about 6300 SQL
commands for five concurrent backends, which takes about 10 minutes to
run and produces a corrupted table about one run out of two...)  And
this test indicates that the current CVS sources don't have the bug.
So, rather than expending a lot of effort trying to figure out just
what the bug is in 6.3.2, we are going to cross our fingers and put our
production application on 6.4beta.

The bad news: this same script exposes a different bug in the current
sources (and perhaps older releases too).  *Very* rarely, like less
than one run out of ten, the test driver gets wedged or fails with an
"out of memory" error.  I just traced this to its cause, and the cause
is that a SELECT reply coming from the backend is corrupt.  In fact,
what I see in libpq's input buffer is that a "NOTIFY" message has been
inserted into the middle of the tuple data :-(.  So the interlock that
supposedly prevents Async_NotifyFrontEnd() from being invoked during
another command does not work reliably.

I will look into this, but I could use advice from anyone who
understands how that stuff is supposed to work.

            regards, tom lane

> The good news: after much pain, I was able to develop a semi-repeatable
> test for that duplicate-tuple problem.  (It's a script of about 6300 SQL
> commands for five concurrent backends, which takes about 10 minutes to
> run and produces a corrupted table about one run out of two...)  And
> this test indicates that the current CVS sources don't have the bug.
> So, rather than expending a lot of effort trying to figure out just
> what the bug is in 6.3.2, we are going to cross our fingers and put our
> production application on 6.4beta.

That's good news.

> The bad news: this same script exposes a different bug in the current
> sources (and perhaps older releases too).  *Very* rarely, like less
> than one run out of ten, the test driver gets wedged or fails with an
> "out of memory" error.  I just traced this to its cause, and the cause
> is that a SELECT reply coming from the backend is corrupt.  In fact,
> what I see in libpq's input buffer is that a "NOTIFY" message has been
> inserted into the middle of the tuple data :-(.  So the interlock that
> supposedly prevents Async_NotifyFrontEnd() from being invoked during
> another command does not work reliably.
>
> I will look into this, but I could use advice from anyone who
> understands how that stuff is supposed to work.

Tom, I can't think of anyone who understands it better than you.

However, if you find something in the backend and need help, let me
know.

I will be on vacation from Sunday until Wednesday.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026