Thread: JDBC broken connection against Postgres 8.4

JDBC broken connection against Postgres 8.4

From
Michael Bell
Date:
As you see below, something seems....whacked. A simple loop with an unpooled connection (code provided below) often
givesan exception when connecting. The Thread.sleep statement has been made as high as 5000 ms, and exceptions still
sporadicallyoccur. 

I don't know if this is a JDBC driver issue or core server issue. Or something here. But the code is so simple....


Single box test (client, server on same machine, accessing 127.0.0.1)
Postgesql 8.4.01
JDBC3 8.4.701 (also tried 8.3.603)
Sun JDK 1.5_16
Windows XP SP3, 2GB RAM, 64MB-1GB heap

Exception in thread "main" org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:137)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:124)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:386)
    at org.postgresql.Driver.connect(Driver.java:260)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at com.gwava.db.DerbyConnectionPool.main(DerbyConnectionPool.java:193)
Caused by: java.net.SocketException: Connection reset
    at java.net.SocketInputStream.read(Unknown Source)
    at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
    at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:254)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:95)
    ... 9 more


Code to reproduce:

 public static void main(String[] args) throws SQLException, InstantiationException, IllegalAccessException,
ClassNotFoundException,InterruptedException { 

        Connection con=null;
        Class.forName("org.postgresql.Driver").newInstance();
        int count=0;
        while (count < 250) {
        try {
            System.out.println(count);
            con= DriverManager.getConnection("jdbc:postgresql://localhost/retain","mjb","turnip");
        } finally {
            if (con != null) con.close();
        }
        Thread.sleep(2);
        count++;
        }
    }





Re: JDBC broken connection against Postgres 8.4

From
dmp
Date:
>
>
>As you see below, something seems....whacked. A simple loop with an unpooled connection (code provided below) often
givesan exception when connecting. The Thread.sleep statement has been made as high as 5000 ms, and exceptions still
sporadicallyoccur. 
>
>I don't know if this is a JDBC driver issue or core server issue. Or something here. But the code is so simple....
>
>
>Single box test (client, server on same machine, accessing 127.0.0.1)
>Postgesql 8.4.01
>JDBC3 8.4.701 (also tried 8.3.603)
>Sun JDK 1.5_16
>Windows XP SP3, 2GB RAM, 64MB-1GB heap
>
>Code to reproduce:
>
> public static void main(String[] args) throws SQLException, InstantiationException, IllegalAccessException,
ClassNotFoundException,InterruptedException { 
>
>        Connection con=null;
>        Class.forName("org.postgresql.Driver").newInstance();
>        int count=0;
>        while (count < 250) {
>        try {
>            System.out.println(count);
>            con= DriverManager.getConnection("jdbc:postgresql://localhost/retain","mjb","turnip");
>        } finally {
>            if (con != null) con.close();
>        }
>        Thread.sleep(2);
>        count++;
>        }
>    }
>

I'm unable to reproduce the exception on a common client with the two
PostgreSQL
8.3.x database configurations, one local the other on the network,
listed below.

All linux configurations

Client:

java version "1.5.0_12"
jdbc version: "postgresql-8.3-603"

Database Hosts:

Host: 127.0.0.1
-- Generated On: 2009.08.17 AD at 09:24:16 MDT
-- SQL version: PostgreSQL 8.3.3
-- Database: world

-- Host: cindy.dandymadeproductions.net
-- Generated On: 2009.08.17 AD at 09:10:41 MDT
-- SQL version: PostgreSQL 8.3.0
-- Database: world

danap






Re: JDBC broken connection against Postgres 8.4

From
Michael Bell
Date:
I am not surprised. I strongly suspect this is an issue with Windows/PostgresSQL. OTOH, I did most of my testing
againstPostgres 8.4 server. 



----- Original Message ----
From: dmp <danap@ttc-cmc.net>
To: pgsql-jdbc@postgresql.org
Cc: Michael Bell <mikebell90@yahoo.com>
Sent: Monday, August 17, 2009 8:48:41 AM
Subject: Re: [JDBC] JDBC broken connection against Postgres 8.4

