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

Started by Nonameabout 23 years ago2 messagesbugs
Jump to latest
#1Noname
Christoph_Becker@t-online.de

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Noname (#1)
Re: ERROR: heap_mark4update: (am)invalid tid, after the 14th update in 7.3.2

Christoph_Becker@t-online.de (Christoph_Becker) writes:

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;

If there are multiple p_kvk rows matching some p_behbl row, then this
is a known problem. See the archives from a week or two back.

regards, tom lane