3

I am trying to create a stored procedure to be used in a PostgreSQL DBMS.

The purpose of this stored procedure is to delete all records that present the following problem...

Query:

my_database=# SELECT file INTO my_file_now FROM public.my_datatable WHERE my_id='2fdf5297-8d4a-38bc-bb26-b8a4b7ba47ec';
ERROR:  missing chunk number 0 for toast value 3483039 in pg_toast_3473493

Based on the above behavior I created the following stored procedure:

Stored procedure:

DO $f$
DECLARE
    my_file_now BYTEA;
    my_id_now UUID;
BEGIN
FOR my_id_now IN SELECT my_id FROM public.my_datatable LOOP
    BEGIN
        SELECT file
            INTO my_file_now
            FROM public.my_datatable WHERE my_id=my_id_now;
        EXCEPTION
            WHEN OTHERS THEN
            RAISE NOTICE 'CORRUPTED MY_ID - % ', my_id_now;
            DELETE FROM public.my_datatable WHERE my_id=my_id_now;
    END;
END LOOP;
END;
$f$;

QUESTION: Why is the error observed in the query not caught by the "EXCEPTION" block in the stored procedure?

Thanks! =D

Improve this question

3 Answers 3

Reset to default
1

I think that the error is caught by the exception block.

The error you observe comes from the DELETE statement in the exception handler.

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Unfortunately the block within "EXCEPTION" is not fired. Thanks!
If you force an error in the SELECT file [...] block, you can prove what I say.
I cannot prove it, because I don't have a corrupted database at hand, but you can if you add a RAISE NOTICE in the exception handler before the DELETE. If the notice is displayed, I was right.
This behavior is very strange because the exception "WHEN OTHERS THEN" catches any exception and it actually occurs. I really don't know what happens. =\
Exceptions that are thrown in the exception handler are not caught by the exception handler.
|
0

In our view there is really no reason for the block in "EXCEPTION" not to be executed. Sounds like a bug to us. We reported this to the PostgreSQL community in their mailing list ( [email protected] ).

Given the impossibility of using the above stored procedure I created a bash script with a much, much more sophisticated approach (see below).

Refers to the "CONFIGURATION SECTION" section for more information, to make your adjustments and test the bash script before applying changes to your table.

The "CONFIGURATION SECTION" section is at the end of the script. At first it is sufficient only make adjustments to it.

#!/bin/bash

# NOTE: Avoid problems with relative paths! By Questor
SCRIPTDIR_V="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

F_GET_STDERR_R=""
F_GET_STDOUT_R=""
F_GET_EXIT_CODE_R=0
f_get_stderr_stdout() {
    : 'Run a command and capture output from stderr, stdout and exit code

    Args:
        CMD_TO_EXEC (str): Command to be executed.

    Returns:
        F_GET_STDERR_R (str): Output to stderr.
        F_GET_STDOUT_R (str): Output to stdout.
        F_GET_EXIT_CODE_R (int): Exit code.
    '

    CMD_TO_EXEC=$1
    F_GET_STDERR_R=""
    F_GET_STDOUT_R=""
    unset t_std t_err t_ret
    eval "$( eval "$CMD_TO_EXEC" 2> >(t_err=$(cat); typeset -p t_err) > >(t_std=$(cat); typeset -p t_std); t_ret=$?; typeset -p t_ret )"
    F_GET_STDERR_R=$t_err
    F_GET_STDOUT_R=$t_std
    F_GET_EXIT_CODE_R=$t_ret
}

