BUG #1296: Server crashes when relation already exists using JDBC

The following bug has been logged online:

Bug reference:      1296
Logged by:          Bryan Ray

Email address:      bryan.ray@gmail.com

PostgreSQL version: 8.0 Beta

Operating system:   Win32 (XP SP1 - version 5.1.2600)

Description:        Server crashes when relation already exists using JDBC

Details:

I am using the postgresql jdbc driver to access a postgresql 7.5 development
version. I wrote some code to  create a relation, and tried to catch the
exception if it already existed. After catching the exception the next query
resulted in an IO exception and the server restarts. The restart is noted in
the event viewer:

TRAP: FailedAssertion("!(portal->resowner == ((void *)0))", File:
"portalmem.c", Line: 561)
.

And the stack trace is like so:

org.postgresql.util.PSQLException: An I/O error occured while sending to the
backend
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:142)
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.
    at java:346)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2S
    at tatement.java:294)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Stat
    at ement.java:249)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
org.postgresql.jdbc2.optional.PooledConnectionImpl$StatementHandler.invoke(
    at PooledConnectionImpl.java:392)
    at $Proxy2.executeUpdate(Unknown Source)
com.logicacmg.uk.rayb.TableUniqueKeyGenerator.initTables(TableUniqueKeyGene
    at rator.java)
com.logicacmg.uk.rayb.TableUniqueKeyGeneratorTest.testInitTables(TableUniqu
    at eKeyGeneratorTest.java)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at junit.framework.TestCase.runTest(TestCase.java:154)
    at junit.framework.TestCase.runBare(TestCase.java:127)
    at junit.framework.TestResult$1.protect(TestResult.java:106)
    at junit.framework.TestResult.runProtected(TestResult.java:124)
    at junit.framework.TestResult.run(TestResult.java:109)
    at junit.framework.TestCase.run(TestCase.java:118)
    at junit.framework.TestSuite.runTest(TestSuite.java:208)
    at junit.framework.TestSuite.run(TestSuite.java:203)
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestR
    at unner.java:421)
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner
    at .java:305)
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunne
    at r.java:186)
Caused by: java.net.SocketException: Connection reset by peer: socket write
error
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(Unknown Source)
    at java.net.SocketOutputStream.write(Unknown Source)
    at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
    at java.io.BufferedOutputStream.flush(Unknown Source)
    at org.postgresql.core.PGStream.flush(PGStream.java:468)
org.postgresql.core.v3.QueryExecutorImpl.sendSync(QueryExecutorImpl.java:47
    at 4)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:138)
    ... 26 more

Postgres is installed as a service under Win XP service pack 1. I am using
the pgdev.306.jdbc3.jar with Sun Java SE 1.4.2_05.

When doing the equivalent operations from psql, psql gives the expected
result (not restarting teh server):

RayB=# SELECT * FROM keygeneration;
 uniqueness | nextid
------------+--------
 myTable    |      0
(1 row)

RayB=# CREATE TABLE keygeneration (uniqueness VARCHAR(20) PRIMARY KEY,
nextid INT);
ERROR: relation "keygeneration" already exists
RayB=# SELECT * FROM keygeneration;
 uniqueness | nextid
------------+--------
 myTable    |      0
(1 row)

I have included a JUnit test case below which generated the problem. The
java code I used is identical to the code that is on postgres.org:

/*
 * Created on Oct 18, 2004
 */
package com.logicacmg.uk.rayb;

import java.sql.*;
import javax.sql.DataSource;

/**
 * @author RayB
 *
 * Purpose:
 * Design Patterns:
 */
public class TableUniqueKeyGenerator implements KeyGenerator
{
    private DataSource datasource = null;
    private String tableName = null;

    // SQL
    private static final String CREATE_TABLE_SQL = "CREATE TABLE
keygeneration (uniqueness VARCHAR(30) PRIMARY KEY,nextid INT NOT NULL)";
    private static final String CREATE_NEXT_KEY_SQL = "INSERT INTO
keygeneration (uniqueness,nextid) VALUES (?,0)";
    private static final String GET_NEXT_KEY_SQL = "SELECT nextid FROM
keygeneration WHERE uniqueness=?";
    private static final String SET_NEXT_KEY_SQL = "UPDATE keygeneration SET
nextid=? WHERE uniqueness=?";

