- asyncpg version: 0.18.3
- PostgreSQL version: 11.4
- Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
the issue with a local PostgreSQL install?: No
- Python version: 3.7.3
- Platform: ArchLinux
- Do you use pgbouncer?: No
- Did you install asyncpg with pip?: Yes
- If you built asyncpg locally, which version of Cython did you use?: N/A
- Can the issue be reproduced under both asyncio and
uvloop?: uvloop is not relevant to the bug
It seems that asyncpg does not allow to use the operator - between a jsonb value and a text value. It is indeed possible, as documented here (fourth row from the bottom):
https://www.postgresql.org/docs/11/functions-json.html#FUNCTIONS-JSONB-OP-TABLE
The query that I am using is:
UPDATE campaigns.campaigns
SET stage_names = stage_names - $3, stages_order = stages_order - $3
WHERE user_id = $1 AND id = $2;
Those two columns are defined as follows:
CREATE TABLE IF NOT EXISTS campaigns.campaigns (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES user_data.users(id),
stage_names JSONB NOT NULL DEFAULT '{"0":"X1","1":"X2","2":"X3","3":"X4"}',
stages_order JSONB NOT NULL DEFAULT '["0","1","2","3"]',
);
When I execute:
await pool.execute(
sql.REORDER_CAMPAIGN_STAGES,
user['id'],
campaign_id,
str(stage_id),
)
I get the following error:
File "/lib/python3.7/site-packages/asyncpg/pool.py", line 509, in execute
return await con.execute(query, *args, timeout=timeout)
File "/lib/python3.7/site-packages/asyncpg/connection.py", line 275, in execute
_, status, _ = await self._execute(query, args, 0, timeout, True)
File "/lib/python3.7/site-packages/asyncpg/connection.py", line 1414, in _execute
query, args, limit, timeout, return_status=return_status)
File "/lib/python3.7/site-packages/asyncpg/connection.py", line 1422, in __execute
return await self._do_execute(query, executor, timeout)
File "/lib/python3.7/site-packages/asyncpg/connection.py", line 1444, in _do_execute
result = await executor(stmt, None)
File "asyncpg/protocol/protocol.pyx", line 178, in bind_execute
File "asyncpg/protocol/prepared_stmt.pyx", line 160, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $3: '1' (a sized iterable container expected (got type 'str'))
It seems asyncpg is expecting an array, when in fact a string is perfectly fine. I have tried working around the problem by changing the query to the following:
UPDATE campaigns.campaigns
SET stage_names = stage_names - ($3)[1], stages_order = stages_order - ($3)[1]
WHERE user_id = $1 AND id = $2;
and the code to:
await pool.execute(
sql.REORDER_CAMPAIGN_STAGES,
user['id'],
campaign_id,
[str(stage_id)],
)
in the hope of making it work by supplying an array like asyncpg wants and then taking the first element. But this produces a completely wrong result and I'm not sure why.
All is fine in psql:
=# select stage_names - '1' from campaigns.campaigns where id = 1;
?column?
-------------------------------------------------------
{"0": "X1", "2": "X3", "3": "X4"}
(1 row)
=# select stages_order - '1' from campaigns.campaigns where id = 1;
?column?
-----------------
["0", "3", "2"]
(1 row)
EDIT: The problem was totally unrelated to asyncpg.
the issue with a local PostgreSQL install?: No
uvloop?: uvloop is not relevant to the bug
It seems that asyncpg does not allow to use the operator
-between ajsonbvalue and atextvalue. It is indeed possible, as documented here (fourth row from the bottom):https://www.postgresql.org/docs/11/functions-json.html#FUNCTIONS-JSONB-OP-TABLE
The query that I am using is:
Those two columns are defined as follows:
When I execute:
I get the following error:
It seems asyncpg is expecting an array, when in fact a string is perfectly fine. I have tried working around the problem by changing the query to the following:
and the code to:
in the hope of making it work by supplying an array like asyncpg wants and then taking the first element. But this produces a completely wrong result and I'm not sure why.
All is fine in psql:
EDIT: The problem was totally unrelated to asyncpg.