Support composite types with fields that are of type `citext`

[kevinvalk]

• asyncpg version: 0.28
• PostgreSQL version: 15
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: Local version
• Python version: 3.11
• Platform: 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?: -
• Can the issue be reproduced under both asyncio and uvloop?: not tested

I am using citext for fields that really need to be citext (as in, I care about the case only for displaying but other than that the case should not matter). Now I have a query that goes like

SELECT * FROM project WHERE (owner::citext, name::citext)::record = ANY($1);

Throughout asyncpg it is mentioned to get this to work (normally) you add a composite type. So we add a composite type and cast the array to the composite type.

CREATE TYPE project_fqn AS (
    owner citext,
    name citext
);

SELECT * FROM project WHERE (owner::citext, name::citext) = ANY($1::project_fqn[]);

Sadly this fails due to an <class 'asyncpg.exceptions._base.UnsupportedClientFeatureError'>: cannot decode type "public"."project_fqn": text encoding of composite types is not supported. error. It can be "fixed" by changing all citext types for a text type but then we lose the case insensitive search.

Is this something that can be fixed or are there any workarounds? I tried doing a set_builtin_type_codec("citext", codec_name=25) but that seems to have no impact.

Some references:

• https://github.com/MagicStack/asyncpg/issues/476
• https://github.com/MagicStack/asyncpg/issues/82

[kevinvalk]

On alternative that has shortcomings is using LOWER() so you use the text type everywhere and make sure to lowercase your input array from the application layer.

CREATE TYPE project_fqn AS (
    owner text,
    name text
);

SELECT * FROM project WHERE (LOWER(owner::text), LOWER(name::text)) = ANY($1::project_fqn[]);