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

On Wed, Jun 7, 2017 at 7:27 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> On Wed, Jun 7, 2017 at 10:47 AM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>> I suppose you'll need two tuplestores for the ON CONFLICT DO UPDATE
>> case -- one for updated tuples, and the other for inserted tuples.
>
> Hmm. Right. INSERT ... ON CONFLICT DO UPDATE causes both AFTER
> INSERT and AFTER UPDATE statement-level triggers to be fired, but then
> both kinds see all the tuples -- those that were inserted and those
> that were updated. That's not right.

Or maybe it is right. We're out of spec here, so we'd basically have
to decide (though MERGE's behaviour would be interesting to compare
with). What we have seems reasonable for an AFTER INSERT OR UPDATE
statement-level trigger, I think. It's just a bit questionable if you
asked for just one of those things. The question is whether the
trigger's 'when' clause is supposed to refer to the type of statement
you ran or the way individual rows turned out to be processed in our
out-of-standard ON CONFLICT case. If you take the former view then we
could simply decree that such a statement is both an INSERT and an
UPDATE for trigger selection purposes.

--
Thomas Munro
http://www.enterprisedb.com