Unfortunately you're wrong. I've now tested it, but I was also pretty confident before - I'm a postgres developer, and I worked on/comitted the PG upsert implementation ;)

  postgres[10287][1]=# CREATE TABLE data(key text unique);
  CREATE TABLE
  
  postgres[10287][1]=# BEGIN ISOLATION LEVEL SERIALIZABLE ;
  BEGIN
  
  postgres[10284][1]=# BEGIN ISOLATION LEVEL SERIALIZABLE ;
  BEGIN
  
  postgres[10287][1]*=# INSERT INTO data VALUES('alice');
  INSERT 0 1
  
  postgres[10284][1]*=# INSERT INTO data SELECT 'alice' WHERE NOT EXISTS(SELECT * FROM data WHERE key = 'alice');
  
  postgres[10287][1]*=# COMMIT;
  COMMIT
  
  postgres[10284][1]*=# 
  ERROR:  23505: duplicate key value violates unique constraint "data_key_key"
  DETAIL:  Key (key)=(alice) already exists.
  SCHEMA NAME:  public
  TABLE NAME:  data
  CONSTRAINT NAME:  data_key_key
  LOCATION:  _bt_check_unique, nbtinsert.c:535

> > If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row.

> My understanding is that the update from S2 would be rerun applied.

> Specifically postgres calls out:

> > The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition.

Those comments are about row-level locks - they're not the problem here. What you get is a constraint violation due to the unique constraint.