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

Jason Southern (southern(at)heymax(dot)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(at)heymax(dot)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