一部のシーケンスが巻戻り、プライマリキーが重複してユニークキー制約違反となった。
数ヶ月前にフォーラムにて報告されていたらしく、現在鋭意修正中、だとか。
現在および将来的な問題を回避するためには setval 関数を使って is_called を false にすればよい、とか。
To avoid the issue, please follow these steps:
After migrating to Aurora PostgreSQL, before you begin using the database, for each sequence in your database follow these steps:
Get the current state of the sequence by executing “\d ”. For example:
...
Note the current last_value. To ensure the sequence doesn't regress in the future, we want to clear the is_called flag by executing “select setval(, , false)”. For example:
select setval('id_seq', 115149, false);
...
Notice the last value hasn't changed, but is_called is false and log_cnt is 0. On the next call to nextval() with these changes, Aurora PostgreSQL will write a fresh log record that guarantees the sequence number will not regress.If you have already run a workload on your migrated snapshot and have encountered duplicate sequence numbers, you can repair the sequence using similar steps. Run a SELECT query on the table using the sequence to find the maximum value used, say maxval. Follow the steps above using maxval+1 as last_value. This will both set the next sequence value to something unique and clear the is_called flag to ensure the change is logged.
また、RDS/Aurora の修正がリリースされても、既存インスタンスについては作業が必要だとか。
Planning to run a sequence fix script tomorrow setting the maxval + 1 and the is_called flag to false on every sequence that I have.
Is that correct ?
Yes, that is the correct remedy for instances that have already encountered this problem.
Note, when we deploy a fix, the change will only affect new migrations from RDS Postgres to Aurora PostgreSQL-compatible Edition. Any migration that occurs before the fix is in place will still require the specified steps to correct the issue, even if the problem is not detected until after the fix is in place.
We are working hard to test and deploy the fix. I'll update this thread when it is available.