f_log_manager() {
    : 'Generate and manage output and error logs.

    Args:
        VALUE_TO_INSERT (str): Value to insert into log file.
        LOG_TYPE (Optional[int]): 0 - Output log; 1 - Error log. Default 0.
        CREATE_NEW_LOG (Optional[int]): 0 - Inserts into existing log file; 
    1 - Creates a new log file. Default 0.
        PATH_TO_LOG (Optional[str]): Folder path to create log file (without "/" 
    at the end). If empty, a new log file will be created in the current folder 
    (value in "SCRIPTDIR_V").
        VAL_INS_ON_SCREEN (Optional[int]): 0 - Will not print "VALUE_TO_INSERT" 
    on screen; 1 - Will print "VALUE_TO_INSERT" on screen. Default 0.
    '

    VALUE_TO_INSERT=$1
    LOG_TYPE=$2
    CREATE_NEW_LOG=$3
    PATH_TO_LOG=$4
    VAL_INS_ON_SCREEN=$5

    if [ -z "$LOG_TYPE" ] ; then
        LOG_TYPE=0
    fi
    if [ -z "$CREATE_NEW_LOG" ] ; then
        CREATE_NEW_LOG=0
    fi
    if [ -z "$PATH_TO_LOG" ] ; then
        PATH_TO_LOG="$SCRIPTDIR_V"
    fi
    if [ -z "$VAL_INS_ON_SCREEN" ] ; then
        VAL_INS_ON_SCREEN=0
    fi

    LOG_FILE_NAME=""
    case $LOG_TYPE in
        0)
            LOG_FILE_NAME="$PATH_TO_LOG/output.log"
        ;;
        1)
            LOG_FILE_NAME="$PATH_TO_LOG/error.log"
        ;;
    esac

    LOG_APPEND_OR_CREATE=""
    case $CREATE_NEW_LOG in
        0)
            LOG_APPEND_OR_CREATE=">>"
        ;;
        1)
            LOG_APPEND_OR_CREATE=">"
        ;;
    esac

    if [[ ${VAL_INS_ON_SCREEN} -eq 1 ]]; then
        echo "$VALUE_TO_INSERT"
    fi

    eval "echo \"$VALUE_TO_INSERT\" $LOG_APPEND_OR_CREATE $LOG_FILE_NAME"
}

# NOTE: Will search across all table rows for corrupted records. Corrupted records 
# will be basically all records in which a field with a FILE ("BYTEA") cannot be 
# returned with its value. By Questor
f_chk_corrupted() {
    f_log_manager " > [chk_corrupted] >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" 0 0 "" 1

    # NOTE: Get the number of records from the table. By Questor
    f_get_stderr_stdout "sudo -u postgres psql $TARGET_BASE -c \"SELECT COUNT(*) FROM $TARGET_TABLE;\""

    TBL_ROWS_QUANTITY=$(echo "$F_GET_STDOUT_R" | sed '3!d')
    TBL_ROWS_QUANTITY="${TBL_ROWS_QUANTITY#"${TBL_ROWS_QUANTITY%%[![:space:]]*}"}"

    if [ ${TEST_MODE} -eq 1 ] ; then

        # NOTE: GOOD FOR TESTING BEFORE DELETING EFFECTIVELY. By Questor
        TBL_ROWS_QUANTITY=$T_MODE_ROWS

    fi

    for (( i=0; i<$TBL_ROWS_QUANTITY; i++ )); do
        f_log_manager " > [rom_pos: $i] ---------------------------" 0 0 "" 1

        # NOTE: Search the row for its position and test if the row has problems 
        # trying to retrieve the file field. By Questor
        f_get_stderr_stdout "sudo -u postgres psql $TARGET_BASE -c \"SELECT $TARGET_TABLE_FILE_FLD FROM $TARGET_TABLE LIMIT 1 OFFSET $i;\""

        if [[ ${F_GET_EXIT_CODE_R} -ne 0 ]] && [[ $F_GET_STDERR_R == *" chunk number "* ]]; then

            # NOTE: If the row has problems, then fetch the row by its position and 
            # retrieve the indicated id field. By Questor
            f_get_stderr_stdout "sudo -u postgres psql $TARGET_BASE -c \"SELECT $TARGET_TABLE_ID_FLD FROM $TARGET_TABLE LIMIT 1 OFFSET $i;\""

            # NOTE: Handle the output (id field) and insert input in the file 
            # "ids_corrpt_files.txt". The "ids_corrpt_files.txt" file has the rows 
            # that will be deleted according to the criteria. By Questor
            ID_CORRPT_FILE=$(echo "$F_GET_STDOUT_R" | sed '3!d')
            ID_CORRPT_FILE="${ID_CORRPT_FILE#"${ID_CORRPT_FILE%%[![:space:]]*}"}"
            echo "$ID_CORRPT_FILE" >> ids_corrpt_files.txt

            # NOTE: Corrupted row. Logging. By Questor
            f_log_manager "CORRUPTED --- $ID_CORRPT_FILE" 0 0 "" 1

        else

            # NOTE: NOT corrupted row . Logging. By Questor
            f_log_manager "UNCORRUPTED" 0 0 "" 1

        fi
        f_log_manager " < ---------------------------" 0 0 "" 1
    done
    f_log_manager " < [chk_corrupted] <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<" 0 0 "" 1
}

