After switching an ads data source from daily tables to weekly tables, I found an empty table still sitting in the schema — one that was only ever CREATEd in the earliest migration, held zero rows, and was never referenced by runtime code. It even carried stale field names and unnormalized columns.

Encountered this while building AI Ops — AI-powered analytics that surfaces market trends, user behavior, and sales data to drive precise operational strategy. After this data-source switch, the write path had long since moved to the new weekly tables and a cleanup migration had already dropped the old daily table. The one thing missed was a monthly table that existed only in the baseline CREATE — it had no new writes, no corresponding DROP, and just sat dormant in the schema with its deprecated field definitions.

TL;DR​

The signature of an orphan table: only CREATEd in an early/baseline migration, zero references in current code, often with stale or unnormalized column names. Batch migrations don't touch these automatically. You have to actively list every table in the schema with information_schema.tables, compare against code references, identify the orphans, and write a DROP migration to clean them up — not just psql your way to a one-off delete.

The symptom​

A typical orphan table looks like this:

• Zero rows — the business stopped writing to it long ago;
• Zero runtime references — no SELECT/INSERT anywhere in code, only the CREATE in a migration file;
• Stale fields — column names from a previous naming convention (e.g. ad_plan_id/product_id), out of step with current standards;
• Unnormalized columns — possibly even Chinese column names that were never cleaned up.

It doesn't crash and doesn't affect production, so from a "nothing's broken online" perspective it's invisible. But the harm is implicit: it misleads newcomers into thinking it's still in use, pollutes the schema namespace, adds noise to cross-table audits, and could be read as dirty data by some mistaken SELECT *.

Root cause​

Database migrations follow a pervasive pattern: migrations are "additive".

A data-source switch typically evolves like this:

1. An early baseline migration CREATEs a batch of tables (daily, monthly);
2. Once the business runs, the write path starts depending on them;
3. Requirements change, new tables (weekly) are introduced, and writes migrate over;
4. The old daily table's writes stop, and a migration DROPs it;
5. But the monthly table (or any table that was only ever CREATEd in the baseline and never directly used by the write path) gets no corresponding DROP.

The problem is step 5: migration attention focuses on "tables in use right now" — which ones are being written, which ones queries hit. A table that "once existed but never entered the main path" is in neither the write path nor the query path, so it never triggers a DROP and becomes an orphan. This is the same family as Airflow DAG metadata lingering after deletion: "removed the entry, forgot to clean the structure" — a high-frequency failure mode in migration work.

The fix​

Core flow: list all tables → compare references → confirm empty → write a DROP migration → verify.

Step 1: list all base tables in the schema with information_schema​

-- List all base tables in a schema (exclude views)
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

information_schema.tables is a SQL-standard catalog view, portable across PostgreSQL/MySQL/SQL Server with stable fields — ideal for baking into an audit script.

Step 2: grep the codebase to confirm runtime references​

For each candidate table, search for references in the codebase, excluding migration files themselves:

# Search runtime code references, excluding the migrations directory
grep -rn "ad_product_monthly_stats" src/ --include="*.py" \
  | grep -v "migrations/"
# 0 lines of output → no runtime reference, it's a candidate

Zero references is the key evidence for an orphan. Make sure to exclude the migration directory — the CREATE in the baseline doesn't count as a "reference".

Step 3: confirm it's empty​

SELECT count(*) FROM your_schema.ad_product_monthly_stats;
-- 0 → confirmed no data, safe to clean up

Be extra careful with tables that have data: first confirm they're truly abandoned (not just recently unwritten), and back up logically if in doubt.

Step 4: write a DROP migration (not a manual delete)​

-- db-migrations/{project}/027_drop_ad_product_monthly_stats.sql
DROP TABLE IF EXISTS your_schema.ad_product_monthly_stats;

Always go through a migration file: it's version-controlled, replayed consistently across environments (dev/staging/prod), and leaves an audit trail. A one-off psql delete only works on the current machine — on another box, the table grows back.

Step 5: verify the drop​

SELECT to_regclass('your_schema.ad_product_monthly_stats');
-- Returns NULL → the table no longer exists

to_regclass() is the standard way to check whether a relation exists; NULL confirms the drop succeeded.

Batch audit: sweep same-prefix siblings at once​

After dropping one table, list all same-prefix siblings and walk through each — avoid "dropped one, missed its siblings":

-- List all tables under a prefix, run steps 2-5 on each
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
  AND table_name LIKE 'ad_%'
ORDER BY table_name;

FAQ​

How do I list all tables in a PostgreSQL database?​

Query information_schema.tables, filtering by table_schema and table_type = 'BASE TABLE' to list all base tables in a schema. It's more scriptable than psql's \dt, and because it's the SQL standard, the same query is portable across databases.

How do I find unused or orphan tables in PostgreSQL?​

List all tables with information_schema.tables, then compare against references in your codebase or query logs. Tables with zero runtime references and no writes are orphan candidates; confirm the row count with SELECT count(*), and once you've verified no data and no foreign-key dependencies, write a DROP migration to clean them up.

What's the difference between information_schema and pg_catalog?​

information_schema is the SQL-standard catalog view — portable across PostgreSQL/MySQL/SQL Server with stable fields, ideal for portable audit scripts. pg_catalog is the PostgreSQL-specific catalog, richer and more detailed (e.g. precise row-count estimates, storage details) but subject to change between versions. For portable schema audits, prefer information_schema.

When you persist a dict containing a Python set with json.dumps(data, default=str) and later read it back to test membership with in, the result is silently wrong — no exception, but the in checks are completely off.

Encountered this while building AI Ops — AI-powered analytics that surfaces market trends, user behavior, and sales data to drive precise operational strategy. In a decision-replay feature for an analysis template, I needed to serialize a "missing months" set into a snapshot and read it back to decide whether a given month was missing. After replay, months that should have been flagged "missing" were silently judged "not missing" — with zero exceptions anywhere in the chain.

TL;DR​

default=str is not a universal escape hatch. It hands set to str(), storing a literal string like "{1, 2}" in the JSON instead of an array; the type is irreversible on reload, and an in check against it degrades to substring matching, silently returning wrong results. When set is involved, the correct approach is to convert to list before serializing and rebuild with set() on read.

The symptom​

This code fully reproduces the silent failure:

import json

# A dict containing a set — say, "months that still need backfill"
data = {"missing_months": {"3", "5", "12"}}

# Serialize with default=str (the common "just don't crash" shortcut)
serialized = json.dumps(data, default=str)
print(serialized)
# {"missing_months": "{'3', '5', '12'}"}   ← a string, not an array!

# Read it back
back = json.loads(serialized)
value = back["missing_months"]
print(type(value))   # <class 'str'>   ← no longer a set

# Silent bug: you meant to test membership in the "missing" set
print("1" in value)   # True   ← 1 is NOT in {3,5,12}, but "1" is a substring of "12"!
print("3" in value)   # True   ← correct by coincidence
print("9" in value)   # False

"1" in value returns True, yet the original set {"3", "5", "12"} does not contain "1". No exception, no warning — the result is just quietly wrong. This kind of bug is especially dangerous in branches that act on the check (e.g. "is this month missing data? if so, backfill it").

Root cause​

Three layers:

Layer 1: set is not JSON serializable to begin with. JSON has only array (list) and object — no set type. A direct json.dumps({"x": {1, 2}}) raises TypeError: Object of type set is not JSON serializable.

Layer 2: default=str turns the error into silent corruption. The default parameter of json.dumps is called for objects that can't be serialized, and is expected to return a serializable value. When default=str, the object goes to str() — so a set becomes its Python literal form {'3', '5', '12'}, stored as a string in the JSON:

>>> json.dumps({"m": {"3", "5", "12"}}, default=str)
'{"m": "{\'3\', \'5\', \'12\'}"}'

The error is gone — at the cost of the type silently changing from set to str, with no signal that it happened.

Layer 3: in means different things for str vs set. This is the core of the silent bug. For set/list, x in s is a membership test; for str, x in s degrades to substring matching. The reloaded value is the string "{'3', '5', '12'}", so "1" in "{'3', '5', '12'}" tests whether the substring "1" appears — and since "12" contains "1", it returns True.

This is the same family of trap as Airflow PostgresHook silently dropping multi-statement SQL results: the most dangerous bugs don't throw — they silently return the wrong answer, leaving you no signal to investigate.

The fix​

Core principle: store only standard JSON types; rebuild set semantics on the read side.

Option 1: explicitly convert to list before serializing (recommended)​

The most direct and controllable approach — when you know where the set is, convert it to list in place:

import json

# Before serializing: set → list (a standard JSON array)
data = {"missing_months": list({"3", "5", "12"})}
serialized = json.dumps(data)
print(serialized)
# {"missing_months": ["3", "5", "12"]}   ← a proper JSON array

# Rebuild the set after reading back
back = json.loads(serialized)
months = set(back["missing_months"])
print("1" in months)   # False ✓
print("3" in months)   # True  ✓

The serialized result is a clean JSON array — portable, readable, and restorable.

Option 2: a custom default function (when data is complex)​

If the data structure is deep and you're not sure where a set might sneak in, use a default function dedicated to collection types — preserving semantics while still falling back for other non-standard types:

import json

def safe_default(obj):
    # Collection types → list, kept as a standard JSON array
    if isinstance(obj, (set, frozenset)):
        return sorted(obj)          # sort for stable, predictable output
    # Only fall back to str for types that truly can't be represented
    return str(obj)

data = {"missing_months": {"3", "5", "12"}, "created_at": some_datetime}
serialized = json.dumps(data, default=safe_default)
# {"missing_months": ["3", "5", "12"], "created_at": "..."}

back = json.loads(serialized)
months = set(back["missing_months"])
print("1" in months)   # False ✓

Compared to a blind default=str, this function handles "types you need to preserve" (collections) explicitly and only falls back to str for genuinely unrepresentable types — minimizing silent risk.

FAQ​

How do I convert a Python set to JSON?​

A set has no native JSON type, so json.dumps raises TypeError. Convert it with list(set) before serializing to store a standard JSON array, then rebuild with set() when reading it back. This avoids the error and fully restores the set semantics, across languages too.

How do I fix "Object of type set is not JSON serializable" in json.dumps?​

The root cause is that sets aren't JSON serializable. The safe fix is to convert the set to a list before dumping, or pass a default function that returns list(obj) for isinstance(obj, (set, frozenset)). Avoid default=str — it doesn't crash, but it stores the set as a string, so the type can't be restored on read.

Why does in return wrong results after serializing a set with default=str?​

default=str passes the set to str(), storing the literal string '{1, 2}' in JSON. On reload the value is a str, not a set, so x in s degrades from membership testing to substring matching — e.g. "1" in "{'3','5','12'}" returns True because "12" contains the character "1", even though the original set doesn't contain "1". The fix is to serialize as list and rebuild with set() on read.

While triggering the same DAG repeatedly via the Airflow REST API for a staged rollout check, the request came back 4xx with no dag_run_id in the body — the DAG never actually ran — yet the script treated it as success.

Encountered this while building AI Analytics — LLM-powered analytics that surfaces market trends, user behavior, and sales data for precise operations strategy. The staged rollout of the ad-decision pipeline needed to trigger the same analysis repeatedly on Airflow for comparison, and some triggers were failing silently.

TL;DR​

Airflow enforces a unique constraint on each DAG's logical_date (dag_run_id must also be unique). POSTing /dags/{dag_id}/dagRuns with a logical_date that already exists gets rejected with a 4xx, and the response body contains no dag_run_id. If you only check the HTTP status code and don't inspect the returned dag_run_id, you'll mistake the rejection for success. Fix: use a distinct logical_date (and dag_run_id) on every trigger.

Symptoms​

To run a comparison test, the same DAG was triggered repeatedly with a fixed date 2026-01-01:

$ curl -s -X POST "$AIRFLOW/api/v2/dags/my_dag/dagRuns" \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -d '{
      "dag_run_id": "manual-run-1",
      "logical_date": "2026-01-01T00:00:00Z"
    }'
# First time: returns a normal dag_run object with dag_run_id ✅

$ curl -s -X POST "$AIRFLOW/api/v2/dags/my_dag/dagRuns" \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -d '{
      "dag_run_id": "manual-run-2",
      "logical_date": "2026-01-01T00:00:00Z"   # ⚠️ same logical_date
    }'
# Second time: returns an error object, no dag_run_id ❌
{
  "detail": "...",
  "status": 400,
  "title": "Bad Request",
  "type": "https://airflow.apache.org/docs/apache-airflow/2/stable-rest-api-ref.html#/default/Error"
}

If the caller only checks "is it 2xx" and stops there, or parses the JSON without verifying that dag_run_id exists, the second failure is silently swallowed — no error in the logs, no run in the Airflow UI.

Root Cause​

Airflow uses dag_run_id as the primary key for each run and maintains uniqueness on (dag_id, logical_date) in the metadata DB's dag_run table. logical_date is the "logical time" of a run — the scheduler uses it to decide whether a given schedule slot has already executed. Once a run with some logical_date exists for a DAG, triggering again with the same value is rejected to prevent duplicate execution.

