Re: JDBC executeUpdate returns 0 for table partitioning rule insertion - Mailing list pgsql-jdbc

Hi Dave,

Yes it does actually go into the partitioned table, pls find the test case
below.

Thanks,
Tea

=========== PostgresqlTest.java ==============
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import junit.framework.TestCase;

/*
 *
 * $Log$
 */

/**
 * Test Postgresql JDBC driver features
 *
 * @author $Author$
 * @version $Revision$, $Date$
 */

public class PostgresqlTest extends TestCase {
    /**
     * change to your test JDBC URL
     */
    private static final String JDBC_URL = "jdbc:postgresql://"
            + "localhost/test" + "?user=test&password=test";

    private static final String DRIVER_CLASS = "org.postgresql.Driver";

    private static final String SQL_CREATE_TABLE = "CREATE TABLE tbl_test "
            + "(msg VARCHAR(255), create_date DATE)";

    private static final String SQL_CREATE_PARTITION = "CREATE TABLE "
            + "tbl_test_2005_12 (CHECK (create_date >= DATE '2005-12-01' "
            + "AND create_date < DATE '2006-01-01')) INHERITS (tbl_test)";

    /**
     * when date of insert row falls within 2005-12, insert into
     * tbl_test_2005_12 instead
     */
    private static final String SQL_CREATE_INSERT_RULE = "CREATE RULE "
            + "rul_test_2005_12 AS ON INSERT TO tbl_test WHERE (create_date
"
            + ">= DATE '2005-12-01' AND create_date < DATE '2006-01-01') "
            + "DO INSTEAD INSERT INTO tbl_test_2005_12 (msg, create_date) "
            + "VALUES (NEW.msg, NEW.create_date)";

    private static final String SQL_DELETE_TABLE = "DROP TABLE tbl_test "
            + "CASCADE";

    private Connection conn;

    /**
     * the default TestCase constructor
     *
     * @param name
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public PostgresqlTest(String name) throws ClassNotFoundException,
            SQLException {
        super(name);
        Class.forName(DRIVER_CLASS);
    }

    /**
     * remove the test table and its dependents, if any
     *
     * @throws SQLException
     * @throws SQLException
     */
    public void setUp() throws SQLException {
        try {
            conn = DriverManager.getConnection(JDBC_URL);
        } catch (SQLException e) {
            if (conn != null) {
                tearDown();
            }
            throw e;
        }

        try {
            removeTables();
        } catch (SQLException e) {
            // the test tables may not exist initially
        }
    }

    /**
     * clean up the connection
     */
    public void tearDown() throws SQLException {
        conn.close();
    }

    /**
     * create a test table without partition, insert a row and expects
affected
     * rows = 1
     * <p>
     * this test always passes
     *
     * @throws SQLException
     */
    public void testNoPartition() throws SQLException {
        boolean cleanUp = false;
        try {
            createWithoutPartition();
            cleanUp = true;
            int rows = new SQLTemplate(conn).execute("INSERT INTO tbl_test "
                    + "(msg, create_date) VALUES " + "("
                    + "'noPartition', date '2005-12-01')");
            assertEquals(1, rows);
        } finally {
            if (cleanUp) {
                removeTables();
            }
        }
    }

    /**
     * create a test table with partition, insert a row and expects affected
     * rows = 1
     * <p>
     * this test fails on JDBC drivers <= 8.2dev-500 JDBC x
     *
     * @throws SQLException
     */
    public void testAgainstPartition() throws SQLException {
        boolean cleanUp = false;
        try {
            createWithPartition();
            cleanUp = true;
            int rows = new SQLTemplate(conn).execute("INSERT INTO tbl_test "
                    + "(msg, create_date) VALUES " + "("
                    + "'yesPartition', date '2005-12-02')");
            assertEquals(1, rows);
        } finally {
            if (cleanUp) {
                removeTables();
            }
        }
    }

    /**
     * creates only the test table
     *
     * @throws SQLException
     */
    private void createWithoutPartition() throws SQLException {
        new SQLTemplate(conn).execute(SQL_CREATE_TABLE);
    }

    /**
     * creates test table with partitioning
     *
     * @throws SQLException
     */
    private void createWithPartition() throws SQLException {
        new SQLTemplate(conn).execute(new String[] { SQL_CREATE_TABLE,
                SQL_CREATE_PARTITION, SQL_CREATE_INSERT_RULE });
    }

    /**
     * remove test and its dependents
     *
     * @throws SQLException
     */
    private void removeTables() throws SQLException {
        new SQLTemplate(conn).execute(SQL_DELETE_TABLE);
    }

    /**
     * convenient inner class to aid SQL execution
     */
    private final class SQLTemplate {
        private final Connection conn;

        private SQLTemplate(Connection conn) {
            super();
            this.conn = conn;
        }

        private int execute(String sql) throws SQLException {
            return execute(new String[] { sql })[0];
        }

        private int[] execute(String[] sqls) throws SQLException {
            Statement stmt = null;
            try {
                stmt = conn.createStatement();
                // to store the affected rows for each sql
                int[] rowsArr = new int[sqls.length];
                for (int i = 0; i < sqls.length; i++) {
                    rowsArr[i] = stmt.executeUpdate(sqls[i]);
                }
                return rowsArr;
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            } finally {
                stmt.close();
            }
        }
    }
}

===============================================



----- Original Message -----
From: "Dave Cramer" <pg@fastcrypt.com>
To: "Tea Yu" <teayu1@gmail.com>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Tuesday, December 06, 2005 8:54 PM
Subject: Re: [JDBC] JDBC executeUpdate returns 0 for table partitioning rule
insertion


> Tea,
>
> Does the data actually go in the partitioned table, and if so can you
> send us a test case.
>
> The driver is just reporting the result code from the backend so ???
>
> Dave
> On 6-Dec-05, at 5:45 AM, Tea Yu wrote:
>
> >  Hi there,
> >
> > The scenario is quite simple - insert into a partitioned table in
> > which a
> >  rule forwards the insertion into an inherited partition
> > (Postgresql 8.1)
> >
> >  However, JDBC returns 0 during Statement.executeUpdate(sql).  It
> > behaves
> >  normally when partition rule is removed.
> >
> >  It applies to the following driver builds:
> >  8.1 Build 404 (all JDBC ver)
> >  8.2 dev Bulid 500 (all JDBC ver)
> >
> >  Any clues? Thanks much!
> >
> >  Tea
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster