Postgresql – postgres sequence problems, manually created pids, and proper sequence resetting

postgresqlpostgresql-9.6sequence

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.

Best Answer

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!

First, the real fix would be for the code to always use the sequence instead of the inconsistent mix of select 1+max(pk) and nextval('seqname').

That being said, as a band-aid solution before a real fix, you could have a trigger on INSERT for each row that always calls nextval on the sequence, so that you're sure that the sequence never lags behind, even when the INSERT itself is missing a nextval call.

If the column has a DEFAULT nextval('seqname') (either set manually or through a SERIAL declaration), and a trigger calls nextval in addition to the nextval of the DEFAULT, that will just advance the sequence by two instead of one. That should not be a problem for your app because sequences can have holes anyway, due to rollback or caching.

Calling setval with SELECT 1+max(pk) from table also works, but only when there aren't other transactions using the sequence concurrently. So doing that in a trigger doesn't sound like a good idea. Typically this sort of adjustement is only ever done after a bulk load (in fact, that's what pg_dump produces when the sequence is owned by the table).

Related Question