Binary protocol transform failed in load_sequence for query returning both RecordArray and Record

[exrok]

Getting psycopg.ProgrammingError: cannot load sequence of 1 items: 2 loaders registered
when using the binary protocol with query returning both RecordArray and Record.

See minimal reproduction and error traceback logs included below.

Observations

• Changing the query to either of the following resolves the problem:
  • SELECT ARRAY[ROW('e656b0f5-fa8e-4cbe-98db-525f9ab1cacc'::uuid, 32)]
  • SELECT ROW('abc')
• The bug seems to exist in both psycopg_binary and the native python implementation.
• The rust crate tokio-postgres doesn't have this issue using the binary protocol, with the same setup.
• The problem only occurs with the binary protocol.

My hypothesis is that it is using the same type info from the first Record to parse the second Record.

Env

• Python: v3.10
• OS: Arch Linux
• DB: Postgres 15.2
• psycopg 3.1.8 both with and without binary extra
• Don't know if it is used but I have libpq.so.5.15 on my system.

Minimal Reproduction

Running Postgres v15.2

docker run -d -p 5432:5432 --name testdb -e POSTGRES_PASSWORD=postgres postgres:15.2

Python script to trigger failure.

import psycopg

with psycopg.connect("host=localhost user=postgres password=postgres") as conn:
    conn.execute(
        "SELECT ARRAY[ROW('e656b0f5-fa8e-4cbe-98db-525f9ab1cacc'::uuid, 32)], ROW('abc')",
        binary=True,
    ).fetchall()

Error Traceback Logs

Traceback when using only psycopg[binary,pool] version 3.1.8

