PostgreSQL duplicate key violates unique constraint error

postgresqlunique-constraint

CentOS 6.2 64bit Server
PostgreSQL 8.4.9 installed via yum

I am seeing errors from time to time in PostgreSQL's logs about a table called events. The events table is used to stores information generated by triggers in Zabbix everytime a specific event occurs on a server that is being monitored.

Zabbix is the only application using the database server.

Here is the error:

INSTRUCTION :  insert into events (eventid,source,object,objectid,clock,ns,value,value_changed) values (6393428,2,3,38,1353354000,0,1,0)
ERROR:  duplicate key violates unique constraint « events_pkey »

There is no sequence associated with the table events

Here is the table structure for events

                               Table « public.events »
    Colonne    |  Type   |          Modificateurs           | Stockage | Description 
---------------+---------+----------------------------------+----------+-------------
 eventid       | bigint  | non NULL                         | plain    | 
 source        | integer | non NULL Par défaut, 0           | plain    | 
 object        | integer | non NULL Par défaut, 0           | plain    | 
 objectid      | bigint  | non NULL Par défaut, (0)::bigint | plain    | 
 clock         | integer | non NULL Par défaut, 0           | plain    | 
 value         | integer | non NULL Par défaut, 0           | plain    | 
 acknowledged  | integer | non NULL Par défaut, 0           | plain    | 
 ns            | integer | non NULL Par défaut, 0           | plain    | 
 value_changed | integer | non NULL Par défaut, 0           | plain    | 
Index :
    "events_pkey" PRIMARY KEY, btree (eventid)
    "events_1" btree (object, objectid, eventid)
    "events_2" btree (clock)
Référencé par :
    TABLE "acknowledges" CONSTRAINT "c_acknowledges_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
    TABLE "alerts" CONSTRAINT "c_alerts_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE

Question

What steps can I take to resolve this error?

Thank you

Updated 11/26

I tried to run the ALTER TABLE ALTER COLUMN statement that dezso sent me, but I get an error about the bigserial type.

zabbix=# ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;
ERREUR:  le type « bigserial » n'existe pas

I can however create a new table with that data type

zabbix=# \connect postgres
psql (8.4.13)
Vous êtes maintenant connecté à la base de données « postgres ».
postgres=# CREATE TABLE test(x bigserial);
NOTICE:  CREATE TABLE créera des séquences implicites « test_x_seq » pour la colonne serial « test.x »
CREATE TABLE
postgres=# \ds
              Liste des relations
 Schéma |    Nom     |   Type   | Propriétaire 
--------+------------+----------+--------------
 public | test_x_seq | séquence | postgres
(1 ligne)

How should I go about changing eventid to bigserial?

Update 11/27

I have tried another method to add the sequence to the column eventid

create sequence eventid_seq;
alter table events alter column eventid set default nextval('eventid_seq');
select (max(eventid) +1) as nexteventid from events;
SELECT setval('eventid_seq', 6645754);

That worked, but when I turned Zabbix back on, the eventid value continued to increase but the sequence didn't. What am I doing incorrectly?

Best Answer

(I try to compile an answer from the comments.)

It looks like there is a problem in the process with which your application generates the value for the primary key. Generally it is wiser to leave this to the DBMS: define a sequence and leave it fill the PK values. If you have a sequence, you have at least to options: either set the value like

$eventid = exec(SELECT nextval('your_sequence')) // pseudocode!

and use this in the INSERT statement, or completely leave it to PostgreSQL by omitting PK from the INSERT:

INSERT INTO events (source, object, [...]) 
VALUES (2, 3, [...]);

(Personally I usually opt for the second solution.)

You can change to this behaviour by setting the data type of your PK column to bigserial (in case of an integer, it would be just serial):

ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;

This will raise a notice which tells you that a sequence called (probably) events_eventid_seq is generated. You can use it as described above.

EDIT (2012-11-27)

You can set the sequence by a query like

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));

