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

Open
#1,075 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
45/100
Issue type
Feature
Clarity
Mostly clear
Activity status
Stale
Tech stack
postgresql, python
Domain
databases

Research direction

Reproduce the reported SQL using asyncpg 0.28 with PostgreSQL 15 and a composite type whose fields are citext. Start by tracing composite decoding and the set_builtin_type_codec("citext") entry point; done means project_fqn[] values decode without UnsupportedClientFeatureError while preserving citext behavior.

Written by the indexing model from the issue text.

Description

  • 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:

Dominant language
Python
Stars
8.1k
Forks
469
Avg merge
2d 20h
Merged PRs (30d)
9

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from MagicStack/asyncpg

All issues in MagicStack/asyncpg

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.