PostgresDatabase.cs

namespace CoreEx.Database.Postgres;

/// <summary>
/// Provides the <see href="https://www.npgsql.org/">Npgsql (PostgreSQL)</see> <see cref="IDatabase"/> functionality.
/// </summary>
/// <remarks>The <see cref="OnDbException(DbException)"/> override implements transformation of pre-determined PostgreSQL error codes, as follows:
///  <list type="bullet">
///   <item><c>56001</c> -> <see cref="ValidationException"/>.</item>
///   <item><c>56002</c> -> <see cref="BusinessException"/>.</item>
///   <item><c>56003</c> -> <see cref="AuthorizationException"/>.</item>
///   <item><c>56004</c> -> <see cref="ConcurrencyException"/>.</item>
///   <item><c>56005</c> -> <see cref="NotFoundException"/>.</item>
///   <item><c>56006</c> -> <see cref="ConflictException"/>.</item>
///   <item><c>56007</c> -> <see cref="DuplicateException"/>.</item>
///   <item><c>56010</c> -> <see cref="DataConsistencyException"/>.</item>
///  </list>
///  <para>This is in addition to the <see cref="CheckDuplicateErrorNumbers"/> with the corresponding <see cref="DuplicateErrorNumbers"/> that will also result in a <see cref="DuplicateException"/>.</para>
///  <para>The <see cref="OnIsTransientException(Exception)"/> override additionally classifies deadlock, lock timeout, and Azure Database for PostgreSQL maintenance/failover/connection-limit error codes as transient (retryable).</para>
///  <para>This class also implements the <see cref="IUnitOfWork"/> including a <see href="https://microservices.io/patterns/data/transactional-outbox.html">transactional outbox</see>. The <see cref="IUnitOfWork"/> 
///  functionality is enabled by the <see cref="PostgresUnitOfWorkInvoker"/>; note, this is not thread-safe.</para>
/// </remarks>
/// <param name="dataSource">The <see cref="NpgsqlDataSource"/>.</param>
/// <param name="jsonSerializerOptions">The optional <see cref="JsonSerializerOptions"/>.</param>
/// <param name="logger">The optional <see cref="ILogger"/>.</param>
public partial class PostgresDatabase(NpgsqlDataSource dataSource, JsonSerializerOptions? jsonSerializerOptions = null, ILogger<PostgresDatabase>? logger = null) : Database<NpgsqlConnection, PostgresCommand, PostgresDatabaseArgs, PostgresDatabaseColumns>(CreateConnection(dataSource), PostgresInvoker.Default, PostgresDatabaseColumns.Default, jsonSerializerOptions, logger)
{
    /// <summary>
    /// Gets the default <see cref="DuplicateErrorNumbers"/>.
    /// </summary>
    /// <remarks>See <see href="https://www.postgresql.org/docs/current/errcodes-appendix.html"/>.</remarks>
    public static string[] DefaultDuplicateErrorNumbers { get; } = ["23505"];

    /// <summary>
    /// Creates the <see cref="NpgsqlConnection"/> from the specified <paramref name="dataSource"/>.
    /// </summary>
    private static NpgsqlConnection CreateConnection(NpgsqlDataSource dataSource) => dataSource.ThrowIfNull().CreateConnection();

    /// <inheritdoc/>
    public override ISourceConverter<string?> RowVersionConverter => EncodedStringToUInt32Converter.Default;

    /// <summary>
    /// Indicates whether to check the <see cref="DuplicateErrorNumbers"/> when catching the <see cref="PostgresException"/>.
    /// </summary>
    public bool CheckDuplicateErrorNumbers { get; set; } = true;

    /// <summary>
    /// Gets or sets the list of known <see cref="PostgresException.SqlState"/> values that are considered a duplicate error.
    /// </summary>
    /// <remarks>Overrides the <see cref="DefaultDuplicateErrorNumbers"/>.</remarks>
    public string[]? DuplicateErrorNumbers { get; set; }

    /// <inheritdoc/>
    public override PostgresCommand Statement(SqlStatement statement) => new(this, statement);

    /// <inheritdoc/>
    public override DbParameter CreateParameter() => new NpgsqlParameter();

    /// <inheritdoc/>
    protected override Exception? OnDbException(DbException dbex)
    {
        if (dbex is PostgresException pex)
        {
            var msg = pex.Message?.TrimEnd();
            if (string.IsNullOrEmpty(msg))
                msg = null;

            switch (pex.SqlState)
            {
                case "56001": return new ValidationException(msg, pex);
                case "56002": return new BusinessException(msg, pex);
                case "56003": return new AuthorizationException(msg, pex);
                case "56004": return new ConcurrencyException(msg, pex);
                case "56005": return new NotFoundException(msg, pex);
                case "56006": return new ConflictException(msg, pex);
                case "56007": return new DuplicateException(msg, pex);
                case "56010": return new DataConsistencyException(msg, pex);

                default:
                    if (CheckDuplicateErrorNumbers && (DuplicateErrorNumbers ?? DefaultDuplicateErrorNumbers).Contains(pex.SqlState))
                        return new DuplicateException(null, pex);

                    break;
            }
        }

        return base.OnDbException(dbex);
    }

    /// <inheritdoc/>
    /// <remarks><see cref="NpgsqlException.IsTransient"/> only flags network-layer failures (dropped socket/timeout) - it does not consider a server-returned <see cref="PostgresException"/>/<see
    /// cref="PostgresException.SqlState"/> at all, so every code relevant to a genuine server error (deadlock, lock timeout, failover, connection limits) must be listed explicitly here. Includes
    /// well-known concurrency errors (deadlock, lock timeout), plus the codes commonly returned by Azure Database for PostgreSQL during planned maintenance/failover and connection-limit throttling
    /// - relevant because Azure Database for PostgreSQL is a supported production target.</remarks>
    protected override bool OnIsTransientException(Exception exception)
    {
        if (exception is NpgsqlException nex)
        {
            if (nex.IsTransient)
                return true;

            switch (nex.SqlState)
            {
                case "40P01": return true;  // deadlock_detected: https://www.postgresql.org/docs/current/errcodes-appendix.html
                case "55P03": return true;  // lock_not_available: raised by an explicit 'SET LOCAL lock_timeout'.
                case "57014": return true;  // query_canceled: statement_timeout (or explicit cancellation) mid-operation.
                case "40001": return true;  // serialization_failure: defensive - relevant under higher isolation levels or advisory-lock contention.

                // Azure Database for PostgreSQL planned maintenance/failover and connection-limit throttling.
                case "57P01": return true;  // admin_shutdown: connection terminated by planned maintenance/failover.
                case "57P02": return true;  // crash_shutdown.
                case "57P03": return true;  // cannot_connect_now: server starting up/in recovery (e.g. just after a failover).
                case "53300": return true;  // too_many_connections.
                case "53400": return true;  // configuration_limit_exceeded.
            }
        }

        return base.OnIsTransientException(exception);
    }

    /// <inheritdoc/>
    protected override void Dispose(bool disposing)
    {
        if (disposing)
            ((IDatabase)this).Connection?.Dispose(); // We created it, so we dispose of it!

        base.Dispose(disposing);
    }
}