SqlHydra.Query.PgSystemColumns 0.1.0-alpha.3

SqlHydra.Query.PgSystemColumns

PostgreSQL system columns — xmin — on the records SqlHydra generates, and inside its query computation expression.

A system column is not in information_schema, so SqlHydra.Cli never discovers one and the generated record has no field for it. And SELECT u.* does not return one, so a record that does carry the field fails to hydrate on every whole-entity read.

This package covers both. At generation time it adds the field to the tables you name. At query time it adds one operation that names the column explicitly:

SELECT "u".*      becomes      SELECT "u".*, "u"."xmin"

The columns

All six of PostgreSQL's system columns work. The operation names whichever column you give it, so nothing is specific to xmin, and you can chain it to project more than one.

ctid is not a row identifier — it is a physical address, and it changes when the row is updated or moved by VACUUM FULL. Use xmin for concurrency and a primary key for identity. See system columns.

Why you would want xmin

It is PostgreSQL's row version, and it changes on every write to the row. That makes optimistic concurrency a plain column comparison: read the version, then include it in the WHERE of your update. If someone else wrote first the version no longer matches, the UPDATE affects zero rows, and you can refuse the edit instead of silently overwriting theirs. No locks, no re-read.

Install

dotnet add package SqlHydra.Query.PgSystemColumns

Needs SqlHydra.Query 5.1.0 or later, and records generated by SqlHydra.Cli 5.1.0 or later.

Generating the field

Name the columns in the project sqlhydra generates into, as {schema}/{table}.{column} entries separated by ;, the path shape the [filters] section uses. Schema and table are globs, simpler than [filters] ones: * is any run of characters and ? any one, and nothing else is special:

<PropertyGroup>
  <PgSystemColumns>public/users.xmin;public/orders.xmin;sales/*.xmin</PgSystemColumns>
</PropertyGroup>

Register the package in your sqlhydra-npgsql.toml, next to any other extension:

[extensions]
type_mappings = [ "SqlHydra.Query.PgSystemColumns" ]

Build the project, then run dotnet sqlhydra npgsql. Each table you named gets the field, with its attribute and a doc comment:

type users =
    { id: System.Guid
      email: string
      /// PostgreSQL's row version: the id of the transaction that inserted this row version.
      /// ...
      [<ProviderDbType("Xid")>]
      xmin: uint }

    interface IHasWrite<users_write> with ...

/// The columns of `users` a caller may write; the database owns the rest.
and users_write =
    { id: System.Guid
      email: string }

[<ProviderDbType("Xid")>] is required, not decorative: Npgsql has no default mapping for uint32, so without it the compare-and-swap parameter throws before the statement is sent.

The column is read-only, because PostgreSQL rejects any statement that assigns to a system column. SqlHydra leaves a read-only column out of entity's column list and emits a users_write record without it, for writeEntity. So the column never reaches an INSERT or an UPDATE SET, and you do not need excludeColumn.

The build writes the entries next to this package's assembly in bin/, which is where sqlhydra loads the extension from, and copies the assembly there even for a library project. So build after changing the property, before running sqlhydra. The build writes the entries before compiling, so a build that fails because code already uses a field the generated file does not have yet still updates them.

Views get nothing: a view has no system columns of its own, so SELECT xmin FROM a_view fails unless the view projects one. A materialized view does have them, but SqlHydra reports it as a view, so it gets nothing either. Nor does a provider other than PostgreSQL.

There is no default that adds the column to every table. A table whose record carries xmin needs withSystemColumns on every whole-entity read, so adding it to a table nobody asked for breaks that table's reads. For the same reason an entry must name a table: a bare xmin fails. So does a registered package with no entries, a malformed entry, or a column that is not one of the six. Each stops sqlhydra before it reads the schema, rather than generating a file that is missing the field.

The generator's input is a property rather than items because MSBuild expands * in an item's Include against the file system and drops an entry that matches no file.

Usage

open SqlHydra.Query
open SqlHydra.Query.PgSystemColumns.SystemColumns

// Read a row together with its version. Without `withSystemColumns` the emitted SQL is
// `SELECT "u".*`, which omits `xmin`, and hydrating a record that declares the field
// fails.
let userWithVersion =
    select {
        for u in usersTable do
            where (u.id = userId)
            select u
            withSystemColumns (fun u -> u.xmin)
    }

// Compare-and-swap: the version you read goes into the predicate. If someone else wrote
// first, `xmin` no longer matches, the UPDATE affects zero rows, and you can refuse the
// edit instead of silently overwriting theirs. No extension is needed for this — it is an
// ordinary column comparison, and `[<ProviderDbType("Xid")>]` binds the parameter as
// `xid`.
let guardedUpdate (expectedVersion: uint32) =
    update {
        for u in usersTable do
            set u.email "new@example.com"
            where (u.id = userId && u.xmin = expectedVersion)
    }

withSystemColumns must follow select u, because it expands the SELECT u.* that select emits. Placing it earlier raises at query construction rather than returning a row without the column. Placing it after a scalar select does not compile at all — after select u.email the row type is string, so naming u.xmin is a type error.

The column is named with a lambda over the selected row, not as a bare u.xmin. That is what makes the operation usable in a join. select is the one operation that changes the builder's row type while keeping the computation expression's variable space, so after select u in a joined query the row is users while the variable space is still the tuple (u, s). An [<ProjectionParameter>] is elaborated against the variable space, so the bare form would be handed the tuple and every joined read would fail to compile ("expected users but is a tuple of type 'a * 'b"). A plain lambda argument is elaborated against its own parameter type — the selected row — in both shapes:

select {
    for u in usersTable do
        join s in userSettingsTable on (u.id = s.user_id)
        select u
        withSystemColumns (fun u -> u.xmin)   // SELECT "u".*, "u"."xmin", "s".* untouched
}

What this package does not need to do

Writes. A generated system column is read-only, so SqlHydra already keeps it out of INSERT and UPDATE SET (see Generating the field). For a record you wrote by hand, excludeColumn u.xmin ships with SqlHydra.Query and does the same; give the field notAVersion in a record you are about to write — it is never sent and never read back.

The guard itself. The comparison in the example above is an ordinary column comparison. It needs nothing from this package.

License

MIT