Source code for fleetmesh_node.storage.postgres

"""PostgreSQL, as an option beside the SQLite default.

Optional in the convention this repo already uses for wasmtime: the driver is
imported lazily, a node without it still starts and serves, and a
`postgres://` URL is refused by name rather than failing at import. CI sets
FLEETMESH_REQUIRE_POSTGRES=1 so a missing driver cannot turn a suite green by
skipping.

The thirteen stores write SQLite's dialect, which is the house dialect, and
`sql()` here renders it into PostgreSQL's. The census that says this is a
short list rather than a rewrite is docs/storage-backends.md section 4; what
it actually comes to:

  placeholders   `?` -> `%s`, outside string literals
  upserts        `INSERT OR REPLACE` -> `ON CONFLICT (pk) DO UPDATE SET ...`
                 `INSERT OR IGNORE`  -> `ON CONFLICT DO NOTHING`
  types          nothing to do. The node's DDL uses TEXT and INTEGER and
                 nothing else across all 44 tables, and both are PostgreSQL
                 types.
  WITHOUT ROWID  dropped; it is a SQLite storage hint.
  PRAGMA         never reaches here; `tune` sets what a server needs instead.

The conflict target comes from the table's own DDL. Every `CREATE TABLE` in
the tree passes through `sql()` during `_ensure_schema`, before any INSERT can,
so the dialect learns each table's columns and primary key on the way past and
has them when an upsert arrives. All 44 tables declare a primary key, so
`DO UPDATE` always has a target - checked, not assumed.

One connection behind one lock, the shape BaseStore already had. That is what
makes this backend need no retry policy: with a single writer there is nothing
to serialize against, so no transaction is ever told to retry.
"""
from __future__ import annotations

import re
import threading
from typing import Any, Optional

from fleetmesh_core.storage.store_postgres import ERROR_HINT, available, driver

__all__ = ['ERROR_HINT', 'PostgresDialect', 'available', 'driver', 'placeholders']


_CREATE = re.compile(r'CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?([A-Za-z_]\w*)\s*\((.*)\)\s*'
                     r'(?:WITHOUT\s+ROWID)?\s*;?\s*$', re.I | re.S)
_INLINE_PK = re.compile(r'^\s*([A-Za-z_]\w*)\s+\w+[^,]*\bPRIMARY\s+KEY\b', re.I)
_TABLE_PK = re.compile(r'\bPRIMARY\s+KEY\s*\(([^)]*)\)', re.I)
_UPSERT = re.compile(r'^(\s*)INSERT\s+OR\s+(REPLACE|IGNORE)\s+INTO\s+([A-Za-z_]\w*)\s*'
                     r'(\(([^)]*)\))?\s*(VALUES\b.*)$', re.I | re.S)


def _split_columns(body: str) -> list[str]:
    """Top-level comma split of a CREATE TABLE body, ignoring nested parens."""
    out, depth, cur = [], 0, ''
    for ch in body:
        if ch == '(':
            depth += 1
        elif ch == ')':
            depth -= 1
        if ch == ',' and depth == 0:
            out.append(cur)
            cur = ''
        else:
            cur += ch
    if cur.strip():
        out.append(cur)
    return out


