Thread: Curious behaviour with Execute Batch and Prepared Statements
I have a problem that I have described on stackoverflow. It is here: http://stackoverflow.com/questions/11167471/curious-behaviour-with-executebatch-and-prepared-statements
I don’t want to provide the detail here as I think it is too long.
Comments or questions are appreciated.
Brett
Brett Walker <brett.walker@geometryit.com>
Software Developer / Analyst
Geometry Pty Ltd
Telephone 03 6223 1999
Mobile 0458 498 386
Fax 03 6223 1988
Address 31 Salamanca Square, Battery Point, TAS 7004, Australia
Brett, Does it work with a recent version of postgresql ? 8.3 is quite old Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Sun, Jun 24, 2012 at 1:58 AM, Brett Walker <brett.walker@geometryit.com> wrote: > I have a problem that I have described on stackoverflow. It is here: > http://stackoverflow.com/questions/11167471/curious-behaviour-with-executebatch-and-prepared-statements > > > > I don’t want to provide the detail here as I think it is too long. > > > > Comments or questions are appreciated. > > > > Brett > > > > Brett Walker <brett.walker@geometryit.com> > > Software Developer / Analyst > > Geometry Pty Ltd > > > > Telephone 03 6223 1999 > > Mobile 0458 498 386 > > Fax 03 6223 1988 > > Web www.geometryit.com > > Address 31 Salamanca Square, Battery Point, > TAS 7004, Australia > >
Hi Dave, I'm aware that 8.3 is old. I was trying to run a regression test. If it is not supported on 8.3 that is fine but I cannot find any documentation to say that. I am using a recent JDBC driver version. Hoping that would cover it. Brett ________________________________________ From: davecramer@gmail.com [davecramer@gmail.com] On Behalf Of Dave Cramer [pg@fastcrypt.com] Sent: Sunday, 24 June 2012 9:09 PM To: Brett Walker Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Curious behaviour with Execute Batch and Prepared Statements Brett, Does it work with a recent version of postgresql ? 8.3 is quite old Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Sun, Jun 24, 2012 at 1:58 AM, Brett Walker <brett.walker@geometryit.com> wrote: > I have a problem that I have described on stackoverflow. It is here: > http://stackoverflow.com/questions/11167471/curious-behaviour-with-executebatch-and-prepared-statements > > > > I don’t want to provide the detail here as I think it is too long. > > > > Comments or questions are appreciated. > > > > Brett > > > > Brett Walker <brett.walker@geometryit.com> > > Software Developer / Analyst > > Geometry Pty Ltd > > > > Telephone 03 6223 1999 > > Mobile 0458 498 386 > > Fax 03 6223 1988 > > Web www.geometryit.com > > Address 31 Salamanca Square, Battery Point, > TAS 7004, Australia > >
Brett, Clearly we can't document every use case with every version of the driver and server. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Sun, Jun 24, 2012 at 7:26 AM, Brett Walker <brett.walker@geometryit.com> wrote: > Hi Dave, > > I'm aware that 8.3 is old. I was trying to run a regression test. > > If it is not supported on 8.3 that is fine but I cannot find any documentation to say that. > > I am using a recent JDBC driver version. Hoping that would cover it. > > Brett > ________________________________________ > From: davecramer@gmail.com [davecramer@gmail.com] On Behalf Of Dave Cramer [pg@fastcrypt.com] > Sent: Sunday, 24 June 2012 9:09 PM > To: Brett Walker > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] Curious behaviour with Execute Batch and Prepared Statements > > Brett, > > Does it work with a recent version of postgresql ? 8.3 is quite old > > Dave Cramer > > dave.cramer(at)credativ(dot)ca > http://www.credativ.ca > > > On Sun, Jun 24, 2012 at 1:58 AM, Brett Walker > <brett.walker@geometryit.com> wrote: >> I have a problem that I have described on stackoverflow. It is here: >> http://stackoverflow.com/questions/11167471/curious-behaviour-with-executebatch-and-prepared-statements >> >> >> >> I don’t want to provide the detail here as I think it is too long. >> >> >> >> Comments or questions are appreciated. >> >> >> >> Brett >> >> >> >> Brett Walker <brett.walker@geometryit.com> >> >> Software Developer / Analyst >> >> Geometry Pty Ltd >> >> >> >> Telephone 03 6223 1999 >> >> Mobile 0458 498 386 >> >> Fax 03 6223 1988 >> >> Web www.geometryit.com >> >> Address 31 Salamanca Square, Battery Point, >> TAS 7004, Australia >> >> > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
Hi Dave, Is it your feelling that PostgreSQL 8.3 can't support this functionality, even with a recent JDBC driver? It's fine if it can't. Some sort of error Java side or warning in the db log would have been helpful. I was getting nothingonly a curious side effect. It didn't make sense. Brett ________________________________________ From: davecramer@gmail.com [davecramer@gmail.com] On Behalf Of Dave Cramer [pg@fastcrypt.com] Sent: Sunday, 24 June 2012 11:22 PM To: Brett Walker Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Curious behaviour with Execute Batch and Prepared Statements Brett, Clearly we can't document every use case with every version of the driver and server. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Sun, Jun 24, 2012 at 7:26 AM, Brett Walker <brett.walker@geometryit.com> wrote: > Hi Dave, > > I'm aware that 8.3 is old. I was trying to run a regression test. > > If it is not supported on 8.3 that is fine but I cannot find any documentation to say that. > > I am using a recent JDBC driver version. Hoping that would cover it. > > Brett > ________________________________________ > From: davecramer@gmail.com [davecramer@gmail.com] On Behalf Of Dave Cramer [pg@fastcrypt.com] > Sent: Sunday, 24 June 2012 9:09 PM > To: Brett Walker > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] Curious behaviour with Execute Batch and Prepared Statements > > Brett, > > Does it work with a recent version of postgresql ? 8.3 is quite old > > Dave Cramer > > dave.cramer(at)credativ(dot)ca > http://www.credativ.ca > > > On Sun, Jun 24, 2012 at 1:58 AM, Brett Walker > <brett.walker@geometryit.com> wrote: >> I have a problem that I have described on stackoverflow. It is here: >> http://stackoverflow.com/questions/11167471/curious-behaviour-with-executebatch-and-prepared-statements >> >> >> >> I don’t want to provide the detail here as I think it is too long. >> >> >> >> Comments or questions are appreciated. >> >> >> >> Brett >> >> >> >> Brett Walker <brett.walker@geometryit.com> >> >> Software Developer / Analyst >> >> Geometry Pty Ltd >> >> >> >> Telephone 03 6223 1999 >> >> Mobile 0458 498 386 >> >> Fax 03 6223 1988 >> >> Web www.geometryit.com >> >> Address 31 Salamanca Square, Battery Point, >> TAS 7004, Australia >> >> > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
On 06/24/2012 01:58 PM, Brett Walker wrote:
I have a problem that I have described on stackoverflow. It is here: http://stackoverflow.com/questions/11167471/curious-behaviour-with-executebatch-and-prepared-statements
OK, so you've set log_statement = 'all' and restarted or reloaded Pg, re-run your test, and you still don't see the statement hitting the DB? You've possibly found a bug. Now you need to narrow it down.
Does this fail if you run the same statement against 9.1? ie is it specifically a PgJDBC 9.1 vs PostgreSQL 8.3 issue? Or does it happen irrespective of the version of PostgreSQL being targeted? Does the issue exist in older versions of PgJDBC too?
Try enabling detailed logging in PgJDBC and see if you can make out what's going wrong. See: http://jdbc.postgresql.org/documentation/91/connect.html#connection-parameters and 'loglevel' .Set loglevel = 2 in the JDBC driver and re-run your tests.
If that doesn't help: Grab the PgJDBC sources. Attach a debugger to the test and step through the problem statement execution. Watch what PgJDBC is doing, see where it goes wrong.
If you don't want to do all that: Create a self-contained test case as a standalone Java program (source, ant or maven build, all dependencies, etc) that can simply be downloaded and run against a clean PostgreSQL database to demonstrate your problem. You'll want to provide a .sql script to set up the test environment by populating the database too.
--
Craig Ringer
Thanks Craig. It was a bug. After spending a day away from the code and then looking at it for half an hour all refreshed I saw the bugthat I had introduced. See the stackoverflow question. Thanks agian for all your comments. Brett ________________________________________ From: Craig Ringer [ringerc@ringerc.id.au] Sent: Monday, 25 June 2012 2:34 PM To: Brett Walker Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Curious behaviour with Execute Batch and Prepared Statements On 06/24/2012 01:58 PM, Brett Walker wrote: I have a problem that I have described on stackoverflow. It is here: http://stackoverflow.com/questions/11167471/curious-behaviour-with-executebatch-and-prepared-statements OK, so you've set log_statement = 'all' and restarted or reloaded Pg, re-run your test, and you still don't see the statementhitting the DB? You've possibly found a bug. Now you need to narrow it down. Does this fail if you run the same statement against 9.1? ie is it specifically a PgJDBC 9.1 vs PostgreSQL 8.3 issue? Ordoes it happen irrespective of the version of PostgreSQL being targeted? Does the issue exist in older versions of PgJDBCtoo? Try enabling detailed logging in PgJDBC and see if you can make out what's going wrong. See: http://jdbc.postgresql.org/documentation/91/connect.html#connection-parametersand 'loglevel' .Set loglevel = 2 in the JDBCdriver and re-run your tests. If that doesn't help: Grab the PgJDBC sources. Attach a debugger to the test and step through the problem statement execution.Watch what PgJDBC is doing, see where it goes wrong. If you don't want to do all that: Create a self-contained test case as a standalone Java program (source, ant or maven build,all dependencies, etc) that can simply be downloaded and run against a clean PostgreSQL database to demonstrate yourproblem. You'll want to provide a .sql script to set up the test environment by populating the database too. -- Craig Ringer