Re: Erroneous behavior of primary key

Copying the list this time.

On Tue, Aug 28, 2018 at 7:54 AM Daniel J Peacock <bluedanunit(at)gmail(dot)com>
wrote:

>
>
> On Mon, Aug 27, 2018 at 7:14 PM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
> wrote:
>
>> On 28 August 2018 at 07:50, Daniel J Peacock <bluedanunit(at)gmail(dot)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
>>
>