View Revisions: Issue #6990

Back to Issue
Summary 0006990: Detect when PostgreSQL version supports CreateOrReplace command and set dbInfo.TriggersCreateOrReplaceSupported property
Revision 2025-07-09 17:15 by pmarzullo
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 for version 14 and later.

Platform has the major and minor version numbers when building the DDL builder.
Revision 2025-07-09 17:18 by pbelov
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.
Revision 2025-07-09 17:15 by pmarzullo
Additional Information
Revision 2025-07-09 17:18 by pbelov
Additional Information Sourceforge report: https://sourceforge.net/p/symmetricds/discussion/739237/thread/bc2f456bc7/
Revision 2025-07-16 18:46 by pbelov
Additional Information Sourceforge report: https://sourceforge.net/p/symmetricds/discussion/739237/thread/bc2f456bc7/
Another example from PostgreSQL version 13:
... 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
Revision 2025-07-16 18:47 by pbelov
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