Обсуждение: hidden errors calling a volatile function inside a stable function

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@1E5.0U1)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@I.PH!`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@I.PT*"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@I.PH!`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@I.PT*"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

On Thu, Nov 20, 2008 at 6:21 AM, Sabin Coanda
<sabin.coanda@deuromedia.ro> wrote:
> 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

Why did you lie when your declared your function stable? :-)

merlin

Merlin Moncure wrote:
> On Thu, Nov 20, 2008 at 6:21 AM, Sabin Coanda
> <sabin.coanda@deuromedia.ro> wrote:
>> 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
>
> Why did you lie when your declared your function stable? :-)

I suspect the point here is that PostgreSQL tries to stop you violating
your claims about the function's stability by preventing DML operations.
However, it doesn't also attempt to prevent invocation of volatile
functions. When you annotate a function as STABLE you are making a
guarantee to PostgreSQL that the function is stable, rather than telling
PostgreSQL to force the function to behave as STABLE.

This is probably reasonable enough - after all, a volatile function
could even be invoked via an ON SELECT ... DO INSTEAD rule that
transforms a statement, so the function doesn't even know about it. It
could also be invoked dynamically via EXECUTE, which would probably be
tricky to trap. There are more issues than DML when it comes to STABLE
functions, too.

I do wonder if making STABLE functions execute as if `SET
transaction_read_only = 1' had been issued is possible/practical. Would
that work, and would it provide a way to trap at least most unsafe
operations without undue overhead?

--
Craig Ringer

Craig Ringer <craig@postnewspapers.com.au> writes:
> Merlin Moncure wrote:
>> On Thu, Nov 20, 2008 at 6:21 AM, Sabin Coanda
>> <sabin.coanda@deuromedia.ro> wrote:
>>> 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
>>
>> Why did you lie when your declared your function stable? :-)

> I suspect the point here is that PostgreSQL tries to stop you violating
> your claims about the function's stability by preventing DML operations.
> However, it doesn't also attempt to prevent invocation of volatile
> functions.

All this behavior is documented; see
http://www.postgresql.org/docs/8.3/static/xfunc-volatility.html
particularly the NOTE at the bottom.

            regards, tom lane