|
When a program uses JDBC to call a stored procedure
written in PL/pgSQL and that procedure causes a referential integrity violation,
a java.sql.Exception isn’t thrown. When a subsequent, unrelated call is
made on the same connection, it will fail with a referential integrity
exception.
Steps to reproduce:
1. Create two tables, foo and
binky:
CREATE TABLE binky (id integer, primary
key(id))
CREATE TABLE foo (id integer, binky_id integer REFERENCES binky) 2. Create stored procedure foo: CREATE FUNCTION foo()
RETURNS INTEGER AS ' BEGIN -- this should cause a referential integrity violation INSERT INTO foo values(1, 3); return 1; END; ' LANGUAGE 'plpgsql'; 3. Create a test class, Test1 and run it: import java.sql.*;
public class Test { public static void main(String args[]) { // fill this in with proper server, db, user, and password String db = "jdbc:postgresql://postgres1/test;user=test;password=''"; try {
Class.forName("org.postgresql.Driver"); Connection c = DriverManager.getConnection(db); Statement s =
c.createStatement();
// this call should
generate an exception but it doesn't
s.execute("select
foo()");
ResultSet r =
s.getResultSet();
while(r.next())
{
System.out.println("call to foo returned " + r.getInt(1)); } // we should never make it
this far but we do
System.out.println("about
to call select");
s.execute("select * from
binky");
} catch(Exception x)
{
System.out.println(x); } } } Expected output: java.sql.SQLException: ERROR: <unnamed>
referential integrity violation - key referenced from foo not found in
binky
Actual output:
First call to foo returned 1
about to call select java.sql.SQLException: ERROR: <unnamed> referential integrity violation - key referenced from foo not found in binky The problem:
One would expect the call to foo to generate an
exception right away. Instead the program continues to run (it even gets
the return value returned by foo). It isn’t until the select call is made
that the exception generated by the call to foo is thrown.
Platform details:
PostgreSQL 7.1.1 on i686-pc-linux-gnu, compiled by
GCC egcs-2.91.66
Java version 1.3. I built the JDBC driver from the
same sources used to compile the sever. I’m running the Java client on
Redhat Linux 7.1.
Other notes: Calling foo from php gives proper results (i.e. the
call fails right away) so I suspect this has something to do with the JDBC
driver. By default, constraints should be checked immediately.
However, just to make sure this was in no way related to deffered constraint
checking, I tried, "REFERENCES binky NOT DEFERRABLE," and the result was the
same.
|