The catch is that this failure is a 4xx with an error JSON, not a connection error or a 5xx. Many scripts only do a coarse response.status_code == 200 check, or grab the JSON and read fields without verifying dag_run_id is present — so "creation rejected" reads as "creation succeeded".

Solution​

Core idea: use a distinct logical_date (and dag_run_id) on every trigger. For replay / rollout-comparison scenarios, just append a counter to the date:

# Each iteration uses a different logical_date (2026-01-01 / 02 / 03 …)
for i in 1 2 3; do
  curl -s -X POST "$AIRFLOW/api/v2/dags/my_dag/dagRuns" \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -d "{
      \"dag_run_id\": \"manual-run-$i\",
      \"logical_date\": \"2026-01-0${i}T00:00:00Z\"
    }"
done

Even better, use an incrementing timestamp so logical_date and dag_run_id never collide. More importantly: always verify the dag_run_id field in the response — treat it as the only proof the trigger actually succeeded:

import requests

def trigger_dag(dag_id: str, logical_date: str, conf: dict | None = None) -> str:
    resp = requests.post(
        f"{AIRFLOW}/api/v2/dags/{dag_id}/dagRuns",
        headers={"Authorization": f"Bearer {TOKEN}", "Content-Type": "application/json"},
        json={"dag_run_id": f"manual-{logical_date}", "logical_date": logical_date, "conf": conf or {}},
    )
    # ❌ Not enough: status-only check lets 4xx slip through as success
    # resp.raise_for_status()
    data = resp.json()
    # ✅ Correct: only count it as created if dag_run_id is present
    if "dag_run_id" not in data:
        raise RuntimeError(f"Trigger failed: {resp.status_code} {data}")
    return data["dag_run_id"]

# A distinct logical_date each time makes repeated triggers safe
for i in range(1, 4):
    trigger_dag("my_dag", f"2026-01-0{i}T00:00:00Z")

Keep dag_run_id unique too — it's the primary key, and duplicates are rejected outright. A "prefix + logical_date" convention is common: unique, and easy to spot in the UI.

FAQ​

How do you trigger a DAG with the Airflow REST API?​

POST /api/v2/dags/{dag_id}/dagRuns with a body containing at least dag_run_id and logical_date (plus an optional conf for parameters). Both must be unique within the same DAG, or Airflow returns 4xx. In code, prefer the TriggerDagRunOperator, which also generates a unique run id internally.

Why does triggering the same DAG repeatedly fail in Airflow?​

Because Airflow maintains a unique constraint on (dag_id, logical_date) in the dag_run table, and dag_run_id itself is a primary key. Duplicate logical_date or dag_run_id values are rejected with 4xx. For replays or staged comparisons, give each trigger a fresh logical_date (or incrementing timestamp).

After deleting a DAG's .py file in Airflow to retire it, the dag_id still hangs around in the Web UI list and the database; even stranger — if you clear the metadata first and delete the file second, the just-cleared rows "come back to life."

Encountered this while building AI Ops — an LLM-powered analytics pipeline where retiring an old report DAG required cleaning its metadata too, otherwise the UI list and scheduled scans stayed polluted by residual rows.

TL;DR​

Airflow's dag-processor periodically scans the DAG folder and re-registers DAGs, and airflow dags reserialize doesn't purge "file-already-deleted" orphan rows — so just deleting the .py file won't make the dag_id vanish from the UI or DB. Conversely, clearing metadata before deleting the file lets the processor re-register the cleared rows on its next scan ("revival"). Correct order: ①delete the file first so the processor stops registering → ②SQL DELETE in foreign-key order → ③run airflow dags reserialize to verify.

Symptoms​

Retiring the shop_report_aggregation DAG — after deleting its .py file:

$ ls /opt/airflow/project/airflow_dags/shop_report_aggregation.py
ls: cannot access '.../shop_report_aggregation.py': No such file or directory

$ # but it's still in the database
$ docker exec cclhub-db psql -U airflow -d airflow -c \
    "SELECT dag_id, is_paused, is_active FROM dag WHERE dag_id='shop_report_aggregation';"
       dag_id            | is_paused | is_active
--------------------------+-----------+-----------
 shop_report_aggregation | f         | t          ← still there

It's not just the dag table — the matching rows in serialized_dag, dag_code, and dag_version are all still there, so the Web UI keeps showing this "deleted" DAG.

Worse is the reverse order — clear metadata first, delete file second:

T0  DELETE FROM dag WHERE dag_id='shop_report_aggregation';   ← cleared
T1  (.py file not deleted yet)
T2  dag-processor scan fires; file exists, dag table has no row → re-registers
T3  SELECT ... FROM dag WHERE dag_id='shop_report_aggregation';   ← it's back (revival)

Root Cause​

Two mechanisms stack up:

1. dag-processor scans and re-registers periodically. Airflow's dag-processor (part of the Scheduler) scans dags_folder on processor_poll_interval (default ~5 min), parses each .py file, and upserts into the metadata tables (dag, serialized_dag, dag_version). As long as the file exists, the next scan rewrites those rows. That's the direct source of "revival" — you clear the row, the file is still there, and the processor re-registers it as a new DAG.

2. reserialize ignores "file-gone" orphan rows. airflow dags reserialize re-serializes existing DAG files and refreshes serialized_dag; it does not delete orphan dag rows whose files have vanished. And airflow dags cleanup only purges expired dag_run history by default — it also leaves the dag / serialized_dag / dag_code / dag_version metadata tables alone. So after you delete the file, the metadata rows become orphans nobody cleans.

┌─ dag-processor ──────────────────────────────┐
│  scans dags_folder                            │
│  ├─ file present → upsert dag / serialized... │  ← source of revival
│  └─ file absent  → skip, no row deletion      │  ← orphan residue
└───────────────────────────────────────────────┘

Conclusion: to actually remove the metadata, you must give the processor no file to register (delete the file first), then manually clean the residual rows.

Solution​

Step 1: Delete the file first​

Make the .py file disappear from the DAG folder so dag-processor stops registering it.

# In production this is usually synced to the volume-mounted DAG folder
# /opt/airflow/project/airflow_dags/ via git pull
git pull   # removes shop_report_aggregation.py from the repo and the folder

# or delete directly (after confirming nothing depends on it)
rm /opt/airflow/project/airflow_dags/shop_report_aggregation.py

Step 2: Clean metadata in foreign-key order​

DELETE in foreign-key dependency order to avoid constraint violations. Deleting dag_run CASCADEs to task_instance:

BEGIN;

-- 1. run history (CASCADEs to task_instance)
DELETE FROM dag_run      WHERE dag_id = 'shop_report_aggregation';

-- 2. serialized DAG
DELETE FROM serialized_dag WHERE dag_id = 'shop_report_aggregation';

