Thread: 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
Attachment
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 >
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 >
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
> 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
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, Pennsylvania19026
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
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 seethis 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
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
> 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
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, Pennsylvania19026