Erroneous behavior of primary key
Good afternoon, all. 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. When I do a "select ,count(*) from group by 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? Thanks in advance for any insights. Dan Peacock Auto-wares, Inc.
Re: Erroneous behavior of primary key
Copying the list this time. On Tue, Aug 28, 2018 at 7:54 AM Daniel J Peacock wrote: > > > On Mon, Aug 27, 2018 at 7:14 PM David Rowley > wrote: > >> On 28 August 2018 at 07:50, Daniel J Peacock >> 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 , count(*) from group by having count(*) > 1 > and got results. I then ran select count(*) from > (select from group by ) as tab1; > and got a different result than select count(*) from table. > >> >> > When I do a "select ,count(*) from group by >> > 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 ,count(*) from group by 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 >> >
Re: Erroneous behavior of primary key
As a further update on this, we dropped the offending PKs, removed the duplicated rows using delete from where ctid in (select max(ctid) from group by having count(*) > 1), and then rebuilt the primary key. Thus far, we've seen no further corruption. We were wondering if it was a restore corruption of the index as we had dome some recovery testing, however, the same issue is evidenced in our production environment that hasn't been restored at all. Given that the tables and indexes were created automatically with Hibernate, we're wondering if that is the issue. Did something go wrong with the build of the objects initially and we didn't see it in our unit testing until now? We're not sure. So, this has become more of a thought experiment at this point. I attempted to intentionally corrupt a primary key in a throwaway dev box by creating a table with the same type (varchar(255)) as the id field, adding a PK on that field, loading a bit of data in, setting the indisvalid to false in pg_index (per this thread https://dba.stackexchange.com/questions/108393/intentionally-corrupting-an-index-in-postgresql) and attempting to insert the same data again. That test failed with a PK duplicate violation. I tried setting the indisready to false and then it would let me insert the duplicates. I turned the indisready and indisvalid back to true, but I can see the duplicated records in this test table, where in the original table, I could not. Are there any other ideas of how this might have happened as we are stumped? As a later test, we are going to spin up a db and app server to see if the issue crops up again. Unfortunately, that will have to wait until next week. Dan Peacock Auto-wares, Inc. On Mon, Aug 27, 2018 at 7:14 PM David Rowley wrote: > On 28 August 2018 at 07:50, Daniel J Peacock > 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? > > > When I do a "select ,count(*) from group by > > 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 ,count(*) from group by 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. > > 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]. > > [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 >
