On Thu, Jun 8, 2017 at 11:50 AM, Thomas Munro <thomas.mu...@enterprisedb.com> wrote: > On Thu, Jun 8, 2017 at 11:05 AM, Thomas Munro > <thomas.mu...@enterprisedb.com> wrote: >> 1. Keep the current behaviour. [...] >> >> 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. > > A third option would be for an AFTER INSERT OR UPDATE trigger to be > invoked twice, once for the inserts and once again for the updates. > No union required, but also surprising. > > Any other ideas?
I discussed this off-list with Andrew Gierth and we came up with a fourth way: Use separate insert and update tuplestores (as originally suggested by Peter) and use the <trigger event> (INSERT, UPDATE) to decide which one a trigger should see, as described in option 2 above, but disallow INSERT OR UPDATE triggers with transition tables so that we don't have to choose any of the surprising behaviours described above. Triggers with multiple <trigger event>s are a PostgreSQL extension, so by not allowing them with transition tables we don't reduce our compliance. If you want to be invoked twice when you run ON CONFLICT statements (like option 3 above) then you'll need to create two triggers, one for INSERT and the other for UPDATE, and each will see only the transition tuples resulting from inserts or updates respectively. The door is still open for us to allow INSERT OR UPDATE with transition tables in future releases if someone can figure out what that should do. I will draft a patch for this. -- Thomas Munro http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers