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 <[EMAIL PROTECTED]> PGP Key ID: DB3C29FC
pg_qcache.patch.gz
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
