JDBC Connection must be restarted after executing SQL statement that calls a PL/PGSQL function that won't compile

Jason Southern (southern@heymax.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
JDBC Connection must be restarted after executing SQL statement that calls a PL/PGSQL function that won't compile

Long Description
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Jason Southern
Your email address : southern@heymax.com

System Configuration
----------------------
Architecture (example: Intel Pentium) : 600MHz Intel Pentium III, 256MB RAM

Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.16 RedHat 6.2

PostgreSQL version (example: PostgreSQL-6.3) : PostgreSQL-7.0.2

Compiler used (example: gcc 2.7.2) : gcc 2.96

JDBC Driver Version: 7.0.x (jdbc7.0-1.2.jar)

JVM: Sun JVM 1.3

Please enter a FULL description of your problem:
-------------------------------------------------
JDBC Connection object fails to execute SQL after executing a statement that
calls a PL/PGSQL function that won't compile.


Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
-----------------------------------------------------------------------


1. Create a PL/PGSQL function that has a syntax error that prevents it
from being compiled:

DROP FUNCTION BROKEN(VARCHAR);

CREATE FUNCTION BROKEN(VARCHAR)
RETURNS NUMERIC

AS 'DECLARE
   v_string VARCHAR;

   v_first_char CHAR(1);
   v_ascii_val NUMERIC;

BEGIN

    v_ascii_val >= 65 AND v_ascii_val <= 90 THEN
      /* String begins with an uppercase letter A-Z */
      RETURN 1;
   ELSE
      IF v_ascii_val >= 97 AND v_ascii_val <= 122 THEN
         /* String begins with an lowercase letter a-z */
         RETURN 1;;;
      ELSE
         RETURN 0;
      END IF;

   END IF;

END;'
LANGUAGE 'plpgsql';

2. Verify function won't compile by executing the following statement in psql:
SELECT broken('test') AS word
The following message appears:
NOTICE:  plpgsql: ERROR during compile of broken near line 6
ERROR:  parse error at or near ">"

3. Execute the same statement using Statement.executeQuery()
SELECT broken('test') AS word

The following exception is thrown when this SQL statement is executed:

Exception in thread "main" java.sql.SQLException: ERROR: parse error at or near
">"
    at org.postgresql.Connection.ExecSQL(Connection.java:393)
    at org.postgresql.jdbc2.Statement.execute(Statement.java:273)
    at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
    at TestPostgres.main(TestPostgres.java:303) <-- the name of my test class

If this SQLException is caught, any attempt to invoke a properly formed SQL
statement using Statement.executeQuery() results in the following exception being thrown:

Exception in thread "main" No results were returned by the query.
    at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
    at TestPostgres.main(TestPostgres.java:311) <-- the name of my test class

I expected a ResultSet object being returned, even if the statement produces no results.

If one closes the current connection object, reconnects, and creates a new
statement object, Statement.executeQuery() does not throw exception.

Sample Code


No file was uploaded with this report