Devart Forums

Hello,

we get 'NOTICE: there is no transaction in progress' message on the server when closing a connection that contained a transaction. Pseudo code:

using (PgSqlConnection conn = new PgSqlConnection(..))
{
conn.Open();

using (PgSqlTransaction trans = conn.BeginTransaction(...))
{
...execute command under the transaction

if (everythingWentOk)
{
trans.Commit();
}
else
{
trans.Rollback();
}
}
} // !!!! There is the problem here: when executing "conn.Close()", the DB server complains

When we call conn.Commit() the situation does not change.

What are we doing wrong? Is it a bug in the dotConnect driver?

Thanks in advance,
Marek Kulvejt

P.S. The data are stored well and everything seems to work. The problem is that we save a lot of data and we get approx. 1GB of log every day. Moreover, the database itself must do some work just to execute an useless command.

Hello,

allow me one more question please. I'll put it this way:

As we always use a new connection when we want to communicate with the database - something like this:

using (PgSqlConnection conn = new PgSqlConnection(connString))
{
conn.Open();

.. do some DB work
}

How can happen that after the failure I've already described and calling ClearAllPools/ClearPools(), the dotConnect is not able to connect to the database anymore? When the process is restarted then everything works well again. So there are no problems with the databe server itself, networking etc...

According to me, there must be some "static" state that somehow breaks the functionality of the dotConnect.

Could there be something like this? The problem is really weird and it cannot be reproduced in a predictable manner. It just happens sometimes.

Best regards,
Marek

Where can I find the 5.* version? Only 4.5 is present in the download section (...The latest version is 4.50.33 (21-May-2009)....).

Thank you for the answer. I have some additional questions:

1) What is the proper usage of ClearPool()?

- as I use 'using (PqSqlConnection....' - should I call it immediatelly when it happens?

2) What is the difference between ClearPool() and ClearAllPools()? Why one should and other should not work in my scenario? I mean: it does not start working even after 30 seconds. It stops working completely 'forever'.

3) What is the performance impact of not using the connection pool?

Thans for the answers,
Marek

Hello everybody,

we have a problem with reconnection after DB server restart. First we get Socket anf "FATAL" exception (which is correct - we are stopping the SQL server). But then, when the server runs again, we are not able to make a connection and we get "CONNECTION FAILURE" exception. Clearing connection pool does not have any effect. When we restart the .NET application. Everything works well again (-> no problem with the SQL server itself).

We get the following sequence of exceptions:

Devart.Data.PostgreSql.PgSqlException: An established connection was aborted by the software in your host machine ---> System.Net.Sockets.SocketException: An established connection was aborted by the software in your host machine
at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
--- End of inner exception stack trace ---
at Devart.Data.PostgreSql.a.a(Exception A_0)
at Devart.Data.PostgreSql.a.a()
at Devart.Data.PostgreSql.a.f()
at Devart.Data.PostgreSql.t.a(Char A_0)
at Devart.Data.PostgreSql.t.a(Boolean A_0, Boolean A_1)
at Devart.Data.PostgreSql.t.af()
at Devart.Data.PostgreSql.t.b(String A_0)
at Devart.Data.PostgreSql.t.l()
at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at Devart.Data.PostgreSql.PgSqlTransaction..ctor(PgSqlConnection A_0, IsolationLevel A_1)
at Devart.Data.PostgreSql.n.a(IsolationLevel A_0)
at Devart.Common.DbConnectionBase.BeginDbTransaction(IsolationLevel isolationLevel)
at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
at ESG.Db.Relational.Shared.RelationalDataAccess.ExecutePlan(DbConnection connection, DatabaseOperationPlan plan, EsgTraceSource serverTraceSource)
EsgDb Error: 0 : Error in SaveData. Message: Devart.Data.PostgreSql.PgSqlException: An established connection was aborted by the software in your host machine ---> System.Net.Sockets.SocketException: An established connection was aborted by the software in your host machine
at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
--- End of inner exception stack trace ---
at ESG.Db.Relational.Shared.RelationalDataAccess.SynchronizeChanges(Credentials credentials, IEnumerable`1 records, ProcessQueriesResult& operationResult, DatabaseOperationPlan plan, Exception exc, Dictionary`2 usedServerVariables)
at ESG.Db.Relational.Shared.RelationalDataAccess.SaveData(Credentials credentials, IEnumerable`1 records)
at ESG.Db.Server.Engine.ServerImplementation.SaveDataPrivate(Credentials credentials, IEnumerable`1 records)
EsgDb Verbose: 0 : Entering SaveData
EsgDb Error: 0 : Error in SaveData. Message: Devart.Data.PostgreSql.PgSqlException: FATAL: the database system is shutting down

at Devart.Data.PostgreSql.n.x()
at Devart.Data.PostgreSql.n.a()
at Devart.Common.DbConnectionFactory.a(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.PostgreSql.PgSqlConnection.Open()
at ESG.Db.Relational.Shared.RelationalDataAccess.SaveData(Credentials credentials, IEnumerable`1 records)
at ESG.Db.Server.Engine.ServerImplementation.SaveDataPrivate(Credentials credentials, IEnumerable`1 records)
EsgDb Verbose: 0 : Entering SaveData
EsgDb Error: 0 : Error in SaveData. Message: Devart.Data.PostgreSql.PgSqlException: CONNECTION FAILURE
at Devart.Data.PostgreSql.PgSqlConnection.b()
at Devart.Data.PostgreSql.PgSqlConnection.Open()
at ESG.Db.Relational.Shared.RelationalDataAccess.SaveData(Credentials credentials, IEnumerable`1 records)
at ESG.Db.Server.Engine.ServerImplementation.SaveDataPrivate(Credentials credentials, IEnumerable`1 records)
EsgDb Verbose: 0 : Entering SaveData
EsgDb Error: 0 : Error in SaveData. Message: Devart.Data.PostgreSql.PgSqlException: CONNECTION FAILURE
at Devart.Data.PostgreSql.PgSqlConnection.b()
at Devart.Data.PostgreSql.PgSqlConnection.Open()
at ESG.Db.Relational.Shared.RelationalDataAccess.SaveData(Credentials credentials, IEnumerable`1 records)
at ESG.Db.Server.Engine.ServerImplementation.SaveDataPrivate(Credentials credentials, IEnumerable`1 records)


Thanks in advance,
Marek