Hello Everyone!
I have been looking for possible solution throughout the internet and github issues but I am unsuccessful and I have to create this issue. I would be happy if there is already simillar issue and if you forward to me.
My problem is as follows. I have several serializable operations in my application and I use same template for them. I have to execute those operations as quickly as possible. In order to that, I have to keep round trips as minimum as possible.
If I were programming with MSSQL, I would just write a single stored procedure and at beginning of it, I would write "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" and do DML operations and then commit it in a single procedure which makes it single trip.
However, in PostgreSQL, one of the limitation is that you cannot set transaction isolation level in the stored procedure. To set isolation level, I have to use below line.
await using var transaction = await connection.BeginTransactionAsync(IsolationLevel.Serializable); /* ROUND TRIP */
If we assume above line Level 1 (L1) for our transaction, and execution of our proc at L2. Then to commit all changes, I have to turn back to L1 since it is rule for PostgreSQL as below.
await transaction.CommitAsync(); /* ROUND TRIP */
I would like to ask how can I reduce all of these operations to single trip, if this is the best we can do, can we make a feature request to add option to set isolation level in NpgsqlCommand or ExecuteNonQueryAsync? So, I can remove BeginTransactionAsync statement and add the commit statement in procedure. Therefore single trip would be achieved. Thanks in advance.
Best regards.
Note: return await _resiliencePipeline.ExecuteAsync(async cancellationToken => is a retry policy defined with Polly library since this is serializable transaction and preparing for retry policy is a must.
Application Code:
public async Task<long> AddAsync(string username, string email, byte[] passwordHash, byte[] passwordSalt)
{
try
{
return await _resiliencePipeline.ExecuteAsync(async cancellationToken =>
{
var sql = "public.usp_user_create";
var currentInstant = _clock.GetCurrentInstant();
await using var connection = await _npgsqlDataSource.OpenConnectionAsync();
await using var transaction = await connection.BeginTransactionAsync(IsolationLevel.Serializable); /* ROUND TRIP */
await using var command = new NpgsqlCommand(sql, connection, transaction)
{
CommandType = CommandType.StoredProcedure,
Parameters =
{
new() { Value = username },
new() { Value = email },
new() { Value = passwordHash },
new() { Value = passwordSalt },
new() { Value = EmailConfirmationEncryptor.Encrypt(email + currentInstant.ToString()) },
new() { Value = currentInstant.Plus(Duration.FromDays(7)) },
new() { Value = currentInstant },
new() { DbType = DbType.Int64, Direction = ParameterDirection.Output }
}
};
await command.ExecuteNonQueryAsync();
var userId = command.Parameters[7].Value;
await transaction.CommitAsync(); /* ROUND TRIP */
return (long)userId;
});
}
catch
{
return -1;
}
}
Stored Procedure:
CREATE OR REPLACE PROCEDURE public.usp_user_create
(
p_username TEXT,
p_email TEXT,
p_password_hash BYTEA,
p_password_salt BYTEA,
p_hash UUID,
p_expiration_timestamp TIMESTAMP(6) WITH TIME ZONE,
p_create_timestamp TIMESTAMP(6) WITH TIME ZONE,
OUT p_user_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO public."user" (username, email, password_hash, password_salt, is_activated_user, create_timestamp, update_timestamp)
VALUES (p_username, p_email, p_password_hash, p_password_salt, false, p_create_timestamp, p_create_timestamp)
RETURNING id INTO p_user_id;
INSERT INTO public.user_email_confirmation (id, hash, expiration_timestamp, create_timestamp, update_timestamp)
VALUES (p_user_id, p_hash, p_expiration_timestamp, p_create_timestamp, p_create_timestamp);
END;
$$;
Hello Everyone!
I have been looking for possible solution throughout the internet and github issues but I am unsuccessful and I have to create this issue. I would be happy if there is already simillar issue and if you forward to me.
My problem is as follows. I have several serializable operations in my application and I use same template for them. I have to execute those operations as quickly as possible. In order to that, I have to keep round trips as minimum as possible.
If I were programming with MSSQL, I would just write a single stored procedure and at beginning of it, I would write "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" and do DML operations and then commit it in a single procedure which makes it single trip.
However, in PostgreSQL, one of the limitation is that you cannot set transaction isolation level in the stored procedure. To set isolation level, I have to use below line.
await using var transaction = await connection.BeginTransactionAsync(IsolationLevel.Serializable); /* ROUND TRIP */If we assume above line Level 1 (L1) for our transaction, and execution of our proc at L2. Then to commit all changes, I have to turn back to L1 since it is rule for PostgreSQL as below.
await transaction.CommitAsync(); /* ROUND TRIP */I would like to ask how can I reduce all of these operations to single trip, if this is the best we can do, can we make a feature request to add option to set isolation level in NpgsqlCommand or ExecuteNonQueryAsync? So, I can remove BeginTransactionAsync statement and add the commit statement in procedure. Therefore single trip would be achieved. Thanks in advance.
Best regards.
Note:
return await _resiliencePipeline.ExecuteAsync(async cancellationToken =>is a retry policy defined with Polly library since this is serializable transaction and preparing for retry policy is a must.Application Code:
Stored Procedure: