mirror of
https://github.com/BaaRai/ARTEX.git
synced 2026-11-16 21:20:53 +08:00
Compare commits
7
Commits
| Author | SHA1 | Date | |
|---|---|---|---|
|
|
2c514a4cd9 | ||
|
|
d005bfe5ba | ||
|
|
0374f32aeb | ||
|
|
be317a1337 | ||
|
|
feaaf9e416 | ||
|
|
5463ea4375 | ||
|
|
5a8498bb4f |
@@ -48,6 +48,19 @@ jobs:
|
||||
# as rotating failures in the agent package). Serial package execution
|
||||
# keeps the database state deterministic.
|
||||
run: go test -p 1 ./...
|
||||
- name: online index scripts
|
||||
# Apply and roll back scripts/sql on a database built here from
|
||||
# db/schema.sql and db/migrations. The job-level artextest database
|
||||
# never gets the schema (packages use suffixed databases), and cached
|
||||
# test results mean no test database is guaranteed to exist.
|
||||
env:
|
||||
SQL_DSN: postgres://artex:testpass@localhost:5432/artextest_sqlscripts?sslmode=disable
|
||||
run: |
|
||||
psql "$ARTEX_PG_DSN" -X -v ON_ERROR_STOP=1 -c 'CREATE DATABASE artextest_sqlscripts'
|
||||
psql "$SQL_DSN" -X -q -v ON_ERROR_STOP=1 -f db/schema.sql
|
||||
for f in db/migrations/*.sql; do psql "$SQL_DSN" -X -q -v ON_ERROR_STOP=1 -f "$f"; done
|
||||
psql "$SQL_DSN" -X -v ON_ERROR_STOP=1 -f scripts/sql/20261115-task-query-indexes.sql
|
||||
psql "$SQL_DSN" -X -v ON_ERROR_STOP=1 -f scripts/sql/20261115-task-query-indexes.rollback.sql
|
||||
- name: go test -race
|
||||
# Race detector on the packages that pass under -race today. ./server
|
||||
# joined once its suite was checked with -race against postgres and its
|
||||
|
||||
@@ -0,0 +1,38 @@
|
||||
package db
|
||||
|
||||
import (
|
||||
"database/sql"
|
||||
"errors"
|
||||
"strings"
|
||||
"testing"
|
||||
)
|
||||
|
||||
// Migration 0004 leaves a valid, ready btree index on llm_usage(ts) for the
|
||||
// time-window queries (UsageDaily, JudgeUsageStats).
|
||||
func TestLLMUsageTsIndex(t *testing.T) {
|
||||
d, err := Open(testDSN(t))
|
||||
if err != nil {
|
||||
t.Skipf("postgres unavailable (%v) — skipping", err)
|
||||
}
|
||||
t.Cleanup(func() { d.Close() })
|
||||
|
||||
var valid, ready bool
|
||||
var def string
|
||||
err = d.QueryRow(`
|
||||
SELECT i.indisvalid, i.indisready, pg_get_indexdef(i.indexrelid)
|
||||
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
|
||||
JOIN pg_namespace n ON n.oid = c.relnamespace
|
||||
WHERE n.nspname='public' AND c.relname='idx_llm_usage_ts'`).Scan(&valid, &ready, &def)
|
||||
if errors.Is(err, sql.ErrNoRows) {
|
||||
t.Fatal("index public.idx_llm_usage_ts does not exist after Open")
|
||||
}
|
||||
if err != nil {
|
||||
t.Fatal(err)
|
||||
}
|
||||
if !valid || !ready {
|
||||
t.Fatalf("idx_llm_usage_ts indisvalid=%v indisready=%v, want both true (%s)", valid, ready, def)
|
||||
}
|
||||
if !strings.Contains(def, "USING btree (ts)") {
|
||||
t.Fatalf("idx_llm_usage_ts definition %q does not index ts with a btree", def)
|
||||
}
|
||||
}
|
||||
+2
-2
@@ -136,9 +136,9 @@ func assertEmbeddedApplied(t *testing.T, d *DB) []appliedMigration {
|
||||
want := []struct {
|
||||
version int
|
||||
name string
|
||||
}{{1, llmRecordsMigration}, {2, llmUsageMigration}, {3, "0003_task_scope_icp_index.sql"}}
|
||||
}{{1, llmRecordsMigration}, {2, llmUsageMigration}, {3, "0003_task_scope_icp_index.sql"}, {4, "0004_llm_usage_ts_index.sql"}}
|
||||
if len(applied) != len(want) {
|
||||
t.Fatalf("schema_migrations = %+v, want versions 1 to 3", applied)
|
||||
t.Fatalf("schema_migrations = %+v, want versions 1 to 4", applied)
|
||||
}
|
||||
for i, w := range want {
|
||||
content, err := fs.ReadFile(migrationsFS(), w.name)
|
||||
|
||||
@@ -0,0 +1,15 @@
|
||||
-- UsageDaily and JudgeUsageStats (db/llm_usage.go) filter llm_usage on
|
||||
-- ts >= now() - interval; the dashboard polls UsageDaily every 15 seconds.
|
||||
-- llm_usage is an append-only ledger that gains a row per LLM call and is never
|
||||
-- purged, so without an index on ts those queries scan the whole table.
|
||||
--
|
||||
-- This is a plain (non-concurrent) CREATE INDEX: migrations run in a
|
||||
-- transaction, where CONCURRENTLY is not allowed. It is safe here because
|
||||
-- db.Open applies migrations at startup, before the HTTP server listens, so the
|
||||
-- SHARE lock it takes blocks no agent call or request in flight.
|
||||
--
|
||||
-- For an exceptionally large ledger, build the same index online before the
|
||||
-- upgrade, outside a transaction:
|
||||
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_llm_usage_ts ON llm_usage(ts);
|
||||
-- This migration then finds it and does nothing.
|
||||
CREATE INDEX IF NOT EXISTS idx_llm_usage_ts ON llm_usage(ts);
|
||||
+22
-5
@@ -79,15 +79,20 @@ type TaskArchive struct {
|
||||
UpdatedAt time.Time `json:"updated_at"`
|
||||
}
|
||||
|
||||
// archiveBlockerScope selects the live direct dependents that block archiving
|
||||
// their source task. TaskArchiveBlockers and TaskArchiveBlocker share it so the
|
||||
// list and the detail view cannot disagree.
|
||||
const archiveBlockerScope = `
|
||||
FROM task_relations relation
|
||||
JOIN tasks child ON child.id=relation.task_id AND child.deleted_at IS NULL
|
||||
LEFT JOIN task_archives pending ON pending.task_id=child.id
|
||||
WHERE (pending.id IS NULL OR pending.state NOT IN ('archive_queued','archiving'))`
|
||||
|
||||
// TaskArchiveBlockers returns one live direct dependent for every source task
|
||||
// that cannot currently be archived. Dependents already queued for archiving do
|
||||
// not block their source because the FIFO worker will compact them first.
|
||||
func (d *DB) TaskArchiveBlockers() (map[int64]int64, error) {
|
||||
rows, err := d.Query(`SELECT relation.source_task_id, MIN(child.id)
|
||||
FROM task_relations relation
|
||||
JOIN tasks child ON child.id=relation.task_id AND child.deleted_at IS NULL
|
||||
LEFT JOIN task_archives pending ON pending.task_id=child.id
|
||||
WHERE pending.id IS NULL OR pending.state NOT IN ('archive_queued','archiving')
|
||||
rows, err := d.Query(`SELECT relation.source_task_id, MIN(child.id)` + archiveBlockerScope + `
|
||||
GROUP BY relation.source_task_id`)
|
||||
if err != nil {
|
||||
return nil, err
|
||||
@@ -104,6 +109,18 @@ GROUP BY relation.source_task_id`)
|
||||
return blockers, rows.Err()
|
||||
}
|
||||
|
||||
// TaskArchiveBlocker returns the live direct dependent that blocks taskID, or
|
||||
// 0 when nothing blocks it. Same rules as TaskArchiveBlockers, scoped to one
|
||||
// source task so getTask does not aggregate the whole relation table per poll.
|
||||
func (d *DB) TaskArchiveBlocker(taskID int64) (int64, error) {
|
||||
var dependent sql.NullInt64
|
||||
if err := d.QueryRow(`SELECT MIN(child.id)`+archiveBlockerScope+`
|
||||
AND relation.source_task_id=$1`, taskID).Scan(&dependent); err != nil {
|
||||
return 0, err
|
||||
}
|
||||
return dependent.Int64, nil
|
||||
}
|
||||
|
||||
type TaskArchivePage struct {
|
||||
Items []TaskArchive `json:"items"`
|
||||
Total int `json:"total"`
|
||||
|
||||
@@ -362,6 +362,7 @@ func TestTaskArchiveBlockersIgnoreQueuedDependents(t *testing.T) {
|
||||
if blockers[source.ID] != dependent.ID {
|
||||
t.Fatalf("source blocker=%d, want dependent %d", blockers[source.ID], dependent.ID)
|
||||
}
|
||||
assertPerTaskBlockers(t, d, blockers, map[int64]int64{source.ID: dependent.ID, dependent.ID: 0})
|
||||
if err := d.SetPaused(dependent.ID, true); err != nil {
|
||||
t.Fatal(err)
|
||||
}
|
||||
@@ -375,6 +376,25 @@ func TestTaskArchiveBlockersIgnoreQueuedDependents(t *testing.T) {
|
||||
if blocker := blockers[source.ID]; blocker != 0 {
|
||||
t.Fatalf("queued dependent must not block source, got %d", blocker)
|
||||
}
|
||||
assertPerTaskBlockers(t, d, blockers, map[int64]int64{source.ID: 0, dependent.ID: 0})
|
||||
}
|
||||
|
||||
// assertPerTaskBlockers checks TaskArchiveBlocker against the expected blocker
|
||||
// of each task and against the global TaskArchiveBlockers map.
|
||||
func assertPerTaskBlockers(t *testing.T, d *DB, global map[int64]int64, want map[int64]int64) {
|
||||
t.Helper()
|
||||
for taskID, wantBlocker := range want {
|
||||
got, err := d.TaskArchiveBlocker(taskID)
|
||||
if err != nil {
|
||||
t.Fatal(err)
|
||||
}
|
||||
if got != wantBlocker {
|
||||
t.Fatalf("TaskArchiveBlocker(%d)=%d, want %d", taskID, got, wantBlocker)
|
||||
}
|
||||
if got != global[taskID] {
|
||||
t.Fatalf("TaskArchiveBlocker(%d)=%d, TaskArchiveBlockers()[%d]=%d", taskID, got, taskID, global[taskID])
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
func TestRecoverInterruptedArchiveRequiresManualRetry(t *testing.T) {
|
||||
|
||||
@@ -0,0 +1,6 @@
|
||||
\set ON_ERROR_STOP on
|
||||
-- Removes only the two indexes introduced by this change; retains table data.
|
||||
SET lock_timeout = '5s';
|
||||
SET statement_timeout = '10min';
|
||||
DROP INDEX CONCURRENTLY IF EXISTS public.idx_activity_tool_result_lookup;
|
||||
DROP INDEX CONCURRENTLY IF EXISTS public.idx_llm_usage_task_model_tokens;
|
||||
Executable
+22
@@ -0,0 +1,22 @@
|
||||
\set ON_ERROR_STOP on
|
||||
-- Run with psql outside a transaction. Adds indexes without blocking normal DML.
|
||||
SET lock_timeout = '5s';
|
||||
SET statement_timeout = '10min';
|
||||
SET maintenance_work_mem = '128MB';
|
||||
SET max_parallel_maintenance_workers = 1;
|
||||
|
||||
-- db/commands.go joins by tool_use_id without an exploration_id constraint.
|
||||
-- The existing (exploration_id, tool_use_id, id) index has the wrong leading key.
|
||||
CREATE INDEX CONCURRENTLY idx_activity_tool_result_lookup
|
||||
ON public.activity (tool_use_id) INCLUDE (is_error)
|
||||
WHERE kind = 'tool_result';
|
||||
|
||||
-- Match TokenByModel's actual COALESCE(task_id, '') predicate and cover its sums.
|
||||
CREATE INDEX CONCURRENTLY idx_llm_usage_task_model_tokens
|
||||
ON public.llm_usage ((COALESCE(task_id, '')), model)
|
||||
INCLUDE (task_id, input_tokens, output_tokens, cache_read, cache_write);
|
||||
|
||||
-- Mark committed append-only pages visible so the covering index can avoid heap reads.
|
||||
SET vacuum_cost_delay = '2ms';
|
||||
SET vacuum_cost_limit = 1000;
|
||||
VACUUM (ANALYZE) public.llm_usage;
|
||||
Executable
+38
@@ -0,0 +1,38 @@
|
||||
# Online task-query indexes
|
||||
|
||||
`20261115-task-query-indexes.sql` adds two PostgreSQL indexes for existing queries:
|
||||
|
||||
- `activity(tool_use_id) INCLUDE (is_error) WHERE kind = 'tool_result'` supports the tool-result join in `db/commands.go` (`LEFT JOIN activity r ON r.tool_use_id = u.tool_use_id AND r.kind = 'tool_result'`). That join does not constrain the result row's exploration ID, so the existing `idx_act_tool_call (exploration_id, tool_use_id, id)` has the wrong leading key for it.
|
||||
- `llm_usage((COALESCE(task_id, '')), model) INCLUDE (task_id, input_tokens, output_tokens, cache_read, cache_write)` covers the per-task model aggregation in `db/llm_usage.go` (`TokenByModel`). That query filters on `COALESCE(task_id,'') = $1` and groups by `model`. The expression matches that predicate; the existing `idx_llm_usage_model (task_id, model)` cannot serve it.
|
||||
|
||||
## Apply
|
||||
|
||||
```sh
|
||||
psql -X -v ON_ERROR_STOP=1 -d <database> -f scripts/sql/20261115-task-query-indexes.sql
|
||||
```
|
||||
|
||||
Run it outside a transaction: `CREATE INDEX CONCURRENTLY` cannot run inside one, so do not wrap the file in `BEGIN`/`COMMIT` or use `psql -1`. Use the deployment's normal PostgreSQL authentication.
|
||||
|
||||
Both indexes are created concurrently, so normal reads and writes continue. The script sets a 5-second lock timeout, a 10-minute statement timeout and bounded maintenance resources. Its final `VACUUM (ANALYZE)` is an ordinary vacuum, not `VACUUM FULL`; it updates visibility and statistics so the covering index can serve index-only scans.
|
||||
|
||||
This is an explicit, one-time maintenance step, not a numbered migration. `db/migrate.go` runs each migration file inside a transaction, where `CONCURRENTLY` is not allowed. A plain `CREATE INDEX` at startup would lock `activity` against writes for the whole build. No application or database restart is required.
|
||||
|
||||
## After an interruption
|
||||
|
||||
The script intentionally fails if an index with the same name already exists. A partial run may have created the first index before the second failed. An interrupted concurrent build can also leave an invalid index behind. Before retrying, check the state of both indexes:
|
||||
|
||||
```sql
|
||||
SELECT c.relname, i.indisvalid, i.indisready
|
||||
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
|
||||
WHERE c.relname IN ('idx_activity_tool_result_lookup', 'idx_llm_usage_task_model_tokens');
|
||||
```
|
||||
|
||||
Both must show `indisvalid = true` and `indisready = true`. Drop an index that is invalid or not ready with `DROP INDEX CONCURRENTLY`, or run the rollback below, then rerun the script.
|
||||
|
||||
## Rollback
|
||||
|
||||
```sh
|
||||
psql -X -v ON_ERROR_STOP=1 -d <database> -f scripts/sql/20261115-task-query-indexes.rollback.sql
|
||||
```
|
||||
|
||||
The rollback drops only these two indexes, concurrently and only if they exist. Table data is kept. Review the current index definitions before applying it. No application restart is required.
|
||||
+9
-2
@@ -82,9 +82,16 @@ func applyTaskArchiveBlocker(dto *TaskDTO, blockers map[int64]int64) {
|
||||
if err != nil {
|
||||
return
|
||||
}
|
||||
if dependentID := blockers[taskID]; dependentID > 0 {
|
||||
dto.ArchiveBlockedBy = strconv.FormatInt(dependentID, 10)
|
||||
applyTaskArchiveBlockerID(dto, blockers[taskID])
|
||||
}
|
||||
|
||||
// applyTaskArchiveBlockerID records dependentID as the task's archive blocker;
|
||||
// 0 (nothing blocks it) leaves the DTO unchanged.
|
||||
func applyTaskArchiveBlockerID(dto *TaskDTO, dependentID int64) {
|
||||
if dto == nil || dependentID <= 0 {
|
||||
return
|
||||
}
|
||||
dto.ArchiveBlockedBy = strconv.FormatInt(dependentID, 10)
|
||||
}
|
||||
|
||||
// TokenTotalDTO is a whole-task (all agents) token aggregate.
|
||||
|
||||
+4
-2
@@ -1932,8 +1932,10 @@ func (s *Server) getTask(w http.ResponseWriter, r *http.Request) {
|
||||
log.Printf("[tasks] detail metrics query failed for %s — metrics zeroed: %v", t.ID, err)
|
||||
}
|
||||
s.applyTaskMetrics(&dto, t, metrics)
|
||||
archiveBlockers, _ := s.m.PG().TaskArchiveBlockers()
|
||||
applyTaskArchiveBlocker(&dto, archiveBlockers)
|
||||
// Best effort: only this task's blocker, not the whole relation table.
|
||||
taskID, _ := strconv.ParseInt(t.ID, 10, 64)
|
||||
blocker, _ := s.m.PG().TaskArchiveBlocker(taskID)
|
||||
applyTaskArchiveBlockerID(&dto, blocker)
|
||||
writeJSON(w, 200, dto)
|
||||
}
|
||||
|
||||
|
||||
@@ -184,6 +184,18 @@ func TestApplyTaskArchiveBlocker(t *testing.T) {
|
||||
if !strings.Contains(string(raw), `"archive_blocked_by_task_id":"11"`) {
|
||||
t.Fatalf("archive blocker missing from payload: %s", raw)
|
||||
}
|
||||
|
||||
byID := taskDTO(&Task{ID: "7"}, "paused")
|
||||
applyTaskArchiveBlockerID(&byID, 12)
|
||||
if byID.ArchiveBlockedBy != "12" {
|
||||
t.Fatalf("archive blocker by id=%q, want 12", byID.ArchiveBlockedBy)
|
||||
}
|
||||
none := taskDTO(&Task{ID: "7"}, "paused")
|
||||
applyTaskArchiveBlockerID(&none, 0)
|
||||
if none.ArchiveBlockedBy != "" {
|
||||
t.Fatalf("no blocker set %q", none.ArchiveBlockedBy)
|
||||
}
|
||||
applyTaskArchiveBlockerID(nil, 12) // must not panic
|
||||
}
|
||||
|
||||
func TestTaskLLMStateSnapshotIsConcurrentAndDetached(t *testing.T) {
|
||||
|
||||
Reference in New Issue
Block a user