Upgrade scripts expect sequences to be up-to-date

[Brandon Uhlman]

Irrespective of any versions of software, the database upgrade scripts as currently written expect sequences used to generate default key values in some fields to be in sync with the databaes value.

Some database administrators may, in day-to-day business, add values to these tables and manually specify a key value, which may eventually cause a primary key failure when the sequence is called to automatically issue a value, and it has already been manually allocated. In an ideal universe, database administrators would always let the sequence generate the IDs so this wouldn't occur.

I can't decide whether it's a bug that the scripts don't handle this sub-optimal human behaviour. If we decide that it is, I can prepare patches for any currently-maintained upgrade scripts which use sequences to generate primary keys in tables that checks nextval(the_table.primary_key_sequence) against max(the_table.primary_key), and adjusts if necessary before creating new rows - the upgrade-script equivalent of pushing currval() over max(id) when doing the marc2bre dance to import bibs.

...or we can choose to keep the current behaviour, and the upgrades will fail when DBAs misbehave. ;-)

[Dan Scott (denials)]

Brandon and I discussed this in IRC briefly and thought that it might be useful to add a general function that walks the database schema looking for tables with sequences, checks the foo.bar_id_seq.last_value versus the foo.bar.id value, and calls setval() if max(id) is ahead of last_value.

This function could then be invoked at the start of each upgrade script to circumvent DBA misbehaviour. You would certainly want to do it when the database is quiesced, in any case.

Brandon said he would try to work something up. Go Brandon!