>
>
>As you see below, something seems....whacked. A simple loop with an unpooled connection (code provided below) often
givesan exception when connecting. The Thread.sleep statement has been made as high as 5000 ms, and exceptions still
sporadicallyoccur. 
>
>I don't know if this is a JDBC driver issue or core server issue. Or something here. But the code is so simple....
>
>
>Single box test (client, server on same machine, accessing 127.0.0.1)
>Postgesql 8.4.01
>JDBC3 8.4.701 (also tried 8.3.603)
>Sun JDK 1.5_16
>Windows XP SP3, 2GB RAM, 64MB-1GB heap
>
>Code to reproduce:
>
> public static void main(String[] args) throws SQLException, InstantiationException, IllegalAccessException,
ClassNotFoundException,InterruptedException { 
>
>        Connection con=null;
>        Class.forName("org.postgresql.Driver").newInstance();
>        int count=0;
>        while (count < 250) {
>        try {
>            System.out.println(count);
>            con= DriverManager.getConnection("jdbc:postgresql://localhost/retain","mjb","turnip");
>        } finally {
>            if (con != null) con.close();
>        }
>        Thread.sleep(2);
>        count++;
>        }
>    }
>

I'm unable to reproduce the exception on a common client with the two
PostgreSQL
8.3.x database configurations, one local the other on the network,
listed below.

All linux configurations

Client:

java version "1.5.0_12"
jdbc version: "postgresql-8.3-603"

Database Hosts:

Host: 127.0.0.1
-- Generated On: 2009.08.17 AD at 09:24:16 MDT
-- SQL version: PostgreSQL 8.3.3
-- Database: world

-- Host: cindy.dandymadeproductions.net
-- Generated On: 2009.08.17 AD at 09:10:41 MDT
-- SQL version: PostgreSQL 8.3.0
-- Database: world

danap






--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc






Re: JDBC broken connection against Postgres 8.4

From
jerome moliere
Date:


2009/8/17 Michael Bell <mikebell90@yahoo.com>
I am not surprised. I strongly suspect this is an issue with Windows/PostgresSQL. OTOH, I did most of my testing against Postgres 8.4 server.


Hi all,
coul you fine tune your TCP/IP stack, trying to set a lower value for the TIME-WAIT paremeter

HTH
Jerome

--
Jerome Moliere - Mentor/J
http://romjethoughts.blogspot.com/
auteur Eyrolles

Re: JDBC broken connection against Postgres 8.4

From
Michael Bell
Date:
Probably, but I'm hesistant to for several reasons

1. End users will cry. :) This is part of commercial product. Of course they cry more if current condition is there, so that's kind of lame argument.,
2. I don't know TCP tuning much, but it seems to me your suggestion is a very good one if the code I posted was typical of usage. It isn't - it was just an easy way to expose the issue. Typical usage is with a Connection pool, initially set to 5 connections. And I'm getting issues with my single box test, which is hardly a load. Since in this scenario the JDBC Connections are pooled and stick around "forever" (or at least a long time w/o network issues/timeouts), I wouldn't think TIME-WAIT is relevant, right? That's only relevant to opening and tearing down tcp connections in rapid succession (eg like in my loop). Or am I showing the limits of my tcpip knowledge?

For now I'm' going to try to figure out the wire trace issues.

Keep in mind this is not just me :) . On this list back in Feb, some one had the exact same issue. And about a year ago a possibly similar issue (it was blocking on input indefinitely in the same code area, which is worse really)

Does anyone really know of some one who has tested this driver/server combo highly on Windows ?










From: jerome moliere <jerome.moliere@gmail.com>
To: Michael Bell <mikebell90@yahoo.com>
Cc: dmp <danap@ttc-cmc.net>; pgsql-jdbc@postgresql.org
Sent: Tuesday, August 18, 2009 4:56:57 AM
Subject: Re: [JDBC] JDBC broken connection against Postgres 8.4



2009/8/17 Michael Bell <mikebell90@yahoo.com>
I am not surprised. I strongly suspect this is an issue with Windows/PostgresSQL. OTOH, I did most of my testing against Postgres 8.4 server.


Hi all,
coul you fine tune your TCP/IP stack, trying to set a lower value for the TIME-WAIT paremeter

HTH
Jerome

--
Jerome Moliere - Mentor/J
http://romjethoughts.blogspot.com/
auteur Eyrolles

Re: JDBC broken connection against Postgres 8.4

From
Michael Bell
Date:
The other reasons I find this unlikely to be relevant

1. The sleep interval has been as high as 5 seconds, which is a lot of sleeping, and it still occurs. Less often, but it occurs
2. MySQL on the same box has absolutely no issues


