On Wed, Jun 24, 2015 at 7:05 AM, Fujii Masao <masao.fu...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers