Re: BUG #13846: INSERT ON CONFLICT consumes sequencers onconflicts - Mailing list pgsql-bugs

From Paul
Subject Re: BUG #13846: INSERT ON CONFLICT consumes sequencers onconflicts
Date
Msg-id 568bfcfc.44e3ca0a.c02a8.ffffbdec@mx.google.com
Whole thread Raw
In response to Re: BUG #13846: INSERT ON CONFLICT consumes sequencers on conflicts  (Andres Freund <andres@anarazel.de>)
List pgsql-bugs
If none of the columns with defaults are used to detect conflicts, why must=
 they be evaluated before conflict detection in order to reliably detect a =
conflict?

Lots of systems categorize information into =E2=80=98organizational=E2=80=
=99 data that may be hierarchical in nature and fairly static, in relation =
to the other =E2=80=98operational=E2=80=99 kind of data, which is usually g=
enerated from events; those events being organized by the former for batchi=
ng and reporting purposes, etc. It is often the case UPSERTS are most usefu=
l when deriving the organizational data from the operational data, meaning =
a very typical usage of an UPSERT would have many more updates than inserts=
, in direct correlation to the number of events being processed. Its not un=
reasonable over a relatively short period of time (within the overall lifet=
ime of a particular system) to process billions of events, meaning SERIAL t=
yped columns used as surrogate keys to the natural keys of organizational d=
ata could exhaust their sequencers for no good reason, and SMALLSERIAL just=
 wouldn=E2=80=99t be usable, BIGSERIAL could work, but they=E2=80=99re, uhm=
.. BIG.

There should be a way to not evaluate defaulted columns not used in the con=
flict_target, so that SERIAL typed columns not used for conflict detection =
don=E2=80=99t unnecessary exhaust their sequencers=E2=80=A6. (I think that =
was like a quadruple-negative sentence there=E2=80=A6, sorry about that)

Regards,
Paul

From: Andres Freund
Sent: Tuesday, January 5, 2016 9:13 AM
To: paul@salesintel.com
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #13846: INSERT ON CONFLICT consumes sequencers onco=
nflicts

On 2016-01-05 15:02:27 +0000, paul@salesintel.com wrote:
> The following bug has been logged on the website:
>=20
> Bug reference:      13846
> Logged by:          Paul Hester
> Email address:      paul@salesintel.com
> PostgreSQL version: 9.5rc1
> Operating system:   Windows 10 Pro
> Description:       =20
>=20
> When using an INSERT statement with an ON CONFLICT clause, if there is a
> conflict, and the table being inserted into has a column defaulted to
> nextval('seq'), the sequencer 'seq' is always incremented. This can quick=
ly
> and artificially consume all values of the sequencer; it behaves as if
> conflict detection happens after inserts are attempted, rather than befor=
e.

Yes. That's by design. You can't reliably do conflict detection before
evaluating column default values.

Andres Freund

pgsql-bugs by date:

Previous
From: Kevin Grittner
Date:
Subject: Re: BUG #13847: WARNING: skipping "pg_toast_" --- cannot vacuum indexes, views, or special system tables VACUUM
Next
From: Philippe BEAUDOIN
Date:
Subject: Re: BUG #6682: pg_dump and sequence values of serial columns for extension configuration tables