failure to throw java.sql.Exception for referential integrity violation caused by PL/pgSQL stored procedure - Mailing list pgsql-bugs

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.Except=
ion isn't thrown.  When a subsequent, unrelated call is made on the same co=
nnection, 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 {

=20=20=20=20
    public static void main(String args[]) {
 // fill this in with proper server, db, user, and password
 String db =3D "jdbc:postgresql://postgres1/test;user=3Dtest;password=3D''";

 try {
     Class.forName("org.postgresql.Driver");
     Connection c =3D DriverManager.getConnection(db);

     Statement s =3D c.createStatement();

     // this call should generate an exception but it doesn't

     s.execute("select foo()");

     ResultSet r =3D 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:=20

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.  Inst=
ead 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=20=20

Java version 1.3. I built the JDBC driver from the same sources used to com=
pile 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, co=
nstraints should be checked immediately.  However, just to make sure this w=
as in no way related to deffered constraint checking, I tried, "REFERENCES =
binky NOT DEFERRABLE," and the result was the same.