From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
Cc: | Daniel Migowski <dmigowski(at)ikoffice(dot)de>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Proposal: Better generation of values in GENERATED columns. |
Date: | 2019-08-26 17:46:41 |
Message-ID: | 7825.1566841601@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2019-08-26 17:48:23 | Re: Procedure support improvements |
Previous Message | Fabien COELHO | 2019-08-26 17:46:04 | Re: Creating partitions automatically at least on HASH? |