This is similar to other posts, but I have been unable to resolve and have not found a solution elsewhere, e.g. here.
The specific problem is that sometimes I get a failure on an insert to a Retool database table with an autoincrementing primary key due to the primary key already existing. Unfortunately, I have been unable to replicate the exact conditions under which this occurs. Most of the time inserts work, but occasionally I find the nextval of the autoincrement sequence to have been set to something less than the maximum id. I am not running setval commands anywhere to reset the sequence, so I am unsure why this is happening. The problem is fixed by manually resetting the sequence, but I'd rather not do this and just let the autoincrement work as expected. The table does have multiple users that access and update it, including deleting rows. Any ideas on what I may be missing?