Re: BUG #8656: Duplicate data violating unique constraints

On 2013-12-06 09:23:59 -0800, Maciek Sakrejda wrote:
> => with affected_pages as (
> select distinct regexp_replace(ctid::text, '\((\d+),\d+\)', '\1') as page
> from post where id in (select id from post group by id having count(*) >
> 1)
> ) select * from affected_pages, page_header(get_raw_page('post',
> page::integer));
> page | lsn | checksum | flags | lower | upper | special |
> pagesize | version | prune_xid
> -------+-------------+----------+-------+-------+-------+---------+----------+---------+-----------
> 28192 | 12/1B004250 | -1751 | 1 | 304 | 1712 | 8192 |
> 8192 | 4 | 0
> 28194 | 12/1D1304D8 | 10886 | 1 | 316 | 1352 | 8192 |
> 8192 | 4 | 0
> 28203 | 12/BE2BDF8 | -12970 | 0 | 284 | 1592 | 8192 |
> 8192 | 4 | 0
> 28204 | 12/BE2DDE0 | 1235 | 0 | 284 | 392 | 8192 |
> 8192 | 4 | 0
> 28281 | 12/BE33218 | -7837 | 0 | 284 | 392 | 8192 |
> 8192 | 4 | 0
> 28390 | 12/BE4CF70 | -20573 | 0 | 284 | 392 | 8192 |
> 8192 | 4 | 0
> 28392 | 12/1D135E78 | -8032 | 1 | 300 | 992 | 8192 |
> 8192 | 4 | 0
> 28410 | 12/BE67610 | 23108 | 0 | 284 | 872 | 8192 |
> 8192 | 4 | 0
> 28561 | 12/1D164030 | -24596 | 0 | 284 | 392 | 8192 |
> 8192 | 4 | 0
> (9 rows)
>
> => with affected_pages as (
> select distinct regexp_replace(ctid::text, '\((\d+),\d+\)', '\1') as page
> from post where id in (select id from post group by id having count(*) >
> 1)
> ) select * from affected_pages, heap_page_items(get_raw_page('post',
> page::integer));
> page | lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 |
> t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid
> -------+----+--------+----------+--------+--------+--------+----------+------------+-------------+------------+--------+--------+-------
> 28192 | 1 | 8072 | 1 | 118 | 18099 | 0 | 0 |
> (28192,1) | 12 | 2306 | 24 | |
> 28192 | 2 | 7952 | 1 | 118 | 18099 | 80873 | 13 |
> (28192,2) | 12 | 6466 | 24 | |
> 28192 | 3 | 7832 | 1 | 118 | 18099 | 80873 | 12 |
> (28192,3) | 12 | 6466 | 24 | |
> 28192 | 4 | 0 | 3 | 0 | | |

To me this pretty clearly indicates the multixact vacuuming bug from
9.3.2. But given your pg_controldata output:
Latest checkpoint's NextXID: 0/1579944
Latest checkpoint's NextMultiXactId: 592631
Latest checkpoint's NextMultiOffset: 1236955
with of ~600k multis used so far, I cannot see how it could have been
triggered without either nondefault vacuum settings or a vacuum freeze.

Do you perhaps automatedly run VACUUM with different settings from
crontab or similar? Or have per-table vacuum settings?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services