Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What does SELECT * FROM events WHERE eventid = 6393428 return? I'm sure it will return exactly one row, which will show that somehow the eventid generation gives back the wrong id. Generally it is wiser to leave this to a sequence. Another much used but quite bad option is to get max(eventid) + 1. In this latter case you have to be sure that between eventid generation and the insert no other inserts will succeed. –  dezso Nov 21 '12 at 10:41
    
Sorry I don't get it. It's pretty clear how to avoid that error: do not insert values that are already in the database. It's a problem of this Zabbix application, not a PostgreSQL problems (PostgreSQL simply does what it was told to do) –  a_horse_with_no_name Nov 21 '12 at 10:42
1  
@dezso: using max(eventid) + 1 is a horrible idea to generate primary keys. –  a_horse_with_no_name Nov 21 '12 at 10:42
    
@a_horse_with_no_name It is, but widely used. I suspect a case of that here - I'll change the wording to clarify that, thanks. –  dezso Nov 21 '12 at 10:43
1  
@CraigEfrein ALTER TABLE events ALTER COLUMN eventid TYPE bigserial; After that you have to change your application code to either completely omit eventid field from the insert or get a nextval() before. And you must set the starting value of the resulting sequence appropriately. –  dezso Nov 21 '12 at 10:45
show 1 more comment

1 Answer 1

(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));
share|improve this answer
    
+1 for the answer. I still need to test the modification on our preproduction server. I'll get back to you as soon as I have the results. –  Craig Efrein Nov 25 '12 at 6:06
    
Updated my question. –  Craig Efrein Nov 27 '12 at 9:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.