Обсуждение: functions marked STABLE not allowed to do INSERT

New in 8.1 it seems functions marked STABLE are
not allowed to have any INSERT statement in them.

However in this particular case, the insert does not
violate the rule:

"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."

it does basically lookup a value by a foreign key
and builds a surrogate key on demand.

I know I could make it volatile but otoh I really want
the optimizer to optimize calls away as possible.

Now, what to do beside a private revert to the
patch?

Regards
Tino

On 11/14/05, Tino Wildenhain <tino@wildenhain.de> wrote:
> New in 8.1 it seems functions marked STABLE are
> not allowed to have any INSERT statement in them.
>

this is not new, always was said that SATBLE and IMMUTABLE functions
must not modify the database. But beginning with 8.0.0 these kind of
thing are checked at compile time.

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

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. 

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

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.

And the other example, where I do my insert on purpose
and fully knowing what I do gets refused.

Is this a shortcoming of the function compiler?
I dont think so - it retrieves the OID of used
functions anyway so the lookup on stableness
would be easy - and lets skip the silly scan
for INSERT instead.

Regards
Tino

On 11/14/05, Tino Wildenhain <tino@wildenhain.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?

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...

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...

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?


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

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Am Montag, den 14.11.2005, 14:45 -0500 schrieb Jaime Casanova:
> On 11/14/05, Tino Wildenhain <tino@wildenhain.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)

Tino Wildenhain <tino@wildenhain.de> writes:
> Now this is really a bug:

That's in the eye of the beholder (and one who wasn't paying attention
to previous discussion of this point, evidently).

The reason why the no-data-change rule is now enforced, not only
recommended, is that a stable/immutable function now actually would
not see any changes it did make.  Consider code like
INSERT INTO foo VALUES (42, ...);SELECT * INTO rec FROM foo WHERE key = 42;IF NOT FOUND THEN    RAISE EXCEPTION 'where
didmy row go?';
 

If this were allowed in stable/immutable functions, the RAISE would
in fact be reached in 8.1, because the SELECT will be done with the
snapshot of the query that called the function.  This is a feature,
not a bug, because it makes it possible to write a stable function
that selects from the database and be sure that it really is stable
in the face of concurrent changes.

Calling a volatile function that itself makes some database changes
isn't necessarily a wrong thing to do; the rule is just that the
calling stable function isn't going to see those changes, just as
the outer query won't (and never has).

In a larger sense, maybe we ought to forbid stable/immutable functions
calling volatiles, but it's not clear that there are no cases where it
makes sense.  As Robert notes, the lack of this check does provide an
"out" for people who want to do what you want to do.
        regards, tom lane

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
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Am Montag, den 14.11.2005, 15:06 -0500 schrieb Tom Lane:
> Tino Wildenhain <tino@wildenhain.de> writes:
> > Now this is really a bug:
> 
> That's in the eye of the beholder (and one who wasn't paying attention
> to previous discussion of this point, evidently).

Yes I was, but only to the fact it is not useable for 
caching and there are some cases (like random) 
for which STABLE would be bad thing [tm].

> The reason why the no-data-change rule is now enforced, not only
> recommended, is that a stable/immutable function now actually would
> not see any changes it did make.  Consider code like
> 
>     INSERT INTO foo VALUES (42, ...);
>     SELECT * INTO rec FROM foo WHERE key = 42;
>     IF NOT FOUND THEN
>         RAISE EXCEPTION 'where did my row go?';
> 
> If this were allowed in stable/immutable functions, the RAISE would
> in fact be reached in 8.1, because the SELECT will be done with the
> snapshot of the query that called the function.  This is a feature,

Ah this was the missing bit. I though this would only be true
for IMMUTABLE.

Thanks for the explanation. I'm not fine w/ it.

Regards
Tino

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

Robert Treat <xzilla@users.sourceforge.net> writes:
> 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. 

Probably because you never provided a convincing use-case.

As far as the planner is concerned, the only real differences between
stable and volatile functions are: 1. A stable function is safe to use in an indexscan qualification    (which implies
itwill be evaluated only once per scan, not once     per row, but *only* if the relevant index actually gets used). 2.
Stablefunctions are OK to evaluate speculatively when trying to    estimate WHERE-clause selectivities.
 

