On Thu, Sep 3, 2020 at 7:56 PM Geoff Winkless <pgsqladmin@geoff.dj> wrote:
>
> On Mon, 31 Aug 2020 at 14:53, Konstantin Knizhnik
> <k.knizhnik@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