Re: INSERT ON CONFLICT and RETURNING

On 08.09.2020 12:34, Pavel Stehule wrote:

út 8. 9. 2020 v 11:06 odesílatel Konstantin Knizhnik <k.knizhnik@postgrespro.ru> napsal:

I have performed comparison of different ways of implementing UPSERT in Postgres.
May be it will be interesting not only for me, so I share my results:

So first of all initialization step:

  create table jsonb_schemas(id serial, schema bytea primary key);
  create unique index on jsonb_schemas(id);
  insert into jsonb_schemas (schema) values ('some') on conflict(schema) do nothing returning id;

Then I test performance of getting ID of exitsed schema:

1. Use plpgsql script to avoid unneeded database modifications:

create function upsert(obj_schema bytea) returns integer as $$
declare
  obj_id integer;
begin
  select id from jsonb_schemas where schema=obj_schema into obj_id;
  if obj_id is null then
    insert into jsonb_schemas (schema) values (obj_schema) on conflict(schema) do nothing returning id into obj_id;
    if obj_id is null then
      select id from jsonb_schemas where schema=obj_schema into obj_id;
    end if;
  end if;
  return obj_id;
end;
$$ language plpgsql;

In parallel execution the plpgsql variant can fail. The possible raise conditions are not handled.

So maybe this is the reason why this is really fast.

With this example I model real use case, where we need to map long key (json schema in this case) to short  identifier (serial column in this case).
Rows of jsonb_schemas are never updated: it is append-only dictionary.
In this assumption no race condition can happen with this PLpgSQL implementation (and other implementations of UPSERT as well).

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company