#1416 Procedures are NOT displayed with PostgreSQL JDBC Driver postgresql-42.2.9.jar

[Krzysztof Szymko]

After connecting to DB and selecting schema > expanding PROCEDURE node nothing si shown when using PostgreSQL JDBC Driver postgresql-42.2.9.jar

It works as expected when using older driver:
Procedures are NOT displayed with PostgreSQL JDBC Driver postgresql-42.2.8.jar

[Krzysztof Szymko]

As a side info I am using PostgreSQL 12.1 on JDK jdk1.8.0_231

[Gerd Wagner]

Sorry, I can't reproduce the problem.

I tested with PostgreSQL 12.1, postgresql-42.2.9.jar and JDK jdk1.8.0_192 on Linux.

If possible please try out the following Java program

package postgrestest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestPostgreSQL
{

   public static void main(String[] args) throws ClassNotFoundException, SQLException
   {
      Class.forName("org.postgresql.Driver");

      Connection con = DriverManager.getConnection("<yourUrl>", "<yourUser>", "<yourPassword>");

      ResultSet tables = con.getMetaData().getProcedures(null, "<yourSchema>", null);

      while (tables.next())
      {
         System.out.println("Procedure: " + tables.getString("PROCEDURE_NAME"));
         System.out.println("  Schema: " + tables.getString("PROCEDURE_SCHEM"));
         System.out.println("  Catalog: " + tables.getString("PROCEDURE_CAT"));
      }

   }

}

[Krzysztof Szymko]

1. All my tests were done on Win 10
2. Java class TestPostgreSQL lists procedures as expected from provided schema.
3. Build of SQuirreL from 2020/01/22 still behaves same (buggy) with driver 42.2.9
4. Switching to 42.2.8 helps and procedures are shown as expected.

NOTE: In squirrel log I have found weird logs (maybe this will help):

2020-01-22 21:21:05,250 [AWT-EventQueue-1] INFO net.sourceforge.squirrel_sql.client.plugin.PluginManager - Failed to load org.postgresql.osgi.PGDataSourceFactory in F:\Tools\squirrel-sql-4.0.0-TDG\plugins\postgres\lib\postgresql-42.2.9.jar to check if it is assignable to net.sourceforge.squirrel_sql.client.plugin.IPlugin. Reason: java.lang.NoClassDefFoundError: org/osgi/service/jdbc/DataSourceFactory
2020-01-22 21:21:05,258 [AWT-EventQueue-1] INFO net.sourceforge.squirrel_sql.client.plugin.PluginManager - Failed to load org.postgresql.sspi.NTDSAPI in F:\Tools\squirrel-sql-4.0.0-TDG\plugins\postgres\lib\postgresql-42.2.9.jar to check if it is assignable to net.sourceforge.squirrel_sql.client.plugin.IPlugin. Reason: java.lang.NoClassDefFoundError: com/sun/jna/win32/StdCallLibrary

Cheers,
Chris

[Gerd Wagner]

I get similar logs when I click the "List Drivers" button in the driver definition. They should be due to optional OSGI libs, see
https://mvnrepository.com/artifact/org.postgresql/postgresql/42.2.9

You may try to add those OSGI libs to your driver defintion's extra class path and to the Postgres Plugin's lib directory. I have few confidence that it will help. Anyway please let me know if it does.

[Krzysztof Szymko]

I looked into it more deeply. This is what I found:

1. postgresql-42.2.8.jar under PROCEDURE (procedures and functions) are shown OK
2. postgresql-42.2.9.jar - postgresql-42.2.12.jar:
   a) no FUNCTIONS are shown under PROCEDURE
   b) there is no node for FUNCTIONS (only PROCEDURE)

After turning log_statement = 'all' on PostgreSQL you can see the difference:

-- 42.2.8

SELECT NULL AS PROCEDURE_CAT, n.nspname AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL, NULL,
     d.description AS REMARKS, 2 AS PROCEDURE_TYPE,  p.proname || '_' || p.oid AS SPECIFIC_NAME
  FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p
  LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid)
  LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc')
  LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog')
 WHERE p.pronamespace=n.oid
 ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, p.oid::text;

-- 42.2.12

SELECT NULL AS PROCEDURE_CAT, n.nspname AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL, NULL,
     d.description AS REMARKS, 2 AS PROCEDURE_TYPE,  p.proname || '_' || p.oid AS SPECIFIC_NAME
  FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p
  LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid)
  LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc')
  LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog')
 WHERE p.pronamespace=n.oid AND p.prokind='p'and pg_function_is_visible(p.oid)
 ORDER ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, p.oid::text;

You can compare PgDatabaseMetaData.java in JDBC driver source and see the diff.

Hope it helps. PgAdmin supports Functions as well as Procedures, so you can
see how it should work. Below is simple SQL for testing:

DROP SCHEMA IF EXISTS sandboxjdbc CASCADE;
CREATE SCHEMA sandboxjdbc AUTHORIZATION postgres;
GRANT ALL PRIVILEGES ON SCHEMA sandboxjdbc TO postgres;

SET search_path TO 'sandboxjdbc', public;

CREATE OR REPLACE PROCEDURE "sandboxjdbc".sp_proc(v_time VARCHAR) AS '
DECLARE
    sum integer;
BEGIN
    sum := 1 + 1;
    RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION "sandboxjdbc".sp_func(v_time VARCHAR) RETURNS integer AS '
DECLARE
    sum integer;
BEGIN
    sum := 1 + 1;
    RETURN sum;
END;
' LANGUAGE 'plpgsql';

