Re: functions marked STABLE not allowed to do INSERT

The previous discussion/complaints really revolved around how volatility 
effected the planner. There are some scenarios (most revolving around a 
surrogate key lookup type scenario) where 99% of function calls do not 
generate DML changes and because of that we need the planner to treat these 
functions as stable functions rather than volatile functions (and we're aware 
of the tradeoffs of the other 1% case, but willing to take the hit).  At the 
time the check was instituted inside plpgsql, istr some of us saying that we 
needed a 4th volatility that meant "treat my as stable for purposes of the 
planner, but treat me as volatile for other purposes" but the proposals never 
gathered much steam. 

Robert Treat

On Monday 14 November 2005 15:09, Jim C. Nasby wrote:
> ISTM that instead of comming up with clever ways to fool the parser it
> would be better to allow users to force a function to be marked as
> STABLE, etc., even though it's contents indicate that it shouldn't be.
> Since the standard IMMUTABLE | STABLE | VOLATILE is obviously a bad
> choice, I suggest adding [FORCE] as an option, so you could do FORCE
> STABLE.
>
> On Mon, Nov 14, 2005 at 08:55:03PM +0100, Tino Wildenhain wrote:
> > > 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)
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >        choose an index scan if your joining column's datatypes do not
> >        match

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL