Re: attempted to lock invisible tuple - PG 8.4.1

On Tue, Oct 6, 2009 at 8:28 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Stuart Bishop wrote:
>>
>>
>> On Mon, Oct 5, 2009 at 11:00 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>> >Stuart Bishop wrote:
>> >>On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys
>> >><dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
>> >
>> >>> A similar issue was discussed just recently here:
>> >>> http://archives.postgresql.org/pgsql-general/2009-09/msg01219.php
>> >>>
>> >>> That issue involved cursors though (and a serializable isolation level, but
>> >>> you have that). Do you have any triggers that use cursors on the table that
>> >>> the update fails for?
>> >>
>> >>There is a trigger on that table, and it is certainly the culprit as
>> >>can be seen here (different table, same trigger):
>> >
>> >I don't think the committed patch touches anything involved in what
>> >you're testing, but if you could grab CVS tip from the 8.4 branch (or
>> >the snapshot from ftp.postgresql.org:/pub/snapshot/stable/8.4 ) and give
>> >it a try, that'd be great.
>>
>> I trigger the same error with a freshly built snapshot.
>
> mmkay.  So, any luck in constructing a test case?

Yes. Just no luck getting it sent to the mailing list - seems to
silently drop emails with attachments on me :-P

I've managed to get a self contained test case assembled. I'm not
sure what to make of this. The test case builds a small database from
a dump (one table), and triggers the 'invisible tuple' error. If I
touch the table though, such as ALTER TABLE or just updating some data
in in, the problem disappears.

$ sh invisible.sh
[...]
BEGIN
SET
ERROR: attempted to lock invisible tuple
ROLLBACK
BEGIN
UPDATE 1
COMMIT
BEGIN
SET
UPDATE 1
ROLLBACK

The test case (invisible.sh) and required dump (foodump.sql - 60k) are
at http://www.stuartbishop.net/invisible/

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/