Triggers on the subscriber

[valeriaselivanova]

Hi! I'm trying to create triggers on the subscriber that will work before insert in the tables. I tried different configurations but it didn't work. My triggers work when testing them on hand insert but not while replicating. Is it a bug or how can I make it work?

Here I'm creating a function that will redirect data to the different schema depending on the subscription and triggers for a few tables.

CREATE OR REPLACE FUNCTION public.redirect_insert_generic()
RETURNS TRIGGER AS
$$
DECLARE
    sub_name TEXT;
    target_table TEXT;
    sub_rec RECORD;
BEGIN
    -- getting all active subscription
    FOR sub_rec IN
        SELECT subscription_name
        FROM pglogical.show_subscription_status()
        WHERE status = 'replicating'
    LOOP
        sub_name := sub_rec.subscription_name;
        -- redirecting data
        IF sub_name = 'brazil_public_subscription' THEN
            target_table := format('brazil_public.%I', TG_TABLE_NAME);
            RAISE NOTICE 'Inserting into %', target_table;
            EXECUTE format('INSERT INTO %s SELECT $1.*', target_table) USING NEW;
        ELSIF sub_name = 'usa_public_subscription' THEN
            target_table := format('grain_us_public.%I', TG_TABLE_NAME);
            RAISE NOTICE 'Inserting into %', target_table;
            EXECUTE format('INSERT INTO %s SELECT $1.*', target_table) USING NEW;
        ELSIF sub_name = 'russia_public_subscription' THEN
            target_table := format('grain_public.%I', TG_TABLE_NAME);
            RAISE NOTICE 'Inserting into %', target_table;
            EXECUTE format('INSERT INTO %s SELECT $1.*', target_table) USING NEW;
        ELSE
            RAISE EXCEPTION 'Unknown subscription: %', sub_name;
        END IF;
    END LOOP;

    RETURN NULL; -- Prevent the insert in the original table
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE'
    LOOP
        EXECUTE format('
            CREATE TRIGGER redirect_insert_%I_trigger
            BEFORE INSERT ON public.%I
            FOR EACH ROW
            EXECUTE FUNCTION public.redirect_insert_generic();
        ', rec.table_name, rec.table_name);
    END LOOP;
END;
$$;

[moench-tegeder]

You did not mention whether you saw https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE/docs#triggers and marked the trigger as ENABLE REPLICA/ENABLE ALWAYS (via ALTER TABLE ).

[valeriaselivanova]

Yeah, sorry. I haven't marked my triggers the first time. Now I tried this

DO $$
DECLARE
    trig record;
BEGIN
    FOR trig IN
        SELECT event_object_table, trigger_name
        FROM information_schema.triggers
        WHERE trigger_name LIKE 'redirect_insert%'
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(trig.event_object_table) ||
                ' ENABLE ALWAYS TRIGGER ' || quote_ident(trig.trigger_name);
    END LOOP;
END $$;

Triggers started to work but now the replication doesn't work correctly. For example, I have a table with 70k rows and only 100 were replicated.