Re: [HACKERS] Cache query (PREPARE/EXECUTE)

On Tue, 22 Feb 2000, The Hermit Hacker wrote:

> On Tue, 22 Feb 2000, Karel Zak - Zakkr wrote:
>
> > The queryTree and planTree are save in hash table and in the
> > TopMemoryContext (Is it good space for this cache?). All is
> > without change-schema detection (IMHO is user problem if he
> > changes DB schema and use old cached plan). In future I try
>
> Just curious, but a new 'PREPARE name AS...' with the same name just
> overrides the previously saved plan?

Current code return you:

test=# prepare one as select * from aaa;
PREPARE
test=# prepare one as select * from aaa;
ERROR: Query plan with name 'one' already exist.
test=#

I prefer any DROP command instead overriding. But I open for any other
suggestions...

> Actually, can someone who may know the internals of DBI comment on
> this? If I have a CGI that runs the same SELECT call each and every time,
> this would come in handy ... but how does DBI do its prepare? Would it
> set a new name for each invocation, so you would have several 'cached
> plans' for the exact same SELECT call?

I not sure if I good understand you. But..

1/ this cache is in memory only (it is not across re-connection persistent),
not save in any table..etc.
2/ you can have (equil or differnet) several plans in this cache, number of
plans is not limited.
3/ you can't have two same query's name in cache (name is hash key)
4/ after EXECUTE is plan still in cache, you can run it again...

potential usage:

example - you start connection to PG and you know that you need use
20x same question (example INSERT). You can PREPARE plan for this query,
and run fast EXECUTE only (instead 20x full insert);

Karel