Jump to:

10998 Posts in 2732 Topics by 1822 members

All other Modules

SilverStripe Forums » All other Modules » Thousands of primary key errors with generated ID - Duplicate primary key exception when using clustering or high load (Postgres SQL Module)

Discuss all other Modules here.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 285 Views
  • seba.wagner
    Avatar
    Community Member
    5 Posts

    Thousands of primary key errors with generated ID - Duplicate primary key exception when using clustering or high load (Postgres SQL Module) Link to this post

    We are facing an issue with the Postgres Database Module. But I think it will be the same no matter what database module you are using.

    It seems like SilverStripe generally wants to generate the ID in the table. So every insert query, instead of leaving the ID field blank (and maybe do a select afterwards), it tries to do a select on the sequencer (or in MySQL using "mysql_insert_id") to generate an ID and then set this value in the insert query.

    If you have multiple instances of SilverStripe pointing to the same database (because you run some kind of load balancing) or if you simply have a very high load on a single node, you can see thousands!! of primary key errors when inserting rows in the database.

    I don't want to throw the stone here, but in general: If you have a primary key in the database and generate a schema with auto-incrementor's (no matter if its MySQL or Postgres or whatever database), why would it be a good idea to overwrite that and explicitly set the ID in insert queries?
    This just leads to problems. And you try to solve such race conditions by letting the database decide: Who comes first -> serves first. The database will put those parallel inserts into a specific order so that exactly this kind of error will not happen.

    So I am just wondering why this issue is not addressed? From my point of view it is just a matter of load to fall into this trap.

    Thanks,
    Sebastian

    285 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.