Re: UPSERT on partition

On Wed, Jun 24, 2015 at 7:05 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> How should we treat this problem for 9.5? If we want to fix this problem
> completely, probably we would need to make constraint_exclusion work with
> even UPSERT. Which sounds difficult to do at least for 9.5. Any other idea?
> Or we should just treat it as a limitation of UPSERT and add that document?

I think that the real way to fix this is, as you say, to make it so
that it isn't necessary in general to write trigger functions like
this to make inheritance work. I can imagine the system rewriting an
INSERT ... ON CONFLICT DO UPDATE query to target the correct child
partition based on the values proposed for insertion, much like
constraint exclusion. There are some problems with that idea, like
what to do about BEFORE INSERT triggers changing those values (the
values that we use to determine the appropriate child partition).
These seem like problems that can be fixed.

What I think cannot work is making the trigger inheritance pattern
really robust with UPSERT. Sure, we can do a little bit, like expose
basic information about whether the INSERT is ON CONFLICT DO NOTHING
or ON CONFLICT DO UPDATE. But as you imply, it seems unwise to expect
the user to do the rewriting themselves, within their trigger
function. Maybe that's an argument against ever exposing even this
basic information to the user.

Thanks for testing.
--
Peter Geoghegan