| | | 1 | | using Dapper; |
| | | 2 | | using Npgsql; |
| | | 3 | | |
| | | 4 | | namespace Syki.Daemon.Commands; |
| | | 5 | | |
| | 2 | 6 | | public class CommandsProcessorDbListener(IConfiguration configuration, IServiceScopeFactory serviceScopeFactory) : Backg |
| | | 7 | | { |
| | 2 | 8 | | private readonly SemaphoreSlim _throttler = new(10); |
| | | 9 | | |
| | | 10 | | protected override async Task ExecuteAsync(CancellationToken stoppingToken) |
| | | 11 | | { |
| | 2 | 12 | | await using var connection = new NpgsqlConnection(configuration.Database().ConnectionString); |
| | | 13 | | |
| | 2 | 14 | | await connection.OpenAsync(stoppingToken); |
| | | 15 | | |
| | 2 | 16 | | await CreateCommandTrigger(connection); |
| | 2 | 17 | | await CreateCommandBatchTrigger(connection); |
| | | 18 | | |
| | 2 | 19 | | _ = Task.Run(async () => |
| | 2 | 20 | | { |
| | 2 | 21 | | var processor = new CommandsProcessor(serviceScopeFactory); |
| | 2 | 22 | | await processor.Run(); |
| | 4 | 23 | | }, CancellationToken.None); |
| | | 24 | | |
| | 2 | 25 | | connection.Notification += async (o, e) => |
| | 2 | 26 | | { |
| | 2378 | 27 | | if (!await _throttler.WaitAsync(0, CancellationToken.None)) |
| | 0 | 28 | | return; |
| | 2 | 29 | | |
| | 2378 | 30 | | _ = Task.Run(async () => |
| | 2378 | 31 | | { |
| | 2378 | 32 | | try |
| | 2378 | 33 | | { |
| | 2378 | 34 | | var processor = new CommandsProcessor(serviceScopeFactory); |
| | 2378 | 35 | | await processor.Run(); |
| | 2378 | 36 | | } |
| | 0 | 37 | | catch (Exception ex) |
| | 2378 | 38 | | { |
| | 0 | 39 | | Console.WriteLine($"Error processing command: {ex.Message}"); |
| | 0 | 40 | | } |
| | 2378 | 41 | | finally |
| | 2378 | 42 | | { |
| | 2378 | 43 | | _throttler.Release(); |
| | 2378 | 44 | | } |
| | 4756 | 45 | | }, CancellationToken.None); |
| | 2380 | 46 | | }; |
| | | 47 | | |
| | 2 | 48 | | await using (var cmd = new NpgsqlCommand("LISTEN new_command;", connection)) |
| | | 49 | | { |
| | 2 | 50 | | await cmd.ExecuteNonQueryAsync(stoppingToken); |
| | | 51 | | } |
| | | 52 | | |
| | 2382 | 53 | | while (!stoppingToken.IsCancellationRequested) |
| | | 54 | | { |
| | 2380 | 55 | | await connection.WaitAsync(stoppingToken).ConfigureAwait(ConfigureAwaitOptions.SuppressThrowing); |
| | | 56 | | } |
| | 2 | 57 | | } |
| | | 58 | | |
| | | 59 | | private static async Task CreateCommandTrigger(NpgsqlConnection connection) |
| | | 60 | | { |
| | | 61 | | const string sql = @" |
| | | 62 | | CREATE OR REPLACE FUNCTION notify_new_command_function() |
| | | 63 | | RETURNS trigger |
| | | 64 | | LANGUAGE 'plpgsql' |
| | | 65 | | AS $BODY$ |
| | | 66 | | BEGIN |
| | | 67 | | PERFORM pg_notify('new_command', ''); |
| | | 68 | | RETURN NEW; |
| | | 69 | | END |
| | | 70 | | $BODY$; |
| | | 71 | | |
| | | 72 | | CREATE OR REPLACE TRIGGER notify_new_command_trigger |
| | | 73 | | AFTER INSERT ON syki.commands |
| | | 74 | | EXECUTE PROCEDURE notify_new_command_function(); |
| | | 75 | | "; |
| | | 76 | | |
| | 2 | 77 | | await connection.ExecuteAsync(sql); |
| | 2 | 78 | | } |
| | | 79 | | |
| | | 80 | | private static async Task CreateCommandBatchTrigger(NpgsqlConnection connection) |
| | | 81 | | { |
| | | 82 | | const string sql = @" |
| | | 83 | | CREATE OR REPLACE FUNCTION update_command_batch_function() |
| | | 84 | | RETURNS trigger |
| | | 85 | | LANGUAGE 'plpgsql' |
| | | 86 | | AS $BODY$ |
| | | 87 | | DECLARE |
| | | 88 | | batch_status text; |
| | | 89 | | batch_success boolean; |
| | | 90 | | batch_next_command_id uuid; |
| | | 91 | | BEGIN |
| | | 92 | | IF NEW.batch_id IS NULL THEN |
| | | 93 | | RETURN NEW; |
| | | 94 | | END IF; |
| | | 95 | | |
| | | 96 | | SELECT |
| | | 97 | | status, next_command_id INTO batch_status, batch_next_command_id |
| | | 98 | | FROM syki.command_batches |
| | | 99 | | WHERE id = NEW.batch_id |
| | | 100 | | FOR UPDATE; |
| | | 101 | | |
| | | 102 | | IF batch_status = 'Error' THEN |
| | | 103 | | RETURN NEW; |
| | | 104 | | END IF; |
| | | 105 | | |
| | | 106 | | IF batch_status = 'Pending' THEN |
| | | 107 | | UPDATE syki.command_batches |
| | | 108 | | SET status = 'Processing' |
| | | 109 | | WHERE id = NEW.batch_id; |
| | | 110 | | END IF; |
| | | 111 | | |
| | | 112 | | IF NEW.status = 'Error' THEN |
| | | 113 | | UPDATE syki.command_batches |
| | | 114 | | SET status = 'Error' |
| | | 115 | | WHERE id = NEW.batch_id; |
| | | 116 | | RETURN NEW; |
| | | 117 | | END IF; |
| | | 118 | | |
| | | 119 | | SELECT count(1) = 0 INTO batch_success |
| | | 120 | | FROM syki.commands |
| | | 121 | | WHERE batch_id = NEW.batch_id AND status <> 'Success'; |
| | | 122 | | |
| | | 123 | | IF NOT batch_success THEN |
| | | 124 | | RETURN NEW; |
| | | 125 | | END IF; |
| | | 126 | | |
| | | 127 | | UPDATE syki.command_batches |
| | | 128 | | SET status = 'Success', processed_at = now() |
| | | 129 | | WHERE id = NEW.batch_id; |
| | | 130 | | |
| | | 131 | | IF batch_next_command_id IS NOT NULL THEN |
| | | 132 | | UPDATE syki.commands |
| | | 133 | | SET status = 'Pending' |
| | | 134 | | WHERE id = batch_next_command_id; |
| | | 135 | | |
| | | 136 | | PERFORM pg_notify('new_command', ''); |
| | | 137 | | END IF; |
| | | 138 | | |
| | | 139 | | RETURN NEW; |
| | | 140 | | END |
| | | 141 | | $BODY$; |
| | | 142 | | |
| | | 143 | | CREATE OR REPLACE TRIGGER update_command_batch_trigger |
| | | 144 | | AFTER UPDATE ON syki.commands |
| | | 145 | | FOR EACH ROW EXECUTE PROCEDURE update_command_batch_function(); |
| | | 146 | | "; |
| | | 147 | | |
| | 2 | 148 | | await connection.ExecuteAsync(sql); |
| | 2 | 149 | | } |
| | | 150 | | } |