View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006779 | SymmetricDS | Bug | public | 2025-03-21 21:00 | 2025-05-06 02:45 |
| Reporter | pbelov | Assigned To | pbelov | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.16.0 | ||||
| Target Version | 3.16.2 | Fixed in Version | 3.16.2 | ||
| Summary | 0006779: Postgresql DDL trigger fails when sym_trigger_hist is located in another schema | ||||
| Description | Function for DDL trigger (on Postgresql) fails when sym_trigger_hist is located in another schema. The error occurs in the function fsym_on_filtered_ddl, which implements the DDL trigger for Postgres endpoint. ERROR: relation "sym_trigger_hist" does not exist LINE 1: ...id, source_table_name from sym_trigge... ^ QUERY: select trigger_hist_id, source_table_name from sym_trigger_hist where upper(source_table_name) = upper(tableName) and inactive_time is null CONTEXT: PL/pgSQL function tds.fsym_on_all_ddl() line 25 at SQL statement | ||||
| Steps To Reproduce | Create a non-default schemas in Postgresql: "sds" Install SymmetricDS runtime tables into the in the "sds" schema with connection string db.url=jdbc:postgresql://localhost:5432/postgres?currentSchema=sds Configure parameter trigger.capture.ddl.changes=true Create test tables in the "public" or other "business" schema. Configure triggers on test tables. Add column to a test table - as a non-SymmetricDS user in a separate connection targeting the "public" or "business" schema (SET search_path = business). Observe the error relation "sym_trigger_hist" does not exist. SET search_path = business; CREATE TABLE ddl_trigger_test1 ( test_id BIGINT NOT NULL, name char(20) NOT NULL, CONSTRAINT ddl_trigger_test1_PK PRIMARY KEY (test_id) ); ALTER TABLE ddl_trigger_test1 add column test2 BIGINT; SQL Error [42P01]: ERROR: relation "sym_trigger_hist" does not exist Where: PL/pgSQL function sym315client5.fsym_on_filtered_ddl() line 25 at SQL statement | ||||
| Additional Information | The error occurs in the function fsym_on_filtered_ddl, which implements the DDL trigger for Postgres endpoint. PGSQL code references two tables directly: sym_trigger_hist, sym_trigger while they are located in another schema (which is not part of the search_path ). | ||||
| Tags | ddl/schema, dialect: postgresql | ||||
| related to | 0004524 | acknowledged | Add support for PostgreSQL search_path instead of target schema name |
|
|
Merged PRs: https://github.com/JumpMind/symmetric-ds/pull/262 into the 3.15.16 version https://github.com/JumpMind/symmetric-ds/pull/267 into the 3.16.2 version |
|
|
Included in the 3.16.2 release |
|
SymmetricDS: 3.15 714dd1a7 2025-05-01 19:36:24 Committer: GitHub Details Diff |
0006779: Capture DDL events PostgreSQL from non-default schema (0000262) * Add VSCode and MAC exemptions * Explicitly specify schema for SymmetricDS when capturing DDL changes (potentially occurring in other schemas). * Fix object prefix in Postgres DDL triggers |
Affected Issues 0006779 |
|
| mod - .gitignore | Diff File | ||
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/PostgreSqlTriggerTemplate.java | Diff File | ||
|
SymmetricDS: 3.16 254faf81 2025-05-05 14:53:26 Committer: GitHub Details Diff |
0006779: Capture DDL events PostgreSQL from non-default schema (0000267) * Cherry-picked commit for 0006779 from 3.15: Capture DDL events PostgreSQL from non-default schema. * Explicitly specify schema for SymmetricDS when capturing DDL changes (potentially occurring in other schemas). |
Affected Issues 0006779 |
|
| mod - .gitignore | Diff File | ||
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/PostgreSqlTriggerTemplate.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2025-03-21 21:00 | pbelov | New Issue | |
| 2025-03-21 21:00 | pbelov | Status | new => assigned |
| 2025-03-21 21:00 | pbelov | Assigned To | => pbelov |
| 2025-03-21 21:00 | pbelov | Tag Attached: dialect: postgresql | |
| 2025-03-22 13:15 | pbelov | Steps to Reproduce Updated | View Revisions |
| 2025-03-22 13:29 | pbelov | Steps to Reproduce Updated | View Revisions |
| 2025-03-22 13:30 | pbelov | Status | assigned => confirmed |
| 2025-03-22 13:30 | pbelov | Steps to Reproduce Updated | View Revisions |
| 2025-03-22 13:32 | pbelov | Steps to Reproduce Updated | View Revisions |
| 2025-03-26 17:07 | emiller | Relationship added | related to 0004524 |
| 2025-03-27 21:19 | pbelov | Status | confirmed => assigned |
| 2025-03-27 21:19 | pbelov | Description Updated | View Revisions |
| 2025-03-27 21:19 | pbelov | Additional Information Updated | View Revisions |
| 2025-03-27 21:40 | pbelov | Tag Attached: ddl/schema | |
| 2025-03-31 13:19 | pbelov | Target Version | 3.16.1 => 3.16.2 |
| 2025-05-01 20:00 | pbelov | Changeset attached | => SymmetricDS 3.15 714dd1a7 |
| 2025-05-05 14:52 | pbelov | Status | assigned => resolved |
| 2025-05-05 14:52 | pbelov | Resolution | open => fixed |
| 2025-05-05 14:52 | pbelov | Fixed in Version | => 3.16.2 |
| 2025-05-05 14:52 | pbelov | Note Added: 0002871 | |
| 2025-05-05 15:00 | pbelov | Changeset attached | => SymmetricDS 3.16 254faf81 |
| 2025-05-06 02:45 | pbelov | Note Added: 0002887 | |
| 2025-05-06 02:45 | pbelov | Status | resolved => closed |