Add NpgsqlTransactionOptions for read-only/deferrable transactions - #6629

src/Npgsql/NpgsqlTransaction.cs:136

• NpgsqlTransactionOptions isn't validated before generating the BEGIN statement. Invalid flag values (unknown bits) will be silently ignored, and Deferrable without ReadOnly and Serializable will cause a server-side error at execution time. Consider validating flags/combinations and throwing an argument exception early with a clear message.

            var sb = new StringBuilder("BEGIN TRANSACTION ISOLATION LEVEL ").Append(isolationLevelText);
            if ((options & NpgsqlTransactionOptions.ReadOnly) != 0)
                sb.Append(" READ ONLY");
            if ((options & NpgsqlTransactionOptions.Deferrable) != 0)
                sb.Append(" DEFERRABLE");

src/Npgsql/NpgsqlTransaction.cs:137

• Init calls _connector.WriteQuery(..., async: false) but doesn't wait for it to complete (it only asserts in Debug). If the write ever can't complete synchronously (e.g., buffer flush needed), this becomes a fire-and-forget Task, risking an incomplete/partial prepend and unobserved exceptions. Other prepend-only paths in this file (e.g. Save) use .GetAwaiter().GetResult() to guarantee completion.

            // Unlike the isolation levels above, these options can be combined in many ways, making it impractical to pregenerate
            // messages for all combinations. As with PrependInternalMessage above, the (short) BEGIN statement is assumed to
            // always fit in the write buffer, so this completes synchronously.
            var writeTask = _connector.WriteQuery(sb.ToString(), async: false);
            Debug.Assert(writeTask.IsCompleted, "Could not fully write BEGIN message into the buffer");
            _connector.PendingPrependedResponses += 2;

src/Npgsql/PublicAPI.Unshipped.txt:52

• The new public enum NpgsqlTransactionOptions is referenced by the new BeginTransaction overloads but isn’t itself listed in PublicAPI.Unshipped.txt. This means the public API tracking file is incomplete for this PR’s additions.

Npgsql.NpgsqlConnection.BeginTransaction(System.Data.IsolationLevel level, Npgsql.NpgsqlTransactionOptions options) -> Npgsql.NpgsqlTransaction!
Npgsql.NpgsqlConnection.BeginTransactionAsync(System.Data.IsolationLevel level, Npgsql.NpgsqlTransactionOptions options, System.Threading.CancellationToken cancellationToken = default(System.Threading.CancellationToken)) -> System.Threading.Tasks.ValueTask<Npgsql.NpgsqlTransaction!>

src/Npgsql/NpgsqlTransactionOptions.cs:28

• The XML docs for Deferrable currently say it “only has an effect” outside of ReadOnly+Serializable. In PostgreSQL this combination is only valid for SERIALIZABLE READ ONLY transactions; it’s worth documenting that other combinations will fail rather than being ignored.

    /// The transaction can be deferred. This only has an effect when the transaction is both <see cref="ReadOnly"/> and
    /// <see cref="System.Data.IsolationLevel.Serializable"/>, in which case it allows the database to wait for a point in time where
    /// no conflicts can occur before starting the transaction, avoiding the overhead associated with serializable transactions.
    /// Corresponds to <c>DEFERRABLE</c> in <c>BEGIN</c>.

src/Npgsql/NpgsqlTransaction.cs:135

• When transaction options are specified, Init writes the BEGIN statement via WriteQuery(async: false) and blocks on the returned Task. This means BeginTransactionAsync(…, options, cancellationToken) may perform synchronous I/O and will not observe the provided cancellation token if a flush is required (e.g. when the write buffer is full). Consider plumbing the async flag and cancellation token through (or moving the BEGIN construction/writing up into BeginTransaction) so the query can be written with async=true and the caller’s cancellationToken.

            // Unlike the isolation levels above, these options can be combined in many ways, making it impractical to pregenerate
            // messages for all combinations; the BEGIN statement is written out and sent like a regular (prepended) query instead.
            _connector.WriteQuery(sb.ToString(), async: false).GetAwaiter().GetResult();
            _connector.PendingPrependedResponses += 2;

src/Npgsql/NpgsqlConnection.cs:596

• The comment immediately above StartUserAction says BeginTransaction doesn't send anything to the backend (only prepends). With the new transaction options path this is no longer strictly true (a custom BEGIN is written via WriteQuery and may flush if the write buffer is full), so the comment is now misleading.

        using var _ = connector.StartUserAction(cancellationToken);