Bug #510: conditional rules sometimes work more than once

Zoltan Kovacs (kovacsz@pc10.radnoti-szeged.sulinet.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
seemsto 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
databasethe 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 díszléc 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 Ház árnyékoló   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       Jég akku - szürke kupakkal (2000-es fejlesztés) 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       Jég akku - szürke kupakkal (2000-es fejlesztés) 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       Jég akku - szürke kupakkal (2000-es fejlesztés) 4       \N      \N
2       25.0000 0.0000  \N
1001    0       1       f       1       1836    100719  1       \N      5.0000  \N      4
100719  Kis Sándor 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 Sándor 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 Sándor Dugó 4       \N      \N      2       12.0000 25.0000 \N





No file was uploaded with this report