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

