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