Re: functions marked STABLE not allowed to do INSERT

Am Montag, den 14.11.2005, 14:45 -0500 schrieb Jaime Casanova:
> On 11/14/05, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> > Am Montag, den 14.11.2005, 13:29 -0500 schrieb Robert Treat:
> > > On Monday 14 November 2005 10:02, Tino Wildenhain wrote:
> > > > New in 8.1 it seems functions marked STABLE are
> > > > not allowed to have any INSERT statement in them.
> > > >
> > >
> > > Try hiding your inserts in seperate volitle sql function that you can select
> > > inside your stable function. I think the planner won't be smart enough to
> > > realize what your doing to it.
> >
> >
> > Now this is really a bug:
> >
> > =# CREATE OR REPLACE function foo(int) RETURNS int as $$
> > $# DECLARE f ALIAS FOR $1;
> > $# BEGIN
> > $# RETURN (random()*f)::int;
> > $# END;
> > $# $$ LANGUAGE plpgsql STABLE;
> >
> > =# SELECT foo(10);
> > foo
> > -----
> > 6
> > (1 row)
> >
> > Instead of screaming here, where I use a VOLATILE
> > function in my STABLE function which could really
> > be dangerous, it just works.
> >
>
> stable functions must show an stable image of the database, but if you
> start to do insertions, deletions and so how stable the image is?

No, the definiton is:
STABLE indicates that within a single table scan the function will
consistently return the same result for the same argument values, but
that its result could change across SQL statements.

And I'm not speaking of delete. My common usecase is
lookup of key in surrogate-key table and generating
one if not found. If it would break on DELETE
I'd understand it, but it breaks on INSERT which isnt
acceptable imho.

> now, i don't like the behaviour of letting call volatile functions
> inside immutable/stable ones... but some people use it to do what they
> think is good...

Now, we are forcing people to not use INSERT in a STABLE
function but we happily allow them to use VOLATILE
functions where the real danger lives. Doesnt sound
very logical to me.

> if you know you can call volatile functions from stable ones maybe you
> asked enough or read enough to actually know what you are doing...

Thats the point. I know what I'm doing with my INSERT
but am not allowed, but if I didnt know what I do and
use a volatile function, I can happily do that.

> but if you simply put inserts in your stable functions and expect to
> work, maybe you are not reading enough... you can ask to yourself, am
> i reading enough to actually know what am i doing?

Yes I do.
>
> conclusion: think in it as a netsafe for novices, if you think you are
> expert enough take the net off (calling the volatile functions)

Yes sure, but since the change does not really prevent noobs
from doing bad things [tm], it should be reverted or at least
kept consequence - which would be to ban volatile
funtions too.

(IMHO only calling volatile functions should be banned)