ERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index"

[fredrikatmainspringenergy]

ERROR:  duplicate key value violates unique constraint "pg_class_relname_nsp_index"
DETAIL:  Key (relname, relnamespace)=(devices_schema_version_id_seq, 2200) already exists.
STATEMENT:  CREATE TABLE IF NOT EXISTS "devices_schema_version" ("id" SERIAL NOT NULL PRIMARY KEY, "version" BIGINT UNIQUE, "created_at" TIMESTAMPTZ, "updated_at" TIMESTAMPTZ);

This issue was found by starting two concurrent services, both running an auto-migration as part of its startup in which they were making sure that the devices_schema_version table existed, however: Postgres has a known bug with its CREATE TABLE IF NOT EXIST implementation that may cause the above cited error.

The remedy is to acquire a lock using LOCK TABLE pg_catalog.pg_namespace IN SHARE ROW EXCLUSIVE MODE; within the same transaction according to source 1 and source 2.

Perhaps this issue should have been written in https://github.com/go-rel/postgres instead?

[Fs02]

will adding the lock statement manually before create table solve your issue?

example:

func MigrateCreateTodos(schema *rel.Schema) {
    schema.Do(func(ctx context.Context, repo rel.Repository) error {
        return repo.Exec(ctx, "LOCK TABLE pg_catalog.pg_namespace IN SHARE ROW EXCLUSIVE MODE;")
    })

    schema. CreateTableIfNotExists("devices_schema_version", func(t *rel.Table) {
        t.ID("id")
        // ...
    })
}