pgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica'

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: pgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica'
Date: 2019-07-05 10:17:05
Message-ID: a0d82106-4ee8-f165-275d-53b3135a2fae@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,
first off, don't be put off by the version (8.3), please!
we run some replication statements code under with session_replication_role TO 'replica' ,
however this disables FK constraints and consequently some important ON DELETE CASCADE that must be run.

I tested this and this works :
psql -qt -c 'SELECT '\''ALTER TABLE mariner ENABLE ALWAYS TRIGGER "'\''|| tgname||'\''";'\'' from pg_trigger where tgconstrname='\''personal_email_sender_marinerid_fkey'\'' and tgisconstraint and
tgrelid='\''mariner'\''::regclass' | psql -f -

and then test with:
BEGIN ;
set session_replication_role TO 'replica';
DELETE FROM mariner where id = 23700;
SELECT * from personal_email_sender where marinerid = 23700;
 email | marinerid
-------+-----------
(0 rows)

^^^ and verify that the ON DELETE CASCADE is run.

So, besides the obvious comment, that one should not mess with system triggers, do you see any potential gotcha with this particular one?

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Peter Eisentraut 2019-07-05 16:42:40 Re: pgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica'
Previous Message William Sescu (Suva) 2019-07-04 06:15:25 AW: REINDEX VERBOSE DATABASE not working