Percona Live Page Inspection

page inspection

-- Narrowed down to the bad paged by selecting reviews_2018 data with ctid's between X and Y
--  until we found the last page with an error

 select ctid,id from reviews_2018  where ctid between '(382603,0)'::tid and '(382604,16)'::tid;
 select ctid,id from reviews_2018 where id = 'a4868b2d47f697147d4e57cc476a4caf';
/*
   ERROR:  MultiXactId 1483293027 has not been created yet -- apparent wraparound
*/

-- Once we found the first bad row, we grabbed the page id (ctid)
--   Note that the data has since been fixed, so the ctid below has changed
/*
 select ctid,id from reviews_2018 where id = 'a4868b2d47f697147d4e57cc476a4caf';
    ctid    |                id                
------------+----------------------------------
 (338952,1) | a4868b2d47f697147d4e57cc476a4caf
(1 row)

*/ 

/*
 Enable page inspection  
  - Note: must be done by user with SUPER privs in the database being inspected
*/
CREATE EXTENSION pageinspect;

/* 
 Dump raw data info from specific page
*/
 select lp, lp_flags, t_xmin, t_xmax, t_field3, t_ctid,
        to_hex(t_infomask) as infomask, to_hex(t_infomask2) as infomask2
   from heap_page_items(get_raw_page('reviews_2018', 382604)) h;
 

-- what the inspection showed

/*
 lp | lp_flags |   t_xmin   |   t_xmax   |  t_field3  |       t_ctid       | infomask | infomask2
----+----------+------------+------------+------------+--------------------+----------+-----------
  1 |        1 |          0 | 2457403396 |    1048582 | (151256864,239)    | 200      | 0
  2 |        2 |            |            |            |                    |          |
  3 |        2 |            |            |            |                    |          |
  4 |        1 |   25532455 |   26137203 |          0 |        (382604,15) | 2102     | c010
  5 |        1 |   25532455 |   26137203 |          0 |        (382604,16) | 2103     | c010
  6 |        1 | 1682137913 |  862335590 |  959460400 | (892612660,26210)  | 6439     | 6562
  7 |        1 | 1179009631 | 1483293027 | 1382768435 | (1717064302,26444) | 3133     | 4c76
  8 |        1 | 1412509997 |  825899313 |  943012409 |     (959330611,90) | 0        | 0
  9 |        1 |  862073140 | 1701209657 | 1265002032 | (1697722679,25911) | 652d     | 3031
 10 |        1 |   25518614 |          0 |          0 |        (382604,10) | 2903     | 8010
 11 |        2 |            |            |            |                    |          |
 12 |        2 |            |            |            |                    |          |
 13 |        2 |            |            |            |                    |          |
 14 |        2 |            |            |            |                    |          |
 15 |        1 |   26137203 |          0 |          0 |        (382604,15) | 2802     | 8010
 16 |        1 |   26137203 |          0 |          0 |        (382604,16) | 2903     | 8010
 17 |        0 |            |            |            |                    |          |
(17 rows)
*/

--  what the inspection should have shown
   select lp, lp_flags, t_xmin, t_xmax, t_field3, t_ctid,
        to_hex(t_infomask) as infomask, t_infomask2 as infomask2
   from heap_page_items(get_raw_page('reviews_2018', 338952)) h;
/*
 lp | lp_flags |  t_xmin   | t_xmax | t_field3 |   t_ctid    | infomask | infomask2 
----+----------+-----------+--------+----------+-------------+----------+-----------
  1 |        1 |  59125794 |      0 |        0 | (338952,1)  | 2b03     |     32784
  2 |        1 | 199253851 |      0 |        0 | (338952,2)  | 2903     |     32784
  3 |        1 |  31787174 |      0 |        0 | (338952,3)  | b03      |        16
  4 |        2 |           |        |          |             |          |          
  5 |        1 | 199120201 |      0 |        0 | (338952,5)  | 2903     |     32784
  6 |        1 |  31787174 |      0 |        0 | (338952,6)  | b03      |        16
  7 |        1 | 198980747 |      0 |        0 | (338952,7)  | 2903     |     32784
  8 |        1 | 199303235 |      0 |        0 | (338952,8)  | 2902     |     32784
  9 |        2 |           |        |          |             |          |          
 10 |        2 |           |        |          |             |          |          
 11 |        1 |  31787174 |      0 |        0 | (338952,11) | b03      |        16
 12 |        2 |           |        |          |             |          |          
 13 |        2 |           |        |          |             |          |          
 14 |        1 | 199352159 |      0 |        0 | (338952,14) | 2902     |     32784
 15 |        2 |           |        |          |             |          |          
 16 |        0 |           |        |          |             |          |          
 17 |        1 |  73711783 |      0 |        0 | (338952,17) | b03      |        16
 18 |        0 |           |        |          |             |          |          
 19 |        0 |           |        |          |             |          |          
 20 |        0 |           |        |          |             |          |          
 */

/*
 What the dump showed 
 https://www.postgresql.org/message-id/attachment/95823/raw_hexdump.txt
*/

/*
 What the actual record _should_ have looked like:
*/

select * from reviews_2018 where id = 'a4868b2d47f697147d4e57cc476a4caf';  

Torn Page at x1000, or 4k, so suggestion was some bug/issue outside of postgres since postgres writes in 8k pages
 - possibly some know VMWare bugs

/*
  Create new table for partition
*/

select count(*) from reviews where ts between '2018-01-01 00:00:00' AND '2018-12-31 23:59:59';

CREATE TABLE reviews_2018_new (LIKE reviews_2018 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);

\d reviews_2018_new

/*
 Load new table ( Note: we loaded the table from a restored backup )
*/

insert into reviews_2018_new select * from reviews_2018 limit 10;

select count(*) from reviews_2018_new;

/*
  Disable bad partition
*/

ALTER TABLE reviews DETACH PARTITION reviews_2018;

/*
 Attach good partition
*/

ALTER TABLE reviews ATTACH PARTITION reviews_2018_new FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00' ); 

/*
 Validate data
*/

 \d+ reviews
 
 select count(*) from reviews where ts between '2018-01-01 00:00:00' AND '2018-12-31 23:59:59';