View Revisions: Issue #6779

All RevisionsBack to Issue
Summary 0006779: Postgresql DDL trigger fails when sym_trigger_hist is located in another schema
Revision 2025-03-22 13:32 by pbelov
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
Revision 2025-03-22 13:30 by pbelov
Steps To Reproduce Create at least one non-default schemas in Postgresql: "business" and "sds"
Create test tables in the "business" schema.
Install SymmetricDS runtime tables into the in the "business" schema with db.url=jdbc:postgresql://localhost:5432/postgres?currentSchema=sds
Configure parameter trigger.capture.ddl.changes=true
Configure triggers on test tables.
Add column to a test table - as a non-SymmetricDS user in a separate connection targeting the "business" schema.
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
Revision 2025-03-22 13:29 by pbelov
Steps To Reproduce Create two non-default schemas in Postgresql: "business" and "sds"
Create test tables in the "business" schema.
Install SymmetricDS runtime tables into the in the "business" schema with db.url=jdbc:postgresql://localhost:5432/postgres?currentSchema=sds
Configure parameter trigger.capture.ddl.changes=true
Configure triggers on test tables.
Add column to a test table - as a non-SymmetricDS user in a separate connection targeting the "business" schema.
Observe the error relation "sym_trigger_hist" does not exist.


SET search_path = public;

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
Revision 2025-03-22 13:15 by pbelov
Steps To Reproduce Create two non-default schemas in Postgresql: "business" and "sds"
Create test tables in the "business" schema.
Install SymmetricDS runtime tables into the in the "business" schema with db.url=jdbc:postgresql://localhost:5432/postgres?currentSchema=sds
Configure parameter trigger.capture.ddl.changes=true
Configure triggers on test tables.
Add column to a test table - as a non-SymmetricDS user in a separate connection targeting the "business" schema.
Observe the error relation "sym_trigger_hist" does not exist.
Revision 2025-03-21 21:00 by pbelov
Steps To Reproduce