从MySQL迁移PostgreSQL后Laravel单元测试唯一键冲突解决咨询

问题场景 #

我们正从MySQL迁移至PostgreSQL，Laravel PHPUnit测试的所有父类都使用了RefreshDatabase trait，测试数据会在setUp()、种子函数或单个测试中通过seeders、工厂或模型插入。运行测试时出现以下错误：

Illuminate\Database\QueryException 

SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "business_types_pkey"
DETAIL:  Key (id)=(1001) already exists. (SQL: insert into "business_types" ("tenant_id", "name", "slug", "updated_at", "created_at") values (1001, Business Type with Empty Slug, , 2024-05-16 13:20:47, 2024-05-16 13:20:47) returning "id")

相关代码 #

BusinessTypeSeeder代码 #

<?php

namespace Database\Seeders;

use App\Enums\BusinessTypeSlugEnum;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use App\Tenant;

class BusinessTypeSeeder extends Seeder
{

    public const B2C_ID = 1001;
    public const ECOMM_ID = 1002;
    public const B2B_ID = 1003;

    public const BUSINESS_TYPES = [
        self::B2C_ID => [
            'name' => 'B2C',
            'slug' => BusinessTypeSlugEnum::B2C->value
        ],
        self::ECOMM_ID => [
            'name' => 'eComm',
            'slug' => BusinessTypeSlugEnum::Ecomm->value
        ],
        self::B2B_ID => [
            'name' => 'B2B',
            'slug' => BusinessTypeSlugEnum::B2B->value
        ]
    ];

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        foreach (self::BUSINESS_TYPES as $id => $columns) {
            DB::table('business_types')->insert([
                'id' => $id,
                'name' => $columns['name'],
                'slug' => $columns['slug'],
                'tenant_id' => Tenant::firstOrFail()->id
            ]);
        }
    }
}

失败的测试函数 #

public function testResolveWhenBusinessTypeHasEmptySlugDiscoveryShouldNotHaveAnyServices(): void
{
    $this->set_auth();

    // Set the client's business type to one with an empty slug.
    $business_type = BusinessType::create([
        'tenant_id' => Tenant::firstOrFail()->id,
        'name' => 'Business Type with Empty Slug',
        'slug' => ''
    ]);
    $client = Client::findOrFail(SingleClientSeeder::CLIENT_ID);
    $client->business_type_id = $business_type->id;
    $client->save();

    $args = [
        'client_name' => SingleClientSeeder::NAME,
        'tier_id' => TiersSeeder::TIER_ID,
        'create_discovery' => 'yes'
    ];
    $audit = $this->resolve($args);
    $discovery = $audit->discovery;

    // Assert that a Discovery was created with no Departments/Services.
    $this->assertExactlyOneNotSoftDeletedModelInTable($discovery);
    $this::assertEmpty($discovery->departments);
    $this::assertEmpty($discovery->services);
    $this->assertDatabaseCount('discovery_department', 0);
    $this->assertDatabaseCount('discovery_service', 0);
}

问题原因 #

这个问题和PostgreSQL的序列机制直接相关，同时和Laravel的RefreshDatabase trait有关：

• MySQL的自增字段在手动插入硬编码ID后，会自动把自增计数器更新到最大值+1；但PostgreSQL的序列不会自动同步——手动插入ID后，序列的当前值还是初始状态，当用Model::create()创建新记录时，序列会生成已经被占用的ID（比如1001），触发唯一键冲突。
• RefreshDatabase trait在测试间只会回滚事务或重置数据库数据，但不会重置序列的当前值，导致问题持续出现。

解决方案 #

方案1：在Seeder中手动更新序列（无需修改测试） #

在BusinessTypeSeeder的run()方法末尾添加代码，手动更新business_types表的ID序列，让序列从当前最大ID+1开始：

public function run()
{
    foreach (self::BUSINESS_TYPES as $id => $columns) {
        DB::table('business_types')->insert([
            'id' => $id,
            'name' => $columns['name'],
            'slug' => $columns['slug'],
            'tenant_id' => Tenant::firstOrFail()->id
        ]);
    }

    // 更新PostgreSQL序列，避免ID冲突
    $maxId = DB::table('business_types')->max('id');
    DB::statement("ALTER SEQUENCE business_types_id_seq RESTART WITH " . ($maxId + 1));
}

如果有其他硬编码ID的Seeder，都可以添加这段逻辑，或者封装成全局方法复用。

方案2：修改测试基类，自动重置序列 #

在所有测试继承的父类中重写setUp()方法，每次测试前重置相关表的序列：

protected function setUp(): void
{
    parent::setUp();

    // 列出所有有硬编码ID的表
    $tables = ['business_types', /* 补充其他表名 */];
    foreach ($tables as $table) {
        $maxId = DB::table($table)->max('id');
        if ($maxId) {
            DB::statement("ALTER SEQUENCE {$table}_id_seq RESTART WITH " . ($maxId + 1));
        }
    }
}

这个方案只需修改一次测试基类，无需逐个调整Seeder。

方案3：使用insertOrIgnore替代insert（可选） #

如果允许跳过重复插入，可以把Seeder中的insert改成insertOrIgnore，但这只是临时规避问题，无法从根本上解决序列不同步的问题，不推荐作为长期方案。

总结 #

最简便的解决方式是方案1或方案2，无需修改数百个依赖硬编码ID的测试，只需在Seeder或测试基类中添加少量代码，即可解决PostgreSQL序列与手动插入ID不同步的问题。

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