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

I wonder if this race condition is happening because of the .theme include stuff? Might be something to combine the include/.theme files back into the main .theme file and comment out that line that does the include. It's possible that line might be doing something during a cache flush or cache clear?

Maybe that's how "upsert" works now in core?

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

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

@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.

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 drupal 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.

Re: #18

This snippet works for me.

try {
  $lock_exists = (bool) $this->database
    ->select('semaphore')
    ->condition('name', $name)
    ->countQuery()
    ->execute()
    ->fetchField();
  if (!$lock_exists) {
    $this->database->insert('semaphore')
      ->fields([
        'name' => $name,
        'value' => $this->getLockId(),
        'expire' => $expire,
      ])
      ->execute();
  }
}
finally {
  unset($transaction);
}

Overall, I think the default lock implementation should use flock backend instead of database.

@chi

Where should we add the above code snippet?

The snippet needs to be converted to a patch for DatabaseLockBackend.

Disclosure: I have used AI for the PR.

Appears to need a rebase for a conflict in the performance test.