Thread: 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