# NOTE: Delete the rows registered in the "ids_corrpt_files.txt" file in the previous step. By Questor
f_del_corrupted() {
    f_log_manager " > [f_del_corrupted] >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" 0 0 "" 1
    while IFS="" read -r p || [ -n "$p" ]; do
        ID_CORRPT_FILE="$p"
        f_log_manager " > [id_file: $ID_CORRPT_FILE] ---------------------------" 0 0 "" 1

        if [ ${TEST_MODE} -eq 1 ] ; then

            # NOTE: GOOD FOR TESTING BEFORE DELETING EFFECTIVELY. By Questor
            f_get_stderr_stdout "sudo -u postgres psql $TARGET_BASE -c \"SELECT $TARGET_TABLE_ID_FLD FROM $TARGET_TABLE WHERE $TARGET_TABLE_ID_FLD='$ID_CORRPT_FILE';\""

        else

            # NOTE: Deleta a row. By Questor
            f_get_stderr_stdout "sudo -u postgres psql $TARGET_BASE -c \"DELETE FROM $TARGET_TABLE WHERE $TARGET_TABLE_ID_FLD='$ID_CORRPT_FILE';\""
        fi

        # NOTE: Logging on success or error. By Questor
        if [[ ${F_GET_EXIT_CODE_R} -ne 0 ]]; then
            f_log_manager "DELETED ----- ERROR" 0 0 "" 1
        else
            f_log_manager "DELETED ----- OK" 0 0 "" 1
        fi

        f_log_manager " < ---------------------------" 0 0 "" 1
    done < ids_corrpt_files.txt
    f_log_manager " < [f_del_corrupted] <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<" 0 0 "" 1
}

# NOTE: Remove log files from possible previous operations. By Questor
rm -f "/var/tmp/error.log"
rm -f "/var/tmp/output.log"

# NOTE: Remove file "ids_corrpt_files.txt" from possible previous operations. 
# By Questor
rm -f "./ids_corrpt_files.txt"

# NOTE: INFORM YOUR PARAMETERS IN THIS SECTION! By Questor
# > -----------------------------------------------------------------
# CONFIGURATION SECTION
# ---------------------------------

# IMPORTANT: This scrit DELETES UNRECOVERABLE RECORDS that have problems related 
# to "chunk number" ("toast value"). FIRST TRY TO EXECUTE PROCEDURES TO RECOVER 
# YOUR RECORDS AND EXECUTING THIS SCRIPT AS A LAST STEP IF NECESSARY! By Questor

# NOTE: Base with problematic table. By Questor
TARGET_BASE="my_database"

# NOTE: Problematic table . By Questor
TARGET_TABLE="my_table"

# NOTE: Problematic table ids. By Questor
TARGET_TABLE_ID_FLD="my_id"

# NOTE: Problematic file field name ("BYTEA"). By Questor
TARGET_TABLE_FILE_FLD="my_file"

