Queries are cancelled with "User query might have conflicted with replica reconnect." during 'CREATE INDEX CONCURRENTLY'

0

I have an Aurora PostgreSQL regional cluster database (version 15.5), with one writer instance and two reader instances. This database has a large partitioned table with 97 partitions of varying sizes. To avoid downtime caused by locking the table for reads/writes, I've used the following sequence of queries to create an index on the table:

CREATE INDEX my_new_index ON ONLY my_partitioned_table (column_one, column_two);

CREATE INDEX CONCURRENTLY my_new_index_partition_1 on my_partitioned_table_1 (column_one, column_type);
ALTER INDEX my_new_index ATTACH PARTITION my_new_index_partition_1;

... (repeated for each partition)

During the execution of some of the 'CREATE INDEX CONCURRENTLY' commands (which take minutes to hours to execute), I'm seeing queries being cancelled on read replicas with the following error (in the postgres cloudwatch log stream):

ERROR: Canceling statement due to conflict with recovery.
DETAIL: User query might have conflicted with replica reconnect.

I saw https://repost.aws/knowledge-center/rds-postgresql-error-conflict-recovery which described a similar "Canceling statement due to conflict with recovery." error. However, none of the described cases match the 'DETAIL' message that I'm seeing. Additionally, all of my queries ran for a very short time before being cancelled (10s of milliseconds, 84 milliseconds maximum). In my parameter group, I have max_standby_streaming_delay set to 14000, and hot_standby_feedback enabled, so I wouldn't expect to see these kinds of errors.

Is there any way that I can avoid these queries from occurring? The cancelled queries run inside a read-only transaction with SET commands, so it's non-trivial for my application to automatically retry the query when this error occurs.

No Answers

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions