Re: UPDATE of partition key

On Wed, May 17, 2017 at 12:06 PM, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

> On Fri, May 12, 2017 at 4:17 PM, Amit Khandekar <amitdkhan(dot)pg(at)gmail(dot)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
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Rushabh Lathia