On Fri, May 12, 2017 at 4:17 PM, Amit Khandekar <amitdkhan.pg@gmail.com> wrote: > Option 3 > -------- > > BR, AR delete triggers on source partition > BR, AR insert triggers on destination partition. > > Rationale : > Since the update is converted to delete+insert, just skip the update > triggers completely.
+1 to option3 Generally, BR triggers are used for updating the ROW value and AR triggers to VALIDATE the row or to modify some other tables. So it seems that we can fire the triggers what is actual operation is happening at the partition level.
For source partition, it's only the delete operation (no update happened) so we fire delete triggers and for the destination only insert operations so fire only inserts triggers. That will keep the things simple. And, it will also be in sync with the actual partition level delete/insert operations.
We may argue that user might have declared only update triggers and as he has executed the update operation he may expect those triggers to get fired. But, I think this behaviour can be documented with the proper logic that if the user is updating the partition key then he must be ready with the Delete/Insert triggers also, he can not rely only upon update level triggers.
Right, that is even my concern. That user might had declared only update
triggers and when user executing UPDATE its expect it to get call - but
with option 3 its not happening.
In term of consistency option 1 looks better. Its doing the same what
its been implemented for the UPSERT - so that user might be already
aware of trigger behaviour. Plus if we document the behaviour then it
sounds correct -
- Original command was UPDATE so BR update
- Later found that its ROW movement - so BR delete followed by AR delete
- Then Insert in new partition - so BR INSERT followed by AR Insert.
But again I am not quite sure how good it will be to compare the partition
behaviour with the UPSERT.
Earlier I thought that option1 is better but later I think that this can complicate the situation as we are firing first BR update then BR delete and can change the row multiple time and defining such behaviour can be complicated. -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com
