Copying the list this time. On Tue, Aug 28, 2018 at 7:54 AM Daniel J Peacock <bluedanu...@gmail.com> wrote:
> > > On Mon, Aug 27, 2018 at 7:14 PM David Rowley <david.row...@2ndquadrant.com> > wrote: > >> On 28 August 2018 at 07:50, Daniel J Peacock <bluedanu...@gmail.com> >> wrote: >> > I've got an odd situation with a table that has a varchar(255) as the >> > primary key that is getting key values from an Elasticsearch engine. >> What >> > I'm finding is that even though there is a primary key on the table, I >> am >> > getting duplicated keys in the data and the constraint is not blocking >> > these. >> >> How do you know they're duplicated? >> > I did a create table as select, got the same number of rows , did the > select <key>, count(*) from <newtable> group by <key> having count(*) > 1 > and got results. I then ran select count(*) from > (select <key field> from <original table> group by <key field>) as tab1; > and got a different result than select count(*) from <original> table. > >> >> > When I do a "select <pk_field>,count(*) from <table> group by >> > <pk_field> having count(*) > 1" I get no results. Yet, when I search >> the >> > table for a value that is like a key I know to be duplicated, I get >> multiple >> > results. When I select from the table where field is equal to the >> > duplicated field I get one result. I verified that they are distinct >> row >> > with ctid. I also created a clone of the table with CTAS and then >> tried to >> > create a unique index on the id varchar field but that failed with >> > "duplicate keys found". I'm stumped as to what could be the problem. >> > The only thing that I can think of is that the primary key is somehow >> > corrupt. I've noticed this behavior on other tables on this database. >> > What could be causing this sort of problem? >> >> If the index is corrupt then you might find that: >> >> set enable_indexscan = 0; >> set enable_indexonlyscan = 0; >> select <pk_field>,count(*) from <table> group by <pk_field> having >> count(*) > 1; >> >> would return some rows. You should also verify the above query does >> use a Seq Scan by performing an EXPLAIN on the query. >> > > I executed the above and it does return rows. So, it's looking like > corrupted indexes. The next question is "How?" > >> >> There are issues that have been fixed in previous releases which could >> have caused an index to get corrupted in this way, so it's quite >> useful to know which version of PostgreSQL you're running here and if >> you've paid attention to the release notes when you've previously >> upgraded. For example, see [1]. >> > > Ack, newbie mistake there. This was a fresh install of 10.3 via a Docker > Container image using the base postges image. The schema was created via a > Hibernate layer. > > >> [1] https://www.postgresql.org/docs/9.6/static/release-9-6-2.html >> >> -- >> David Rowley http://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Training & Services >> >
