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

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

In response to

Browse pgsql-hackers by date

  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?