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

Hi Dave,

yeah sorry.... the backend actually returns 0, so there's nothing to do with
the Driver but Postgres...

Tea

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