    /**
     * @param tableName The table name for which a unique key will be
generated.
     * @param datasource The DataSource used to communicate with the
database.
     * */
    public TableUniqueKeyGenerator(DataSource datasource,String tableName)
    {
        if(tableName==null||tableName=="")
        {
            throw new IllegalArgumentException("tableName must be a
non-empty String object.");
        }
        if(datasource==null)
        {
            throw new IllegalArgumentException("datasource can not be set to
null.");
        }
        this.datasource = datasource;
        this.tableName = tableName;
    }

    /**
     * @return The table name for which this object generates unique keys.
     * */
    public String getTableName()
    {
        return tableName;
    }

    /* (non-Javadoc)
     * @see com.logicacmg.uk.rayb.KeyGenerator#getNextKey()
     */
    public Key getNextKey() throws SQLException
    {
        Connection connection = null;
        long nextId = 0;
        try
        {
            // Get next id
            connection = datasource.getConnection();
            connection.setAutoCommit(false);
            PreparedStatement nextIdQuery =
connection.prepareStatement(GET_NEXT_KEY_SQL);
            nextIdQuery.setString(1,tableName);
            ResultSet nextIdResults = nextIdQuery.executeQuery();
            while(nextIdResults.next())
            {
                nextId = nextIdResults.getLong(1);
            }
            // Set next id
            PreparedStatement nextIdUpdate =
connection.prepareStatement(SET_NEXT_KEY_SQL);
            nextIdUpdate.setLong(1,nextId+1);
            nextIdUpdate.setString(2,tableName);
            nextIdUpdate.execute();
            connection.commit();
            // Close the connection
            if(connection != null)
            {
                try
                {
                    connection.close();
                }
                catch(SQLException e1)
                {}
            }
            return new Key(new Long(nextId));
        }
        catch(SQLException e2)
        {
            // Rollback the transaction
            try
            {
                connection.rollback();
            }
            catch(SQLException e3)
            {}

            // Close the connection
            if(connection != null)
            {
                try
                {
                    connection.close();
                }
                catch(SQLException e3)
                {}
            }
            // Rethrow the exception
            throw e2;
        }
    }

    /* (non-Javadoc)
     * @see com.logicacmg.uk.rayb.KeyGenerator#initTables()
     */
    public void initTables() throws SQLException
    {
        Connection connection = null;
        //try
        //{
            // Create tables - there is a PostgreSQL bug here, causing a
resource leak
            try
            {
                connection = datasource.getConnection();
                // use connection
                connection.setAutoCommit(false);
                Statement createTables = connection.createStatement();
                createTables.execute(CREATE_TABLE_SQL);
                connection.commit();
            }
            catch (SQLException e)
            {
                // log error
            }
            finally
            {
                if (connection != null)
                {
                    try
                    {
                        connection.close();
                    }
                    catch (SQLException e)
                    {}
                }
            }
//            try
//            {
//                connection = datasource.getConnection();
//                connection.setAutoCommit(false);
//                Statement createTables = connection.createStatement();
//                createTables.execute(CREATE_TABLE_SQL);
//                connection.commit();
//                connection.close();
//            }
//            catch(SQLException e)
//            {
//                // This table is being shared between keygenerators.
//                // Catch the exception generated if the table already
exists.
//            }

            //try
            //{
                // Create the key for the table
                connection = datasource.getConnection();
                connection.setAutoCommit(false);
                PreparedStatement createKey =
connection.prepareStatement(CREATE_NEXT_KEY_SQL);
                createKey.setString(1,tableName);
                System.out.println("Executing...");
                createKey.executeUpdate();
                System.out.println("Committing...");
                connection.commit();
                connection.close();
//            }
//            catch(SQLException e)
//            {
//                // Squish!
//            }

            // Close the connection
            if(connection != null)
            {
                //try
                //{
                    connection.close();
//                }
//                catch(SQLException e1)
//                {}
            }
//        }
//        catch(Exception e2)
//        {
//            // Rollback the transaction
//            try
//            {
//                connection.rollback();
//            }
//            catch(SQLException e3)
//            {}
//
//            // Close the connection
//            if(connection != null)
//            {
//                try
//                {
//                    connection.close();
//                }
//                catch(SQLException e3)
//                {}
//            }
//            // Rethrow the exception
//            throw new SQLException(e2.getMessage());
//        }
    }
}

Testcase:

/*
 * Created on Oct 18, 2004
 */
package com.logicacmg.uk.rayb;

import junit.framework.TestCase;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.postgresql.jdbc3.Jdbc3PoolingDataSource;
/**
 * @author RayB
 *
 * Purpose:
 * Design Patterns:
 */
public class TableUniqueKeyGeneratorTest extends TestCase
{
    private DataSource datasource = null;

    /*
     * @see TestCase#setUp()
     */
    protected void setUp() throws Exception
    {
        super.setUp();
        Jdbc3PoolingDataSource source = new Jdbc3PoolingDataSource();
        //source.setDataSourceName("");
        source.setServerName("localhost");
        source.setDatabaseName("RayB");
        source.setUser("RayB");
        //source.setPassword("password");
        source.setMaxConnections(10);
        datasource = source;
    }

    /*
     * @see TestCase#tearDown()
     */
    protected void tearDown() throws Exception
    {
        super.tearDown();
    }

    public void testInitTables() throws SQLException
    {
        TableUniqueKeyGenerator keyGen = new
TableUniqueKeyGenerator(datasource,"myTable");
        keyGen.initTables();
        //Key key = keyGen.getNextKey();
        /*
        try
        {
            System.out.println(key.getLong().longValue());
        }
        catch(Exception e)
        {}
        */
    }

    public static void main(String args[])
    {
        TableUniqueKeyGeneratorTest test = new
TableUniqueKeyGeneratorTest();
        try
        {
            test.setUp();
            test.testInitTables();
            test.tearDown();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
}

Even if my code is bad / does not work around the problem this shouldn't
cause a server restart.
Bryan