public void ExplicitEnlist()
{
using (var conn = new NpgsqlConnection(ConnectionString))
using (var scope = new TransactionScope())
{
conn.Open();
conn.EnlistTransaction(Transaction.Current);
Assert.That(conn.ExecuteNonQuery(@"INSERT INTO data (name) VALUES('test')"), Is.EqualTo(1));
scope.Complete();
}
AssertNoPreparedTransactions();
using (var conn = OpenConnection())
Assert.That(conn.ExecuteScalar(@"SELECT COUNT(*) FROM data"), Is.EqualTo(1));
}
public void Rollback()
{
var connectionString = ConnectionString + ";enlist=true";
using (var scope = new TransactionScope())
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
Assert.That(conn.ExecuteNonQuery(@"INSERT INTO data (name) VALUES('test')"), Is.EqualTo(1));
// No commit
}
AssertNoPreparedTransactions();
using (var conn = OpenConnection())
Assert.That(conn.ExecuteScalar(@"SELECT COUNT(*) FROM data"), Is.EqualTo(0));
}
public void ManyOpenClose()
{
// The connector's _sentRfqPrependedMessages is a byte, too many open/closes made it overflow
for (var i = 0; i < 255; i++)
{
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
}
}
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
}
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
Assert.That(conn.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
}
}
public void ChangeDatabaseDoesNotAffectOtherConnections()
{
using (var conn1 = new NpgsqlConnection(ConnectionString))
using (var conn2 = new NpgsqlConnection(ConnectionString))
{
// Connection 1 changes database
conn1.Open();
conn1.ChangeDatabase("template1");
Assert.That(conn1.ExecuteScalar("SELECT current_database()"), Is.EqualTo("template1"));
// Connection 2's database should not changed
conn2.Open();
Assert.That(conn2.ExecuteScalar("SELECT current_database()"), Is.Not.EqualTo(conn1.Database));
}
}
public void BreakConnectorInPool(bool keepAlive)
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { MaxPoolSize = 1 };
if (keepAlive)
csb.KeepAlive = 1;
using (var conn = new NpgsqlConnection(csb))
{
conn.Open();
var connectorId = conn.ProcessID;
conn.Close();
// Use another connection to kill the connector currently in the pool
using (var conn2 = OpenConnection())
conn2.ExecuteNonQuery($"SELECT pg_terminate_backend({connectorId})");
// Allow some time for the terminate to occur
Thread.Sleep(2000);
conn.Open();
Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Open));
if (keepAlive)
{
Assert.That(conn.ProcessID, Is.Not.EqualTo(connectorId));
Assert.That(conn.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
}
else
{
Assert.That(conn.ProcessID, Is.EqualTo(connectorId));
Assert.That(() => conn.ExecuteScalar("SELECT 1"), Throws.Exception.TypeOf<NpgsqlException>());
}
}
}
/// <summary>
/// Execute scalar
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="commandType"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static object ExecuteScalar(this NpgsqlConnection conn, string cmdText, CommandType commandType, NpgsqlTransaction transaction)
{
conn.CheckNull(nameof(conn));
return(conn.ExecuteScalar(cmdText, null, commandType, transaction));
}
/// <summary>
/// Execute scalar
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <returns></returns>
public static object ExecuteScalar(this NpgsqlConnection conn, string cmdText)
{
conn.CheckNull(nameof(conn));
return(conn.ExecuteScalar(cmdText, null, CommandType.Text, null));
}
/// <summary>
/// Execute scalar
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public static object ExecuteScalar(this NpgsqlConnection conn, string cmdText, NpgsqlParameter[] parameters, CommandType commandType)
{
conn.CheckNull(nameof(conn));
return(conn.ExecuteScalar(cmdText, parameters, commandType, null));
}
/// <summary>
/// Checks that the connector state is properly managed for COPY operations
/// </summary>
void StateAssertions(NpgsqlConnection conn)
{
Assert.That(conn.Connector.State, Is.EqualTo(ConnectorState.Copy));
Assert.That(conn.State, Is.EqualTo(ConnectionState.Open));
Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Open | ConnectionState.Fetching));
Assert.That(() => conn.ExecuteScalar("SELECT 1"), Throws.Exception.TypeOf<InvalidOperationException>());
}
static void Main(string[] args)
{
var store = Wireup
.Init()
.UsingSqlPersistence("db")
.WithDialect(new PostgreSqlDialect())
.InitializeStorageEngine()
.Build();
var id = Guid.NewGuid().ToString();
//Insert some events
using (var stream = store.OpenStream(id))
{
var events = Enumerable.Range(1, 10).Select(i => new EventMessage
{
Body = new Foo {Id = i, Value = "Bar"},
Headers = new Dictionary<string, object>
{
["UserName"] = i < 8 ? "Bob" : "John"
}
});
foreach (var e in events)
{
stream.Add(e);
stream.CommitChanges(Guid.NewGuid());
}
}
//Direct sql query of json
using (var con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
{
var countOfUpdatesByJohn = con
.ExecuteScalar<int>(@"
select count(1)
from commits, jsonb_array_elements(commits.payload) as events
where
streamidoriginal = @id
and
events->'Headers'->>'UserName' = 'John'", new { id });
}
//Load events directly, bypassing neventstore
using (var con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
{
var events = con
.Query<string>(@"
select payload
from commits
where
streamidoriginal = @id
order by streamrevision", new { id })
.SelectMany(json =>
JsonConvert.DeserializeObject<List<EventMessage>>(json, new JsonSerializerSettings
{
TypeNameHandling = TypeNameHandling.Auto,
DefaultValueHandling = DefaultValueHandling.Ignore,
MetadataPropertyHandling = MetadataPropertyHandling.ReadAhead,
NullValueHandling = NullValueHandling.Ignore
}))
.ToList();
}
}
public void TransactionOnRecycledConnection()
{
// Use application name to make sure we have our very own private connection pool
using (var conn = new NpgsqlConnection(ConnectionString + $";Application Name={TestUtil.GetUniqueIdentifier(nameof(TransactionOnRecycledConnection))}"))
{
conn.Open();
var prevConnectorId = conn.Connector.Id;
conn.Close();
conn.Open();
Assert.That(conn.Connector.Id, Is.EqualTo(prevConnectorId), "Connection pool returned a different connector, can't test");
var tx = conn.BeginTransaction();
conn.ExecuteScalar("SELECT 1");
tx.Commit();
NpgsqlConnection.ClearPool(conn);
}
}
public void FailedTransactionOnCloseWithCustom()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { Pooling = true };
using (var conn = new NpgsqlConnection(csb))
{
conn.Open();
var backendProcessId = conn.ProcessID;
conn.BeginTransaction();
using (var badCmd = new NpgsqlCommand("SEL", conn))
{
badCmd.CommandTimeout = NpgsqlCommand.DefaultTimeout + 1;
Assert.That(() => badCmd.ExecuteNonQuery(), Throws.Exception.TypeOf<PostgresException>());
}
// Connection now in failed transaction state, and a custom timeout is in place
conn.Close();
conn.Open();
Assert.That(conn.ProcessID, Is.EqualTo(backendProcessId));
Assert.That(conn.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
}
}