View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005783 | SymmetricDS | Improvement | public | 2023-04-10 16:30 | 2023-04-11 18:00 |
| Reporter | ei99045 | Assigned To | emiller | ||
| Priority | low | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.14.6 | ||||
| Target Version | 3.14.7 | Fixed in Version | 3.14.7 | ||
| Summary | 0005783: SymmetricDS seems to require Postgres 11, but the docs don't reflect that | ||||
| Description | While upgrading from Symmetric 3.10 to Symmetric 3.14 I got the following error: 2023-04-10 17:20:17,323 INFO [headoffice] [TriggerRouterService] [symmetric-engine-startup-1] Done synchronizing triggers 2023-04-10 17:20:17,323 ERROR [headoffice] [AbstractSymmetricEngine] [symmetric-engine-startup-1] An error occurred while starting SymmetricDS StackTraceKey.init [SqlException:1643060482] org.jumpmind.db.sql.SqlException: ERROR: relation "pg_event_trigger" does not exist Position: 22 at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297) at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:494) at org.jumpmind.db.sql.JdbcSqlTemplate.queryForObject(JdbcSqlTemplate.java:163) at org.jumpmind.db.sql.AbstractSqlTemplate.queryForInt(AbstractSqlTemplate.java:59) at org.jumpmind.symmetric.db.postgresql.PostgreSqlSymmetricDialect.doesDdlTriggerExist(PostgreSqlSymmetricDialect.java:223) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDdlTriggers(TriggerRouterService.java:1806) at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:1471) at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:1416) at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:1412) at org.jumpmind.symmetric.db.DatabaseUpgradeListener.afterUpgrade(DatabaseUpgradeListener.java:205) at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createOrAlterTablesIfNecessary(AbstractSymmetricDialect.java:543) at org.jumpmind.symmetric.db.AbstractSymmetricDialect.initTablesAndDatabaseObjects(AbstractSymmetricDialect.java:181) at org.jumpmind.symmetric.AbstractSymmetricEngine.setupDatabase(AbstractSymmetricEngine.java:455) at org.jumpmind.symmetric.AbstractSymmetricEngine.setup(AbstractSymmetricEngine.java:428) at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:613) at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:601) at org.jumpmind.symmetric.ClientSymmetricEngine.start(ClientSymmetricEngine.java:259) at org.jumpmind.symmetric.web.SymmetricEngineStarter.run(SymmetricEngineStarter.java:42) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: org.postgresql.util.PSQLException: ERROR: relation "pg_event_trigger" does not exist Position: 22 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) at org.jumpmind.db.sql.JdbcSqlTemplate$1.execute(JdbcSqlTemplate.java:174) at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:492) ... 19 more The backend is Postgres version 9.5 and, according to the Postgres docs, the pg_event_trigger relation doesn't exist before version 11: https://www.postgresql.org/docs/current/catalog-pg-event-trigger.html The SymmetricDS User Guide doesn't acknowledge this yet: https://www.symmetricds.org/doc/3.14/html/user-guide.html#_postgresql | ||||
| Tags | dialect: postgresql, documentation, upgrade | ||||
|
|
This seems dependency on Postgres v.11 seems to have been introduced by this commit: https://github.com/JumpMind/symmetric-ds/commit/487d98dfa4430df698fba510061ab9c55201250e linked to this issue: https://www.symmetricds.org/issues/view.php?id=5652 Ideally, it should be possible not do depend on v.11 and still support versions 9 and 10. |
|
|
I'd like to make a change so that SymmetricDS checks that the Postgres version supports event triggers before querying the pg_event_trigger table. However, I found that support for event triggers was added in Postgres 9.3. I confirmed this by testing it using a Postgres 9.3.25 Docker image. You can see it in the documentation here: https://www.postgresql.org/docs/release/9.3.0/ https://www.postgresql.org/docs/9.3/catalog-pg-event-trigger.html Can you run this query to check whether the table exists? select * from information_schema.tables where table_name='pg_event_trigger'; |
|
|
The version I'm running on most nodes is 9.2, so it makes sense that it doesn't find the table: bash-4.1$ psql arpux-ho psql (9.2.24) Type "help" for help. arpux-ho=# select * from information_schema.tables where table_name='pg_event_trigger'; table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action ---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+-------------- - (0 rows) arpux-ho=# |
|
SymmetricDS: 3.14 9e7c2f30 2023-04-11 17:50:39 evan-miller-jumpmind Details Diff |
0005783: Prevented SymmetricDS from checking for DDL triggers when the database does not support them |
Affected Issues 0005783 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSql2008SymmetricDialect.java | Diff File | ||
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleSymmetricDialect.java | Diff File | ||
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/PostgreSqlSymmetricDialect.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractSymmetricDialect.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/db/ISymmetricDialect.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterService.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2023-04-10 16:30 | ei99045 | New Issue | |
| 2023-04-11 07:37 | ei99045 | Tag Attached: dialect: postgresql | |
| 2023-04-11 07:37 | ei99045 | Tag Attached: documentation | |
| 2023-04-11 07:39 | ei99045 | Tag Attached: upgrade | |
| 2023-04-11 08:54 | ei99045 | Note Added: 0002304 | |
| 2023-04-11 15:41 | emiller | Assigned To | => emiller |
| 2023-04-11 15:41 | emiller | Status | new => feedback |
| 2023-04-11 15:41 | emiller | Note Added: 0002305 | |
| 2023-04-11 15:42 | emiller | Relationship added | related to 0005652 |
| 2023-04-11 15:58 | ei99045 | Note Added: 0002306 | |
| 2023-04-11 15:58 | ei99045 | Status | feedback => assigned |
| 2023-04-11 17:51 | emiller | Status | assigned => resolved |
| 2023-04-11 17:51 | emiller | Resolution | open => fixed |
| 2023-04-11 17:51 | emiller | Fixed in Version | => 3.14.7 |
| 2023-04-11 18:00 | Changeset attached | => SymmetricDS 3.14 9e7c2f30 | |
| 2023-05-12 19:49 | admin | Status | resolved => closed |