[docs]def placeholders(statement: str) -> str: """`?` -> `%s`, leaving anything inside a single-quoted literal alone. The node's SQL carries literals like `status='verified'`; a blind replace would corrupt one that happened to contain a question mark. """ out, in_lit, i = [], False, 0 while i < len(statement): ch = statement[i] if ch == "'": # '' inside a literal is an escaped quote, not the end of it if in_lit and i + 1 < len(statement) and statement[i + 1] == "'": out.append("''") i += 2 continue in_lit = not in_lit out.append(ch) elif ch == '?' and not in_lit: out.append('%s') else: out.append(ch) i += 1 return ''.join(out)
[docs]class PostgresDialect: """PostgreSQL. Learns each table's shape from the DDL it renders.""" name: str = 'postgres' rowid_expr: Optional[str] = None # no insertion-order expression; see Dialect
[docs] def __init__(self) -> None: self._cols: dict[str, list[str]] = {} self._pk: dict[str, list[str]] = {}
# ---- connection -------------------------------------------------------
[docs] def connect(self, target: str, timeout: float, readonly: bool = False) -> Any: pg = driver() conn = pg.connect(target, connect_timeout=int(max(1, timeout)), autocommit=False) if readonly: conn.read_only = True return conn
[docs] def tune(self, db: Any, target: str, lock: threading.RLock) -> None: """What a server needs in place of SQLite's PRAGMAs: a statement timeout so a wedged query cannot hold the one connection for ever.""" with lock: cur = db.cursor() try: cur.execute("SET statement_timeout = '30s'") cur.execute("SET idle_in_transaction_session_timeout = '60s'") finally: cur.close() db.commit()
[docs] def learn_table(self, table: Any) -> None: name = getattr(table, 'name', '') if not name: return cols = [c.name for c in getattr(table, 'columns', ()) if hasattr(c, 'name')] pk = list(getattr(table, 'primary_key', ())) if not pk: pk = [c.name for c in getattr(table, 'columns', ()) if getattr(c, 'primary_key', False)] self._cols[name.lower()] = cols if pk: self._pk[name.lower()] = pk
def _learn(self, statement: str) -> None: m = _CREATE.match(statement.strip()) if not m: return table, body = m.group(1), m.group(2) cols, pk = [], [] for part in _split_columns(body): tpk = _TABLE_PK.search(part) if tpk and not re.match(r'^\s*[A-Za-z_]\w*\s+\w', part): pk = [c.strip() for c in tpk.group(1).split(',') if c.strip()] continue name = re.match(r'^\s*([A-Za-z_]\w*)', part) if not name: continue if name.group(1).upper() in ('PRIMARY', 'UNIQUE', 'FOREIGN', 'CHECK', 'CONSTRAINT'): tpk = _TABLE_PK.search(part) if tpk: pk = [c.strip() for c in tpk.group(1).split(',') if c.strip()] continue cols.append(name.group(1)) if _INLINE_PK.match(part): pk = [name.group(1)] self._cols[table.lower()] = cols if pk: self._pk[table.lower()] = pk
[docs] def sql(self, statement: Any) -> str: if hasattr(statement, 'to_sql'): self.learn_table(statement) statement = statement.to_sql('postgres') else: self._learn(statement) out = re.sub(r'\s+WITHOUT\s+ROWID\b', '', statement, flags=re.I) m = _UPSERT.match(out) if m: indent, mode, table, _, named, tail = m.groups() key = table.lower() if mode.upper() == 'IGNORE': out = '%sINSERT INTO %s %s %s ON CONFLICT DO NOTHING' % ( indent, table, '(%s)' % named if named else '', tail) else: pk = self._pk.get(key, []) cols = [c.strip() for c in named.split(',')] if named else self._cols.get(key, []) sets = [c for c in cols if c.lower() not in {p.lower() for p in pk}] if not pk: raise RuntimeError( 'INSERT OR REPLACE INTO %s needs a primary key to become an upsert, ' 'and none was declared in its CREATE TABLE' % table) assign = ', '.join('%s = EXCLUDED.%s' % (c, c) for c in sets) or None action = ('DO UPDATE SET %s' % assign) if assign else 'DO NOTHING' out = '%sINSERT INTO %s %s %s ON CONFLICT (%s) %s' % ( indent, table, '(%s)' % named if named else '', tail, ', '.join(pk), action) return placeholders(out)
# ---- transaction control ---------------------------------------------- # # The driver owns the transaction. psycopg opens one on the first # statement and tracks it; issuing raw BEGIN/COMMIT/ROLLBACK alongside # that leaves the two disagreeing about whether a transaction is open, # and work committed by one path gets rolled back by the other. That is # how the first Postgres run recorded schema version 2 while the column # version 1 adds was missing: the ALTER applied and was then undone, # while the version write survived on the other side of a raw COMMIT.
[docs] def begin(self, db: Any) -> None: return None # psycopg begins on the first statement
[docs] def commit(self, db: Any) -> None: db.commit()
[docs] def rollback(self, db: Any) -> None: db.rollback()
# ---- schema version ---------------------------------------------------
[docs] def get_version(self, db: Any, store: str) -> int: """The schema version of ONE store, keyed by its name. SQLite gets this for free: each store is its own file, so `PRAGMA user_version` is per store. Here all fourteen share one database, and a single version row made them collide - AccountStore wrote 2, GrantStore read 2, concluded its own migrations were applied and skipped the ALTER that adds `grants.env_created_at`. The table was then one column short of what its INSERT supplies, and the first write failed with "INSERT has more expressions than target columns". Only a multi-store test on a server could find that; one store alone passes. """ cur = db.cursor() try: cur.execute('CREATE TABLE IF NOT EXISTS fleetmesh_schema_version (' ' store TEXT PRIMARY KEY, version INTEGER NOT NULL)') cur.execute('SELECT version FROM fleetmesh_schema_version WHERE store = %s', (store,)) row = cur.fetchone() finally: cur.close() return int(row[0]) if row else 0
[docs] def set_version(self, db: Any, store: str, version: int) -> None: cur = db.cursor() try: cur.execute('INSERT INTO fleetmesh_schema_version VALUES (%s, %s) ' 'ON CONFLICT (store) DO UPDATE SET version = EXCLUDED.version', (store, int(version))) finally: cur.close()
# ---- reflection and maintenance ---------------------------------------
[docs] def has_table(self, db: Any, table: str) -> bool: cur = db.cursor() try: cur.execute('SELECT 1 FROM information_schema.tables ' 'WHERE table_schema = current_schema() AND table_name = %s', (table,)) return cur.fetchone() is not None finally: cur.close()
[docs] def has_column(self, db: Any, table: str, column: str) -> bool: cur = db.cursor() try: cur.execute('SELECT 1 FROM information_schema.columns ' 'WHERE table_schema = current_schema() AND table_name = %s ' 'AND column_name = %s', (table, column)) return cur.fetchone() is not None finally: cur.close()
[docs] def is_duplicate_column(self, exc: BaseException) -> bool: return getattr(getattr(exc, 'sqlstate', None), 'strip', lambda: '')() == '42701' \ or 'already exists' in str(exc).lower()
[docs] def vacuum(self, db: Any, pages: int) -> int: """ANALYZE, and nothing more. Postgres reclaims by autovacuum; a manual VACUUM here would block the one connection the node has, and `pages` has no meaning on a server. Returns 0 - nothing was reclaimed by this call, and saying otherwise would be a fake count.""" try: db.commit() with db.cursor() as cur: cur.execute('ANALYZE') db.commit() except Exception: return 0 return 0
[docs] def integrity_check(self, db: Any) -> Optional[str]: """None. File integrity is a file-backed idea; a server answers for its own soundness and `PRAGMA quick_check` has no counterpart here.""" return None
[docs] def errors(self) -> tuple[type[BaseException], ...]: try: return (driver().Error,) except RuntimeError: return ()