0

Scenario:

We have two tables, first one call main_table. second one call look_up table. we need replicate main_table from other DB instance via Postgres build-in logical replication.

in the meantime, we have to filter some rows based on look_up table.

we create a trigger for it.

CREATE OR REPLACE FUNCTION record_filter() RETURNS trigger AS $$
DECLARE
    a_id integer;
BEGIN
    SELECT INTO a_id id from lookup_table where lookup_table.id = NEW.id;
 
    IF (a_id IS NOT NULL) AND (other logic) THEN
        RETURN NEW;
    END IF;
    RETURN null;   
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER record_update 
BEFORE INSERT OR UPDATE ON main_table 
FOR EACH ROW EXECUTE PROCEDURE record_filter();

ALTER TABLE main_table ENABLE ALWAYS TRIGGER record_update;

when we do insert locally from psql shell. trigger was fired and works as expected. however, once we turn on the logical replication. we get errors.

2021-09-29 04:28:21 UTC::@:[27535]:LOG: logical replication table synchronization worker for subscription "subscription_0", table "main_table" has started
2021-09-29 04:28:21 UTC::@:[27535]:ERROR: relation "lookup_table" does not exist at character 26
2021-09-29 04:28:21 UTC::@:[10813]:LOG: background worker "logical replication worker" (PID 27535) exited with exit code 1

Want to understand why?

Ben
  • 1
  • What if you add the schema, like `SELECT ... FROM schema_name.lookup_table ...`? – Laurenz Albe Sep 29 '21 at 21:17
  • I had a similar error and adding the `public.` schema fixed it. Is there a way to default the schema? I'm calling some extension' functions that doesn't explicitly specify it and for this reason I'm getting errors. – loryruta Oct 18 '21 at 11:15

0 Answers0