How do I implement a custom data type?

[fmariluis]

Hi, our db uses ltrees (https://www.postgresql.org/docs/current/static/ltree.html) and asyncpg fails with

asyncpg.exceptions.UndefinedFunctionError: no binary output function available for type ltree

In psycopg2 you could write an adapter to cast the SQL type to a python object (http://initd.org/psycopg/docs/advanced.html#type-casting-of-sql-types-into-python-objects).

Is there a way to do something like that for this driver?

Very interesting project, btw!

[elprans]

Custom data codecs can be specified using Connection.set_type_codec().

As to the error itself, it seems to be a bug, as unknown data types should be returned as text. I'll look into this.

[elprans]

H-m-m. I can't seem to reproduce this. Would you mind sharing a snippet that triggers this?

[fmariluis]

Mmm, it seems something specific to our DB. If I a create a new DB with ltree installed, asyncpg returns text, like you said.

I'll keep trying.

[fmariluis]

It fails with a composite ltree (ltree[]). Complete steps:

Python 3.5.2, Ubuntu 16.04 and PostgreSQL 9.5.3 (with contrib package installed).

1. createdb example_asyncpg
2. Inside the db: CREATE EXTENSION ltree;
3. SQL:

DROP TABLE IF EXISTS test;

CREATE TABLE test (path ltree, comp_path ltree[]);
INSERT INTO test VALUES ('Top', '{"1", "2"}');

4. Python file:

import asyncio
import asyncpg

async def run():
    conn = await asyncpg.connect(user='example', password='example',
                                 database='example_asyncpg', host='127.0.0.1')
    values = await conn.fetch('''SELECT comp_path FROM test''')
    await conn.close()

loop = asyncio.get_event_loop()
loop.run_until_complete(run())

5. Exception:

  File "asyncpg/protocol/protocol.pyx", line 165, in bind_execute (asyncpg/protocol/protocol.c:45839)
  File "/usr/lib/python3.5/asyncio/futures.py", line 361, in __iter__
    yield self  # This tells Task to wait for completion.
  File "/usr/lib/python3.5/asyncio/tasks.py", line 296, in _wakeup
    future.result()
  File "/usr/lib/python3.5/asyncio/futures.py", line 274, in result
    raise self._exception
asyncpg.exceptions.UndefinedFunctionError: no binary output function available for type ltree

[elprans]

OK, so the real issue here is that ltree does not implement binary I/O. Unfortunately, current version of asyncpg does not support text-only datatypes in arrays or composite types, as that would require complicated text parsing for everything. This is a limitation of PostgreSQL protocol.

As workaround, you can cast ltree[] to text[]:

await conn.fetch('''SELECT comp_path::text[] FROM test''')

[fmariluis]

That's perfect, thanks.

[elprans]

I'll see if I can put a PostgreSQL patch together to support binary I/O for ltree.

[vitaly-burovoy]

@fmariluis, try asyncpg-0.8.4: @elprans added text array decoder, your steps to reproduce give correct result for me.

[fmariluis]

I'll give it a try. Thanks.

[elprans]

This is fixed in the released version.