Skip to content

popoto.backends.postgres.schema

popoto.backends.postgres.schema

The Postgres schema compiler (#759 M1b, plan §3).

A :class:~popoto.backends.types.ModelSpec compiles to one typed table:

  • _pk text PRIMARY KEY holds the canonical key string -- the same ClassName:… string Redis uses as the hash key, so Model.pk is the same on both backends.
  • one typed column per field (the type map below); a DatetimeField adds <f>__utcoff integer (offset seconds, NULL = naive), which keeps the #521 round-trip contract that timestamptz alone drops.
  • engine-owned _created_at / _updated_at ([PG-only]).
  • UNIQUE over the key fields, a B-tree per further key field, and a B-tree (partition cols…, f) per SortedField.
  • M1.1 (plain-field breadth): an IndexedField / Relationship column gets a B-tree (UNIQUE for a unique field), a TagField is text[] with a GIN index, the collection fields are jsonb (:mod:.codec), BytesField is bytea, and Meta.indexes compiles to composite B-tree / UNIQUE indexes.

The compiled shape is fingerprinted and recorded in popoto_schema the first time a process binds the model. Create and additive changes (a new nullable column, a new index) apply automatically under pg_advisory_xact_lock; anything else -- a dropped or retyped column, a key change, a newer format version, a schema some other client extended -- raises :class:SchemaDriftError rather than writing (§3 Migrations).

Pure except for :func:ensure_table, which takes an open connection. Never imports redis.

SCHEMA_FORMAT_VERSION = 1 module-attribute

Bumped when the compiler's output shape changes incompatibly. A client that finds a newer version in popoto_schema refuses to write.

ENGINE_COLUMNS = (('_created_at', 'timestamptz NOT NULL DEFAULT now()'), ('_updated_at', 'timestamptz NOT NULL DEFAULT now()'), ('_migrated_from', 'jsonb'), ('_estimated_fields', 'text[]')) module-attribute

Engine-owned columns on every table (plan §1.1, [PG-only]).

COLUMNLESS_KINDS = frozenset({'BM25Field', 'ExistenceFilter', 'FrequencySketch', 'CoOccurrenceField'}) module-attribute

(M4 adds CoOccurrenceField: its edges live in .graph's table.)

Column dataclass

One stored column. field is the popoto field it belongs to (None for _pk); role is "value", "utcoff" or "pk".

Source code in src/popoto/backends/postgres/schema.py
@dataclass(frozen=True)
class Column:
    """One stored column. ``field`` is the popoto field it belongs to (``None``
    for ``_pk``); ``role`` is ``"value"``, ``"utcoff"`` or ``"pk"``."""

    name: str
    sql_type: str
    field: Optional[str]
    role: str = "value"
    py_type: Optional[type] = None

TableSpec dataclass

What one model compiles to. indexes maps index name -> the CREATE [UNIQUE] INDEX body after ON <table>; both are part of the fingerprint.

Source code in src/popoto/backends/postgres/schema.py
@dataclass(frozen=True)
class TableSpec:
    """What one model compiles to. ``indexes`` maps index name -> the
    ``CREATE [UNIQUE] INDEX`` body after ``ON <table>``; both are part of the
    fingerprint."""

    model: str
    schema: str
    table: str
    columns: tuple[Column, ...]
    indexes: tuple[tuple[str, str, bool], ...]
    key_fields: tuple[str, ...]
    field_types: dict[str, type]
    unique_indexes: dict[str, tuple[str, ...]] = dataclass_field(default_factory=dict)
    """Unique index name -> the fields it covers (to name a 23505)."""
    field_kinds: dict[str, str] = dataclass_field(default_factory=dict)
    """Field name -> its popoto kind (``FieldSpec.kind``)."""
    capped_fields: frozenset[str] = frozenset()
    """``ListField(max_length=N)`` columns: elements are type-tagged (.codec)."""
    meta_indexes: dict[str, tuple[str, ...]] = dataclass_field(default_factory=dict)
    """``Meta.indexes`` unique index name -> its fields (to word a 23505)."""
    companions: tuple[tuple[str, tuple[str, ...]], ...] = ()
    """Companion tables (M2b): ``(name, DDL statements)``, each statement
    idempotent (``IF NOT EXISTS``), created with the table and on an additive
    migration. Part of the fingerprint when present."""
    search: Any = None
    """The compiled search layout (:class:`.search.SearchLayout`) for a model
    with BM25 / embedding / membership fields, else ``None``."""
    ttl: bool = False
    """``Meta.ttl`` (M5): the table has ``_expires_at`` and every read of it
    filters expired rows out (:mod:`.ttl`)."""

    def kind(self, name: str) -> str:
        return self.field_kinds.get(name, "Field")

    def is_tag(self, name: str) -> bool:
        return self.kind(name) in TAG_KINDS

    def is_json(self, name: str) -> bool:
        return self.field_types.get(name) in JSON_TYPES and not self.is_tag(name)

    @property
    def qualified(self) -> str:
        return f"{quote_ident(self.schema)}.{quote_ident(self.table)}"

    @property
    def datetime_fields(self) -> tuple[str, ...]:
        """Fields stored as ``timestamptz`` + ``<f>__utcoff``."""
        return tuple(
            name for name, t in self.field_types.items() if t is datetime.datetime
        )

    def column_map(self) -> dict[str, str]:
        return {c.name: c.sql_type for c in self.columns}

    def fingerprint(self) -> str:
        shape: dict[str, Any] = {
            "format": SCHEMA_FORMAT_VERSION,
            "columns": sorted(self.column_map().items()),
            "indexes": sorted([list(i) for i in self.indexes]),
            "engine": [name for name, _ in ENGINE_COLUMNS],
        }
        if self.companions:
            shape["companions"] = [[n, list(s)] for n, s in self.companions]
        payload = json.dumps(shape, sort_keys=True)
        return hashlib.sha256(payload.encode()).hexdigest()

    def companion_sql(self) -> list[str]:
        return [stmt for _name, stmts in self.companions for stmt in stmts]

    def create_sql(self) -> list[str]:
        cols = ['"_pk" text PRIMARY KEY']
        cols += [
            f"{quote_ident(c.name)} {c.sql_type}"
            for c in self.columns
            if c.role != "pk"
        ]
        cols += [f"{quote_ident(name)} {decl}" for name, decl in ENGINE_COLUMNS]
        stmts = [f"CREATE TABLE {self.qualified} ({', '.join(cols)})"]
        stmts += [self.index_sql(i) for i in self.indexes]
        stmts += self.companion_sql()
        return stmts

    def index_sql(self, index: tuple[str, str, bool]) -> str:
        name, body, unique = index
        return (
            f"CREATE {'UNIQUE ' if unique else ''}INDEX IF NOT EXISTS "
            f"{quote_ident(name)} ON {self.qualified} {body}"
        )

unique_indexes = dataclass_field(default_factory=dict) class-attribute instance-attribute

Unique index name -> the fields it covers (to name a 23505).

field_kinds = dataclass_field(default_factory=dict) class-attribute instance-attribute

Field name -> its popoto kind (FieldSpec.kind).

capped_fields = frozenset() class-attribute instance-attribute

ListField(max_length=N) columns: elements are type-tagged (.codec).

meta_indexes = dataclass_field(default_factory=dict) class-attribute instance-attribute

Meta.indexes unique index name -> its fields (to word a 23505).

companions = () class-attribute instance-attribute

Companion tables (M2b): (name, DDL statements), each statement idempotent (IF NOT EXISTS), created with the table and on an additive migration. Part of the fingerprint when present.

search = None class-attribute instance-attribute

The compiled search layout (:class:.search.SearchLayout) for a model with BM25 / embedding / membership fields, else None.

ttl = False class-attribute instance-attribute

Meta.ttl (M5): the table has _expires_at and every read of it filters expired rows out (:mod:.ttl).

datetime_fields property

Fields stored as timestamptz + <f>__utcoff.

CatalogBefore dataclass

What :func:grant_created compares against: the relations already in the schema, and whether the schema itself existed, when the DDL transaction began.

Source code in src/popoto/backends/postgres/schema.py
@dataclass(frozen=True)
class CatalogBefore:
    """What :func:`grant_created` compares against: the relations already
    in the schema, and whether the schema itself existed, when the DDL
    transaction began."""

    relations: frozenset[int]
    schema_existed: bool

quote_ident(name)

Double-quote an identifier. Names reach here from field and model names, which Python already restricts; anything else is refused.

Source code in src/popoto/backends/postgres/schema.py
def quote_ident(name: str) -> str:
    """Double-quote an identifier. Names reach here from field and model
    names, which Python already restricts; anything else is refused."""
    if not _IDENT.fullmatch(name):
        raise BackendCapabilityError(f"{name!r} is not a usable Postgres identifier")
    return f'"{name}"'

table_name_for(model_name)

popoto.<model_snake> (plan §3); the schema is the backend's.

Source code in src/popoto/backends/postgres/schema.py
def table_name_for(model_name: str) -> str:
    """``popoto.<model_snake>`` (plan §3); the schema is the backend's."""
    return _bounded(_snake(model_name))

compile_table(spec, schema)

Compile spec to a :class:TableSpec. Pure.

Source code in src/popoto/backends/postgres/schema.py
def compile_table(spec: ModelSpec, schema: str) -> TableSpec:
    """Compile ``spec`` to a :class:`TableSpec`. Pure."""
    table = table_name_for(spec.name)
    columns: list[Column] = [Column("_pk", "text", None, role="pk")]
    field_types: dict[str, type] = {}
    reserved = {"_pk"} | {name for name, _ in ENGINE_COLUMNS}
    for name in sorted(spec.fields):
        if name in reserved:
            raise BackendCapabilityError(
                f"{spec.name}.{name} collides with an engine-owned column"
            )
        if spec.fields[name].kind in COLUMNLESS_KINDS:
            # Side-effect fields (M2b): no value of their own, only companion
            # tables, which compile_search adds below.
            continue
        py_type = _field_type(spec, name)
        field_types[name] = py_type
        sql_type = SQL_TYPES[py_type]
        if spec.fields[name].kind in TAG_KINDS:
            sql_type = "text[]"
        columns.append(Column(name, sql_type, name, py_type=py_type))
        if py_type is datetime.datetime:
            columns.append(Column(name + UTCOFF_SUFFIX, "integer", name, role="utcoff"))
    # M2a: state columns the memory fields and mixins keep beside the field
    # values (confidence state, access tracking), from .memory.
    from .memory import memory_columns, memory_indexes

    taken = {c.name for c in columns}
    for state_col in memory_columns(spec):
        if state_col.name in taken or state_col.name in reserved:
            raise BackendCapabilityError(
                f"{spec.name}: column {state_col.name} collides with a field"
            )
        columns.append(state_col)

    indexes: list[tuple[str, str, bool]] = []
    unique_indexes: dict[str, tuple[str, ...]] = {}

    def index_name(*parts: str) -> str:
        return _bounded("__".join((table,) + parts + ("idx",)))

    key_fields = tuple(spec.key_fields)  # DB_key order
    if key_fields:
        cols = ", ".join(quote_ident(k) for k in key_fields)
        indexes.append((index_name("keys"), f"({cols})", True))
        unique_indexes[index_name("keys")] = key_fields
        for extra in key_fields[1:]:
            indexes.append((index_name(extra), f"({quote_ident(extra)})", False))
    for name, fs in sorted(spec.fields.items()):
        # A unique field (UniqueKeyField, unique=True) gets its own UNIQUE --
        # Redis enforces it in pre_save; here the index does, and a 23505 on
        # it becomes the same ModelException. A sole key field is already
        # unique through the key index.
        if fs.options.get("unique") and key_fields != (name,):
            indexes.append((index_name("uniq", name), f"({quote_ident(name)})", True))
            unique_indexes[index_name("uniq", name)] = (name,)
    for name, fs in sorted(spec.fields.items()):
        # IndexedField / Relationship: a B-tree on the column (a unique one
        # already has the UNIQUE above, which serves equality). TagField: GIN,
        # which serves @> (contains/all) and && (any).
        if fs.kind in TAG_KINDS:
            body = f"USING gin ({quote_ident(name)})"
            indexes.append((index_name(name), body, False))
        elif (
            fs.kind in INDEXED_KINDS or fs.kind in RELATIONSHIP_KINDS
        ) and not fs.options.get("unique"):
            indexes.append((index_name(name), f"({quote_ident(name)})", False))
    meta_indexes: dict[str, tuple[str, ...]] = {}
    unique_meta = {tuple(i) for i in spec.unique_indexes}
    for names in spec.indexes:
        # Meta.indexes: a composite B-tree, UNIQUE for is_unique=True. NULLs
        # stay distinct, as Redis skips a tuple with a None in it.
        unique = tuple(names) in unique_meta
        cols = ", ".join(quote_ident(n) for n in names)
        meta_name = index_name("meta", *names)
        indexes.append((meta_name, f"({cols})", unique))
        if unique:
            unique_indexes[meta_name] = tuple(names)
            meta_indexes[meta_name] = tuple(names)
    for name, fs in sorted(spec.fields.items()):
        if fs.kind not in SORTED_KINDS:
            continue
        partition = tuple(fs.options.get("partition_by", ()) or ())
        # The trailing _pk COLLATE "C" is the ORDER BY tie-break (Redis's
        # bytewise member order), so a range read with LIMIT is index-ordered.
        cols = ", ".join(quote_ident(c) for c in partition + (name,))
        body = f'({cols}, "_pk" COLLATE "C")'
        indexes.append((index_name("sort", name), body, False))
    # M2b: BM25 / embedding / membership fields add auxiliary columns, an HNSW
    # index and companion tables (imported here: search imports this module).
    from .search import compile_search

    search = compile_search(spec, schema, table, index_name)
    if search is not None:
        columns.extend(search.columns)
        indexes.extend(search.indexes)
    indexes.extend(memory_indexes(spec, index_name))
    # M4: one edge table per CoOccurrenceField (.graph).
    from .graph import compile_graph

    companions = (search.companions if search is not None else ()) + compile_graph(
        spec, schema, table
    )

    # M5: Meta.ttl adds _expires_at and its partial index (.ttl); a model
    # without it compiles exactly as before.
    from .ttl import ttl_columns, ttl_indexes

    columns.extend(ttl_columns(spec))
    indexes.extend(ttl_indexes(spec, index_name))
    # M5: a GeoField's geohash score and decoded position (.geo).
    from .geo import geo_columns, geo_indexes

    columns.extend(geo_columns(spec))
    indexes.extend(geo_indexes(spec, index_name))
    return TableSpec(
        model=spec.name,
        schema=schema,
        table=table,
        columns=tuple(columns),
        indexes=tuple(indexes),
        key_fields=key_fields,
        field_types=field_types,
        unique_indexes=unique_indexes,
        field_kinds={name: fs.kind for name, fs in spec.fields.items()},
        capped_fields=frozenset(
            name for name, fs in spec.fields.items() if fs.options.get("capped")
        ),
        meta_indexes=meta_indexes,
        companions=companions,
        search=search,
        ttl=spec.ttl is not None,
    )

schema_lock_key(schema)

The advisory-lock key every first use of schema serialises on.

CREATE SCHEMA IF NOT EXISTS is not race-free: two sessions that both see the schema missing both insert into pg_namespace and one fails with a unique violation. So every path that may create the schema -- :func:ensure_table and :func:engine_table_ddl -- takes this one key first, then its per-table key (:func:table_lock_key), always in that order so the two never deadlock (#759 M4b B1).

Source code in src/popoto/backends/postgres/schema.py
def schema_lock_key(schema: str) -> str:
    """The advisory-lock key every first use of ``schema`` serialises on.

    ``CREATE SCHEMA IF NOT EXISTS`` is not race-free: two sessions that both
    see the schema missing both insert into ``pg_namespace`` and one fails
    with a unique violation. So *every* path that may create the schema --
    :func:`ensure_table` and :func:`engine_table_ddl` -- takes this one key
    first, then its per-table key (:func:`table_lock_key`), always in that
    order so the two never deadlock (#759 M4b B1).
    """
    return f"popoto:ddl:{schema}"

table_lock_key(schema, table)

The per-table advisory-lock key, taken after :func:schema_lock_key.

Source code in src/popoto/backends/postgres/schema.py
def table_lock_key(schema: str, table: str) -> str:
    """The per-table advisory-lock key, taken after :func:`schema_lock_key`."""
    return f"popoto:ddl:{schema}.{table}"

ddl_timeout_sql(*, waiting=False)

SET LOCAL lock_timeout and statement_timeout for one DDL transaction, from Defaults (#776, PR #793 review).

Two phases, because they bound different waits:

  • waiting=True -- before the advisory locks are taken, and while the catalog is read: lock_timeout is Defaults.PG_DDL_SCHEMA_LOCK_TIMEOUT_MS, a much longer backstop. The schema-wide DDL advisory lock is held for as long as another process's migration runs, and a process whose model is already current just waits its turn (as it always did) instead of failing at the short limit. The wait cannot be a deadlock: every holder of these locks is itself bounded by the DDL phase below, so the chain ends.
  • waiting=False (default) -- immediately before a DDL statement: lock_timeout is Defaults.PG_DDL_LOCK_TIMEOUT_MS, so a DDL that waits on a table lock another session's open transaction holds gives up instead of hanging with the backend's first-use lock held.

statement_timeout is PG_DDL_STATEMENT_TIMEOUT_MS in both. SET LOCAL ends with the transaction, so a pooled connection is never left modified.

Source code in src/popoto/backends/postgres/schema.py
def ddl_timeout_sql(*, waiting: bool = False) -> str:
    """``SET LOCAL lock_timeout`` and ``statement_timeout`` for one DDL
    transaction, from ``Defaults`` (#776, PR #793 review).

    Two phases, because they bound different waits:

    * ``waiting=True`` -- *before* the advisory locks are taken, and while the
      catalog is read: ``lock_timeout`` is
      ``Defaults.PG_DDL_SCHEMA_LOCK_TIMEOUT_MS``, a much longer backstop. The
      schema-wide DDL advisory lock is held for as long as another process's
      migration runs, and a process whose model is already current just waits
      its turn (as it always did) instead of failing at the short limit. The
      wait cannot be a deadlock: every holder of these locks is itself
      bounded by the DDL phase below, so the chain ends.
    * ``waiting=False`` (default) -- immediately before a DDL statement:
      ``lock_timeout`` is ``Defaults.PG_DDL_LOCK_TIMEOUT_MS``, so a DDL that
      waits on a table lock another session's open transaction holds gives up
      instead of hanging with the backend's first-use lock held.

    ``statement_timeout`` is ``PG_DDL_STATEMENT_TIMEOUT_MS`` in both.
    ``SET LOCAL`` ends with the transaction, so a pooled connection is never
    left modified."""
    from ...fields.constants import Defaults

    lock_ms = max(1, int(Defaults.PG_DDL_LOCK_TIMEOUT_MS))
    if waiting:
        lock_ms = max(1, int(Defaults.PG_DDL_SCHEMA_LOCK_TIMEOUT_MS))
    statement_ms = max(1, int(Defaults.PG_DDL_STATEMENT_TIMEOUT_MS))
    return (
        f"SET LOCAL lock_timeout = {lock_ms}; "
        f"SET LOCAL statement_timeout = {statement_ms}; "
    )

set_ddl_timeouts(cur, *, waiting=False)

Run :func:ddl_timeout_sql on cur, inside its transaction.

Source code in src/popoto/backends/postgres/schema.py
def set_ddl_timeouts(cur: Any, *, waiting: bool = False) -> None:
    """Run :func:`ddl_timeout_sql` on ``cur``, inside its transaction."""
    for stmt in ddl_timeout_sql(waiting=waiting).split("; "):
        if stmt.strip():
            cur.execute(stmt.strip().rstrip(";"))

engine_table_ddl(schema, table, body)

One message that creates an engine-owned side table on first use.

It takes the schema lock, then the table lock, then creates the schema and the table, all in the one implicit transaction the caller runs it in, so a concurrent :func:ensure_table or another engine table's first use waits on the schema rather than racing it into pg_namespace. Returns the SQL and its parameters for PostgresBackend._run(..., write=True).

Source code in src/popoto/backends/postgres/schema.py
def engine_table_ddl(schema: str, table: str, body: str) -> tuple[str, list[str]]:
    """One message that creates an engine-owned side table on first use.

    It takes the schema lock, then the table lock, then creates the schema and
    the table, all in the one implicit transaction the caller runs it in, so a
    concurrent :func:`ensure_table` or another engine table's first use waits
    on the schema rather than racing it into ``pg_namespace``. Returns the SQL
    and its parameters for ``PostgresBackend._run(..., write=True)``.
    """
    return (
        ddl_timeout_sql(waiting=True) + "SELECT pg_advisory_xact_lock(hashtext(%s)); "
        "SELECT pg_advisory_xact_lock(hashtext(%s)); "
        + ddl_timeout_sql()
        + f"CREATE SCHEMA IF NOT EXISTS {quote_ident(schema)}; "
        f"CREATE TABLE IF NOT EXISTS {quote_ident(schema)}.{quote_ident(table)} "
        f"({body})",
        [schema_lock_key(schema), table_lock_key(schema, table)],
    )

ensure_table(conn, ts, *, auto, grant_to=None)

Create or check ts on conn inside one transaction, under an advisory transaction lock (PgBouncer transaction-mode safe). Returns what it did: "created", "migrated" or "current".

Raises :class:SchemaDriftError for every case it will not reconcile.

grant_to (#800, opt-in grant_main_role) names a role to grant access to what this call creates -- the schema, the registry, the table and its companions and sequences -- in the same transaction (:func:grant_created). A "current" table grants nothing.

Source code in src/popoto/backends/postgres/schema.py
def ensure_table(
    conn: Any, ts: TableSpec, *, auto: bool, grant_to: Optional[str] = None
) -> str:
    """Create or check ``ts`` on ``conn`` inside one transaction, under an
    advisory transaction lock (PgBouncer transaction-mode safe). Returns what
    it did: ``"created"``, ``"migrated"`` or ``"current"``.

    Raises :class:`SchemaDriftError` for every case it will not reconcile.

    ``grant_to`` (#800, opt-in ``grant_main_role``) names a role to grant
    access to what this call creates -- the schema, the registry, the table
    and its companions and sequences -- in the same transaction
    (:func:`grant_created`). A ``"current"`` table grants nothing.
    """
    with conn.transaction():
        cur = conn.cursor()
        capture: Optional[list[CatalogBefore]] = [] if grant_to is not None else None
        outcome = _ensure_table_in(cur, ts, auto=auto, capture=capture)
        if grant_to is not None and capture and outcome != "current":
            grant_created(cur, ts.schema, grant_to, capture[0])
        return outcome

quote_role(role)

Double-quote a role name as Postgres quotes any identifier. A role name is whatever the server reports (current_user), not a field name, so it is escaped rather than refused (:func:quote_ident).

Source code in src/popoto/backends/postgres/schema.py
def quote_role(role: str) -> str:
    """Double-quote a role name as Postgres quotes any identifier. A role
    name is whatever the server reports (``current_user``), not a field
    name, so it is escaped rather than refused (:func:`quote_ident`)."""
    return '"' + role.replace('"', '""') + '"'

catalog_before(conn, schema)

Read :class:CatalogBefore for schema on conn (a connection or cursor), inside the DDL transaction it precedes.

Source code in src/popoto/backends/postgres/schema.py
def catalog_before(conn: Any, schema: str) -> CatalogBefore:
    """Read :class:`CatalogBefore` for ``schema`` on ``conn`` (a connection
    or cursor), inside the DDL transaction it precedes."""
    rows = conn.execute(
        "SELECT c.oid FROM pg_class c JOIN pg_namespace n "
        "ON n.oid = c.relnamespace WHERE n.nspname = %s "
        "AND c.xmin IS DISTINCT FROM pg_current_xact_id_if_assigned()::xid",
        (schema,),
    ).fetchall()
    (existed,) = conn.execute(
        "SELECT to_regnamespace(%s) IS NOT NULL", (schema,)
    ).fetchone()
    return CatalogBefore(frozenset(int(r[0]) for r in rows), bool(existed))

grant_created(conn, schema, role, before)

Grant role access to exactly what this transaction created in schema (#800): run on conn after the DDL and before its commit.

"Created here" is two facts together: the object's catalog row was written by this transaction (its xmin is this transaction's id), and it was not there when the transaction began (before). The first excludes everything another session made, however concurrently; the second excludes a table this transaction only altered (ADD COLUMN rewrites its pg_class row). So nothing an admin made by hand, no other role's table and no object a previous process created is ever granted on, and a warm start -- nothing created -- grants nothing, which leaves any REVOKE an admin has run in place.

Tables get :data:APP_TABLE_PRIVILEGES (the registry only :data:APP_REGISTRY_PRIVILEGES), sequences :data:APP_SEQUENCE_PRIVILEGES, and the schema USAGE only when this transaction created it. No ALTER DEFAULT PRIVILEGES, no ON ALL TABLES. Returns the statements it ran.

Source code in src/popoto/backends/postgres/schema.py
def grant_created(
    conn: Any, schema: str, role: str, before: CatalogBefore
) -> list[str]:
    """Grant ``role`` access to exactly what this transaction created in
    ``schema`` (#800): run on ``conn`` after the DDL and before its commit.

    "Created here" is two facts together: the object's catalog row was
    written by this transaction (its ``xmin`` is this transaction's id), and
    it was not there when the transaction began (``before``). The first
    excludes everything another session made, however concurrently; the
    second excludes a table this transaction only altered (``ADD COLUMN``
    rewrites its ``pg_class`` row). So nothing an admin made by hand, no
    other role's table and no object a previous process created is ever
    granted on, and a warm start -- nothing created -- grants nothing, which
    leaves any ``REVOKE`` an admin has run in place.

    Tables get :data:`APP_TABLE_PRIVILEGES` (the registry only
    :data:`APP_REGISTRY_PRIVILEGES`), sequences
    :data:`APP_SEQUENCE_PRIVILEGES`, and the schema ``USAGE`` only when this
    transaction created it. No ``ALTER DEFAULT PRIVILEGES``, no ``ON ALL
    TABLES``. Returns the statements it ran."""
    rows = conn.execute(
        "SELECT c.oid, c.relkind, c.relname FROM pg_class c JOIN pg_namespace n "
        "ON n.oid = c.relnamespace WHERE n.nspname = %s "
        "AND c.relkind IN ('r', 'p', 'S') "
        "AND c.xmin = pg_current_xact_id_if_assigned()::xid ORDER BY c.oid",
        (schema,),
    ).fetchall()
    (schema_created,) = conn.execute(
        "SELECT xmin = pg_current_xact_id_if_assigned()::xid "
        "FROM pg_namespace WHERE nspname = %s",
        (schema,),
    ).fetchone() or (False,)
    s, r = quote_role(schema), quote_role(role)
    statements: list[str] = []
    if schema_created and not before.schema_existed:
        statements.append(f"GRANT USAGE ON SCHEMA {s} TO {r}")
    for oid, kind, name in rows:
        if int(oid) in before.relations:
            continue
        target = f"{s}.{quote_role(str(name))}"
        if kind == "S":
            statements.append(
                f"GRANT {APP_SEQUENCE_PRIVILEGES} ON SEQUENCE {target} TO {r}"
            )
        elif name == POPOTO_SCHEMA_TABLE:
            statements.append(
                f"GRANT {APP_REGISTRY_PRIVILEGES} ON TABLE {target} TO {r}"
            )
        else:
            statements.append(f"GRANT {APP_TABLE_PRIVILEGES} ON TABLE {target} TO {r}")
    for statement in statements:
        conn.execute(statement)
    return statements