search

schemaspy / schemaspy

Database documentation built easy
http://schemaspy.org
GNU Lesser General Public License v3.0
3.2k stars 313 forks source link

ERROR: column p.proisagg does not exist #537

Open jflambert opened 5 years ago

jflambert commented 5 years ago

Seeing a warning/error that I don't see with latest official release 6.0.0.

Expected Behavior

Shouldn't see a warning, or warning should be more helpful.

Current Behavior

Seeing a schema warning with the latest schemaspy 6.1.0-SNAPSHOT

Steps to Reproduce (for bugs)

  1. java -jar ./schemaspy-6.1.0-SNAPSHOT.jar --debug -o test -t pgsql -dp ./postgresql-42.2.5.jar -host localhost
  2. Observe the following warning
INFO  - The following profiles are active: default
INFO  - Started Main in 2.867 seconds (JVM running for 3.844)
INFO  - Configuration file not found
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 11.2 (Ubuntu 11.2-1.pgdg18.04+1)
INFO  - Gathering schema details
DEBUG - select r.routine_name, case when p.proisagg then 'AGGREGATE' else 'FUNCTION' end as routine_type, case when p.proretset then 'SETOF ' else '' end || case when r.data_type = 'USER-DEFINED' then r.type_udt_name else r.data_type end as dtd_identifier, r.external_language as routine_body, r.routine_definition, r.sql_data_access, r.security_type, r.is_deterministic, d.description as routine_comment from information_schema.routines r left join pg_namespace ns on r.routine_schema = ns.nspname left join pg_proc p on ns.oid = p.pronamespace and r.routine_name = p.proname left join pg_description d on d.objoid = p.oid where r.routine_schema = ? [public]
WARN  - Failed to retrieve stored procedure/function details using sql 'select r.routine_name, case when p.proisagg then 'AGGREGATE' else 'FUNCTION' end as routine_type, case when p.proretset then 'SETOF ' else '' end || case when r.data_type = 'USER-DEFINED' then r.type_udt_name else r.data_type end as dtd_identifier, r.external_language as routine_body, r.routine_definition, r.sql_data_access, r.security_type, r.is_deterministic, d.description as routine_comment from information_schema.routines r left join pg_namespace ns on r.routine_schema = ns.nspname left join pg_proc p on ns.oid = p.pronamespace and r.routine_name = p.proname left join pg_description d on d.objoid = p.oid where r.routine_schema = :schema'
org.postgresql.util.PSQLException: ERROR: column p.proisagg does not exist
  Hint: Perhaps you meant to reference the column "p.prolang".
  Position: 34
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
    at org.schemaspy.input.dbms.service.RoutineService.initRoutines(RoutineService.java:58)
    at org.schemaspy.input.dbms.service.RoutineService.gatherRoutines(RoutineService.java:43)
    at org.schemaspy.input.dbms.service.DatabaseService.gatherSchemaDetails(DatabaseService.java:103)
    at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:244)
    at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:121)
    at org.schemaspy.cli.SchemaSpyRunner.runAnalyzer(SchemaSpyRunner.java:98)
    at org.schemaspy.cli.SchemaSpyRunner.run(SchemaSpyRunner.java:87)
    at org.schemaspy.Main.main(Main.java:55)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:50)
    at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:51)

Context

Your Environment

npetzall commented 5 years ago

Searching for p.proisagg among our issues (including closed) you'll find #470

PostgreSQL made changes to their schemas in 11.2 so there is a new databaseType -t psgsql11 that can be used.

jflambert commented 5 years ago

OK! Thanks.

Could you maybe auto-detect that setting or improve the error message to suggest this new type?

npetzall commented 5 years ago

Auto-detect would be a cool feature I might look into that.

jflambert commented 5 years ago

Right, I would also argue that when PostgreSQL 12 is released, using a -t pgsql11 option might be counter-intuitive.

npetzall commented 5 years ago

Man those 1 and l look the same.

We have taken the same approach for other dbms. So the idea is 11 or newer.

Same goes for mssql and this is to not break backwards compatability. Need to check that this is documented in more than a single issue discussing this.

npetzall commented 5 years ago

Documentation is needed for the strategy. It would be possible to check for databaseTypes that extends the selected to show alternatives.

Adding a version property to the databaseType would make a type of autodetection possible.

npetzall commented 5 years ago

A proposal for auto-detect #544

donmedo2000 commented 4 years ago

Had thesame issue in phppgadmin and realised error is caused by phppgadmin directly accessing postgres schema using queries designed for postgres < 10. Work around is to run these two commands in ubuntu command prompt to effect column name change.

sed -i "s/NOT pp.proisagg/pp.prokind='f'/g" /usr/share/phppgadmin/classes/database/Postgres.php sed -i "s/NOT p.proisagg/p.prokind='f'/g" /usr/share/phppgadmin/classes/database/Postgres.php

You may need to run command as sudo to avoid permission denied error. Also check to confirm correct case of phpPgAdmin folder name in above commands

mmatela commented 1 year ago

To prevent future confusion, maybe type pgsql should be an alias for pgsql11 and there should be pgsql10 for older versions (which occur less and less in the wild)?