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
Sat, 13 Apr 2002 18:47:32 -0400
Neil Conway <nconway@klamath.dyndns.org>
experimental pg_qcache patch
Does it cache all queries or just explicitly prepared ones?

Does is check for cached queries all the time or just explicitly EXECUTED
ones?

Chris

----- Original Message -----
From: "Neil Conway" <nconway@klamath.dyndns.org>
To: "PostgreSQL Hackers" <pgsql-hackers@postgresql.org>
Sent: Sunday, April 14, 2002 6:47 AM
Subject: [HACKERS] experimental pg_qcache patch


> 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
>


----------------------------------------------------------------------------
----


>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


Sun, 14 Apr 2002 12:11:22 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: experimental pg_qcache patch
Does it cache all queries or just explicitly prepared ones?

Does is check for cached queries all the time or just explicitly EXECUTED
ones?

Chris

----- Original Message -----
From: "Neil Conway" <nconway@klamath.dyndns.org>
To: "PostgreSQL Hackers" <pgsql-hackers@postgresql.org>
Sent: Sunday, April 14, 2002 6:47 AM
Subject: [HACKERS] experimental pg_qcache patch


> 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
>


----------------------------------------------------------------------------
----


>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


Sun, 14 Apr 2002 12:11:31 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: experimental pg_qcache patch
On Sun, 14 Apr 2002 12:11:31 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> wrote:
> Does it cache all queries or just explicitly prepared ones?

Just explicitly prepared ones. Caching all queries opens a can of
worms that I'd rather not deal with at the moment (volunteers to
tackle this problem are welcome).
> 
> Does is check for cached queries all the time or just explicitly EXECUTED
> ones?

A cached query plan is only used for EXECUTE queries -- it is
not used all the time. My gut feeling WRT to caching everything
is similar to my response to your first question.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Sun, 14 Apr 2002 00:36:38 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: experimental pg_qcache patch
> Just explicitly prepared ones. Caching all queries opens a can of
> worms that I'd rather not deal with at the moment (volunteers to
> tackle this problem are welcome).

I definitely agree.  I think that the optimisation possiblities offered to
the DBA for shared prepared statements are quite large enough to offer
exciting possibilities.  Also, it will minimise the locking contentions Tom
speaks of.

> > Does is check for cached queries all the time or just explicitly
EXECUTED
> > ones?
>
> A cached query plan is only used for EXECUTE queries -- it is
> not used all the time. My gut feeling WRT to caching everything
> is similar to my response to your first question.

It'll be interesting to have VIEWs automatically prepared and executed from
the cache...

Chris



Sun, 14 Apr 2002 13:04:50 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: experimental pg_qcache patch
Neil Conway wrote:
> 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 :-)

I want to say I am really excited about this patch.  It illustrates the
type of major features that will appear in the coming months.  In the
past few releases, I don't think we had enough development time for our
new people to get up to speed.  By the time they were ready to tackle
major features, we were wrapping up development (or we thought we were
and were discouraging new feature additions).

With our beta target now out at September, I am sure we will have an
exciting summer of major feature additions that will significancy pair
down the TODO list and give users features they have been waiting for
for years.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 14 Apr 2002 01:20:38 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
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@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Sun, 14 Apr 2002 22:13:17 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: experimental pg_qcache patch
On Sun, Apr 14, 2002 at 10:13:17PM +0200, Karel Zak wrote:
 
