Temp tables are considered as corrupted. about pg_page_verification HOT 6 OPEN

I suppose we can add check similar to https://github.com/google/pg_page_verification/blob/master/pg_page_verification.c#L156 but not sure if this is right thing to do.

from pg_page_verification.

I suppose you mean temp files (in pgsql_tmp directory), and not temporary tables?

from pg_page_verification.

I mean files which were created for temp tables CREATE TEMPORARY TABLE....

from pg_page_verification.

I don't have the issue for temporary tables, but I have it for temporary files.

from pg_page_verification.

Got an error that most likely describes this case.

I had a remote server mssql, I wanted to create foreign tables, and materialized views above it.
but due to incorrect options and types between mssql and postgresql fields, attempts to change options for foreign tables and refresh materialized views, tds_fdw.so library crashed, the instance crashed:
Jan 22 18:17:37 ua-dev-mongo kernel: traps: postgres[51520] general protection ip:7f8dbfac3eb3 sp:7ffd11fe94d0 error:0 in tds_fdw.so[7f8dbfabb000+12000]
but
`
2020-01-22 18:17:37.797 EET [6031] LOG: server process (PID 51520) was terminated by signal 11: Segmentation fault
2020-01-22 18:17:37.797 EET [6031] DETAIL: Failed process was running: select crm.refresh_dictionaries()
2020-01-22 18:17:37.797 EET [6031] LOG: terminating any other active server processes

crm.refresh_dictionaries() have 4 commands to refresh materialized view like:
refresh materialized view concurrently crm.Questionnaire_Questions with data;

After this case, the pg_page_verification found errors in two files of some:
ls -1 data/base/74465/t* data/base/74465/t77_1670499 data/base/74465/t77_1670502 data/base/74465/t77_1670504 data/base/74465/t77_1670505 data/base/74465/t77_1670508 data/base/74465/t77_1670510 data/base/74465/t77_1670511 (4 of them has zero size, 3 - 1 page 8k )

example of error pg_page_verification message ():
DEBUG: scanning segment filename: /opt/backup/pgsql/2020-01-23-0001/base/74465/t77_1670504 FILENAME: t77_1670504 DEBUG: filename: /opt/backup/pgsql/2020-01-23-0001/base/74465/t77_1670504[0] segmentBlockOffset: -87031808, maxSegmentSize: 1073741824, segmentNumber: 1670504, relative blkno: 0, absolute blkno: -87031808, checksum: fd9, phdr->pd_checksum: 9a88, phdr->pd_flags: 0, phdr->pd_lower: 48, phdr->pd_upper: 8176, phdr->pd_special: 8176, phdr->pd_pagesize_version: 8196, phdr->pd_prune_xid: 0 ERROR: corruption found in /opt/backup/pgsql/2020-01-23-0001/base/74465/t77_1670504[0], expected fd9, found 9a88 DEBUG: is_page_corrupted for /opt/backup/pgsql/2020-01-23-0001/base/74465/t77_1670504[0] returns: 1 DEBUG: direntry: /opt/backup/pgsql/2020-01-23-0001/base/74465/97756 - statbuf.st_mode: 33152 DEBUG: scanning segment filename: /opt/backup/pgsql/2020-01-23-0001/base/74465/97756

I do not have enough experience in administration postgresql, but I suppose that these are temporary files that should have been created and renamed to target filenames, as a result of the refresh materialized view. But as a result of the server crash, the full operation was not completed, but temporary files remained.

from pg_page_verification.

Those are files for temp tables and indexes. You should better use pg_verify_checksums or pg_checksums, depending on your PostgreSQL version.

from pg_page_verification.