Re: Erroneous behavior of primary key

On Mon, Aug 27, 2018 at 7:14 PM David Rowley <david.rowley@2ndquadrant.com> wrote:

On 28 August 2018 at 07:50, Daniel J Peacock <bluedanunit@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