Re: experimental pg_qcache patch

On Sat, Apr 13, 2002 at 06:47:32PM -0400, Neil Conway wrote:
> 
> 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 :-)I will try it during this week.

> 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
 Is needful use shared cache? This is right and cardinal question. (Is pre-forked backends expected in next release?) 

> 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.
I don't know this bug :-)

> (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?
The memory managment is diffrent between 7.0 and 7.2. There isneedful port cache shared-memory managment. I will look
atit.
 

> (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
My original syntax was:
 PREPARE sel AS SELECT * FROM pg_class WHERE relname ~~ $1 USING text;
... USING is behind query.
   Karel

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz