Cockroachdb copy_records_to_table support?

[cmflynn]

• asyncpg version: v0.21.0
• PostgreSQL version: CCL v20.1.8 @ 2020/10/21 15:46:38 (go1.13.9)
• Python version: 3.8
• Platform: macos
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes

---

EDIT: Confirmed cockroachdb does not support the COPY ... FROM STDIN syntax. (https://github.com/cockroachdb/cockroach/issues/16392)

Reasoning is: "because it's 1) complicated and 2) slow. All the data goes through a single connection. CockroachDB provides an IMPORT PGDUMP statement which is much faster and can operate in parallel."

Hopefully one of the maintainers can comment on this.

---

Hello, I'm not sure if this has been asked before, but I'm having issues running copy_records_to_table into a cockroachdb table/temp table. Here's a code snippet which will reproduce the issue when running a local cockroachdb.

Exception:

PostgresSyntaxError at or near "(": syntax error
DETAIL:  source SQL:
COPY "cool_table" FROM STDIN (FORMAT binary)
                             ^

import asyncio
import asyncpg

async def bwrite():
    dsn = "postgres://root@localhost:26257/postgres"
    conn: asyncpg.Connection = await asyncpg.connect(dsn)
    await conn.execute("SET experimental_enable_temp_tables = on")
    await conn.execute(
        """CREATE TEMP TABLE cool_table(
                objectid int8,
                CONSTRAINT "primary" PRIMARY KEY (objectid ASC)
            );"""
    )
    try:
        this_res = await conn.fetch("SHOW TABLES from pg_temp")
        print(this_res)
        await conn.copy_records_to_table(
            "cool_table", records=[(12345,), (67890,)]
        )
    except Exception as e:
        print(e)

if __name__ == "__main__":
    # build: CCL v20.1.8 @ 2020/10/21 15:46:38 (go1.13.9)
    loop = asyncio.get_event_loop()
    res = loop.run_until_complete(bwrite())

[cmflynn]

Just an update on this - I did confirm this works as expected on a postgres database. So the issue is with cockroachdb. Any chance somebody could look into this or point me in a direction to help fix it?

Another update: when using this version of CockroachDB CCL v20.1.3 (x86_64-unknown-linux-gnu, built 2020/06/23 08:44:08, go1.13.9) I get the error that I've seen another user report: SimpleQuery not allowed while in extended protocol mode

Update: Tried with cockroachdb v20.2.0-rc.4 "cockroachdb/cockroach-unstable:v20.2.0-rc.4" and got this again:

PostgresSyntaxError at or near "(": syntax error
DETAIL:  source SQL:
COPY "cool_table" FROM STDIN (FORMAT binary)

After going through some cockroachdb docs it looks they use IMPORT INTO whereas the copy_records_to_table is trying to "COPY .. FROM STDIN". Perhaps the api is not compatible at this point.

[cmflynn]

Found the issue. I'll look into how to put a PR up for it shortly.

[lacasaprivata2]

+1

[cmflynn]

sorry I never got around to fixing this. iirc the copy func had some syntactical differences that caused the issue.

[lacasaprivata2]

yeah cockroach is quickly moving away from standard syntax of postgres