ERROR: heap_mark4update: (am)invalid tid in triggers - Mailing list pgsql-general

From Kevin Hendrickson
Subject ERROR: heap_mark4update: (am)invalid tid in triggers
Date
Msg-id Pine.LNX.4.44.0304031540280.6826-100000@iceborg.groupinfo.com
Whole thread Raw
Responses Re: ERROR: heap_mark4update: (am)invalid tid in triggers  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
As the title says I'm getting this heap_mark4update error when I execute
code inside a trigger. I have search the archives, but haven't found an
answer.

I'm porting a database from SQL Server 7 to PostgreSQL 7.3.2. There are
plenty of triggers, primary keys, foreign keys and check constraints. The
problems I've encountered occur under PostgreSQL 7.3.2 on both MacOS X
10.2.4 and RedHat Linux 8.0. All triggers are row level (because that's
what Postgresql supports) and are executed BEFORE the action.

I have a pl/pgsql function which updates table A. Inside it's trigger,
table A updates table B. Both tables A and B have foreign key constraints,
but not to each other. The updates on tables A and B use the FROM list so
that other tables can be used in the WHERE clause on UPDATE.  My
understanding is that the FROM list is non-standard ANSI, but both SQL
Server and Postgresql seem to support it - I don't know if this is a
factor. The tables are small (less than 1000 rows).  The problem seems to
be in the triggers. For a while I've been getting the "invalid tid" error
in the update trigger for table B. At first I thought it was because the
query in table A's trigger was too complex, but even a very simple update
on table B fails.  What is more, if I update table B directly from psql,
the trigger fails if I do anything other than RAISE NOTICE before RETURN
NEW. Occasionally for some unknown reason the update will succeed without
me changing anything. I never encountered this problem prior to Postgresql
7.3 with other tables, but this particular portion of the database wasn't
tested in 7.2, so I don't know if this is a factor or not.

So I whittled down the trigger on table B to just RETURN NEW. I dropped
the trigger function (w/ cascade) and installed the new trigger. Whenever
I update table B, I get success.

Next I added a trivial variable (myVal INT) to the whittled down trigger
and assigned it a random value before the RETURN NEW statement and
replaced the trigger on table B.  The first time I update table B (whether
from a test function or directly from psql) I get the invalid tid error.
But every update after that succeeds everytime.

What gives?  So how does one debug an invalid tid error? I've tried
turning on debugging, but there is too much information to sift through
when you don't exactly know what you are looking for.

Kevin
PS. The simplified trigger is written below:

-----------------------------------
CREATE OR REPLACE FUNCTION B_Trg ()
RETURNS TRIGGER
AS '

DECLARE
    myVal    INTEGER;

BEGIN
    myVal := 21;
    RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER B_U_I BEFORE UPDATE OR INSERT
ON TableB
FOR EACH ROW
EXECUTE PROCEDURE B_Trg();
--------------------------------------------


pgsql-general by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Postgres Syslog
Next
From: Bruno Wolff III
Date:
Subject: Re: [PERFORM] [HACKERS] OSS database needed for testing