It's tough to believe that a function with side-effects is reasonable to
use in either of those ways (and no, "it only changes the database 1% of
the time" doesn't make it more reasonable).  In fact, I'd go so far as
to say that you're a fool if you use a function with side-effects in a
WHERE clause, ever --- but doubly so if you then want to claim to the
planner that it hasn't got any side-effects.

Now, the current discussion about stable functions really has to do with
semantics of SQL-command evaluation within the function itself, which is
only weakly related to what the planner thinks about it.  So it's not
a-prior impossible that we've overloaded the meaning of "stable" too
much and should split the concepts somehow.  But it's not clear to me
why or how, which is why I'm wanting a plausible use-case.
        regards, tom lane

On Monday 14 November 2005 18:36, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > 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.
>
> Probably because you never provided a convincing use-case.
>

It's hard to be convincing when you start out thinking the other side to be 
fools.

> As far as the planner is concerned, the only real differences between
> stable and volatile functions are:
>   1. A stable function is safe to use in an indexscan qualification
>      (which implies it will be evaluated only once per scan, not once
>       per row, but *only* if the relevant index actually gets used).
>   2. Stable functions are OK to evaluate speculatively when trying to
>      estimate WHERE-clause selectivities.
>
> It's tough to believe that a function with side-effects is reasonable to
> use in either of those ways (and no, "it only changes the database 1% of
> the time" doesn't make it more reasonable).  In fact, I'd go so far as
> to say that you're a fool if you use a function with side-effects in a
> WHERE clause, ever --- but doubly so if you then want to claim to the
> planner that it hasn't got any side-effects.
>

The basic scenario is one of a function that, given input, looks up 
corresponding information in a cache table.  If it can't find the 
information, it goes through a more complicated (and slower) search to obtain 
the information, inserts that information into the cache, and returns the 
result.  Note it always returns the same result whether the cache contains 
the information or not, which means you really do only need to evaluate it 
once per scan.  The problem is that when you mark such functions as volatile 
the performance you get is horrendous, so you're forced to mark them as 
stable so the planner will make use of index scans and such and give decent 
performance. Now maybe that's not a convincing use-case, but it is a common 
one.   

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

On Mon, Nov 14, 2005 at 08:31:50PM -0500, Robert Treat wrote:
> The basic scenario is one of a function that, given input, looks up
> corresponding information in a cache table.  If it can't find the
> information, it goes through a more complicated (and slower) search
> to obtain the information, inserts that information into the cache,
> and returns the result.  Note it always returns the same result
> whether the cache contains the information or not, which means you
> really do only need to evaluate it once per scan.  The problem is
> that when you mark such functions as volatile the performance you
> get is horrendous, so you're forced to mark them as stable so the
> planner will make use of index scans and such and give decent
> performance. Now maybe that's not a convincing use-case, but it is a
> common one.

Isn't this the sort of case that Tom just explained as not functioning
in 8.1, as the STABLE functions, and all functions called by the
STABLE functions will use the snapshot that is used at the time it
was called? As in, you do the INSERT, but within the same SELECT
statement invoking this 'STABLE' function, it never sees the inserted
cached value?

Also - what does it do with parallel inserts of the same cache values?
Three or four clients all require the data at the same time - they execute
the cache table lookup, to fail to find a row, they then all resolve the
query the slow way, and each try to insert a cache row.

The case seems problematic to me. Isn't it better served by a caching
daemon, such as memcached? It has similar problems - not transaction
safe, and so on, but I would suspect that this caching table that you
describe above cannot ever be truly transaction safe, unless you store
full row dependencies for each of the cache records, and validate
against the dependencies before returning any data. Who is to say the
cache data is up-to-date? Invalidation of the cache data rows may not
solve this either.

I'd say why bother?

Personally, I'm more in favour of PostgreSQL doing cheap caching of
query to results, making those very common slow queries you mention
faster where possible. For example, keeping the query results in a LRU
cache, with an identifier that would allow it to quickly determine if
all dependent tables have changed or not, allowing it to return the
results as is, if all of the tables are unchanged since the last
execution. To make it faster, and to minimize caching of less frequent
queries, perhaps the first few times a query is executed, it should
only remember the number of times it has been executed, and only after
some threshhold has passed, start to cache the results, and the
dependency information. If a query rarely keeps the same dependency
information, keep it in a cache of queries to never cache results or
dependency information for? I'm sure this has been talked about at
length, before I joined this mailing list.

Cheers,
mark

-- 
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada
 One ring to rule them all, one ring to find them, one ring to bring them all                      and in the darkness
bindthem...
 
                          http://mark.mielke.cc/

On Monday 14 November 2005 20:59, mark@mark.mielke.cc wrote:
> On Mon, Nov 14, 2005 at 08:31:50PM -0500, Robert Treat wrote:
> > The basic scenario is one of a function that, given input, looks up
> > corresponding information in a cache table.  If it can't find the
> > information, it goes through a more complicated (and slower) search
> > to obtain the information, inserts that information into the cache,
> > and returns the result.  Note it always returns the same result
> > whether the cache contains the information or not, which means you
> > really do only need to evaluate it once per scan.  The problem is
> > that when you mark such functions as volatile the performance you
> > get is horrendous, so you're forced to mark them as stable so the
> > planner will make use of index scans and such and give decent
> > performance. Now maybe that's not a convincing use-case, but it is a
> > common one.
>
> Isn't this the sort of case that Tom just explained as not functioning
> in 8.1, as the STABLE functions, and all functions called by the
> STABLE functions will use the snapshot that is used at the time it
> was called? As in, you do the INSERT, but within the same SELECT
> statement invoking this 'STABLE' function, it never sees the inserted
> cached value?
>

That's the whole point, it doesn't need to see the cached value as it has 
already done the look-up the expensive way.  But all subsequent queries will 
get the value from the cache table, thereby avoiding the expensive query.

> Also - what does it do with parallel inserts of the same cache values?
> Three or four clients all require the data at the same time - they execute
> the cache table lookup, to fail to find a row, they then all resolve the
> query the slow way, and each try to insert a cache row.
>
> The case seems problematic to me. Isn't it better served by a caching
> daemon, such as memcached? It has similar problems - not transaction
> safe, and so on, but I would suspect that this caching table that you
> describe above cannot ever be truly transaction safe, unless you store
> full row dependencies for each of the cache records, and validate
> against the dependencies before returning any data. Who is to say the
> cache data is up-to-date? Invalidation of the cache data rows may not
> solve this either.
>

These  are all business logic decsions and as such would be implementation 
dependent.  Generally the idea is that once the expensive query is done, it's 
value is unlikely to change. If this were something that would change a lot 
then it wouldn't exactly be non-volatle would it? 

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

On Mon, Nov 14, 2005 at 10:02:32PM -0500, Robert Treat wrote:
> > Isn't this the sort of case that Tom just explained as not functioning
> > in 8.1, as the STABLE functions, and all functions called by the
> > STABLE functions will use the snapshot that is used at the time it
> > was called? As in, you do the INSERT, but within the same SELECT
> > statement invoking this 'STABLE' function, it never sees the inserted
> > cached value?
> That's the whole point, it doesn't need to see the cached value as it has 
> already done the look-up the expensive way.  But all subsequent queries will 
> get the value from the cache table, thereby avoiding the expensive query.

Ok. I think I get it. But -- isn't the STABLE definition itself enough to
benefit the same query, without INSERT, assuming appropriate optimization
of STABLE?

The INSERT is only for caching across multiple statements, then,
correct? Or is it to get around a deficiency in the implementation of
STABLE?

> > [ application side caching? ]
> These  are all business logic decsions and as such would be implementation 
> dependent.  Generally the idea is that once the expensive query is done, it's 
> value is unlikely to change. If this were something that would change a lot 
> then it wouldn't exactly be non-volatle would it? 

I think that's the point. Whether the data changes or not in the table, isn't
restricted by the definition of the functions that access the data.

I believe I see your argument, and given a suitable definition of STABLE
(such as only table snapshots being used for the STABLE function, and all
functions invoked by the STABLE function), I can see INSERT being safe
(although perhaps difficult to understand).

I predict wierd scenarios, including a VOLATILE function that normally
expects to be able to update a table, and view the updates
immediately, failing in unexpected ways when called from a STABLE
function. Yuck. It really sounds like something is wrong. Or missing.

I'm scared of it.

Cheers,
mark

-- 
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada
 One ring to rule them all, one ring to find them, one ring to bring them all                      and in the darkness
bindthem...
 
                          http://mark.mielke.cc/

On Mon, Nov 14, 2005 at 10:30:48PM -0500, mark@mark.mielke.cc wrote:
> On Mon, Nov 14, 2005 at 10:02:32PM -0500, Robert Treat wrote:
> > > Isn't this the sort of case that Tom just explained as not functioning
> > > in 8.1, as the STABLE functions, and all functions called by the
> > > STABLE functions will use the snapshot that is used at the time it
> > > was called? As in, you do the INSERT, but within the same SELECT
> > > statement invoking this 'STABLE' function, it never sees the inserted
> > > cached value?
> > That's the whole point, it doesn't need to see the cached value as it has 
> > already done the look-up the expensive way.  But all subsequent queries will 
> > get the value from the cache table, thereby avoiding the expensive query.
> 
> Ok. I think I get it. But -- isn't the STABLE definition itself enough to
> benefit the same query, without INSERT, assuming appropriate optimization
> of STABLE?
> 
> The INSERT is only for caching across multiple statements, then,
> correct? Or is it to get around a deficiency in the implementation of
> STABLE?

FWIW, another use-case:
I've got some code that logs page hits. Being that it's dirt simple, it
just uses the incomming URL as a means for logging. I want that info to
be normalized, so part of logging involves looking up that url to see if
it already exists in the url table, and returning it's id. If it doesn't
already exist, the function creates it and then returns the ID.

> > > [ application side caching? ]
> > These  are all business logic decsions and as such would be implementation 
> > dependent.  Generally the idea is that once the expensive query is done, it's 
> > value is unlikely to change. If this were something that would change a lot 
> > then it wouldn't exactly be non-volatle would it? 
> 
> I think that's the point. Whether the data changes or not in the table, isn't
> restricted by the definition of the functions that access the data.
> 
> I believe I see your argument, and given a suitable definition of STABLE
> (such as only table snapshots being used for the STABLE function, and all
> functions invoked by the STABLE function), I can see INSERT being safe
> (although perhaps difficult to understand).
> 
> I predict wierd scenarios, including a VOLATILE function that normally
> expects to be able to update a table, and view the updates
> immediately, failing in unexpected ways when called from a STABLE
> function. Yuck. It really sounds like something is wrong. Or missing.
> 
> I'm scared of it.

ISTM that there might be need for another level of function stability
marking (which could possible be determined automatically). It's
certainly possible to construct a function that can modify data but will
always return the same results in a tablescan (current definition of
STABLE). If there's performance benefits to be had on functions that are
both STABLE (as per the old definition) and don't modify any data (or
contain any volatile functions?) then that should be a new level of
stability.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Mon, 2005-11-14 at 22:30, mark@mark.mielke.cc wrote:
> On Mon, Nov 14, 2005 at 10:02:32PM -0500, Robert Treat wrote:
> > > Isn't this the sort of case that Tom just explained as not functioning
> > > in 8.1, as the STABLE functions, and all functions called by the
> > > STABLE functions will use the snapshot that is used at the time it
> > > was called? As in, you do the INSERT, but within the same SELECT
> > > statement invoking this 'STABLE' function, it never sees the inserted
> > > cached value?
> > That's the whole point, it doesn't need to see the cached value as it has 
> > already done the look-up the expensive way.  But all subsequent queries will 
> > get the value from the cache table, thereby avoiding the expensive query.
> 
> Ok. I think I get it. But -- isn't the STABLE definition itself enough to
> benefit the same query, without INSERT, assuming appropriate optimization
> of STABLE?
> 
> The INSERT is only for caching across multiple statements, then,
> correct? Or is it to get around a deficiency in the implementation of
> STABLE?
> 

Correct... the idea is to cache across queries, not within a query... I
dug around to find some code; in the example below findparenttype is a
very expensive function.

DECLARE   intChild     ALIAS FOR $1;   intTheHost     INTEGER;   strStatus     TEXT;
BEGIN   SELECT host_id INTO intTheHost FROM findhost_cache WHERE entity_id =
intChild;   IF NOT FOUND THEN       SELECT findparenttype(intChild, 'H') INTO intTheHost;
       IF intTheHost IS NOT NULL THEN           strStatus := 'INSERT INTO findhost_cache VALUES (' ||
intChild || ',' || intTheHost || ')';           RAISE DEBUG 'FindParentCache Update : % ',strStatus;           EXECUTE
strStatus;       ELSE           RAISE NOTICE 'DATABASE WARNING : Unable To Find An
 
Associated Host For This Entity : %',$1;       END IF;
   END IF;RETURN intTheHost;

END;


<snip thoughts on ways to abuse the implementation>

> I'm scared of it.

Dude... I'm scared of your sig ;^D

> 
> Cheers,
> mark
> 
> -- 
> mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
> .  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
> |\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
> |  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada
> 
>   One ring to rule them all, one ring to find them, one ring to bring them all
>                        and in the darkness bind them...
> 
>                            http://mark.mielke.cc/
> 
> 


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