Bug 1459499

Created attachment 1285738 [details]
Sample tar file for reproducing the bug

Description of problem:

jdbc driver attempts to get type information on dropped columns when calling a stored procedure that access a table which one/more of its columns was dropped.

When upgrading to version 42.1.1 we got an exception when accessing a stored procedure that does a simple select from one of our tables :

java.lang.NullPointerException
at org.postgresql.jdbc.TypeInfoCache.getSQLType(TypeInfoCache.java:185)

Generally speaking, the bug is that jdbc driver attempts to get type information on dropped
columns when calling a stored procedure that access a table which one of its columns was dropped.

The attached sample.tar package demonstraits this failure

In this package please find :

    pom.xml - the maven pom file
    src - java source directory for the sample code
    sample.sql - SQL code for creating a sample table and a stored procedure to demonstrate the peoblem.


Version-Release number of selected component (if applicable):

42.1.1

How reproducible:

Steps to Reproduce:

1) please extract the package to a directory named 'sample'

2) create the following DB user

createuser -U postgres -P -s -e testuser

when prompt for password for the new user type:

123456

3) create a database that is owned by the "testuser" user :

su - postgres -c "psql -U postgres -c 'create database testdb owner testuser template template0;' template1"

4) create the sample table and the SP that access it :

psql -U testuser -f ./sample.sql testdb

5) run the following from pgsql prompt

SELECT
relnatts,
attname,
attisdropped
FROM
pg_class
JOIN pg_attribute att ON attrelid = pg_class.oid
WHERE
relname = 'vm_dynamic';

you will get the folowing result and as you see all attisdropped = false since this is a new created table

relnatts | attname | attisdropped
----------+----------+--------------
3 | tableoid | f
3 | cmax | f
3 | xmax | f
3 | cmin | f
3 | xmin | f
3 | ctid | f
3 | key_col | f
3 | val_col | f
3 | comment | f

6) compile and run the application from the sample directory

mvn clean install -Dm2.localRepository=
for example: mvn clean install -Dm2.localRepository=/home/emesika/.m2/repository
java -jar target/JDBCDriverBug-1.0-SNAPSHOT.jar

The result is that callMetaDataStoredProcedureDirectly method perfprms as expected

7) run the following from pgsql prompt

alter table sample drop column comment ;

8) run the following from pgsql prompt

SELECT
relnatts,
attname,
attisdropped
FROM
pg_class
JOIN pg_attribute att ON attrelid = pg_class.oid
WHERE
relname = 'vm_dynamic';

relnatts | attname | attisdropped
----------+------------------------------+--------------
3 | tableoid | f
3 | cmax | f
3 | xmax | f
3 | cmin | f
3 | xmin | f
3 | ctid | f
3 | key_col | f
3 | val_col | f
3 | ........pg.dropped.3........ | t

Now you have a column named "........pg.dropped.3........" , this is the 'comment' column that was dropped

9) compile and run the application from the sample directory

mvn clean install -Dm2.localRepository=
java -jar target/JDBCDriverBug-1.0-SNAPSHOT.jar

You will got this Exception:

Exception in thread "main" java.lang.NullPointerException
at org.postgresql.jdbc.TypeInfoCache.getSQLType(TypeInfoCache.java:185)
at org.postgresql.jdbc.TypeInfoCache.getSQLType(TypeInfoCache.java:181)
at org.postgresql.jdbc.PgDatabaseMetaData.getProcedureColumns(PgDatabaseMetaData.java:1153)
at JDBCSample.JDBCDriverBug.callMetaDataStoredProcedureDirectly(JDBCDriverBug.java:47)
at JDBCSample.JDBCDriverBug.main(JDBCDriverBug.java:17)


Actual results:

When a table has any dropped columns , a stored procedure that attempts to access that table will cause NullPointerException when trying to return the stored procedure call result metadata 


Expected results:

dropped columns should be omitted for metadata queries as it was in old drivers 
(9.2.x)


Additional info:

Please see also the open issue in :

See issue https://github.com/pgjdbc/pgjdbc/issues/838