still on techniques to cache table slices was: optimiser STABLE vs. temp table

On Tue, 1 Apr 2008 18:32:25 +0200
Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:

> On Tue, Apr 01, 2008 at 06:06:35PM +0200, Ivan Sergio Borgonovo
> wrote:
> > Would you please be so kind to rephrase:
> >
> > http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html

> <snip>

> > I can't understand how it can call a function a single time and
> > avoid to cache the result.
> > Is it limited to a single statement?
>
> Yes, it's limited to a single statememnt. Let's say hypothetically
> postgres could optimise:

It be nice if it was a bit clearer in the docs.
Someone may guess it from the fact that between 2 statements there
could be something that change the result of the function... but well
on the other side people may hope the optimiser is smarter than what
it really is and still can call a function just when actually needed
and discern between:

select ... stablefunc()
select ... stablefunc()

and

select ... stablefunc()
insert somestuff
select ... stablefunc()

That would make caching stuff definitively easier...
IMMUTABLE is too much but extending the "cacheability" of functions a
little bit further with some other attribute would make things much
easier.

> Does this helps,

Definitively. thanks.

> Anything persistnat usually needs to be in a table. If it's a really
> small amount you could use the global namespace in pl/perl or
> similar in other languages.

It does look as it is "semi-persistent"... so temp tables may
actually do the trick.

Albe Laurenz's example was partially comforting. Could somebody point
me to some other technique or a more in depth discussion or whatever
that will help me to learn a bit more about this issue and available
techniques?

I'm wondering about the visibility of temp tables defined in a
function.
From my understanding pg behaviour is the one I'm looking for.
I could use the same temp table name across different sessions
without the trouble of clashes... but well this behaviour is not
standard and I'm worried it will bite me once pg will follow the
standard.

Using the same name would make possible to do something like:

if(table doesn't exist)
create table
end if
return rows

but this looks like it is going to be harder than expected since temp
table aren't easy to "find".
I'd have to resort to catching exceptions etc...

If I use Albe Laurenz's technique I'll have to do some bookkeeping to
store and pass the temp table name across functions.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it