When using Redshift - PostgresSyntaxError: syntax error at or near "ORDER" - in query with no "ORDER"

[yehorb]

• asyncpg version: asyncpg==0.27.0.
• PostgreSQL version: PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.46987.
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: I am using Amazon Redshift. I could not reproduce the issue with potgres:9.0 Docker image.
• Python version: Python 3.9.12.
• Platform: Windows 10 Pro, 22H2, 19045.2604.
• Do you use pgbouncer?: No.
• Did you install asyncpg with pip?: Yes.
• If you built asyncpg locally, which version of Cython did you use?: -
• Can the issue be reproduced under both asyncio and uvloop?: Have not tried uvloop.

Running this snippet:

options = ConnectionOptions()

sslctx = ssl.create_default_context(ssl.Purpose.SERVER_AUTH)
sslctx.check_hostname = False
sslctx.verify_mode = ssl.CERT_NONE

connection = await asyncpg.connect(options.dsn, ssl=sslctx)

try:
    query = "select 'all' = any($1::text[])"
    await connection.fetch(query, ["all"])

finally:
    await connection.close()

I get this exception:

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "ORDER"

I am wondering, where the "syntax error at or near "ORDER" comes from, as there is no "ORDER" in the original query.

If asyncpg does not rewrite queries in any way, and the query passed to fetch is forwarded to the database without modification (https://github.com/MagicStack/asyncpg/issues/434#issuecomment-910474719, https://github.com/MagicStack/asyncpg/discussions/859#discussioncomment-1742492) - where does the "ORDER" come from?

Can this issue be worked around, or I should abandon asyncpg as a Redshift client? I do understand that Redshift is not officially supported, but this client is very good otherwise.

I was not able to reproduce the error with the postgres:9.0 instance (the oldest I was able to find quickly). The same (except ssl=False) snippet works without errors.

[elprans]

The error comes from the type introspection query. It might be possible to adapt asyncpg to Redshift, PRs welcome.

[stefanondisponibile]

Hey @elprans I'm experiencing the same error when trying to simply select a SUPER field from Redshift. Did you already have an idea of what are the main points to make asyncpg Redshift-compatible?

[stefanondisponibile]

For anyone else stumbling into this: the solution for me was to register a custom encoder/decoder for the super type via asyncpg's Connection.set_type_codec.

[KevinLeung-LTP]

Hi @elprans , I meet the same problem when trying a simple select query without ORDER from redshift. Are there any solution for this problem?

[remigabillet]

For anyone else stumbling into this: the solution for me was to register a custom encoder/decoder for the super type via asyncpg's Connection.set_type_codec.

@stefanondisponibile Thanks for the help. I can't get past this error though. Can you share the code you used?

[stefanondisponibile]

hey @remigabillet, I think that could depend specifically on the error you're getting, but you can find an example here. So for example:

# ...
await connection.set_type_codec(
  typename="super",
  schema="pg_catalog",
  encoder=your_encoder, decoder=your_decoder
)

# your_encoder and your_decoder can be as simple as json.dumps/json.loads to more complex implementations depending also on how you're storing your data.

[remigabillet]

@stefanondisponibile this works! thank you!. I wasn't sure which schema to set. In case it's useful, here's the code I'm using:

import json

await conn.set_type_codec(
  "super",
  schema="pg_catalog",
  encoder=json.dumps,
  decoder=json.loads
  )

[stefanondisponibile]

Glad it helped!

[remigabillet]

@stefanondisponibile I'm running into the same error again when querying Redshift, when trying to bind a list: PostgresSyntaxError: syntax error at or near "ORDER"

await conn.fetch(
  "SELECT id, name FROM t WHERE id = ANY($1::BIGINT[])",
  [976646132813407501, 976646132813407502]
)

I debugged the code found OID 1016 is the one with a missing code. It maps to the type _int8. Unfortunately, when trying to set a codec, I get a new error:

InterfaceError: cannot use custom codec on type pg_catalog._int8: it is neither a scalar type nor a composite type

asyncpg returns this error if typelem is not 0, which is the case:

I'm not sure hot how to proceed. Let me know if you have any ideas.

[stefanondisponibile]

@remigabillet I'm not sure that's directly related to this issue. Are you experiencing it also with other queries?

Is the query you want to execute even working if you run it directly from the Redshift editor or psql? Array support in Redshift is quite different from Postgres (it's very poor actually, they have some ARRAY functions but they don't support the equivalent of the Postgres array data type), I would write that query with something like:

SELECT id, name FROM t WHERE id IN (976646132813407501, 976646132813407502)

So maybe in your case I wouldn't even use args ($1). Is asyncpg your only option here? I think in this issue we're trying to find some way to make asyncpg work as much as possible with Redshift given the overlap, but I wouldn't expect asyncpg to support Redshift at all, they're different kind of animals.