Re: vacuumlo fails pgsql ver 8.3

Pat Heuvel <pheuvel@tpg.com.au> writes:
> On 6/02/2012 4:39 AM, Tom Lane wrote:
>> What exactly happens when you try to reindex pg_largeobject?

> ERROR:  could not create unique index "pg_largeobject_loid_pn_index"
> DETAIL:  Table contains duplicated values.

Could be worse.  What you'll need to do is look through the
pg_largeobject catalog for duplicated (loid, pageno) values, and
manually DELETE the redundant rows, or else reassign them new OIDs
if you want to keep the data.

A tip for issuing the removal commands is to use the CTID column to
distinguish otherwise-identical rows, ie you could do something
like
    select ctid, loid, pageno from pg_largeobject
    where (loid, pageno) in (select loid, pageno from pg_largeobject
                group by 1, 2 having count(*) > 1);
    ... examine results ...
    delete from pg_largeobject where ctid = '...';

I believe you'll need to do any direct DELETE or UPDATE on the
catalog as superuser.

            regards, tom lane