Обсуждение: 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

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++;
        }
    }

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

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

> 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

>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

> 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

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....

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

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