liveBook · Manning

ON CONFLICT clause

As explained in Designing AI Systems

The ON CONFLICT clause is a PostgreSQL SQL construct that specifies how an insert should proceed when it violates a uniqueness or exclusion constraint.

ON CONFLICT makes an insert safe when a uniqueness constraint is already satisfied. In session storage, concurrent requests carrying the same session ID can race: the first insert succeeds, while the second updates the existing row's timestamp instead of raising a duplicate-key error. section 4.4.2

The clause supports a production-oriented session persistence pattern:

  • Use a supplied session ID to find an existing session before creating one. section 4.4.2
  • Resolve concurrent inserts by updating the existing row rather than failing. section 4.4.2
  • Combine conflict handling with PostgreSQL's RETURNING feature to receive the resulting row without a separate SELECT. section 4.4.2

Concurrent session requests

Two requests submit the same session ID. The first INSERT creates the session; the second follows the ON CONFLICT path, refreshes its timestamp, and returns the existing row. section 4.4.2

Listing 4.11 PostgreSQL storage implementation
import json
import uuid
from datetime import datetime
from typing import List, Optional, Tuple
import psycopg2    #A
from psycopg2.extras import RealDictCursor    #A
 
class PostgresSessionStorage(SessionStorage):
    
    def __init__(self, connection_string: str):    #B
        self.connection_string = 
➥connection_string    #B
        self.conn = psycopg2.connect(    #B
            connection_string,     #B
            cursor_factory=RealDictCursor    #C
        )    
    
    def get_or_create_session(
        self, 
        user_id: str, 
        session_id: Optional[str] = None
    ) -> Session:
        cursor = self.conn.cursor()    #D
        
        if session_id:    #E
            cursor.execute(    
                "SELECT * FROM sessions 
➥WHERE session_id = %s",    #F
                (session_id,)    
            )    
            row = cursor.fetchone()    
            if row:    #E
                return self._row_to_session(row)    
        
        new_session_id = session_id 
➥or str(uuid.uuid4())    #G
        now = datetime.utcnow()
        
        cursor.execute(
            """INSERT INTO sessions (session_id, user_id, created_at,
                updated_at)
            VALUES (%s, %s, %s, %s)
            ON CONFLICT (session_id)
                DO UPDATE SET updated_at = EXCLUDED.updated_at
            RETURNING *""",    #H
            (new_session_id, user_id, now, now)
        )
        self.conn.commit()    #I
        
        return self._row_to_session(cursor.fetchone())

Inspect the PostgreSQL storage implementation surrounding get_or_create_session and its database interaction. section 4.4.2

How Designing AI Systems covers it

How deep it goes
Explains the race condition and resulting timestamp update, but does not detail complete ON CONFLICT syntax.
Examples
Uses two concurrent requests submitting the same session ID as the concrete race-condition example.
What it assumes
Assumes PostgreSQL inserts and uniqueness constraints, while demonstrating how concurrent requests trigger conflict handling.
What you can do after
Apply it via PostgreSQL to make get-or-create persistence resilient to duplicate concurrent inserts.
How it connects
Connects ON CONFLICT with uniqueness constraints, duplicate-key errors, PostgreSQL RETURNING, and concurrent requests.

Also explained in

Compare how 2 books explain ON CONFLICT clause →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Designing AI Systems. The section links point to the parts of the book it draws on.