On Thu, Jun 8, 2017 at 10:21 AM, Kevin Grittner <kgri...@gmail.com> wrote: > On Wed, Jun 7, 2017 at 5:00 PM, Peter Geoghegan <p...@bowt.ie> wrote: > >> My assumption about how transition tables ought to behave here is >> based on the simple fact that we already fire both AFTER >> statement-level triggers, plus my sense of aesthetics, or bias. I >> admit that I might be missing the point, but if I am it would be >> useful to know how. > > Well, either will work. My inclination is that a single statement > should cause one execution of the FOR EACH STATEMENT trigger, but if > a good case can be made that we should have a FOR EACH STATEMENT > trigger fire for each clause within a statement -- well, it won't be > a problem for matview maintenance. The biggest hurt there would be > to *my* sense of aesthetics. ;-)
Ok, I think we have two options here: 1. Keep the current behaviour. ON CONFLICT statements cause inserted and updated tuples to be mixed together in the same 'new table' tuplestore. Trigger authors will need to be aware that AFTER INSERT triggers might see some rows that were actually updated, and vice versa, which seems moderately surprising. If you defined one AFTER INSERT trigger and one AFTER UPDATE trigger, you'll see the same rows in both if someone runs such a statement (as I showed upthread), which also seems to violate the PoLA. 2. Make a code change that would split the 'new table' tuplestore in two: an insert tuplestore and an update tuplestore (for new images; old images could remain in the old tuplestore that is also used for deletes) as Peter suggests. That raises two questions for me: (1) Does it really make sense for the 'when' clause, which sounds like it only controls when we fire a trigger, also to affect which transition tuples it sees? There is something slightly fishy about that. (2) Assuming yes, should an AFTER INSERT OR UPDATE trigger see the union of the the two tuplestores? Trigger authors would need to be aware a single statement can produce a mixture of updates and inserts, but only if they explicitly invite such problems into their lives with that incantation. I think the code change for 2 is not enormous. Split the tuplestore in two, route tuples appropriately, and teach NamedTuplestoreScan to scan both if a union is needed. It does sound like the less user-hostile of the options. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
