message_digest_update_8102() fails with DB error

It seems like the schema and DB index don't square up here, which AFAICT both prevents messages being delivered and prevents the DB update running (if there are existing entries).

#3031714: Database row deletions are inefficient added a unique index to mid but did not have a corresponding update to message_digest_schema() to make that column a serial.

In Drupal\message_digest\Plugin\Notifier\DigestBase::deliver() we trigger a DB insert without setting mid, which is fine if mid is a serial and unique, OR if mid is not a serial and can store duplicates.

AFAICT, this won't work. If the schema update is applied (which can only happen if there are no existing message_digest entries in the DB anyway?), then future digest messages will not be inserted to that table.

Is this something which only works on MySQL perhaps? It still seems like it would not behave correctly.

When there's no pre-existing data in message_digest

Running message deliveries throws a lot of integrity constriant violation exceptions:

[error] Drupal\Core\Database\IntegrityConstraintViolationException: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "message_digest__mid__key"
DETAIL: Key (mid)=(17) already exists.: INSERT INTO message_digest (receiver, entity_type, entity_id, notifier, timestamp, mid) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5); Array()

When there's already data in message_digest

DB is Postgres, and we have existing entries in the message_digest table. The column mid is not unique; far from it:

nzmathsd8=# select count(*), mid from message_digest group by mid having count(*) > 1;
1924	1
1926	2
1926	3
1926	4

I don't think we can rely on {message_digest}.mid being unique based on the schema, but 022e5b2 merged in #3031714: Database row deletions are inefficient treats it as such.

vagrant@example:/vagrant$ drush updatedb
 -------- ------------ --------------- ---------------------------------------- 
  Module   Update ID    Type            Description                             
 -------- ------------ --------------- ---------------------------------------- 
  messag   8102         hook_update_n   Add unique index to mid field.          
  e_dige                                                                        
  st                                                                            
 -------- ------------ --------------- ---------------------------------------- 

 Do you wish to run the specified pending updates? (yes/no) [yes]:
 > yes

>  [notice] Update started: message_digest_update_8102
>  [error]  SQLSTATE[23505]: Unique violation: 7 ERROR:  could not create unique index "message_digest__mid__key" 
> DETAIL:  Key (mid)=(1) is duplicated.: ALTER TABLE {message_digest} ADD CONSTRAINT message_digest__mid__key UNIQUE (mid); Array
> (
> )
>  
>  [error]  Update failed: message_digest_update_8102 
 [error]  Update aborted by: message_digest_update_8102 
 [error]  Finished performing updates.