Missing PostgreSQL coordination primitives: advisory xact locks, FOR UPDATE SKIP LOCKED, LOCK TABLE IN SHARE ROW EXCLUSIVE

[johannesdb]

Filing this as a separate feature request per @zachmu's suggestion in #2581, since it's outside the scope of that parser bug.

Background

We're building a multi-service Go application (SSO + exhibition platform) against DoltgreSQL 0.56.1 via jackc/pgx/v5. Our architecture would like to push cross-service coordination down to the database layer (standard PostgreSQL pattern), but we've hit three missing primitives that currently force us into a single-instance design with in-process sync.Mutex coordination.

Missing primitives (verified in internal POC against 0.56.1)

1. Transaction-level advisory locks

SELECT pg_advisory_xact_lock(12345);
SELECT pg_try_advisory_xact_lock(12345);

Currently returns: function not found. The session-level variants (pg_advisory_lock / pg_advisory_unlock) do work, which is great — but the transaction-scoped versions are significantly safer for app code because they're automatically released on commit/rollback, eliminating a whole class of lock-leak bugs.

Primary use case: serializing idempotent operations (e.g. sending a specific magic link, processing a specific webhook delivery) across concurrent requests without the foot-gun of forgetting to release.

2. FOR UPDATE / FOR UPDATE SKIP LOCKED

SELECT id FROM jobs WHERE status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED;

Currently returns: locking clauses are not yet supported.

Primary use case: worker queues. This is the canonical PostgreSQL pattern for building durable job queues directly on the primary database (see What's the least terrible way to build a queue on Postgres, river, etc.). Without SKIP LOCKED, we'd have to either (a) run a separate queue system, or (b) fall back to advisory locks + retry loops, which is significantly less efficient under contention.

3. LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE

LOCK TABLE sequences IN SHARE ROW EXCLUSIVE MODE;

Currently returns: parser doesn't recognize the syntax.

Primary use case: migration scripts that need to temporarily block concurrent DDL while allowing concurrent reads. Less critical than the above two, but useful for online schema migrations.

Priority ranking (from our perspective)

If these need to be sequenced:

1. pg_advisory_xact_lock — highest impact, smallest surface area. We could drop our in-process sync.Mutex coordinator immediately.
2. FOR UPDATE SKIP LOCKED — unlocks building queue/scheduler features on DoltgreSQL alone. Currently we'd need a separate queue system.
3. LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE — nice to have, not blocking us.

Happy to split into separate issues

I've filed this as one umbrella issue since they all relate to multi-instance coordination, but very happy to split into three if that's easier to track against the roadmap — just let me know.

Context

This follows on from #2581 where we're also dealing with a parser thread-safety bug that currently forces us to MaxConns=1. Together, these items shape whether DoltgreSQL can act as a drop-in Postgres replacement for multi-instance Go services. No urgency on any of this — just wanted to give you clean signal on what matters for this class of app.

Thanks for the great work on Doltgres.

[Hydrocharged]

Thank you for the bug! I'm assigning Zach since it looks like he was already looking into the related issue, but if that's not the case we'll reassign.