View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006990 | SymmetricDS | Improvement | public | 2025-07-09 17:15 | 2025-08-07 15:49 |
| Reporter | pmarzullo | Assigned To | emiller | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.16.0 | ||||
| Target Version | 3.16.5 | Fixed in Version | 3.16.5 | ||
| Summary | 0006990: Detect when PostgreSQL version supports CreateOrReplace command and set dbInfo.TriggersCreateOrReplaceSupported property | ||||
| Description | In the Postgres DDL Builder, we should set the setTriggersCreateOrReplaceSupported(true) property so that we don't have to drop the triggers first before creating the trigger using the CREATE OR REPLACE construct. This is only applicable to PostgreSQL version 14 and later! Platform has the major and minor version numbers when building the DDL builder. | ||||
| Additional Information | Sourceforge report: https://sourceforge.net/p/symmetricds/discussion/739237/thread/bc2f456bc7/ Another example from PostgreSQL version 13, which CANNOT be fixed with this code change as there is no support for CREATE OR REPLACE TRIGGER command in that version: ... ERROR .... [TriggerRouterService] ... Failed to create triggers for public.my_table StackTraceKey.init [SqlException:1231213399] org.jumpmind.db.sql.SqlException: ERROR: must be owner of relation my_table 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.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:377) at org.jumpmind.db.sql.JdbcSqlTransaction.execute(JdbcSqlTransaction.java:284) at org.jumpmind.symmetric.db.postgresql.PostgreSqlSymmetricDialect.removeTrigger(PostgreSqlSymmetricDialect.java:233) at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessaryWithException(TriggerRouterService.java:2506) at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessary(TriggerRouterService.java:2415) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:2366) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTrigger(TriggerRouterService.java:2209) at org.jumpmind.symmetric.service.impl.TriggerRouterService$2.run(TriggerRouterService.java:2063) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:840) Caused by: org.postgresql.util.PSQLException: ERROR: must be owner of relation warehouse_amortization_types at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:341) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:326) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:302) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:297) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:182) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:182) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:291) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:284) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:375) ... 12 more | ||||
| Tags | dialect: postgresql | ||||
|
SymmetricDS: 3.16 49ddf641 2025-07-11 16:24:10 evan-miller-jumpmind Committer: GitHub Details Diff |
0006990: Detect PostgreSQL versions which supports CreateOrReplace command for triggers (#322) |
Affected Issues 0006990 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/PostgreSqlSymmetricDialect.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2025-07-09 17:15 | pmarzullo | New Issue | |
| 2025-07-09 17:15 | pmarzullo | Tag Attached: dialect: postgresql | |
| 2025-07-09 17:18 | pbelov | Description Updated | View Revisions |
| 2025-07-09 17:18 | pbelov | Additional Information Updated | View Revisions |
| 2025-07-09 17:18 | pbelov | Status | new => confirmed |
| 2025-07-10 18:28 | emiller | Assigned To | => emiller |
| 2025-07-10 18:28 | emiller | Status | confirmed => assigned |
| 2025-07-10 18:29 | emiller | Target Version | => 3.16.5 |
| 2025-07-11 16:25 | pbelov | Status | assigned => resolved |
| 2025-07-11 16:25 | pbelov | Fixed in Version | => 3.16.5 |
| 2025-07-11 16:25 | pbelov | Summary | Postgres databaseinfo should set setTriggersCreateOrReplaceSupported(true) property => Detect when PostgreSQL version supports CreateOrReplace command and set dbInfo.TriggersCreateOrReplaceSupported property |
| 2025-07-11 17:00 | Changeset attached | => SymmetricDS 3.16 49ddf641 | |
| 2025-07-16 18:46 | pbelov | Additional Information Updated | View Revisions |
| 2025-07-16 18:47 | pbelov | Additional Information Updated | View Revisions |
| 2025-08-07 15:49 | pbelov | Note Added: 0003303 | |
| 2025-08-07 15:49 | pbelov | Status | resolved => closed |