Hi,
I have "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)" on Windows OS , but I experienced the same problem on
"PostgreSQL 8.3.5, compiled by Visual C++ build 1400" too.
I attach the demo database here, to be available to test according with the
following scenario.
I found in a STABLE function, for instance "TEST_0"(), it is not allowed to
use INSERT statement. Trying this will give me the error:
ERROR: INSERT is not allowed in a non-volatile function
Same behavior is for DELETE statement (e.g. "TEST_1"()).
If I set the function to VOLATILE (as "TEST_2"() ), it works very well.
I replace DELETE and INSERT statements with a volatile function call, in
"TEST_3"(), and I call it. It works well too.
Finally I set the function as STABLE, not VOLATILE (see "TEST_4"()).
First I call:
DELETE FROM "A";
Then another call:
SELECT "TEST_4"();
I get no rosen errors, but the results are wrong (see the log results),
because "TEST_4"() doesn't see the changes made by the called function.
I find this behaviour it is very dangerous because it is completely hidden.
What do you say ?
TIA,
Sabin
begin 666 081120_DEMO_01.backup
M4$=$35 !"@`$" $!`0`````9`````!(`````#0`````4``````H`````; ``
M``````````<```!$14U/7S Q``4````X+C(N- `%````."XR+C0`#P````!'
[EMAIL PROTECTED] ``0```# `" ```$5.0T]$24Y'[EMAIL PROTECTED]/1$E.
M1P`>````4T54(&-L:65N=%]E;F-O9&EN9R ]("=55$8X)SL*```````!`0``
M``$!`````0$````````````%````9F%L<V4!`0````,```````````!([EMAIL PROTECTED]
M`````````0```# ``0```# `"@```%-41%-44DE.1U,`"@```%-41%-44DE.
M1U,`* ```%-%5"!S=&%N9&%R9%]C;VYF;W)M:6YG7W-T<FEN9W,@/2 G;VXG
M.PH```````$!`````0$````!`0````````````4```!F86QS90$!`````P``
M`````````$D&```````````$````,3(V,@`'[EMAIL PROTECTED]'````1$5-
M3U\P,0`(````1$%404)!4T4`1P```$-214%412!$051!0D%312 B1$5-3U\P
M,2(@5TE42"!414U03$%412 ]('1E;7!L871E,"!%3D-/1$E.1R ]("=55$8X
M)SL*`!D```!$4D]0($1!5$%"05-%(")$14U/7S Q(CL*`0$````!`0````$!
[EMAIL PROTECTED];W-T9W)E<P`%````9F%L<V4!`0````,[EMAIL PROTECTED]
M````````! ```#$R-C(`!P```#<Q-#0X.3<`!P```$1%34]?,#$`!P```$-/
M34U%3E0`:P```$-/[EMAIL PROTECTED]@1$%404)!4T4@(D1%34]?,#$B($E3("=4
M:&[EMAIL PROTECTED]&%T86)A<[EMAIL PROTECTED]&%I;G,@96YT:71I97,@=&[EMAIL
PROTECTED])E871E('-C96YA
M<FEO(&9O<B!C;VYC=7)E;[EMAIL PROTECTED])SL*```````!`0````$!`````0$`
M````" ```'!O<W1G<F5S``4```!F86QS90`$````,38P.0$!`````P``````
M``````$````````````$````,C8Q-0`'````-SDX,S<W, `&````<'5B;&EC
[EMAIL PROTECTED]@<'5B;&EC.PH`% ```$12
M3U @4T-(14U!('[EMAIL PROTECTED];W-T9W)E
M<P`%````9F%L<V4!`0````,````````````,`0``````````! ```#(V,3(`
M!0```#$V,[EMAIL PROTECTED]<```!P;'!G<W%L`!,```!04D]#14154D%,($Q!3D=504=%
M`"[EMAIL PROTECTED])/0T5$55)!3"!,04Y'54%'12!P;'!G<W%L.PH`(@``
M`$123U @4%)/0T5$55)!3"!,04Y'54%'12!P;'!G<W%L.PH!`0````$!````
M`0$`````" ```'!O<W1G<F5S``4```!F86QS90$!`````P```````````!,`
M```````````$````,3(U-0`'````-SDX,[EMAIL PROTECTED](````061D5&]!*"D`" ``
[EMAIL PROTECTED])3TX@(D%D9%1O02(H*2!21515
M4DY3(&EN=&5G97(*(" @($%3("0D#0I"14=)[EMAIL PROTECTED]"41%3$5412!&4D]-(")!
M(CL-"@E)[EMAIL PROTECTED] B02(@5D%,5453("@Q*3L-"@E204E312!.3U1)
M0T4@)S(@+2 E)[EMAIL PROTECTED]"!%6$E35%,H(%-%3$5#5"!T<G5E($923TT@
M(D$B(%=(15)%(")#;VQ?,2(@/2 Q("[EMAIL PROTECTED]"@E215154DX@,#L-"D5.1#L-
M"B0D"B @("!,04Y'54%'12!P;'!G<W%L.PH`(0```$123U @1E5.0U1)3TX@
M<'5B;&EC+B)!9&14;T$B*"D["@$!``````8```!P=6)L:6,!`0`````(````
M<&]S=&=R97,`!0```&9A;'-E``,[EMAIL PROTECTED],`````````
M```4````````````! ```#$R-34`!P```#<Y.#,X-S(`" ```%1%4U1?,"@I
[EMAIL PROTECTED]&54Y#5$E/[EMAIL PROTECTED]>`0``0U)%051%($953D-424].(")415-47S
B*"D@
M4D5455).4R!I;G1E9V5R"B @("!!4R D) T*0D5'24X-"@E204E312!.3U1)
M0T4@)[EMAIL PROTECTED])SL-"@E204E312!.3U1)0T4@)S$@
M+2 E)[EMAIL PROTECTED]"!%6$E35%,H(%-%3$5#5"!T<G5E($923TT@(D$B(%=(
M15)%(")#;VQ?,2(@/2 Q("[EMAIL PROTECTED]"@E)[EMAIL PROTECTED] B02(@5D%,5453
M("@Q*3L-"@E204E312!.3U1)0T4@)S,@+2 E)[EMAIL PROTECTED]"!%6$E35%,H
M(%-%3$5#5"!T<G5E($923TT@(D$B(%=(15)%(")#;VQ?,2(@/2 Q("[EMAIL PROTECTED]
M"@T*"5)%5%523B P.PT*14Y$.PT*)"0*(" @($Q!3D=504=%('!L<&=S<6P@
M4U1!0DQ%.PH`(0```$123U @1E5.0U1)3TX@<'5B;&EC+B)415-47S B*"D[
M"@$!``````8```!P=6)L:6,!`0`````(````<&]S=&=R97,`!0```&9A;'-E
M``,[EMAIL PROTECTED],````````````5````````````! ```#$R
M-34`!P```#<Y.#,[EMAIL PROTECTED]" ```%1%4U1?,[EMAIL
PROTECTED]@```!&54Y#5$E/[EMAIL PROTECTED]
M0U)%051%($953D-424].(")415-47S$B*"[EMAIL PROTECTED]).4R!I;G1E9V5R"B @
M("!!4R D) T*0D5'24X-"@E204E312!.3U1)0T4@)RTM+2TM+2TM+2TM+2T@
M5$535%\Q)SL-"@E204E312!.3U1)0T4@)[EMAIL PROTECTED] E)[EMAIL PROTECTED]"!%6$E3
M5%,H(%-%3$5#5"!T<G5E($923TT@(D$B(%=(15)%(")#;VQ?,2(@/2 Q("D@
M*3L-"@[EMAIL PROTECTED])/32 B02([#0H)24Y315)4($E.5$\@(D$B(%9!3%5%
M4R H,2D[#0H)4D%)[EMAIL PROTECTED]("<S("T@)2<L("[EMAIL PROTECTED])4U13
M*"[EMAIL PROTECTED]')U92!&4D]-(")!(B!72$5212 B0V]L7S$B(#T@,2 I("D[
M#0H-"@E215154DX@,#L-"D5.1#L-"B0D"B @("!,04Y'54%'12!P;'!G<W%L
M(%-404),13L*`"$```!$4D]0($953D-424].('!U8FQI8RXB5$535%\Q([EMAIL PROTECTED]
M.PH!`0`````&````<'5B;&EC`0$`````" ```'!O<W1G<F5S``4```!F86QS
M90`#````,[EMAIL PROTECTED]
M,C4U``<[EMAIL PROTECTED]@```!415-47S(H*0`(````1E5.0U1)3TX`:@$`
M`$-214%412!&54Y#5$E/3B B5$535%\R([EMAIL PROTECTED](%)%5%523E,@:6YT96=E<@H@
M(" @05,@)"0-"D)%1TE.#0H)4D%)[EMAIL PROTECTED]("<M+2TM+2TM+2TM+2TM
M(%1%4U1?,B<[#0H)4D%)[EMAIL PROTECTED]("<Q("T@)2<L("[EMAIL PROTECTED])
M4U13*"[EMAIL PROTECTED]')U92!&4D]-(")!(B!72$5212 B0V]L7S$B(#T@,2 I
M("D[#0H)1$5,151%($923TT@(D$B.PT*"4E.4T525"!)3E1/(")!(B!604Q5
M15,@*#$I.PT*"5)!25-%($Y/5$E#12 G,R M("4G+" H4T5,14-4($5825-4
M4R@@4T5,14-4('[EMAIL PROTECTED])/32 B02(@5TA%4D4@(D-O;%\Q(B ]([EMAIL
PROTECTED] I
M.PT*#0H)4D5455).(# [#0I%3D0[#0HD) H@(" @3$%.1U5!1T4@<&QP9W-Q
M;#L*`"$```!$4D]0($953D-424].('!U8FQI8RXB5$535%\R([EMAIL PROTECTED]
M```&````<'5B;&EC`0$`````" ```'!O<W1G<F5S``4```!F86QS90`!````
M,0`#````,C8X`0$````#````````````%P````````````0````Q,C4U``<`
[EMAIL PROTECTED]<[EMAIL PROTECTED],H*0`(````1E5.0U1)3TX`3P$``$-214%4
M12!&54Y#5$E/3B B5$535%\S([EMAIL PROTECTED](%)%5%523E,@:6YT96=E<@H@(" @05,@
M)"0-"D)%1TE.#0H)4D%)[EMAIL PROTECTED]("<M+2TM+2TM+2TM+2TM(%1%4U1?
M,R<[#0H)4D%)[EMAIL PROTECTED]("<Q("T@)2<L("[EMAIL PROTECTED])4U13*"!3
[EMAIL PROTECTED]')U92!&4D]-(")!(B!72$5212 B0V]L7S$B(#T@,2 I("D[#0H)
M4$521D]232 B061D5&]!([EMAIL PROTECTED]"5)!25-%($Y/5$E#12 G,R M("4G+" H
M4T5,14-4($5825-44R@@4T5,14-4('[EMAIL PROTECTED])/32 B02(@5TA%4D4@(D-O
M;%\Q(B ]([EMAIL PROTECTED] I.PT*#0H)4D5455).(# [#0I%3D0[#0HD) H@(" @3$%.
M1U5!1T4@<&QP9W-Q;#L*`"$```!$4D]0($953D-424].('!U8FQI8RXB5$53
M5%\S([EMAIL PROTECTED]&````<'5B;&EC`0$`````" ```'!O<W1G<F5S``4`
M``!F86QS90`!````,0`#````,C8X`0$````#````````````& ``````````
M``0````Q,C4U``<[EMAIL PROTECTED]<[EMAIL PROTECTED](````1E5.0U1)
[EMAIL PROTECTED]&54Y#5$E/3B B5$535%\T([EMAIL PROTECTED](%)%5%523E,@:6YT
M96=E<@H@(" @05,@)"0-"D)%1TE.#0H)4D%)[EMAIL PROTECTED]("<M+2TM+2TM
M+2TM+2TM(%1%4U1?-"<[#0H)4D%)[EMAIL PROTECTED]("<Q("T@)2<L("A314Q%
[EMAIL PROTECTED])4U13*"[EMAIL PROTECTED]')U92!&4D]-(")!(B!72$5212 B0V]L7S$B
M(#T@,2 I("D[#0H)4$521D]232 B061D5&]!([EMAIL PROTECTED]"5)!25-%($Y/5$E#
M12 G,R M("4G+" H4T5,14-4($5825-44R@@4T5,14-4('[EMAIL PROTECTED])/32 B
M02(@5TA%4D4@(D-O;%\Q(B ]([EMAIL PROTECTED] I.PT*#0H)4D5455).(# [#0I%3D0[
M#0HD) H@(" @3$%.1U5!1T4@<&QP9W-Q;"!35$%"3$4["@`A````1%)/4"!&
M54Y#5$E/3B!P=6)L:6,N(E1%4U1?-"([EMAIL PROTECTED]'!U8FQI8P$!
[EMAIL PROTECTED];W-T9W)E<P`%````9F%L<V4``P```#(V. `!````,0$!````
M`P```````````/D$```````````$````,3(U.0`'````-SDX,S<W,0`!````
M00`%````5$%"3$4`,P```$-214%412!404),12 B02(@* H@(" @(D-O;%\Q
M(B!I;G1E9V5R($Y/5"!.54Q,"BD["@`7````1%)/4"!404),12!P=6)L:6,N
M(D$B.PH!`0`````&````<'5B;&EC````````" ```'!O<W1G<F5S``4```!F
M86QS90`!````,0$!`````P```````````$8&`````0`````!````, `'````
M-SDX,S<W,0`!````00`*````5$%"[EMAIL PROTECTED]'P```$-/
M4%D@(D$B("@B0V]L7S$B*2!&4D]-('-T9&[EMAIL PROTECTED]'!U8FQI8P$!````
[EMAIL PROTECTED];W-T9W)E<P`%````9F%L<V4`! ```#$R-S,!`0````$`````````
[EMAIL PROTECTED] ```#(V,#8`!P```#<Y.#,[EMAIL PROTECTED]<&ME>0`*
M````0T].4U1204E.5 !(````04Q415(@5$%"[EMAIL PROTECTED],62 B02(*(" @($%$
M1"!#3TY35%)!24Y4(")!7W!K97DB([EMAIL PROTECTED]("@B0V]L7S$B*3L*
M`#8```!!3%1%4B!404),12!/3DQ9('!U8FQI8RXB02(@1%)/4"!#3TY35%)!
M24Y4(")!7W!K97DB.PH!`0`````&````<'5B;&EC````````" ```'!O<W1G
M<F5S``4```!F86QS90`$````,3(W,P`$````,3(W,P$!`````P``````````
=`0!&[EMAIL PROTECTED]([EMAIL PROTECTED]"Q0"I````````
`
end
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
