Re: JDBC executeUpdate returns 0 for table partitioning rule insertion

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