ERROR: heap_mark4update: (am)invalid tid, after the 14th update in 7.3.2
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
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