hkc-8010 opened a new issue, #71921:
URL: https://github.com/apache/airflow/issues/71921
### Apache Airflow version
3.2.2 (and current `main`)
### What happened
Upgrading 2.11.2 -> 3.2.2 on PostgreSQL aborts in
`0049_3_0_0_remove_pickled_data_from_xcom_table`:
```
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation)
invalid input syntax for type json
DETAIL: Token "NaN" is invalid.
CONTEXT: JSON data, line 1: ..._amount_ugx\": 604441.0, \"commission_ugx\":
"NaN...
[SQL:
ALTER TABLE xcom
ALTER COLUMN value TYPE JSONB
USING CASE
WHEN value IS NOT NULL THEN CAST(CONVERT_FROM(value, 'UTF8')
AS JSONB)
ELSE NULL
END
]
```
The migration job exits 1 and retries. On the deployment where we hit this
it looped 144 times over 35 hours, each attempt rewriting the whole 39 GB xcom
table before failing, with no scheduler or API server up in between.
### What you think went wrong
The sanitize step wraps non-finite tokens in unescaped double quotes:
```sql
regexp_replace(txt, '([:,\[]\s*|^)(NaN|-?Infinity)(?=\s*[,}\]]|$)',
'\1"\2"', 'g')
```
That is only correct when the token sits in a top-level JSON document. When
a task pushes
already-serialized JSON, the stored value is a JSON string wrapping a JSON
document, so every
interior quote is backslash-escaped:
```
"[{\"recipient_country\": \"Uganda\", ..., \"commission_ugx\": NaN}, ...]"
```
The sanitizer rewrites `\"commission_ugx\": NaN}` to `\"commission_ugx\":
"NaN"}`. The injected
raw quote closes the wrapping string, the following `NaN` becomes a bare
token, and the JSONB cast
rejects it.
Worth being explicit about: the sanitizer creates the invalid JSON here. The
stored bytea is
exactly what Airflow 2's `json.dumps` writes for a NaN float, and it casts
to JSONB fine before
sanitization.
### How to reproduce
On PostgreSQL 16:
```sql
CREATE TABLE t(id int primary key, value bytea);
-- json.dumps(json.dumps({"amount": 604441.0, "commission": float("nan")}))
INSERT INTO t VALUES (1, convert_to('"{\"amount\": 604441.0, \"commission\":
NaN}"', 'UTF8'));
-- as stored, this casts fine
SELECT CAST(CONVERT_FROM(value, 'UTF8') AS JSONB) FROM t;
-- now apply the migration's sanitize step
UPDATE t SET value = convert_to(
regexp_replace(convert_from(value, 'UTF8'),
'([:,\[]\s*|^)(NaN|-?Infinity)(?=\s*[,}\]]|$)', '\1"\2"', 'g'),
'UTF8');
SELECT CAST(CONVERT_FROM(value, 'UTF8') AS JSONB) FROM t;
-- ERROR: invalid input syntax for type json
-- DETAIL: Token "NaN" is invalid.
-- CONTEXT: JSON data, line 1: "{\"amount\": 604441.0, \"commission\":
"NaN...
```
Two shapes fail the same way: the whole XCom value being the serialized
document (above), and the
serialized document sitting under a key in a dict, `{"report": "{\"amount\":
NaN}"}`. MySQL and
SQLite have the same problem in their branches of the same migration.
### Affected versions
Every release carrying the quoting sanitizer, and current `main`. #53812,
#57614, #57866 and #62686
each refined which tokens get matched, but all of them insert unescaped
quotes, and #69064 added
NUL and invalid-byte guards without touching the replacement text. So there
is no version to
upgrade to, and the only way out for an affected deployment is editing the
offending rows in the
metadata DB by hand.
### Suggested fix
Substitute the bare literal `null` instead of a quoted string. The
replacement has to be valid at
whatever nesting depth the token happens to occupy, and a quote character is
not. `null` needs no
escaping, so it is correct at every depth, and it is what most JSON encoders
emit for a non-finite
float anyway. This does change the outcome for the already-working top-level
case, from the string
`"NaN"` to `null`.
Migration 0055 (`dag_run.conf`) is not affected. It converts the
deserialized Python object and
lets `json.dumps` handle the quoting, which gets the escaping right at any
depth.
### Are you willing to submit PR?
- [x] Yes I am willing to submit a PR!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
