PostgreSQL errors: duplicate key value violates unique constraint (key_value____pkey and semaphore____pkey)

I think this is a Drupal 7 problem, not Drupal 10.

@daffie No, I still get these errors consistently on Drupal 9. I've just set up my error log monitoring to ignore them, but it would be nice if I didn't have to do that.

@m.stenta: Could you add a test or an other way to reproduce the bug? It is now a bit difficult to solve.

I haven't figured out what causes it, and haven't been able to intentionally reproduce it. I host a large number of sites running D9 on PostgreSQL though and my logs show that it is happening pretty consistently. It seems to be correlated to cache rebuild (and/or some cron runs), but it doesn't happen all the time so it's hard to nail down.

@m.stenta: Could you possibly add the full query that is failing. That is including the part "EXCLUDED" and the part after "ON CONFLICT". Maybe too many Drupal merge queries in a short timeframe.

As far as I know, the query I pasted in the issue summary is the entire query that failed.

I admit I don't have a good sense for how the semaphore table works, but I'm not sure that upsert() is being used for semaphore table modifications... it looks like only insert() and update() are used. Am I reading that correctly?

https://git.drupalcode.org/project/drupal/-/blob/bef31d1d77de39e8622678a...

This same error message happens on a clean install of drupal, aside from the theme name being olivero, claro, etc.

This is because of how drupal tries to acquire the lock by inserting, and then catching the exception. Postgres by default logs an error for such failed inserts, although you could configure postgres to do less verbose logging.

Failing to acquire the lock is almost guaranteed even with just a single user, because that one page load can bootstrap drupal multiple times when loading CSS and JS files. Easiest way to reproduce is to clear cache and shift-reload page to force your browser to reload all the CSS and JS files.

Checked my logs and found that pgsql sometimes hangs and after restart is unable to insert into semaphore

{"timestamp":"2024-08-28T14:33:18.076Z","user":"drupal","dbname":"db","pid":508,"remote_host":"::1","remote_port":"59604","session_id":"66cf352d.1fc","ps_display":"INSERT","session_start":"2024-08-28T10:42:45.000Z","vxid":"4/88","txid":5172853,"error_severity":"ERROR","state_code":"23505","detail":"Key (name)=(cron) already exists.","statement":"INSERT INTO \"semaphore\" (\"name\", \"value\", \"expire\") VALUES ('cron', '186628320366cf352e108841.46027453', '1724856498.0675')","backend_type":"client backend","query_id":0,"message":"duplicate key value violates unique constraint \"semaphore____pkey\""}
{"timestamp":"2024-08-28T14:33:18.188Z","user":"drupal","dbname":"db","pid":509,"remote_host":"::1","remote_port":"59614","session_id":"66cf352d.1fd","ps_display":"INSERT","session_start":"2024-08-28T10:42:45.000Z","vxid":"5/273","txid":5172855,"error_severity":"ERROR","state_code":"23505","detail":"Key (name)=(cron) already exists.","statement":"INSERT INTO \"semaphore\" (\"name\", \"value\", \"expire\") VALUES ('cron', '1720247566cf352e2d60e7.70432522', '1724856498.1857')","backend_type":"client backend","query_id":0,"message":"duplicate key value violates unique constraint \"semaphore____pkey\""}

Re #7 Writing a test requires concurrent requests and reading PostgreSQL log. That's not what Drupal testing system is capable of. To reproduce the issue manually you can run simultaneously a couple CLI scripts which acquire a lock with the same ID.
The root reason of this error is explained well in #14.

Possible solution is execution SELECT query before the INSERT in the same transaction. Though it may behave differently depending on configured transaction isolation level.

We can also consider using some third party lock implementation, for instance symfony/lock.