Migration 104 failing due to attribute_pk_seq out of step with table

[cailyoung]

error: migration failed: duplicate key value violates unique constraint "attribute_pk", Key (id)=(17) already exists. in line 0: WITH inserted_attribute AS (
    INSERT INTO attribute (name)
    VALUES ('analyzer.largestRetryPriority')
    RETURNING id
)
INSERT INTO project_attribute
SELECT inserted_attribute.id, FALSE, project.id
FROM project, inserted_attribute; (details: pq: duplicate key value violates unique constraint "attribute_pk")

Looking at the migration history it's possible we have up-and-down migrated this preprod environment in ways that could lead to the sequence getting out of whack with the actual contents of the attribute table.

Without a way to reset the sequence properly we can't upgrade.

[matt-richardson]

It might be related to 9e8e197#diff-82dc9dd2a3a10dcf5ab377ecdfe8ef9cceb6fbecd7c7ed46fd225db17f1bd0d5R15, back in September 19.

[Pink-Bumblebee]

This info is related to the 25.8.21 release.

[cailyoung]

@Pink-Bumblebee Matt works with us :)

The linked migration resets the sequence every time it runs; MAX(id) in that attribute table is 19, but INSERT INTO is getting 17 for the next sequence value. I don't have our full migration history to hand, but it's likely it's gone forward and backwards a fair bit as it's the preproduction environment.

In production the same table has 34 as an id so I have no way to know if a production deployment will work.

[Pink-Bumblebee]

Matt works with us :)

I know it :)
But for other users the version should be highlighted, as I think.

[pbortnik]

Hi @cailyoung ,
Yes, this issue may be caused by different flows in ID selection within previous migration scripts. Additionally, it can become corrupted if migration scripts are repeatedly run (up and down) multiple times.

To resolve this, you can directly update the current state of the sequence using the following query before running the migration:

SELECT setval('attribute_id_seq', (select max(id) from attribute), true);

[cailyoung]

@pbortnik that worked for us.