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 is
needful port cache shared-memory managment. I will look at it.

> (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(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz