Developers encountered issues with sequences in our postgres 9.6 database over the past year, where they stop working, so they have resorted to things like inserting rows with a pid manually created based on the last inserted record's pid, and resetting the sequence using this code:
SELECT pg_catalog.setval(pg_get_serial_sequence('mytable', 'pid'), (SELECT MAX(pid)+1 FROM mytable) );
I am experienced enough to know that we should be able to rely on the database to create our ids, and that creating one's own PIDs based on last max is not a 'best practice' nor safe for all scenarios, though it generally has worked for our daily use.
The reason they stopped working the first time is the well-known cause that a table was restored for example. However, at this point I think the manual code and sequences are stepping on each other, and the sequence resetting code is not solid. It seems obvious that inserting the last max will undermine the sequence, which simply has its own number it increments.
While I am reading up, I would like to know if someone point me in the right direction today to get the sequence back up, and working flawlessly, on an existing table - and have it work even though code is in place that will sometimes insert a pid from code, based on last max. (Longer term, of course, I am fully aware it is best all such code will is dropped - but is there a way to work around that code for now?)
Included in a solution would be a way in postgreSQL 9.6 to have it reset the sequence itself if there is a conflict - not sure if possible, and am bracing for lectures from the more experienced out there - but that's why I am here!
Finally - and this is one disturbing fact that got me here - after resetting the sequence in pg admin I see two pids in the table in pg admin 3 and 4, which also shows in the create script. The 'second' PID column does not show when doing \d in psql, which is good - but I thought it might be relevant.
UPDATE - on that last point, the ghost duplicate PIDs for a table in pg admin were caused because there were two sequences for that same column/table combo, the second created at some point to try and fix broken sequences over time (eg mytable_pid_seq and mytable_pid_seq1). Not sure why/how this was allowed to happen in the database.