Re: experimental pg_qcache patch

Does it cache all queries or just explicitly prepared ones?

Does is check for cached queries all the time or just explicitly EXECUTED
ones?

Chris

----- Original Message -----
From: "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org>
To: "PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org>
Sent: Sunday, April 14, 2002 6:47 AM
Subject: [HACKERS] experimental pg_qcache patch

> Hi all,
>
> I've attached an updated version of Karel Zak's pg_qcache patch, which
> adds PREPARE/EXECUTE support to PostgreSQL (allowing prepared SQL
> statements). It should apply cleanly against CVS HEAD, and compile
> properly -- beyond that, cross your fingers :-)
>
> Please take a look at the code, play around with using PREPARE and
> EXECUTE, etc. Let me know if you have any suggestions for improvement
> or if you run into any problems -- I've probably introduced some
> regressions when I ported the code from 7.0 to current sources.
>
> BTW, if you run the regression tests, I'd expect (only) the "prepare"
> test to fail: I've only written partial regression tests so far. If
> any other tests fail, please let me know.
>
> The basic syntax looks like:
>
> PREPARE <plan_name> AS <query>;
> EXECUTE <plan_name> USING <parameters>;
> DEALLOCATE PREPARE <plan_name>;
>
> To get a look at what's being stored in the cache, try:
>
> SELECT qcache_state();
>
> For more information on the qCache code, see the README that
> Karel posted to the list a few days ago.
>
> There are still lots of things that need to be improved. Here's
> a short list: (the first 3 items are the most important, any help
> on those would be much appreciated)
>
> (1) It has a tendancy to core-dump when executing stored queries,
> particularly if the EXECUTE has an INTO clause -- it will work
> the first time, but subsequent attempts will either dump core or
> claim that they can't find the plan in the cache.
>
> (2) Sometimes executing a PREPARE gives this warning:
>
> nconway=> prepare q1 as select * from pg_class;
> WARNING: AllocSetFree: detected write past chunk end in
TransactionCommandContext 0x83087ac
> PREPARE
>
> Does anyone know what problem this indicates?
>
> (3) Preparing queries with parameters doesn't work:
>
> nconway=> PREPARE sel USING text AS SELECT * FROM pg_class WHERE relname
~~ $1;
> ERROR: Parameter '$1' is out of range
>
> (4) Add a mechanism for determining if there is already a
> cached plan with a given name.
>
> (5) Finish regression tests
>
> (6) Clean up some debugging messages, correct Karel's English,
> code cleanup, etc.
>
> (7) IMHO, the number of qcache buffers should be configurable
> in postgresql.conf, not as a command-line switch.
>
> (8) See if the syntax can be adjusted to be more compatible
> with the SQL92 syntax. Also, some of the current syntax is
> ugly, in order to make parsing easier.
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilconway(at)rogers(dot)com>
> PGP Key ID: DB3C29FC
>

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

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>