From: jerome moliere <jerome.moliere@gmail.com>
To: Michael Bell <mikebell90@yahoo.com>
Cc: dmp <danap@ttc-cmc.net>; pgsql-jdbc@postgresql.org
Sent: Tuesday, August 18, 2009 4:56:57 AM
Subject: Re: [JDBC] JDBC broken connection against Postgres 8.4



2009/8/17 Michael Bell <mikebell90@yahoo.com>
I am not surprised. I strongly suspect this is an issue with Windows/PostgresSQL. OTOH, I did most of my testing against Postgres 8.4 server.


Hi all,
coul you fine tune your TCP/IP stack, trying to set a lower value for the TIME-WAIT paremeter

HTH
Jerome

--
Jerome Moliere - Mentor/J
http://romjethoughts.blogspot.com/
auteur Eyrolles

Re: JDBC broken connection against Postgres 8.4

From
dmp
Date:
> As you see below, something seems....whacked. A simple loop with an
> unpooled connection (code provided below) often gives an exception
> when connecting. The Thread.sleep statement has been made as high as
> 5000 ms, and exceptions still sporadically occur.
>
> I don't know if this is a JDBC driver issue or core server issue. Or
> something here. But the code is so simple....
>
> Single box test (client, server on same machine, accessing 127.0.0.1)
> Postgesql 8.4.01
> JDBC3 8.4.701 (also tried 8.3.603)
> Sun JDK 1.5_16
> Windows XP SP3, 2GB RAM, 64MB-1GB heap
>
> Exception in thread "main" org.postgresql.util.PSQLException: The
> connection attempt failed.
>     at
> org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:137)
>     at
> org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
>     at
> org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:124)
>     at
> org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
>     at
> org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
>     at org.postgresql.Driver.makeConnection(Driver.java:386)
>     at org.postgresql.Driver.connect(Driver.java:260)
>     at java.sql.DriverManager.getConnection(Unknown Source)
>     at java.sql.DriverManager.getConnection(Unknown Source)
>     at com.gwava.db.DerbyConnectionPool.main(DerbyConnectionPool.java:193)
> Caused by: java.net.SocketException: Connection reset
>     at java.net.SocketInputStream.read(Unknown Source)

>
> I'm unable to reproduce the exception on a common client with the two
> PostgreSQL 8.3.x database configurations, one local the other on the
> network, listed below.
>
> All linux configurations
>
> Client:
>
> java version "1.5.0_12"
> jdbc version: "postgresql-8.3-603"
>
> Database Hosts:
>
> Host: 127.0.0.1
> -- Generated On: 2009.08.17 AD at 09:24:16 MDT
> -- SQL version: PostgreSQL 8.3.3
> -- Database: world
>
> -- Host: cindy.dandymadeproductions.net
> -- Generated On: 2009.08.17 AD at 09:10:41 MDT
> -- SQL version: PostgreSQL 8.3.0
> -- Database: world
>
> danap
>
> 2009/8/17 Michael Bell <mikebell90@yahoo.com>
>
> I am not surprised. I strongly suspect this is an issue with
> Windows/PostgresSQL. OTOH, I did most of my testing against Postgres 8.4
> server.


I'm still unable to reproduce the exception on the given configuration
when the JDBC has
been upgraded to postgresql-8.4-701.jdbc3. I will upgrade to PostgreSQL
8.4 tomorrow,
been meaning to, on one of the systems and try again. I realize you are
talking about 8.4
and what I was trying to do is provide an elimination of one or more
variables to isolate
the problem, so the experts here could focus, which I believe they may
have already done. I
also understand that mine are Linux and your is windows and you believe
there lies the possible
whack so to speak. Couple of things though:

1. The original test code you provided I believe is not doing exactly
what you think. con is never
     NULL besides the first time through the loop, which the test to
close the connection is taking
    place no matter whether a connection was established or not. You
