Re: attempted to lock invisible tuple - PG 8.4.1 - Mailing list pgsql-general

On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys
<dalroi@solfertje.student.utwente.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@stuartbishop.net>
http://www.stuartbishop.net/