pg.ddx.io psycopg/psycopg GitHub issues and pull requests
help / color / mirror / Atom feed[psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
10+ messages / 2 participants
[nested] [flat]
* [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
@ 2026-11-19 17:37 "anish-yen (@anish-yen)" <noreply+anish-yen@github.com>
0 siblings, 0 replies; 10+ messages in thread
From: anish-yen (@anish-yen) @ 2026-11-19 17:37 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
Fix the `json` and `jsonb` loaders to decode server data using the connection encoding instead of assuming UTF-8.
When `client_encoding` is not UTF-8 (e.g. a `LATIN1` database), the server sends JSON text in that encoding, but `_JsonLoader.load()` passed the raw bytes to `json.loads()`, which only auto-detects UTF-8/16/32. Any non-ASCII character in a JSON value then raised `UnicodeDecodeError`, while casting the same value to `text` loaded fine.
`_JsonLoader` now records the connection encoding the same way `TextLoader` does and decodes explicitly when it is not UTF-8/ASCII, keeping the bytes fast path for UTF-8 connections. The same handling applies to `JsonbBinaryLoader`, whose payload the server converts to the client encoding as well.
Fixes #1504. Thanks @fppfurtado for the precise report and the suggested approach.
Regression test: `test_load_non_utf8_encoding` covers `json`/`jsonb` in text and binary formats under `client_encoding=latin1`. Verified locally against the loaders with a LATIN1 connection encoding (the new behavior fails with `UnicodeDecodeError` on the unfixed code and passes with the fix); flake8/black/mypy clean.
This contribution was developed with AI assistance and verified by the author.
inline patch 1: psycopg/psycopg/types/json.py, tests/types/test_json.py (+25/-4 lines) download inline-1.patch
diff --git a/psycopg/psycopg/types/json.py b/psycopg/psycopg/types/json.py
index 7ec732a6a..f69c923ad 100644
--- a/psycopg/psycopg/types/json.py
+++ b/psycopg/psycopg/types/json.py
@@ -18,6 +18,7 @@
from ..pq import Format
from ..adapt import AdaptersMap, Buffer, Dumper, Loader, PyFormat
from ..errors import DataError
+from .._encodings import conn_encoding
JsonDumpsFunction: TypeAlias = Callable[[Any], str | bytes]
JsonLoadsFunction: TypeAlias = Callable[[str | bytes], Any]
@@ -250,12 +251,22 @@ class _JsonLoader(Loader):
def __init__(self, oid: int, context: abc.AdaptContext | None = None):
super().__init__(oid, context)
self.loads = self.__class__._loads
+ enc = conn_encoding(self.connection)
+ # json.loads() can auto-detect the UTF encodings from the input
+ # bytes; for other connection encodings the data must be decoded
+ # explicitly (#1504).
+ self._encoding = "" if enc in ("utf-8", "ascii") else enc
def load(self, data: Buffer) -> Any:
+ return self.loads(self._decode(data))
+
+ def _decode(self, data: Buffer) -> str | bytes:
# json.loads() cannot work on memoryview.
if not isinstance(data, bytes):
data = bytes(data)
- return self.loads(data)
+ if self._encoding:
+ return data.decode(self._encoding)
+ return data
class JsonLoader(_JsonLoader):
@@ -276,9 +287,7 @@ class JsonbBinaryLoader(_JsonLoader):
def load(self, data: Buffer) -> Any:
if data and data[0] != 1:
raise DataError(f"unknown jsonb binary format: {data[0]}")
- if not isinstance((data := data[1:]), bytes):
- data = bytes(data)
- return self.loads(data)
+ return self.loads(self._decode(data[1:]))
def _get_current_dumper(
diff --git a/tests/types/test_json.py b/tests/types/test_json.py
index f649ebdf3..b49c78541 100644
--- a/tests/types/test_json.py
+++ b/tests/types/test_json.py
@@ -127,6 +127,18 @@ def test_load_copy(conn, val, jtype, fmt_out):
assert got == json.loads(val)
+@pytest.mark.crdb_skip("encoding")
+@pytest.mark.parametrize("jtype", ["json", "jsonb"])
+@pytest.mark.parametrize("fmt_out", pq.Format)
+def test_load_non_utf8_encoding(conn, jtype, fmt_out):
+ # The loaders must decode the server data with the connection encoding,
+ # not always as UTF-8 (#1504).
+ cur = conn.cursor(binary=fmt_out)
+ conn.execute("set client_encoding to latin1")
+ cur.execute(f"select %s::{jtype}", ('{"a": "\xe9"}',))
+ assert cur.fetchone()[0] == {"a": "\xe9"}
+
+
@pytest.mark.parametrize("fmt_in", PyFormat)
@pytest.mark.parametrize("wrapper", ["Json", "Jsonb"])
def test_dump_customise(conn, wrapper, fmt_in):
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
@ 2026-11-19 17:57 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dvarrazzo (@dvarrazzo) @ 2026-11-19 17:57 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
Thank you for the report and fix, guys.
I'll review better soon, but, @anish-yen could you please add a changelog entry for the next bugfix release?
Cheers!
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
@ 2026-11-19 18:00 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dvarrazzo (@dvarrazzo) @ 2026-11-19 18:00 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
@anish-yen also could you please add a test with the 'ascii' encoding? Note that 'ascii' in postgres means no encoding, so, for example, the text loader will return bytes instead of str.
I guess you should create a table and put in a value with a non-ascii char when the connection is in utf8, then switch it to ascii, read the data, and see what comes out.
My fear is that the result might depend on the database encoding, but maybe that's not the case and we receive utf8 which we can easily decode. It would be good if there was a test in the test suite though.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
@ 2026-11-19 20:54 ` "anish-yen (@anish-yen)" <noreply+anish-yen@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: anish-yen (@anish-yen) @ 2026-11-19 20:54 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
Both done, thank you for the quick look.
- Added a changelog entry under a new "Psycopg 3.3.10 (unreleased)" section.
- Added `test_load_ascii_encoding`: creates a table, inserts a JSON value with a non-ASCII char with the connection in UTF-8, switches to `sql_ascii`, and reads it back, for `json`/`jsonb` in both text and binary format.
On the SQL_ASCII behavior: with no recoding, the server sends the stored bytes unchanged, so data written as UTF-8 arrives as valid UTF-8 and `json.loads()` parses it through its own auto-detection. The loader keeps the bytes fast path for `ascii`, mirroring how `TextLoader` treats it as "encoding unknown". On a database whose stored bytes are not UTF-8 the parse would fail the same way it did before this change; with SQL_ASCII the genuine encoding is unknowable to the client, so passing the bytes through seems the best available behavior.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
@ 2026-11-19 22:27 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dvarrazzo (@dvarrazzo) @ 2026-11-19 22:27 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
I played a bit with the issues and I think that the loader side is now largely correct.
I've run some tests, and there is still a corner case in which we get things wrong. If 1) a databasein non-utf8 encoding 2) contains json with non-ascii chars which is 3) read with a client in SQL_ASCII. I think that this combination is pathological enough that we might not care about it: SQL_ASCII is a pretty special configuration, as it means reading raw data from the server, so it's not really even compatible with reading JSON and expecting a Python value. We should return objects that have bytes instead of strings, so it wouldn't even be valid json.
As a fallback we could consider to use `server_encoding`, but again using SQL_ASCII means wanting to read the raw data, so I'd expect that if someone is using this combination of configuration they would read the `::text` cast of the json field, in order to get a bytes string containing some jsonish value, and they would know themselves what to do with it. Or they would use a custom load function.
What is actually a more real problem is on the dumping side. What happens if there's a non-utf8 connection and someone dumps a non-ascii char? By default, everything works, because we use by default [`json.dumps()`](https://docs.python.org/3/library/json.html#json.dump) which uses by default `ensure_ascii=True`. But if someone uses a custom dump function that emits bytes in utf8 we would send corrupted data to the server. An `€` sent to a latin9 connection would be stored as `€`.
So I think we should fix the dumper too, I propose this way:
- if the dump function returns `str`, encode it in the client encoding (currently we encode utf8);
- if the dump function returns `bytes`:
- if the database is utf8 or SQL_ASCII, use it as it is;
- otherwise, decode the utf8 and encode it in the client_encoding.
The latter is kinda expensive but it is what it is, and it only affects the non-utf8 databases, which are now no more relevant - and better slow and correct than fast and wrong.
Could you please add a test to verify the issue, and verify the fix in the three cases? I think we could use the following grid of parameters for the tests:
- encoding: connection in utf8, ascii, latin9
- dumper: the following two functions
- one producing non-ascii str (e.g. `lambda obj: jsong.dumps(obj, ensure_ascii=False)`
- one producing utf8 encoded bytes (e.g. lambda obj: jsong.dumps(obj, ensure_ascii=False).encode()`)
- type: json and jsonb of course.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
@ 2026-11-19 22:40 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dvarrazzo (@dvarrazzo) @ 2026-11-19 22:40 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
(on psycopg/psycopg/types/json.py:268)
Please use the following, which is what we are currently using in other code:
```suggestion
return str(data, self._encoding)
```
this would actually also have the advantage of working with `memoryview`, so we wouldn't need the `bytes()` transformation and the if could be placed at the beginning of the function.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
@ 2026-11-19 22:42 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dvarrazzo (@dvarrazzo) @ 2026-11-19 22:42 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
(on psycopg/psycopg/types/json.py:263)
It's kinda contradictory to call a function "decode" if it can return the unchanged bytes. But then I don't know which name to give it. `_maybe_decode()`, maybe?
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
@ 2026-11-19 22:43 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dvarrazzo (@dvarrazzo) @ 2026-11-19 22:43 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
(on psycopg/psycopg/types/json.py:257)
I think that this comment is misplaced. This is a bit of information that we can attach to the `_decode()` function below.
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [psycopg/psycopg] PR #1505 (changes requested): Decode JSON data using the connection encoding
@ 2026-11-19 22:43 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: dvarrazzo (@dvarrazzo) @ 2026-11-19 22:43 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
^ permalink raw [nested|flat] 10+ messages in thread
* Re: [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding
@ 2026-11-20 00:11 ` "anish-yen (@anish-yen)" <noreply+anish-yen@github.com>
8 siblings, 0 replies; 10+ messages in thread
From: anish-yen (@anish-yen) @ 2026-11-20 00:11 UTC (permalink / raw)
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
Thanks for the review! All addressed in the two follow-up commits:
- Dumping: `_JsonDumper` now encodes `str` results in the connection encoding, and recodes utf8 bytes returned by the dumps function when the database encoding is not utf8/SQL_ASCII (left as-is in those two cases, as you suggested).
- Tests: added `test_dump_non_utf8_encoding` on the grid you proposed: encoding (utf8, sql_ascii, latin9) x dumps returning str with `ensure_ascii=False` or utf8 bytes x json/jsonb, in both text and binary formats.
- Style: `_decode()` renamed to `_maybe_decode()`, it now uses `str(data, self._encoding)` so memoryview works without the `bytes()` conversion, and the comment moved onto the function.
- Updated the changelog entry to mention dumping too.
I left the SQL_ASCII-with-non-utf8-database corner case alone, per your comment.
^ permalink raw [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-11-20 00:11 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-19 17:37 [psycopg/psycopg] PR #1505: Decode JSON data using the connection encoding "anish-yen (@anish-yen)" <noreply+anish-yen@github.com>
2026-11-19 17:57 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-19 18:00 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-19 20:54 ` "anish-yen (@anish-yen)" <noreply+anish-yen@github.com>
2026-11-19 22:27 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-19 22:40 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-19 22:42 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-19 22:43 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-19 22:43 ` Re: [psycopg/psycopg] PR #1505 (changes requested): Decode JSON data using the connection encoding "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-20 00:11 ` "anish-yen (@anish-yen)" <noreply+anish-yen@github.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox