Re: attempted to lock invisible tuple - PG 8.4.1

On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys
<dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
> On 5 Oct 2009, at 8:58, Stuart Bishop wrote:
>
>> I'm running our products test suite against PostgreSQL 8.4.1. The test
>> suite runs fine against 8.3.7.
>>
>> With 8.4.1, some of our tests are failing with the exception
>> 'attempted to lock invisible tuple'. The failures are repeatable -
>> they crash every time at the same point. They crash no matter if they
>> are being run in isolation or as part of the larger test suite.
>>
>> Anyone know what we could be doing that triggers that? Looking at our
>> statement logs we don't seem to be doing anything unusual. The failing
>> tests I've checked are running under SERIALIZABLE isolation level, and
>> the database will have been recreated a few instants ago using
>> 'createdb --template test_template_db'.
>
> 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):

launchpad_dev=# begin;
BEGIN
launchpad_dev=# set transaction_isolation to serializable;
SET
launchpad_dev=# update bug set name='foooz' where id=1;
ERROR: attempted to lock invisible tuple
launchpad_dev=# abort;
ROLLBACK
launchpad_dev=# alter table bug disable trigger tsvectorupdate;
ALTER TABLE
launchpad_dev=# begin;
BEGIN
launchpad_dev=# set transaction_isolation to serializable;
SET
launchpad_dev=# update bug set name='foooz' where id=1;
UPDATE 1
launchpad_dev=# abort;
ROLLBACK

I haven't had luck reducing this to a test case though. I'll give it
another shot tomorrow. Here are some more details for the audience:

launchpad_dev=# \d bug
Table "public.bug"
Column | Type |
Modifiers
------------------------+-----------------------------+------------------------------------------------------------------------------------
id | integer | not null
default nextval('bug_id_seq'::regclass)
datecreated | timestamp without time zone | not null
default timezone('UTC'::text, ('now'::text)::timestamp(6) with time
zone)
name | text |
title | text | not null
description | text | not null
owner | integer | not null
duplicateof | integer |
fti | ts2.tsvector |
private | boolean | not null default false
security_related | boolean | not null default false
date_last_updated | timestamp without time zone | not null
default timezone('UTC'::text, now())
date_made_private | timestamp without time zone |
who_made_private | integer |
date_last_message | timestamp without time zone |
number_of_duplicates | integer | not null default 0
message_count | integer | not null default 0
users_affected_count | integer | default 0
users_unaffected_count | integer | default 0
hotness | integer | not null default 0
Indexes:
"bug_pkey" PRIMARY KEY, btree (id)
"bug_name_key" UNIQUE, btree (name)
"bug__date_last_message__idx" btree (date_last_message)
"bug__date_last_updated__idx" btree (date_last_updated) CLUSTER
"bug__datecreated__idx" btree (datecreated)
"bug__hotness__idx" btree (hotness)
"bug__users_affected_count__idx" btree (users_affected_count)
"bug__users_unaffected_count__idx" btree (users_unaffected_count)
"bug__who_made_private__idx" btree (who_made_private) WHERE
who_made_private IS NOT NULL
"bug_duplicateof_idx" btree (duplicateof)
"bug_fti" gist (fti)
"bug_owner_idx" btree (owner)
Check constraints:
"notduplicateofself" CHECK (NOT id = duplicateof)
"sane_description" CHECK (ltrim(description) <> ''::text AND
char_length(description) <= 50000)
"valid_bug_name" CHECK (valid_bug_name(name))
Foreign-key constraints:
"bug__who_made_private__fk" FOREIGN KEY (who_made_private)
REFERENCES person(id)
"bug_duplicateof_fk" FOREIGN KEY (duplicateof) REFERENCES bug(id)
"bug_owner_fk" FOREIGN KEY (owner) REFERENCES person(id)
Referenced by:
TABLE "bugactivity" CONSTRAINT "$1" FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "bugsubscription" CONSTRAINT "$2" FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "bug" CONSTRAINT "bug_duplicateof_fk" FOREIGN KEY
(duplicateof) REFERENCES bug(id)
TABLE "bugaffectsperson" CONSTRAINT "bugaffectsperson_bug_fkey"
FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "bugattachment" CONSTRAINT "bugattachment_bug_fk" FOREIGN
KEY (bug) REFERENCES bug(id)
TABLE "bugbranch" CONSTRAINT "bugbranch_bug_fkey" FOREIGN KEY
(bug) REFERENCES bug(id)
TABLE "bugcve" CONSTRAINT "bugcve_bug_fk" FOREIGN KEY (bug)
REFERENCES bug(id)
TABLE "bugmessage" CONSTRAINT "bugmessage__bug__fk" FOREIGN KEY
(bug) REFERENCES bug(id)
TABLE "bugnomination" CONSTRAINT "bugnomination__bug__fk" FOREIGN
KEY (bug) REFERENCES bug(id)
TABLE "bugnotification" CONSTRAINT "bugnotification_bug_fkey"
FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "bugnotificationarchive" CONSTRAINT
"bugnotificationarchive__bug__fk" FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "bugpackageinfestation" CONSTRAINT
"bugpackageinfestation_bug_fk" FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "bugproductinfestation" CONSTRAINT
"bugproductinfestation_bug_fk" FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "bugtask" CONSTRAINT "bugtask__bug__fk" FOREIGN KEY (bug)
REFERENCES bug(id)
TABLE "bugwatch" CONSTRAINT "bugwatch_bug_fk" FOREIGN KEY (bug)
REFERENCES bug(id)
TABLE "hwsubmissionbug" CONSTRAINT "hwsubmissionbug_bug_fkey"
FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "mentoringoffer" CONSTRAINT "mentoringoffer_bug_fkey"
FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "questionbug" CONSTRAINT "questionbug__bug__fk" FOREIGN KEY
(bug) REFERENCES bug(id)
TABLE "specificationbug" CONSTRAINT "specificationbug_bug_fk"
FOREIGN KEY (bug) REFERENCES bug(id)
Triggers:
set_bug_number_of_duplicates_t AFTER INSERT OR DELETE OR UPDATE ON
bug FOR EACH ROW EXECUTE PROCEDURE set_bug_number_of_duplicates()
Disabled triggers:
tsvectorupdate BEFORE INSERT OR UPDATE ON bug FOR EACH ROW EXECUTE
PROCEDURE ts2.ftiupdate('name', 'a', 'title', 'b', 'description', 'd')

