Re: [HACKERS] 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 tipshints 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 ARGUDOIDEALX S.A.S Consultant bases de données 15-17, av. de Ségur http://www.idealx.com F-75007 PARIS ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] experimental pg_qcache patch
On Sun, 14 Apr 2002 22:39:32 +0200
Karel Zak [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
PGP Key ID: DB3C29FC
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 [EMAIL PROTECTED] To: PostgreSQL Hackers [EMAIL PROTECTED] 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 [EMAIL PROTECTED] PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 [EMAIL PROTECTED] To: PostgreSQL Hackers [EMAIL PROTECTED] 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 [EMAIL PROTECTED] PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] experimental pg_qcache patch
On Sun, 14 Apr 2002 12:11:31 +0800 Christopher Kings-Lynne [EMAIL PROTECTED] 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 [EMAIL PROTECTED] PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
