Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1krMCc-0000QE-7N for pgsql-hackers@arkaria.postgresql.org; Mon, 21 Dec 2020 14:30:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1krMCb-0003SH-3k for pgsql-hackers@arkaria.postgresql.org; Mon, 21 Dec 2020 14:30:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1krMCa-0003S8-HJ for pgsql-hackers@lists.postgresql.org; Mon, 21 Dec 2020 14:30:20 +0000 Received: from mxout3.routing.net ([2a03:2900:1:a::8]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1krMCS-0003r3-3X for pgsql-hackers@postgresql.org; Mon, 21 Dec 2020 14:30:19 +0000 Received: from mxbox3.masterlogin.de (unknown [192.168.10.78]) by mxout3.routing.net (Postfix) with ESMTP id 422E8604EA; Mon, 21 Dec 2020 14:30:08 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mailerdienst.de; s=20200217; t=1608561008; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=CHbdeiSjwz1U/6FiHcfhfM+3Dz3gtQC+vttdv0vv3to=; b=jYM8lD+8pi7o8BRYm8qZXXTDFO/kU3UlzYZlKuOFSvydoO+sighV2t3mXrVb7K7yRkk58G wgt5DZVEcbvCpOl4R8yXsaFw9EYIpRAof9mE4CAys29vY+pWgtS8m9h2p4wf2K/FzvJpC9 yZYb+fOJC0f4ABy/iIvkTpXERv+OeBk= Received: from remote.index.de (business-90-187-114-221.pool2.vodafone-ip.de [90.187.114.221]) by mxbox3.masterlogin.de (Postfix) with ESMTPSA id 8F0813604C1; Mon, 21 Dec 2020 14:30:07 +0000 (UTC) Received: from invidia.index-gruppe.local (192.168.3.140) by acedia.index-gruppe.local (192.168.3.121) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1034.26; Mon, 21 Dec 2020 15:30:06 +0100 Received: from acedia.index-gruppe.local (192.168.3.121) by invidia.index-gruppe.local (192.168.3.140) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1034.26; Mon, 21 Dec 2020 15:30:06 +0100 Received: from acedia.index-gruppe.local ([fe80::ecd1:5d0a:a834:dd56]) by acedia.index-gruppe.local ([fe80::ecd1:5d0a:a834:dd56%5]) with mapi id 15.01.1034.033; Mon, 21 Dec 2020 15:30:06 +0100 From: Arne Roland To: Amit Langote CC: Tomas Vondra , "David G. Johnston" , PostgreSQL-development Subject: Re: a misbehavior of partition row movement (?) Thread-Topic: a misbehavior of partition row movement (?) Thread-Index: AQHWmLNk3TJTUi7wd0y+nolzgVoC06mEPugAgADLzICAAI9GgIAAAx+AgEuIyICAJct6AIAALEm6gADJA4CAAACYgIAKLgLQ Date: Mon, 21 Dec 2020 14:30:06 +0000 Message-ID: <051b188a0011412bbecefc0025d5cb1b@index.de> References: , In-Reply-To: Accept-Language: en-US, de-DE Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [192.168.3.145] Content-Type: multipart/alternative; boundary="_000_051b188a0011412bbecefc0025d5cb1bindexde_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_051b188a0011412bbecefc0025d5cb1bindexde_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Amit, thanks for the quick reply! Sadly I have been busy and the second part of y= our 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 m= eant 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 d= el_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 u= pd_trig_fkt(); update a set id=3D2; 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 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 wro= te: > 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 CASC= ADE > update a set id=3D2; > ERROR: update or delete on table "a" violates foreign key constraint > "a_fk" on table "b" > DETAIL: Key (id)=3D(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=3D2; > UPDATE 1 > > select * from b; > id > ---- > 2 > (1 row) -- Amit Langote EDB: http://www.enterprisedb.com --_000_051b188a0011412bbecefc0025d5cb1bindexde_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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:49= 7.


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 ba= rely 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);<= br> create table b (id serial,  primary key (id)) partition b= y 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 d= el_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 u= pd_trig_fkt();

update a set id=3D2;

To me the issue seems to have litte to do with the fact that the trigg= er is executed on the leaf node in itself, but rather we lack the infrastru= cture to track whether the tuple is removed or only rerouted.

Regards
Arne

From: Amit Langote <am= itlangote09@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 (?)
 
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);<= br> > 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 C= ASCADE
> update a set id=3D2;
> ERROR:  update or delete on table "a" violates foreign = key constraint
> "a_fk" on table "b"
> DETAIL:  Key (id)=3D(1) is still referenced from table "b&qu= ot;.
>
> 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);<= br> > 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=3D2;
> UPDATE 1
>
> select * from b;
>  id
> ----
>   2
> (1 row)

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



 --_000_051b188a0011412bbecefc0025d5cb1bindexde_--