Transition tables vs ON CONFLICT

[Moving this to its own thread, for earlier discussion see the
transition-tables-vs-wCTE thread[1].]

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.

On Fri, Jun 9, 2017 at 11:28 AM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> 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.

Here is a patch implementing the above. It should be applied on top
of transition-tuples-from-wctes-v2.patch[2].

This is patch 3 of a stack of 3 patches addressing currently known
problems with transition tables.

[1] https://www.postgresql.org/message-id/CAEepm%3D3HZY%2B2Vr5P3pvVYfKLrwhPWT6vGLtBOeCh6K5Cwb8L7w%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAEepm%3D2ZQ%2BmujsvWXhOqaNxpc2-0hDev6q7a%2BXrbOn2%3Dcr7%3D0A%40mail.gmail.com

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