Regards,
Chris

[Gerd Wagner]

I tried out postgresql-42.2.12.jar and now can reproduce the problem.
It is caused by the driver's implementation of java.sql.DatabaseMetaData, see
https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getProcedures-java.lang.String-java.lang.String-java.lang.String-

The implementation does not return any results. Which rather clearly makes it a problem of the JDBC driver. So please contact the JDBC driver vendor for a fix.

[Andy Schönemann]

Today, I wrote to an PostgreSQL-Developer on https://github.com/pgjdbc/pgjdbc/issues/950. Maybe this issue will be fixed soon.

[Dave Cramer]

OK, I have looked at this in the driver. getProcedures returns procedures and no functions. getFunctions returns functions and no procedures. AFAIK, this is correct.

[Andy Schönemann]

@Gerd Wagner: Could you please look into this again? Squirrel 4.2.0 with postgreSql 42.2.20 still doesn't list procedures.

[Dave Cramer]

Note you have to call getProcedures to get procedures. Previously getFunctions would have returned both procedures and functions

[Gerd Wagner]

Is fixed in our GIT repository and will be available in in future versions and snapshots.
Excerpt from change log:
#1416 To read procedures SQuirreL now calls java.sql.DataBaseMetaData.getFunctions(), too,
instead of java.sql.DataBaseMetaData.getProcedures() only.
From which method a procedure/function was read is displayed at the procedure's "Info" tab.

@Dave:
With driver version 42.2.12 when DataBaseMetaData.getProcedures(null, null, null) or DataBaseMetaData.getFunctions(null, null, null) is called procedures/functions from the schemas public and pg_catalog are returned only.
With diver version 42.2.8 in all procedures and functions were returned.

[Dave Cramer]

@Gerd https://github.com/pgjdbc/pgjdbc/blob/f61fbfe7b72ccf2ca0ac2e2c366230fdb93260e5/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L1052-L1053 should allow all functions to be returned ? I'd be curious why that doesn't work

[Gerd Wagner]

@Dave
I can still reproduce the problem with the latest driver version 42.2.20 on PostgreSQL version 12.1. Below you find my code to reproduce the problem.
Hope you don't mind I didn't dig into why it doesn't work. But at least the else clause of this code looks suspicious to me:
if (schemaPattern != null && !schemaPattern.isEmpty()) {
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
} else {
/ limit to current schema if no schema given /
sql += "and pg_function_is_visible(p.oid)";
}

package postgrestest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Needed DDLs:
 * CREATE SCHEMA sandboxjdbc
 *
 * CREATE OR REPLACE PROCEDURE sandboxjdbc.sp_proc(v_time VARCHAR) AS '
 * DECLARE
 *     sum integer;
 * BEGIN
 *     sum := 1 + 1;
 *     RETURN;
 * END;
 * ' LANGUAGE 'plpgsql';
 *
 * CREATE OR REPLACE FUNCTION sandboxjdbc.sp_func(v_time VARCHAR) RETURNS integer AS '
 * DECLARE
 *     sum integer;
 * BEGIN
 *     sum := 1 + 1;
 *     RETURN sum;
 * END;
 * ' LANGUAGE 'plpgsql';
 *
 */
public class TestPostgreSQL_2
{
   public static void main(String[] args) throws ClassNotFoundException, SQLException
   {
      Class.forName("org.postgresql.Driver");

      Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/<yourDB>", "<yourUser>", "<yourPassword>");

      //ResultSet procs = con.getMetaData().getProcedures(null, "sandboxjdbc", null);
      ResultSet procs = con.getMetaData().getProcedures(null, null, null);

      while (procs.next())
      {
         final String procedure_schem = procs.getString("PROCEDURE_SCHEM");
//         if("pg_catalog".equals(procedure_schem))
//         {
//            continue;
//         }

         System.out.println("Procedure: " + procs.getString("PROCEDURE_NAME"));
         System.out.println("  Schema: " + procedure_schem);
         System.out.println("  Catalog: " + procs.getString("PROCEDURE_CAT"));
      }

      //ResultSet functs = con.getMetaData().getFunctions(null, "sandboxjdbc", null);
      ResultSet functs = con.getMetaData().getFunctions(null, null, null);

      while (functs.next())
      {
         final String function_schem = functs.getString("FUNCTION_SCHEM");
//         if("pg_catalog".equals(function_schem))
//         {
//            continue;
//         }

         System.out.println("Function: " + functs.getString("FUNCTION_NAME"));
         System.out.println("  Schema: " + function_schem);
         System.out.println("  Catalog: " + functs.getString("FUNCTION_CAT"));
      }
   }
}

[Dave Cramer]

@gerdwagner no I don't mind you digging into it. I see that was a regression now. Thanks!

[Dave Cramer]

@gerdwagner so looking at this and they history I'm left with a quandary. The spec doesn't say anything about search path, and or visibility. I understand your issue, that you would like to see everything, but the average person may not want to see procedures that are not visible to them. Are there other databases that have something akin to search_path ?

[Gerd Wagner]

@davecramer the first time I heard of a search_path at all was from Krzysztof Szymko's comment above. So no I don't know if something similar exists for other databases. It seems to me somewhat related to setting the default schema which is even part of JDBC, see Connection.setSchema(String).

What made me believe that procedures/functions from all schemas should be returned is the passage "null means that the schema name should not be used to narrow the search" which can be found in the schema parameter's description of getProcedures(...) and getFunctions(...).

[Dave Cramer]

feel free to opine https://github.com/pgjdbc/pgjdbc/pull/1633