On Thu, Sep 3, 2020 at 7:56 PM Geoff Winkless <pgsqlad...@geoff.dj> wrote: > > On Mon, 31 Aug 2020 at 14:53, Konstantin Knizhnik > <k.knizh...@postgrespro.ru> wrote: > > If we are doing such query: > > > > INSERT INTO jsonb_schemas (schema) VALUES (obj_schema) > > ON CONFLICT (schema) DO UPDATE schema=jsonb_schemas.schema RETURNING id > > > > > > Then as far as I understand no extra lookup is used to return ID: > > The conflict resolution checks the unique index on (schema) and > decides whether or not a conflict will exist. For DO NOTHING it > doesn't have to get the actual row from the table; however in order > for it to return the ID it would have to go and get the existing row > from the table. That's the "extra lookup", as you term it. The only > difference from doing it with RETURNING id versus WITH... COALESCE() > as you described is the simpler syntax.
As I know, conflict resolution still has to fetch heap tuples, see _bt_check_unique(). As I understand it, the issues are as follows. 1) Conflict resolution uses the dirty snapshot. It's unclear whether we can return this tuple to the user, because the query has a different snapshot. Note, that CTE query by Konstantin at thead start doesn't handle all the cases correctly, it can return no rows on conflict. We probably should do the trick similar to the EPQ mechanism for UPDATE. For instance, UPDATE ... RETURNING old.* can return the tuple, which doesn't match the query snapshot. But INSERT ON CONFLICT might have other caveats in this area, it needs careful analysis. 2) Checking unique conflicts inside the index am is already the encapsulation-breaking hack. Returning the heap tuple for index am would be even worse hack. We probably should refactor this whole area before. ------ Regards, Alexander Korotkov