Re: functions marked STABLE not allowed to do INSERT

On Monday 14 November 2005 20:59, mark(at)mark(dot)mielke(dot)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