Problems with EF Migrations to Postgres database using Citus

Question

I am using EFCore migrations to alter a postgres db with the Citus extension.

I am trying the following steps with my migration:

1. Create the table
2. Call SELECT create_distributed_table etc.
3. Create a second table
4. Add a foreign key from the second table to the first
5. Call SELECT create_distributed_table etc. on the second table

This fails with the error of not being able to create a foreign key from a local table to a distributed table.

I amended my migration from step 3. above by swapping steps 4 and 5 around, i.e. create the distributed table and then create the foreign key: 3. Create a second table 4. Call SELECT create_distributed_table 5. Add a foreign key from the second table to the first

I then get the error "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."

This is the migration script for the second approach, as you can see I've just commented out the creation of the Foreign Key and added that in after creating the table as a distributed table.

I can work around this by creating two migrations and manually moving the creation of the second table to the second migration but it's not ideal especially if as part of a release we have numerous new tables it would require a migration for each one.

I haven't used PostgreSQL or Citus before so I'm hoping that I'm doing something wrong. In the migration below, I am having these problems with the learner table.

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");
    }
}

Answer 1

This fails with the error of not being able to create a foreign key from a local table to a distributed table.

Regarding this section of the code:

            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);
            });

First of call, you don't need to comment the function call that creates "fk_learner_users_user_id". However, you need to defer the creation of "fk_learner_gender_gender_id" to the later phase (probably by using migrationBuilder.AddForeignKey() construct) to get rid of the error mentioned.

"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."

This looks like a bug, I'd expect Citus to automatically switch to sequential exec already.