Traceback (most recent call last):
  File "/home/user/code/bug/src/repro.py", line 22, in <module>
    rows = cursor.fetchall()
  File "/home/user/code/bug/.venv/lib/python3.10/site-packages/psycopg/cursor.py", line 851, in fetchall
    records = self._tx.load_rows(self._pos, self.pgresult.ntuples, self._make_row)
  File "psycopg_binary/_psycopg/transform.pyx", line 481, in psycopg_binary._psycopg.Transformer.load_rows
  File "/home/user/code/bug/.venv/lib/python3.10/site-packages/psycopg/types/composite.py", line 159, in load
    return self._tx.load_sequence(
  File "psycopg_binary/_psycopg/transform.pyx", line 541, in psycopg_binary._psycopg.Transformer.load_sequence
  File "psycopg_binary/_psycopg/transform.pyx", line 551, in psycopg_binary._psycopg.Transformer.load_sequence
psycopg.ProgrammingError: cannot load sequence of 1 items: 2 loaders registered

Traceback when using only psycopg[pool] version 3.1.8

Traceback (most recent call last):
  File "/home/user/code/bug/src/repro.py", line 22, in <module>
    rows = cursor.fetchall()
  File "/home/user/code/bug/.venv/lib/python3.10/site-packages/psycopg/cursor.py", line 851, in fetchall
    records = self._tx.load_rows(self._pos, self.pgresult.ntuples, self._make_row)
  File "/home/user/code/bug/.venv/lib/python3.10/site-packages/psycopg/_transform.py", line 305, in load_rows
    record[col] = self._row_loaders[col](val)
  File "/home/user/code/bug/.venv/lib/python3.10/site-packages/psycopg/types/composite.py", line 159, in load
    return self._tx.load_sequence(
  File "/home/user/code/bug/.venv/lib/python3.10/site-packages/psycopg/_transform.py", line 328, in load_sequence
    raise e.ProgrammingError(
psycopg.ProgrammingError: cannot load sequence of 1 items: 2 loaders registered

Traceback of different error when the column containing 32 was removed from the record in the array

cursor = conn.execute(
    "SELECT ARRAY[ROW('e656b0f5-fa8e-4cbe-98db-525f9ab1cacc'::uuid)], ROW('abc')",
    binary=True,
).fetchall()

Traceback (most recent call last):
  File "/home/user/code/bug/src/repro.py", line 22, in <module>
    rows = cursor.fetchall()
  File "/home/user/code/bug/.venv/lib/python3.10/site-packages/psycopg/cursor.py", line 851, in fetchall
    records = self._tx.load_rows(self._pos, self.pgresult.ntuples, self._make_row)
  File "psycopg_binary/_psycopg/transform.pyx", line 481, in psycopg_binary._psycopg.Transformer.load_rows
  File "/home/user/code/bug/.venv/lib/python3.10/site-packages/psycopg/types/composite.py", line 159, in load
    return self._tx.load_sequence(
  File "psycopg_binary/_psycopg/transform.pyx", line 541, in psycopg_binary._psycopg.Transformer.load_sequence
  File "psycopg_binary/_psycopg/transform.pyx", line 567, in psycopg_binary._psycopg.Transformer.load_sequence
  File "/home/user/code/bug/.venv/lib/python3.10/site-packages/psycopg/types/uuid.py", line 57, in load
    return UUID(bytes=data)
  File "/usr/lib/python3.10/uuid.py", line 186, in __init__
    raise ValueError('bytes is not a 16-char string')
ValueError: bytes is not a 16-char string

[Thomas-airmatrix]

I have explored the code a bit in the python implementation.
I believe this has to do with the caching in the _loaders.

For instance, the following works as expected.

import psycopg

prev = psycopg._transform.Transformer.get_loader

def wrapper(this, obj, format):
    this._loaders = ({}, {})
    return prev(this, obj, format)

psycopg._transform.Transformer.get_loader = wrapper

with psycopg.connect("host=localhost user=postgres password=postgres") as conn:
    values = conn.execute(
        "SELECT ARRAY[ROW('e656b0f5-fa8e-4cbe-98db-525f9ab1cacc'::uuid, 32)], ROW('abc')",
        binary=True,
    ).fetchall()
    print(values)

[dvarrazzo]

I see. You don't even need the array for it. The problem is that probably a single transformer is allocated to convert the data of type ROW. But, even if the two fields have the same OID, they need different transformers.

This breaks the paradigm of having a single loader for every field with the same oid. We might either drop that caching or special-case the record type in order to make sure that each column gets its own transformer instance.

[dvarrazzo]

Example showing the problem with two different rows.

>>> cur = conn.execute("SELECT ROW('e656b0f5-fa8e-4cbe-98db-525f9ab1cacc'::uuid, 32), ROW('abc')", binary=True)

>>> cur.fetchone()
...
ProgrammingError: cannot load sequence of 1 items: 2 loaders registered

>>> cur.description
[<Column 'row', type: record (oid: 2249)>,
 <Column 'row', type: record (oid: 2249)>]

Because two columns have the same oid Psycopg assumes that it can use the same loader instance. The loader instance uses a Transformer object which is configured to load the fields found in the first row, so the second row can crash in the most different manner.

The problem is not even with ROW being in different columns: postgres can actually return different rows types in the same column in different records:

piro=# values (ROW(1::integer)), (ROW('2020-01-01'::date));
┌──────────────┐
│   column1    │
├──────────────┤
│ (1)          │
│ (2020-01-01) │
└──────────────┘
(2 rows)

so the failure looks like:

>>> cur = conn.execute("VALUES (ROW('e656b0f5-fa8e-4cbe-98db-525f9ab1cacc'::uuid, 32)), (ROW('abc'))", binary=True)

>>> cur.fetchone()
((UUID('e656b0f5-fa8e-4cbe-98db-525f9ab1cacc'), 32),)

>>> cur.fetchone()
...
ProgrammingError: cannot load sequence of 1 items: 2 loaders registered

So it seems that we can still use the same loader instance for different column. What we need is probably to get the tuple of oids encoded in the row value, and use different transformers for different sequence of types.

[dvarrazzo]

Fixed in 6b67558! To be released in 3.1.9.