Repeated database initialization can drop one of `action`'s ordered indexes

[TomUvi]

Gitea Version

1.27.3

What happened?

Description

Gitea 1.27.3 can remove IDX_action_c_u during a repeated database
initialization on PostgreSQL. A later initialization does not restore the
missing index. Both indexes are declared by the model and have the same columns
in different, intentional orders:

• IDX_action_c_u: (user_id, is_deleted, created_unix)
• IDX_action_c_u_d: (created_unix, user_id, is_deleted)

The different leading columns support different query access patterns, as
described in PR #38076.

Reproduction

Environment: official Gitea 1.27.3 binary, PostgreSQL 18.6, empty disposable
database, offline configuration.

1. Run gitea migrate against the empty database in a new process.
2. Verify that both indexes above exist with the stated column order.
3. Run the same gitea migrate command again in another process.
4. Inspect pg_indexes or pg_index.

In a bounded clean-schema test, step 3 removed only IDX_action_c_u on the first
trial. The full catalog changed by exactly that missing object.

A deterministic reconciliation test also fails:

1. Start from the pristine schema containing both indexes.
2. Drop only IDX_action_c_u.
3. Run gitea migrate in a new process.

The declared index remains missing and no other catalog object changes.

Expected behavior

Repeated initialization preserves both ordered indexes. If a declared index is
missing, schema synchronization restores the exact definition.

Root cause

Gitea 1.27.3 pins XORM 1.3.11. schemas.Index.Equal compares type and column
count, then searches each column anywhere in the other index. It ignores column
position and index name. SyncWithOptions matches desired and existing indexes
by ranging over maps, accepts the first equal index and does not consume that
match. Both desired indexes can therefore match one existing index; the other is
unmarked and dropped, or a missing sibling is never added.

PR #38076 already documents the order-insensitive Equal limitation in the
v339 migration and explicitly recreates c_u. The unconditional SyncAllTables
pass that follows migration can still undo the intended schema. Gitea main pins
XORM 1.4.1, whose equality and matching logic remains unchanged; current XORM
v1 source also retains it.

Impact

PR #38076 added the three-column c_u form to avoid sorting all matching
dashboard actions before applying the page limit. Losing it can reintroduce the
slow dashboard query while initialization still succeeds.

Suggested direction

Make composite-index equality positional and require one-to-one matching of
desired and existing indexes. Cover same-column/different-order siblings and a
missing sibling in regression tests. Then update Gitea to the fixed XORM release
and test repeated initialization on each supported database.

Candidate XORM change

The direct fix for this Gitea case is for schemas.Index.Equal to compare
column positions instead of searching the whole destination slice:

 for i := 0; i < len(index.Cols); i++ {
-    var found bool
-    for j := 0; j < len(dst.Cols); j++ {
-        if index.Cols[i] == dst.Cols[j] {
-            found = true
-            break
-        }
-    }
-    if !found {
+    if index.Cols[i] != dst.Cols[i] {
         return false
     }
 }

The positional comparison fixes the Gitea pair directly. As separate hardening,
SyncWithOptions should also make matching one-to-one, but merely skipping an
already used database index is insufficient. With identical desired indexes A
and B and only actual B, A could otherwise consume B first and make the later
attempt to create B fail because that database name already exists.

A robust matcher should use two passes:

// First reserve every same-name, equal definition.
for name, expected := range table.Indexes {
    if actual, ok := oriTable.Indexes[name]; ok && expected.Equal(actual) {
        matchedExpected[name] = true
        foundIndexNames[name] = true
    }
}

// Then match only the remaining definitions to unused equivalent indexes.
for name, expected := range table.Indexes {
    if matchedExpected[name] {
        continue
    }
    for actualName, actual := range oriTable.Indexes {
        if foundIndexNames[actualName] {
            continue
        }
        if expected.Equal(actual) {
            matchedExpected[name] = true
            foundIndexNames[actualName] = true
            break
        }
    }
    if !matchedExpected[name] {
        addedNames[name] = expected
    }
}

Maintainers should decide separately whether fallback matching across different
names remains compatible behavior. Exact same-name matches must be reserved
before that fallback.

Required regression coverage:

1. Index.Equal returns false for (a,b,c) versus (c,a,b) and true for the
   same ordered columns.
2. A table declaring two same-set/different-order indexes retains both after
   repeated SyncWithOptions calls.
3. After either ordered sibling is dropped, the next sync restores that exact
   sibling without changing the other index.
4. For identical ordered definitions named A and B, test actual {A,B}, {A}
   and {B}; restore the exact missing name without a duplicate-name error.
5. A changed definition under the same name is dropped and recreated.
6. Run the synchronization tests for PostgreSQL, MySQL, SQLite and MSSQL because
   the matching loop is shared and positional comparison can expose an
   introspector that returns the wrong column order.

This is a source-level candidate derived from the pinned implementation. It can
cause a one-time rebuild where an existing index has the same columns in another
order; that is necessary for physical lookup semantics, but still needs the XORM
maintainers' compatibility review and cross-database test suite before release.

Relevant sources:

• https://github.com/go-gitea/gitea/blob/v1.27.3/models/activities/action.go#L151-L169
• https://github.com/go-gitea/gitea/blob/v1.27.3/models/migrations/v1_27/v339.go#L14-L39
• perf: extend action c_u index to include created_unix for faster dashboard feeds #38076
• https://gitea.com/xorm/xorm/src/tag/v1.3.11/schemas/index.go#L50-L72
• https://gitea.com/xorm/xorm/src/tag/v1.3.11/sync.go#L228-L282

How are you running Gitea?

Official Gitea 1.27.3 binary (gitea version 1.27.3 built with go1.26.7-X:jsonv2 : bindata) on macOS arm64, using a new isolated PostgreSQL 18.6 (Homebrew) database over a private Unix socket. The fixture is offline and uses synthetic data only.