Skip to content

Problem with set_type_codec and array of json/jsonb #623

Closed
@CaselIT

Description

@CaselIT
  • 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

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions