pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedGeoserver-PostGIS performance problems
13+ messages / 6 participants
[nested] [flat]
* Geoserver-PostGIS performance problems
@ 2012-07-24 07:21 Riaan van den Dool <rvddool@csir.co.za>
0 siblings, 1 reply; 13+ messages in thread
From: Riaan van den Dool @ 2012-07-24 07:21 UTC (permalink / raw)
To: pgsql-performance
Hi
We have noticed something strange in the interaction between our Geoserver instance and Postgres/PostGIS.
After setting Geoserver's log level to include developer debugging, I managed to capture a single request from Geoserver WMS to PostGIS.
The (shortened) sequence of events and their timestamps:
12:31:22,658 - SELECT query for MSG is sent to Postgres
12:32:10,315 - Rendering for MSG layer starts
12:32:10,356 - DB Connection Closed
========
~ 48 seconds
Interestingly enough, when I execute the same query (MSG) directly from PgAdmin3:
SELECT "frp_mw",encode(ST_AsBinary(ST_Force_2D("the_geom")),'base64') as "the_geom" FROM "public"."af_msg_abba_datetime_today" WHERE ("the_geom" && GeometryFromText('POLYGON ((-27.67968749408379 -46.92207325648429, -27.67968749408379 -6.186892358058866, 75.67968748740275 -6.186892358058866, 75.67968748740275 -46.92207325648429, -27.67968749408379 -46.92207325648429))', 4326) AND (("frp_mw" >= -1 AND "frp_mw" <= 150) OR ("frp_mw" >= 151 AND "frp_mw" <= 300) OR ("frp_mw" >= 301 AND "frp_mw" <= 600) OR ("frp_mw" >= 601 AND "frp_mw" <= 50000)));
I get 6515 rows in 380 ms.
Ie Postgres is able to return the results of the query within 380ms if queried from PgAdmin3 but Geoserver takes about 48 seconds to get hold of the same resultset.
Is this some kind of JDBC problem perhaps?
Some details about our setup:
Master Postgres database is on a separate VM from Geoserver, but we replicate to a slave Postgres cluster on the Geoserver VM (same host). So Geoserver is referencing the 'localhost' read-only Postgres cluster for its queries.
The 380 ms response time shown above was from the slave Postgres cluster, same one that Geoserver is using.
All Linux (Ubuntu 11.10) based. Postgres 9.1 PostGIS 1.5 Geoserver 2.1.3
Riaan
--
This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard.
The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html.
This message has been scanned for viruses and dangerous content by MailScanner,
and is believed to be clean.
Please consider the environment before printing this email.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-24 07:54 Brett Walker <brett.walker@geometryit.com>
parent: Riaan van den Dool <rvddool@csir.co.za>
0 siblings, 1 reply; 13+ messages in thread
From: Brett Walker @ 2012-07-24 07:54 UTC (permalink / raw)
To: Riaan van den Dool <rvddool@csir.co.za>; pgsql-performance
Hi Riaan,
I am familiar if the Geoserver/Geotools package and I believe that the problem is not Postgres/PostGIS but rather Geoserver.
The DB Connection Closed message is not sent at the end of the query, but rather at the end of the rendering. There is more than just querying happening between the Select message and the Closed message.
Brett
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Riaan van den Dool
Sent: Tuesday, 24 July 2012 5:21 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Geoserver-PostGIS performance problems
Hi
We have noticed something strange in the interaction between our Geoserver instance and Postgres/PostGIS.
After setting Geoserver's log level to include developer debugging, I managed to capture a single request from Geoserver WMS to PostGIS.
The (shortened) sequence of events and their timestamps:
12:31:22,658 - SELECT query for MSG is sent to Postgres
12:32:10,315 - Rendering for MSG layer starts
12:32:10,356 - DB Connection Closed
========
~ 48 seconds
Interestingly enough, when I execute the same query (MSG) directly from PgAdmin3:
SELECT "frp_mw",encode(ST_AsBinary(ST_Force_2D("the_geom")),'base64') as "the_geom" FROM "public"."af_msg_abba_datetime_today" WHERE ("the_geom" && GeometryFromText('POLYGON ((-27.67968749408379 -46.92207325648429, -27.67968749408379 -6.186892358058866, 75.67968748740275 -6.186892358058866, 75.67968748740275 -46.92207325648429, -27.67968749408379 -46.92207325648429))', 4326) AND (("frp_mw" >= -1 AND "frp_mw" <= 150) OR ("frp_mw" >= 151 AND "frp_mw" <= 300) OR ("frp_mw" >= 301 AND "frp_mw" <= 600) OR ("frp_mw" >= 601 AND "frp_mw" <= 50000)));
I get 6515 rows in 380 ms.
Ie Postgres is able to return the results of the query within 380ms if queried from PgAdmin3 but Geoserver takes about 48 seconds to get hold of the same resultset.
Is this some kind of JDBC problem perhaps?
Some details about our setup:
Master Postgres database is on a separate VM from Geoserver, but we replicate to a slave Postgres cluster on the Geoserver VM (same host). So Geoserver is referencing the 'localhost' read-only Postgres cluster for its queries.
The 380 ms response time shown above was from the slave Postgres cluster, same one that Geoserver is using.
All Linux (Ubuntu 11.10) based. Postgres 9.1 PostGIS 1.5 Geoserver 2.1.3
Riaan
--
This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard.
The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html.
This message has been scanned for viruses and dangerous content by MailScanner<http://www.mailscanner.info/;,
and is believed to be clean.
Please consider the environment before printing this email.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-24 08:10 Riaan van den Dool <rvddool@csir.co.za>
parent: Brett Walker <brett.walker@geometryit.com>
0 siblings, 1 reply; 13+ messages in thread
From: Riaan van den Dool @ 2012-07-24 08:10 UTC (permalink / raw)
To: Brett Walker <brett.walker@geometryit.com>; pgsql-performance
Thank you for this response.
It turns out our performance problems were solved when I switched off 'Prepared statements' in Geoserver for the PostGIS data store. It makes quite a huge difference.
Riaan
>>> Brett Walker <brett.walker@geometryit.com> 7/24/2012 09:54 AM >>>
Hi Riaan,
I am familiar if the Geoserver/Geotools package and I believe that the problem is not Postgres/PostGIS but rather Geoserver.
The DB Connection Closed message is not sent at the end of the query, but rather at the end of the rendering. There is more than just querying happening between the Select message and the Closed message.
Brett
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Riaan van den Dool
Sent: Tuesday, 24 July 2012 5:21 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Geoserver-PostGIS performance problems
Hi
We have noticed something strange in the interaction between our Geoserver instance and Postgres/PostGIS.
After setting Geoserver's log level to include developer debugging, I managed to capture a single request from Geoserver WMS to PostGIS.
The (shortened) sequence of events and their timestamps:
12:31:22,658 - SELECT query for MSG is sent to Postgres
12:32:10,315 - Rendering for MSG layer starts
12:32:10,356 - DB Connection Closed
========
~ 48 seconds
Interestingly enough, when I execute the same query (MSG) directly from PgAdmin3:
SELECT "frp_mw",encode(ST_AsBinary(ST_Force_2D("the_geom")),'base64') as "the_geom" FROM "public"."af_msg_abba_datetime_today" WHERE ("the_geom" && GeometryFromText('POLYGON ((-27.67968749408379 -46.92207325648429, -27.67968749408379 -6.186892358058866, 75.67968748740275 -6.186892358058866, 75.67968748740275 -46.92207325648429, -27.67968749408379 -46.92207325648429))', 4326) AND (("frp_mw" >= -1 AND "frp_mw" <= 150) OR ("frp_mw" >= 151 AND "frp_mw" <= 300) OR ("frp_mw" >= 301 AND "frp_mw" <= 600) OR ("frp_mw" >= 601 AND "frp_mw" <= 50000)));
I get 6515 rows in 380 ms.
Ie Postgres is able to return the results of the query within 380ms if queried from PgAdmin3 but Geoserver takes about 48 seconds to get hold of the same resultset.
Is this some kind of JDBC problem perhaps?
Some details about our setup:
Master Postgres database is on a separate VM from Geoserver, but we replicate to a slave Postgres cluster on the Geoserver VM (same host). So Geoserver is referencing the 'localhost' read-only Postgres cluster for its queries.
The 380 ms response time shown above was from the slave Postgres cluster, same one that Geoserver is using.
All Linux (Ubuntu 11.10) based. Postgres 9.1 PostGIS 1.5 Geoserver 2.1.3
Riaan
--
This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard.
The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html.
This message has been scanned for viruses and dangerous content by MailScanner ( http://www.mailscanner.info/ ),
and is believed to be clean.
Please consider the environment before printing this email.
--
This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard.
The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html.
This message has been scanned for viruses and dangerous content by MailScanner ( http://www.mailscanner.info/ ),
and is believed to be clean.
Please consider the environment before printing this email.
--
This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard.
The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html.
This message has been scanned for viruses and dangerous content by MailScanner,
and is believed to be clean.
Please consider the environment before printing this email.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-24 15:50 Maciek Sakrejda <m.sakrejda@gmail.com>
parent: Riaan van den Dool <rvddool@csir.co.za>
0 siblings, 1 reply; 13+ messages in thread
From: Maciek Sakrejda @ 2012-07-24 15:50 UTC (permalink / raw)
To: Riaan van den Dool <rvddool@csir.co.za>; +Cc: Brett Walker <brett.walker@geometryit.com>; pgsql-performance
This may be another issue of the problem discussed here:
http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
(Kris Jurka explains the crux of it in that thread).
Note that it seems the preparing/planning interaction was not the
poster's actual problem, but it may have been yours. As Tom Lane notes
in that thread, this should get better in 9.2.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-25 18:45 Merlin Moncure <mmoncure@gmail.com>
parent: Maciek Sakrejda <m.sakrejda@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Merlin Moncure @ 2012-07-25 18:45 UTC (permalink / raw)
To: Maciek Sakrejda <m.sakrejda@gmail.com>; +Cc: Riaan van den Dool <rvddool@csir.co.za>; Brett Walker <brett.walker@geometryit.com>; pgsql-performance
On Tue, Jul 24, 2012 at 10:50 AM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
> This may be another issue of the problem discussed here:
> http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
> (Kris Jurka explains the crux of it in that thread).
>
> Note that it seems the preparing/planning interaction was not the
> poster's actual problem, but it may have been yours. As Tom Lane notes
> in that thread, this should get better in 9.2.
jdbc should get some blame too -- it's really aggressive about
preparing queries.
merlin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-25 19:17 Vinicius Abrahao <vinnix.bsd@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Vinicius Abrahao @ 2012-07-25 19:17 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Maciek Sakrejda <m.sakrejda@gmail.com>; Riaan van den Dool <rvddool@csir.co.za>; Brett Walker <brett.walker@geometryit.com>; pgsql-performance
On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Jul 24, 2012 at 10:50 AM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
>> This may be another issue of the problem discussed here:
>> http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
>> (Kris Jurka explains the crux of it in that thread).
>>
>> Note that it seems the preparing/planning interaction was not the
>> poster's actual problem, but it may have been yours. As Tom Lane notes
>> in that thread, this should get better in 9.2.
>
> jdbc should get some blame too -- it's really aggressive about
> preparing queries.
>
indeed!
Is there any reason for that?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-25 19:26 Merlin Moncure <mmoncure@gmail.com>
parent: Vinicius Abrahao <vinnix.bsd@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Merlin Moncure @ 2012-07-25 19:26 UTC (permalink / raw)
To: Vinicius Abrahao <vinnix.bsd@gmail.com>; +Cc: Maciek Sakrejda <m.sakrejda@gmail.com>; Riaan van den Dool <rvddool@csir.co.za>; Brett Walker <brett.walker@geometryit.com>; pgsql-performance
On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>> Note that it seems the preparing/planning interaction was not the
>>> poster's actual problem, but it may have been yours. As Tom Lane notes
>>> in that thread, this should get better in 9.2.
>>
>> jdbc should get some blame too -- it's really aggressive about
>> preparing queries.
>>
>
> indeed!
> Is there any reason for that?
IMNSHO it's an oversight in the core JDBC design dating back to the
beginning: you have two basic choices for executing SQL. The
unparameterized Statement or the parameterized PreparedStatement.
There should have been a 'ParamaterizedStatement' that gave the
expectation of paramaterization without setting up and permanent
server side structures to handle the query; libpq makes this
distinction and it works very well. Of course, there are various ways
to work around this but the point stands.
merlin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-25 19:59 Vinicius Abrahao <vinnix.bsd@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Vinicius Abrahao @ 2012-07-25 19:59 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Maciek Sakrejda <m.sakrejda@gmail.com>; Riaan van den Dool <rvddool@csir.co.za>; Brett Walker <brett.walker@geometryit.com>; pgsql-performance
On Wed, Jul 25, 2012 at 4:26 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
>> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>>> Note that it seems the preparing/planning interaction was not the
>>>> poster's actual problem, but it may have been yours. As Tom Lane notes
>>>> in that thread, this should get better in 9.2.
>>>
>>> jdbc should get some blame too -- it's really aggressive about
>>> preparing queries.
>>>
>>
>> indeed!
>> Is there any reason for that?
>
> IMNSHO it's an oversight in the core JDBC design dating back to the
> beginning: you have two basic choices for executing SQL. The
> unparameterized Statement or the parameterized PreparedStatement.
> There should have been a 'ParamaterizedStatement' that gave the
> expectation of paramaterization without setting up and permanent
> server side structures to handle the query; libpq makes this
> distinction and it works very well. Of course, there are various ways
> to work around this but the point stands.
>
That is true, I was observing the same, days ago:
Running queries and statments in jdbc:
https://github.com/vinnix/JavaLab/blob/master/Scrollable.java
And running queries with libpq:
https://github.com/vinnix/testLibPQ/blob/master/testlibpq.c
Is this possible to change something (I really don't know what or
where) in the jdbc driver
to get more direct aproach? (if that's make any sense to you guys...)
Best regards,
vinnix
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-25 20:04 Merlin Moncure <mmoncure@gmail.com>
parent: Vinicius Abrahao <vinnix.bsd@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Merlin Moncure @ 2012-07-25 20:04 UTC (permalink / raw)
To: Vinicius Abrahao <vinnix.bsd@gmail.com>; +Cc: Maciek Sakrejda <m.sakrejda@gmail.com>; Riaan van den Dool <rvddool@csir.co.za>; Brett Walker <brett.walker@geometryit.com>; pgsql-performance
On Wed, Jul 25, 2012 at 2:59 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
> On Wed, Jul 25, 2012 at 4:26 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
>>> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>>>> Note that it seems the preparing/planning interaction was not the
>>>>> poster's actual problem, but it may have been yours. As Tom Lane notes
>>>>> in that thread, this should get better in 9.2.
>>>>
>>>> jdbc should get some blame too -- it's really aggressive about
>>>> preparing queries.
>>>>
>>>
>>> indeed!
>>> Is there any reason for that?
>>
>> IMNSHO it's an oversight in the core JDBC design dating back to the
>> beginning: you have two basic choices for executing SQL. The
>> unparameterized Statement or the parameterized PreparedStatement.
>> There should have been a 'ParamaterizedStatement' that gave the
>> expectation of paramaterization without setting up and permanent
>> server side structures to handle the query; libpq makes this
>> distinction and it works very well. Of course, there are various ways
>> to work around this but the point stands.
>>
>
> That is true, I was observing the same, days ago:
>
> Running queries and statments in jdbc:
> https://github.com/vinnix/JavaLab/blob/master/Scrollable.java
>
> And running queries with libpq:
> https://github.com/vinnix/testLibPQ/blob/master/testlibpq.c
>
> Is this possible to change something (I really don't know what or
> where) in the jdbc driver
> to get more direct aproach? (if that's make any sense to you guys...)
you can disable server-side preparing in the url or as library
setting. see here:
"jdbc:postgresql://localhost:5432/test?prepareThreshold=3";
unfortunately postgres jdbc is bugged and does not honor the above for
transaction control commands (begin, commit, etc). This patch
http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch
will fix it, assuming it hasn't been fixed in recent postgres jdbc.
merlin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-26 02:13 Rural Hunter <ruralhunter@gmail.com>
parent: Vinicius Abrahao <vinnix.bsd@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Rural Hunter @ 2012-07-26 02:13 UTC (permalink / raw)
To: pgsql-performance
Why not just use simple Statement instead of PreparedStatement and
construct the SQL with concated string or StringBuilder? like this:
int col1=xxx;
String col2="xxxx";
String sql="select * from table where col1="+col+" and col2='"+col2+"'";
δΊ 2012/7/26 3:59, Vinicius Abrahao ει:
> On Wed, Jul 25, 2012 at 4:26 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
>>> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>>>> Note that it seems the preparing/planning interaction was not the
>>>>> poster's actual problem, but it may have been yours. As Tom Lane notes
>>>>> in that thread, this should get better in 9.2.
>>>> jdbc should get some blame too -- it's really aggressive about
>>>> preparing queries.
>>>>
>>> indeed!
>>> Is there any reason for that?
>> IMNSHO it's an oversight in the core JDBC design dating back to the
>> beginning: you have two basic choices for executing SQL. The
>> unparameterized Statement or the parameterized PreparedStatement.
>> There should have been a 'ParamaterizedStatement' that gave the
>> expectation of paramaterization without setting up and permanent
>> server side structures to handle the query; libpq makes this
>> distinction and it works very well. Of course, there are various ways
>> to work around this but the point stands.
>>
> That is true, I was observing the same, days ago:
>
> Running queries and statments in jdbc:
> https://github.com/vinnix/JavaLab/blob/master/Scrollable.java
>
> And running queries with libpq:
> https://github.com/vinnix/testLibPQ/blob/master/testlibpq.c
>
> Is this possible to change something (I really don't know what or
> where) in the jdbc driver
> to get more direct aproach? (if that's make any sense to you guys...)
>
> Best regards,
>
> vinnix
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-26 06:34 Maciek Sakrejda <m.sakrejda@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Maciek Sakrejda @ 2012-07-26 06:34 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Vinicius Abrahao <vinnix.bsd@gmail.com>; Riaan van den Dool <rvddool@csir.co.za>; Brett Walker <brett.walker@geometryit.com>; pgsql-performance
> unfortunately postgres jdbc is bugged and does not honor the above for
> transaction control commands (begin, commit, etc). This patch
> http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch
> will fix it, assuming it hasn't been fixed in recent postgres jdbc.
Looks like it's still an issue:
https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/v3/QueryExecutorImpl.java#L426
Although I don't quite follow why it's an issue in the first
place--isn't the point to avoid creating a plan with parameter markers
but not actual parameter information? BEGIN, COMMIT, et al never have
markers in the first place. What am I missing?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-26 06:51 Maciek Sakrejda <m.sakrejda@gmail.com>
parent: Rural Hunter <ruralhunter@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Maciek Sakrejda @ 2012-07-26 06:51 UTC (permalink / raw)
To: Rural Hunter <ruralhunter@gmail.com>; +Cc: pgsql-performance
On Wed, Jul 25, 2012 at 7:13 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
> Why not just use simple Statement instead of PreparedStatement and construct
> the SQL with concated string or StringBuilder? like this:
> int col1=xxx;
> String col2="xxxx";
> String sql="select * from table where col1="+col+" and col2='"+col2+"'";
Ah, finally get to apply the old there's-an-xkcd-for-that rule here:
http://xkcd.com/327/
Or, more informatively: http://en.wikipedia.org/wiki/SQL_injection
Note that it's not completely crazy (in fact, the JDBC driver used to
this this forever ago): if you know what you're doing, you *can*
safely escape strings and avoid injection. But it's not for the faint
of heart.
Also, if you control the parameters and can verify that escaping is
not (and will never be) necessary over the domain of their possible
values, that's another option.
But in general, it's safer to let drivers worry about this.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Geoserver-PostGIS performance problems
@ 2012-07-26 13:32 Merlin Moncure <mmoncure@gmail.com>
parent: Maciek Sakrejda <m.sakrejda@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Merlin Moncure @ 2012-07-26 13:32 UTC (permalink / raw)
To: Maciek Sakrejda <m.sakrejda@gmail.com>; +Cc: Vinicius Abrahao <vinnix.bsd@gmail.com>; Riaan van den Dool <rvddool@csir.co.za>; Brett Walker <brett.walker@geometryit.com>; pgsql-performance
On Thu, Jul 26, 2012 at 1:34 AM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
>> unfortunately postgres jdbc is bugged and does not honor the above for
>> transaction control commands (begin, commit, etc). This patch
>> http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch
>> will fix it, assuming it hasn't been fixed in recent postgres jdbc.
>
> Looks like it's still an issue:
> https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/v3/QueryExecutorImpl.java#L426
>
> Although I don't quite follow why it's an issue in the first
> place--isn't the point to avoid creating a plan with parameter markers
> but not actual parameter information? BEGIN, COMMIT, et al never have
> markers in the first place. What am I missing?
This causes problems for connection poolers. (see;
http://pgbouncer.projects.postgresql.org/doc/faq.html#_disabling_prepared_statements_in_jdbc).
merlin
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2012-07-26 13:32 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-24 07:21 Geoserver-PostGIS performance problems Riaan van den Dool <rvddool@csir.co.za>
2012-07-24 07:54 ` Brett Walker <brett.walker@geometryit.com>
2012-07-24 08:10 ` Riaan van den Dool <rvddool@csir.co.za>
2012-07-24 15:50 ` Maciek Sakrejda <m.sakrejda@gmail.com>
2012-07-25 18:45 ` Merlin Moncure <mmoncure@gmail.com>
2012-07-25 19:17 ` Vinicius Abrahao <vinnix.bsd@gmail.com>
2012-07-25 19:26 ` Merlin Moncure <mmoncure@gmail.com>
2012-07-25 19:59 ` Vinicius Abrahao <vinnix.bsd@gmail.com>
2012-07-25 20:04 ` Merlin Moncure <mmoncure@gmail.com>
2012-07-26 06:34 ` Maciek Sakrejda <m.sakrejda@gmail.com>
2012-07-26 13:32 ` Merlin Moncure <mmoncure@gmail.com>
2012-07-26 02:13 ` Rural Hunter <ruralhunter@gmail.com>
2012-07-26 06:51 ` Maciek Sakrejda <m.sakrejda@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox