Bug #510: conditional rules sometimes work more than once

Zoltan Kovacs (kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)hu) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
conditional rules sometimes work more than once

Long Description
Using 7.1.3, I've got a problem with conditional rules. This is the same problem which occured also in 7.1.1, but it seems to be indeterministic (sometimes works, sometimes not).

My definitions are attached in defs.pgsql. It requires t1.out and t2.out (contents of two tables written out with COPY statements). I tried to load defs.pgsql into a clean database and the conditional rule worked well. But in my production database the same UPDATE causes a strange thing: the RULE calls the function as many times as many rows the view contains.

Consider the following UPDATE:

update szamla_tetele_eddigi set mennyiseg=5 where szamla=1009 and tetelszam=1;

It should give only one line of DEBUG:

DEBUG: 1009/1

In a clean database I got the correct result. But in my production database I got:

DEBUG: 1/11
DEBUG: 1/3
DEBUG: 1/5
DEBUG: 1/6
DEBUG: 1/9
DEBUG: 1/1
DEBUG: 1/4
DEBUG: 1/2
DEBUG: 1/10
DEBUG: 1/12
DEBUG: 1/7
DEBUG: 1/8
DEBUG: 1001/2
DEBUG: 1001/3
DEBUG: 1006/1
DEBUG: 1006/2
DEBUG: 1007/1
DEBUG: 1007/2
DEBUG: 1007/3
DEBUG: 1007/4
DEBUG: 1001/1
..............

Is my view definition too complex for the PostgreSQL server?

Sample Code
defs.pgsql
----------

CREATE FUNCTION plpgsql_call_handler ( ) RETURNS opaque AS '/usr/local/pgsql-7.1.3/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

CREATE FUNCTION szalllev_szamla_szamla_kapcs(int4,int4) RETURNS bool AS '
begin
raise debug ''%/%'',$1,$2;
return ''f'';
end;
' LANGUAGE 'PLPGSQL';

CREATE TABLE szamla_modositasa (
szamla int4 not null,
sorszam int4 check (sorszam >= 0) default 0,
primary key (szamla, sorszam),
kelt date check ((not kelt is null) or (not lezarva)),
beerkezett date,
kezdemenyezo int4 not null,
leiras text,
lezarva bool default 'f',
lezaras_idopontja timestamp,
lezarta int4,
megnyito int4,
maxszam int4,
maxlezartszam int4,
maxlezarva bool default 'f'
);

CREATE TABLE szamla_tetele (
szamla int4 not null,
modositas int4 not null check (modositas >= 0),
foreign key (szamla,modositas) references
szamla_modositasa(szamla,sorszam) on delete cascade,
tetelszam int4 not null,
archiv bool default 'f',
primary key (szamla, tetelszam, modositas, archiv),
sorrend int4 not null,
kulso_cikk int4
check (not kulso_cikk is null or (fajta != 4 and fajta != 90 and fajta != 100)),
cikk int4
check (not cikk is null or (fajta != 4 and fajta != 90 and fajta != 100)),
minoseg int4
check (not minoseg is null or (fajta != 4 and fajta != 90 and fajta != 100)) DEFAULT 1,
szolgaltatas int4,
mennyiseg numeric(14,4) not null,
mettol int4,
fajta int4,
azonosito varchar,
megnevezes varchar,
mennyisegi_egyseg int4 default 4,
megjegyzes varchar,
ajanlat int4,
hibastatusz int4 not null default 0,
netto_egysegar numeric(14,4) not null,
afa_szazalek numeric(14,4),
besorolasi_szam varchar);

create view szamla_tetele_eddigi as select
mt.szamla, mt.tetelszam, mt.sorrend, mt.kulso_cikk, mt.cikk,
mt.mennyiseg, mt.minoseg, mm.sorszam as modositas,
mt.fajta, mt.azonosito, mt.megnevezes, mt.mennyisegi_egyseg, mt.hibastatusz,
mt.netto_egysegar, mt.afa_szazalek, mt.besorolasi_szam,
mt.megjegyzes, mt.ajanlat,

mt.mennyiseg*mt.netto_egysegar as netto_ertek,
mt.afa_szazalek*mt.mennyiseg*mt.netto_egysegar/100 as afa_osszege,
mt.mennyiseg*mt.netto_egysegar*(1+mt.afa_szazalek/100) as osszesen

from szamla_tetele as mt, szamla_modositasa as mm
where (mm.sorszam < mettol and mm.sorszam >= modositas and archiv)

or (mm.sorszam >= modositas and not archiv)
and mm.szamla = mt.szamla
group by mt.szamla, tetelszam, sorrend, kulso_cikk, cikk, mennyiseg, minoseg, sorszam, fajta, azonosito, megnevezes, mennyisegi_egyseg, hibastatusz,
netto_egysegar, afa_szazalek, besorolasi_szam, megjegyzes, ajanlat;


CREATE RULE szmte_update0 AS ON UPDATE TO szamla_tetele_eddigi
DO INSTEAD NOTHING;

CREATE RULE szmte_update AS ON UPDATE TO szamla_tetele_eddigi
WHERE not szalllev_szamla_szamla_kapcs(new.szamla,new.tetelszam)

