no schema has been selected to create in (SQLSTATE 3F000) after upgrading from 3.18.0 to 3.19.2

[kevintsou17]

I'm wondering if anyone has faced a similar issue after upgrading from 3.18.0 to 3.19.2. After the upgrade, we encountered the "No schema has been selected to create in (SQLSTATE 3F000)" issue.

[mfridman]

Is it possible you've also updated your Postgres instance to version 15 or higher?

This error typically has to do with the search path, either:

1. it's empty (which is what pg_dump by default, SELECT pg_catalog.set_config('search_path', '', false);)
2. it's invalid
3. insufficient roles for whatever schema you have

The reason I ask about Postgres 15 or higher, is because there was changes specifically related to this. Check out the release notes and search for "public schema".

[mfridman]

That is very peculiar. The major changes between those 2 releases:

v3.18.0...v3.19.2

• a fix to remapping redshift / postgres to a driver
• making goose annotations case-insensitive
• updating all the dependencies

Do you use goose as a library (importing in your project) or as a CLI (either go install or downloading released binaries)?

[kevintsou17]

sorry for the late response.
We use goose as library and run it in the code.

[kevintsou17]

Just following up on this.
Thanks.

[mfridman]

Hey there, I would love to help you, but don't immediately see what the problem could be. I'll audit the changes to see if there's a good explanation for this.

If possible, a reproducible example, or even just the failing migration, or any other kind of details, would help debug this .

[kevintsou17]

I will try to investigate further, and if there's still no resolution, I will post a reproducible example. Thank you.

[hbibel]

I encountered the exact same error message and tried specifying the schema name in the migration files, e.g. CREATE TABLE my_schema.users, but to no avail. Then I noticed that the error actually was caused when goose tried to create the goose_db_version table. In my case the issue was that the schema didn't exist yet (forgot to create the schema when spinning up a test container).

Remember to always check your database logs.

[mfridman]

What command(s) were you running? For most operations, goose creates (and always checks) the goose_db_version table exists, so I'm definitely pretty surprised you're getting into this state.

[hbibel]

Here's a minimal example:

// main.go
package main

import (
        "context"
        "embed"

        "github.com/jackc/pgx/v5/pgxpool"
        "github.com/jackc/pgx/v5/stdlib"

        "github.com/pressly/goose/v3"
)

//go:embed migrations/*.sql
var embedMigrations embed.FS

// SetupPostgres creates a Postgres connection and runs migrations.
func SetupPostgres(ctx context.Context, connStr string) (*pgxpool.Pool, error) {
        conn, err := pgxpool.New(ctx, connStr)
        if err != nil {
                return nil, err
        }

        db := stdlib.OpenDBFromPool(conn)

        goose.SetBaseFS(embedMigrations)

        if err := goose.SetDialect("postgres"); err != nil {
                conn.Close()
                return nil, err
        }

        if err := goose.Up(db, "migrations"); err != nil {
                conn.Close()
                return nil, err
        }

        return conn, nil
}

func main() {
        connStr := "postgres://postgres:foo@localhost:5432/postgres?search_path=my_schema"
        ctx := context.Background()
        conn, err := SetupPostgres(ctx, connStr)
        if err != nil {
                panic(err)
        }
        conn.Close()
}

// go.mod
module hbibel/go-playground

go 1.21.0

require (
        github.com/jackc/pgx/v5 v5.5.5
        github.com/pressly/goose/v3 v3.20.0
)

require (
        github.com/jackc/pgpassfile v1.0.0 // indirect
        github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
        github.com/jackc/puddle/v2 v2.2.1 // indirect
        github.com/mfridman/interpolate v0.0.2 // indirect
        github.com/sethvargo/go-retry v0.2.4 // indirect
        go.uber.org/multierr v1.11.0 // indirect
        golang.org/x/crypto v0.21.0 // indirect
        golang.org/x/sync v0.7.0 // indirect
        golang.org/x/text v0.14.0 // indirect
)

-- migrations/0001_create_users.sql

-- +goose Up
CREATE TABLE users (id INTEGER, name TEXT);

-- +goose Down
DROP TABLE users;

database:

docker run --rm -e POSTGRES_PASSWORD=foo -p 5432:5432 postgres:16

[mfridman]

Thanks for providing a reproducible example, I'll try to play around with this to see if I can debug this locally and see the same thing.