PostgreSQL监听通知触发时出现53300错误（客户端数量过多）的解决方案咨询

Hey there, let's work through your two key issues with PostgreSQL LISTEN/NOTIFY and SignalR—those 53300 "too many clients" errors, and notifications dying when you close the connection. I’ve built similar real-time notification systems before, so here’s what I’d recommend:

1. Resolving Error 53300: "sorry, too many clients already" #

This error pops up when your app exceeds PostgreSQL’s maximum allowed connections. Let’s fix this from both the database and application side:

• Check and adjust PostgreSQL’s max connections
  First, verify your current connection limit with this query:

  SHOW max_connections;
  

  The default is often 100, which can be too low for apps spawning multiple LISTEN connections. Update this in your postgresql.conf file (look for the max_connections setting) or via your cloud database provider’s console. Just note: more connections mean more memory usage, so adjust based on your server’s resources.

• Stop creating a new connection for every LISTEN request
  Looking at your code, every call to BrokerConfig() spins up a new NpgsqlConnection. That’s a fast track to hitting connection limits. Instead, use a single long-lived connection for all LISTEN operations—PostgreSQL’s NOTIFY will send all relevant messages to this one connection, so you don’t need multiple connections listening to the same channel.

• Tweak your connection string settings
  Your current string has Connection Idle Lifetime=0;Timeout=0;Command Timeout=0; which can cause issues:

  • Connection Idle Lifetime=0 keeps connections in the pool forever (okay for your long-lived LISTEN connection, but bad for short-lived ones)
  • Timeout=0 and Command Timeout=0 mean infinite waits, which can hang your app if the database is unresponsive. Set reasonable values (e.g., Timeout=30;Command Timeout=60) instead.

2. Keeping Notifications Running When Connections Close #

PostgreSQL’s LISTEN/NOTIFY is tied directly to a connection—close the connection, and you stop receiving notifications. Here’s how to fix this:

• Maintain a persistent connection with auto-reconnect
  You need to keep the LISTEN connection open indefinitely, and automatically re-establish it if it drops (e.g., network blips, database restarts). The best way to do this in an ASP.NET Core app is with a background hosted service, which runs continuously in the background.

Example Optimized Code (Background Service + SignalR) #

Here’s a rewritten version of your logic using a hosted service to manage the long-lived connection and handle reconnections:

using Microsoft.AspNetCore.SignalR;
using Npgsql;

public class TicketNotificationListener : BackgroundService
{
    private readonly IHubContext<YourSignalRHub> _hubContext;
    private readonly string _dbConnectionString = "host=x.x.x.x;Port=5432;Database=Ticket;User Id=someid;Password=pwd;Timeout=30;Command Timeout=60;";
    private NpgsqlConnection _dbConnection;

    // Inject your SignalR hub context to push notifications to clients
    public TicketNotificationListener(IHubContext<YourSignalRHub> hubContext)
    {
        _hubContext = hubContext;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            try
            {
                // Initialize and open the connection
                _dbConnection = new NpgsqlConnection(_dbConnectionString);
                _dbConnection.Notification += OnTicketNotificationReceived;
                
                await _dbConnection.OpenAsync(stoppingToken);
                
                // Subscribe to the notification channel
                using var listenCmd = new NpgsqlCommand("LISTEN notifytickets;", _dbConnection);
                await listenCmd.ExecuteNonQueryAsync(stoppingToken);

                // Wait for notifications until the connection drops or we get a cancellation signal
                await _dbConnection.WaitAsync(stoppingToken);
            }
            catch (Exception ex)
            {
                // Log the error (use your logging framework instead of Console)
                Console.WriteLine($"Notification connection failed: {ex.Message}. Retrying in 5s...");
                // Wait before retrying to avoid overwhelming the database
                await Task.Delay(5000, stoppingToken);
            }
            finally
            {
                // Clean up the connection if it exists
                _dbConnection?.Dispose();
            }
        }
    }

    private async void OnTicketNotificationReceived(object sender, NpgsqlNotificationEventArgs e)
    {
        // Parse the notification payload (adjust based on your data format)
        var ticketUpdate = ParseNotificationPayload(e.Payload);
        
        // Push the update to all SignalR clients
        await _hubContext.Clients.All.SendAsync("ReceiveTicketUpdate", ticketUpdate);
    }

    private object ParseNotificationPayload(string payload)
    {
        // Add your logic to convert the payload string to a usable object (e.g., JSON deserialization)
        return payload;
    }
}

• Why this works:
  • The background service runs continuously, keeping the connection open.
  • If the connection drops (for any reason), it automatically retries after a short delay.
  • Only one connection is used for listening, so you won’t hit the max connections limit.
  • SignalR’s hub context is injected directly, making it easy to push notifications to clients.

Final Tips #

• Monitor your connections: Use this query to check active connections and ensure you’re not leaking them:

  SELECT count(*) FROM pg_stat_activity WHERE datname = 'Ticket';
  

• Register the hosted service: Don’t forget to add your background service to Program.cs:

  builder.Services.AddHostedService<TicketNotificationListener>();
  

内容的提问来源于stack exchange，提问作者Nitin Soni