[HACKERS] rule and JDBC

[Tatsuo Ishii]

Hi,

It seems JDBC driver does not handle if a INSERT SQL statement
performed by executeUpdate() is actually a SELECT, which is rewritten
by the rule system. 

Exception in thread "main" postgresql.stat.result
        at 
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:199)
        at 
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
        at test.main(test.java:10)

I'm not sure whether the JDBC driver or PostgreSQL backend should be
blamed though.
--
Tatsuo Ishii

Here is a test case:

create table daily_log (
    log_date timestamp default current_timestamp,
    log_memo varchar
);
create table daily_log01 (
    log_date timestamp default current_timestamp,
    log_memo varchar
);
create table daily_log02 (
    log_date timestamp default current_timestamp,
    log_memo varchar
);

create function insert_daily_log(timestamp,varchar) returns void as '
    declare
        in_log_date alias for $1;
        in_log_memo alias for $2;
    begin
        if 1=0 then
            insert into daily_log01 (log_date,log_memo)
                values (in_log_date,in_log_memo);
        elsif 1=1 then
            insert into daily_log02 (log_date,log_memo)
                values (in_log_date,in_log_memo);
        end if;
        return;
    end;
' language plpgsql;

create rule insert_daily_log_rule as on insert to daily_log
       do instead select insert_daily_log(NEW.log_date,NEW.log_memo);

import java.sql.*;

public class test {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection conn = DriverManager.getConnection(
            "jdbc:postgresql://localhost/testdb", "dummy", "");
        conn.setAutoCommit(false);
        Statement stat = conn.createStatement();
        stat.executeUpdate(
            "insert into daily_log (log_memo) values ('hoge')");
/* this will not raise an error BTW
        stat.executeQuery(
            "insert into daily_log (log_memo) values ('hoge')");
*/
        conn.commit();
        stat.close();
        conn.close();
    }
}

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster