Re: a misbehavior of partition row movement (?)

From: Arne Roland <A(dot)Roland(at)index(dot)de>
To: Amit Langote <amitlangote09(at)gmail(dot)com>
Cc: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: a misbehavior of partition row movement (?)
Date: 2020-12-21 14:30:06
Message-ID: 051b188a0011412bbecefc0025d5cb1b@index.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Amit,

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'm sorry, I should have been more careful rereading my posts. The case I meant is the one below. I checked the thread again. I can barely believe, I didn't post such an example along back then. Sorry for the confusion!

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;

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 rather we lack the infrastructure to track whether the tuple is removed or only rerouted.

Regards
Arne

________________________________
From: Amit Langote <amitlangote09(at)gmail(dot)com>
Sent: Tuesday, December 15, 2020 4:45:19 AM
To: Arne Roland
Cc: Tomas Vondra; David G. Johnston; PostgreSQL-development
Subject: Re: a misbehavior of partition row movement (?)

On Tue, Dec 15, 2020 at 12:43 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> Quoting your original example:
>
> drop table a, b;
> create table a (id serial, primary key (id)) partition by range (id);
> create table b (id serial, primary key (id)) partition by range (id);
> alter table b add constraint a_fk foreign key (id) references a (id)
> on delete cascade;
> 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);
>
> -- correctly errors out instead of silently performing the ON DELETE CASCADE
> update a set id=2;
> ERROR: update or delete on table "a" violates foreign key constraint
> "a_fk" on table "b"
> DETAIL: Key (id)=(1) is still referenced from table "b".
>
> select * from b;
> id
> ----
> 1
> (1 row)
>
> Changing the example to specify ON UPDATE CASCADE:
>
> drop table a, b;
> create table a (id serial, primary key (id)) partition by range (id);
> create table b (id serial, primary key (id)) partition by range (id);
> alter table b add constraint a_fk foreign key (id) references a (id)
> on delete cascade;

Oops, I copy-pasted the wrong block of text from my terminal. I meant:

alter table b add constraint a_fk foreign key (id) references a (id)
on delete cascade on update cascade;

> 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);
>
> -- correctly applies ON UPDATE CASCADE action
> update a set id=2;
> UPDATE 1
>
> select * from b;
> id
> ----
> 2
> (1 row)

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Konstantin Knizhnik 2020-12-21 15:22:23 Inconsistent/confusing handling of tablespaces for partitioned tables
Previous Message Laurenz Albe 2020-12-21 14:20:51 Re: allow to \dtS+ pg_toast.*