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
