Обсуждение: 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
Re: [HACKERS] NOTIFY interlock broken (was Yipes, I'm getting bit by duplicate tuples)
От
Bruce Momjian
Дата:
> 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