PostgreSQL: ERROR: missing chunk number 0 for toast value 123456 in pg_toast_2619

Problem: After database migration

SELECT * FROM <table_name>

fails with the following message:

ERROR: missing chunk number 0 for toast value 123456 in pg_toast_2619

At first, I found https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a and https://newbiedba.wordpress.com/2015/07/07/postgresql-missing-chunk-0-for-toast-value-in-pg_toast/, but I could not isolate corrupted row, i.e. every query of the following form

SELECT * FROM <table_name> ORDER BY id LIMIT 5000 OFFSET 0;
SELECT * FROM <table_name> ORDER BY id LIMIT 5000 offset 5000;
... 

even

SELECT * FROM <table_name> ORDER BY id LIMIT 1;

failed with the same error message.

A second article (see https://fluca1978.github.io/2021/02/08/PostgreSQLToastCorruption) together with a set of Postgres functions for detecting corrupted toasts (see https://gitlab.com/fluca1978/fluca1978-pg-utils/-/blob/master/examples/toast/find_bad_toast.sql) inspired me how to find the table backed by `pg_toast_2619`, since it was already clear that the problem was not in my <table_name>:

SELECT reltoastrelid, reltoastrelid::regclass, pg_relation_filepath( reltoastrelid::regclass ), relname
FROM   pg_class
WHERE  reltoastrelid = 'pg_toast.pg_toast_2619'::regclass
  AND  relkind = 'r';

The result was pg_statistic and I executed the inverse query to recheck:

SELECT reltoastrelid, reltoastrelid::regclass, pg_relation_filepath( reltoastrelid::regclass ), relname
FROM   pg_class
WHERE  relname = 'pg_statistic'
  AND  relkind = 'r';

the result was pg_toast_2619. Hence the problem was not in <table_name> but in pg_statistic, more precisely in the TOAST of pg_statistic. But why the SELECT * FROM <tabel_name> generated this problem? Because Postgres uses pg_statistic to plan the query. Meanwhile a collegue of mine 

found https://www.ongres.com/blog/solving-postgresql-statistic-corruption/. This article shows how to deal with such cases but also shows that pg_toast_2619 is somehow constant between different Postgres installations.

What I learned from this problem:

1. How to find a table by its corresponding TOAST.
2. There are hidden actions behind a simple SELECT * FROM <tabel_name>, i.e. PostgreSQL  can perform a reading from another table first and then from the table `<tabel_name>` (not everything is as it seems at first sight).

PostgreSQL: ERROR: Key (id)=(4703) is duplicated. could not create unique index ""

Problem: After database migration

REINDEX <tabel_name>;

fails with the following message:

ERROR:  Key (id)=(4703) is duplicated. could not create unique index "<table_name>"

It's very strange because a simple

SELECT * FROM <table_name> WHERE id = 4703;

returns only one row. So where does REINDEX find the second (or more) row with id = 4703?

After searching in Google, with "Key (id)=(4703) is duplicated. could not create unique index", I found  

https://stackoverflow.com/questions/45315510/postgres-could-not-create-unique-index-key-is-duplicated, 

that inspired me to try 

SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;
SELECT * FROM <table_name> WHERE id = 4703;

This query returned two rows. To understand the difference in behavior modify both queries to execute an EXPLAIN SELECT * FROM <table_name> ... instead of SELECT * FROM <table_name> ... and you'll see that Postgres is using the index in the first query to search for the row, that is why 
we don't see the second row, it's not present in the index (by the definition of the index). While the second query is searching for it physically in the table and thus finds both rows.

Why there are two rows with the same id, is another question. My assumption is that one row was marked as deleted in the source database and somehow the data was corrupted and it appeared as non deleted in the destination database. The database was migrated byte by byte, not using .sql export/import.

What I learned from this problem:

How to force Postgres not to use (or use) table's index for a specific query.
A SELECT * FROM <table_name> in most cases is not a physical read of `<table_name>`, i.e. more generally, in Postgres, not everything is as it seems.