PHP SilverStripe ORM: Duplicate key value violates unique constraint on DataObject record

Question

I have a function on my website that quickly stores a bunch of values ​​for the same type DataObject. In most cases this is normal, but sometimes I get an error

ERROR: duplicate key value violates unique constraint ...

Reading the documentation I see:

SilverStripe does not use the built-in automatic database numbering system. Instead, it will generate a new identifier, adding 1 to the current maximum ID

And before looking at the code, it looks like it extracts the maximum number from the primary key, inserts a record with this identifier, then sets the DataObject values ​​and writes again. In my load-balanced environment, when these several records are sent, I believe that the insertion occurs with the same primary key, therefore, with an error.

As far as I can see, this is a problem that I cannot do without. From the other questions and doco, I cannot establish a composite primary key. The only thing I can think of is to run custom sql to create, which uses a built-in automatic numbering system.

Is there a better way to handle this error or the way in which I can set the composite primary key?

EDIT

Complete mistake

Query failed: ERROR: duplicate key value violates unique constraint 'TABLE_pkey'
DETAIL: Key ('ID')=(136) already exists.

And the statement:

INSERT INTO "TABLE" ("ClassName", "Name", "MemberID", "OtherTabeID", "Value", "LastEdited", "Created", "ID") VALUES ($1, $2, $3, $4, $5, $6, $7, $8),Array) 

, , DB. ?

2

, , INSERT Created, , ID:

SELECT last_value FROM "TABLENAME_ID_seq"

UPDATE .

, , , , . INSERT returning "ID", .

3

, , , Created:

pg_query_params(Resource id #154,INSERT INTO "TABLENAME" ("ClassName", "Name", "MemberID", "OTHERTABLEID", "Value", "LastEdited", "Created", "ID") VALUES ($1, $2, $3, $4, $5, $6, $7, $8),Array) 
PostgreSQLConnector.php:200
PostgreSQLConnector->preparedQuery(INSERT INTO "TABLENAME" ("ClassName", "Name", "MemberID", "OTHERTABLEID", "Value", "LastEdited", "Created", "ID") VALUES (?, ?, ?, ?, ?, ?, ?, ?),Array,256) 
Database.php:143
SS_Database->{closure}(INSERT INTO "TABLENAME" ("ClassName", "Name", "MemberID", "OTHERTABLEID", "Value", "LastEdited", "Created", "ID") VALUES (?, ?, ?, ?, ?, ?, ?, ?)) 
Database.php:193
SS_Database->benchmarkQuery(INSERT INTO "TABLENAME" ("ClassName", "Name", "MemberID", "OTHERTABLEID", "Value", "LastEdited", "Created", "ID") VALUES (?, ?, ?, ?, ?, ?, ?, ?),Closure,Array) 
Database.php:146
SS_Database->preparedQuery(INSERT INTO "TABLENAME" ("ClassName", "Name", "MemberID", "OTHERTABLEID", "Value", "LastEdited", "Created", "ID") VALUES (?, ?, ?, ?, ?, ?, ?, ?),Array,256) 
DB.php:365
DB::prepared_query(INSERT INTO "TABLENAME" ("ClassName", "Name", "MemberID", "OTHERTABLEID", "Value", "LastEdited", "Created", "ID") VALUES (?, ?, ?, ?, ?, ?, ?, ?),Array) 
SQLExpression.php:121

Answer 1

@CraigRinger, , . PostgreSQL, currval(), .

, , ​​. , .

Answer 2

( SilverStripe 2.1 2007 ), , , .

, SilverStripe , SilverStripe, :

• SiteTree
• ( ),

UPDATE SiteTree .

, , .

Answer 3

: SilverStripe , . , , max(). .

- , max(...) : concurrency . . :

INSERT INTO my_table(id, ...)
VALUES
(
  (SELECT max(id) + 1 FROM my_table),
  ...
);

SELECT . , insert . insert SELECT, , SELECT .

, LOCK TABLE SELECT ... FOR UPDATE . A SELECT ... FOR UPDATE .

, SilverStripe, , , LOCK TABLE mytable IN EXCLUSIVE MODE; SELECT max(...), , .

Or better, fix it to just use the database sequence.

If there is a real business need for numbering without spaces, use a supported table of counters UPDATE ... RETURNING .... (If you need portability, you should use SELECT ... FOR UPDATE, then UPDATE, in the same transaction).

Update. In recent versions, the framework no longer uses this approach.

(Eliminated annoying statement about the approach as unconstructive)