使用Citus时EF Core迁移PostgreSQL数据库的问题

问题背景 #

我在用EF Core迁移操作带Citus扩展的PostgreSQL数据库，先后尝试两种迁移步骤都遇到错误：

第一次尝试步骤及错误 #

1. 创建表
2. 调用SELECT create_distributed_table等语句
3. 创建第二个表
4. 添加第二个表到第一个表的外键
5. 对第二个表调用SELECT create_distributed_table等语句

报错：无法从本地表向分布式表创建外键

第二次调整步骤及错误 #

将外键创建和转为分布式表的步骤互换：

1. 创建第二个表
2. 调用SELECT create_distributed_table
3. 添加外键

报错：

When there is a foreign key to a reference table or to a local table, Citus needs to perform all operations over a single connection per node to ensure consistency.

我注释掉了表创建时的外键，转为分布式表后再添加，但这种方式如果拆分多个迁移会很麻烦（大量新表时每个表都要单独迁移），之前没接触过PostgreSQL和Citus，希望找到正确操作方式，问题出在learner表上，迁移脚本如下：

public partial class Initial : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "client",
            columns: table => new
            {
                id = table.Column<Guid>(type: "uuid", nullable: false),
                name = table.Column<string>(type: "text", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("pk_client", x => x.id);
            });
        migrationBuilder.Sql("SELECT create_distributed_table('client', 'id');");

        migrationBuilder.CreateTable(
            name: "gender",
            columns: table => new
            {
                id = table.Column<long>(type: "bigint", nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                description = table.Column<string>(type: "text", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("pk_gender", x => x.id);
            });
        migrationBuilder.Sql("SELECT create_reference_table('gender');");
        
        migrationBuilder.CreateTable(
            name: "user",
            columns: table => new
            {
                id = table.Column<Guid>(type: "uuid", nullable: false),
                client_id = table.Column<Guid>(type: "uuid", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("pk_user", x => new { x.id, x.client_id });
                table.ForeignKey(
                    name: "fk_user_client_client_id",
                    column: x => x.client_id,
                    principalTable: "client",
                    principalColumn: "id",
                    onDelete: ReferentialAction.Cascade);
            });
        migrationBuilder.Sql("SELECT create_distributed_table('user', 'client_id', colocate_with => 'client');");
        
        migrationBuilder.CreateTable(
            name: "learner",
            columns: table => new
            {
                id = table.Column<Guid>(type: "uuid", nullable: false),
                client_id = table.Column<Guid>(type: "uuid", nullable: false),
                user_id = table.Column<Guid>(type: "uuid", nullable: false),
                gender_id = table.Column<long>(type: "bigint", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("pk_learner", x => new { x.id, x.client_id });
                table.ForeignKey(
                    name: "fk_learner_gender_gender_id",
                    column: x => x.gender_id,
                    principalTable: "gender",
                    principalColumn: "id");
                // table.ForeignKey(
                //     name: "fk_learner_users_user_id",
                //     columns: x => new { x.user_id, x.client_id },
                //     principalTable: "user",
                //     principalColumns: new[] { "id", "client_id" },
                //     onDelete: ReferentialAction.Cascade);
            });
        migrationBuilder.Sql("SELECT create_distributed_table('learner', 'client_id', colocate_with => 'client');");
        migrationBuilder.AddForeignKey(
            "fk_learner_users_user_id",
            "learner",
            new string[] {"user_id", "client_id"},
            "user",
            null,
            null,
            new string[] {"id", "client_id"}
        );
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "learner");

        migrationBuilder.DropTable(
            name: "gender");

        migrationBuilder.DropTable(
            name: "user");

        migrationBuilder.DropTable(
            name: "client");
    }
}

解决方案 #

核心原因 #

Citus对分布式表的外键有两个关键约束：

1. 本地表不能引用分布式表，必须先将引用方转为分布式表，再创建外键
2. 当外键指向引用表（如gender）或本地表时，Citus要求所有相关操作必须在单节点的同一个数据库连接内完成，而EF Core默认会把CreateTable、AddForeignKey等操作拆分成独立命令，可能跨连接执行，触发一致性错误。

具体修复方法 #

把learner表的创建、转为分布式表、添加外键的操作合并成一个原生SQL脚本，通过一次migrationBuilder.Sql调用执行，确保所有操作在同一个连接内完成：

替换原来learner相关的所有代码（从CreateTable到AddForeignKey）为：

migrationBuilder.Sql(@"
CREATE TABLE learner (
    id uuid NOT NULL,
    client_id uuid NOT NULL,
    user_id uuid NOT NULL,
    gender_id bigint,
    CONSTRAINT pk_learner PRIMARY KEY (id, client_id),
    CONSTRAINT fk_learner_gender_gender_id FOREIGN KEY (gender_id) REFERENCES gender(id)
);

SELECT create_distributed_table('learner', 'client_id', colocate_with => 'client');

ALTER TABLE learner ADD CONSTRAINT fk_learner_users_user_id 
    FOREIGN KEY (user_id, client_id) REFERENCES ""user""(id, client_id) 
    ON DELETE CASCADE;
");

注意：PostgreSQL中user是保留关键字，引用时必须用双引号包裹。

额外注意事项 #

• 确保所有关联的分布式表（client、user、learner）都设置了colocate_with => 'client'，Citus要求外键关联的分布式表必须处于同一分区组（colocated），否则无法创建外键。
• 对于其他类似的表，都可以采用这种合并操作到单个SQL脚本的方式，避免拆分多个迁移。

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