I have a background service that runs scheduled tasks as well as looks for notification events in PostgreSQL. Every 30 minutes or so the following function gets called.
public static async Task Main(ILogger<Worker> _logger, SiteStructure _s, int milliseconds)
{
logger = _logger;
s = _s;
logger.LogInformation("Running GIS scheduled job: {time}", DateTimeOffset.Now);
string connectionString = s.geoConfigObj.getConnectionString();
await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();
conn.Notification += (o, e) =>LoadData(_logger,conn,e.Payload);
await using (var cmd = new NpgsqlCommand("LISTEN data_load;", conn))
cmd.ExecuteNonQuery();
conn.Wait(milliseconds); // wait for events
Console.WriteLine("Do a query.");
}
The function will listen for notifications for 30 minutes then it will timeout on the wait event and do some other maintenance type stuff.
When it calls the LoadData function I get an error saying:
The connection is already in state 'Waiting'
Can I not reuse my connection to process the data?