ERROR: heap_mark4update: (am)invalid tid, after the 14th update in 7.3.2

From: Christoph_Becker(at)t-online(dot)de (Christoph_Becker)
To: pgsql-bugs(at)postgresql(dot)org
Subject: ERROR: heap_mark4update: (am)invalid tid, after the 14th update in 7.3.2
Date: 2003-04-08 07:39:50
Message-ID: 192nhu-0bTbGaC@fwd00.sul.t-online.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Bugreport

The following Update-query did work fine until January (pre 7.3.2, or even
7.3.1?) (I need it usally each quater of a year).

UPDATE p_behbl SET fallnr = p_kvk.lfdnr FROM p_kvk WHERE
trim(p_behbl.abgerechnet) = 'n' and trim(p_behbl.ltyp) = 'KCH' and
p_behbl.fallnr is null and p_behbl.patnr = p_kvk.patnr and
p_behbl.datum >= p_kvk.startdatum and
p_behbl.datum <= p_kvk.enddatum;

Now, with PostgreSQL 7.3.2 it fails with the following message (The warnigs are
ok, they are produced by an on-update-trigger. But they may be interesting
because the show that the Update takes place for 14 rows before the error
occurs):

WARNING: Admin postgres changed p_behbl.lfdnr 95815
WARNING: Admin postgres changed p_behbl.lfdnr 92039
WARNING: Admin postgres changed p_behbl.lfdnr 97370
WARNING: Admin postgres changed p_behbl.lfdnr 96157
WARNING: Admin postgres changed p_behbl.lfdnr 97210
WARNING: Admin postgres changed p_behbl.lfdnr 97197
WARNING: Admin postgres changed p_behbl.lfdnr 97207
WARNING: Admin postgres changed p_behbl.lfdnr 97204
WARNING: Admin postgres changed p_behbl.lfdnr 97198
WARNING: Admin postgres changed p_behbl.lfdnr 97196
WARNING: Admin postgres changed p_behbl.lfdnr 96651
WARNING: Admin postgres changed p_behbl.lfdnr 96650
WARNING: Admin postgres changed p_behbl.lfdnr 96162
WARNING: Admin postgres changed p_behbl.lfdnr 96161
ERROR: heap_mark4update: (am)invalid tid
[local]:px:postgres:#

The transaction stopps after having updated 14 rows (from 1187).
The transaction is then rolled back, so no changes occur.

The update-trigger was introduced reacently. But dropping the trigger does not
cure the problem.
What does prevent the error ist simplifiing the query until there is no longer
any FROM p_kvk.
For example, the following simple Update will work:

UPDATE p_behbl SET fallnr = 99999 WHERE
p_behbl.fallnr is null;

But the following simplification will produce the error:

UPDATE p_behbl SET fallnr = p_kvk.lfdnr FROM p_kvk WHERE
p_behbl.fallnr is null and p_behbl.patnr = p_kvk.patnr;

More facts which may help:
The updated table 'p_behbl' as well as the referenced table 'p_kvk' have a
primary key called 'lfdnr'.
Both tables are further linked by a foreign key 'patnr' to a mastertable which
has a column 'patnr' as its primary key.

The on-update-trigger checks from which group the user is, to install more
sofisticated accessrights. If the user belongs to group 'admin' the trigger only
issues a Warning and allows the update.

Regards
Christoph Becker

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2003-04-08 09:09:05 Bug #938: Wrong UPDATE if exist INNER JOIN and alias for table
Previous Message Kang Wonseok) <wskang@world.kaist.ac.kr> 2003-04-08 07:00:41 When to sort texts data encoded in UTF-8, retrieve as non-ordered data.