Postgres View/Modify Functions

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.

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

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.

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

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.

Thanks Andrey,

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

Cheers,

Fahim.

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.

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)

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