I'm having trouble with canceled queries because of replication conflicts. Specifically, I get the message User query might have needed to see row versions that must be removed event if no deletes are happening and the read query isn't even selecting from a table that is being modified.
Here's my test script:
from typing import Any
# pip install anyio psycopg-pool
import anyio
from anyio.abc import TaskStatus
from psycopg_pool import AsyncConnectionPool
async def create_data(*, task_status: TaskStatus[Any] = anyio.TASK_STATUS_IGNORED) -> None:
# primary
dsn = 'postgres://...'
async with AsyncConnectionPool(dsn) as pool:
async with pool.connection() as conn:
await conn.set_autocommit(True)
await conn.execute('DROP TABLE IF EXISTS test;')
await conn.execute('CREATE TABLE test (x int);')
task_status.started()
x = 0
while True:
x += 1
await conn.execute('INSERT INTO test (x) VALUES (%(x)s);', {'x': x})
await conn.execute('VACUUM (VERBOSE) test;')
async def read_data() -> None:
# read replica
dsn = 'postgres://...'
async with AsyncConnectionPool(dsn) as pool:
async with pool.connection() as conn:
await conn.set_autocommit(True)
async with conn.cursor() as cur:
while True:
await cur.execute(
"""
SELECT pg_sleep(1);
"""
)
async def main() -> None:
async with anyio.create_task_group() as tg:
await tg.start(create_data)
await anyio.sleep(1)
tg.start_soon(read_data)
anyio.run(main)
I've set max_standby_streaming_delay = 0ms on the replica.
If I change the query to SELECT 1; I don't have any issues (at least if I run for 30s); with the SELECT pg_sleep(1); query I get the error instantly.
Why am I getting the error in this scenario? Every other question I've seen along these lines involves deletes/updates in the primary and queries on the read replica that interact with those rows. Is Postgres unable to stream updates from the primary if any query is running on the read replica?
