Thread: What do people use for connection pooling with PostgreSQL JDBC
The specific solution should work well with - hibernate - pure JDBC with postgresql specific types and arrays Do you use something inside java or do you just use stuff like pgpool or pgbouncer ? -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
dbcp, or c3po, however pgbouncer has it's merits too if you have a large number of app servers hitting the same db server.
Dave
Dave
On Thu, Mar 5, 2009 at 3:31 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
The specific solution should work well with
- hibernate
- pure JDBC with postgresql specific types and arrays
Do you use something inside java or do you just use stuff like pgpool or
pgbouncer ?
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On Thu, Mar 5, 2009 at 2:13 PM, Dave Cramer <pg@fastcrypt.com> wrote: > dbcp, or c3po, however pgbouncer has it's merits too if you have a large > number of app servers hitting the same db server. Same here, c3p0 more often than dbcp. We use pgbouncer only for the big PHP platforms. -- Guillaume
On Thu, 2009-03-05 at 08:13 -0500, Dave Cramer wrote: > dbcp, or c3po, however pgbouncer has it's merits too if you have a > large number of app servers hitting the same db server. It seems that neither dbcp not c3po does not support Arrays ? How have you dealt with that ? > Dave > > On Thu, Mar 5, 2009 at 3:31 AM, Hannu Krosing <hannu@2ndquadrant.com> > wrote: > The specific solution should work well with > > - hibernate > - pure JDBC with postgresql specific types and arrays > > Do you use something inside java or do you just use stuff like > pgpool or > pgbouncer ? > > > -- > Hannu Krosing http://www.2ndQuadrant.com > PostgreSQL Scalability and Availability > Services, Consulting and Training > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc > -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
huh ? What makes you think it doesn't deal with arrays ?
Dave
Dave
On Mon, Apr 6, 2009 at 7:13 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
On Thu, 2009-03-05 at 08:13 -0500, Dave Cramer wrote:It seems that neither dbcp not c3po does not support Arrays ?
> dbcp, or c3po, however pgbouncer has it's merits too if you have a
> large number of app servers hitting the same db server.
How have you dealt with that ?--
> Dave
>
> On Thu, Mar 5, 2009 at 3:31 AM, Hannu Krosing <hannu@2ndquadrant.com>
> wrote:
> The specific solution should work well with
>
> - hibernate
> - pure JDBC with postgresql specific types and arrays
>
> Do you use something inside java or do you just use stuff like
> pgpool or
> pgbouncer ?
>
>
> --
> Hannu Krosing http://www.2ndQuadrant.com
> PostgreSQL Scalability and Availability
> Services, Consulting and Training
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
On Mon, 2009-04-06 at 07:43 -0400, Dave Cramer wrote:
> huh ? What makes you think it doesn't deal with arrays ?
I have a sample function, whit takes an array argument
CREATE OR REPLACE FUNCTION arraySum(
multiplier bigint,
valuelist bigint[],
OUT result int
) AS
$$
BEGIN
result = 0;
FOR idx IN array_lower(valuelist, 1)..array_upper(valuelist, 1) LOOP
result = result + multiplier * valuelist[idx];
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
When I call this function directly via JDBC or using proxool I get a
result, when doing it via c3p0 or dbcp I get an exception -
Long[] array = { 1L, 2L };
PreparedStatement ps = conn.prepareStatement(
"SELECT * FROM arraySum(?, ?)");
ps.setLong(1, 3L);
ps.setArray(2, conn.createArrayOf("bigint", array));
ps.execute();
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
On Tue, Apr 7, 2009 at 4:35 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
OK, createArrayOf is a relatively new method. So .... you have a few ways around this.
1) don't use it, create your own arrays.
2) fix the dbcp code
3) complain to dbcp people
On Mon, 2009-04-06 at 07:43 -0400, Dave Cramer wrote:I have a sample function, whit takes an array argument
> huh ? What makes you think it doesn't deal with arrays ?
CREATE OR REPLACE FUNCTION arraySum(
multiplier bigint,
valuelist bigint[],
OUT result int
) AS
$$
BEGIN
result = 0;
FOR idx IN array_lower(valuelist, 1)..array_upper(valuelist, 1) LOOP
result = result + multiplier * valuelist[idx];
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
When I call this function directly via JDBC or using proxool I get a
result, when doing it via c3p0 or dbcp I get an exception -
Long[] array = { 1L, 2L };
PreparedStatement ps = conn.prepareStatement(
"SELECT * FROM arraySum(?, ?)");
ps.setLong(1, 3L);
ps.setArray(2, conn.createArrayOf("bigint", array));
ps.execute();
OK, createArrayOf is a relatively new method. So .... you have a few ways around this.
1) don't use it, create your own arrays.
2) fix the dbcp code
3) complain to dbcp people
--Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
I think you can extract the underlying PGConnection object by calling conn.getInnerMostDelegate() (sp?) method and then do your array stuff. Your dbcp datasource has to allow access to underlying JDBC objects however (there was a property to allow this).
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dave Cramer
Sent: Tuesday, April 07, 2009 1:24 PM
To: Hannu Krosing
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] What do people use for connection pooling with PostgreSQL JDBC
On Tue, Apr 7, 2009 at 4:35 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
On Mon, 2009-04-06 at 07:43 -0400, Dave Cramer wrote:
> huh ? What makes you think it doesn't deal with arrays ?
I have a sample function, whit takes an array argument
CREATE OR REPLACE FUNCTION arraySum(
multiplier bigint,
valuelist bigint[],
OUT result int
) AS
$$
BEGIN
result = 0;
FOR idx IN array_lower(valuelist, 1)..array_upper(valuelist, 1) LOOP
result = result + multiplier * valuelist[idx];
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
When I call this function directly via JDBC or using proxool I get a
result, when doing it via c3p0 or dbcp I get an exception -
Long[] array = { 1L, 2L };
PreparedStatement ps = conn.prepareStatement(
"SELECT * FROM arraySum(?, ?)");
ps.setLong(1, 3L);
ps.setArray(2, conn.createArrayOf("bigint", array));
ps.execute();
OK, createArrayOf is a relatively new method. So .... you have a few ways around this.
1) don't use it, create your own arrays.
2) fix the dbcp code
3) complain to dbcp people
--Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
On Tue, 2009-04-07 at 06:24 -0400, Dave Cramer wrote:
>
>
> On Tue, Apr 7, 2009 at 4:35 AM, Hannu Krosing <hannu@2ndquadrant.com>
> wrote:
> On Mon, 2009-04-06 at 07:43 -0400, Dave Cramer wrote:
> > huh ? What makes you think it doesn't deal with arrays ?
>
>
> I have a sample function, whit takes an array argument
>
> CREATE OR REPLACE FUNCTION arraySum(
> multiplier bigint,
> valuelist bigint[],
> OUT result int
> ) AS
> $$
> BEGIN
> result = 0;
> FOR idx IN array_lower(valuelist,
> 1)..array_upper(valuelist, 1) LOOP
> result = result + multiplier * valuelist[idx];
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql SECURITY DEFINER;
>
>
> When I call this function directly via JDBC or using proxool I
> get a
> result, when doing it via c3p0 or dbcp I get an exception -
>
> Long[] array = { 1L, 2L };
> PreparedStatement ps = conn.prepareStatement(
> "SELECT * FROM arraySum(?, ?)");
> ps.setLong(1, 3L);
> ps.setArray(2, conn.createArrayOf("bigint", array));
> ps.execute();
>
> OK, createArrayOf is a relatively new method. So .... you have a few
> ways around this.
>
> 1) don't use it, create your own arrays.
> 2) fix the dbcp code
> 3) complain to dbcp people
It's both dbcp and c3po.
I understand that dbcp and c3po work on some other databases jdbc
drivers, so maybe I could also do
4) fix jdbc code to provide required createArrayOf support
no ?
>
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
Hannu Krosing wrote: > I understand that dbcp and c3po work on some other databases jdbc > drivers, so maybe I could also do > > 4) fix jdbc code to provide required createArrayOf support > > no ? You could start by showing us the actual exception that you get? -O
On Tue, Apr 7, 2009 at 9:23 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
Well, the newer drivers support createArray, so perhaps you need a newer driver ?
As well as showing us the exception.
It's both dbcp and c3po.On Tue, 2009-04-07 at 06:24 -0400, Dave Cramer wrote:
>
>
> On Tue, Apr 7, 2009 at 4:35 AM, Hannu Krosing <hannu@2ndquadrant.com>
> wrote:
> On Mon, 2009-04-06 at 07:43 -0400, Dave Cramer wrote:
> > huh ? What makes you think it doesn't deal with arrays ?
>
>
> I have a sample function, whit takes an array argument
>
> CREATE OR REPLACE FUNCTION arraySum(
> multiplier bigint,
> valuelist bigint[],
> OUT result int
> ) AS
> $$
> BEGIN
> result = 0;
> FOR idx IN array_lower(valuelist,
> 1)..array_upper(valuelist, 1) LOOP
> result = result + multiplier * valuelist[idx];
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql SECURITY DEFINER;
>
>
> When I call this function directly via JDBC or using proxool I
> get a
> result, when doing it via c3p0 or dbcp I get an exception -
>
> Long[] array = { 1L, 2L };
> PreparedStatement ps = conn.prepareStatement(
> "SELECT * FROM arraySum(?, ?)");
> ps.setLong(1, 3L);
> ps.setArray(2, conn.createArrayOf("bigint", array));
> ps.execute();
>
> OK, createArrayOf is a relatively new method. So .... you have a few
> ways around this.
>
> 1) don't use it, create your own arrays.
> 2) fix the dbcp code
> 3) complain to dbcp people
I understand that dbcp and c3po work on some other databases jdbc
drivers, so maybe I could also do
4) fix jdbc code to provide required createArrayOf support
no ?
Well, the newer drivers support createArray, so perhaps you need a newer driver ?
As well as showing us the exception.
>
--Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
On Wed, 2009-04-08 at 01:25 +1200, Oliver Jowett wrote: > Hannu Krosing wrote: > > > I understand that dbcp and c3po work on some other databases jdbc > > drivers, so maybe I could also do > > > > 4) fix jdbc code to provide required createArrayOf support > > > > no ? > > You could start by showing us the actual exception that you get? for dbcp - java.lang.AbstractMethodError: org.apache.tomcat.dbcp.dbcp.PoolingDataSource $PoolGuardConnectionWrapper.createArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array; for c3p0 - java.lang.AbstractMethodError: com.mchange.v2.c3p0.impl.NewProxyConnection.createArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array; > -O > -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
On Tue, 2009-04-07 at 09:39 -0400, Dave Cramer wrote:
>
>
> On Tue, Apr 7, 2009 at 9:23 AM, Hannu Krosing <hannu@2ndquadrant.com>
> wrote:
>
> On Tue, 2009-04-07 at 06:24 -0400, Dave Cramer wrote:
> >
> >
> > On Tue, Apr 7, 2009 at 4:35 AM, Hannu Krosing
> <hannu@2ndquadrant.com>
> > wrote:
> > On Mon, 2009-04-06 at 07:43 -0400, Dave Cramer
> wrote:
> > > huh ? What makes you think it doesn't deal with
> arrays ?
> >
> >
> > I have a sample function, whit takes an array
> argument
> >
> > CREATE OR REPLACE FUNCTION arraySum(
> > multiplier bigint,
> > valuelist bigint[],
> > OUT result int
> > ) AS
> > $$
> > BEGIN
> > result = 0;
> > FOR idx IN array_lower(valuelist,
> > 1)..array_upper(valuelist, 1) LOOP
> > result = result + multiplier *
> valuelist[idx];
> > END LOOP;
> > RETURN;
> > END;
> > $$ LANGUAGE plpgsql SECURITY DEFINER;
> >
> >
> > When I call this function directly via JDBC or using
> proxool I
> > get a
> > result, when doing it via c3p0 or dbcp I get an
> exception -
> >
> > Long[] array = { 1L, 2L };
> > PreparedStatement ps = conn.prepareStatement(
> > "SELECT * FROM arraySum(?, ?)");
> > ps.setLong(1, 3L);
> > ps.setArray(2, conn.createArrayOf("bigint",
> array));
> > ps.execute();
> >
> > OK, createArrayOf is a relatively new method. So .... you
> have a few
> > ways around this.
> >
> > 1) don't use it, create your own arrays.
> > 2) fix the dbcp code
> > 3) complain to dbcp people
>
>
> It's both dbcp and c3po.
>
> I understand that dbcp and c3po work on some other databases
> jdbc
> drivers, so maybe I could also do
>
> 4) fix jdbc code to provide required createArrayOf support
>
> no ?
>
> Well, the newer drivers support createArray, so perhaps you need a
> newer driver ?
As I said, yhis works when I use JDBC directly, or proxool connection
proxy
> As well as showing us the exception.
for dbcp -
java.lang.AbstractMethodError:
org.apache.tomcat.dbcp.dbcp.PoolingDataSource
$PoolGuardConnectionWrapper.createArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array;
for c3p0 -
java.lang.AbstractMethodError:
com.mchange.v2.c3p0.impl.NewProxyConnection.createArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array;
Servlet code to test direct, proxool, c3po and dbcp attached
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
Attachment
Hannu Krosing wrote: > On Wed, 2009-04-08 at 01:25 +1200, Oliver Jowett wrote: >> Hannu Krosing wrote: >> >>> I understand that dbcp and c3po work on some other databases jdbc >>> drivers, so maybe I could also do >>> >>> 4) fix jdbc code to provide required createArrayOf support >>> >>> no ? >> You could start by showing us the actual exception that you get? > > for dbcp - > > java.lang.AbstractMethodError: > org.apache.tomcat.dbcp.dbcp.PoolingDataSource > $PoolGuardConnectionWrapper.createArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array; > > > for c3p0 - > > java.lang.AbstractMethodError: > com.mchange.v2.c3p0.impl.NewProxyConnection.createArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array; That means that neither dbcp nor c3po's connection wrapper classes implement createArrayOf() (i.e. they were compiled against an earlier JDBC specification that lacks those methods, but your runtime environment is more recent and you are trying to use those methods from your app). It is a problem with the connection pool wrapper code, not the PostgreSQL JDBC driver. Perhaps you need to explicitly build DBCP/C3PO against a newer JDBC release? Or perhaps they just don't support those methods yet. -O