From: | Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: INSERT ON CONFLICT and RETURNING |
Date: | 2020-09-08 10:34:43 |
Message-ID: | 5e4ccc71-7975-3a4d-6d29-b6a3108cd292@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 08.09.2020 12:34, Pavel Stehule wrote:
>
>
> út 8. 9. 2020 v 11:06 odesílatel Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexey Kondratov | 2020-09-08 10:36:16 | Re: Global snapshots |
Previous Message | Magnus Hagander | 2020-09-08 10:28:02 | Re: Inconsistency in determining the timestamp of the db statfile. |