Re: JDBC executeUpdate returns 0 for table partitioning rule insertion

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

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
>

On Tue, 6 Dec 2005, Tea Yu wrote:

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

That's certainly not what I'm seeing here. I've attached my testcase. So
I've shown you mine, now you show me yours.

Kris Jurka

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