RE: Duplicate key in UUID primary key index...
I have a table with a UUID as the primary key field. I am using a procedure to insert a parent and child record and using a UUID generated by an external system (webhook). The UUID insert is generating a "duplicate key" error on insert, but when I SELECT the uuid, nothing is returned. I've even manually scanned all the UUIDs in the table and verified the UUID I'm trying to insert does not exist in the table, and yet nothing I do will get past the duplicate key.
I've tried to vacuum the table, recreate the primary key, recreate the table, and even recreated the database and remigrated data to it. And it's not just one UUID... out of 350,000 records, there are about 20 or so that are throwing "duplicate key" errors. Any ideas what I can try next?
On 2021-10-14 03:02:08 +0000, Eric Tobias wrote:
I have a table with a UUID as the primary key field. I am using a procedure to
insert a parent and child record and using a UUID generated by an external
system (webhook). The UUID insert is generating a “duplicate key” error on
insert, but when I SELECT the uuid, nothing is returned.
Are you perhaps trying to insert the same uuid twice in the same
transaction? In this case the second insert would fail causing the
whole transaction to fail and you would never see that uuid in another
session.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
It turns out the issue was related to JSON and the UUID. I had a single row of data being inserted from the "source" side, but there were two rows contained in JSON within one of the fields. My extract pattern denormalized the JSON content and caused two records to be insert... with the same UUID. I should have thought of the JSON part!
Thank you for all the assistance, everyone.