Thread: [HACKERS] 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@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
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
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
>>>>> "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)
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