Subject: Using composite types in psycopg3

From: Vladimir Ryabtsev <[email-masked]>
Date: Tue, 10 Nov 2020 13:59:31 -0800

Hello,

I have tried psycopg3 very briefly and I have a question.

I have a couple of use cases in systems I currently work with
that prevented (or seriously limited) usage of psycopg2, so
I had to use other drivers. This generally relates to accessing
composite types that you can construct on-the-fly in SQL queries.
Consider the following example:

'''
import psycopg2

def get_query(fpath):
    with open(fpath, 'rt') as f:
        return f.read()

def main():
    conn = psycopg2.connect('postgres://user:password@host/db')
    cur = conn.cursor()
    cur.execute(get_query('query.sql'))
    result = cur.fetchone()[0]
    print(type(result), result)
    cur.close()
    conn.close()

if __name__ == '__main__':
    main()
'''

Where query.sql is:

'''
with test as (
    select 1 as id, 'one' val
    union all
    select 1, 'one more'
    union all
    select 2, 'two'
)
select array(
    select (id, array_agg(val))
    from test
    group by id
)
'''

psycopg2 returns the 'result' as a basic string, while
in asyncpg and py-postgresql I have structured data
(roughly 'List[Tuple[int, List[str]]]').

I tried the same in psycopg3 and it is little bit better, but
not entirely: it shows the outer list, the tuples inside it,
but the innermost list is still represented as a basic string:
'{one,"one more"}'.

Is it something you are still working on? Any workarounds?

Vladimir

---

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 11 Nov 2020 00:24:11 +0000

> Is it something you are still working on? Any workarounds?
Yes: by obtaining data from the db in binary mode you can get
information about deeply nested objects. psycopg2 works only in text
mode, psycopg3 in both.

    In [1]: query = """
       ...: with test as (
       ...:     select 1 as id, 'one' val
       ...:     union all
       ...:     select 1, 'one more'
       ...:     union all
       ...:     select 2, 'two'
       ...: )
       ...: select array(
       ...:     select (id, array_agg(val))
       ...:     from test
       ...:     group by id
       ...: )"""

    In [2]: import psycopg3

    In [3]: from psycopg3.pq import Format

    In [4]: cnn = psycopg3.connect("")

    In [5]: cnn.cursor().execute(query).fetchone()[0]
    Out[5]: [('1', '{one,"one more"}'), ('2', '{two}')]

    In [6]: cnn.cursor(format=Format.BINARY).execute(query).fetchone()[0]
    Out[6]: [(1, ['one', 'one more']), (2, ['two'])]

Binary loading/dumping is not supported yet for all the data types,
but the plan is to cover all the builtins. Still not sure about the
interface to request text/binary results, or whether binary shouldn't
be the default as opposed to text. There is still ground to cover, but
we are getting there.

-- Daniele

---

From: Vladimir Ryabtsev <[email-masked]>
Date: Tue, 10 Nov 2020 16:35:57 -0800

Awesome, thanks.

It would be great if it worked out of the box, as in other drivers.
Appreciate your efforts.

Vladimir

---

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 11 Nov 2020 00:48:17 +0000

> It would be great if it worked out of the box, as in other drivers.

I do agree.

I have much more experience with the text format, and in my
understanding not all the types support binary I/O. But my
understanding could be wrong.

After covering the binary format for the missing types (especially
numeric and date/time objects) we can probably do more testing and
check if defaulting to the binary format doesn't have unexpected
consequences.

Cheers!

-- Daniele