Repository: pressly/goose (language: go)

Issue:
The "postgres migration table existence check" feature added in v3.23.1 is a breaking change for non-default schema
Before this change, the goose migrations "just worked", as long as the postgres searchPath was set to the correct schema.
With the new change, the existence check looks in the "Public" schema, where it is not present, and then fails when trying to create the table in the default schema (according to searchPath).

While we might get around this, by prefixing the migration table name with the schema, it then requires the migration to be schema aware, where it didn't need to be before. 


Discussion / hints:
Apologies this broke you. We typically take backwards-incompatible changes quite seriously but this was one of those changes we thought would improve the implementation and affect very few folks, because 

1. most (but not all) likely use the `public` schema
2. are already setting a goose table with the schema and/or specifying the schema in their migrations

Your feedback is valid though. Would a `--schema-prefix` flag or library option work for your use case?
We could make the table existence check opt-in, but the current implementation is more robust since it doesn't rely on error handling to detect tables. Several users reported that the previous approach created unnecessary log noise.

EDIT: I wonder if we could update the implementation to only search for the table name without specifying the schema, and optionally let users specify the schema, instead of defaulting to `"public"`. That way, it should "just work", respecting the schemas specified in your `search_path` setting.

Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index aab29c1bd..e64d43ca1 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -7,6 +7,9 @@ adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html).
 
 ## [Unreleased]
 
+- Fix regression (`v3.23.1` and `v3.24.0`) in postgres migration table existence check for
+  non-default schema. (#882, #883)
+
 ## [v3.24.0]
 
 - Add support for loading environment variables from `.env` files, enabled by default.
diff --git a/internal/dialect/dialectquery/postgres.go b/internal/dialect/dialectquery/postgres.go
index facbc2ff7..21ba63b36 100644
--- a/internal/dialect/dialectquery/postgres.go
+++ b/internal/dialect/dialectquery/postgres.go
@@ -5,13 +5,6 @@ import (
 	"strings"
 )
 
-const (
-	// defaultSchemaName is the default schema name for Postgres.
-	//
-	// https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PUBLIC
-	defaultSchemaName = "public"
-)
-
 type Postgres struct{}
 
 var _ Querier = (*Postgres)(nil)
@@ -53,14 +46,18 @@ func (p *Postgres) GetLatestVersion(tableName string) string {
 
 func (p *Postgres) TableExists(tableName string) string {
 	schemaName, tableName := parseTableIdentifier(tableName)
-	q := `SELECT EXISTS ( SELECT FROM pg_tables WHERE schemaname = '%s' AND tablename = '%s' )`
-	return fmt.Sprintf(q, schemaName, tableName)
+	if schemaName != "" {
+		q := `SELECT EXISTS ( SELECT 1 FROM pg_tables WHERE schemaname = '%s' AND tablename = '%s' )`
+		return fmt.Sprintf(q, schemaName, tableName)
+	}
+	q := `SELECT EXISTS ( SELECT 1 FROM pg_tables WHERE tablename = '%s' )`
+	return fmt.Sprintf(q, tableName)
 }
 
 func parseTableIdentifier(name string) (schema, table string) {
 	schema, table, found := strings.Cut(name, ".")
 	if !found {
-		return defaultSchemaName, name
+		return "", name
 	}
 	return schema, table
 }


Test patch: