Devart Forums

I am trying to connect via PGPool and have similar issues as mentioned here:
http://www.devart.com/forums/viewtopic.php?p=51100

It would appear doing selects in a transaction causes a problem with my front end application timing out. Yet if I connect via pgadmin from the same machine the query finishes almost instantly < 2s

Via dotconnect the same query can take 10 seconds or not at all and it would appear the transaction is the issue.

Have you any solution to this? or what replication solutions have you tested successfully with dotConnect?

1. CONNECTION FAILURE.
As Jp.gouigoux mentioned in that thread (http://www.devart.com/forums/viewtopic.php?p=51100), this problem occured after a restart of pgpool-II or his PostgreSQL Server. In this case the issue is associated with invalid connections that remained in the pool. Connection pool can be cleared with the Devart.Data.PostgreSql.PgSqlConnection.ClearPool() and ClearAllPools() methods, if our provider's pool is used (http://www.devart.com/forums/viewtopic.php?t=15247). Also the reason can be the version (2 or 3) of the protocol you are using in your connection string . We recommend you to use protocol 3. We sometimes encountered such problem with protocol 2 in our autotests. If you use protocol 3, this is rather a network problem.

As for clearing pool of pgpool-II, please refer to the pgpool-II documentation.

2. The performance issue.
Why are you using transaction when performing select? Transaction envolves additional resources, so it can take a bit more time for execution. Besides, how are you using transaction? Here are general tips about transactions: http://msdn.microsoft.com/en-us/library ... %20_topic9. Please post here your code and the call stack when transaction execution hangs.