addAutoIncrement does not initialize sequence with PostgreSQL
Description
Environment
PostgreSQL
Activity
The Alchemist March 11, 2015 at 2:54 PM
@: My apologies, you're right. My git pull request relates to only createTable changes, not addAutoIncrement. I mistakenly thought the code was shared between the two.
Please ignore my previous comment.
Gerd Behrmann March 11, 2015 at 2:48 PM
I am not certain how the pull request relates to the bug report. The pull request touches how createTable change is implemented, correct? This bug is about the addAutoIncrement change that changes an existing field in an existing table to an auto increment field. In that case the sequence has to be initialized to a value that is bigger than any existing value for the field that is converted to an auto-increment field.
The Alchemist March 11, 2015 at 1:59 PM
I get the impression the best way is to RESTART the sequence:
http://www.postgresql.org/message-id/20060721040534.GA38749@winnie.fuhr.org
http://stackoverflow.com/questions/16362930/change-the-starting-value-of-a-serial-postgresql
Pull request at https://github.com/liquibase/liquibase/pull/376
Nathan Voxland March 6, 2015 at 6:54 PM
Duplicate with additional info in
The addAutoIncrement change, when used with PostgreSQL, creates a sequence object and alters an existing field to use the sequence as a default value.
If the table that this change is applied to contains existing data, the sequence should be initialized to at least the max of all the existing values in the existing field that is turned into an auto-increment field. This does not happen. Instead the sequence starts at 1. Thus when inserting new rows, primary key collisions may occur.
When using the same change with HSQLDB, rows created after the change is applied will have auto increment values higher than any existing values in the table.
The addAutoIncrement change should initialize the sequence using a statement like:
SELECT setval('table_field_seq', (SELECT max(field) FROM table));
One can add a postgresql specific change set using an sql change containing the above expression as a workaround.