always close the connection,
    Suggest you try:

    } finally {
            if (con != null)
            {
                con.close();
                con = null;
            }

    I know this is probably be nothing, but your code is just not doing
what you set it up to do.


2. If you say your connection is not pooled, why in your original
exception trace, quoted above, is:

-> at com.gwava.db.DerbyConnectionPool.main(DerbyConnectionPool.java:193)

danap

Re: JDBC broken connection against Postgres 8.4

From
Michael Bell
Date:

>I'm still unable to reproduce the exception on the given configuration when the JDBC has
>been upgraded to postgresql-8.4-701.jdbc3. I will upgrade to PostgreSQL 8.4 tomorrow,
>been meaning to, on one of the systems and try again. I realize you are talking about 8.4
>and what I was trying to do is provide an elimination of one or more variables to isolate
>the problem, so the experts here could focus, which I believe they may have already done. I
>also understand that mine are Linux and your is windows and you believe there lies the possible
>whack so to speak. Couple of things though:

Yup.. Thanks. I tried both the 8.3 and 8.4 jdbc but only against 8.4. Note that a post in Feb DID go against 8.3 and he
hadthe same issue (search archives for connection: reset) 


1. The original test code you provided I believe is not doing exactly what you think. con is never
    NULL besides the first time through the loop, which the test to close the connection is taking
   place no matter whether a connection was established or not. You always close the connection,
   Suggest you try:

   } finally {
           if (con != null)
           {
               con.close();
               con = null;
           }

   I know this is probably be nothing, but your code is just not doing what you set it up to do.

Actually it is deliberately that way. I am deliberately creating a new connection and closing in a loop. (eg 250 new
connectionspaired by closes) Am I being spacy and missing something? :) 
Otherwise you would be creating a new Connection and relying on garbage collection to clean up the old deferenced
connectionobject. (admittedly this matters not for this dumb test loop) 

Also note that the Thread.sleep(2) was originally Thread.sleep(5000). That still triggers the exception, maybe slightly
furtherin the loop. 

2. If you say your connection is not pooled, why in your original exception trace, quoted above, is:

-> at com.gwava.db.DerbyConnectionPool.main(DerbyConnectionPool.java:193)

Laziness. That just happened to be the class where I wrote that method, for boring and unrelated reasons*. Ok, mostly
justlaziness. Note that I originally SAW this issue when running Postgres through Hibernate 3.3.1, using C3P0 .9.1.2. I
thensaid "well hey I'll eliminate Hibernate and the Connection pool as variables and just use raw JDBC to clean it up
tothat. 

*warning: boring and unedifying explanation: I had a static method called getCon() to call to get the unpooled
connectionin this class, so I used that. I then simplified the code to a single main method with no alien (well non
jdk)methods to make it easier to post. Both had the same results. Ironically one of the reasons I did so was to avoid
someonewondering if I was using the cpool :) 

danap






Re: JDBC broken connection against Postgres 8.4

From
dmp
Date:
> I'm still unable to reproduce the exception on the given configuration
> when the JDBC has
> been upgraded to postgresql-8.4-701.jdbc3. I will upgrade to
> PostgreSQL 8.4 tomorrow,
>
> danap.
>
> Yup.. Thanks. I tried both the 8.3 and 8.4 jdbc but only against 8.4.
> Note that a post in Feb DID go against 8.3 and he had the same issue
> (search archives for connection: reset)


Updated a server configuration today to 8.4.0. Still unable to reproduce
on a windows and
linux clients accessing server with the exact test code provided. I know
this is not your
XP/localhost configuration. I see no sense in setting up PostgreSQL on
XP for my needs
so this is all I can do to help.

Client 1, Linux:

java version "1.5.0_12"
jdbc version: "postgresql-8.4-701 JDBC3"

Client 2, XP:

java version "1.6.0"
jdbc version "postgresql-8.4-701 JDBC3"

Database Hosts:

-- Host: cindy.dandymadeproductions.com
-- Generated On: 2009.08.19 AD at 12:29:25 MDT
-- SQL version: PostgreSQL 8.4.0
-- Database: world

danap

Re: JDBC broken connection against Postgres 8.4

From
Віталій Тимчишин
Date:
May be, you have a firewall software that is working and can't keep up with all this connections opened over loopback?

2009/8/17 Michael Bell <mikebell90@yahoo.com>
As you see below, something seems....whacked. A simple loop with an unpooled connection (code provided below) often gives an exception when connecting. The Thread.sleep statement has been made as high as 5000 ms, and exceptions still sporadically occur.

I don't know if this is a JDBC driver issue or core server issue. Or something here. But the code is so simple....

Re: JDBC broken connection against Postgres 8.4

From
"Donald Fraser"
Date:
I note from your original email that your test platform is Windows XP.
Please check the system event logs for ID 4226.
If you see this message then you have a TCP connection limit issue causing
problems.


----- Original Message -----
From: ??????? ????????
To: Michael Bell
Cc: pgsql-jdbc@postgresql.org
Sent: Thursday, August 20, 2009 1:09 PM
Subject: Re: [JDBC] JDBC broken connection against Postgres 8.4


