Thread: 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=
=20
7.3.1?) (I need it usally each quater of a year).=20

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

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

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

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=20
cure the problem.
What does prevent the error ist simplifiing  the query until there is no lo=
nger=20
any FROM p_kvk.=20
For example, the following simple Update will work:

UPDATE p_behbl SET fallnr =3D 99999 WHERE
p_behbl.fallnr is null;=20

But the following simplification will produce the error:

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

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

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

Regards=20
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