Re: External search engine, advice

Tom Lane wrote:
>
> mlw <markw(at)mohawksoft(dot)com> writes:
> >> Hmm ... given that ftss refers to external files, is it a good idea to
> >> mark it cachable?
>
> > This I don't understand. What is the lifetime of a value that "iscacheable?"
>
> Forever. cachable says it's OK to reduce "func(constant)" to "constant"
> on sight. Right now it's not really forever because we don't save query
> plans for very long (unless they're inside a plpgsql function) ... but
> if you have a function that depends on any outside data besides its
> arguments, you'd be ill-advised to mark it cachable.

That's scary!!!

I can sort of see why you'd want that, but can you also see why a developer
would not want that?

Take this query:

select * from table where field = function(...);

Without the "iscacheable" flag, this function will force a table scan, but
although the returned value may change over time, it would not change for this
particular transaction.

Some functions need to be called each time they are evaluated.
Some functions need to be called only once per transaction.

Do you really see any need for a function's result to have a lifetime beyond
its transaction? I see a real danger in preserving the value of a function
across a transaction. Granted, things like "create index fubar_ndx on fubar
(function(field));" depend on this behavior, but other applications will have
problems.

How do we get a cached value of a function that exists for a transaction, such
that we can use indexes, and how do we identify the functions who's results
should have a longer lifetime?

Am I out in left field here? Does anyone see this as a problem? I guess there
should be three states to the lifetime of a functions return value?