From: Arne Roland <A.Roland@index.de>
To: Amit Langote <amitlangote09@gmail.com>
CC: Tomas Vondra <tomas.vondra@2ndquadrant.com>, "David G. Johnston"
	<david.g.johnston@gmail.com>, PostgreSQL-development
	<pgsql-hackers@postgresql.org>
Subject: Re: a misbehavior of partition row movement (?)
Thread-Topic: a misbehavior of partition row movement (?)
Thread-Index: 
 AQHWmLNk3TJTUi7wd0y+nolzgVoC06mEPugAgADLzICAAI9GgIAAAx+AgEuIyICAJct6AIAALEm6gADJA4CAAACYgIAKLgLQgAENYgCAAAtkAIAAfNeR
Date: Mon, 28 Dec 2020 13:01:50 +0000
Message-ID: <dde1b71053e048e9acccc31c69f04050@index.de>
References: 
 <CA+HiwqFvkBCmfwkQX_yBqv2Wz8ugUGiBDxum8=WvVbfU1TXaNg@mail.gmail.com>,<CA+HiwqHoSSUzZopd5W29VBBu3EVsb3OfXikeS3K9-qh63kBbyA@mail.gmail.com>
In-Reply-To: 
 <CA+HiwqHoSSUzZopd5W29VBBu3EVsb3OfXikeS3K9-qh63kBbyA@mail.gmail.com>
Accept-Language: en-US, de-DE
Content-Language: en-US
Content-Type: multipart/alternative;
	boundary="_000_dde1b71053e048e9acccc31c69f04050indexde_"
MIME-Version: 1.0
Precedence: bulk

--_000_dde1b71053e048e9acccc31c69f04050indexde_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

While I'd agree that simply deleting with "on delete cascade" on tuple rero=
uting is a strong enough violation of the spirit of partitioning changing t=
hat behavior, I am surprised by the idea to make a differentiation between =
fks and other triggers. The way user defined triggers work, is a violation =
to the same degree I get complaints about that on a monthly (if not weekly)=
 basis. It's easy to point towards the docs, but the docs offer no solution=
 to archive the behavior, that makes partitioning somewhat transparent. Mos=
t people I know don't partition because they like to complicate things, but=
 just because they have to much data.

It isn't just a thing with after triggers. Imo before triggers are even wor=
se: If we move a row between partitions we fire all three triggers at once =
(at different leaf pages obviously). It's easy to point towards the docs. H=
eart bleed was well documented, but I am happy that it was fixed. I don't w=
ant to imply this totally unrelated security issue has anything to do with =
our weird behavior. I just want to raise the question whether that's a good=
 thing, because frankly I haven't met anyone thus far, who thought it is.

To me it seems the RI triggers are just a specific victim of the way we mad=
e triggers work in general.

What I tried to express, albeit I apparently failed: I care about having tr=
iggers, which make partitioning transparent, on the long run.

> because what can happen
> today with CASCADE triggers does not seem like a useful behavior by
> any measure.

I wholeheartedly agree. I just want to point out, that you could state the =
same about triggers in general.

Backwards compatibility is usually a pretty compelling argument. I would st=
ill want to raise the question, whether this should change, because I frank=
ly think this is a bad idea.

You might ask: Why am I raising this question in this thread?

In case we can not (instantly) agree on the fact that this behavior should =
change, I'd still prefer to think about making that behavior possible for o=
ther triggers (possibly later) as well. One possibility would be having an =
entry in the catalog to mark when the trigger should fire.

I don't want to stall your definitely useful RI-Trigger fix, but I sincerel=
y believe, that we have to do better with triggers in general.

If we would make the condition when to fire or not dependent something besi=
des that fact whether the trigger is internal, we could at a later date cho=
ose to make both ways available, if anyone makes a good case for this. Even=
 though I still think it's not worth it.

>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.

I tried to look that up, but I couldn't find any discussion about this. Do =
you have any ideas in which thread that was handled?

> Sorry, it seems you are right and the 2nd patch indeed fails to apply to =
master.

Thank you! I hope to have a more in depth look later this week.

Regards
Arne


--_000_dde1b71053e048e9acccc31c69f04050indexde_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"><!-- P {margin-top:0;margi=
n-bottom:0;} --></style>
</head>
<body dir=3D"ltr">
<div id=3D"divtagdefaultwrapper" style=3D"font-size:12pt;color:#000000;font=
-family:Calibri,Arial,Helvetica,sans-serif;" dir=3D"ltr">
<div>While I'd agree that simply deleting with &quot;on delete cascade&quot=
; on tuple rerouting is a strong enough violation of the spirit of partitio=
ning changing that behavior, I am surprised by the idea to make a different=
iation between fks and other triggers. The
 way user defined triggers work, is a violation to the same degree I get co=
mplaints about that on a monthly (if not weekly) basis. It's easy to point =
towards the docs, but the docs offer no solution to archive the behavior, t=
hat makes partitioning somewhat
 transparent. Most people I know don't partition because they like to compl=
icate things, but just because they have to much data.<br>
<br>
It isn't just a thing with after triggers. Imo before triggers are even wor=
se: If we move a row between partitions we fire all three triggers at once =
(at different leaf pages obviously). It's easy to point towards the docs. H=
eart bleed was well documented,
 but I am happy that it was fixed. I don't want to imply this totally unrel=
ated security issue has anything to do with our weird behavior. I just want=
 to raise the question whether that's a good thing, because frankly I haven=
't met anyone thus far, who thought
 it is.<br>
<br>
To me it seems the RI triggers are just a specific victim of the way we mad=
e triggers work in general.<br>
<br>
What I tried to express, albeit I apparently failed: I care about having tr=
iggers, which make partitioning transparent, on the long run.<br>
<br>
&gt; because what can happen<br>
&gt; today with CASCADE triggers does not seem like a useful behavior by<br=
>
&gt; any measure.<br>
<br>
I wholeheartedly agree. I just want to point out, that you could state the =
same about triggers in general.<br>
<br>
Backwards compatibility is usually a pretty compelling argument. I would st=
ill want to raise the question, whether this should change, because I frank=
ly think this is a bad idea.<br>
<br>
You might ask: Why am I raising this question in this thread?<br>
<br>
In case we can not (instantly) agree on the fact that this behavior should =
change, I'd still prefer to think about making that behavior possible for o=
ther triggers (possibly later) as well. One possibility would be having an =
entry in the catalog to mark when
 the trigger should fire.<br>
<br>
I don't want to stall your definitely useful RI-Trigger fix, but I sincerel=
y believe, that we have to do better with triggers in general.<br>
<br>
If we would make the condition when to fire or not dependent something besi=
des that fact whether the trigger is internal, we could at a later date cho=
ose to make both ways available, if anyone makes a good case for this. Even=
 though I still think it's not worth
 it.</div>
<div><br>
&gt;I don't quite recall if the decision to implement it like this was<br>
&gt; based on assuming that this is what users would like to see happen in<=
br>
&gt; this case or the perceived difficulty of implementing it the other way=
<br>
&gt; around, that is, of firing AFTER UPDATE triggers in this case.<br>
<br>
I tried to look that up, but I couldn't find any discussion about this. Do =
you have any ideas in which thread that was handled?<br>
<br>
&gt; Sorry, it seems you are right and the 2nd patch indeed fails to apply =
to master.<br>
<br>
Thank you! I hope to have a more in depth look later this week.<br>
<br>
Regards<br>
Arne<br>
<br>
</div>
<br>
</div>
</body>
</html>

--_000_dde1b71053e048e9acccc31c69f04050indexde_--