no decoder for composite type element in position _ of type OID _

[Mayzie]

• asyncpg version: 0.17.0
• PostgreSQL version: 10.5
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?:
• Python version: 3.7.0
• Platform: Linux
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: pipenv
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and uvloop?: asyncio definitely, untested with uvloop

Whenever I am trying to call a plpgsql stored procedure which returns a custom defined enum, I receive the following error: RuntimeError: no decoder for composite type element in position 2 of type OID 88492

Type with OID 88492 in my database (from pg_catalog.pg_type) is listed as ssg.logintype which is defined as below:

	CREATE TYPE SSG.LOGINTYPE AS ENUM (
		'phone',
		'email'
	);

Partial traceback (asyncpg relevant parts):

  File "/home/mayzie/.local/share/virtualenvs/api-xyfAGMEp/lib/python3.7/site-packages/asyncpg/prepared_stmt.py", line 179, in fetchval
    data = await self.__bind_execute(args, 1, timeout)
  File "/home/mayzie/.local/share/virtualenvs/api-xyfAGMEp/lib/python3.7/site-packages/asyncpg/prepared_stmt.py", line 203, in __bind_execute
    self._state, args, '', limit, True, timeout)
  File "asyncpg/protocol/protocol.pyx", line 212, in bind_execute
  File "asyncpg/protocol/coreproto.pyx", line 71, in asyncpg.protocol.protocol.CoreProtocol._read_server_messages
  File "asyncpg/protocol/coreproto.pyx", line 205, in asyncpg.protocol.protocol.CoreProtocol._process__bind_execute
  File "asyncpg/protocol/coreproto.pyx", line 495, in asyncpg.protocol.protocol.CoreProtocol._parse_data_msgs
  File "asyncpg/protocol/protocol.pyx", line 770, in asyncpg.protocol.protocol.BaseProtocol._decode_row
  File "asyncpg/protocol/prepared_stmt.pyx", line 286, in asyncpg.protocol.protocol.PreparedStatementState._decode_row
  File "asyncpg/protocol/codecs/base.pyx", line 257, in asyncpg.protocol.protocol.Codec.decode
  File "asyncpg/protocol/codecs/base.pyx", line 166, in asyncpg.protocol.protocol.Codec.decode_scalar
  File "asyncpg/protocol/codecs/record.pyx", line 39, in asyncpg.protocol.protocol.anonymous_record_decode
RuntimeError: no decoder for composite type element in position 2 of type OID 88492

[elprans]

Please provide a more elaborate example. Enum types in composites, as well as enum types returned by functions work as expected for me in these tests:

https://github.com/MagicStack/asyncpg/blob/2fc50e48e1838b2d2eeabfa0ed3722896c48e349/tests/test_codecs.py#L1627-L1664

[elprans]

Your issue appears to be because some of your queries return an enum value in an anonymous record. Here's a script that reproduces this:

import asyncio
import asyncpg


async def main():
    conn = await asyncpg.connect()

    await conn.execute('''
        CREATE TYPE my_enum_t AS ENUM ('abc');
    ''')

    try:
        res = await conn.fetchval("SELECT ROW('abc'::my_enum_t)")
        print(res, type(res))

    finally:
        await conn.execute('''
            DROP TYPE my_enum_t;
        ''')
        await conn.close()

asyncio.get_event_loop().run_until_complete(main())

This is actually, a known (although not documented) limitation. There is no way to fully introspect a query like SELECT (1, 'foo'), and asyncpg must know the types of all data returned by the query in order to be able to correctly decode it.

There are two workarounds:

1. Cast the enum element to text: SELECT (1, enum_value::text), or
2. Use an explicit composite type: SELECT (1, enum_value)::my_record_type.

I have an idea on how to fix asyncpg to handle this case gracefully, but it'd require some non-trivial changes.

[Mayzie]

Sorry, it happens when I am returning a record which contains the custom enum data type from a PL/pgSQL function.

CREATE OR REPLACE FUNCTION SSG.GetUserDetails (
		AIdentifier	TEXT,
		--
		OUT OUserID		UUID,
		OUT OName		TEXT,
		OUT OType		SSG.LOGINTYPE,
		...
) RETURNS RECORD AS $$ ...

(that is the offending function and argument that caused the error in the OP)

[Mayzie]

I should mention that the python-postgres/fe library which I have used previously works fine in this particular use case and is able to recognise and convert the custom enum data type.

In the meantime I have just worked around it by switching all stored function argument data types from the custom enum to text, and casting to text within the function.

[zozzz]

@elprans what do you think about preloading all types per pool / or connection (if not created by pool), of course configurable and if all types preloaded, the type introspection can be omitted when executing query.

[elprans]

Preloading wouldn't help with nested anonymous composite types: you can't enumerate them reasonably.

[zozzz]

Maybe not working for this ticket, but for #714 can work. Because if i run a simple query that initialize the int array postgresql type, and everything works fine.

await conn.execute('''
    DROP TABLE IF EXISTS "hidden";
    CREATE TABLE "hidden" (
        "int4" INT4 NOT NULL,
        "created" TIMESTAMP
    );
''')

await conn.execute('INSERT INTO "hidden" VALUES ($1, CURRENT_TIMESTAMP)', 1)
# initialize int array type (OID=1007), so can be decoded
await conn.fetchrow('SELECT ARRAY_AGG("int4") FROM "hidden"')
# the result is decoded
await conn.fetchrow('SELECT ROW((SELECT ARRAY_AGG("int4") FROM "hidden"))')

# another
# initialize record array type (OID=2287), so can be decoded
await conn.fetchrow('SELECT ARRAY_AGG(ROW("int4")) FROM "hidden"')
await conn.fetchrow('SELECT ROW((SELECT ARRAY_AGG(ROW("int4")) FROM "hidden"))')

Or just simply write all array OIDs into code, just like: pgtypes.pxi:112

[Hultner]

Maybe not working for this ticket, but for #714 can work. Because if i run a simple query that initialize the int array postgresql type, and everything works fine.

await conn.execute('''
    DROP TABLE IF EXISTS "hidden";
    CREATE TABLE "hidden" (
        "int4" INT4 NOT NULL,
        "created" TIMESTAMP
    );
''')

await conn.execute('INSERT INTO "hidden" VALUES ($1, CURRENT_TIMESTAMP)', 1)
# initialize int array type (OID=1007), so can be decoded
await conn.fetchrow('SELECT ARRAY_AGG("int4") FROM "hidden"')
# the result is decoded
await conn.fetchrow('SELECT ROW((SELECT ARRAY_AGG("int4") FROM "hidden"))')

# another
# initialize record array type (OID=2287), so can be decoded
await conn.fetchrow('SELECT ARRAY_AGG(ROW("int4")) FROM "hidden"')
await conn.fetchrow('SELECT ROW((SELECT ARRAY_AGG(ROW("int4")) FROM "hidden"))')

Or just simply write all array OIDs into code, just like: pgtypes.pxi:112

Is there a cleaner way to register the oids? I have a proc returning a couple of int arrays (oid 1007), creating a hidden table at the start of the session seems like a very ugly solution.