Skip site navigation (1) Skip section navigation (2)

Re: BUG #5611: SQL Function STABLE promoting to VOLATILE

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brian Ceccarelli <bceccarelli(at)net32(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #5611: SQL Function STABLE promoting to VOLATILE
Date: 2010-08-11 20:34:19
Message-ID: 11354.1281558859@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Brian Ceccarelli <bceccarelli(at)net32(dot)com> writes:
> OK.   The documentation says "allows the optimizer to optimize . . . ."    But then the example guarantees the one-time-only for a index scan condition.   

No, the documentation states that *if* an index scan is used, functions
involved in the indexscan's qual condition will be evaluated just once,
rather than once per row.  There is no "guarantee" of any sort that such
a plan will be chosen.  The point of the STABLE marking is to inform the
optimizer that it is safe to choose an index scan because the function's
results will not change compared to the naive SQL semantics wherein the
WHERE condition is evaluated for each row.  Thus, the guarantee actually
runs the other way: you are promising the optimizer that your function
doesn't have side effects or change its results intra-query.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Brian CeccarelliDate: 2010-08-11 20:47:50
Subject: Re: BUG #5611: SQL Function STABLE promoting to VOLATILE
Previous:From: Robert HaasDate: 2010-08-11 20:17:00
Subject: Re: BUG #5611: SQL Function STABLE promoting to VOLATILE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group