Re: 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

On Thu, Mar 5, 2009 at 3:31 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)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(at)postgresql(dot)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(at)fastcrypt(dot)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(at)2ndquadrant(dot)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(at)postgresql(dot)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

On Mon, Apr 6, 2009 at 7:13 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:

> 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(at)2ndquadrant(dot)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(at)postgresql(dot)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(at)2ndquadrant(dot)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
>
>

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(at)postgresql(dot)org [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Dave Cramer
Sent: Tuesday, April 07, 2009 1:24 PM
To: Hannu Krosing
Cc: pgsql-jdbc(at)postgresql(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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 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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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

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