-- 3. version
DELETE FROM dag_version  WHERE dag_id = 'shop_report_aggregation';

-- 4. dag main table
DELETE FROM dag          WHERE dag_id = 'shop_report_aggregation';

-- 5. dag_code is keyed by source hash; multiple DAGs may share the same code;
--    only delete hashes no longer referenced by any serialized_dag
DELETE FROM dag_code
WHERE dag_hash NOT IN (SELECT dag_hash FROM serialized_dag);

COMMIT;

Step 3: Verify​

airflow dags reserialize

# confirm the dag row is not rebuilt
docker exec cclhub-db psql -U airflow -d airflow -c \
  "SELECT count(*) FROM dag WHERE dag_id='shop_report_aggregation';"
#  count
# -------
#      0   ✅

After reserialize, dag / serialized_dag / dag_code / dag_version are all 0 for that dag_id, and the next processor scan doesn't rebuild them — the cleanup is stable.

As a side note, on the same pipeline, pandas NaN crashing XCom serialization is another pitfall worth bookmarking.

Notes​

FAQ​

Why does a DAG still show in Airflow after deleting its .py file?​

Deleting the file doesn't clean the database. Rows in dag / serialized_dag / dag_code / dag_version still exist, and the Web UI reads those tables to render the list, so the deleted DAG keeps showing. Airflow has no built-in command to purge these orphan rows automatically; you must SQL DELETE them manually in foreign-key order.

How do I completely delete an Airflow DAG and all its metadata?​

Three steps: 1) delete the .py file so dag-processor stops registering it; 2) SQL DELETE in foreign-key order (dag_run → serialized_dag → dag_version → dag → orphan dag_code); 3) run airflow dags reserialize, then query the dag table to confirm the dag_id row count stays at 0 and isn't rebuilt.

What's the correct order to clean Airflow DAG metadata, and why not clear metadata before deleting the file?​

Delete the file first, then clean metadata. If you reverse it, the .py file still exists, so dag-processor re-registers the cleared dag row on its next scan — the metadata "comes back to life." Only by making the file vanish first (so the processor has nothing to register) and then cleaning the residual rows can you fully retire the DAG.

When an Airflow task calls ti.xcom_push() to pass pandas-processed results downstream, the task crashes outright — ValueError: Out of range float values are not JSON compliant: nan — and the app's custom logs table shows no error at all.

Encountered this while building AI Ops — an LLM-powered analytics pipeline where Airflow DAGs pull SQL data, process it with pandas, and pass results between tasks via XCom.

TL;DR​

XCom serializes with JSON under the hood, and Airflow calls json.dumps(..., allow_nan=False) to follow the JSON spec strictly — which has no NaN / Infinity whatsoever. The moment a float NaN (converted from SQL NULL by pandas) enters the data passed to xcom_push, serialization throws ValueError. Fix: recursively walk the data before push and convert NaN / ±Inf to None (JSON null).

Symptoms​

A quarterly report DAG failed, but the symptom was baffling — in the app's custom logs table, steps 1–4 for that trace were all fine, "analysis done" even logged twice (a retry), then it cut off: step 5 missing, with no error row at all:

trace=91c126c3
├─ step 1  SQL fetch            ✅
├─ step 2  pandas process       ✅
├─ step 3  rule judge           ✅
├─ step 4  LLM analysis done    ✅  ← retried after this
└─ step 5  XCom push result     ❌  ← missing, no error record

The real traceback was only in the Airflow task log:

# inside container /opt/airflow/logs/dag_id=ai_analysis_v2/run_id=.../task_id=analyze_results/attempt=N.log
ValueError: Out of range float values are not JSON compliant: nan
  File ".../ai_analysis_tasks.py", line 142, in analyze_results
    ti.xcom_push(key='sql_metadata', value=result)

The crash landed exactly on ti.xcom_push — the instant the task pushed results into XCom.

Root Cause​

Three layers stack up, all required:

1. The JSON spec has no NaN / Infinity. RFC 8259 only allows finite numeric literals. Python's json.dumps will happily emit bare NaN and Infinity by default, but those are Python-specific extensions, not valid JSON — any strict parser (Airflow included) rejects them.

2. Airflow XCom serializes with allow_nan=False. XCom's default JSON serializer explicitly disables NaN tolerance, so encountering NaN throws ValueError: Out of range float values are not JSON compliant instead of silently emitting invalid JSON.

3. pandas reads SQL NULL as NaN. pandas.read_sql returns float('nan') for SQL NULL columns. Once such a column flows through computation and to_dict('records') into the result object, NaN hitches a ride into xcom_push:

import pandas as pd

# A SQL NULL cell → pandas reads it as NaN
df = pd.DataFrame({"ad_roi": [1.2, None, 0.8]})
records = df.to_dict("records")
# [{'ad_roi': 1.2}, {'ad_roi': nan}, {'ad_roi': 0.8}]   ← nan slipped in

# downstream task crashes on push
ti.xcom_push(key="result", value=records)
# ValueError: Out of range float values are not JSON compliant: nan

This stayed latent for a long time because the data usually had values in those columns; it only surfaced when a client had zero ad spend for an entire quarter and ad_roi came back NULL across the board — the first time NaN entered the XCom path at scale.

Why no error in the logs table? Because the crash happens during XCom serialization, outside the task function's try/except — the exception bubbles straight up to the Airflow scheduler and only lands in Airflow's own task log. The app's custom logs table catch never gets a chance to record it. That's what makes this failure so confusing: it looks "silent."

Solution​

Scrub all NaN / ±Inf from the data before it enters XCom.

1. Write a pure recursive cleaner​

import math

def json_safe_value(obj):
    """
    Recursively convert NaN / +Inf / -Inf to None so the data is
    strictly JSON-serializable. Handles dict / list / tuple / scalar;
    unknown types pass through unchanged.
    """
    if isinstance(obj, float):
        if math.isnan(obj) or math.isinf(obj):
            return None
        return obj
    if isinstance(obj, dict):
        return {k: json_safe_value(v) for k, v in obj.items()}
    if isinstance(obj, (list, tuple)):
        return [json_safe_value(v) for v in obj]
    return obj

Why not df.fillna(None)? Because fillna(None) on numeric columns is unstable across pandas versions and dtypes — sometimes it coerces the dtype instead of nulling values. It also only handles DataFrames, not floats already nested inside dicts/lists after to_dict. Recursive cleaning at the "data is now native Python structures" layer is the most robust fallback.

2. Centralize the guard before push​

The worry-free approach is to hang the cleanup on the single chokepoint all xcom_push calls go through, rather than remembering to call it at every push site:

def push_safe(ti, key, value):
    """Clean NaN/Inf before XCom push to prevent serialization crashes."""
    ti.xcom_push(key=key, value=json_safe_value(value))

