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