[bugfix] fix issues with postgres array serialization (#4295)

thank you to @kipvandenbos -erino for figuring this out!

Reviewed-on: https://codeberg.org/superseriousbusiness/gotosocial/pulls/4295
Co-authored-by: kim <grufwub@gmail.com>
Co-committed-by: kim <grufwub@gmail.com>

4 files changed, 38 insertions, 2 deletions

diff --git a/internal/db/bundb/media.go b/internal/db/bundb/media.go
index 845844980..86f63ace2 100644
--- a/internal/db/bundb/media.go
+++ b/internal/db/bundb/media.go
@@ -196,13 +196,17 @@ func (m *mediaDB) DeleteAttachment(ctx context.Context, id string) error {
 			})
 
 			if len(updatedIDs) != len(status.AttachmentIDs) {
+
+				// Convert to bun array for serialization.
+				arrIDs := bunArrayType(tx, updatedIDs)
+
 				// Note: this handles not found.
 				//
 				// Attachments changed, update the status.
 				if _, err := tx.NewUpdate().
 					Table("statuses").
 					Where("? = ?", bun.Ident("id"), status.ID).
-					Set("? = ?", bun.Ident("attachment_ids"), updatedIDs).
+					Set("? = ?", bun.Ident("attachment_ids"), arrIDs).
 					Exec(ctx); err != nil {
 					return gtserror.Newf("error updating status: %w", err)
 				}
diff --git a/internal/db/bundb/migrations/20250625173327_filter_migration_fix.go b/internal/db/bundb/migrations/20250625173327_filter_migration_fix.go
index c5fa965a4..31c3713c6 100644
--- a/internal/db/bundb/migrations/20250625173327_filter_migration_fix.go
+++ b/internal/db/bundb/migrations/20250625173327_filter_migration_fix.go
@@ -110,12 +110,16 @@ func init() {
 						return gtserror.Newf("error selecting %T ids: %w", data.Model, err)
 					}
 
+					// Convert related IDs to bun array
+					// type for serialization in query.
+					arrIDs := bunArrayType(tx, relatedIDs)
+
 					// Now update the relevant filter
 					// row to contain these related IDs.
 					if _, err := tx.NewUpdate().
 						Model((*newmodel.Filter)(nil)).
 						Where("? = ?", bun.Ident("id"), filterID).
-						Set("? = ?", bun.Ident(col), relatedIDs).
+						Set("? = ?", bun.Ident(col), arrIDs).
 						Exec(ctx); err != nil {
 						return gtserror.Newf("error updating filters.%s ids: %w", col, err)
 					}
diff --git a/internal/db/bundb/migrations/util.go b/internal/db/bundb/migrations/util.go
index f20f23c3f..4a3a62b21 100644
--- a/internal/db/bundb/migrations/util.go
+++ b/internal/db/bundb/migrations/util.go
@@ -34,10 +34,24 @@ import (
 	"github.com/uptrace/bun"
 	"github.com/uptrace/bun/dialect"
 	"github.com/uptrace/bun/dialect/feature"
+	"github.com/uptrace/bun/dialect/pgdialect"
 	"github.com/uptrace/bun/dialect/sqltype"
 	"github.com/uptrace/bun/schema"
 )
 
+// bunArrayType wraps the given type in a pgdialect.Array
+// if needed, which postgres wants for serializing arrays.
+func bunArrayType(db bun.IDB, arr any) any {
+	switch db.Dialect().Name() {
+	case dialect.SQLite:
+		return arr // return as-is
+	case dialect.PG:
+		return pgdialect.Array(arr)
+	default:
+		panic("unreachable")
+	}
+}
+
 // doWALCheckpoint attempt to force a WAL file merge on SQLite3,
 // which can be useful given how much can build-up in the WAL.
 //
diff --git a/internal/db/bundb/util.go b/internal/db/bundb/util.go
index 6c743ffe9..d0d25a236 100644
--- a/internal/db/bundb/util.go
+++ b/internal/db/bundb/util.go
@@ -29,6 +29,7 @@ import (
 	"code.superseriousbusiness.org/gotosocial/internal/paging"
 	"github.com/uptrace/bun"
 	"github.com/uptrace/bun/dialect"
+	"github.com/uptrace/bun/dialect/pgdialect"
 )
 
 // likeEscaper is a thread-safe string replacer which escapes
@@ -60,6 +61,19 @@ func likeOperator(query *bun.SelectQuery) string {
 	return ""
 }
 
+// bunArrayType wraps the given type in a pgdialect.Array
+// if needed, which postgres wants for serializing arrays.
+func bunArrayType(db bun.IDB, arr any) any {
+	switch db.Dialect().Name() {
+	case dialect.SQLite:
+		return arr // return as-is
+	case dialect.PG:
+		return pgdialect.Array(arr)
+	default:
+		panic("unreachable")
+	}
+}
+
 // whereLike appends a WHERE clause to the
 // given SelectQuery, which searches for
 // matches of `search` in the given subQuery