Bug #510: conditional rules sometimes work more than once - Mailing list pgsql-bugs
| From | pgsql-bugs@postgresql.org |
|---|---|
| Subject | Bug #510: conditional rules sometimes work more than once |
| Date | |
| Msg-id | 200111061418.fA6EIHf50407@postgresql.org Whole thread Raw |
| Responses |
Re: Bug #510: conditional rules sometimes work more than once
|
| List | pgsql-bugs |
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
pgsql-bugs by date: