Set statement_timeout at the ROLE level (ALTER ROLE): unsettable from the client under Supavisor transaction mode, so MARS-695 could not include it

Sub-tickets

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus descriptionChanged · 5w ago
  was: Split off MARS-695 (app-side breaker) because the mechanism is NOT available at the app layer and I will not fake it. FINDING. Elazar authorised statement_timeout=10000 on the Mars runtime pool. It cannot be set there. Supabase documents session-level timeout settings as usable ONLY through Supavisor SESSION mode (:5432) or a direct connection - 'It cannot be used in the Dashboard, with the Supabase Client API, nor with Supavisor in Transaction mode (port 6543)'. Source: https://supabase.com/docs/guides/database/postgres/timeouts . Mars's runtime DATABASE_URL is the TRANSACTION pooler :6543 - MARS-145's own close event records the cutover at SHA c987038, 'Serverless runtime cut from direct-5432 to Supavisor TRANSACTION pooler (:6543, dot-user DSN)'. So neither of the two client-side routes works: the startup parameter is not supported in transaction mode, and a session-level SET does not survive because the server connection is returned to Supavisor between transactions. I did NOT ship a workaround. Two were considered and rejected rather than silently substituted: (a) adding to the pool config - unverifiable from the agent side (prod DATABASE_URL is Vercel type=sensitive and unreadable) and, if Supavisor rejects an unsupported startup parameter the way pgbouncer does, it breaks EVERY prod connection; (b) folding into withTransaction's BEGIN - mechanically works and is one round trip, but it covers only the WRITE path while the measured 46.7s regression is on a READ (get_practica_detail_bundle), and a blanket 10s on every transaction newly risks the cron and bulk paths (rectificar-reminder/route.ts:418, the dozen call sites in administrar-actions.ts). ASK: role-level . One DDL, covers reads AND writes, works regardless of pooling mode, zero application cost, no per-query round trip. db lane owns it. WHY IT MATTERS AND WHAT IT DOES NOT FIX. This is the hold-time half of the 2026-09-04 outage. MARS-680 measured a Postgres-reported max exec_time of 46.7s on get_practica_detail_bundle; db-mars-cc's 2026-08-30 sampling found 8 concurrent calls for the SAME practicaId each taking 16.9-37.6s, SELECT-only with no lock wait - a self-reinforcing pileup. Every second one of those runs is a second a SUPAVISOR server connection is held, which is the pool that actually exhausted (see MARS-695 for the evidence that the ECHECKOUTTIMEOUT came from Supavisor, not from our max:6 client pool). Bounding hold time is the only thing that attacks the cause; MARS-695's breaker only sheds load once the failure is already happening. DECIDE AND RECORD, do not guess: (1) which role the runtime DSN actually authenticates as - the dot-user form is what Supavisor presents, so confirm what ALTER ROLE has to name; (2) whether 10s is right for every caller on that role, or whether the cron/bulk paths need their own role or a per-function (evolutiva-db.md's push-compute-to-Postgres pattern already puts those in SQL functions, which can carry their own setting); (3) whether DATABASE_URL_DIRECT's session-mode weekly-kpi pool (max:16) shares the role and would inherit a limit that breaks a legitimately long KPI batch. A blanket ALTER ROLE that times out the Monday 06:00 ART weekly-kpi run trades one outage for another.
• wi-cli-venus commented · 5w ago
  SPEC, drafted by coder02-mars-cc for db-mars-cc to apply. Read the first section before doing anything: this WI as originally written is either already done or actively dangerous, depending on which role it means, and that ambiguity was the whole risk. MEASURED TONIGHT, pg_roles on prod: mars_app_runtime -> rolconfig = {statement_timeout=10000} postgres -> rolconfig = {search_path=...} (NO statement_timeout) No role matching 'postgres.%' exists at all. THREE CONSEQUENCES. 1. THE ASK IS ALREADY DONE ON mars_app_runtime. db-mars-cc set it when provisioning the role (migrations 127/128). Nobody needs to apply anything there. What is missing is not the setting - it is the cutover. 2. IT IS SET ON A ROLE THE APP DOES NOT USE. MARS-694 rolled the mars_app_runtime cutover back on 2026-09-05 after ~9 minutes of student-facing failure, and the retry is explicitly NOT authorised - it is still blocked on fn_admin_invariant_ok. Production DATABASE_URL authenticates as postgres today. So Mars currently runs with NO statement_timeout, exactly as MARS-695 states, and a 46.7s query (MARS-680) still holds a Supavisor server connection for 46.7s. This is precisely the set-versus-enforced distinction: the value reads back correctly on a role nothing connects as. 3. THEREFORE: MARS-696 IS BLOCKED ON MARS-694, NOT INDEPENDENTLY ACTIONABLE. It should be marked blocked with that dependency rather than left in backlog looking like available work - a ticket that cannot proceed but looks queued is the failure mode evolutiva-commons Scaffold-Time-Retirement-Check names for the backlog surface specifically. DO NOT SET IT ON postgres AS A STOPGAP. This is the one instruction in this spec that matters most, because it is the obvious shortcut and it is wrong. postgres is the superuser and it is shared: the Vercel runtime pool, weekly-kpi's own pool on DATABASE_URL_DIRECT (session mode :5432, max 16), venus applog-pull, every migration, Supabase MCP, and every agent psql session all authenticate as it. A 10s statement_timeout on postgres would cut off the Monday 06:00 ART weekly-kpi batch silently, break long migrations, and surface weeks later as an unrelated failure nobody connects to this change. It converts one page-load problem into several batch problems on a schedule where nobody is watching. The role split in MARS-694 exists BECAUSE a shared superuser cannot carry a per-workload setting - applying it to postgres would defeat the reason mars_app_runtime was created. WHAT IS ACTUALLY LEFT TO DO, in order: (a) Nothing on this WI until MARS-694's cutover is authorised by Elazar and lands. Block it on MARS-694. (b) When the cutover lands, CONFIRM enforcement rather than configuration. Acceptance evidence is a real long-running query being cut off as mars_app_runtime through the :6543 transaction pooler - e.g. SELECT pg_sleep(15) returning error 57014 (query_canceled) at ~10s. A rolconfig read is NOT acceptance: we have already been wrong once this week about which mechanisms take effect under Supavisor transaction pooling, and a value that is set and a value that is enforced are different claims. (c) Confirm the value survives the pooler. statement_timeout as a ROLE default is applied by Postgres at session start, so it should hold under transaction pooling where a client-side SET does not - but that is the assumption this whole WI turns on and it has not been measured on Mars. Measure it, do not reason it. (d) Confirm weekly-kpi is NOT affected: verify DATABASE_URL_DIRECT still authenticates as postgres after the cutover, not as mars_app_runtime. If the two ever converge on one role, 10s is wrong for the KPI batch and it needs its own role or a per-function SET (evolutiva-db.md's push-compute-to-Postgres pattern already puts those workloads in SQL functions, which can carry their own statement_timeout). (e) Decide whether 10s is right for every app caller. It is the value already on the role; nobody has checked it against the slowest legitimate app query. A report export that legitimately takes 12s would start failing. ROLE NAMING, stated plainly so it is not resolved by whoever reads this later: the target is the Postgres role mars_app_runtime. It is NOT the pooled DSN username. Supavisor presents a tenant-qualified username (the missing tenant suffix was MARS-694's actual root cause - FATAL ENOIDENTIFIER before any query ran), and that suffix is Supavisor routing, not a Postgres identifier - confirmed above by the absence of any 'postgres.%' role. ALTER ROLE must name the bare role. Cross-refs: MARS-695 (breaker, live - bounds the damage, not the cause), MARS-680 (the 46.7s regression this is meant to bound), MARS-694 (the blocking cutover), MARS-698 (second outage, and the measurement that appEvents cannot record these events at all).
• wi-cli-venus blocked · 5w ago
  Blocked on MARS-694. statement_timeout=10000 is ALREADY set on mars_app_runtime (verified in pg_roles tonight); the app still authenticates as postgres because MARS-694's cutover was rolled back and the retry is unauthorised. Nothing to apply until that lands. Must NOT be set on postgres as a stopgap - shared with weekly-kpi, applog-pull and migrations; see the spec comment.
• wi-cli-venus verified · 5w ago
  Spec item (c) CLOSED by db-mars-cc measurement, 2026-09-06. Role-default statement_timeout (ALTER ROLE ... SET) IS enforced under Supavisor transaction-mode pooling on :6543, identically to direct :5432. Method: disposable throwaway role db_mars_cc_probe_test (no grants, CONNECTION LIMIT 2, rolconfig statement_timeout=10000), never the mars_app_runtime credential. SHOW statement_timeout returned 10s on connect through BOTH :6543 and :5432; SELECT pg_sleep(15) was canceled at ~10s with 57014 'canceling statement due to statement timeout' on both paths. Role dropped in the same work item, post-drop pg_roles check confirms 0 rows. audit-mars-ca self-DM'd before and after per the review-depth pilot. This was the assumption the whole WI turned on and it is now measured rather than reasoned: a role default is applied by Postgres at session start, so it survives transaction pooling where a client-side SET does not. Do not re-derive it. WHAT THIS DOES NOT CHANGE. mars_app_runtime's rolconfig is correct and will enforce the moment anything authenticates as that role - but nothing does today, so the setting is correct and INERT. MARS-696 stays blocked on MARS-694's cutover authorisation. The remaining acceptance evidence at cutover time is unchanged and still required: the same 57014 cancellation reproduced as mars_app_runtime specifically, through the production DSN, because db-mars-cc's probe proves the MECHANISM works and not that this role on this connection string is configured correctly - MARS-694's root cause was a connection-string defect (missing Supavisor tenant suffix, FATAL ENOIDENTIFIER) that no role-side measurement would have caught. Still open from the spec: (d) confirm DATABASE_URL_DIRECT still authenticates as postgres after cutover so weekly-kpi does not inherit a 10s limit, and (e) confirm 10s clears the slowest legitimate app query.
• wi-cli-venus verified · 5w ago
  MEASURED (2026-09-06, db-mars-cc): ALTER ROLE ... SET statement_timeout IS enforced through Supavisor transaction-mode pooling (:6543), not just direct/session-mode. Used a disposable throwaway role (db_mars_cc_probe_test, no grants, CONNECTION LIMIT 2, ALTER ROLE ... SET statement_timeout=10000), created and dropped in the same test (post-drop pg_roles check confirmed 0 rows). Connected via :6543: SHOW statement_timeout returned 10s; SELECT pg_sleep(15) canceled at ~10s with 57014 "canceling statement due to statement timeout". Identical result via direct :5432 (control). This resolves the mechanism uncertainty in the original finding: the Supabase doc quote about transaction-mode limitations applies to a CLIENT-issued SET/startup-parameter, which does not survive because the server connection returns to Supavisor between transactions. A ROLE-level default (ALTER ROLE ... SET) is different - it is applied by Postgres itself at session start on the SERVER side of the pooled connection, independent of client-side SET support, so it is not subject to that limitation. mars_app_runtime already carries this rolconfig (set in migration 127/128) and will enforce correctly once anything actually authenticates as that role. Still blocked on MARS-694 (cutover not authorized) - nothing changes today.
• wi-cli-venus verified · 5w ago
  PRE-CUTOVER ACCEPTANCE EVIDENCE, 2026-09-10. Exact stored mars_app_runtime tenant-qualified :6543 DSN exercised directly: SELECT pg_sleep(15) was canceled with query_canceled/57014 under role-level statement_timeout=10s (client wall time 11.386s including connection setup). This closes the role-and-connection-string enforcement check without changing Vercel or deploying. Threshold check is favorable over the current pg_stat_statements window (stats_reset 2026-09-10 09:28:15 UTC): get_practica_detail_bundle had 27 calls, mean 354.8ms, max 1596.0ms; next application-shaped high-call statement classify_image had 96 calls, mean 39.5ms, max 1312.4ms; no observed high-call application statement exceeded 1.6s. Thus observed legitimate app traffic has at least 6.2x headroom under 10s. Boundary: this recent sample cannot prove rare unobserved exports; DATABASE_URL_DIRECT/weekly-kpi remains deliberately on postgres and must be confirmed after coder's Vercel cutover. No DB mutation, env write, or deployment performed.
• wi-cli-venus verified · 5w ago
  SPEC ITEM (e), NAMED BATCH PATHS MEASURED 2026-09-10 under exact mars_app_runtime tenant-qualified :6543 DSN, using EXPLAIN (ANALYZE, BUFFERS) on the exact SELECT text extracted from source. error-digest 24h grouped appEvents query: planning 12.119ms, execution 6.570ms, 3 rows, 17 shared hits, 0 reads/temp. rectificar-reminder cohort snapshot: planning 99.900ms, execution 132.180ms, 56 rows, 2708 shared hits + 5 reads, 0 temp. The slower named batch statement has ~75.6x execution-time headroom below 10s. statement_timeout is per statement, so route wall time, mail delivery, and transaction aggregate time are outside this check; neither route was invoked and no notification/state mutation occurred. Combined with current interactive stats (max 1.596s), all named runtime-role DB reads now measured are below 10s. MARS-696 remains blocked only on coder's Vercel cutover and post-cutover confirmation that DATABASE_URL_DIRECT still authenticates as postgres.
• wi-cli-venus verified · 5w ago
  POST-CUTOVER ITEM (d), 2026-09-10. Vercel production DATABASE_URL_DIRECT exists as type=sensitive and is structurally undecryptable even through the authenticated individual env API (HTTP 200 with sealed value). Its createdAt and updatedAt are identical: 2026-06-22 09:54:04 UTC, proving it was not modified during today's DATABASE_URL cutover. Independently exercised the canonical DATABASE_URL_DIRECT DSN retained in pm/06: configured user=postgres, direct host db.ustenjufophwhlkzfdso.supabase.co:5432, SELECT current_user returned postgres, statement_timeout returned 2min, inet_server_port returned 5432. Conclusion: configuration evidence supports weekly-kpi remaining isolated on postgres/direct and not inheriting mars_app_runtime's 10s limit. Strict boundary: the opaque Vercel value itself cannot be compared or directly exercised without invoking a deployed DIRECT-pool code path; source+metadata+canonical-credential evidence is confirmed, deployed-value identity is not independently observable from this lane.
• wi-cli-venus unblocked · 5w ago
  BLOCKER IS FALSE AS WRITTEN - MEASURED AGAINST THE RUNNING SYSTEM, NOT INFERRED FROM THE ROW. The blockedReason says 'the app still authenticates as postgres because MARS-694's cutover was rolled back and the retry is unauthorised.' The app does NOT authenticate as postgres. It authenticates as mars_app_runtime, right now, today. DISCRIMINATOR USED, because a connection snapshot alone would not have separated my own psql session from the app pool: I fired the live health probe with the DB arm and then read pg_stat_activity within the second. probe-curl.sh --app mars .../api/health?db=1 -> {"version":"2.79.45","deploymentId":"dpl_BDndpXxgQvea1XdY3MuKsBa3eZiT","dbOk":true,"ts":"2026-09-11T13:00:58.732Z"} pg_stat_activity, application_name='Supavisor', one second later: mars_app_runtime | 1 conn | backend_start 2026-09-11 12:51:49Z | state_change 2026-09-11 13:00:58.726Z applog_reader | 1 conn | (unrelated lane) The probe's SELECT 1 lands at 13:00:58.732 and the runtime connection's last activity is 13:00:58.726 - the same request, sub-10ms apart. AND THE NEGATIVE HALF MATTERS MORE THAN THE POSITIVE ONE: there is NO postgres connection through Supavisor at all. The only postgres backend on the box is my own direct :5432 psql. If the cutover had been rolled back there would be a postgres/Supavisor row and there is not. CONSEQUENCE: this row's ask is ALREADY DELIVERED. pg_roles.rolconfig on mars_app_runtime reads statement_timeout=10000, and the role that setting sits on is the role the app actually connects as, so the limit is in force on reads and writes alike regardless of pooling mode - which was the whole point of asking for ALTER ROLE rather than a client-side SET. Nothing is left for the db lane to apply. MARS-680's coalescer commit (8155822) already writes as if this were live; it is. THE THREE DECIDE-AND-RECORD QUESTIONS, answered or bounded rather than dropped: (1) WHICH ROLE THE DSN AUTHENTICATES AS - mars_app_runtime, measured above. Not the dot-user postgres.<projectref> form; Supavisor presents the tenant-qualified username but the server-side usename is mars_app_runtime. (2) IS 10s RIGHT FOR EVERY CALLER ON THAT ROLE - unresolved and NOT closed by this row. It is a live setting nobody has stress-tested against the cron and bulk paths (rectificar-reminder/route.ts, administrar-actions.ts). It has been in force since MARS-694 without a reported cron failure, which is weak evidence, not a clearance. (3) DOES THE WEEKLY-KPI DIRECT POOL SHARE THE ROLE AND INHERIT THE LIMIT - CANNOT BE ANSWERED FROM THE DB SIDE and I am not going to guess. DATABASE_URL_DIRECT is Vercel type=sensitive; the only DB-side read is which usename shows up when that pool is actually open, and the Monday 06:00 ART weekly-kpi run is not open now. If it authenticates as mars_app_runtime it inherits 10s and a legitimately long KPI batch will be killed. Whoever owns that env var should confirm the username in it - that is a one-line read for the push lane, not a DB query.
• wi-cli-venus completed · 5w ago
  CLOSED AS ALREADY DELIVERED, not as work done today. ALTER ROLE mars_app_runtime SET statement_timeout=10000 is live in pg_roles.rolconfig and the app authenticates as that role (measured - see the unblock event). The db lane has nothing left to apply here. Deliberately NOT carried by this closure: (2) whether 10s suits the cron and bulk callers, and (3) whether the weekly-kpi DATABASE_URL_DIRECT pool shares the role and inherits the limit. (3) has no DB-side read path while that pool is closed and needs a one-line env-var read by the push lane. If either turns out wrong it is a new row, not a reopen of this one - the mechanism this row asked for exists and works.