Re: BUG #8656: Duplicate data violating unique constraints

On 2013-12-05 17:21:04 -0800, Maciek Sakrejda wrote:
> On Thu, Dec 5, 2013 at 1:15 AM, Andres Freund <andres@2ndquadrant.com>wrote:
> The original data has been deleted, but I did manage to do PITR to a point
> that exhibits the issue:

Cool..

> => 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 page_header(get_raw_page('post', page::integer)) from
> affected_pages;

Hm, could you rewrite that to:
...
select * FROM affectec_pages, page_header(get_raw_page('post', page::integer));

That should work in 9.3 and will give us a much more readable result.

> => 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 heap_page_items(get_raw_page('post', page::integer)) from
> affected_pages;

Same here.

Thanks!

Andres Freund

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