Results 1 to 8 of 8

Thread: duplicate key

  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Unanswered: duplicate key

    Hi,
    i have problem with inserting new rows into tables. I use as a primary key as 'serial' (int4) with sequences . Sometimes some strange situation appears i.e. if i have primary keys like 1,2,4 than PostgreSQL (sequence) is generating primary key = 3, and then 4. 4 already exist so duplicate key error occures. I have found some solution: SELECT setval('reference_referencelist_nr_seq', (SELECT max(referencelist_nr)+1 FROM reference))
    but it isn't good for me while i can do this when already error appear. I want that postgres generate always good primary key. Any ideas ?
    ps. sorry for my English

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    This sounds as if you have manually inserted or upated the values in the primary key column.

    If you are using a serial column as the primary key, then you should never provide a value for that column when inserting or updating your data. You have to rely on the sequence generating unique numbers.

    A sequence never returns the same number twice (except when you specify the CYCLE option which is not done for serial columns), so there must have been some kind of manual updating going on.

  3. #3
    Join Date
    Mar 2008
    Posts
    4

    duplicate key

    Such a situation occurs when i.e. i delete some rows in database and than when i restart database (postgres), probably the sequence (which is using by serial) is set to first "free" row (so it isn't the heighest value). And then when autoicrement set serial to value that exist in table the errors (problem) appear.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by entaroadun7
    Such a situation occurs when i.e. i delete some rows in database and than when i restart database (postgres), probably the sequence (which is using by serial) is set to first "free" row
    Definitely not. The sequence has no idea how many or which rows are in the table. It's "only" a generator for non-repeating (!) values.
    If your sequence returns a number that is already used in the table, someone has inserted/updated that value manually into the table.

  5. #5
    Join Date
    Mar 2008
    Posts
    4
    Do you know maybe what happen with sequence after i.e. restart postgres? Does it have always highest value or after restart is set off? Does it check if value already exist? Serial use autoincrement, but it doesn't check if new 'key' is already in table?

    someone has inserted/updated that value manually into the table.
    Yes, for test I insert rows manually (now i know that was wrong ), but even without INSERT manually new rows such a situation occur i.e. when i delete rows through my application and than i restart server (postgres).

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    It sounds as if something is resetting the sequence. I can't think of a situation where PostgreSQL could do this on its own. Check to determine if there is a delete trigger on the table in question which might be using the setval function to reset the sequence.

    Are you using transactions with your data manipulation?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Mar 2008
    Posts
    4
    Thanks for yours suggestion. Probably it was my mistake, but i will test this again. Do you know maybe if postgresql support two phase commit (i use spring + hibernate and i need some drivers) ?

  8. #8
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    could you clarify what you mean by 'restart server'. If you are simply restarting it then that will have no effect on the current serial value. If you are recreating the sequence and then loading all the historical data you will run into duplicate errors.

    with hibernate you can tell it how to deal with these serial values:
    Code:
        <id name="id" column="auto_id" type="integer">
          <generator class="sequence">
            <param name="sequence">auto_id_seq</param>
          </generator>
        </id>
    what hibernate does is fetch the value then add it to the container and then stores the container to the db.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •