Re: Proposal: Better generation of values in GENERATED columns.

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 2019-08-26 13:28, Daniel Migowski wrote:
>> I would like to implement a fallback solution 
>> that detects such errors and automatically updates the nextvalue of the 
>> sequence when the nextvalue is already used on insert.

> ISTM that such a system would likely have many of the same problems as
> the often-proposed ignore-errors mode for COPY, namely that you can't
> catch errors and do something else other than rethrowing the error.

In principle you could probably use the same infrastructure used by
ON CONFLICT to detect the unique-key violation.  But ON CONFLICT is
mighty complicated, and not very cheap either.  I don't for one second
believe Daniel's assertion that this could be done without a
significant COPY performance hit.

I'm also dubious that the right response to a duplicate key would be
as simple as "try the next nextval() result".  I for one wouldn't be
satisfied with waiting for COPY to grind through a few thousand/million
sequential nextval values before finding one that doesn't conflict with
the existing table entries.

The actually-sound advice for loading data that might have conflicting
serial values is to do the equivalent of

    setval('sequence', max(existing keys) + 1)

before you start loading.  I wonder whether there's a way to make that
simpler.

            regards, tom lane