# inside the task
push_safe(ti, "sql_metadata", result)
push_safe(ti, "processor_output", processor_result)

3. Fix the "silent failure" observability gap​

Fixing serialization alone isn't enough — the gap where exceptions outside try/except never reach the app's logs table must be closed too. Attach a failure decorator that logs the top-level exception to your table before re-raising:

import functools
import logging

logger = logging.getLogger(__name__)

def log_task_failure(fn):
    @functools.wraps(fn)
    def wrapper(*args, **kwargs):
        try:
            return fn(*args, **kwargs)
        except Exception:
            logger.error("task %s failed", fn.__name__, exc_info=True)
            # write the traceback into the app's custom logs table here
            raise
    return wrapper

@log_task_failure
def analyze_results(**context):
    ...

Now if another exception slips outside a catch, the app's logs table still gets an error row — no more "silent failure."

After the fix, rerunning the same conf: DAG all green, DB write success, and the formerly-NaN ad_roi lands as null in the database; downstream is happy.

On the same Airflow analytics pipeline, this isn't the only way data silently misbehaves — PostgresHook silently dropping multi-statement SQL results is another classic.

Notes​

FAQ​

How to fix Airflow "Out of range float values are not JSON compliant"?​

XCom serializes with json.dumps(allow_nan=False) and ran into NaN / Infinity, which the JSON spec does not allow. The usual root cause is pandas reading a SQL NULL into float('nan') that then flows into xcom_push. Fix it by recursively converting NaN / ±Inf to None (JSON null) before push, centralized in a pure json_safe_value helper.

Why does my Airflow task fail but my custom logs table has no error?​

If the exception happens during XCom serialization, outside the task function's try/except, it only bubbles up to the Airflow scheduler and lands in the Airflow task log (inside the container at /opt/airflow/logs/). The app's custom logs table catch never sees it, so it looks like a "silent failure." To triage, read the Airflow task log traceback directly instead of only checking app logs.

Can Airflow XCom store pandas NaN directly?​

No. XCom defaults to JSON serialization, and the JSON spec only has finite numbers — no NaN / Infinity. The right fix is to convert NaN to None (JSON null) before push. Switching to binary object serialization sidesteps the type limit but produces unreadable, non-portable values with deserialization security risk; not recommended for production.

While building a server health-check endpoint, systemctl is-active redis-server returned inactive — yet Redis was happily serving requests.

Encountered this while building AI Analytics — LLM-powered analytics that surfaces market trends, user behavior, and sales data for precise operations strategy. The monitoring dashboard needs to reflect the real status of every infrastructure component, and Redis was reporting a false negative from the start.

TL;DR​

systemctl is-active only works for services that systemd manages through unit files. If Redis (or any service) runs as a bare process with no .service unit, systemctl can never see its true state and returns inactive (exit code 3). Reliable probing has to bypass systemctl and ask the process (pgrep) or the port (ss) directly.

Symptoms​

The probe called systemctl is-active for both Nginx and Redis:

$ systemctl is-active nginx
active            # ✅ fine

$ systemctl is-active redis-server
inactive          # ❌ looks like Redis is down
$ echo $?
3                 # exit code 3 = inactive

But every business endpoint was reading and writing Redis fine — only /api/v1/server-monitor/status reported Redis as down.

Root Cause​

systemd is a process manager, and it only knows about units it started and owns. When you run systemctl start redis-server (or let systemd read redis-server.service), systemd records that unit's state and is-active can return active.

On this server, Redis was started as a bare process — a direct redis-server invocation, or launched via nohup / a custom script, never registered as a systemd service. So:

• There is no redis-server.service in systemd's unit list at all;
• systemctl is-active redis-server can't find the unit, treats it as inactive, and returns exit 3;
• Nginx, by contrast, is a standard systemd service, so is-active correctly reports active.

In one line: is-active reflects systemd's view, not the system process view. "The process is running" and "systemd knows it's running" are two different things.

Solution​

Switch the probe to a "systemctl first → process/port fallback" chain. If systemctl hits, use it; otherwise confirm the process is actually alive with pgrep or ss. Use execFileSync (no shell, args passed as an array) to avoid command injection:

import { execFileSync } from "node:child_process";

/** Run a single command safely (no shell); unify non-zero exit to null */
function sh(cmd: string, args: string[]): string | null {
  try {
    return execFileSync(cmd, args, {
      stdio: ["ignore", "pipe", "ignore"],
      timeout: 2000,
    })
      .toString()
      .trim();
  } catch {
    return null; // inactive / process missing / timeout all land here
  }
}

/**
 * Probe whether a service is up: systemctl first, bare-process fallback.
 * @param unit   systemd unit name (e.g. "nginx")
 * @param proc   process name (e.g. "redis") for the pgrep fallback
 * @param port   listening port (e.g. 6379) for the ss fallback
 */
function isServiceUp(unit: string, proc?: string, port?: number): boolean {
  // 1. Try systemctl first (standard systemd services)
  const st = sh("systemctl", ["is-active", unit]);
  if (st && st !== "inactive" && st !== "unknown") {
    return true; // active, or activating/reloading etc.
  }

  // 2. Fallback A: find a PID by process name
  if (proc && sh("pgrep", ["-f", proc])) return true;

  // 3. Fallback B: confirm a listening port
  if (port) {
    const listening = sh("ss", ["-lnt"]);
    if (listening && listening.includes(`:${port} `)) return true;
  }

  return false;
}

// Nginx: standard systemd service, systemctl hits directly
const nginxUp = isServiceUp("nginx");

// Redis: may be a bare process — pass process name + port as fallback
const redisUp = isServiceUp("redis-server", "redis", 6379);

The most robust final check at the application layer is to let the service answer for itself — Redis's PING, PostgreSQL's SELECT 1, an HTTP health endpoint. A listening port only proves "the process started", not "the service is ready", so on critical paths add one more application-level probe:

$ redis-cli ping
PONG    # process alive + responsive = truly up

FAQ​

Why does systemctl is-active show inactive when the process is actually running?​

systemctl only queries services that systemd manages through unit files. If the process was started directly or with nohup as a bare process with no .service unit, systemd neither knows about it nor tracks it, so is-active can only return inactive (exit 3). That's a blind spot in systemd's view, not the process being down.

How do you reliably check whether a process is running?​

Don't rely on systemctl alone. Use pgrep <name> to find the PID, or ss -lntp | grep <port> to confirm a listening port — these inspect the system process table / network stack, independent of systemd management. For critical services, add an application-level probe (e.g. redis-cli ping) to verify both that the process exists and that it responds.

