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

• 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

asyncpg 0.28 と PostgreSQL 15、およびフィールドが citext である複合型を使って、報告された SQL を再現します。まず、複合型のデコードとエントリポイント set_builtin_type_codec("citext") を追跡します。project_fqn[] の値が UnsupportedClientFeatureError なしでデコードされ、citext の動作が維持されれば完了です。