Symfony Doctrine迁移导致PostgreSQL主键序列异常

问题场景 #

用pgloader将MariaDB数据迁移到PostgreSQL后，程序原本能正常插入Post数据。但执行以下Doctrine迁移同步命令后，插入新Post时触发唯一键冲突错误：

docker exec -ti app php bin/console doctrine:migrations:sync-metadata-storage
docker exec -ti app php bin/console make:migration
docker exec -ti app php bin/console doctrine:migrations:migrate 

错误信息 #

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "idx_16660_primary"
DETAIL: Key (id)=(1) already exists.

迁移文件中的关键SQL #

生成的迁移文件包含以下修改ID字段的语句：

$this->addSql('ALTER TABLE post ALTER id TYPE INT');
$this->addSql('ALTER TABLE post ALTER id DROP DEFAULT');
$this->addSql('ALTER TABLE post ALTER id ADD GENERATED BY DEFAULT AS IDENTITY');

实体ID声明 #

项目中Post实体的ID字段声明如下：

/**
 * @var int
 */
#[ORM\Column(name: 'id', type: 'integer', nullable: false)]
#[Id]
#[ORM\GeneratedValue(strategy: 'IDENTITY')]
private $id;

问题原因 #

PostgreSQL的IDENTITY序列默认从1开始生成值，但迁移过来的MariaDB数据已经占用了从1开始的ID。执行迁移添加IDENTITY后，新插入数据时序列生成的第一个ID是1，和现有数据冲突。

解决方法 #

方法1：手动重置序列 #

1. 先查询post表中最大的ID值：

SELECT MAX(id) FROM post;

假设查询结果是100。

2. 重置IDENTITY序列的起始值为最大ID+1：

ALTER SEQUENCE post_id_seq RESTART WITH 101;

如果不确定序列名称，可以执行\d post查看表结构，找到ID字段对应的序列名（通常格式为表名_id_seq）。

3. 保留迁移文件中的三条SQL语句，执行迁移后再运行上述序列重置命令，即可正常插入新数据，同时保证元数据与数据库同步。

方法2：在迁移文件中自动处理 #

直接在迁移文件中添加重置序列的语句，执行迁移时自动完成同步：

$this->addSql('ALTER TABLE post ALTER id TYPE INT');
$this->addSql('ALTER TABLE post ALTER id DROP DEFAULT');
$this->addSql('ALTER TABLE post ALTER id ADD GENERATED BY DEFAULT AS IDENTITY');
// 自动将序列值设置为表中最大ID
$this->addSql("SELECT setval('post_id_seq', (SELECT MAX(id) FROM post))");

内容的提问来源于stack exchange，提问作者Sam