Re: JDBC executeUpdate returns 0 for table partitioning rule insertion

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 &lt;= 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(at)fastcrypt(dot)com>
> To: "Tea Yu" <teayu1(at)gmail(dot)com>
> Cc: <pgsql-jdbc(at)postgresql(dot)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
>