[Occurrence Condition]
Occurs in connection with data corruption in the PostgreSQL TOAST table.
[Cause]
This may occur when an antivirus software monitors the directory used by PostgreSQL.
[Workaround]
If your antivirus software monitors the directory used by PostgreSQL, you can avoid it by excluding it from the monitoring target.
Alternatively, you can avoid this by periodically executing REINDEX and VACUUM ANALYZE of the corresponding table.
[Remedy]
If the error has already occurred while processing the asynchronous task, you can eliminate the error by identifying and deleting REINDEX and VACUUM ANALYZE of the corresponding table and the corrupted data.
Then, by the above workaround, you can avoid the subsequent error occurrence.
The following describes the procedure for identifying and deleting REINDEX and VACUUM ANALYZE of the corresponding table and the corrupted data.
1. Make a backup of the system database.
2. Perform REINDEX.
(1) Specify the target toast table.
Identify the target toast table from the error message
[Error message example]
===================
org.postgresql.util.PSQLException: ERROR: missing chunk number 0 for toast value 2859633 in pg_toast_112208
===================
In the case of the above message, "pg_toast_112208" is the target toast table.
(2) Identify the relation table of the toast table.
[PSQL example]
===================
=> select 112208::regclass;
regclass
--------------------
im_async_task_info
===================
In the above example, the relation is specified from "112208" of "pg_toast_112208".
(3) REINDEX the toast table and the relation table.
[PSQL example]
===================
= # GRANT usage ON schema pg_toast TO user; -- Grant authority to pg_toast to REINDEX execution user (executed by a superuser)
===================
===================
=> REtable pg_toast.pg_toast_112208; -- REINDEX toast table
=> REINDEX table im_async_task_info; -- REINDEX im_async_task_info
===================
3. Perform VACUUM and ANALYZE on the relation table.
[PSQL example]
===================
=> VACUUM ANALYZE im_async_task_info; -- VACUUM ANALYZE im_async_task_info
===================
If you get the above error, go to the next step.
If there are no errors, the process is complete.
4. Identify and delete corrupted data
(1) Identify the error row in the relation table identified in 2.
In the case of the following example, SELECT is executed one by one to identify the error occurrence line.
[PSQL example]
===================
=> SET client_min_messages TO notice; -- Settings for output to the console
=> DO $$
declare
v_rec record;
BEGIN
for v_rec in SELECT * FROM im_async_task_info ORDER BY message_id loop
RAISE NOTICE '%', v_rec.message_id;
end loop;
END;
$$
;
-- The data in the row where the error occurred is corrupted.
===================
(2) Delete the data in the row where the error occurred and the related data.
In the example below, "im_async_task_info” and "im_async_context_info" associated with "im_async_task_info" is deleted.
[PSQL example]
===================
=> DELETE FROM im_async_context_info WHERE identifier IN (SELECT context FROM im_async_task_info WHERE message_id = 'XXXXXX');
=> DELETE FROM im_async_task_info where message_id ='XXXXXX';
===================
(3) Check if the error has been resolved.
Execute 4-(1) again and check that there are no error lines.
If there is an error line, delete it as described in 4-(2).
5. Perform VACUUMFULL on the relation table.
[PSQL example]
===================
=> VACUUMFULL im_async_task_info; -- VACUUMFULL im_async_task_info
===================
The process is now complete.
-- Target ----------------------------------------------------------------------
iAP/Accel Platform/All Updates
--------------------------------------------------------------------------------
FAQID:1163
The error "Caused by: org.postgresql.util.PSQLException: ERROR: missing chunk number XXXX for toast value XXXX in pg_toast_XXXX" occurs when processing an asynchronous task.
