cockroachdb: try lock failed in line 0: SELECT pg_advisory_lock($1) (details: ERROR: unknown function: pg_advisory_lock() (SQLSTATE 42883))

[dbist]

Describe the Bug
I'm opening this bug on behalf of another user. I've not tested this library before but given the error message I can say CockroachDB does not support pg_advisory_loc() function today. Is there a workaround given this library claims support for CockroachDB?

Steps to Reproduce
Steps to reproduce the behavior:

Can't provide step 1 and 2 as this is not my reproduction

{"level":"ERROR","@timestamp":"2022-02-14T14:55:16.377Z","caller":"migrations/migrations.go:24","message":"Database ConnectionPool Failed : try lock failed in line 0: SELECT pg_advisory_lock($1) (details: ERROR: unknown function: pg_advisory_lock() (SQLSTATE 42883)) "}
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x58 pc=0xbad7c6]

goroutine 1 [running]:
github.com/golang-migrate/migrate/v4.(*Migrate).lock(0x0, 0x0, 0x0)
	/go/pkg/mod/github.com/golang-migrate/migrate/v4@v4.14.1/migrate.go:890 +0x46
github.com/golang-migrate/migrate/v4.(*Migrate).Up(0x0, 0xc00022afa0, 0xa0)
	/go/pkg/mod/github.com/golang-migrate/migrate/v4@v4.14.1/migrate.go:268 +0x2f
REDACTED/app/db/migrations.RunMigrations(0xc0004ba318, 0xc000010090)
	/service/app/db/migrations/migrations.go:27 +0xed
main.initHTTPServer(0x7fff5d29fdc8, 0xc)
	/service/main/command.go:72 +0x6fd
main.execute(0x7fff5d29fdd5, 0xa, 0x7fff5d29fdc8, 0xc)
	/service/main/command.go:97 +0x2c2
main.main()
	/service/main/main.go:21 +0x125

Expected Behavior

Migration should succeed

Migrate Version

based on the stack trace, the version is v4@v4.14.1

Loaded Source Drivers
e.g. s3, github, go-bindata, gcs, file
Obtained by running: migrate -help

N/A

Loaded Database Drivers
e.g. spanner, stub, clickhouse, cockroachdb, crdb-postgres, postgres, postgresql, pgx, redshift, cassandra, cockroach, mysql
Obtained by running: migrate -help

N/A

Go Version
e.g. go version go1.11 linux/amd64
Obtained by running: go version

N/A

Stacktrace
Please see above

[dhui]

Are they using the cockroachdb driver? If so, it shouldn't be using advisory locks. The current implementation (and v4.14.1) use a lock table

[dbist]

thank you for your response, we confirmed the party was not using cockroachdb driver. I really appreciate your prompt response.

[filhodanuvem]

Just saying this for more junior people; yes, using the driver and switching the protocol on the connection string from postgresql://user:password@... to cockroachdb://user:password@... does work.