When you validate LLM tool_call / function call output with Zod, the model occasionally emits an extra field — say you only defined amount / category, and it also fills in note — and the whole validation fails, the action is silently dropped, and the user just gets "not recognized." In reality an entire tool_call was whole-rejected.

Encountered this while building Life — a natural-language bookkeeping and wellness assistant where you just talk to record entries and the AI extracts amount, category, and account. When a user said "delete that coffee from yesterday," the model slipped an extra note: "coffee" into the delete locator, trying to locate by remark.

TL;DR​

Zod's .strict() means "this object must not contain any unknown keys — one extra throws." That constraint fits validating clients you fully control, but LLM function call output is model-generated and inherently uncontrollable — it fills in fields it "thinks should be there," especially when multiple tools share similar schemas. One irrelevant field kills the whole tool_call, validation returns null, and the action is silently lost. Fix: drop .strict() and use Zod's default strip (silently removes unknown keys) for tolerance, paired with safeParse as a fallback.

Symptoms​

The locator schema for delete/update defines a few known fields, tightened with .strict():

import { z } from "zod";

// ❌ dangerous: with .strict()
const LocatorSchema = z.object({
  date: z.string().optional(),
  category: z.string().optional(),
  noteContains: z.string().optional(),
}).strict();   // ← any unknown key throws

// parse the LLM's tool_call arguments
function parseToolCall(raw: unknown) {
  const parsed = LocatorSchema.safeParse(raw);
  if (!parsed.success) {
    return null;   // ← the whole tool_call is dropped
  }
  return parsed.data;
}

The user says "delete that coffee from yesterday," and the model produces a reasonable but extra-fielded output:

{
  "date": "yesterday",
  "noteContains": "coffee",
  "note": "coffee"
}

The model filled both noteContains (in schema) and note (out of schema, which it thought should exist). .strict() rejects the unknown key note outright, parseToolCall returns null, and the delete action is silently dropped — the user gets "not recognized" when it was actually a whole-reject.

Root Cause​

.strict() changes Zod's policy on unknown keys, and LLM output naturally carries unknown keys.

Zod z.object() has three policies for unknown keys:

.strict() is designed for "contract strictness" — the server defines which fields exist, the client should supply only those, and anything extra is a breach. That logic holds for traditional APIs because the client is developer-written and can be held to the contract.

But LLM function calling flips the premise:

1. The output comes from model generation, not a developer-written client. The model guesses what to fill based on the schema's description and examples; schemas reused across domains (e.g. a locator shared by budget / mood / todo) confuse it further, so it fills in fields it "thinks should be there."
2. Wrong fields are the norm, not an exception. The model occasionally emitting an extra note or omitting an optional field is expected behavior in LLM apps and shouldn't be punished by failing the whole call.
3. The failure is silently swallowed. After safeParse fails and returns null, the upstream can only vaguely say "not recognized," while the real cause (an unknown key) sits unseen in parsed.error.

LLM output { date, noteContains, note }
                        │
                        ▼
         .strict() hits unknown key "note"
                        │
                        ▼
           safeParse → { success: false }
                        │
                        ▼
          parseToolCall returns null (action dropped)
                        │
                        ▼
       user gets "not recognized" (actually a whole-reject)

Solution​

1. Drop .strict(), use default strip for tolerance​

// ✅ recommended: no .strict(), Zod defaults to stripping unknown keys
const LocatorSchema = z.object({
  date: z.string().optional(),
  category: z.string().optional(),
  noteContains: z.string().optional(),
});
// the extra "note" is silently removed; known fields parse normally

After dropping .strict(), "delete that coffee from yesterday" parses cleanly into { date, noteContains }; the extra note is stripped and the delete action runs correctly.

2. If unknown keys are useful, keep them with .passthrough()​

When the extra field actually carries semantics you want to use (e.g. the model filled note to express "locate by remark"), don't drop it — keep it and decide how to consume it:

const LocatorSchema = z.object({
  date: z.string().optional(),
  category: z.string().optional(),
  noteContains: z.string().optional(),
}).passthrough();   // keep unknown keys; parsed.data.note is still readable

Even better, promote it to a known field — if the model keeps filling some unknown key, the schema is missing a capability slot, so add it (here noteContains was added after absorbing the "locate by remark" need).

3. Make failures observable — don't silently return null​

Regardless of policy, when safeParse fails, log the specific error instead of swallowing it into null:

function parseToolCall(raw: unknown) {
  const parsed = LocatorSchema.safeParse(raw);
  if (!parsed.success) {
    // log Zod's concrete error (which key, what problem) for triage
    logger.warn(
      { raw, issues: parsed.error.issues },
      "locator parse failed"
    );
    return null;
  }
  return parsed.data;
}

Now when something goes wrong, the log has the full issues (including the unknown-key path), instead of an unactionable "not recognized."

After the fix, "delete that coffee from yesterday" → delete_record { locator: { noteContains: "coffee" } } parses correctly with no silent drop.

Notes​

FAQ​

Why does Zod .strict() make LLM output validation fail?​

.strict() requires an object to have no unknown keys — one extra throws. LLM function call output is model-generated, guessing from the schema description, and routinely includes fields it thinks should be there (especially with cross-domain schemas). The moment an unknown key appears, .strict() fails the entire validation and drops the whole tool_call.

Should I use strict when validating LLM function calling output with Zod?​

Not recommended. .strict() suits validating clients you fully control (developer-written code can be held to a contract), but LLM output is uncontrollable and over/under-filling is the norm. Drop .strict() and use Zod's default strip (silently removes unknown keys) for better tolerance; if unknown keys carry semantics you want, use .passthrough() to keep them, or promote them to known fields.

Does Zod strip or throw on unknown fields by default?​

Default is strip — it silently removes unknown keys without error. .strict() makes it throw on unknown keys; .passthrough() keeps them as-is. For uncontrollable output like LLM generations, prefer default strip or passthrough over .strict(), which kills the whole payload over a single irrelevant field.

Wanting to "just pass the exception up unchanged" from a catch block, I reflexively wrote throw; — the bare rethrow I was used to in C# — and tsx/esbuild immediately failed to transform it: Unexpected ";".

Encountered this while building AI Analytics — an LLM-powered analytics pipeline that surfaces market trends, user behavior, and sales data for precise operations.

TL;DR​

JavaScript has no bare rethrow syntax. throw; (a bare throw) is a compile-time SyntaxError in all three toolchains: Node, tsc, and esbuild. To rethrow a caught exception you must throw e (the catch block needs a binding); to throw a new one, throw new Error(...).

The symptom​

The same throw; produces differently-worded errors across toolchains, but all of them are syntax errors (not runtime errors):

try {
  something();
} catch {
  throw;   // ← bare rethrow
}

