Hi, On Mon, Dec 28, 2020 at 10:01 PM Arne Roland <a.rol...@index.de> wrote: > While I'd agree that simply deleting with "on delete cascade" on tuple > rerouting is a strong enough violation of the spirit of partitioning changing > that 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. Most > 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 > worse: 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. > Heart bleed was well documented, but I am happy that it was fixed. I don't > want 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.
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 this should 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 making that behavior possible for > other triggers (possibly later) as well. One possibility would be having an > entry in the catalog 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 triggers in 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 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? 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
