Improve support for arrays of composite types

[bobobo1618]

It's unclear at the moment how to pass composite type values (in my case, arrays of them) to asyncpg.

For example, if I want to do something like this:

CREATE TYPE keyvalue AS (key text, value text);
CREATE TABLE test (id int, data keyvalue);
INSERT INTO test (id, data) VALUES (1, ROW('stuff', 'things'));

How to do so with asyncpg isn't clear. I've tried tuples and received errors about integer types (one part of my composite type is an integer). The docs mention something about a Record type but not where to find it.

[elprans]

Composite types accept tuples (or any iterable) as input. Can you please provide the exact query and the error?

My best guess would be that argument type introspection is not returning the correct type. If that's the case, casting the variable explicitly should help: $1::keyvalue.

[bobobo1618]

The query looks like this:

INSERT INTO pixiv_posts (id, explicit, image_url, caption, tags, title, user_id, work_title, type, ugoira_frames) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) ON CONFLICT DO NOTHING

The table is defined as:

CREATE TYPE pixiv_type AS enum ('illustration', 'manga', 'ugoira');
CREATE TYPE pixiv_ugoira_frame AS (file text, delay int);
CREATE TABLE pixiv_posts (id int PRIMARY KEY, explicit boolean, image_url text, caption text, tags text[], title text, userId int, work_title text, type pixiv_type, ugoira_frames pixiv_ugoira_frame[]);

The arguments I'm passing through to the prepared statement's fetch_val in Python are:

[
    58685213, False, 'http://i2.pixiv.net/img-zip-ugoira/img/2016/08/28/10/25/11/58685213_ugoira600x600.zip', 'Twitterのフォロワーさんのアイコン用に描きました(*^▽^*)', ['うごイラ', 'きんいろモザイク', 'アリス・カータレット'], '【きんいろモザイク】「アリス喜怒哀楽」イラスト/H*lab [pixiv]', 14421408, 'アリス喜怒哀楽', 'ugoira', 
    [
        ('000000.jpg', 100), ('000001.jpg', 100), ('000002.jpg', 100), ('000003.jpg', 100), 
        ('000004.jpg', 100), ('000005.jpg', 100), ('000006.jpg', 100), ('000007.jpg', 100), 
        ('000008.jpg', 100), ('000009.jpg', 100), ('000010.jpg', 100), ('000011.jpg', 100), 
        ('000012.jpg', 100), ('000013.jpg', 100), ('000014.jpg', 100), ('000015.jpg', 100)
]]

The traceback I'm getting is:

env/lib/python3.5/site-packages/asyncpg/prepared_stmt.py in fetchval(self, column, timeout, *args)
    178         protocol = self._connection._protocol
    179         data, status, _ = await protocol.bind_execute(
--> 180             self._state, args, '', 1, True, timeout)
    181         self._last_status = status
    182         if not data:

env/lib/python3.5/site-packages/asyncpg/protocol/protocol.pyx in bind_execute (asyncpg/protocol/protocol.c:45856)()

env/lib/python3.5/site-packages/asyncpg/protocol/prepared_stmt.pyx in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg (asyncpg/protocol/protocol.c:42239)()

env/lib/python3.5/site-packages/asyncpg/protocol/codecs/base.pyx in asyncpg.protocol.protocol.Codec.encode (asyncpg/protocol/protocol.c:12276)()

env/lib/python3.5/site-packages/asyncpg/protocol/codecs/base.pyx in asyncpg.protocol.protocol.Codec.encode_array (asyncpg/protocol/protocol.c:11822)()

env/lib/python3.5/site-packages/asyncpg/protocol/codecs/array.pyx in asyncpg.protocol.protocol.array_encode (asyncpg/protocol/protocol.c:28287)()

env/lib/python3.5/site-packages/asyncpg/protocol/codecs/array.pyx in asyncpg.protocol.protocol._write_array_data (asyncpg/protocol/protocol.c:27753)()

env/lib/python3.5/site-packages/asyncpg/protocol/codecs/array.pyx in asyncpg.protocol.protocol._write_array_data (asyncpg/protocol/protocol.c:27987)()

ValueError: invalid array element: an integer is required (got type str)

I get the same error when adding ::pixiv_ugoira_frame[] to $10 and when executing the query directly rather than through a PreparedStatement. I also get the same error when removing the tags value.

[elprans]

OK, I see the issue now. There is an array of composites and asyncpg mistakes the input for a multi-dimensional array, as the array encoder treats any Container as an array slice. This case, indeed, needs some thought and, possibly, exposure of the Record type to specify composite input explicitly. Meanwhile, you can use the following:

class Record:
    def __init__(self, elements):
        self.elements = elements

    def __iter__(self):
        return iter(self.elements)

    def __len__(self):
        return len(self.elements)

And then

await asyncpg.execute('''
    INSERT INTO pixiv_posts(ugoira_frames) VALUES($1)
''', [Record(('000000.jpg', 100))])

[elprans]

Fix committed: 2a0b1bb.