go-postgres-migration-ambiguous-column

Answer

Root cause: rollup_domains_daily() is an INSERT INTO metrics_domains_daily ... SELECT ... FROM shadow_attribution sa JOIN tool_events te ... whose SELECT list and GROUP BY referenced publisher_domain and user_id unqualified. Both columns exist in both tables, so Postgres raises ERROR: column reference "publisher_domain" is ambiguous — SQLSTATE 42702 — and the nightly rollup fails, leaving metrics_domains_daily empty.

Fix (3 parts):

1. Edit the base migration 001_*.up.sql for fresh installs

Every column that exists in both tables must be qualified (sa. = shadow_attribution, te. = tool_events):

CREATE OR REPLACE FUNCTION rollup_domains_daily(rollup_date date DEFAULT CURRENT_DATE)
RETURNS void AS $$
BEGIN
    INSERT INTO metrics_domains_daily (metric_date, publisher_domain, user_id, event_count, attributed_count)
    SELECT
        rollup_date,
        sa.publisher_domain,      -- was: publisher_domain  (ambiguous)
        sa.user_id,               -- was: user_id           (ambiguous)
        count(te.id),
        count(sa.id)
    FROM shadow_attribution sa
    JOIN tool_events te ON te.user_id = sa.user_id
                        AND te.publisher_domain = sa.publisher_domain
                        AND te.occurred_at::date = sa.attributed_at::date
    WHERE te.occurred_at::date = rollup_date
    GROUP BY sa.publisher_domain, sa.user_id;   -- was: GROUP BY publisher_domain, user_id
END;
$$ LANGUAGE plpgsql;

2. Add migration 015_rollup_domains_daily_fix.up.sql for live DBs

001 is already recorded in schema_migrations, so the migrate runner never re-executes it on live databases. The corrected function must be (re)defined in a new migration. CREATE OR REPLACE is safe here — it replaces the body of the existing function in place, so no DROP/re-grant dance is required:

-- 015_rollup_domains_daily_fix.up.sql
CREATE OR REPLACE FUNCTION rollup_domains_daily(rollup_date date DEFAULT CURRENT_DATE)
RETURNS void AS $$
BEGIN
    INSERT INTO metrics_domains_daily (metric_date, publisher_domain, user_id, event_count, attributed_count)
    SELECT rollup_date,
           sa.publisher_domain, sa.user_id,
           count(te.id), count(sa.id)
    FROM shadow_attribution sa
    JOIN tool_events te ON te.user_id = sa.user_id
                        AND te.publisher_domain = sa.publisher_domain
                        AND te.occurred_at::date = sa.attributed_at::date
    WHERE te.occurred_at::date = rollup_date
    GROUP BY sa.publisher_domain, sa.user_id;
END;
$$ LANGUAGE plpgsql;

-- 015_rollup_domains_daily_fix.down.sql
-- Re-create the original (buggy) body, or no-op with a comment; typically:
-- SELECT 1;  -- non-destructive; the fixed body is the desired state

3. Remove the test SKIP branch so post-conditions run

The integration test contained a branch that skipped the known failure:

// remove this whole block:
if os.Getenv("ROLLUP_SKIP_KNOWN_BUG") != "" {
    t.Skip("rollup_domains_daily has ambiguous-column bug (SQLSTATE 42702)")
}

After removal, the post-conditions execute and assert the fix:

func TestRollupDomainsDaily(t *testing.T) {
    seedAttributionsAndEvents(t)            // u1/example.com (2 events), u2/example.org (1 event)
    _, err := db.Exec("SELECT rollup_domains_daily()")
    if err != nil {
        t.Fatalf("rollup_domains_daily failed: %v", err) // was 42702 before fix
    }
    var n int
    if err := db.QueryRow(
        "SELECT count(*) FROM metrics_domains_daily WHERE metric_date = CURRENT_DATE",
    ).Scan(&n); err != nil {
        t.Fatal(err)
    }
    if n < 2 {
        t.Fatalf("expected >=2 rolled-up rows, got %d", n)
    }
}

Live verification (docker-compose)

docker compose build api && docker compose up -d api      # rebuild & restart
docker compose logs api | grep -i "migrat"                # expect: applied 015_rollup_domains_daily_fix
docker compose exec db psql -U app -d app -c "SELECT rollup_domains_daily();"
docker compose exec db psql -U app -d app -c "SELECT count(*) FROM metrics_domains_daily WHERE metric_date = CURRENT_DATE;"
docker compose logs api | grep -i "daily rollup failed"   # expect: no matches after fix