Re: BUG #13846: INSERT ON CONFLICT consumes sequencers onconflicts

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 ‘organizational’ data that may be hierarchical in nature and fairly static, in relation to the other ‘operational’ kind of data, which is usually generated from events; those events being organized by the former for batching and reporting purposes, etc. It is often the case UPSERTS are most useful 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 unreasonable over a relatively short period of time (within the overall lifetime of a particular system) to process billions of events, meaning SERIAL typed columns used as surrogate keys to the natural keys of organizational data could exhaust their sequencers for no good reason, and SMALLSERIAL just wouldn’t be usable, BIGSERIAL could work, but they’re, uhm.. BIG.

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

Regards,
Paul

From: Andres Freund
Sent: Tuesday, January 5, 2016 9:13 AM
To: paul(at)salesintel(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #13846: INSERT ON CONFLICT consumes sequencers onconflicts

On 2016-01-05 15:02:27 +0000, paul(at)salesintel(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 13846
> Logged by: Paul Hester
> Email address: paul(at)salesintel(dot)com
> PostgreSQL version: 9.5rc1
> Operating system: Windows 10 Pro
> Description:
>
> 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 quickly
> and artificially consume all values of the sequencer; it behaves as if
> conflict detection happens after inserts are attempted, rather than before.

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

Andres Freund