7 Replies Latest reply on Dec 21, 2015 5:23 PM by jmarton

    SQL Developer won't display tables for PostgreSQL connection

    jansohn

      I'm experiencing a similar problem already discussed here (Re: Issue connecting to PostgreSQL with Oracle SQL Developer) which didn't offer a solution.

       

      I can successfully connect to the PostgreSQL DB but as soon as I try to open schemata / tables / etc. an error window pops up saying "ERROR: syntax error at or near ""CATALOG_NAME"" Position: 31 Vendor code 0".

       

      postgresql_sqlDeveloper.jpg

       

      I tried all kinds of different combinations on Windows and Linux:

       

      SQL Developer version: 4.1.2.20.64

      Oracle JDK: 1.8.0_51, 32bit, Windows 7 (also tried 1.8.0_60, 64bit, SLES10)

      PostgreSQL JDBC driver: postgresql-9.4-1205.jdbc42.jar (also tried jdbc3, jdbc4, jdbc41)

      PostgreSQL Server: 8.1.23-0.11.1, 64bit, SLES10

       

      One person (jmarton) seemed to get it working. Otherwise I could only find answers that the listing won't work (i.e. http://stackoverflow.com/a/28671213/3375325). Would be great if someone could help with or fix this issue. I can provide debug logs if I'm told how to produce them!

        • 1. Re: SQL Developer won't display tables for PostgreSQL connection
          jmarton

          Still works for me using the current :

          SQL Developer version: 4.1.2.20.64

          Oracle JDK: 1.8.0_31, 64bit, Linux

          Instant client: x64-12.1.0.2.0 (inactive per SQL Developer configuration)

          PostgreSQL JDBC driver: postgresql-9.4-1206-jdbc42.jar

           

          Could you please show me the connection settings you are using?

          • 2. Re: SQL Developer won't display tables for PostgreSQL connection
            jansohn

            I'm using the simple connection string hostname/database? with standard port 5432. Do you know a way how to debug this?

            • 3. Re: Re: SQL Developer won't display tables for PostgreSQL connection
              jmarton

              I've turned on all statement logging in PostgreSQL, and received the following in the PostgreSQL 9.4 server's statement log when opening schemata/public/tables, and executed a simple query on table a. Please do this and report me what you see in the log.

               

              To turn on PostgreSQL server-side logging on a default Debian Jessie install, I needed to do as follows:

              • mkdir /var/lib/postgresql/9.4/main/pg_log
              • chown postgres:postgres /var/lib/postgresql/9.4/main
              • chmod 700 /var/lib/postgresql/9.4/main
              • postgresql.conf:
                • log_statement = 'all'
                • logging_collector = on

               

               

              2015-12-15 11:25:20 CET [11881-1] testuser@testdb LOG:  execute <unnamed>: SET extra_float_digits = 3
              2015-12-15 11:25:20 CET [11881-2] testuser@testdb LOG:  execute <unnamed>: select  distinct catalog_name "CATALOG_NAME" from information_schema.schemata order by catalog_name
              2015-12-15 11:25:20 CET [11881-3] testuser@testdb LOG:  execute <unnamed>: SELECT nspname AS TABLE_SCHEM , NULL AS TABLE_CATALOG  FROM pg_catalog.pg_namespace WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_' OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_' OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_'))  ORDER BY TABLE_SCHEM
              2015-12-15 11:25:30 CET [11881-4] testuser@testdb LOG:  execute <unnamed>:                 
                                          select  $1 "CATALOG_NAME", schema_name "SCHEMA_NAME" from information_schema.schemata
                                          where schema_name!='information_schema'
                                      
              2015-12-15 11:25:30 CET [11881-5] testuser@testdb DETAIL:  parameters: $1 = 'testdb'
              2015-12-15 11:25:33 CET [11881-6] testuser@testdb LOG:  execute <unnamed>: select table_name "TNAME" from information_schema.Tables where TABLE_SCHEMA = $1  
                                                  and (TABLE_TYPE = 'BASE TABLE' OR table_schema='information_schema')
                                          
              2015-12-15 11:25:33 CET [11881-7] testuser@testdb DETAIL:  parameters: $1 = 'public'
              2015-12-15 11:31:11 CET [11881-9] testuser@testdb LOG:  execute <unnamed>: select * from a
              
              
              
              1 person found this helpful
              • 4. Re: Re: Re: SQL Developer won't display tables for PostgreSQL connection
                jansohn

                OK, that helped to identify the command that fails. Here's my output:

                 

                2015-12-21 15:52:57 CET testuser testdb LOG:  statement: PREPARE <unnamed> AS select  distinct catalog_name "CATALOG_NAME" from information_schema.schemata order by catalog_name
                2015-12-21 15:52:57 CET testuser testdb ERROR:  syntax error at or near ""CATALOG_NAME"" at character 31
                2015-12-21 15:52:57 CET testuser testdb LOG:  statement: PREPARE <unnamed> AS SELECT nspname AS TABLE_SCHEM , NULL AS TABLE_CATALOG  FROM pg_catalog.pg_namespace WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_' OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_' OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_'))  ORDER BY TABLE_SCHEM
                2015-12-21 15:52:57 CET testuser testdb LOG:  statement: <BIND>
                2015-12-21 15:52:57 CET testuser testdb LOG:  statement: EXECUTE <unnamed>  [PREPARE:  SELECT nspname AS TABLE_SCHEM , NULL AS TABLE_CATALOG  FROM pg_catalog.pg_namespace WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_' OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_' OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_'))  ORDER BY TABLE_SCHEM]
                
                

                 

                So the problem seems to be that PostgreSQL 8.1 expects an additional AS in the first query:

                 

                2015-12-21 15:55:39 CET testuser testdb LOG:  statement: PREPARE <unnamed> AS select  distinct catalog_name as "CATALOG_NAME" from information_schema.schemata order by catalog_name
                2015-12-21 15:55:40 CET testuser testdb LOG:  statement: <BIND>
                2015-12-21 15:55:40 CET testuser testdb LOG:  statement: EXECUTE <unnamed>  [PREPARE:  select  distinct catalog_name as "CATALOG_NAME" from information_schema.schemata order by catalog_name]
                
                

                 

                I hope we'll be able to get away from SLES10 soon in order to upgrade to a newer PostgreSQL version.

                • 5. Re: Re: Re: Re: SQL Developer won't display tables for PostgreSQL connection
                  jmarton

                  It seems that the AS keyword for column labels is optional starting from PostgreSQL version 8.4.

                   

                  I'm unsure if this worth to be fixed in SQL Developer as many PostgreSQL catalog queries needs to be reviewed, like writing

                  instead

                  select  distinct catalog_name "CATALOG_NAME" from information_schema.schemata order by catalog_name
                  ;
                  select  $1 "CATALOG_NAME", schema_name "SCHEMA_NAME" from information_schema.schemata  
                                              where schema_name!='information_schema'
                  ;
                  
                  

                   

                  an additional AS keyword for column aliasing in the built-in catalogue-queries for PostgreSQL.

                   

                  select  distinct catalog_name AS "CATALOG_NAME" from information_schema.schemata order by catalog_name
                  ;
                  select  $1 AS "CATALOG_NAME", schema_name AS "SCHEMA_NAME" from information_schema.schemata  
                                              where schema_name!='information_schema'
                  ;
                  

                   

                  thatJeffSmith-Oracle, do you know an easy way, like e.g. catalogue queries are somewhere in a resource file that can be patched by the experienced end users (on their own risk)

                  • 6. Re: SQL Developer won't display tables for PostgreSQL connection
                    thatJeffSmith-Oracle

                    there's  no official support for postgresql - we did a bit of work to see what we could do to assist with migrations to Oracle Database

                     

                    let us know if you're working on such a migration and I'll see what we can do

                     

                    touching the jars that ship with SQLDev violate the EULA, so if you're asking me for an easy way to hack the source, I can't help you

                    • 7. Re: SQL Developer won't display tables for PostgreSQL connection
                      jmarton

                      OK, Jeff, thank you, I didn't think about touching the JARs. It could have been in some config files or so, but your answer means for me that this is not the case.