Re: a misbehavior of partition row movement (?) - Mailing list pgsql-hackers

Hi,

On Mon, Dec 21, 2020 at 11:30 PM Arne Roland <A.Roland@index.de> wrote:
> thanks for the quick reply! Sadly I have been busy and the second part of your patch does no longer apply in
src/include/nodes/execnodes.h:497.

I don't see any problem with applying the patch.  Are you sure you're
applying the patch to the correct git branch?  The patch is meant to
be applied to the development (master) branch.

> I'm sorry, I should have been more careful rereading my posts. The case I meant is the one below. I checked the
threadagain. I can barely believe, I didn't post such an example along back then. Sorry for the confusion!
 

No worries, thanks for the follow up.

> create table a (id serial, primary key (id)) partition by range (id);
> create table b (id serial,  primary key (id)) partition by range (id);
> create table a1 partition of a for values from (1) to (2);
> create table a2 partition of a for values from (2) to (3);
> create table b1 partition of b for values from (1) to (2);
> create table b2 partition of b for values from (2) to (3);
> insert into a (id) values (1);
> insert into b (id) values (1);
>
> create or replace function del_trig_fkt()
>  returns trigger
>  language plpgsql
> as $$
>   begin
>     raise notice 'Deleted!';
>     return old;
>   end;
> $$;
> create trigger a_del_trig after delete on a for each row execute function del_trig_fkt();
> create or replace function public.upd_trig_fkt()
>  returns trigger
>  language plpgsql
> as $function$
> begin
>   raise notice 'Updated!';
>   return new;
> end;
> $function$;
> create trigger a_upd_trig after update on a for each row execute function upd_trig_fkt();
>
> update a set id=2;

The output for this I get with (or without) the patch is:

NOTICE:  Deleted!
UPDATE 1

> To me the issue seems to have litte to do with the fact that the trigger is executed on the leaf node in itself, but
ratherwe lack the infrastructure to track whether the tuple is removed or only rerouted.
 

This behavior of partition key updates with regard to *user-defined*
AFTER triggers is documented:

https://www.postgresql.org/docs/current/trigger-definition.html

"As far as AFTER ROW triggers are concerned, AFTER DELETE and AFTER
INSERT triggers are applied; but AFTER UPDATE triggers are not applied
because the UPDATE has been converted to a DELETE and an INSERT."

I don't quite recall if the decision to implement it like this was
based on assuming that this is what users would like to see happen in
this case or the perceived difficulty of implementing it the other way
around, that is, of firing AFTER UPDATE triggers in this case.

As for the original issue of this thread, it happens to be fixed by
firing the *internal* AFTER UPDATE triggers that are involved in
enforcing the foreign key even if the UPDATE has been turned into
DELETE+INSERT, which it makes sense to do, because what can happen
today with CASCADE triggers does not seem like a useful behavior by
any measure.

-- 
Amit Langote
EDB: http://www.enterprisedb.com