The misleading one is esbuild's Unexpected ";" — it's tempting to read as "esbuild/tsx doesn't support some newer syntax." But run the same snippet through native Node and you get the identical SyntaxError. This isn't a tool limitation; the language itself has no such form.

Root cause​

The ECMAScript throw statement mandates an expression:

ThrowStatement : throw Expression ;

That is, throw must be followed by a value (throw err, throw new Error(), throw "fail") — the slot before the semicolon cannot be empty. JavaScript has no "bare throw = rethrow the current exception" semantics, which is the key difference from C# / Java / Python:

One common confusion: ES2019 added optional catch binding (catch {} may omit the parameter), but that is orthogonal to bare throw. Even with a binding present, throw; still errors —

try { f(); } catch (e) { throw; }   // still a SyntaxError; e is NOT auto-fed to throw

Confirmed in tsx as Unexpected ";". The expression after throw cannot be omitted; there are no exceptions.

The fix​

Pick the form that matches your intent:

// 1. Rethrow the original exception — the most common need
try {
  doWork();
} catch (e) {
  log(e);
  throw e;            // ✅ include e
}

// 2. Wrap in a new exception
try {
  doWork();
} catch (e) {
  throw new Error(`failed: ${e.message}`);   // ✅ throw + expression
}

// 3. With ES2019 catch {} (no parameter), there is nothing to rethrow — throw new
try {
  doWork();
} catch {
  throw new Error("doWork failed");   // ✅ throw; here would be wrong
}

A minimal runnable repro and fix — run it directly with tsx:

function risky(): void {
  throw new Error("origin");
}

function rethrowOptional(): void {
  try {
    risky();
  } catch (e) {          // ← must receive e
    console.log("caught, rethrowing");
    throw e;             // ← not throw;
  }
}

try {
  rethrowOptional();
} catch (e) {
  console.log("recovered:", (e as Error).message);   // origin
}

On the call stack: throw e reuses the same error object, whose .stack was captured at new Error time; rethrow does not overwrite it. Only throw new Error(...) generates a fresh stack from the current throw site. So "does rethrow lose the stack?" — no, as long as you don't construct a new error.

Another common exception-handling pitfall is a catch block that swallows the error entirely, surfacing as a silent failure — see Python task marked failed but no error? try/except swallowed it. Worth watching across every language.

Caveats​

FAQ​

How do you rethrow a caught exception in JavaScript?​

Use throw e, and catch must take a binding: catch (e) { ...; throw e; }. JavaScript has no bare rethrow — a standalone throw; is a SyntaxError that Node, tsc, and esbuild all reject at compile time. It is not a limitation of any single tool.

Does rethrowing an exception in JavaScript preserve the original stack?​

Yes. throw e reuses the same error object, whose .stack was captured when the error was constructed with new Error; rethrow neither overwrites nor resets it. Only throw new Error(...) generates a fresh stack from the current throw site — so if you want the earliest origin during debugging, use throw e.

How do you correctly rethrow inside a JavaScript try/catch?​

catch must receive the error and throw it back: try { ... } catch (e) { log(e); throw e; }. With ES2019's catch {} (parameter omitted) there is no variable to throw, so you can only throw new Error(...). Either way, throw must be followed by an expression — throw; is always illegal.

While prefixing vector collections per tenant with {tenant_id}_{collection}, the very first request bounced straight back from Milvus — invalid collection name: the first character ... must be an underscore or letter — and the endpoint returned 500.

Encountered this while building AI Customer Service — 24/7 AI support that answers product usage questions with instant guidance and best practices.

TL;DR​

Milvus strictly validates collection names: the first character must be a letter or underscore, only [a-zA-Z0-9_] are allowed (no hyphens), and length ≤ 255 — otherwise it raises invalid collection name (error code 1100). A UUID typically starts with a digit and always contains hyphens -, tripping both rules, so you cannot concat a tenant_id UUID into a collection name for isolation. Use the original name plus a tenant field filter instead.

The symptom​

A query endpoint with collection=system_product_help returned 500, with a single line in the rag-service log:

pymilvus.exceptions.MilvusException: code=1100,
Invalid collection name: 00000000-0000-0000-0000-000000000001_system_product_help.
the first character of a collection name must be an underscore or letter

The strange part: another endpoint with the same parameter (/query-logs) returned 200 — because it only reads PostgreSQL and never touches Milvus. Only paths that actually call Milvus has_collection trigger the validation.

Root cause​

The code built the collection name with f"{tenant_id}_{collection}", yielding e.g. 00000000-0000-0000-0000-000000000001_system_product_help. This name breaks two rules at once:

00000000-0000-0000-0000-000000000001_system_product_help
^              ^ ^
│              │ └─ underscore is fine here, but...
│              └─── hyphen `-` is illegal
└────────────────── first char is digit `0` (must be letter/underscore)

Milvus's collection name rules (source nameutil.go, regex ^[a-zA-Z_][a-zA-Z0-9_]*$, length ≤ 255):

A UUID almost always violates this: the standard 8-4-4-4-12 form carries 4 hyphens, and the first segment usually starts with a digit. Prefixing a collection name with such a token gets every has_collection / describe_collection / create call rejected server-side with code 1100.

Worse: because the concatenated name was never valid, the supposed "per-tenant prefix isolation" never actually worked — the collections that really exist in the database all use the un-prefixed original names. The concat logic was systematically disconnected from the real data; an assumption baked into code that no one ever verified.

The fix​

Don't put tenant_id in the collection name. Always use the original name; let a regular field handle tenant isolation:

from pymilvus import MilvusClient

client = MilvusClient(uri="http://localhost:19530")

# ❌ Wrong: UUID prefix — starts with a digit + contains hyphens → code 1100
tenant_id = "00000000-0000-0000-0000-000000000001"
bad_name = f"{tenant_id}_system_product_help"   # illegal

# ✅ Right: collection keeps its original name; tenant_id is a schema field
client.create_collection(
    collection_name="system_product_help",        # legal, stable
    schema=client.create_schema(auto_id=True, enable_dynamic_field=False),
)
# Filter by tenant_id at write and query time, instead of renaming the collection
client.insert(
    collection_name="system_product_help",
    data=[{"tenant_id": tenant_id, "text": "...", "vector": [...]}],
)

If you genuinely need "a readable prefix" for multi-tenant or environment isolation, convert any arbitrary string into a safe slug before concatenating:

import re

def safe_slug(raw: str) -> str:
    # Replace anything outside [a-zA-Z0-9_] with underscore; prefix if first char is illegal
    s = re.sub(r"[^a-zA-Z0-9_]", "_", raw)
    if not re.match(r"^[a-zA-Z_]", s):
        s = "_" + s
    return s[:255]   # keep within the length cap

