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

Hi,

On Mon, Dec 28, 2020 at 10:01 PM Arne Roland <A.Roland@index.de> wrote:
> While I'd agree that simply deleting with "on delete cascade" on tuple rerouting is a strong enough violation of the
spiritof partitioning changing that behavior, I am surprised by the idea to make a differentiation between fks and
othertriggers. 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. Most people I know don't partition because they like to
complicatethings, but just because they have to much data. 
>
> It isn't just a thing with after triggers. Imo before triggers are even worse: If we move a row between partitions we
fireall three triggers at once (at different leaf pages obviously). It's easy to point towards the docs. Heart bleed
waswell documented, but I am happy that it was fixed. I don't want to imply this totally unrelated security issue has
anythingto do with our weird behavior. I just want to raise the question whether that's a good thing, because frankly I
haven'tmet anyone thus far, who thought it is. 

Just to be clear, are you only dissatisfied with the firing of
triggers during a row-moving UPDATEs on partitioned tables or all
firing behaviors of triggers defined on partitioned tables?  Can you
give a specific example of the odd behavior in that case?

> To me it seems the RI triggers are just a specific victim of the way we made triggers work in general.

That's true.

> What I tried to express, albeit I apparently failed: I care about having triggers, 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 still want to raise the question, whether
thisshould change, because I frankly 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
makingthat behavior possible for other triggers (possibly later) as well. One possibility would be having an entry in
thecatalog to mark when the trigger should fire. 

Sorry, I don't understand what new setting for triggers you are
thinking of here.

> I don't want to stall your definitely useful RI-Trigger fix, but I sincerely believe, that we have to do better with
triggersin general. 
>
> If we would make the condition when to fire or not dependent something besides that fact whether the trigger is
internal,we could at a later date choose to make both ways available, if anyone makes a good case for this. Even though
Istill 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
washandled? 

It was discussed here:

https://www.postgresql.org/message-id/flat/CAJ3gD9do9o2ccQ7j7%2BtSgiE1REY65XRiMb%3DyJO3u3QhyP8EEPQ%40mail.gmail.com

It's a huge discussion, so you'll have to ctrl+f "trigger" to spot
relevant emails.  You might notice that the developers who
participated in that discussion gave various opinions and what we have
today got there as a result of a majority of them voting for the
current approach.  Someone also said this during the discussion:
"Regarding the trigger issue, I can't claim to have a terribly strong
opinion on this. I think that practically anything we do here might
upset somebody, but probably any halfway-reasonable thing we choose to
do will be OK for most people." So what we've got is that
"halfway-reasonable" thing, YMMV. :)


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