public async Task Save(List <AsnDescriptionCountryInfo> asnNameCountryInfos)
{
_log.LogInformation("Persisting asn to description and country info");
Stopwatch stopwatch = Stopwatch.StartNew();
IEnumerable <IEnumerable <AsnDescriptionCountryInfo> > batches = asnNameCountryInfos.Batch(15000);
string connectionString = await _connectionInfo.GetConnectionStringAsync();
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
await connection.OpenAsync();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
await Truncate(connection, transaction);
foreach (IEnumerable <AsnDescriptionCountryInfo> batch in batches)
{
await Save(batch.ToList(), connection, transaction);
}
await transaction.CommitAsync();
await RefreshMaterializedView(connection);
}
connection.Close();
}
_log.LogInformation($"Persisted asn to description and country info in {stopwatch.ElapsedMilliseconds} ms.");
}
public async Task Save(List <IpToAsn> ipToAsns)
{
_log.LogInformation("Persisting ip to asn info");
Stopwatch stopwatch = Stopwatch.StartNew();
IEnumerable <IEnumerable <IpToAsn> > batches = ipToAsns.Batch(20000);
string connectionString = await _connectionInfo.GetConnectionStringAsync();
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
await connection.OpenAsync();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
await Truncate(connection, transaction);
foreach (IEnumerable <IpToAsn> batch in batches)
{
await Save(batch.ToList(), connection, transaction);
}
await transaction.CommitAsync();
await RefreshMaterializedView(connection);
}
connection.Close();
}
_log.LogInformation($"Persisted ip to asn info in {stopwatch.ElapsedMilliseconds} ms.");
}
public async Task SingleCommit(DbCommand command, CancellationToken cancellation)
{
NpgsqlTransaction tx = null;
try
{
tx = _connection.BeginTransaction();
command.Connection = _connection;
await command.ExecuteNonQueryAsync(cancellation).ConfigureAwait(false);
await tx.CommitAsync(cancellation).ConfigureAwait(false);
}
catch (Exception)
{
if (tx != null)
{
await tx.RollbackAsync(cancellation).ConfigureAwait(false);
}
// Let the caller deal with retries
await reopenConnectionIfNecessary(cancellation).ConfigureAwait(false);
throw;
}
finally
{
tx?.SafeDispose();
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="connection"></param>
/// <param name="executeAsync"></param>
/// <param name="transaction"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <TResult> TransactionalExecuteAsync <TResult>(this NpgsqlConnection connection,
Func <Task <TResult> > executeAsync,
NpgsqlTransaction transaction,
CancellationToken cancellationToken = default)
{
// Variables
var result = default(TResult);
var hasTransaction = transaction != null;
// Open
await connection.EnsureOpenAsync(cancellationToken);
// Ensure transaction
if (hasTransaction == false)
{
#if NET5_0
transaction = await connection.BeginTransactionAsync(cancellationToken);
#else
transaction = connection.BeginTransaction();
#endif
}
try
{
// Execute
if (executeAsync != null)
{
result = await executeAsync();
}
// Commit
if (hasTransaction == false)
{
await transaction.CommitAsync(cancellationToken);
}
}
catch
{
// Rollback
if (hasTransaction == false)
{
await transaction.RollbackAsync(cancellationToken);
}
// Throw
throw;
}
finally
{
// Dispose
if (hasTransaction == false)
{
await transaction.DisposeAsync();
}
}
// Return
return(result);
}
public async Task CommitAsync(CancellationToken token)
{
if (_options.OwnsTransactionLifecycle)
{
await Transaction.CommitAsync(token).ConfigureAwait(false);
await Transaction.DisposeAsync().ConfigureAwait(false);
}
}
public async Task Commit()
{
if (_transaction == null)
{
return;
}
await _transaction.CommitAsync();
_transaction = null;
Commited?.Invoke(this);
}
private async ValueTask ExecuteWithTransactionAsync(string statement, object param)
{
if (_connection.State is ConnectionState.Closed)
{
await _connection.OpenAsync();
}
await using NpgsqlTransaction transaction = await _connection.BeginTransactionAsync(IsolationLevel.ReadCommitted);
await _connection.ExecuteAsync(statement, param, transaction);
await transaction.CommitAsync();
}
public static async Task <bool> ExecTransactionAsync(List <string> sqls, string connection = null)
{
var connString = GetConnection(connection);
bool result = false;
try
{
using (var conn = new NpgsqlConnection(connString))
{
await conn.OpenAsync();
NpgsqlTransaction tran = conn.BeginTransaction();
try
{
foreach (var sql in sqls)
{
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
result = await cmd.ExecuteNonQueryAsync() > 0;
}
}
}
catch (Exception ex0)
{
Log.WriteLog("ERROR-DB：" + ex0.Message);
result = false;
}
finally
{
if (result)
{
await tran.CommitAsync();
}
else
{
await tran.RollbackAsync();
}
}
}
}
catch (Exception ex)
{
Log.WriteLog("ERROR-DB：" + ex.Message);
}
return(result);
}
public async Task <int> WriteAsync(IEnumerable <AppMetric> appMetrics)
{
int affectedRows = 0;
if (appMetrics == null)
{
throw new ArgumentNullException(nameof(appMetrics));
}
if (appMetrics.Count() == 0)
{
return(affectedRows);
}
using (NpgsqlConnection conn = await OpenConnectionAsync())
using (NpgsqlTransaction tx = conn.BeginTransaction())
using (NpgsqlCommand upsertCmd = new NpgsqlCommand(mMetricsUpsertSql, conn, tx))
{
try
{
NpgsqlParameter pMetricId = upsertCmd.Parameters.Add("m_id",
NpgsqlDbType.Varchar);
NpgsqlParameter pMetricCategory = upsertCmd.Parameters.Add("m_category",
NpgsqlDbType.Varchar);
NpgsqlParameter pMetricValue = upsertCmd.Parameters.Add("m_value",
NpgsqlDbType.Bigint);
await upsertCmd.PrepareAsync();
foreach (AppMetric m in appMetrics)
{
pMetricId.Value = m.Id.ValueId;
pMetricCategory.Value = m.Id.ValueCategory;
pMetricValue.Value = m.Value;
affectedRows += await upsertCmd.ExecuteNonQueryAsync();
}
await tx.CommitAsync();
}
catch (Exception)
{
await tx.RollbackAsync();
throw;
}
}
return(affectedRows);
}
public async Task Commit(CancellationToken stoppingToken)
{
if (_entries != null)
{
var messagesToSave = _entries.Where(x => x.ProcessedUtc.HasValue);
foreach (var message in messagesToSave)
{
var sql = $"update \"_outbox\" set " +
$"{Q(nameof(OutboxEntry.ProcessedUtc))} = @processedUtc " +
$"where {Q("Id")} = @id;";
await _connection.ExecuteAsync(sql, new { id = message.MessageId, processedUtc = DateTime.UtcNow }, _transaction);
}
}
await _transaction.CommitAsync(stoppingToken);
}
public async Task InsertTaskAndResultDataAsync(IEnumerable <Tuple <QueuedTask, QueuedTaskResult> > queuedTaskPairs)
{
using (NpgsqlConnection conn = await OpenDbConnectionAsync())
using (NpgsqlTransaction tx = conn.BeginTransaction())
{
foreach (Tuple <QueuedTask, QueuedTaskResult> p in queuedTaskPairs)
{
if (p.Item1 != null)
{
await AddQueuedTaskAsync(p.Item1, conn, tx);
}
if (p.Item2 != null)
{
await AddQueuedTaskResultAsync(p.Item2, conn, tx);
}
}
await tx.CommitAsync();
}
}
public async Task Save(List <string> publicSuffixEntries)
{
_log.LogInformation("Persisting public suffix list");
Stopwatch stopwatch = Stopwatch.StartNew();
string connectionString = await _connectionInfo.GetConnectionStringAsync();
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
await connection.OpenAsync();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
await Truncate(connection, transaction);
await Save(publicSuffixEntries, connection, transaction);
await transaction.CommitAsync();
await RefreshMaterializedView(connection);
}
connection.Close();
}
_log.LogInformation($"Persisted public suffix list in {stopwatch.ElapsedMilliseconds} ms.");
}
public async Task <BulkOperationResult <TEntity> > ExecuteOperationAsync <TEntity>(
SqlOperation operation,
NpgsqlConnection connection,
ICollection <TEntity> elements,
CancellationToken cancellationToken)
where TEntity : class
{
if (elements == null)
{
throw new ArgumentNullException(nameof(elements));
}
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (_logger.IsEnabled(LogLevel.Trace))
{
_logger.LogTrace($"Preparing a bulk {operation} operation");
}
Stopwatch sw = null;
if (_logger.IsEnabled(LogLevel.Information))
{
sw = Stopwatch.StartNew();
}
var entityType = typeof(TEntity);
if (!_options.SupportedEntityTypes.ContainsKey(entityType))
{
throw new ArgumentException($"Mapping for type '{entityType.FullName}' was not found.", nameof(elements));
}
var entityProfile = _options.SupportedEntityTypes[entityType];
var maximumEntitiesPerSent = GetCurrentMaximumSentElements(entityProfile);
var currentFailureStrategy = GetCurrentFailureStrategy(entityProfile);
var result = currentFailureStrategy == FailureStrategies.IgnoreFailure
? new BulkOperationResult <TEntity>(elements.Count)
: null;
if (elements.Count == 0)
{
return(result);
}
var(needOperatedElements, needNotOperatedElements, needProblemElements) = GetExtendedFailureInformation(entityProfile);
NpgsqlTransaction transaction = null;
if (currentFailureStrategy == FailureStrategies.StopEverythingAndRollback)
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync(cancellationToken);
}
transaction = connection.BeginTransaction();
}
var totalSqlSize = 0L;
var totalSqlCommandCount = 0;
if (maximumEntitiesPerSent == 0 || elements.Count <= maximumEntitiesPerSent)
{
try {
var(sqlSize, sqlCommandCount) = await ExecutePortionAsync(operation,
connection,
elements,
entityProfile,
cancellationToken);
totalSqlSize += sqlSize;
totalSqlCommandCount += sqlCommandCount;
if (transaction != null)
{
await transaction.CommitAsync(cancellationToken);
}
result?.Operated.AddRange(elements);
if (sw != null)
{
var(size, suffix) = totalSqlSize.PrettifySize();
_logger.LogInformation($"{operation}ed {elements.Count} {entityProfile.EntityType.Name} elements, {totalSqlCommandCount} sql commands ({size:0.00}{suffix}) in total {sw.ElapsedMilliseconds / 1000f:0.000} s");
}
}
catch (Exception ex) {
var notOperatedElements = needNotOperatedElements ? elements : null;
var operatedElements = needOperatedElements ? new List <TEntity>() : null;
var problemElements = needProblemElements ? elements : null;
await ProcessFailureAsync(operation,
currentFailureStrategy,
ex,
transaction,
operatedElements,
notOperatedElements,
problemElements,
cancellationToken);
result?.NotOperated.AddRange(elements);
}
}
else
{
var operated = needOperatedElements ? new List <TEntity>(elements.Count) : null;
var notOperated = needNotOperatedElements ? new List <TEntity>(elements.Count) : null;
var problem = needProblemElements ? new List <TEntity>(maximumEntitiesPerSent) : null;
var iterations = Math.Round((decimal)elements.Count / maximumEntitiesPerSent,
MidpointRounding.AwayFromZero);
for (var i = 0; i < iterations; i++)
{
var portion = elements.Skip(i * maximumEntitiesPerSent)
.Take(maximumEntitiesPerSent)
.ToList();
try {
var(sqlSize, sqlCommandCount) = await ExecutePortionAsync(operation,
connection,
portion,
entityProfile,
cancellationToken);
totalSqlSize += sqlSize;
totalSqlCommandCount += sqlCommandCount;
operated?.AddRange(portion);
result?.Operated.AddRange(portion);
}
catch (Exception ex) {
if (currentFailureStrategy != FailureStrategies.IgnoreFailure)
{
problem?.AddRange(portion);
}
result?.NotOperated.AddRange(portion);
switch (currentFailureStrategy)
{
// Ignore strategy is ignored here because it does not throw an SqlBulkExecutionException
case FailureStrategies.StopEverything:
{
//Since i is not incremented yet, this command will add current portion to notOperated as well as the rest of elements
notOperated?.AddRange(elements.Skip(i * maximumEntitiesPerSent)
.ToList());
break;
}
case FailureStrategies.StopEverythingAndRollback:
{
operated?.Clear();
notOperated?.AddRange(elements);
break;
}
}
await ProcessFailureAsync(operation,
currentFailureStrategy,
ex,
transaction,
operated,
notOperated,
problem,
cancellationToken);
}
}
if (transaction != null)
{
await transaction.CommitAsync(cancellationToken);
}
if (sw != null)
{
var(size, suffix) = totalSqlSize.PrettifySize();
_logger.LogInformation($"{operation}ed {elements.Count} {entityProfile.EntityType.Name} elements in {iterations} steps, {totalSqlCommandCount} sql commands ({size:0.00}{suffix}) in total {sw.ElapsedMilliseconds / 1000f:0.000} s");
}
}
return(result);
}
public virtual async Task Commit()
{
await _transaction.CommitAsync();
}
public Task CommitAsync(CancellationToken cancellationToken) => _transaction.CommitAsync(cancellationToken);
public async Task <IQueuedTaskToken> DequeueAsync(params string[] selectTaskTypes)
{
NpgsqlConnection conn = null;
QueuedTask dequeuedTask = null;
QueuedTaskResult dequeuedTaskResult = null;
PostgreSqlQueuedTaskToken dequeuedTaskToken = null;
MonotonicTimestamp startDequeue;
DateTimeOffset refNow = mTimestampProvider.GetNow();
CheckNotDisposedOrThrow();
try
{
mLogger.DebugFormat("Begin dequeue task. Looking for types: {0}.",
string.Join <string>(",", selectTaskTypes));
startDequeue = MonotonicTimestamp
.Now();
conn = await OpenQueueConnectionAsync();
if (conn == null)
{
return(null);
}
using (NpgsqlTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{
//1. Dequeue means that we acquire lock on a task in the queue
// with the guarantee that nobody else did, and respecting
// the priority and static locks (basically the task_locked_until which says
// that it should not be pulled out of the queue until the
// current abstract time reaches that tick value)
dequeuedTask = await TryDequeueTaskAsync(selectTaskTypes, refNow, conn, tx);
if (dequeuedTask != null)
{
//2. Mark the task as being "Processing" and pull result info
// The result is stored separately and it's what allows us to remove
// the task from the queue at step #2,
// whils also tracking it's processing status and previous results
dequeuedTaskResult = await TryUpdateTaskResultAsync(dequeuedTask, conn, tx);
if (dequeuedTaskResult != null)
{
await tx.CommitAsync();
dequeuedTaskToken = new PostgreSqlQueuedTaskToken(dequeuedTask,
dequeuedTaskResult,
refNow);
}
}
if (dequeuedTaskToken != null)
{
IncrementDequeueCount(MonotonicTimestamp.Since(startDequeue));
}
else
{
await tx.RollbackAsync();
}
}
}
finally
{
if (conn != null)
{
await conn.CloseAsync();
conn.Dispose();
}
}
return(dequeuedTaskToken);
}
public async Task <int> WriteAsync(IEnumerable <TaskPerformanceStats> executionTimeInfoBatch)
{
int affectedRows = 0;
if (executionTimeInfoBatch == null)
{
throw new ArgumentNullException(nameof(executionTimeInfoBatch));
}
if (executionTimeInfoBatch.Count() == 0)
{
return(0);
}
using (NpgsqlConnection conn = await OpenConnectionAsync())
using (NpgsqlTransaction tx = conn.BeginTransaction())
using (NpgsqlCommand upsertCmd = new NpgsqlCommand(mPerfMonInfoUpsertSql, conn, tx))
{
try
{
NpgsqlParameter pPayloadType = upsertCmd.Parameters
.Add("payload_type", NpgsqlDbType.Varchar);
NpgsqlParameter pNExecutionCycles = upsertCmd.Parameters
.Add("n_execution_cycles", NpgsqlDbType.Bigint);
NpgsqlParameter pLastExecutionTime = upsertCmd.Parameters
.Add("last_execution_time", NpgsqlDbType.Bigint);
NpgsqlParameter pAvgExecutionTime = upsertCmd.Parameters
.Add("avg_execution_time", NpgsqlDbType.Bigint);
NpgsqlParameter pFastestExecutionTime = upsertCmd.Parameters
.Add("fastest_execution_time", NpgsqlDbType.Bigint);
NpgsqlParameter pLongestExecutionTime = upsertCmd.Parameters
.Add("longest_execution_time", NpgsqlDbType.Bigint);
NpgsqlParameter pTotalExecutionTime = upsertCmd.Parameters
.Add("total_execution_time", NpgsqlDbType.Bigint);
await upsertCmd.PrepareAsync();
foreach (TaskPerformanceStats s in executionTimeInfoBatch)
{
pPayloadType.Value = s.PayloadType;
pNExecutionCycles.Value = 1;
pLastExecutionTime.Value = s.DurationMilliseconds;
pAvgExecutionTime.Value = s.DurationMilliseconds;
pFastestExecutionTime.Value = s.DurationMilliseconds;
pLongestExecutionTime.Value = s.DurationMilliseconds;
pTotalExecutionTime.Value = s.DurationMilliseconds;
affectedRows += await upsertCmd.ExecuteNonQueryAsync();
}
await tx.CommitAsync();
}
catch (Exception)
{
await tx.RollbackAsync();
throw;
}
}
return(affectedRows);
}