On Fri, Jun 2, 2017 at 7:07 AM, Amit Khandekar <amitdkhan.pg@gmail.com> wrote:
> So, according to that, below would be the logic :
>
> Run partition constraint check on the original NEW row.
> If it succeeds :
> {
> Fire BR UPDATE trigger on the original partition.
> Run partition constraint check again with the modified NEW row
> (may be do this only if the trigger modified the partition key)
> If it fails,
> abort.
> Else
> proceed with the usual local update.
> }
> else
> {
> Fire BR UPDATE trigger on original partition.
> Find the right partition for the modified NEW row.
> If it is the same partition,
> proceed with the usual local update.
> else
> do the row movement.
> }
Sure, that sounds about right, although the "Fire BR UPDATE trigger on
the original partition." is the same in both branches, so I'm not
quite sure why you have that in the "if" block.
>> Actually, it seems like that's probably the
>> *easiest* behavior to implement. Otherwise, you might fire triggers,
>> discover that you need to re-route the tuple, and then ... fire
>> triggers again on the new partition, which might reroute it again?
>
> Why would update BR trigger fire on the new partition ? On the new
> partition, only BR INSERT trigger would fire if at all we decide to
> fire delete+insert triggers. And insert trigger would not again cause
> the tuple to be re-routed because it's an insert.
OK, sure, that makes sense. I guess it's really the insert case that
I was worried about -- if we have a BEFORE ROW INSERT trigger and it
changes the tuple and we reroute it, I think we'd have to fire the
BEFORE ROW INSERT on the new partition, which might change the tuple
again and cause yet another reroute, and in this worst case this is an
infinite loop. But it sounds like we're going to fix that problem --
I think correctly -- by only ever allowing the tuple to be routed
once. If some trigger tries to make a change the tuple after that
such that re-routing is required, they get an error. And what you are
describing here seems like it will be fine.
> But now I think you are saying, the row that is being inserted into
> the new partition might get again modified by the INSERT trigger on
> the new partition, which might in turn cause it to fail the new
> partition constraint. But in that case, it will not cause another row
> movement, because in the new partition, it's an INSERT, not an UPDATE,
> so the operation would end there, aborted.
Yeah, that's what I was worried about. I didn't want a row movement
to be able to trigger another row movement and so on ad infinitum.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company