Skip site navigation (1) Skip section navigation (2)

failure to throw java.sql.Exception for referential integrity violation caused by PL/pgSQL stored procedure

From: "Gerrit van Wingerden" <gerritv(at)primevalsoup(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: failure to throw java.sql.Exception for referential integrity violation caused by PL/pgSQL stored procedure
Date: 2001-12-18 18:06:48
Message-ID: 001701c187ee$c37167c0$0a64a8c0@redhook (view raw or flat)
Thread:
Lists: 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.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.

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2001-12-18 22:24:08
Subject: Re: Bug #542: 7.1.3 will not compile/install on SCO Open
Previous:From: pgsql-bugsDate: 2001-12-18 16:57:27
Subject: Bug #543: Unable to build postgres on Aix

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group