In order to let the sequence produce the next value for eventid, you must not set a value explicitly in your INSERT. If you do, it will take the manually supplied value and won't advance the sequence. This will cause the same unique violation error later.

Regarding ALTER TABLE: as you could see, there is some discrepancy between ALTER TABLE - type does not exists and CREATE TABLE. Are you sure you try this on the same database? I can't remember the older versions, but already 8.3 had the bigserial shorthand (it's not a real type). (Giving it further thought, it is quite possible you can't use it in ALTER TABLE, since it is a shorthand...) You can solve the problem by following the manual:

CREATE SEQUENCE events_eventid_seq;

ALTER TABLE events 
ALTER COLUMN eventid TYPE bigint;

ALTER SEQUENCE events_eventid_seq OWNED BY events.eventid;

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));

Related Solutions

Postgresql – Postgres multiple joins slow query, how to store default child record

You write:

Each customer can have multiple sites, but only one should be displayed in this list.

Yet, your query retrieves all rows. That would be a point to optimize. But you also do not define which site is to be picked.

Either way, it does not matter much here. Your EXPLAIN shows only 5026 rows for the site scan (5018 for the customer scan). So hardly any customer actually has more than one site. Did you ANALYZE your tables before running EXPLAIN?

From the numbers I see in your EXPLAIN, indexes will give you nothing for this query. Sequential table scans will be the fastest possible way. Half a second is rather slow for 5000 rows, though. Maybe your database needs some general performance tuning?

Maybe the query itself is faster, but "half a second" includes network transfer? EXPLAIN ANALYZE would tell us more.

If this query is your bottleneck, I would suggest you implement a materialized view.

After you provided more information I find that my diagnosis pretty much holds.

The query itself needs 27 ms. Not much of a problem there. "Half a second" was the kind of misunderstanding I had suspected. The slow part is the network transfer (plus ssh encoding / decoding, possibly rendering). You should only retrieve 100 rows, that would solve most of it, even if it means to execute the whole query every time.

If you go the route with a materialized view like I proposed you could add a serial number without gaps to the table plus index on it - by adding a column row_number() OVER (<your sort citeria here>) AS mv_id.

Then you can query:

SELECT *
FROM   materialized_view
WHERE  mv_id >= 2700
AND    mv_id <  2800;

This will perform very fast. LIMIT / OFFSET cannot compete, that needs to compute the whole table before it can sort and pick 100 rows.

pgAdmin timing

When you execute a query from the query tool, the message pane shows something like:

Total query runtime: 62 ms.

And the status line shows the same time. I quote pgAdmin help about that:

The status line will show how long the last query took to complete. If a dataset was returned, not only the elapsed time for server execution is displayed, but also the time to retrieve the data from the server to the Data Output page.

If you want to see the time on the server you need to use SQL EXPLAIN ANALYZE or the built in Shift + F7keyboard shortcut or Query -> Explain analyze. Then, at the bottom of the explain output you get something like this:

Total runtime: 0.269 ms

How to Determine if a Column is Defined as Serial Data Type in PostgreSQL

SERIAL and BIGSERIAL are kind of pseudo-types. As you noticed, they are really just INT and BIGINT internally.

What happens behind the scenes is that PostgreSQL creates a sequence and sets up a dependency on it to the table. You can search pg_class for the sequence name and how it relates to the table.

pg_class: http://www.postgresql.org/docs/9.2/static/catalog-pg-class.html

SQL Fiddle: http://sqlfiddle.com/#!12/dfcbd/6

Sequence Functions: http://www.postgresql.org/docs/9.2/static/functions-sequence.html

This StackOverflow post might be helpful: https://stackoverflow.com/questions/1493262/list-all-sequences-in-a-postgres-db-8-1-with-sql

UPDATE: You can also use pg_depend to figure out which sequences relate to the table /column you are interested in: http://www.postgresql.org/docs/9.2/static/catalog-pg-depend.html