Re: a misbehavior of partition row movement (?)
| От | Arne Roland |
|---|---|
| Тема | Re: a misbehavior of partition row movement (?) |
| Дата | |
| Msg-id | 051b188a0011412bbecefc0025d5cb1b@index.de обсуждение исходный текст |
| Ответ на | Re: a misbehavior of partition row movement (?) (Amit Langote <amitlangote09@gmail.com>) |
| Список | 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 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();
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
Arne
From: Amit Langote <amitlangote09@gmail.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 (?)
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@gmail.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
> 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
В списке pgsql-hackers по дате отправления:
Следующее
От: Konstantin KnizhnikДата:
Сообщение: Inconsistent/confusing handling of tablespaces for partitioned tables