Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

All other Modules /

Discuss all other Modules here.

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

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


Reply


325 Views

Avatar
seba.wagner

Community Member, 5 Posts

13 November 2013 at 11:58am

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