> > (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.

 Hmm, I probably found it be first look to patch file.

 The WARNING message is from leak detection. I'm sure that you see
 this message if you use SHARE cache type.

 - PREPARE_KEY_PREFIX_SIZE is 4 not 3

 - in the PrepareKey() is needful fix:


+       if (store == PREPARE_STORE_SHARE) {     /* shared between same DB */
+               *flag |= QCF_SHARE_NOTREMOVEABLE;
+               key = (char *) palloc(strlen(name) + PREPARE_KEY_PREFIX_SIZE
+                                       + strlen(DatabaseName) +1);
                                                            ^^^^^^^
                                                  must be 3 

+               sprintf(key, "%s_%s_", DatabaseName, PREPARE_KEY_PREFIX);
                              ^^^^^^
                 the space for '_' is not allocated :-(

 It's my bug probably, I good knew why we need leak detection :-)

        Karel

 PS. Sorry that I don't send a patch, but now I haven't my computer there. 
-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Sun, 14 Apr 2002 22:39:32 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: experimental pg_qcache patch
On Sun, 14 Apr 2002 22:39:32 +0200
"Karel Zak" <zakkr@zf.jcu.cz> wrote:
>  - PREPARE_KEY_PREFIX_SIZE is 4 not 3
> 
>  - in the PrepareKey() is needful fix:
> 
> 
> +       if (store == PREPARE_STORE_SHARE) {     /* shared between same DB */
> +               *flag |= QCF_SHARE_NOTREMOVEABLE;
> +               key = (char *) palloc(strlen(name) + PREPARE_KEY_PREFIX_SIZE
> +                                       + strlen(DatabaseName) +1);
>                                                             ^^^^^^^
>                                                   must be 3 
> 
> +               sprintf(key, "%s_%s_", DatabaseName, PREPARE_KEY_PREFIX);
>                               ^^^^^^
>                  the space for '_' is not allocated :-(
> 
>  It's my bug probably, I good knew why we need leak detection :-)

Thanks Karel! I made the changes you suggest and the warning (and
the accompanying memory leak) have gone away.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Sun, 14 Apr 2002 17:41:24 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: experimental pg_qcache patch
> I've attached an updated version of Karel Zak's pg_qcache patch, which
> adds PREPARE/EXECUTE support to PostgreSQL (allowing prepared SQL
> statements). 

Woah :-))

Thanks Neil! You may be remind of a thread in february, where I talked
about a survey about migrating from Oracle 8.0 / NT4 to PostgreSQL 7.2 /
Red Hat 7.2 ...

Overall performances obtained are a ratio of 1,33 on standard queries
of the application, like on migrated CONNECT BY Oracle statements
(thanks again to OpenACS guys for this). This ratio is very good for us
and our customer. We felt some pride about such results.

But we faced a problem in migrating bulk plain batch in Oracle Pro*C to
ECPG: performances where 3 times slower, due to incapacity of PG to
prepare statments (some well informed guys here in PG list gave us
tips&hints to use SPI's prepared statment. Unfortunately, this would
result in a loss of functionalities from Pro*C to ECPG.. :-( so we had
to abandon this issue).

I talk here about CURSORs. 

I imagine that with your patch, we could prepare statments used in
cursors. We going to test this and benchmark the application. Not sure
it works, I think ECPG has first to take into consideration those new
functialities (Michael?).

Be sure to have feedback on this :-)

Thanks again for such initiative! I'm going to inform my co-worker (C++
senior) on your patch with the hope he can help you.

Cheers,

-- 
Jean-Paul ARGUDO                                IDEALX S.A.S
Consultant bases de donnï¿½es                     15-17, av. de Sï¿½gur
http://www.idealx.com                           F-75007 PARIS

Tue, 16 Apr 2002 10:25:00 +0200
Jean-Paul ARGUDO <jean-paul.argudo@idealx.com>
Re: experimental pg_qcache patch
Jean-Paul ARGUDO 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). 
> 
> Woah :-))
> 
> Thanks Neil! You may be remind of a thread in february, where I talked
> about a survey about migrating from Oracle 8.0 / NT4 to PostgreSQL 7.2 /
> Red Hat 7.2 ...
> 
> Overall performances obtained are a ratio of 1,33 on standard queries
> of the application, like on migrated CONNECT BY Oracle statements
> (thanks again to OpenACS guys for this). This ratio is very good for us
> and our customer. We felt some pride about such results.

Yes, I was specifically thinking of your case to make use of this.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 16 Apr 2002 11:51:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: experimental pg_qcache patch