Hi,
please see my comments inline.
Tom Lane wrote:
Julius Stroffek <Julius.Stroffek@sun.com> writes:
Attached is the example script 'repro.sql' which creates two relations
tab1 and tab2. It also creates a rule on tab1 which simply does insert
into tab2. The insert statement into tab1 is executed afterwards. It
responds with 'INSERT 0 1'. However if I would create the same rule with
the where clause the response to the same insert statement is 'INSERT 0
0'. The output of the script executed through psql is in 'repro.out'.
Is this a bug?
No. See
http://www.postgresql.org/docs/8.2/static/rules-status.html
and note that you don't have an unconditional INSTEAD rule.
I explored this and agree that the current PostgreSQL behavior as described in the above link is correct.
However, this behavior is a serious issue when using Java Persistence through Hibernate (and probably using other providers as well). I have created a simple application running on glassfish just inserting records to the tables using Hibernate and PostgreSQL as a persistence provider.
If I would use partitioning of the tables all the insert transactions would be marked for rollback and would be rolled back. After playing a bit with a very ugly code of glassfish trying to change it to report the root cause why the transactions are marked for roll back I discovered that Hibernate uses Statement.executeBatch method to execute the sql statements which then is supposed to return the number of rows affected by the passed statements. Hibernate then compares the value returned by this function with the number of records it passes to the batch. The number of affected rows is determined in PostgreSQL JDBC driver in QueryExecutorImpl.interpretCommandStatus method by parsing the command status string returned.
The JDBC javadoc at
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#executeBatch() describes the behavior and allows to return Statement.SUCCESS_NO_INFO in a case where the number of rows is unknown. More severe issue is that JDBC spec requires a set of methods Statement.executeUpdate which are forced to return the number of rows affected and can not return Statement.SUCCESS_NO_INFO.
Any thoughts how to deal with this issue? Was there a discussion on this already in JDBC driver team?
There is only one option that comes to my mind - always return Statment.SUCCESS_NO_INFO in executeBatch (or possibly only depending on some java property). I can not see any simple solution for Statement.executeUpdate since the number of rows affected may differ depending on the rules and might be also difficult to calculate.
Thanks
Cheers
Julo
