pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Subject: pgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica'
Date: Fri, 5 Jul 2019 13:17:05 +0300
Message-ID: <a0d82106-4ee8-f165-275d-53b3135a2fae@matrix.gatewaynet.com> (raw)

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






view thread (3+ messages)  latest in thread

Message-ID: <a0d82106-4ee8-f165-275d-53b3135a2fae@matrix.gatewaynet.com>
Permalink:  ../a0d82106-4ee8-f165-275d-53b3135a2fae@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/a0d82106-4ee8-f165-275d-53b3135a2fae@matrix.gatewaynet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: achill@matrix.gatewaynet.com
  Subject: Re: pgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica'
  In-Reply-To: <a0d82106-4ee8-f165-275d-53b3135a2fae@matrix.gatewaynet.com>
  References: <a0d82106-4ee8-f165-275d-53b3135a2fae@matrix.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox