Horton

Thread

Commits

  1. Fix transition tables for ON CONFLICT.

  1. Transition tables vs ON CONFLICT

    Thomas Munro <thomas.munro@enterprisedb.com> — 2017-06-09T04:10:15Z

    [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@bowt.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.munro@enterprisedb.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

  2. Re: Transition tables vs ON CONFLICT

    Thomas Munro <thomas.munro@enterprisedb.com> — 2017-06-12T02:40:08Z

    On Fri, Jun 9, 2017 at 4:10 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> On Wed, Jun 7, 2017 at 10:47 AM, Peter Geoghegan <pg@bowt.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.
>
> [...]
>
> Here is a patch implementing the above.  It should be applied on top
> of transition-tuples-from-wctes-v2.patch[2].

Here's a new version of patch #3.  It's rebased on top of
transition-tuples-from-wctes-v3.patch.  I also moved a comment for
execReplication.c out of this patch into patch #2, correcting a
mistake in my pancake stacking.

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

  3. Re: Transition tables vs ON CONFLICT

    Thomas Munro <thomas.munro@enterprisedb.com> — 2017-06-12T19:19:00Z

    On Mon, Jun 12, 2017 at 2:40 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> On Fri, Jun 9, 2017 at 4:10 PM, Thomas Munro
> <thomas.munro@enterprisedb.com> wrote:
>> On Wed, Jun 7, 2017 at 10:47 AM, Peter Geoghegan <pg@bowt.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.
>>
>> [...]
>>
>> Here is a patch implementing the above.  It should be applied on top
>> of transition-tuples-from-wctes-v2.patch[2].
>
> Here's a new version of patch #3.

That accidentally removed a comment that I wanted to keep.  Here is a
better version.

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

  4. Re: Transition tables vs ON CONFLICT

    Andrew Gierth <andrew@tao11.riddles.org.uk> — 2017-06-18T23:04:04Z

    >>>>> "Thomas" == Thomas Munro <thomas.munro@enterprisedb.com> writes:

 Thomas> That accidentally removed a comment that I wanted to keep.
 Thomas> Here is a better version.

I plan to commit this soon; if anyone has any comment to make, now would
be a good time.

-- 
Andrew (irc:RhodiumToad)

  5. Re: Transition tables vs ON CONFLICT

    Thomas Munro <thomas.munro@enterprisedb.com> — 2017-06-22T05:25:15Z

    On Mon, Jun 19, 2017 at 11:04 AM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:
>>>>>> "Thomas" == Thomas Munro <thomas.munro@enterprisedb.com> writes:
>
>  Thomas> That accidentally removed a comment that I wanted to keep.
>  Thomas> Here is a better version.
>
> I plan to commit this soon; if anyone has any comment to make, now would
> be a good time.

Here's patch #3 rebased for the recent reindent.

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