Closed
Description
- asyncpg version: 0.21
- PostgreSQL version: PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit (docker image)
- Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
the issue with a local PostgreSQL install?: no - Python version: Python 3.8.5
- Platform: windows 10. Same happen on linux
- Do you use pgbouncer?: no
- Did you install asyncpg with pip?: yes
- If you built asyncpg locally, which version of Cython did you use?: no
- Can the issue be reproduced under both asyncio and
uvloop?: not tried
I think there is an issue in how asyncpg handles the json input data when an user sets a type codec for json
and/or jsonb
and the column type is JSON[]
or JSONB[]
.
For the type json[]
there is actually a working configuration, by setting the format='binary'
when setting the type codec, but the same configuration fails on jsonb
This came up while trying to normalize the behavior of all pg dbapi for sqlalchemy sqlalchemy/sqlalchemy#5584
Below is a reproduction case that uses only asyncpg, without sqlalchemy.
import asyncio
import asyncpg
import json
async def connect():
return await asyncpg.connect(user="scott", password="tiger", database="test", host="127.0.0.1")
async def setup():
conn = await connect()
await conn.execute("DROP TABLE IF EXISTS json_table")
await conn.execute("DROP TABLE IF EXISTS jsonb_table")
await conn.execute("CREATE TABLE json_table (id SERIAL, js_col JSON[])")
await conn.execute("CREATE TABLE jsonb_table (id SERIAL, js_col JSONB[])")
await conn.close()
js_insert = "INSERT INTO json_table(js_col) VALUES (cast($1 as json[]))"
jsb_insert = "INSERT INTO jsonb_table(js_col) VALUES (cast($1 as jsonb[]))"
js_select = "select js_col from json_table order by id"
jsb_select = "select js_col from jsonb_table order by id"
values = [(['"foo"'],), (["22"],), (["null"],), (None,), (["[2]"],)]
async def go(conn, jsonb, text):
i, s = (jsb_insert, jsb_select) if jsonb else (js_insert, js_select)
try:
await conn.executemany(i, values)
v = list(await conn.fetch(s))
# print(v)
print(text, jsonb, "ok")
except Exception as e:
print(text, jsonb, "fail", e)
finally:
await conn.close()
async def without_codec(jsonb):
conn = await connect()
await go(conn, jsonb, "without_codec")
async def with_codec(jsonb):
conn = await connect()
for type in ("json", "jsonb"):
await conn.set_type_codec(type, encoder=lambda v: v, decoder=json.loads, schema="pg_catalog")
await go(conn, jsonb, "with_codec")
async def with_codec_binary(jsonb):
conn = await connect()
for type in ("json", "jsonb"):
await conn.set_type_codec(type, encoder=str.encode, decoder=json.loads, schema="pg_catalog", format="binary")
await go(conn, jsonb, "with_codec_binary")
asyncio.run(setup())
for fn in (without_codec, with_codec, with_codec_binary):
print(fn)
asyncio.run(fn(False))
asyncio.run(fn(True))
The output I get is:
~~ without_codec
without_codec json ok
without_codec jsonb ok
~~ with_codec
with_codec json fail. Error: invalid input syntax for type json
DETAIL: Token "NULL" is invalid.
with_codec jsonb fail. Error: invalid input syntax for type json
DETAIL: Token "NULL" is invalid.
~~ with_codec_binary
with_codec_binary json ok
with_codec_binary jsonb fail. Error: unsupported jsonb version number 34
When the test works the data that is inserted is correct.
The expected result is that all cases work, or that at least a set_codec configuration for both json
and jsonb
is functioning.
cc @fantix
Metadata
Metadata
Assignees
Labels
No labels