May be, you have a firewall software that is working and can't keep up with
all this connections opened over loopback?


2009/8/17 Michael Bell <mikebell90@yahoo.com>

As you see below, something seems....whacked. A simple loop with an unpooled
connection (code provided below) often gives an exception when connecting.
The Thread.sleep statement has been made as high as 5000 ms, and exceptions
still sporadically occur.

I don't know if this is a JDBC driver issue or core server issue. Or
something here. But the code is so simple....


Re: JDBC broken connection against Postgres 8.4

From
Michael Bell
Date:
Windows XP firewall is disabled. And MySQL doesn't have this problem on same box with or without.


From: Віталій Тимчишин <tivv00@gmail.com>
To: Michael Bell <mikebell90@yahoo.com>
Cc: pgsql-jdbc@postgresql.org
Sent: Thursday, August 20, 2009 5:09:25 AM
Subject: Re: [JDBC] JDBC broken connection against Postgres 8.4

May be, you have a firewall software that is working and can't keep up with all this connections opened over loopback?

2009/8/17 Michael Bell <mikebell90@yahoo.com>
As you see below, something seems.....whacked. A simple loop with an unpooled connection (code provided below) often gives an exception when connecting. The Thread.sleep statement has been made as high as 5000 ms, and exceptions still sporadically occur.

I don't know if this is a JDBC driver issue or core server issue. Or something here. But the code is so simple....


Re: JDBC broken connection against Postgres 8.4

From
Michael Bell
Date:
Great point. I even found some ID 4226 from around that time a few days ago. However, I cleared my event log, repeated
thetest and the issue, and no entries appeared in the event log. So although this sounds promising, it doesn't appear
tobe the case. 



----- Original Message ----
From: Donald Fraser <postgres@kiwi-fraser.net>
To: [JDBC] <pgsql-jdbc@postgresql.org>
Sent: Thursday, August 20, 2009 7:22:38 AM
Subject: Re: [JDBC] JDBC broken connection against Postgres 8.4

I note from your original email that your test platform is Windows XP.
Please check the system event logs for ID 4226.
If you see this message then you have a TCP connection limit issue causing problems.


----- Original Message ----- From: ??????? ????????
To: Michael Bell
Cc: pgsql-jdbc@postgresql.org
Sent: Thursday, August 20, 2009 1:09 PM
Subject: Re: [JDBC] JDBC broken connection against Postgres 8.4


May be, you have a firewall software that is working and can't keep up with all this connections opened over loopback?


2009/8/17 Michael Bell <mikebell90@yahoo.com>

As you see below, something seems....whacked. A simple loop with an unpooled connection (code provided below) often
givesan exception when connecting. The Thread.sleep statement has been made as high as 5000 ms, and exceptions still
sporadicallyoccur. 

I don't know if this is a JDBC driver issue or core server issue. Or something here. But the code is so simple....

-- Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc






Re: JDBC broken connection against Postgres 8.4

From
Michael Bell
Date:
First of all, I want to thank everyone for their input.

Second, I installed PG 8.4 server on another box (Windows 2000), and all was well.

So the issue APPEARS to be only if you if the Server is on Windows XP. I'm not sure if the localhost thing contributes
sinceMS does weird things to fake a loopback. I suspect that is the issue. 

Case closed, though it might be worth noting a warning thereof.





----- Original Message ----
From: Donald Fraser <postgres@kiwi-fraser.net>
To: [JDBC] <pgsql-jdbc@postgresql.org>
Sent: Thursday, August 20, 2009 7:22:38 AM
Subject: Re: [JDBC] JDBC broken connection against Postgres 8.4

I note from your original email that your test platform is Windows XP.
Please check the system event logs for ID 4226.
If you see this message then you have a TCP connection limit issue causing problems.


----- Original Message ----- From: ??????? ????????
To: Michael Bell
Cc: pgsql-jdbc@postgresql.org
Sent: Thursday, August 20, 2009 1:09 PM
Subject: Re: [JDBC] JDBC broken connection against Postgres 8.4


May be, you have a firewall software that is working and can't keep up with all this connections opened over loopback?


2009/8/17 Michael Bell <mikebell90@yahoo.com>

As you see below, something seems....whacked. A simple loop with an unpooled connection (code provided below) often
givesan exception when connecting. The Thread.sleep statement has been made as high as 5000 ms, and exceptions still
sporadicallyoccur. 

I don't know if this is a JDBC driver issue or core server issue. Or something here. But the code is so simple....

-- Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc