From: Andres Freund <andres@anarazel.de>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Geoghegan <pg@heroku.com>
Cc: hlinnaka <hlinnaka@iki.fi>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Date: Fri, 1 May 2015 16:49:21 +0200
Message-ID: <20150501144921.GH22649@awork2.anarazel.de> (raw)
In-Reply-To: <CA+TgmoYozhG9dcwpVwQ+cpOs+5jakx6MUuXy9zVoXcDSViYqZg@mail.gmail.com>
References: <CAM3SWZTGE0z5_E8wRi9wt3qgOiMpSwtwr5uDcZDGhRZ1_pz6vg@mail.gmail.com>
<20150501135800.GC22649@awork2.anarazel.de>
<CA+TgmoYQ185bf=_Z5r0MWRbN2xLgPNMK4N-Q9aAPOC7coZ4HZA@mail.gmail.com>
<20150501141052.GE22649@awork2.anarazel.de>
<CA+TgmoZ_X1D_S84aKc-_g6+wU5ri9Q-XYRe6yAMvuwe8wkuwCg@mail.gmail.com>
<20150501142403.GG22649@awork2.anarazel.de>
<CA+TgmoYozhG9dcwpVwQ+cpOs+5jakx6MUuXy9zVoXcDSViYqZg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 2015-05-01 10:39:35 -0400, Robert Haas wrote:
> On Fri, May 1, 2015 at 10:24 AM, Andres Freund <andres@anarazel.de> wrote:
> > The BEFORE UPDATE would catch things in this case.
>
> OK. In that case, I'm a lot less sure what the right decision is. It
> seems weird for both the BEFORE INSERT and BEFORE UPDATE triggers to
> get a crack at the same tuple, so your way might be better after all.
> But on the other hand, the BEFORE INSERT trigger might have had side
> effects, so we can't just pretend it didn't happen.
Well, I think it's pretty unavoidable to fire both. On that part I think
were pretty lengthy discussions a year or so back.
> One idea is to decide that an INSERT with an ON CONFLICT UPDATE
> handler is still an INSERT. Period. So the INSERT triggers run, the
> UPDATE triggers don't, and that's it.
I think that'd be much worse.
One question, that I was wondering about earlier, that also might
influence this discussion, is what happens if you change the key we're
using for resolution during either the BEFORE trigger or the ON CONFLICT
... SET. Right now the conflict will be on the version *after* the
BEFORE INSERT, which I think think is the only reasonable option.
And if you're mad enough to change the key in the ON CONFLICT ... SET
... you'll possibly get conflicts. I was, over IM, arguing for that to
be forbidden to protect users against themselves, but Heikki said people
might e.g. want to set a column in a key to NULL in that case.
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: andres@anarazel.de, robertmhaas@gmail.com, pg@heroku.com, hlinnaka@iki.fi, jeff.janes@gmail.com, sfrost@snowman.net, dean.a.rasheed@gmail.com, bruce@momjian.us, pgsql-hackers@postgresql.org
Subject: Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
In-Reply-To: <20150501144921.GH22649@awork2.anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox