Thread: [HACKERS] Transition tables vs ON CONFLICT

[HACKERS] Transition tables vs ON CONFLICT

From
Thomas Munro
Date:
[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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Attachment

Re: [HACKERS] Transition tables vs ON CONFLICT

From
Thomas Munro
Date:
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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Attachment

Re: [HACKERS] Transition tables vs ON CONFLICT

From
Thomas Munro
Date:
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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Attachment

Re: [HACKERS] Transition tables vs ON CONFLICT

From
Andrew Gierth
Date:
>>>>> "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)



Re: [HACKERS] Transition tables vs ON CONFLICT

From
Thomas Munro
Date:
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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Attachment