diff --git a/synapse/storage/engines/_base.py b/synapse/storage/engines/_base.py
index ad222e7e2d..9d82c59384 100644
--- a/synapse/storage/engines/_base.py
+++ b/synapse/storage/engines/_base.py
@@ -28,6 +28,11 @@ if TYPE_CHECKING:
from synapse.storage.database import LoggingDatabaseConnection
+# A string that will be replaced with the appropriate auto increment directive
+# for the database engine, expands to an auto incrementing integer primary key.
+AUTO_INCREMENT_PRIMARY_KEYPLACEHOLDER = "$%AUTO_INCREMENT_PRIMARY_KEY%$"
+
+
class IsolationLevel(IntEnum):
READ_COMMITTED: int = 1
REPEATABLE_READ: int = 2
diff --git a/synapse/storage/engines/postgres.py b/synapse/storage/engines/postgres.py
index 90641d5a18..8c8c6d0414 100644
--- a/synapse/storage/engines/postgres.py
+++ b/synapse/storage/engines/postgres.py
@@ -25,6 +25,7 @@ from typing import TYPE_CHECKING, Any, Mapping, NoReturn, Optional, Tuple, cast
import psycopg2.extensions
from synapse.storage.engines._base import (
+ AUTO_INCREMENT_PRIMARY_KEYPLACEHOLDER,
BaseDatabaseEngine,
IncorrectDatabaseSetup,
IsolationLevel,
@@ -256,4 +257,10 @@ class PostgresEngine(
executing the script in its own transaction. The script transaction is
left open and it is the responsibility of the caller to commit it.
"""
+ # Replace auto increment placeholder with the appropriate directive
+ script = script.replace(
+ AUTO_INCREMENT_PRIMARY_KEYPLACEHOLDER,
+ "BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY",
+ )
+
cursor.execute(f"COMMIT; BEGIN TRANSACTION; {script}")
diff --git a/synapse/storage/engines/sqlite.py b/synapse/storage/engines/sqlite.py
index b11094c5c1..9d1795ebe5 100644
--- a/synapse/storage/engines/sqlite.py
+++ b/synapse/storage/engines/sqlite.py
@@ -25,6 +25,7 @@ import threading
from typing import TYPE_CHECKING, Any, List, Mapping, Optional
from synapse.storage.engines import BaseDatabaseEngine
+from synapse.storage.engines._base import AUTO_INCREMENT_PRIMARY_KEYPLACEHOLDER
from synapse.storage.types import Cursor
if TYPE_CHECKING:
@@ -168,6 +169,11 @@ class Sqlite3Engine(BaseDatabaseEngine[sqlite3.Connection, sqlite3.Cursor]):
> first. No other implicit transaction control is performed; any transaction
> control must be added to sql_script.
"""
+ # Replace auto increment placeholder with the appropriate directive
+ script = script.replace(
+ AUTO_INCREMENT_PRIMARY_KEYPLACEHOLDER, "INTEGER PRIMARY KEY AUTOINCREMENT"
+ )
+
# The implementation of `executescript` can be found at
# https://github.com/python/cpython/blob/3.11/Modules/_sqlite/cursor.c#L1035.
cursor.executescript(f"BEGIN TRANSACTION; {script}")
|
