pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica'
3+ messages / 2 participants
[nested] [flat]
* pgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica'
@ 2019-07-05 10:17 Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Achilleas Mantzios @ 2019-07-05 10:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica'
@ 2019-07-05 16:42 Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Eisentraut @ 2019-07-05 16:42 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org
On 2019-07-05 12:17, Achilleas Mantzios wrote:
> 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?
I think this is generally OK.
But if you are doing this, you probably have a replication system that
is running under the replica session role. The question is then whether
that replication system is OK with it. If you create replication sets
that contain parts of foreign key relationships and not others then you
might run into constraint violations.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica'
@ 2019-07-08 06:19 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 0 replies; 3+ messages in thread
From: Achilleas Mantzios @ 2019-07-08 06:19 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin@lists.postgresql.org
On 5/7/19 7:42 μ.μ., Peter Eisentraut wrote:
> On 2019-07-05 12:17, Achilleas Mantzios wrote:
>> 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?
> I think this is generally OK.
>
> But if you are doing this, you probably have a replication system that
> is running under the replica session role. The question is then whether
> that replication system is OK with it. If you create replication sets
> that contain parts of foreign key relationships and not others then you
> might run into constraint violations.
Thank you Peter. This is based on DBMIrror, I am running a heavily changed version of it for over 15 years, the FK aspect was one of the initial concerns dealt by the code. The whole idea behind it
was to satisfy FK dependencies in a minimalistic manner : include only those FK rows that are necessary.
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-07-08 06:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-05 10:17 pgsql 8.3 : force FK (and consequently ON DELETE CASCADE) to be run under session_replication_role TO 'replica' Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-07-05 16:42 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2019-07-08 06:19 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
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