# NOTE: IN TEST MODE YOU CAN CHECK CODE OPERATION WITH YOURS PARAMETERS WITHOUT 
# DELETING RECORDS/MODIFYING DATA. WE STRONGLY RECOMMEND USING THIS MODE FIRST 
# FOR TESTING! By Questor
TEST_MODE=1

# NOTE: How many rows you want to test in test mode. By Questor
T_MODE_ROWS=10

# < -----------------------------------------------------------------

# NOTE: Triggers the two main methods of the script. By Questor
f_chk_corrupted
f_del_corrupted

exit 0

[Refs.: https://www.youtube.com/watch?v=4jcC-lYGM0k , https://www.mail-archive.com/[email protected]&q=subject:%22Re%5C%3A+unexpected+chunk+number%22&o=newest&f=1 , https://raghavt.blog/fixing-up-a-corrupted-toast-table/ , https://dba.stackexchange.com/questions/31008/pg-dump-and-error-missing-chunk-number-0-for-toast-value , Postgres log file contains: missing chunk number 0 for toast value 815441 in pg_toast_2619 , https://postgrespro.com/list/thread-id/2324305 , https://www.postgresql.org/docs/8.4/storage-toast.html , https://www.postgresql-archive.org/pg-dump-error-td2077707.html , https://postgrespro.com/list/thread-id/1151339 , https://oraerr.com/database/postgre/pg_dump-and-error-missing-chunk-number-0-for-toast-value/ , https://postgrespro.ru/list/thread-id/1207970 , https://newbiedba.wordpress.com/2015/07/07/postgresql-missing-chunk-0-for-toast-value-in-pg_toast/ , https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a ]

Improve this answer

Comments

0

I thought it would be useful to post here some answers obtained in one of the official email lists of PG ( [email protected], https://lists.postgresql.org/ ).

Tom's Answers:

  • QUESTION
my_database=# SELECT file INTO my_file_now FROM public.my_datatable WHERE

my_id='2fdf5297-8d4a-38bc-bb26-b8a4b7ba47ec'; ERROR: missing chunk number 0 for toast value 3483039 in pg_toast_3473493

  • ANSWER

We've fixed a few bugs over the years that manifest in that type of problem --- are you up to date on minor releases? It's also possible that reindexing that toast table would fix it.

  • QUESTION

Based on the above behavior I created the following stored procedure:

...
my_file_now BYTEA;
...
SELECT file
    INTO my_file_now
    FROM public.my_datatable WHERE my_id=my_id_now;

QUESTION: Why is the error observed in the query not caught by the "EXCEPTION" block in the stored procedure?

  • ANSWER

I think that plpgsql will not bother to dereference a TOAST pointer when storing it into a local variable (although this statement is very possibly version-dependent, and you didn't say what PG version you are using).

A more reliable way to trigger the problem is to do some computation that requires the value of the field, perhaps along the lines of

PERFORM md5(file) FROM public.my_datatable WHERE my_id=my_id_now;
EXCEPTION
    WHEN OTHERS THEN
    RAISE NOTICE 'CORRUPTED MY_ID - % ', my_id_now;
    DELETE FROM public.my_datatable WHERE my_id=my_id_now;

I don't know that I'd give a procedure like this license to delete my entire table :-(. If you really don't care how much data survives, why not just TRUNCATE the table and be done with it? Otherwise, printing the list of troublesome rows for manual review seems way more prudent.

David's Answers:

  • QUESTION

Thanks for the suggestions! I found it a bit strange "pgsql" not to "understand" as an exception the "ERROR: missing chunk number 0 for toast value 3483039 in pg_toast_3473493"

  • ANSWER

I think Tom's point is that the function you wrote never actually attempted to print out the value of the field so the error never got triggered in the function. You need to actually attempt to manipulate the data to get an error. If you did get the function to actually encounter the error it should (haven't tested myself) be caught in the exception handler; i.e., "A more reliable way to trigger the problem is to do some computation that requires the value of the field, perhaps along the lines of [query to try]".

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.