We see records in a table that violate primary key and unique key constraints. What could have caused it? How can we track the problem (what needs to be logged/recorded to understand the situation better)?
We are surveilling over a few hundred (if not thousands) of Postgres (version 9.5.1) databases in production. Every now and then, in one database or the other, some tables get rows that violate primary key constraint.
select ctid, id
(where id has a primary key constraint) returns
(0,29);"fb2f529b-87b3-46ff-90ca-e70096c7a08a"
(0,28);"fb2f529b-87b3-46ff-90ca-e70096c7a08a"
(0,26);"fb2f529b-87b3-46ff-90ca-e70096c7a08a"
(0,24);"fb2f529b-87b3-46ff-90ca-e70096c7a08a"
deleting the rows using the ctid works nicely, while accessing the data using the id results in weird errors (due to the violated constraint).
The rows might be inserted using different concurrent sessions. The select is run in a separate database connection. The database is used heavily over JavaBeans. Backing and restoring of the content of the DB could have taken place (in weird ways).
The id have the following type/constraints
CREATE TABLE tbl1
(
id uuid NOT NULL,
att1 character varying(255) NOT NULL,
CONSTRAINT pk_fesert PRIMARY KEY (id)
)
I cannot post complete tables / records as there personally identifiable information there. The particular table receives hardly ever update traffic except when exceptions happen (certain components in JEE application fail in the distributed environment).
It could be a bug in postgres, but we do not even know how to even start to track it. Among numerous installation of postgres this happens at most a few times per year. Could this be related to backup-restore? Is it possible to pause constraints in postgres in some way that are not bound to a specific transaction? Under which circumstances can happen that multiple rows are inserted into a table that may violate constraints?
OS: Windows Server (2016), runs in a virtualization environment (VMware ESXi 6.0).
Update:
One a different occasion after a power failure we ended up with a database of inconsistent state. I am gathering the logs now, but we see is that a table, that is supposed to have a record does not have any. Inserting a record (the record that was there prior to incident) into the table throws a primary key constraint violation. There are a few dozen of foreign key constraints that are currently violated. Comments?
