[Hyperdrive] Clarify whether pg_advisory_xact_lock is supported #34640

Existing documentation URL(s)

• https://developers.cloudflare.com/hyperdrive/reference/supported-databases-and-features/#unsupported-postgresql-features
• https://developers.cloudflare.com/hyperdrive/concepts/how-hyperdrive-works/#pooling-mode

What changes are you suggesting?

I would like to make it clear in the docs whether pg_advisory_xact_lock is supported through Hyperdrive. Before opening a PR, I would like to confirm the intended behavior so that the change describes it correctly.

The "Unsupported PostgreSQL features" section lists "Advisory locks" as a single item, without distinguishing session-level locks (pg_advisory_lock) from transaction-level locks (pg_advisory_xact_lock). If transaction-level locks are supported, applications could use them for simple mutual exclusion through Hyperdrive, without opening a separate direct database connection only for locking.

Earlier versions of the docs recommended transaction-level advisory locks. After #11958, the docs said:

Named prepared statements (which are rarely used), SET commands, and advisory locks are not supported. Use features such as LOCAL and pg_advisory_xact_lock which are scoped to single transactions.

This sentence was removed in #15031. As far as I can tell, that PR focused on adding common errors and reorganizing the feature list, and I could not find a note about this particular change. So I am not sure whether the removal reflects a change in what is supported.

Later, #19035 removed SET from the unsupported list, and the "Pooling mode" section now explains that SET inside an explicit transaction takes effect and that the connection is reset when it returns to the pool. I could not find a corresponding update for pg_advisory_xact_lock, although similar reasoning seems to apply: Hyperdrive pools connections in transaction mode, and PostgreSQL releases a transaction-level advisory lock when the transaction ends, which happens before the connection returns to the pool.

There is also some evidence that this works in practice. In emdash-cms/emdash#1744, PlanetScale Insights showed select pg_advisory_xact_lock($1) running with application_name = Cloudflare Hyperdrive. No errors were reported, and lock waits of several seconds were recorded. This suggests that the lock is acquired through Hyperdrive and blocks other sessions as expected. However, I would like to confirm whether this is supported behavior or something that happens to work today.

Could you let me know which of the following reflects the intended behavior? In both cases, "advisory locks" includes the try and shared variants.

1. Both session-level and transaction-level advisory locks are unsupported. I would state this explicitly for both kinds.
2. Only session-level advisory locks are unsupported. I would document that pg_advisory_xact_lock can be used inside an explicit transaction (for example, between BEGIN and COMMIT).

If neither is accurate (for example, if transaction-level locks are supported with some limitations), I would appreciate any details, and I would include them in the docs as well.

Additional information

• 0e0d0aa (correct error in yaml frontmatter #10959): initial Hyperdrive docs, including the first recommendation of LOCAL and pg_advisory_xact_lock
• 22a0eb3 ([Hyperdrive] Revisiting Learning section #11958): revision that kept the recommendation in the wording quoted above
• 5c74a4a (Hyperdrive error and supported postgres feature documentation improvements #15031): rewrite that removed the recommendation
• 68400b9 (Update how-hyperdrive-works.mdx #19035): revision that documented SET support inside a transaction