{"action":"created","issue":{"url":"https://api.github.com/repos/ControlSystemStudio/cs-studio/issues/1929","repository_url":"https://api.github.com/repos/ControlSystemStudio/cs-studio","labels_url":"https://api.github.com/repos/ControlSystemStudio/cs-studio/issues/1929/labels{/name}","comments_url":"https://api.github.com/repos/ControlSystemStudio/cs-studio/issues/1929/comments","events_url":"https://api.github.com/repos/ControlSystemStudio/cs-studio/issues/1929/events","html_url":"https://github.com/ControlSystemStudio/cs-studio/issues/1929","id":184174994,"number":1929,"title":"RDBArchiveReader - Postgres connection & multi-threading issue","user":{"login":"rosarp","id":1832625,"avatar_url":"https://avatars.githubusercontent.com/u/1832625?v=3","gravatar_id":"","url":"https://api.github.com/users/rosarp","html_url":"https://github.com/rosarp","followers_url":"https://api.github.com/users/rosarp/followers","following_url":"https://api.github.com/users/rosarp/following{/other_user}","gists_url":"https://api.github.com/users/rosarp/gists{/gist_id}","starred_url":"https://api.github.com/users/rosarp/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rosarp/subscriptions","organizations_url":"https://api.github.com/users/rosarp/orgs","repos_url":"https://api.github.com/users/rosarp/repos","events_url":"https://api.github.com/users/rosarp/events{/privacy}","received_events_url":"https://api.github.com/users/rosarp/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-10-20T09:12:43Z","updated_at":"2016-10-20T15:27:15Z","closed_at":null,"body":"Plotting a PV over a long period of time will end up in making a request to the\r\nBEAUTY RDB which will take many minutes - the progress view will show the\r\nbackground operation.\r\n\r\nDuring the execution of the query, it is possible to change the time request\r\nfor the plot by changing start/end time or by using the panning tool. In this\r\ncase, the previous request will be aborted and a new one sent.\r\n\r\nBut it happens that interacting with the plot properties view without changing\r\nthe time scale will end unexpectedly the request with the following exception: \r\n\r\n```\r\n2016-10-13 07:20:35.861 WARNING [Thread 151]\r\norg.csstudio.trends.databrowser2.ui.Controller$1 (archiveFetchFailed) - No\r\narchived data for UTIL-S15-0000:15KV_ACT_POWER\r\norg.postgresql.util.PSQLException: ERROR: current transaction is aborted,\r\ncommands ignored until end of transaction block\r\n    at\r\norg.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)\r\n    at\r\norg.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)\r\n    at\r\norg.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)\r\n    at\r\norg.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)\r\n    at\r\norg.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)\r\n    at\r\norg.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:285)\r\n    at\r\norg.csstudio.archive.reader.rdb.RDBArchiveReader.getStatusValues(RDBArchiveReader.java:166)\r\n    at\r\norg.csstudio.archive.reader.rdb.RDBArchiveReader.<init>(RDBArchiveReader.java:134)\r\n    at\r\norg.csstudio.archive.reader.rdb.RDBArchiveReader.<init>(RDBArchiveReader.java:85)\r\n    at\r\norg.csstudio.archive.reader.rdb.RDBArchiveReaderFactory.getArchiveReader(RDBArchiveReaderFactory.java:48)\r\n    at\r\norg.csstudio.archive.reader.ArchiveRepository.getArchiveReader(ArchiveRepository.java:111)\r\n    at\r\norg.csstudio.trends.databrowser2.archive.ArchiveFetchJob$WorkerThread.run(ArchiveFetchJob.java:124)\r\n    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\r\n    at java.util.concurrent.FutureTask.run(FutureTask.java:266)\r\n    at\r\njava.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)\r\n    at\r\njava.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)\r\n    at java.lang.Thread.run(Thread.java:745)\r\n\r\n```\r\n\r\nFrom then on, the RDB connection has to be re-initialised and the simple way to\r\ndo that is to restart cs-studio.\r\n\r\nSolution:\r\nI found two issues here. \r\n1. When Postres connection is canceled, it needs to be cleaned up and created again.\r\n2. Worker thread needs little more time to properly cancel and cleanup reader before new ArchiveFetchJob is created by Controller. \r\n\r\nTo resolve this, I suggest below changes.\r\n\r\n```\r\ndiff --git a/applications/archive/archive-plugins/org.csstudio.archive.reader.rdb/src/org/csstudio/archive/reader/rdb/RDBArchiveReader.java b/applications/archive/archive-plugins/org.csstudio.archive.reader.rdb/src/org/csstudio/archive/reader/rdb/RDBArchiveReader.java\r\nindex d853af7..31e2fa5 100644\r\n--- a/applications/archive/archive-plugins/org.csstudio.archive.reader.rdb/src/org/csstudio/archive/reader/rdb/RDBArchiveReader.java\r\n+++ b/applications/archive/archive-plugins/org.csstudio.archive.reader.rdb/src/org/csstudio/archive/reader/rdb/RDBArchiveReader.java\r\n@@ -504,6 +504,9 @@\r\n                     // does NOT stop an ongoing Oracle query!\r\n                     // Only this seems to do it:\r\n                     statement.cancel();\r\n+                    // When connection is canceled\r\n+                    // it gets broken and needs to re-establish again\r\n+                    ConnectionCache.clean();\r\n                 }\r\n                 catch (Exception ex)\r\n                 {\r\n@@ -519,7 +522,6 @@\r\n     public void close()\r\n     {\r\n         cancel();\r\n-        ConnectionCache.release(rdb);\r\n     }\r\n\r\n     @Override\r\ndiff --git a/applications/databrowser/databrowser-plugins/org.csstudio.trends.databrowser2/src/org/csstudio/trends/databrowser2/ui/Controller.java b/applications/databrowser/databrowser-plugins/org.csstudio.trends.databrowser2/src/org/csstudio/trends/databrowser2/ui/Controller.java\r\nindex 1ad33a1..039ff0e 100644\r\n--- a/applications/databrowser/databrowser-plugins/org.csstudio.trends.databrowser2/src/org/csstudio/trends/databrowser2/ui/Controller.java\r\n+++ b/applications/databrowser/databrowser-plugins/org.csstudio.trends.databrowser2/src/org/csstudio/trends/databrowser2/ui/Controller.java\r\n@@ -702,6 +702,6 @@\r\n             job = new ArchiveFetchJob(pv_item, start, end, archive_fetch_listener);\r\n             archive_fetch_jobs.add(job);\r\n         }\r\n-        job.schedule();\r\n+        job.schedule(1000);\r\n     }\r\n }\r\n\r\n```\r\n\r\n\r\n\r\nThese changes will be required at least by Postgres Db. With other Db, if this issue is not reproducible, then it might have performance impact.\r\n\r\nP.S. : I am working ITER CSS."},"comment":{"url":"https://api.github.com/repos/ControlSystemStudio/cs-studio/issues/comments/255139629","html_url":"https://github.com/ControlSystemStudio/cs-studio/issues/1929#issuecomment-255139629","issue_url":"https://api.github.com/repos/ControlSystemStudio/cs-studio/issues/1929","id":255139629,"user":{"login":"kasemir","id":1932421,"avatar_url":"https://avatars.githubusercontent.com/u/1932421?v=3","gravatar_id":"","url":"https://api.github.com/users/kasemir","html_url":"https://github.com/kasemir","followers_url":"https://api.github.com/users/kasemir/followers","following_url":"https://api.github.com/users/kasemir/following{/other_user}","gists_url":"https://api.github.com/users/kasemir/gists{/gist_id}","starred_url":"https://api.github.com/users/kasemir/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kasemir/subscriptions","organizations_url":"https://api.github.com/users/kasemir/orgs","repos_url":"https://api.github.com/users/kasemir/repos","events_url":"https://api.github.com/users/kasemir/events{/privacy}","received_events_url":"https://api.github.com/users/kasemir/received_events","type":"User","site_admin":false},"created_at":"2016-10-20T15:27:15Z","updated_at":"2016-10-20T15:27:15Z","body":" > When Postres connection is canceled, it needs to be cleaned up and created again.\r\n\r\nWe're not cancelling the Connection. We're cancelling a Statement.\r\n\r\nAre you saying that the Postgres implementation is broken: Cancelling a Statement will render the complete Connection disfunctional?\r\nThis is the JDBC Statement#cancel javadoc:\r\n```\r\n    /**\r\n     * Cancels this <code>Statement</code> object if both the DBMS and\r\n     * driver support aborting an SQL statement.\r\n     * This method can be used by one thread to cancel a statement that\r\n     * is being executed by another thread.\r\n     *\r\n     * @exception SQLException if a database access error occurs or\r\n     * this method is called on a closed <code>Statement</code>\r\n     * @exception SQLFeatureNotSupportedException if the JDBC driver does not support\r\n     * this method\r\n     */\r\n    void cancel() throws SQLException;\r\n```\r\nBased on the documentation, a specific implementation (like Postgres) may not support cancellation, but that would be indicated by a `SQLFeatureNotSupportedException`.\r\n\r\nI think what's really happening is indicated by the error\r\n```\r\nPSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block\r\n```\r\nI don't think we call `setAutoCommit(false)` on that connection, but for some reason Postgres seems to consider the cancelled statement a situation where the connection is in the middle of an incomplete transaction.\r\n\r\nSo maybe try this in RDBArchiveReader#cancel:\r\n````\r\n                    # Cancel ongoing statement because reader is closed,\r\n                    # another request was started, we no longer care about this old request\r\n                    statement.cancel();\r\n                    # For Postgres: Close the ongoing transaction\r\n                    rdb.getConnection().rollback();\r\n\r\n```\r\n"}}
{"actor":{"display_login":"kasemir"}}