Come posso risolvere l'errore "annullamento dell'istruzione a causa di un conflitto con il ripristino" quando eseguo una query sulla replica di lettura per l'istanza database di RDS per PostgreSQL?

Breve descrizione

Questo errore potrebbe verificarsi a causa della mancanza di visibilità da parte dell'istanza primaria sull'attività in corso sulla replica di lettura. Il conflitto con il ripristino si verifica quando le informazioni WAL non possono essere applicate alla replica di lettura perché le modifiche potrebbero ostacolare un'attività in corso sulla replica di lettura.

Ad esempio, si supponga di eseguire un'istruzione DROP sull'istanza primaria quando un'istruzione SELECT a esecuzione prolungata è in esecuzione sulla replica di lettura nella tabella rilasciata nell'istanza primaria. Quindi, la replica di lettura ha due opzioni:

• Attesa del completamento dell'istruzione SELECT prima di applicare il record WAL. In questo caso, il ritardo di replica aumenta.
• Applicazione del record WAL e quindi annullamento dell'istruzione SELECT. In questo caso, viene visualizzato l'errore "annullamento dell'istruzione a causa di un conflitto con il recupero".

La replica di lettura risolve questi conflitti di replica in base al valore dei parametri max_standby_streaming_delay e max_standby_archive_delay. Il parametro max_standby_streaming_delay determina quanto tempo deve attendere la replica di lettura prima di annullare le query in standby che sono in conflitto con le voci WAL che stanno per essere applicate. Se l'istruzione in conflitto è ancora in esecuzione dopo questo periodo di tempo, PostgreSQL annulla l'istruzione e genera il seguente messaggio di errore:

ERROR: canceling statement due to conflict with recovery

Questo errore in genere viene visualizzato a causa di query a esecuzione prolungata sulla replica di lettura.

Nell'esempio precedente con l'istruzione DROP, la richiesta DROP viene memorizzata nel file WAL per essere applicata successivamente alla replica di lettura per coerenza. Si supponga che un'istruzione SELECT sia già in esecuzione sulla replica di lettura che tenta di recuperare i dati dall'oggetto eliminato con un runtime superiore al valore in max_standby_streaming_delay. Quindi, l'istruzione SELECT viene annullata in modo che l'istruzione DROP possa essere applicata.

Sessione 1 (replica di lettura) Esegui un'istruzione SELECT su example_table:

postgres=> SELECT * from example_table;

Sessione 2 (primaria) Esegui un'istruzione DROP su example_table:

postgres=> DROP TABLE example_table;

Quindi, viene visualizzato il seguente errore:

postgres@postgres:[27544]:ERROR:  canceling statement due to conflict with recovery
postgres@postgres:[27544]:DETAIL:  User was holding a relation lock for too long.
postgres@postgres:[27544]:STATEMENT:  select * from example_table;

Inoltre, potrebbe verificarsi un conflitto di query quando una transazione sulla replica di lettura sta leggendo tuple impostate per l'eliminazione sull'istanza primaria. L'eliminazione delle tuple seguita dall'aspirazione sull'istanza primaria causa un conflitto con la query SELECT ancora in esecuzione sulla replica. In questo caso, la query SELECT sulla replica viene terminata con il seguente messaggio di errore:

ERROR:  canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.

Risoluzione

Quando una replica di lettura incontra un conflitto e viene visualizzato l'errore "annullamento dell'istruzione a causa di un conflitto con il ripristino" nel registro degli errori, puoi impostare determinati parametri personalizzati in base al messaggio di errore in modo da ridurre l'impatto del conflitto. Tieni presente che i parametri personalizzati devono essere impostati sulla replica di lettura.

Viene visualizzato l'errore "annullamento dell'istruzione a causa di un conflitto con il ripristino" con "DETTAGLIO: l'utente ha mantenuto un blocco di relazione per troppo tempo"

max_standby_streaming_delay/max_standby_archive_delay: puoi utilizzare questi parametri per concedere più tempo prima di annullare le istruzioni di standby che sono in conflitto con le voci WAL che stanno per essere applicate. Questi valori rappresentano la quantità totale di tempo consentita per l'applicazione dei dati WAL dopo la ricezione dei dati dall'istanza primaria. Questi parametri vengono specificati in base a dove vengono letti i dati WAL. Se i dati WAL vengono letti dalla replica in streaming, utilizza il parametro max_standby_streaming_delay. Se i dati WAL vengono letti dalla posizione di archivio in Amazon Simple Storage Service (Amazon S3), utilizza il parametro max_standby_archive_delay.

Quando imposti questi parametri tieni presente quanto segue:

• Se i valori di questi parametri vengono impostati su -1, l'istanza di replica può attendere per sempre il completamento delle query in conflitto, aumentando il ritardo di replica.
• Se i valori di questi parametri vengono impostati su 0, le query in conflitto vengono annullate e le voci WAL vengono applicate all'istanza di replica.
• Il valore di default per questi parametri è impostato su 30 secondi.
• Se non specifichi l'unità durante l'impostazione di questi parametri, sarà utilizzato il millisecondo.

Regola i valori di questi parametri per bilanciare l'annullamento delle query o il ritardo di replica in base al caso d'uso.

Nota: se stai aumentando max_standby_archive_delay per evitare di annullare le query che sono in conflitto con la lettura delle voci di archivio WAL, è consigliabile aumentare anche max_standby_streaming_delay in modo da evitare cancellazioni collegate a conflitti con voci WAL in streaming.

Viene visualizzato l'errore "annullamento dell'istruzione a causa di un conflitto con il ripristino" con "DETTAGLIO: la query dell'utente potrebbe aver bisogno di vedere le versioni delle righe che devono essere rimosse"

hot_standby_feedback: se attivi questo parametro, i messaggi di feedback vengono inviati all'istanza primaria dalla replica di lettura con le informazioni sulla transazione attiva meno recente. Pertanto, l'istanza primaria non rimuove i record di cui potrebbe aver bisogno la transazione.

Se attivi questo parametro sulla replica di lettura, le query a esecuzione prolungata sulla replica di lettura potrebbero causare un aumento della tabella sull'istanza primaria. Questo perché le operazioni di vuoto non rimuovono le tuple morte che potrebbero essere richieste dalle query in esecuzione sulla replica di lettura. Questo parametro è disattivato per impostazione predefinita. Pertanto, presta attenzione quando attivi questo parametro.

Puoi inoltre controllare la vista pg_stat_database_conflicts nella replica di lettura per le statistiche sulle istruzioni annullate a causa di conflitti con il ripristino sulla replica di lettura.

---