On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxx> http://www.stuartbishop.net/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