launchpad_dev=# \df+ ts2.ftiupdate
List of functions
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------
Schema | ts2
Name | ftiupdate
Result data type | trigger
Argument data types |
Type | trigger
Volatility | volatile
Owner | stub
Language | plpythonu
Source code |
: new = TD["new"]
: args = TD["args"][:]
:
: # Short circuit if none of the
relevant columns have been
: # modified and fti is not being set
to NULL (setting the fti
: # column to NULL is thus how we can
force a rebuild of the fti
: # column).
: if TD["event"] == "UPDATE" and
new["fti"] != None:
: old = TD["old"]
: relevant_modification = False
: for column_name in args[::2]:
: if new[column_name] !=
old[column_name]:
: relevant_modification = True
: break
: if not relevant_modification:
: return "OK"
:
: # Generate an SQL statement that
turns the requested
: # column values into a weighted tsvector
: sql = []
: for i in range(0, len(args), 2):
: sql.append(
:
"ts2.setweight(ts2.to_tsvector('default', coalesce("
: "substring(ltrim($%d)
from 1 for 2500),'')),"
: "CAST($%d AS \"char\"))"
% (i + 1, i + 2))
: args[i] = new[args[i]]
:
: sql = "SELECT %s AS fti" % "||".join(sql)
:
: # Execute and store in the fti column
: plan = plpy.prepare(sql, ["text",
"char"] * (len(args)/2))
: new["fti"] = plpy.execute(plan,
args, 1)[0]["fti"]
:
: # Tell PostgreSQL we have modified the data
: return "MODIFY"
:
Description | Trigger function that keeps the fti tsvector
column up to date.

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