A Heroku Postgres delete trigger is triggering and successfully deleting its target Postgres rows, however the delete isn't syncing over to Salesforce via Heroku Connect. When the same delete is done manually it syncs to Salesforce without issue.
Are there any special considerations for Postgres delete triggers that need to sync over to Salesforce via Heroku Connect, e.g., setting certain fields before performing the delete?
From Heroku support...
Connect uses an almost-entirely-unused Postgres session variable called xmlbinary as a flag to prevent Connect's own writes to a table from triggering additional _trigger_log entries. If these deletes are happening as a result of Connect activity (and therefore executing in the context of a Connect database connection), Connect is likely seeing this xmlbinary variable and ignoring the change. If you can change the value of xmlbinary to base64 for the duration of your trigger, it should fix it. That would look like this:
CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
DECLARE
oldxmlbinary varchar;
BEGIN
-- Save old value
oldxmlbinary := get_xmlbinary();
-- Change value to ensure writing to _trigger_log is enabled
SET SESSION xmlbinary TO 'base64';
-- Perform your trigger functions here
-- Reset the value
EXECUTE 'SET SESSION xmlbinary TO ' || oldxmlbinary;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;