Postgres View/Modify Functions

Can't seem to view/edit the functions in a Postgres database.

F4 - Routine Editor:  Spins endlessly returning nothing.
CTRL+SHIFT+C - Copy DDL: Copies an undefined create statement with variables.  No definition is actually shown.

Is this a defect/undeveloped feature for Postgres??

Cheers,

Fahim.

9 comments
Comment actions Permalink

Hello,

Function editor should work. Can you please first try to Synchronize action the Datasource in Database Tool Window - does it help? Do you see routines for the schema in Database tool window?

Please attach idea.log https://intellij-support.jetbrains.com/hc/en-us/articles/206827517 and output of Test Connection in Datasource Properties dialog. Thanks.

0
Comment actions Permalink

idea.log attached along with a text file showing the results of test connection and an error message received when explicitly trying to synchronize the routines folder.

PostgreSQL 8.2.15 (Greenplum Database 4.3.5.1 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2

0xDBE 142.4685.2



Attachment(s):
idea.log.zip
error_when_synchronizing_routines_folder.txt.zip
0
Comment actions Permalink

Thanks. Could you please check with the last 0xDBE version from https://confluence.jetbrains.com/display/DBE/0xDBE+1.0+EAP  several issues with Postgres introspection were fixed there.

After update please try to synchronize the schema again (or try re-creating the Datasource) and then select function editor. Does it work or do you still get errors? Thanks.

0
Comment actions Permalink

Hi,

Still get the same errors on 142.4861.1  ( i think it might have to do with the fact that this is a greenplum db ).

i tested it with 142.3723.2 and i get an error:
Connection to [myconnectionobject] failed: Exception in thread "main" java.lang.ClassNotFoundException: org.postgresql.Driver
  at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
  at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
  at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
  at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
  at java.lang.Class.forName0(Native Method)
  at java.lang.Class.forName(Class.java:264)
  at com.intellij.database.remote.RemoteJdbcServer.main(RemoteJdbcServer.java:15)

I remeber it used to work just fine on prior builds - i remember 141.191 was the first version i was using and it worked then.

also found this with regards to same error message i'm experiencing.

https://devnet.jetbrains.com/thread/466871

0
Comment actions Permalink

Thank you for clarification. PostgreSQL 8.0 is not supported by current (native) schema introspection, please vote and follow this related issue https://youtrack.jetbrains.com/issue/DBE-1460 .

Also, please try to use this workaround https://youtrack.jetbrains.com/issue/DBE-1459#comment=27-1117743 .

Make sure also to re-download database driver for the DataSource.

0
Comment actions Permalink

Thanks Andrey,

So just curious - how was it working in past versions of the application!?

Cheers,

Fahim.

0
Comment actions Permalink

Previously introspector based on JDBC metadata was used (it is not very precise and more slow), now we have native introspector for every database version/vendor allowing us to move forward with support for different databases. Using old (JDBC-based introspector) should help in this case.

0
Comment actions Permalink

Updated to newest version and got this error when trying to synchronize the database this time ....

ERROR: cannot cast type xid to character varying
ERROR: cannot cast type xid to character varying
The SQL statement:
select max(last_tx) as state_number
from (
  select max(xmin::varchar::bigint) as last_tx
    from pg_catalog.pg_type
    where typnamespace = oid(?)
  union all
  select max(xmin::varchar::bigint) as last_tx
    from pg_catalog.pg_class
    where relnamespace = oid(?)
  union all
  select max(xmin::varchar::bigint) as last_tx
    from pg_catalog.pg_proc
    where pronamespace = oid(?)
  union all
  select max(xmin::varchar::bigint) as last_tx
    from pg_catalog.pg_operator
    where oprnamespace = oid(?)
  union all
  select max(A.xmin::varchar::bigint) as last_tx
    from pg_catalog.pg_attribute A join pg_catalog.pg_class K on K.oid = A.attrelid
    where K.relnamespace = oid(?)
) X

  at org.jetbrains.jdba.jdbc.BaseExceptionRecognizer.recognizeException(BaseExceptionRecognizer.java:48)
  at org.jetbrains.jdba.jdbc.JdbcIntermediateSession.recognizeException(JdbcIntermediateSession.java:347)
  at org.jetbrains.jdba.jdbc.JdbcIntermediateSeance.execute(JdbcIntermediateSeance.java:98)
  at com.intellij.database.remote.jdba.impl.RemoteSeanceImpl.execute(RemoteSeanceImpl.java:24)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:497)
  at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:323)
  at sun.rmi.transport.Transport$1.run(Transport.java:200)
  at sun.rmi.transport.Transport$1.run(Transport.java:197)
  at java.security.AccessController.doPrivileged(Native Method)
  at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
  at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
  at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
  at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$254(TCPTransport.java:683)
  at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler$$Lambda$1/11414187.run(Unknown Source)
  at java.security.AccessController.doPrivileged(Native Method)
  at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
  at java.lang.Thread.run(Thread.java:745)
  at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:276)
  at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:253)
  at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:162)
  at java.rmi.server.RemoteObjectInvocationHandler.invokeRemoteMethod(RemoteObjectInvocationHandler.java:194)
  at java.rmi.server.RemoteObjectInvocationHandler.invoke(RemoteObjectInvocationHandler.java:148)
  at com.sun.proxy.$Proxy114.execute(Unknown Source)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:497)
  at com.intellij.execution.rmi.RemoteUtil.invokeRemote(RemoteUtil.java:124)
  at com.intellij.execution.rmi.RemoteUtil.access$100(RemoteUtil.java:36)
  at com.intellij.execution.rmi.RemoteUtil$RemoteInvocationHandler.invoke(RemoteUtil.java:229)
  at com.sun.proxy.$Proxy115.execute(Unknown Source)
  at org.jetbrains.jdba.intermediate.AdaptIntermediateSeance.execute(AdaptIntermediateSeance.java:28)
  at org.jetbrains.jdba.core.BaseQueryRunner.run(BaseQueryRunner.java:66)
  at com.intellij.dbm.postgre.PostgreIntrospector$SchemaRetriever.a(PostgreIntrospector.java:320)
  at com.intellij.dbm.postgre.PostgreIntrospector$SchemaRetriever.retrieve(PostgreIntrospector.java:207)
  at com.intellij.dbm.postgre.PostgreIntrospector$2.run(PostgreIntrospector.java:162)
  at org.jetbrains.jdba.core.BaseSession.inTransaction(BaseSession.java:88)
  at org.jetbrains.jdba.core.BaseFacade$2.run(BaseFacade.java:93)
  at org.jetbrains.jdba.core.BaseFacade.inSession(BaseFacade.java:125)
  at org.jetbrains.jdba.core.BaseFacade.inTransaction(BaseFacade.java:89)
  at com.intellij.dbm.postgre.PostgreIntrospector.a(PostgreIntrospector.java:158)
  at com.intellij.dbm.postgre.PostgreIntrospector.b(PostgreIntrospector.java:146)
  at com.intellij.dbm.postgre.PostgreIntrospector.introspect(PostgreIntrospector.java:85)
  at com.intellij.database.dataSource.NativeSchemaLoader.a(NativeSchemaLoader.java:113)
  at com.intellij.database.dataSource.NativeSchemaLoader.introspectAndAdapt(NativeSchemaLoader.java:67)
  at com.intellij.database.dataSource.DatabaseSchemaLoader.loadDataSourceState(DatabaseSchemaLoader.java:99)
  at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:61)
  at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:36)
  at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:34)
  at com.intellij.database.dataSource.AbstractDataSource.performJdbcOperation(AbstractDataSource.java:116)
  at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:34)
  at com.intellij.database.dataSource.DataSourceUiUtil$2.run(DataSourceUiUtil.java:187)
  at com.intellij.openapi.progress.impl.CoreProgressManager$TaskRunnable.run(CoreProgressManager.java:563)
  at com.intellij.openapi.progress.impl.CoreProgressManager$2.run(CoreProgressManager.java:142)
  at com.intellij.openapi.progress.impl.CoreProgressManager.a(CoreProgressManager.java:446)
  at com.intellij.openapi.progress.impl.CoreProgressManager.executeProcessUnderProgress(CoreProgressManager.java:392)
  at com.intellij.openapi.progress.impl.ProgressManagerImpl.executeProcessUnderProgress(ProgressManagerImpl.java:54)
  at com.intellij.openapi.progress.impl.CoreProgressManager.runProcess(CoreProgressManager.java:127)
  at com.intellij.openapi.progress.impl.ProgressManagerImpl$1.run(ProgressManagerImpl.java:126)
  at com.intellij.openapi.application.impl.ApplicationImpl$8.run(ApplicationImpl.java:365)
  at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
  at java.util.concurrent.FutureTask.run(FutureTask.java:266)
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
  at java.lang.Thread.run(Thread.java:745)
  at org.jetbrains.ide.PooledThreadExecutor$1$1.run(PooledThreadExecutor.java:55)
Caused by: org.jetbrains.jdba.exceptions.StrippedSQLException: org.postgresql.util.PSQLException: ERROR: cannot cast type xid to character varying
  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
  at org.jetbrains.jdba.jdbc.JdbcIntermediateSeance.execute(JdbcIntermediateSeance.java:84)
  at com.intellij.database.remote.jdba.impl.RemoteSeanceImpl.execute(RemoteSeanceImpl.java:24)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:497)
  at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:323)
  at sun.rmi.transport.Transport$1.run(Transport.java:200)
  at sun.rmi.transport.Transport$1.run(Transport.java:197)
  at java.security.AccessController.doPrivileged(Native Method)
  at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
  at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
  at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
  at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$254(TCPTransport.java:683)
  at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler$$Lambda$1/11414187.run(Unknown Source)
  at java.security.AccessController.doPrivileged(Native Method)
  at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
  at java.lang.Thread.run(Thread.java:745)

0
Comment actions Permalink

Is it for PostgreSQL 8.2.15 database? If you use mentioned DB version, please try using lagacy introspector as advised here https://youtrack.jetbrains.com/issue/DBE-1459#comment=27-1117743

0

Please sign in to leave a comment.