DO INSTEAD
update szamla_tetele set
szamla = new.szamla, modositas = new.modositas,
tetelszam = new.tetelszam, sorrend = new.sorrend, kulso_cikk = new.kulso_cikk,
cikk = new.cikk, minoseg = new.minoseg, mennyiseg = new.mennyiseg,
fajta = new.fajta, azonosito = new.azonosito, megnevezes = new.megnevezes, mennyisegi_egyseg = new.mennyisegi_egyseg,
hibastatusz = new.hibastatusz, netto_egysegar = new.netto_egysegar,
afa_szazalek = new.afa_szazalek, besorolasi_szam = new.besorolasi_szam,
megjegyzes = new.megjegyzes, ajanlat = new.ajanlat
where szamla = old.szamla and
tetelszam = old.tetelszam and not archiv;

COPY szamla_modositasa FROM 't1.out';
COPY szamla_tetele FROM 't2.out';

t1.out
------

1 0 2001-09-20 2001-09-20 1029 \N f \N \N 1045 \N f
2 0 2001-09-20 2001-09-20 1029 \N f \N \N 1045 \N f
1001 0 2001-09-25 2001-09-25 1108 \N f \N \N 1045 \N f
1004 0 2001-09-25 2001-09-25 1108 \N f \N \N \N \N f
1005 0 2001-09-25 2001-09-25 1108 \N f \N \N \N \N f
1007 0 2001-09-25 2001-09-25 1004 \N f \N \N \N \N f
1008 0 2001-11-06 2001-11-06 1066 \N f \N \N 1045 \N f
1009 0 2001-11-06 2001-11-06 1066 \N f \N \N 1045 \N f
1003 0 2001-09-25 2001-09-25 1108 \N f \N \N 1045 \N f
1006 0 2001-09-25 2001-09-25 1004 \N t 2001-09-25 15:16:22+021045 1045 1 0 f
1006 1 2001-11-06 \N 1001 <res> f \N \N 1045 1 f

t2.out
------
1 0 11 f 3 \N \N \N \N 2.0000 \N 6
123434 froccs (nagy) 6 \N \N 0 1001.0000 0.0000 \N
1 0 3 f 8 \N \N \N \N 22.0000 \N 6
11111111 \N \N \N \N 0 12.0000 0.0000 \N
1 0 5 f 7 \N \N \N \N 122.0000 \N
6 123434 froccs (nagy) 6 \N \N 0 1001.0000 0.0000 \N
1 0 6 f 6 \N \N \N \N 1223.0000 \N
6 123434 froccs (nagy) 6 \N \N 0 1001.0000 0.0000 \N
1 0 9 f 5 \N \N \N \N 12.0000 \N 6
234312 froccs (kicsi) \N \N \N 0 23.0000 0.0000 \N
1 0 1 f 8 2066 101110 1 \N 33.0000 \N 4
1202744 Styron 678 E 4 \N \N 2 23.0000 0.0000 \N
1 0 4 f 8 \N \N \N \N 12.0000 \N 6
123434 froccs (nagy) \N \N \N 0 122.0000 0.0000 \N
1 0 2 f 8 \N \N \N \N 233.0000 \N
6 21 \N \N \N \N 0 33.0000 0.0000 \N
1 0 10 f 9 \N \N \N \N 2.0000 \N 6
123434 froccs (nagy) 6 \N \N 0 2.0000 0.0000 \N
1 0 12 f 4 1625 100602 1 \N 1233.0000 \N
4 1221714 Huzalpolc dszlc ZLKF 301 4 \N \N 2 34344.0000
0.0000 \N
1 0 7 f 1 \N \N \N \N 2334.0000 \N
6 123434 froccs (nagy) 6 \N \N 0 1001.0000 0.0000 \N
1 0 8 f 2 \N \N \N \N 122.0000 \N
6 234312 froccs (kicsi) \N \N \N 0 22.0000 0.0000 \N
1001 0 2 f 1 2363 101431 1 \N 340.0000 \N
4 068.9930.152.00 Winkel 4 \N \N 2 12.0000 23.0000 \N
1001 0 3 f 1 1961 101073 1 \N 4500.0000 \N
4 068.9580.498.00 Hz rnykol 4 \N \N 2 230.0000 23.0000
\N
1006 0 1 f 1 2066 101110 1 \N 200.0000 \N
4 1202744 Styron 678 E 4 \N \N 2 33.0000 0.0000 \N
1006 0 2 f 1 2089 101151 1 \N 210.0000 \N
4 211226601 Jg akku - szrke kupakkal (2000-es fejleszts) 4 \N \N
2 25.0000 0.0000 \N
1007 0 1 f 1 2066 101110 1 \N 200.0000 \N
4 1202744 Styron 678 E 4 \N \N 2 33.0000 0.0000 \N
1007 0 2 f 1 2089 101151 1 \N 210.0000 \N
4 211226601 Jg akku - szrke kupakkal (2000-es fejleszts) 4 \N \N
2 25.0000 0.0000 \N
1007 0 3 f 1 2066 101110 1 \N -200.0000 \N
4 1202744 Styron 678 E 4 \N \N 2 33.0000 0.0000 \N
1007 0 4 f 1 2089 101151 1 \N -210.0000 \N
4 211226601 Jg akku - szrke kupakkal (2000-es fejleszts) 4 \N \N
2 25.0000 0.0000 \N
1001 0 1 f 1 1836 100719 1 \N 5.0000 \N 4
100719 Kis Sndor Dug 4 \N \N 2 345.0000 12.0000 \N
1008 0 1 f 1 1836 100719 1 \N 0.0000 \N 4
100719 Kis Sndor Dug 4 \N \N 2 12.0000 0.0000 \N
1009 0 1 f 1 1836 100719 1 \N 0.0000 \N 4
100719 Kis Sndor Dug 4 \N \N 2 12.0000 25.0000 \N

No file was uploaded with this report