experimental pg_qcache patch - Mailing list pgsql-hackers

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@rogers.com>
PGP Key ID: DB3C29FC