Thread: CVS JDBC driver will try to use server-side-prepare on unpreparable SQL
It looks like the driver is trying to use server-side prepare on SQL that it won't work on: Aug 16 00:05:40 flood postgres[12989]: [12-1] LOG: query: PREPARE JDBC_STATEMENT_4 AS CREATE TABLE "testBigDB/persisted_testBigDB/persisted_one"( pk BYTEA NOT Aug 16 00:05:40 flood postgres[12989]: [12-2] NULL, generation INT8 NOT NULL, data BYTEA NOT NULL, CONSTRAINT Aug 16 00:05:40 flood postgres[12989]: [12-3] "pkey_testBigDB/persisted_testBigDB/persisted_one" PRIMARY KEY (pk)); EXECUTEJDBC_STATEMENT_4 Aug 16 00:05:40 flood postgres[12989]: [13] ERROR: parser: parse error at or near "CREATE" at character 29 This then turns up as a SQLException on the java side. Yes, I know, "don't do that then!", but isn't the plan to default to server-side prepare eventually? Should we only be doing PREPARE on queries that are known to be safe (e.g. single-statement SELECTs), or is it better to try to catch the errors and abandon the prepare? (more general, but sounds a bit hairy). The reason that this came up is I'm modifying the driver to allow server-side prepare to be toggled at the connection- and datasource- level. Patches for that to follow once I've sorted this problem out. -O
Oliver Jowett <oliver@opencloud.com> writes:
> It looks like the driver is trying to use server-side prepare on SQL that it
> won't work on:
Note that if you use the new-protocol Parse message, you can prepare
any SQL command.
I was thinking yesterday that it would not take very much more work
to make the PREPARE SQL command accept any SQL command after it, too.
But that's close enough to a new feature that I wasn't gonna do it
for 7.4. If you feel it's important for the JDBC driver then we can
talk about it. (Neither of these things would help you on a pre-7.4
server, of course, so maybe you need a different approach anyhow.)
> Should we only be doing PREPARE on queries that are known to be safe (e.g.
> single-statement SELECTs), or is it better to try to catch the errors and
> abandon the prepare? (more general, but sounds a bit hairy).
Uh, don't you have to be prepared to catch errors in PREPARE anyway?
What if the command is syntactically or semantically wrong?
regards, tom lane
On Fri, 15 Aug 2003, Tom Lane wrote: > > Should we only be doing PREPARE on queries that are known to be safe (e.g. > > single-statement SELECTs), or is it better to try to catch the errors and > > abandon the prepare? (more general, but sounds a bit hairy). > > Uh, don't you have to be prepared to catch errors in PREPARE anyway? > What if the command is syntactically or semantically wrong? > I believe he's suggesting that on a prepare error you would retry without prepare instead of just reporting it back to the caller. Kris Jurka
On 15/08/2003 13:17 Oliver Jowett wrote: > It looks like the driver is trying to use server-side prepare on SQL that > it > won't work on: > > Aug 16 00:05:40 flood postgres[12989]: [12-1] LOG: query: PREPARE > JDBC_STATEMENT_4 AS CREATE TABLE > "testBigDB/persisted_testBigDB/persisted_one"( > pk BYTEA NOT > Aug 16 00:05:40 flood postgres[12989]: [12-2] NULL, generation INT8 > NOT NULL, data BYTEA NOT NULL, CONSTRAINT > Aug 16 00:05:40 flood postgres[12989]: [12-3] > "pkey_testBigDB/persisted_testBigDB/persisted_one" PRIMARY KEY (pk)); > EXECUTE JDBC_STATEMENT_4 > Aug 16 00:05:40 flood postgres[12989]: [13] ERROR: parser: parse error > at or near "CREATE" at character 29 > > This then turns up as a SQLException on the java side. > > Yes, I know, "don't do that then!", but isn't the plan to default to > server-side prepare eventually? IMHO, there are some issues to be sorted out first: a) making them work with connection pools. Currently (as in released not CVS tip) the server-side statement is deallocated when the JDBC statement is closed. I was looking at this a few weeks ago whilst experimenting with server-side prepares. Somehow, the back-end statement names need to be cached so the statements can remain "alive" after the JDBC statement has been closed (and also deallcated when connections are closed). b) as I understand it, the server-side prepared statement might end up using a sub-optimal plan after some time due to effect of the inserts/updates/deletes. This could be important in web applications (which would probably be using connection pooling too) where a statement might stay prepared for weeks or even months. > Should we only be doing PREPARE on queries that are known to be safe > (e.g. > single-statement SELECTs), or is it better to try to catch the errors and > abandon the prepare? (more general, but sounds a bit hairy). Hopefully SELECT, INSERT, UPDATE and DELETE should all be ok. The test I tried was with an INSERT and that worked very well - 1000 per second compared to 420 per second without server side prepares. If this test was anything like representative, then there are big performance gains here. > > The reason that this came up is I'm modifying the driver to allow > server-side prepare to be toggled at the connection- and datasource- > level. > Patches for that to follow once I've sorted this problem out. Does that include having a parameter in the URL? Some people might find that very useful as they could then use server-side prepares without having to import any postgres-specific classes and for connection pooling, I think it would be essential. rgds -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Oliver, See my comments below. Oliver Jowett wrote: > It looks like the driver is trying to use server-side prepare on SQL that it > won't work on: > > Aug 16 00:05:40 flood postgres[12989]: [12-1] LOG: query: PREPARE JDBC_STATEMENT_4 AS CREATE TABLE "testBigDB/persisted_testBigDB/persisted_one"( pk BYTEA NOT > Aug 16 00:05:40 flood postgres[12989]: [12-2] NULL, generation INT8 NOT NULL, data BYTEA NOT NULL, CONSTRAINT > Aug 16 00:05:40 flood postgres[12989]: [12-3] "pkey_testBigDB/persisted_testBigDB/persisted_one" PRIMARY KEY (pk)); EXECUTEJDBC_STATEMENT_4 > Aug 16 00:05:40 flood postgres[12989]: [13] ERROR: parser: parse error at or near "CREATE" at character 29 > > This then turns up as a SQLException on the java side. > > Yes, I know, "don't do that then!", but isn't the plan to default to > server-side prepare eventually? Yes and no. The plan is to convert fully over to the new V3 protocol which will better handle cases like this and a lot of other things. So yes the plan is to move fully to server side prepared statements, but via a different mechanism. And conversly the plan isn't to move the current mechanism forward as it has many limitations (as you are finding out). One of the big reasons for the new functionality in the V3 protocol is to provide better support for these type of opperations efficiently. However a workaround for this specific problem would be to only use server side prepared statements in the current implementation for executeQuery calls, not for executeUpdate or for plain execute. > > Should we only be doing PREPARE on queries that are known to be safe (e.g. > single-statement SELECTs), or is it better to try to catch the errors and > abandon the prepare? (more general, but sounds a bit hairy). > > The reason that this came up is I'm modifying the driver to allow > server-side prepare to be toggled at the connection- and datasource- level. > Patches for that to follow once I've sorted this problem out. > I would rather see you invest your time in implementing the V3 protocol to do this correctly. I am reluctant to commit patches along the lines of what you are describing (check the archives for previous discussions on this). But in short the reason is, that in general using the current prepared implementation will be *slower* than not using it, unless you are reusing the statement a number of times. Therefore unless you have some sort of complex application layer that is caching Statement objects and reusing them, this feature will nagatively impact performance, and IMHO will lead to problems because people will assume that something like this should be used and complain when it makes things slower. Since in order to be useful you need application logic to cache and reuse the Statement objects, it isn't that difficult to have that logic also turn on server side prepare using the current methods. Finally, if you do want to pursue your current course, I would like to see some sort of benchmarks that show these changes actually on average help. thanks, --Barry > -O > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
I can't understand. How can I "cache" my Statements? As far as I know after I return my connection to tomcat's connectionpooling my statement becomes pratically unusable, isn't it? On Fri, 15 Aug 2003 09:55:02 -0700 Barry Lind <blind@xythos.com> wrote: > Oliver, > > See my comments below. > > Oliver Jowett wrote: > > It looks like the driver is trying to use server-side prepare on SQL that it > > won't work on: > > > > Aug 16 00:05:40 flood postgres[12989]: [12-1] LOG: query: PREPARE JDBC_STATEMENT_4 AS CREATE TABLE "testBigDB/persisted_testBigDB/persisted_one"( pk BYTEA NOT > > Aug 16 00:05:40 flood postgres[12989]: [12-2] NULL, generation INT8 NOT NULL, data BYTEA NOT NULL, CONSTRAINT > > Aug 16 00:05:40 flood postgres[12989]: [12-3] "pkey_testBigDB/persisted_testBigDB/persisted_one" PRIMARY KEY (pk));EXECUTE JDBC_STATEMENT_4 > > Aug 16 00:05:40 flood postgres[12989]: [13] ERROR: parser: parse error at or near "CREATE" at character 29 > > > > This then turns up as a SQLException on the java side. > > > > Yes, I know, "don't do that then!", but isn't the plan to default to > > server-side prepare eventually? > > Yes and no. The plan is to convert fully over to the new V3 protocol > which will better handle cases like this and a lot of other things. So > yes the plan is to move fully to server side prepared statements, but > via a different mechanism. And conversly the plan isn't to move the > current mechanism forward as it has many limitations (as you are finding > out). One of the big reasons for the new functionality in the V3 > protocol is to provide better support for these type of opperations > efficiently. > > However a workaround for this specific problem would be to only use > server side prepared statements in the current implementation for > executeQuery calls, not for executeUpdate or for plain execute. > > > > > Should we only be doing PREPARE on queries that are known to be safe (e.g. > > single-statement SELECTs), or is it better to try to catch the errors and > > abandon the prepare? (more general, but sounds a bit hairy). > > > > The reason that this came up is I'm modifying the driver to allow > > server-side prepare to be toggled at the connection- and datasource- level. > > Patches for that to follow once I've sorted this problem out. > > > > I would rather see you invest your time in implementing the V3 protocol > to do this correctly. I am reluctant to commit patches along the lines > of what you are describing (check the archives for previous discussions > on this). But in short the reason is, that in general using the current > prepared implementation will be *slower* than not using it, unless you > are reusing the statement a number of times. Therefore unless you have > some sort of complex application layer that is caching Statement objects > and reusing them, this feature will nagatively impact performance, and > IMHO will lead to problems because people will assume that something > like this should be used and complain when it makes things slower. > Since in order to be useful you need application logic to cache and > reuse the Statement objects, it isn't that difficult to have that logic > also turn on server side prepare using the current methods. > > Finally, if you do want to pursue your current course, I would like to > see some sort of benchmarks that show these changes actually on average > help. > > thanks, > --Barry > > > > -O > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org /~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br) \ / Ribbon Campaign Analista de Sistemas X Against HTML Cel.: 51-91287530 / \ Email! Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone: 51-32303341
Felipe, You need to write your own connection pool to do this. That is why I am saying that it takes a lot of coding to get this to all work efficiently. --Barry Felipe Schnack wrote: > I can't understand. How can I "cache" my Statements? As far as I know after I return my connection to tomcat's connectionpooling my statement becomes pratically unusable, isn't it? > > On Fri, 15 Aug 2003 09:55:02 -0700 > Barry Lind <blind@xythos.com> wrote: > > >>Oliver, >> >>See my comments below. >> >>Oliver Jowett wrote: >> >>>It looks like the driver is trying to use server-side prepare on SQL that it >>>won't work on: >>> >>>Aug 16 00:05:40 flood postgres[12989]: [12-1] LOG: query: PREPARE JDBC_STATEMENT_4 AS CREATE TABLE "testBigDB/persisted_testBigDB/persisted_one"( pk BYTEA NOT >>>Aug 16 00:05:40 flood postgres[12989]: [12-2] NULL, generation INT8 NOT NULL, data BYTEA NOT NULL, CONSTRAINT >>>Aug 16 00:05:40 flood postgres[12989]: [12-3] "pkey_testBigDB/persisted_testBigDB/persisted_one" PRIMARY KEY (pk)); EXECUTEJDBC_STATEMENT_4 >>>Aug 16 00:05:40 flood postgres[12989]: [13] ERROR: parser: parse error at or near "CREATE" at character 29 >>> >>>This then turns up as a SQLException on the java side. >>> >>>Yes, I know, "don't do that then!", but isn't the plan to default to >>>server-side prepare eventually? >> >>Yes and no. The plan is to convert fully over to the new V3 protocol >>which will better handle cases like this and a lot of other things. So >>yes the plan is to move fully to server side prepared statements, but >>via a different mechanism. And conversly the plan isn't to move the >>current mechanism forward as it has many limitations (as you are finding >>out). One of the big reasons for the new functionality in the V3 >>protocol is to provide better support for these type of opperations >>efficiently. >> >>However a workaround for this specific problem would be to only use >>server side prepared statements in the current implementation for >>executeQuery calls, not for executeUpdate or for plain execute. >> >> >>>Should we only be doing PREPARE on queries that are known to be safe (e.g. >>>single-statement SELECTs), or is it better to try to catch the errors and >>>abandon the prepare? (more general, but sounds a bit hairy). >>> >>>The reason that this came up is I'm modifying the driver to allow >>>server-side prepare to be toggled at the connection- and datasource- level. >>>Patches for that to follow once I've sorted this problem out. >>> >> >>I would rather see you invest your time in implementing the V3 protocol >>to do this correctly. I am reluctant to commit patches along the lines >>of what you are describing (check the archives for previous discussions >>on this). But in short the reason is, that in general using the current >>prepared implementation will be *slower* than not using it, unless you >>are reusing the statement a number of times. Therefore unless you have >>some sort of complex application layer that is caching Statement objects >>and reusing them, this feature will nagatively impact performance, and >>IMHO will lead to problems because people will assume that something >>like this should be used and complain when it makes things slower. >>Since in order to be useful you need application logic to cache and >>reuse the Statement objects, it isn't that difficult to have that logic >>also turn on server side prepare using the current methods. >> >>Finally, if you do want to pursue your current course, I would like to >>see some sort of benchmarks that show these changes actually on average >>help. >> >>thanks, >>--Barry >> >> >> >>>-O >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 6: Have you searched our list archives? >>> >>> http://archives.postgresql.org >>> >> >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > /~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br) > \ / Ribbon Campaign Analista de Sistemas > X Against HTML Cel.: 51-91287530 > / \ Email! Linux Counter #281893 > > Centro Universitário Ritter dos Reis > http://www.ritterdosreis.br > ritter@ritterdosreis.br > Fone: 51-32303341 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Hm, and what my connection pool woud do to re-use them? On Fri, 15 Aug 2003 11:08:59 -0700 Barry Lind <blind@xythos.com> wrote: > Felipe, > > You need to write your own connection pool to do this. That is why I am > saying that it takes a lot of coding to get this to all work efficiently. > > --Barry > > Felipe Schnack wrote: > > I can't understand. How can I "cache" my Statements? As far as I know after I return my connection to tomcat's connectionpooling my statement becomes pratically unusable, isn't it? > > > > On Fri, 15 Aug 2003 09:55:02 -0700 > > Barry Lind <blind@xythos.com> wrote: > > > > > >>Oliver, > >> > >>See my comments below. > >> > >>Oliver Jowett wrote: > >> > >>>It looks like the driver is trying to use server-side prepare on SQL that it > >>>won't work on: > >>> > >>>Aug 16 00:05:40 flood postgres[12989]: [12-1] LOG: query: PREPARE JDBC_STATEMENT_4 AS CREATE TABLE "testBigDB/persisted_testBigDB/persisted_one"( pk BYTEA NOT > >>>Aug 16 00:05:40 flood postgres[12989]: [12-2] NULL, generation INT8 NOT NULL, data BYTEA NOT NULL, CONSTRAINT > >>>Aug 16 00:05:40 flood postgres[12989]: [12-3] "pkey_testBigDB/persisted_testBigDB/persisted_one" PRIMARY KEY (pk));EXECUTE JDBC_STATEMENT_4 > >>>Aug 16 00:05:40 flood postgres[12989]: [13] ERROR: parser: parse error at or near "CREATE" at character 29 > >>> > >>>This then turns up as a SQLException on the java side. > >>> > >>>Yes, I know, "don't do that then!", but isn't the plan to default to > >>>server-side prepare eventually? > >> > >>Yes and no. The plan is to convert fully over to the new V3 protocol > >>which will better handle cases like this and a lot of other things. So > >>yes the plan is to move fully to server side prepared statements, but > >>via a different mechanism. And conversly the plan isn't to move the > >>current mechanism forward as it has many limitations (as you are finding > >>out). One of the big reasons for the new functionality in the V3 > >>protocol is to provide better support for these type of opperations > >>efficiently. > >> > >>However a workaround for this specific problem would be to only use > >>server side prepared statements in the current implementation for > >>executeQuery calls, not for executeUpdate or for plain execute. > >> > >> > >>>Should we only be doing PREPARE on queries that are known to be safe (e.g. > >>>single-statement SELECTs), or is it better to try to catch the errors and > >>>abandon the prepare? (more general, but sounds a bit hairy). > >>> > >>>The reason that this came up is I'm modifying the driver to allow > >>>server-side prepare to be toggled at the connection- and datasource- level. > >>>Patches for that to follow once I've sorted this problem out. > >>> > >> > >>I would rather see you invest your time in implementing the V3 protocol > >>to do this correctly. I am reluctant to commit patches along the lines > >>of what you are describing (check the archives for previous discussions > >>on this). But in short the reason is, that in general using the current > >>prepared implementation will be *slower* than not using it, unless you > >>are reusing the statement a number of times. Therefore unless you have > >>some sort of complex application layer that is caching Statement objects > >>and reusing them, this feature will nagatively impact performance, and > >>IMHO will lead to problems because people will assume that something > >>like this should be used and complain when it makes things slower. > >>Since in order to be useful you need application logic to cache and > >>reuse the Statement objects, it isn't that difficult to have that logic > >>also turn on server side prepare using the current methods. > >> > >>Finally, if you do want to pursue your current course, I would like to > >>see some sort of benchmarks that show these changes actually on average > >>help. > >> > >>thanks, > >>--Barry > >> > >> > >> > >>>-O > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 6: Have you searched our list archives? > >>> > >>> http://archives.postgresql.org > >>> > >> > >> > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > > > > > /~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br) > > \ / Ribbon Campaign Analista de Sistemas > > X Against HTML Cel.: 51-91287530 > > / \ Email! Linux Counter #281893 > > > > Centro Universitário Ritter dos Reis > > http://www.ritterdosreis.br > > ritter@ritterdosreis.br > > Fone: 51-32303341 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > /~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br) \ / Ribbon Campaign Analista de Sistemas X Against HTML Cel.: 51-91287530 / \ Email! Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone: 51-32303341
Felipe, Your custom connection pool would provide implementations for Statement.close() that doesn't really close the statement, but puts it back into some hashtable/pool where the key to the Statement object is the text of the sql. Then prepareStatement(sql) would first look for an existing Statement object in the hash for that sql and reuse it, else create a new one if it didn't find it. --Barry Felipe Schnack wrote: > Hm, and what my connection pool woud do to re-use them? > > On Fri, 15 Aug 2003 11:08:59 -0700 > Barry Lind <blind@xythos.com> wrote: > > >>Felipe, >> >>You need to write your own connection pool to do this. That is why I am >>saying that it takes a lot of coding to get this to all work efficiently. >> >>--Barry >> >>Felipe Schnack wrote: >> >>> I can't understand. How can I "cache" my Statements? As far as I know after I return my connection to tomcat's connectionpooling my statement becomes pratically unusable, isn't it? >>> >>>On Fri, 15 Aug 2003 09:55:02 -0700 >>>Barry Lind <blind@xythos.com> wrote: >>> >>> >>> >>>>Oliver, >>>> >>>>See my comments below. >>>> >>>>Oliver Jowett wrote: >>>> >>>> >>>>>It looks like the driver is trying to use server-side prepare on SQL that it >>>>>won't work on: >>>>> >>>>>Aug 16 00:05:40 flood postgres[12989]: [12-1] LOG: query: PREPARE JDBC_STATEMENT_4 AS CREATE TABLE "testBigDB/persisted_testBigDB/persisted_one"( pk BYTEA NOT >>>>>Aug 16 00:05:40 flood postgres[12989]: [12-2] NULL, generation INT8 NOT NULL, data BYTEA NOT NULL, CONSTRAINT >>>>>Aug 16 00:05:40 flood postgres[12989]: [12-3] "pkey_testBigDB/persisted_testBigDB/persisted_one" PRIMARY KEY (pk));EXECUTE JDBC_STATEMENT_4 >>>>>Aug 16 00:05:40 flood postgres[12989]: [13] ERROR: parser: parse error at or near "CREATE" at character 29 >>>>> >>>>>This then turns up as a SQLException on the java side. >>>>> >>>>>Yes, I know, "don't do that then!", but isn't the plan to default to >>>>>server-side prepare eventually? >>>> >>>>Yes and no. The plan is to convert fully over to the new V3 protocol >>>>which will better handle cases like this and a lot of other things. So >>>>yes the plan is to move fully to server side prepared statements, but >>>>via a different mechanism. And conversly the plan isn't to move the >>>>current mechanism forward as it has many limitations (as you are finding >>>>out). One of the big reasons for the new functionality in the V3 >>>>protocol is to provide better support for these type of opperations >>>>efficiently. >>>> >>>>However a workaround for this specific problem would be to only use >>>>server side prepared statements in the current implementation for >>>>executeQuery calls, not for executeUpdate or for plain execute. >>>> >>>> >>>> >>>>>Should we only be doing PREPARE on queries that are known to be safe (e.g. >>>>>single-statement SELECTs), or is it better to try to catch the errors and >>>>>abandon the prepare? (more general, but sounds a bit hairy). >>>>> >>>>>The reason that this came up is I'm modifying the driver to allow >>>>>server-side prepare to be toggled at the connection- and datasource- level. >>>>>Patches for that to follow once I've sorted this problem out. >>>>> >>>> >>>>I would rather see you invest your time in implementing the V3 protocol >>>>to do this correctly. I am reluctant to commit patches along the lines >>>>of what you are describing (check the archives for previous discussions >>>>on this). But in short the reason is, that in general using the current >>>>prepared implementation will be *slower* than not using it, unless you >>>>are reusing the statement a number of times. Therefore unless you have >>>>some sort of complex application layer that is caching Statement objects >>>>and reusing them, this feature will nagatively impact performance, and >>>>IMHO will lead to problems because people will assume that something >>>>like this should be used and complain when it makes things slower. >>>>Since in order to be useful you need application logic to cache and >>>>reuse the Statement objects, it isn't that difficult to have that logic >>>>also turn on server side prepare using the current methods. >>>> >>>>Finally, if you do want to pursue your current course, I would like to >>>>see some sort of benchmarks that show these changes actually on average >>>>help. >>>> >>>>thanks, >>>>--Barry >>>> >>>> >>>> >>>> >>>>>-O >>>>> >>>>>---------------------------(end of broadcast)--------------------------- >>>>>TIP 6: Have you searched our list archives? >>>>> >>>>> http://archives.postgresql.org >>>>> >>>> >>>> >>>> >>>> >>>>---------------------------(end of broadcast)--------------------------- >>>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >>> >>> >>> >>> >>> /~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br) >>> \ / Ribbon Campaign Analista de Sistemas >>> X Against HTML Cel.: 51-91287530 >>> / \ Email! Linux Counter #281893 >>> >>>Centro Universitário Ritter dos Reis >>>http://www.ritterdosreis.br >>>ritter@ritterdosreis.br >>>Fone: 51-32303341 >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 6: Have you searched our list archives? >>> >>> http://archives.postgresql.org >>> >> >> > > > > /~\ The ASCII Felipe Schnack (felipes@ritterdosreis.br) > \ / Ribbon Campaign Analista de Sistemas > X Against HTML Cel.: 51-91287530 > / \ Email! Linux Counter #281893 > > Centro Universitário Ritter dos Reis > http://www.ritterdosreis.br > ritter@ritterdosreis.br > Fone: 51-32303341 >
Re: CVS JDBC driver will try to use server-side-prepare on unpreparable SQL
From
Oliver Jowett
Date:
On Fri, Aug 15, 2003 at 11:58:42AM -0400, Kris Jurka wrote: > > > On Fri, 15 Aug 2003, Tom Lane wrote: > > > > Should we only be doing PREPARE on queries that are known to be safe (e.g. > > > single-statement SELECTs), or is it better to try to catch the errors and > > > abandon the prepare? (more general, but sounds a bit hairy). > > > > Uh, don't you have to be prepared to catch errors in PREPARE anyway? > > What if the command is syntactically or semantically wrong? > > > > I believe he's suggesting that on a prepare error you would retry without > prepare instead of just reporting it back to the caller. Yes, that's what I meant. I've realised it won't work transparently, though, as if you're in a transaction the bad PREPARE will abort it. -O
Re: CVS JDBC driver will try to use server-side-prepare on unpreparable SQL
From
Oliver Jowett
Date:
On Fri, Aug 15, 2003 at 09:55:02AM -0700, Barry Lind wrote: Hi Barry, Thanks for the detailed response! > Yes and no. The plan is to convert fully over to the new V3 protocol > which will better handle cases like this and a lot of other things. So > yes the plan is to move fully to server side prepared statements, but > via a different mechanism. And conversly the plan isn't to move the > current mechanism forward as it has many limitations (as you are finding > out). One of the big reasons for the new functionality in the V3 > protocol is to provide better support for these type of opperations > efficiently. > > However a workaround for this specific problem would be to only use > server side prepared statements in the current implementation for > executeQuery calls, not for executeUpdate or for plain execute. Unfortunately it's executeUpdate() where I benefit from server-side prepare.. > >Should we only be doing PREPARE on queries that are known to be safe (e.g. > >single-statement SELECTs), or is it better to try to catch the errors and > >abandon the prepare? (more general, but sounds a bit hairy). > > > >The reason that this came up is I'm modifying the driver to allow > >server-side prepare to be toggled at the connection- and datasource- level. > >Patches for that to follow once I've sorted this problem out. > > > > I would rather see you invest your time in implementing the V3 protocol > to do this correctly. I am reluctant to commit patches along the lines > of what you are describing (check the archives for previous discussions > on this). I had a search in the archives .. nothing conclusive. There was: http://archives.postgresql.org/pgsql-jdbc/2003-01/msg00012.php (and replies) where the main objection seemed to be to turning on server-side prepare *by default*. Did you have another thread in mind? I'm not sure if we're ready to move our system to 7.4 & V3 at this stage. The JDBC driver seems somewhat less mature when it comes to the V3 protocol, and at the moment it seems like the additional development time needed to clean up the driver for V3 outweighs the benefits we'd get (for our app, anyway). I'm happy to be convinced otherwise, though. > But in short the reason is, that in general using the current > prepared implementation will be *slower* than not using it, unless you > are reusing the statement a number of times. Therefore unless you have > some sort of complex application layer that is caching Statement objects > and reusing them, this feature will nagatively impact performance, and > IMHO will lead to problems because people will assume that something > like this should be used and complain when it makes things slower. > Since in order to be useful you need application logic to cache and > reuse the Statement objects, it isn't that difficult to have that logic > also turn on server side prepare using the current methods. My situation is that I *do* have an application that caches PreparedStatements that are frequently used; however, there is no general framework for this in place. It's a decent amount of work to get an efficient framework from this in place as you need to key the statement cache on both connection and query for the general case. If you're not writing a general framework you can take advantage of the app structure. In this case, I know that a particular object exclusively owns a single connection, and I know the set of statements that the object will execute .. so I can just prepare statements aheadof time and store them directly on the object itself. To turn on use of server-side prepare with the current driver, I must cast every PreparedStatement I create to org.postgresql.PGStatement and call setUseServerPrepare() (either explicitly or via a helper). Currently, our app code is postgresql-independent and quite simple. Ideally I'd like to keep it that way! I looked for the "obvious" solution of being able to change the default use-server-prepare at a connection or (even better) datasource level. If I can do it at the datasource level, this means there is *no* postgresql-driver-specific code involved (we initialize datasource objects from a config file via reflection) assuming I can sort out this CREATE TABLE problem in a driver-independent way (e.g. fix the driver!). But there's nothing there to do that currently. Yes, you can do this in other ways, but it's going to be considerably more complex .. for this app, it might double the code needed. I understand that in many cases turning on server-side prepare everywhere will actually slow things down. In this case, though, it'd speed things up .. but the driver doesn't let me turn in on globally even though I know what I'm doing. So I'm scratching an itch :) > Finally, if you do want to pursue your current course, I would like to > see some sort of benchmarks that show these changes actually on average > help. On average they won't help, that's why you wouldn't turn it on indiscriminately, in the same way you don't use PGStatement.setUseServerPrepare() indiscriminately. The app in our case is essentially a big insert engine. Using server-side prepare seems to give us a 15% or so improvement: (these stats are a bit old and not very rigorous -- the postmaster is competing with the java process for CPU -- but you get the idea) Without server-side prepare: ===== testBigDB/persisted===== Testing with 10000x10 200-byte creates [...] Insert time: 14603ms Flush time: 76991ms With server-side prepare: Insert time: 14312ms Flush time: 62835ms (the total SQL time is insert + flush; "insert" in this case means "insert in-memory"; the SQL bit is a write-back cache, "flush" waits for outstanding SQL work to end) -O
Re: CVS JDBC driver will try to use server-side-prepare on unpreparable SQL
From
Oliver Jowett
Date:
On Fri, Aug 15, 2003 at 04:22:17PM +0100, Paul Thomas wrote: > a) making them work with connection pools. Currently (as in released not > CVS tip) the server-side statement is deallocated when the JDBC statement > is closed. I was looking at this a few weeks ago whilst experimenting with > server-side prepares. Somehow, the back-end statement names need to be > cached so the statements can remain "alive" after the JDBC statement has > been closed (and also deallcated when connections are closed). This should be done in the pooling implementation, not in the driver. (well, modulo the JDBC3 statement caching bits, which I haven't looked at). You can do this in a driver-independent way: wrap prepared statements and have close() return the underlying statement to a per-connection cache rather than closing the underlying statement. Somewhat like connection pooling, really: the pooling mechanism manages the creation and destruction of the underlying driver resource according to whatever rules it implements. The driver doesn't need to do anything clever and just implements the create/destroy/use primitives. > >The reason that this came up is I'm modifying the driver to allow > >server-side prepare to be toggled at the connection- and datasource- > >level. > >Patches for that to follow once I've sorted this problem out. > > Does that include having a parameter in the URL? Some people might find > that very useful as they could then use server-side prepares without > having to import any postgres-specific classes and for connection pooling, > I think it would be essential. A URL parameter would be easy to add. We use reflection on DataSource javabean-like methods to get the same sort of driver independence here. -O
On Fri, Aug 15, 2003 at 09:55:02AM -0700, Barry Lind wrote: > But in short the reason is, that in general using the current > prepared implementation will be *slower* than not using it, unless you > are reusing the statement a number of times. [...] Hmm, has anyone considered an approach where the statement begins to use server-side prepare after it has been used N times? (the assumption being that it will continue to be reused). N=2 is probably enough to get 90% of the cases :) -O
Oliver Jowett wrote: > On Fri, Aug 15, 2003 at 09:55:02AM -0700, Barry Lind wrote: > > >>But in short the reason is, that in general using the current >>prepared implementation will be *slower* than not using it, unless you >>are reusing the statement a number of times. [...] > > > Hmm, has anyone considered an approach where the statement begins to use > server-side prepare after it has been used N times? (the assumption being > that it will continue to be reused). N=2 is probably enough to get 90% of > the cases :) > I agree that this would likely work quite well. If you use it more than once you are likely to use it again. --Barry
Oliver Jowett wrote: > On Fri, Aug 15, 2003 at 09:55:02AM -0700, Barry Lind wrote: > > Hi Barry, > > Thanks for the detailed response! > > >>Yes and no. The plan is to convert fully over to the new V3 protocol >>which will better handle cases like this and a lot of other things. So >>yes the plan is to move fully to server side prepared statements, but >>via a different mechanism. And conversly the plan isn't to move the >>current mechanism forward as it has many limitations (as you are finding >>out). One of the big reasons for the new functionality in the V3 >>protocol is to provide better support for these type of opperations >>efficiently. >> >>However a workaround for this specific problem would be to only use >>server side prepared statements in the current implementation for >>executeQuery calls, not for executeUpdate or for plain execute. > > > Unfortunately it's executeUpdate() where I benefit from server-side prepare.. > I probably should have said '... for executeQuery and executeUpdate calls, not for plain execute.' I consider the purpose of executeQuery() to execute SELECT statements, and executeUpdate() for INSERT, UPDATE, and DELETE, and plain execute() for everything else. Though technically the spec doesn't limit things in this way, practically (given the return types) it is implied IMHO. > >>>Should we only be doing PREPARE on queries that are known to be safe (e.g. >>>single-statement SELECTs), or is it better to try to catch the errors and >>>abandon the prepare? (more general, but sounds a bit hairy). >>> >>>The reason that this came up is I'm modifying the driver to allow >>>server-side prepare to be toggled at the connection- and datasource- level. >>>Patches for that to follow once I've sorted this problem out. >>> >> >>I would rather see you invest your time in implementing the V3 protocol >>to do this correctly. I am reluctant to commit patches along the lines >>of what you are describing (check the archives for previous discussions >>on this). > > > I had a search in the archives .. nothing conclusive. There was: > > http://archives.postgresql.org/pgsql-jdbc/2003-01/msg00012.php > > (and replies) where the main objection seemed to be to turning on > server-side prepare *by default*. Did you have another thread in mind? > Yeah that was probably it. Besides I think I restated my opinions in my response to you, so there probably isn't anything else in the archives that says much more. > I'm not sure if we're ready to move our system to 7.4 & V3 at this stage. > The JDBC driver seems somewhat less mature when it comes to the V3 protocol, > and at the moment it seems like the additional development time needed to > clean up the driver for V3 outweighs the benefits we'd get (for our app, > anyway). I'm happy to be convinced otherwise, though. > I certainly can't tell you where it makes the most sense for you to invest your time. It is your time after all. I can only voice my opinions on where I would like people to invest their time for what I believe to be the overall improvement of the driver. But often what I would like differs from the individual project priorities of developers, which is fine and what open source is all about. So go ahead and work on this in the manner your project needs. Given your recent patches, I fully expect that no matter how you contribute your time, it will greatly improve the driver. thanks, --Barry