M
M
Mikhail Beschetnov2014-06-26 10:36:43
PostgreSQL
Mikhail Beschetnov, 2014-06-26 10:36:43

How can duplicate records appear in tables with a unique constraint in PostgreSQL 9.3.4?

Straight trouble.
It was on previous versions, but at the moment the latest one is available from the 9.3 branch.
The point is the following. There is a table (without triggers, foreign keys and rules). A unique constraint was imposed on the table (on one field, but in other problematic tables there were several).
From time to time (once every few weeks) duplicate rows are suddenly found in the tables, which should not have been there due to the presence of a unique constraint.
Moreover, there is reason to believe that this happens precisely when inserting a record, because a sequence is attached to one of the fields, and it is "fresh" in duplicates, i.e. at an insertion of record as though the restriction breaks through.
Has anyone encountered something similar?
DDL:

CREATE TABLE "public"."test" (
"remoteId" int8,
"title" text NOT NULL,
"localId" int4 DEFAULT nextval('"test_localId_seq"'::regclass) NOT NULL,
CONSTRAINT "uniqueRemoteId" UNIQUE ("remoteId")
)
WITH (OIDS=FALSE)
;

Duplicates, respectively, in style:
15,Название,8
15,Название,259

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Mikhail Beschetnov, 2014-06-27
@TerminusMKB

The trouble (probably) is that there is no replication.
This table is processed (for writing) as follows.
Once every 10 minutes, several thousand values ​​are written to it. Almost all the values ​​are already there at the time of recording, so the unique constraint works every time for almost every insertion. And there are two thousand such insertions at a time.
If we had triggers falling off (in theory), there would be duplicates by the number of inserts, because, to put it mildly, a lot. And there are, say, 2 lines per week :)
I'll try to wait for more options, but for now I'll make a fictitious non-working table with a restriction and I'll try to put duplicates there around the clock.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question