Re: unique index violation after pg_upgrade to PG10

On Tue, Oct 24, 2017 at 01:30:19PM -0500, Justin Pryzby wrote:
> On Tue, Oct 24, 2017 at 01:27:14PM -0500, Kenneth Marshall wrote:
> > On Tue, Oct 24, 2017 at 01:14:53PM -0500, Justin Pryzby wrote:
>
> > > Note:
> > > I run a script which does various combinations of ANALYZE/VACUUM (FULL/ANALYZE)
> > > following the upgrade, and a script runs nightly with REINDEX and pg_repack
> > > (and a couple of CLUSTER), so you should assume that any combination of those
> > > maintenance commands have been run.
> > >
> > > In our reindex/repack log I found the first error due to duplicates:
> > > Tue Oct 24 01:27:53 MDT 2017: sites: sites_idx(repack non-partitioned)...
> > > WARNING: Error creating index "public"."index_61764": ERROR: could not create unique index "index_61764"
> > > DETAIL: Key (site_office, site_location)=(CRCLMT-DOEMS0, 1120) is duplicated.
> > > WARNING: Skipping index swapping for "sites", since no new indexes built
> > > WARNING: repack failed for "sites_idx"
> > > reindex: warning, dropping invalid/unswapped index: index_61764
> > >
> >
> > Hi Justin,
> >
> > This sounds like a pg_repack bug and not a PostgreSQL bug. What version are
> > you running?
>
> Really ? pg_repack "found" and was victim to the duplicate keys, and rolled
> back its work. The CSV logs clearly show that our application INSERTed rows
> which are duplicates.
>
> [pryzbyj(at)database ~]$ rpm -qa pg_repack10
> pg_repack10-1.4.2-1.rhel6.x86_64
>
> Justin

Hi Justin,

I just dealt with a similar problem with pg_repack and a PostgreSQL 9.5 DB,
the exact same error. It seemed to caused by a tuple visibility issue that
allowed the "working" unique index to be built, even though a duplicate row
existed. Then the next pg_repack would fail with the error you got. In our
case I needed the locality of reference to keep the DB performance acceptable
and it was not a critical issue if there was a duplicate. We would remove the
duplicates if we had a failure. We never had a problem with the NO pg_repack
scenarios.

Regards,
Ken