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````!'
M!@```````````0```# ``0```# `" ```$5.0T]$24Y'``@```!%3D-/1$E.
M1P`>````4T54(&-L:65N=%]E;F-O9&EN9R ]("=55$8X)SL*```````!`0``
M``$!`````0$````````````%````9F%L<V4!`0````,```````````!(!@``
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,@`'````-S$T-#@Y-P`'````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````$!
M``````@```!P;W-T9W)E<P`%````9F%L<V4!`0````,```````````!*!@``
M````````! ```#$R-C(`!P```#<Q-#0X.3<`!P```$1%34]?,#$`!P```$-/
M34U%3E0`:P```$-/34U%3E0@3TX@1$%404)!4T4@(D1%34]?,#$B($E3("=4
M:&4@9&%T86)A<V4@8V]N=&%I;G,@96YT:71I97,@=&\@8W)E871E('-C96YA
M<FEO(&9O<B!C;VYC=7)E;G0@86-C97-S)SL*```````!`0````$!`````0$`
M````" ```'!O<W1G<F5S``4```!F86QS90`$````,38P.0$!`````P``````
M``````$````````````$````,C8Q-0`'````-SDX,S<W, `&````<'5B;&EC
M``8```!30TA%34$`%@```$-214%412!30TA%34$@<'5B;&EC.PH`% ```$12
M3U @4T-(14U!('!U8FQI8SL*`0$````!`0````$!``````@```!P;W-T9W)E
M<P`%````9F%L<V4!`0````,````````````,`0``````````! ```#(V,3(`
M!0```#$V,S@V``<```!P;'!G<W%L`!,```!04D]#14154D%,($Q!3D=504=%
M`"0```!#4D5!5$4@4%)/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,S@V-P`(````061D5&]!*"D`" ``
M`$953D-424].`.T```!#4D5!5$4@xxxxxxx)3TX@(D%D9%1O02(H*2!21515
M4DY3(&EN=&5G97(*(" @($%3("0D#0I"14=)3@T*"41%3$5412!&4D]-(")!
M(CL-"@E)3E-%4E0@24Y43R B02(@5D%,5453("@Q*3L-"@E204E312!.3U1)
M0T4@)S(@+2 E)RP@*%-%3$5#5"!%6$E35%,H(%-%3$5#5"!T<G5E($923TT@
M(D$B(%=(15)%(")#;VQ?,2(@/2 Q("D@*3L-"@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``,````R-C@``0```#$!`0````,`````````
M```4````````````! ```#$R-34`!P```#<Y.#,X-S(`" ```%1%4U1?,"@I
M``@```!&54Y#5$E/3@!>`0``0U)%051%($953D-424].(")415-47S B*"D@
M4D5455).4R!I;G1E9V5R"B @("!!4R D) T*0D5'24X-"@E204E312!.3U1)
M0T4@)RTM+2TM+2TM+2TM+2T@5$535%\Q)SL-"@E204E312!.3U1)0T4@)S$@
M+2 E)RP@*%-%3$5#5"!%6$E35%,H(%-%3$5#5"!T<G5E($923TT@(D$B(%=(
M15)%(")#;VQ?,2(@/2 Q("D@*3L-"@E)3E-%4E0@24Y43R B02(@5D%,5453
M("@Q*3L-"@E204E312!.3U1)0T4@)S,@+2 E)RP@*%-%3$5#5"!%6$E35%,H
M(%-%3$5#5"!T<G5E($923TT@(D$B(%=(15)%(")#;VQ?,2(@/2 Q("D@*3L-
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``,````R-C@``0```#$!`0````,````````````5````````````! ```#$R
M-34`!P```#<Y.#,X-C@`" ```%1%4U1?,2@I``@```!&54Y#5$E/3@!Q`0``
M0U)%051%($953D-424].(")415-47S$B*"D@4D5455).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@)S$@+2 E)RP@*%-%3$5#5"!%6$E3
M5%,H(%-%3$5#5"!T<G5E($923TT@(D$B(%=(15)%(")#;VQ?,2(@/2 Q("D@
M*3L-"@E$14Q%5$4@1E)/32 B02([#0H)24Y315)4($E.5$\@(D$B(%9!3%5%
M4R H,2D[#0H)4D%)4T4@3D]424-%("<S("T@)2<L("A314Q%0U0@15A)4U13
M*"!314Q%0U0@=')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(B@I
M.PH!`0`````&````<'5B;&EC`0$`````" ```'!O<W1G<F5S``4```!F86QS
M90`#````,C8X``$````Q`0$````#````````````%@````````````0````Q
M,C4U``<````W.3@S.#8Y``@```!415-47S(H*0`(````1E5.0U1)3TX`:@$`
M`$-214%412!&54Y#5$E/3B B5$535%\R(B@I(%)%5%523E,@:6YT96=E<@H@
M(" @05,@)"0-"D)%1TE.#0H)4D%)4T4@3D]424-%("<M+2TM+2TM+2TM+2TM
M(%1%4U1?,B<[#0H)4D%)4T4@3D]424-%("<Q("T@)2<L("A314Q%0U0@15A)
M4U13*"!314Q%0U0@=')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('1R=64@1E)/32 B02(@5TA%4D4@(D-O;%\Q(B ](#$@*2 I
M.PT*#0H)4D5455).(# [#0I%3D0[#0HD) H@(" @3$%.1U5!1T4@<&QP9W-Q
M;#L*`"$```!$4D]0($953D-424].('!U8FQI8RXB5$535%\R(B@xxxx!`0``
M```&````<'5B;&EC`0$`````" ```'!O<W1G<F5S``4```!F86QS90`!````
M,0`#````,C8X`0$````#````````````%P````````````0````Q,C4U``<`
M```W.3@S.#<P``@```!415-47S,H*0`(````1E5.0U1)3TX`3P$``$-214%4
M12!&54Y#5$E/3B B5$535%\S(B@I(%)%5%523E,@:6YT96=E<@H@(" @05,@
M)"0-"D)%1TE.#0H)4D%)4T4@3D]424-%("<M+2TM+2TM+2TM+2TM(%1%4U1?
M,R<[#0H)4D%)4T4@3D]424-%("<Q("T@)2<L("A314Q%0U0@15A)4U13*"!3
M14Q%0U0@=')U92!&4D]-(")!(B!72$5212 B0V]L7S$B(#T@,2 I("D[#0H)
M4$521D]232 B061D5&]!(B@xxxx*"5)!25-%($Y/5$E#12 G,R M("4G+" H
M4T5,14-4($5825-44R@@4T5,14-4('1R=64@1E)/32 B02(@5TA%4D4@(D-O
M;%\Q(B ](#$@*2 I.PT*#0H)4D5455).(# [#0I%3D0[#0HD) H@(" @3$%.
M1U5!1T4@<&QP9W-Q;#L*`"$```!$4D]0($953D-424].('!U8FQI8RXB5$53
M5%\S(B@xxxx!`0`````&````<'5B;&EC`0$`````" ```'!O<W1G<F5S``4`
M``!F86QS90`!````,0`#````,C8X`0$````#````````````& ``````````
M``0````Q,C4U``<````W.3@S.#<Q``@```!415-47S0H*0`(````1E5.0U1)
M3TX`5@$``$-214%412!&54Y#5$E/3B B5$535%\T(B@I(%)%5%523E,@:6YT
M96=E<@H@(" @05,@)"0-"D)%1TE.#0H)4D%)4T4@3D]424-%("<M+2TM+2TM
M+2TM+2TM(%1%4U1?-"<[#0H)4D%)4T4@3D]424-%("<Q("T@)2<L("A314Q%
M0U0@15A)4U13*"!314Q%0U0@=')U92!&4D]-(")!(B!72$5212 B0V]L7S$B
M(#T@,2 I("D[#0H)4$521D]232 B061D5&]!(B@xxxx*"5)!25-%($Y/5$E#
M12 G,R M("4G+" H4T5,14-4($5825-44R@@4T5,14-4('1R=64@1E)/32 B
M02(@5TA%4D4@(D-O;%\Q(B ](#$@*2 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?-"(H*3L*`0$`````!@```'!U8FQI8P$!
M``````@```!P;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$%"3$4@1$%400``````````````'P```$-/
M4%D@(D$B("@B0V]L7S$B*2!&4D]-('-T9&EN.PH`!@```'!U8FQI8P$!````
M``@```!P;W-T9W)E<P`%````9F%L<V4`! ```#$R-S,!`0````$`````````
M``!%!@``````````! ```#(V,#8`!P```#<Y.#,W-S0`!@```$%?<&ME>0`*
M````0T].4U1204E.5 !(````04Q415(@5$%"3$4@3TY,62 B02(*(" @($%$
M1"!#3TY35%)!24Y4(")!7W!K97DB(%!224U!4ED@2T59("@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!&!@````T```!XG(O1X^+B`@`"Q0"I````````
`
end
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
