SqlHydra.Query.PgSystemColumns 0.1.0-alpha.1

SqlHydra.Query.PgSystemColumns

PostgreSQL system columns — xmin — inside the SqlHydra query computation expression.

SELECT u.* does not return a system column. So a generated record that carries an xmin field fails to hydrate on every whole-entity read, and the usual workaround is a hand-written SQL statement for each site that needs the version.

This package 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 4.1.1 or later, and records generated by SqlHydra.Cli.

Your generated record needs the field. xmin is a uint32, and [<ProviderDbType("Xid")>] is what makes the comparison bind natively:

[<ProviderDbType("Xid")>]
xmin: uint32

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. excludeColumn u.xmin ships with SqlHydra.Query and drops the column from an INSERT column list or an UPDATE SET clause. The database owns the value, so 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