Proposal: Better generation of values in GENERATED columns.
Hello,
one of the most frustating things when I started with PostgreSQL was
that IDENTITY columns are based on sequences that are completly
disconnected from the table contents and manually imported data will
lead to errors like 'duplicate key value violates unique constraint
"xyz_pkey"'.
I had to fight this initially with an insert trigger that always updates
the sequences on each insert, or with client side code that updates the
sequence when such an error occurs and then retries the insert.
Even Microsoft Access did a better job at autogenerated primaries keys,
and while I love the elegant design of PostgreSQL in many ways I believe
we can do better here. 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.
I believe this can be implemented without affecting performance
negatively when one just does extra stuff in the error case, so I
wouldn't do table scans when creating the insert initially.
Any reasons why this isn't a good idea to try?
Regards,
Daniel Migowski
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.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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