name = f"{safe_slug(tenant_id)}_system_product_help"   # legal

When debugging a 500 like this, first scan the service logs (PM2 or equivalent) for MilvusException — the error code and the "first character must be ..." hint pinpoint an illegal name almost immediately, so you don't need to dig into business logic.

As an aside, services that depend on Milvus have their own gotcha: containers without a restart policy take the whole RAG pipeline down after a crash — see Docker Compose service won't come back? Check the restart policy. On the query side, watch out for RRF scores being incompatible with the similarity threshold in hybrid search.

Caveats​

FAQ​

What are the Milvus collection name naming rules?​

The first character must be a letter or underscore; the remaining characters allow only letters, digits, and underscores ([a-zA-Z0-9_]). Hyphens and spaces are forbidden, and the maximum length is 255 characters. Milvus enforces this server-side with a regex; violations raise invalid collection name (error code 1100), failing both creation and lookup. snake_case is the safe choice.

What is the maximum length of a Milvus collection name?​

255 characters. Anything longer is rejected with invalid collection name (error code 1100). Real names rarely approach this limit — what usually pushes you over is concatenating long UUIDs or multi-segment paths into the name, which is itself a sign you shouldn't be putting that dynamic string in the collection name at all.

Why can't a UUID be used as a Milvus collection name prefix?​

The standard UUID form usually starts with a digit (violating "first char must be a letter/underscore") and always contains four hyphens - (not in the allowed charset) — both break the rules. Using tenant_id as a collection-name prefix for isolation is a common misuse: not only is the name illegal, it also makes the collection count balloon with tenants. The right approach is to put tenant_id in a regular field or partition key and keep the collection name stable.

Right after tightening a table's unique key — dropping a column that no longer discriminated between rows — every previously working UPSERT immediately failed in bulk with there is no unique or exclusion constraint matching the ON CONFLICT specification.

Encountered this while building AI Analytics — an LLM-powered analytics pipeline that surfaces market trends, user behavior, and sales data for precise operations.

TL;DR​

PostgreSQL's ON CONFLICT (cols) requires cols to exactly match an existing unique constraint or unique index (same columns, same order — otherwise SQL state 42P10). The moment you ALTER the unique key, every INSERT ... ON CONFLICT that references it must be updated; and once the migration lands, the write side must deploy immediately, because the in-between window keeps erroring.

The symptom​

As soon as the unique-key change went live, the scheduled import job failed across the board, with only this line in the write log:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
SQL state: 42P10

Zero rows written to the business table, while plain SELECTs against the same table worked fine — the failure was isolated to the ON CONFLICT write path.

Root cause​

The column set you pass to ON CONFLICT (cols) is the arbiter. PostgreSQL requires it to exactly match some UNIQUE constraint or unique index on the table:

• the set of columns must be the same;
• the order of columns must be the same;
• for a partial unique index (one with a WHERE), ON CONFLICT must carry the same WHERE.

When nothing matches, PostgreSQL has no index to decide what "conflict" means, and raises 42P10.

The classic trigger is shrinking a unique key. The original key had 3 columns; you realize one of them (say audience) has 4 distinct values whose metric rows are 100% identical — pure redundancy — so you drop it down to 2 columns. That's the right optimization, but the old INSERT still says ON CONFLICT (c1, c2, c3) while only (c1, c2) remains as a unique constraint. The arbiter has no landing spot, and the statement errors out.

old unique key: UNIQUE (store_id, metric_key, audience)
new unique key: UNIQUE (store_id, metric_key)

old INSERT: ON CONFLICT (store_id, metric_key, audience)   ← no match

The fix​

Here is a minimal reproduction — create, trigger, and fix in one go, runnable directly in psql:

-- 1. A table with a 3-column unique key
CREATE TABLE daily_metric (
    store_id   TEXT NOT NULL,
    metric_key TEXT NOT NULL,
    audience   TEXT NOT NULL,
    value      NUMERIC,
    CONSTRAINT daily_metric_unique UNIQUE (store_id, metric_key, audience)
);

-- 2. Old UPSERT: ON CONFLICT includes audience
INSERT INTO daily_metric (store_id, metric_key, audience, value)
VALUES ('s1', 'revenue', 'visitor', 100)
ON CONFLICT (store_id, metric_key, audience)
DO UPDATE SET value = EXCLUDED.value;

-- 3. Shrink the unique key: drop audience
ALTER TABLE daily_metric
    DROP CONSTRAINT daily_metric_unique,
    ADD  CONSTRAINT daily_metric_unique_new UNIQUE (store_id, metric_key);

-- 4. Re-run the INSERT from step 2 — it now errors ↓
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

The fix is to shrink the INSERT's ON CONFLICT columns to match the 2-column key. Since audience no longer discriminates, pin it to a literal on the write side so incoming parameters can't synthesize extra rows:

INSERT INTO daily_metric (store_id, metric_key, audience, value)
VALUES ('s1', 'revenue', 'visitor', 100)
ON CONFLICT (store_id, metric_key)         -- ← shrunk to match
DO UPDATE SET value = EXCLUDED.value;

The part that actually bites is the deployment order, not the SQL itself:

1. Ship the migration first (DROP old constraint + ADD new constraint);
2. Immediately ship the write-side code (the INSERT's ON CONFLICT becomes 2 columns);
3. Leave no gap between the two — old code against the new schema raises 42P10, and new code against the old schema raises 42P10 just the same (no 2-column unique constraint exists yet).

If you use an ORM like Drizzle, an ON CONFLICT column list baked into a sql template is easy to forget when the schema changes — the cost of schema/write-side drift shows up in another Drizzle + PostgreSQL pitfall too.

Caveats​

FAQ​

Does PostgreSQL ON CONFLICT require a unique constraint?​

Only when you name columns. ON CONFLICT (cols) must match an existing UNIQUE constraint or unique index exactly, or you get 42P10. If you just want "skip on any conflict" without caring which constraint fired, use ON CONFLICT DO NOTHING without columns — it needs no specific index.

Can PostgreSQL ON CONFLICT target multiple unique constraints?​

No. A single INSERT's ON CONFLICT can name only one arbiter constraint (one column set, or one index name). A table may have multiple unique keys, but a single statement picks exactly one for conflict detection. To handle different unique keys differently, either split into multiple writes or query first in the application layer before choosing INSERT vs UPDATE.

How to fix there is no unique or exclusion constraint matching the ON CONFLICT specification?​

That is error code 42P10: the ON CONFLICT column set has no matching unique index on the table. Check in order: a UNIQUE constraint covers those columns, the columns and their order match exactly, and any INSERT was updated after a recent unique-key change. If the arbiter is a partial unique index with a WHERE, add the same WHERE clause to ON CONFLICT.