
Based on Tom's comments and this email, I am adding this to the TODO
list:

* Add new pg_proc cachable settings to specify whether function can be
  evaluated only once or once per query


---------------------------------------------------------------------------

> > Someone reported to me that they can't get their queries to use indexes.
> > It turns out this is because timestamp() has pg_proc.proiscachable set
> > to false in many cases.  Date() also has this in some cases.
> 
> Please let me add a reference to this email from Tom Lane:
> 
> 	http://fts.postgresql.org/db/mw/msg.html?mid=1041918
> 
> It specifically states:
> 	
> 	[More complete] reasonable [cachable] definitions would be:
> 	
> 	1. noncachable: must be called every time; not guaranteed to return same
> 	result for same parameters even within a query.  random(), timeofday(),
> 	nextval() are examples.
> 	
> 	2. fully cachable: function guarantees same result for same parameters
> 	no matter when invoked.  This setting allows a call with constant
> 	parameters to be constant-folded on sight.
> 	
> 	3. query cachable: function guarantees same result for same parameters
> 	within a single query, or more precisely within a single
> 	CommandCounterIncrement interval.  This corresponds to the actual
> 	behavior of functions that execute SELECTs, and it's sufficiently strong
> 	to allow the function result to be used in an indexscan, which is what
> 	we really care about.
> 
> Item #2 clearly mentions constant folding, I assume by the optimizer. 
> What has me confused is why constant folding is needed to perform index
> lookups.  Can't the executor call the function and then do the index
> lookup?  Is this just a failing in our executor?  Is there a reason
> #1-type noncachable functions can't use indexes?  Is the timezone
> related here?
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 26 Dec 2001 18:41:21 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Timestamp conversion can't use index