0

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?

1 Answer 1

0

Just because the query on the standby server hasn't accessed a certain table yet is no guarantee that it won't do so before it is done. As a consequence, WAL records that remove row versions that the query might still use cannot be replayed. If you have max_standby_streaming_delay = 0, that means that the query gets cancelled.

You can increase max_standby_streaming_delay to delay replay and let the query finish. Alternatively, you can set hot_standby_feedback = on on the standby to keep the primary from removing row versions that might still be needed on the standby, but you will still get replication conflicts on account of buffer pin or lock conflicts.

See this article fore a more detailed discussion.

8
  • Thank you Laurenz. Your article is by far the best source of information out there. I guess what is still unclear to me is what operations exactly cause the conflicts. For example why does running VACUUM create deleted rows if I'm not deleting any rows? I'm only ever appending. Does having a filter in the query that excludes rows that may be deleted help, or is there anything I can do on the query side?
    – LoveToCode
    Feb 22 at 17:06
  • When you run DELETE, the rows are just marked as deleted. VACUUM is a "garbage collection" that actually removes the rows. That hapens automatically in the background. If you only INSERT, that should not be a problem. What kinds of conflicts do you get? Snapshot? Buffer pin?, Lock? Feb 22 at 18:51
  • With the example above I get User query might have needed to see row versions that must be removed. even though I'm only INSERTing, not DELETEing. I obviously wouldn't be running VACUUM manually in production but it would run eventually so I'm just trying to make this test deterministic.
    – LoveToCode
    Feb 22 at 19:57
  • Yes, that is VACUUM. You must be updating or deleting something somewhere. Feb 23 at 6:40
  • This happens on vanilla postgres instances that I deployed especially for this test with zero data on them and no other connections. If I comment out the VACUUM line or the INSERT line the error goes away.
    – LoveToCode
    Feb 23 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.