Re: PG10 transition tables, wCTEs and multiple operations on the same table

On Thu, Jun 8, 2017 at 11:50 AM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> On Thu, Jun 8, 2017 at 11:05 AM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)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