Обсуждение: ERROR: heap_mark4update: (am)invalid tid in triggers

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();
--------------------------------------------

Kevin Hendrickson <hndrcksn@groupinfo.com> writes:
> 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 think your search wasn't very thorough --- does this look like your
problem?
http://fts.postgresql.org/db/mw/msg.html?mid=1082183

The contents of the trigger don't matter too much to that bug, only
whether the calling query is such that it might try to update the same
row multiple times.

            regards, tom lane

On Thursday, April 3, 2003, at 05:41  PM, Tom Lane wrote:

> Kevin Hendrickson <hndrcksn@groupinfo.com> writes:
>> 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 think your search wasn't very thorough --- does this look like your
> problem?
> http://fts.postgresql.org/db/mw/msg.html?mid=1082183
>
> The contents of the trigger don't matter too much to that bug, only
> whether the calling query is such that it might try to update the same
> row multiple times.

You mean Google isn't omniscient? :-) I searched via Google and the
Postgresql archives mailing list website. I started searching before
3/26 when your post was made. But I even searched yesterday and got
other posts about heap_mark4update, but missed yours. Anyway thanks for
the link. This seems to explain what is going on in my case.

I switched my test trigger from a BEFORE action to an AFTER action and
it works. So I tried it on the real trigger and no more invalid tid
error. So what is the fix for situations where your table really needs
a BEFORE action trigger? Is there a patch for Postgresql 7.3.2 that
addresses this issue yet?

BTW. Thank you for your endeavor to track down this issue. It was
driving me nuts for quite some time.

Kevin Hendrickson

Kevin Hendrickson <hndrcksn@groupinfo.com> writes:
> So what is the fix for situations where your table really needs
> a BEFORE action trigger? Is there a patch for Postgresql 7.3.2 that
> addresses this issue yet?

It is patched in CVS; here is the log entry:

2003-03-27 09:33  tgl

    * src/: backend/commands/trigger.c, backend/executor/execMain.c,
    include/commands/trigger.h (REL7_3_STABLE): GetTupleForTrigger must
    use outer transaction's command counter for time qual checking, not
    GetCurrentCommandId.  Per test case from Steve Wolfe.

We are starting to think about a 7.3.3 release, but in the meantime you
could pull the tip of the REL7_3_STABLE branch from our CVS server and
use that.  Or just get the diffs for the above three files and patch
manually.

            regards, tom lane