On Tue, Oct 24, 2017 at 01:14:53PM -0500, Justin Pryzby wrote: > I upgrade another instance to PG10 yesterday and this AM found unique key > violations. > > Our application is SELECTing FROM sites WHERE site_location=$1, and if it > doesn't find one, INSERTs one (I know that's racy and not ideal). We ended up > with duplicate sites, despite a unique index. We removed the duplicate rows > and reindexed fine. This is just a heads up with all the detail I can fit in > a > mail (but there's more if needed). > ... > 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? Regards, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers