Using a query with a join with multiple active portals enabled results in a error inside cockroachdb with asyncpg

[dikshant]

Start a cockroach single node on v23.1.6:

cockroach start-single-node --insecure

Use the following schema:

CREATE TABLE city (city STRING PRIMARY KEY NOT NULL UNIQUE);

CREATE TABLE student (
    id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    city string,
    FOREIGN KEY (city) REFERENCES city(city)
);

INSERT INTO CITY VALUES ('new york');
INSERT INTO CITY VALUES ('las vegas');

INSERT INTO STUDENT (first_name, last_name, city) VALUES ('john', 'appleseed', 'las vegas');
INSERT INTO STUDENT (first_name, last_name, city) VALUES ('john', 'doe', 'new york');

Run this example with Python3 and asyncpg (version 0.27.0):
Have to run it multiple times in quick succession to see error pop up in CRDB strangely enough. I used a bash for loop

for i in {1..10}; do
     python example.py
done;

Code:

import asyncio
import asyncpg

async def main():
    pool = await asyncpg.create_pool('postgres://[email protected]:26257/defaultdb?sslmode=disable')
    await pool.execute('SET multiple_active_portals_enabled = true;');
    async with pool.acquire() as conn:
        # set session variable
        st = await conn.prepare('SELECT * FROM student LEFT JOIN city ON student.city=city.city;')
        async with conn.transaction():
            # create cursor 1
            cur1 = await st.cursor()
            # partially read from cursor 1
            rec1 = await cur1.fetchrow()
            print(rec1)

            # create cursor 2
            # this will fail without the session variable set above
            # we still see an error even with not using the second cursor.
            # simply setting multiple_active_portals_enabled is enough to see the error
            cur2 = await st.cursor()
            rec2 = await cur2.fetchrow()
            print(rec2)

        # Close the connection.
        await conn.close()

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

Execute

pip3 install asyncpg
python3 example.py

In CockroachDB Server stdout we see:

* ERROR: Queued as error 45743dc8fb584b62bc74c07700a26a6b

If I grep through the logs for 45743dc8fb584b62bc74c07700a26a6b I see unexpected leftover bytes:

W230912 18:51:18.118911 42053 util/log/logcrash/crash_reporting.go:394 ⋮ [T1,n1,client=127.0.0.1:57989,hostnossl,user=root] 798  flow 0515a01b: unexpected 40960 leftover bytes
E230912 18:51:18.119238 42053 util/log/logcrash/crash_reporting.go:373 ⋮ [T1,n1,client=127.0.0.1:57989,hostnossl,user=root] 799  Queued as error ‹45743dc8fb584b62bc74c07700a26a6b›

Jira issue: CRDB-31456

[yuzefovich]

Hm, I'm unable to reproduce this. @dikshant I made minor changes to the schema (because it was hitting errors), and then following the steps don't result in an error. Could you double check that you can reproduce this? Perhaps version of python or asyncpg matters?

[dikshant]

@yuzefovich ah whoops, I just fixed the schema above. I am using asyncpg 0.27.0 and Python 3.11.5.

Just realized after rerunning on a fresh cockroach single node, I couldn't reproduce by running the example just once. But if I run it in quick succession multiple times I can see the error. So try a bash for loop like so:

for i in {1..10}; do
     python example.py
done;

[yuzefovich]

Thanks, I think I see the problem.

[yuzefovich]

@dikshant is the fix for this something that we'd like in 23.1.11? I think the fix is pretty safe, but if there is nobody waiting for it, I'd prefer getting it into 23.1.12 (after longer baking period).

[dikshant]

@dikshant is the fix for this something that we'd like in 23.1.11? I think the fix is pretty safe, but if there is nobody waiting for it, I'd prefer getting it into 23.1.12 (after longer baking period).

Waiting for 23.1.12 is fine.