PostgreSQL JDBC - Re: JDBC executeUpdate returns 0 for table partitioning rule insertion

Author

Re: JDBC executeUpdate returns 0 for table partitioning rule insertion

Tea Yu

2005-12-06, 7:23 am

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

Dave Cramer

2005-12-06, 7:23 am

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
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tea Yu

2005-12-07, 3:40 am

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_RU
LE = "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 ClassNotFoundExcepti
on
* @throws SQLException
*/
public PostgresqlTest(Strin
g name) throws ClassNotFoundExcepti
on,
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_U
RL);
} 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 {
createWithoutPartiti
on();
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 createWithoutPartiti
on() throws SQLException {
new SQLTemplate(conn). execute(SQL_CREATE_T
ABLE);
}

/**
* 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_RU
LE });
}

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

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

private SQLTemplate(Connecti
on 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

[color=darkred]
> 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:
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links