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

On Thu, Jun 8, 2017 at 9:19 AM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:
> On Wed, Jun 7, 2017 at 3:42 AM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
>> 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.
>
> The idea of transition tables is that you see all changes to the
> target of a single statement in the form of delta relations -- with
> and "old" table for any rows affected by a delete or update and a
> "new" table for any rows affected by an update or delete. If we
> have a single statement that combines INSERT and UPDATE behaviors,
> it might make sense to have an "old" table for updates and a single
> "new" table for both.

That would be cool because that's the behaviour we have.

Is there anything about that semantics that is incompatible with the
incremental matview use case? For example, are the "counting" and
"DRed" algorithms you've proposed (for non-recursive and recursive
views) driven entirely by deletions and insertions, so that updates
look like deletes followed by inserts anyway? If so, I think our
current treatment of ON CONFLICT DO UPDATE should be fine: you can't
tell whether the tuples in the new table result from insert or update
without also consulting the old table, but if the algorithm treats all
tuples in the old table as deletes (even though in this case they
result from updates) and all tuples in the new table as inserts (even
though in this case *some* of them result from updates) anyway then I
don't think it matters.

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