[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 <p...@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.mu...@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
transition-tuples-from-on-conflict-v1.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
