From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | PegoraroF10 <marcos(at)f10(dot)com(dot)br>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problem with trigger makes Detail record be invalid |
Date: | 2018-04-20 13:55:21 |
Message-ID: | 4f528d7d-90d0-55d1-f017-b72d730080db@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 04/19/2018 06:49 PM, PegoraroF10 wrote:
> Correct, that delete done a partial commit. And this is absolutely
> unacceptable.
Yet a known possible outcome. See the section on Triggers towards bottom
of page:
https://en.wikipedia.org/wiki/Foreign_key
> I know I did that trigger incorrectly but referential integrity is
> obligatory.
I would agree if the FK relationship was entirely driven by the system
trigger e.g:
alter table Detail add constraint FKMasterDetail foreign key (Master_ID)
references Master(ID) on update cascade on delete cascade;
As soon as you added your UPDATE/DELETE trigger you took on
responsibility for how the data was passed around. I understand that
this was not communicated as well as it should be in the docs.
> Imagine if I have a database crash and need to restore as soon as possible.
> How much time I´ll spend removing those records from a backup to get entire
> database restored properly.
Myself, having written more then my fair share of poorly thought out
trigger functions, I test new ones extensively before I release them
into the wild.
>
> Well, I´ll review all my triggers. And I have hundreds of them.
>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2018-04-20 14:21:05 | Re: Problem with trigger makes Detail record be invalid |
Previous Message | Hannes Erven | 2018-04-20 13:28:06 | Re: postgres on physical replica crashes |