duplicate key value violates unique constraint "pg_type_typname_nsp_index"

[dotchev]

We hit an error in our tests similar to #235

pq: duplicate key value violates unique constraint "pg_type_typname_nsp_index" in line 0: CREATE TABLE "schema_migrations" (version bigint not null primary key, dirty boolean not null)

What bothers me is that this is not ErrLocked. So it seems like the lock does not work as expected.
Or do I get it wrong?
We use Postgres and we call it like this

func updateSchema(db *sqlx.DB) error {
	driver, err := migratepg.WithInstance(db.DB, &migratepg.Config{})
	if err != nil {
		return err
	}
	m, err := migrate.NewWithDatabaseInstance("file://storage/postgres/migrations", "postgres", driver)
	if err != nil {
		return err
	}
	err = m.Up()
	if err == migrate.ErrNoChange {
		logrus.Debug("Database schema already up to date")
		err = nil
	}
	return err
}

We use migrate ver 3.2.0
We use pq driver

[dhui]

Are you able to reproduce the issue using the master branch?

There's an fix to the postgres db driver that'll be in the next release: #13

[dotchev]

We still use the old code. We will try to reproduce with the latest master.

[dhui]

FYI, the postgres db driver fix I mentioned above is now in the latest release (v3.3.0)

[dhui]

@dotchev Is this still an issue w/ the latest release?

[nareshov]

@dhui i'm facing the same issue also when using the latest code (with go get -u -d github.com/golang-migrate/migrate/cli).

when invoked as follows:

% migrate -path /go/src/github.com/XYZ/abc/platform/tools/migrations/pgsql/platform/guardian -database 'postgres://u:p@localhost:5432/platform_test?sslmode=disable' down

[dhui]

Does anyone have steps to reproduce this?

Was there an old migrate process/connection that didn't support Postgres advisory locks?
Also, there's a bugfix in the latest release the correctly closes dangling DB connections in Postgres that might help.

[tsenart]

Hello!

Here's a reproducible test command that runs into this. We're using v4.1.0.

[tsenart]

The problem seems to be that func (*Postgres) ensureVersionTable() error isn't atomic.

[tsenart]

This commit doesn't seem to fix it unfortunately: tsenart@f6caf3e

Nor wrapping it all in a transaction.

[dhui]

Fix is in master. I'll cut at new release with this fix in ~1 week.