Connection Pooling Question

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
mdb
Posts: 9
Joined: Thu 16 Oct 2008 13:54

Connection Pooling Question

Post by mdb » Wed 27 May 2009 15:09

Has anyone ran into any problems, or know of any bugs that cause problems when running pgpool on the database server?
We are using pgpool-II for connection pooling, and no other applications have a problem, but for some reason whenever I attempt to use dotConnect the transaction never completes. Looking on the database server, I see the connection as active, but regardless of the query it never returns.
If I connect directly to the port the database is listening on (pgpool is running on port 5432), with the exact same connection information and query, everything works great, but connecting to pgpool isn't working.
Anyone?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 28 May 2009 16:00

dotConnect for PostgreSQL has integrated connection pooling support. Why don't you want to use our implementation of this feature?

Otherwise, try turning off dotConnect for PostgreSQL connection pooling that is turned on by default (the Pooling property of the PgSqlConnection object).

mdb
Posts: 9
Joined: Thu 16 Oct 2008 13:54

Post by mdb » Thu 28 May 2009 17:00

It's not that I don't want to use the dotConnect connection pooling, it's that we have multiple applications, written in C#, Python, PHP, etc.. running on multiple platforms, so to enhance database performance across the board we run connection pooling on the server.
The first thing I tried was turning off dotConnect connection pooling but it had no effect whatsoever. Any other changes that might help?

Looking at it from the server side, the connection is made, and I can see the query running from pgadmin, but it continues running and never returns.

mdb
Posts: 9
Joined: Thu 16 Oct 2008 13:54

Post by mdb » Mon 08 Jun 2009 12:46

Any thoughts as to why this would be a problem, any other possible workarounds other than disabling dotConnect connection pooling (which has no effect), or anything else to try to help narrow down the problem?

jp.gouigoux
Posts: 20
Joined: Tue 09 Jun 2009 10:23

Post by jp.gouigoux » Thu 05 Nov 2009 14:55

Having the same pooling problem here. I tried the solution, but Devart.Data.PostgreSql.PgSqlConnection does not contain any Pooling field or property. I am using the latest version, downloaded a few weeks ago (I am at the end of my trial period, and after buying the Oracle version, we are in the process of validating before buying DotConnect for PostGreSQL).

mdb
Posts: 9
Joined: Thu 16 Oct 2008 13:54

Post by mdb » Thu 05 Nov 2009 15:07

Finally, someone else has the same problem!

Still haven't found a solution, and the only answer I've received from Devart was to use their connection pooling builtin, which isn't helpful as there are multiple apps across different programming languages and platforms that all need to access the database.

Unfortunately I've just resorted to connecting directly to the database server with dotConnect, and bypassing pgpool, which is a very fragile and annoying workaround. Everytime a new version comes out I always hope it's fixed, but no joy thus far. Every other client library I've tried works fine with pgpool, except for this one.

If you find a solution, please let me know...

Mike

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 06 Nov 2009 13:00

Jean-Philippe, you can turn off pooling by setting Pooling=false; in your connection string. Please refer to http://www.devart.com/dotconnect/postgresql/docs/ , the Index tab, the ConnectionString Property (PgSqlConnection) section.

Michael, we have checked the 4.65.52 version of dotConnect for PostgreSQL with Ubuntu 9.0, PostgreSQL Server 8.3.7, and pgpool-II 1.3.2. It works fine with the following code (5433 is a port of pgpool-II, the PostgreSQL server resides at the same host):

Code: Select all

      string connStr = "server=***;uid=***;pwd=***;port=5433;";
      PgSqlConnection pgConnection = new PgSqlConnection(connStr);
      pgConnection.Open();
      PgSqlCommand pgCommand = new PgSqlCommand();
      PgSqlTransaction myTrans;
      // Start a local transaction
      myTrans = pgConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
      // Assign transaction object for a pending local transaction
      pgCommand.Transaction = myTrans;
      pgCommand.Connection = pgConnection;
      try {
        pgCommand.CommandText = "INSERT INTO dept(DeptNo, DName) Values(111, 'DEVELOPMENT')";
        pgCommand.ExecuteNonQuery();
        myTrans.Commit();
        Console.WriteLine("Record is written to database.");
      }
      catch (Exception e) {
        myTrans.Rollback();
        Console.WriteLine(e.ToString());
        Console.WriteLine("Neither record was written to database.");
      }
      finally {
        pgConnection.Close();
      }
1. How should we modify this code to reproduce the problem?
2. Please post here your call stack.

jp.gouigoux
Posts: 20
Joined: Tue 09 Jun 2009 10:23

Post by jp.gouigoux » Fri 06 Nov 2009 14:49

I just tried the code you gave us, simply copying and pasting it in a console utility. I only changed the connection string to "User ID=\"testcomplete\";Password=\"********\";Database=postgresql_test_jpg;Server=172.16.1.112;Port=9999;" in order to match my settings, and the only other change I made was to the SQL INSERT command to put it on an existing table.

When running, I obtain the following exception on pgConnection.Open() :

Exception non gérée : Devart.Data.PostgreSql.PgSqlException: Unexpected server r
esponse.
à Devart.Data.PostgreSql.ac.ac()
à Devart.Data.PostgreSql.ac.a()
à Devart.Common.DbConnectionFactory.a(DbConnectionBase A_0)
à Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
à Devart.Common.DbConnectionBase.Open()
à Devart.Data.PostgreSql.PgSqlConnection.Open()
à ConsoleTestPgDirect.Program.TestDevart() dans E:\Projets\PostGreSQL\Console
TestPgDirect\Program.cs:ligne 117
à ConsoleTestPgDirect.Program.Main(String[] args) dans E:\Projets\PostGreSQL\
ConsoleTestPgDirect\Program.cs:ligne 31

My bench is as follows :
- PostGreSQL : EnterpriseDB Advanced Server 8.3 R2 (830 106)
- Linux : CentOS 5.4 x32
- Kernel : 2.6.18-164
- PGPool-II : 2.2.5

If I add "Pooling=false;" at the end of the connection string, no change : I get the same exception. If I change the port to 5444 (which is my database without PGPOOL), it works fine.

Please tell me if you need traces or anything else that can help...

mdb
Posts: 9
Joined: Thu 16 Oct 2008 13:54

Post by mdb » Fri 06 Nov 2009 14:50

I'll give the new beta a try and let you know!

jp.gouigoux
Posts: 20
Joined: Tue 09 Jun 2009 10:23

Post by jp.gouigoux » Tue 10 Nov 2009 11:24

I have tried the beta here, and it works better than the stable version I used previously : I do not have the exception I had previously.

Two initial remarks, though :
- I have an apparent performance problem in some cases with the pool.
- When I launch my benchmarks two times in a row, I receive a CONNECTION FAILURE exception on the second try.

In both cases, I will finish my benchmarks first, to see if it comes from the provider or the pgpool configuration, and will keep you updated in the next day or so.

Thanks for this first correction, that was extremely quick !

jp.gouigoux
Posts: 20
Joined: Tue 09 Jun 2009 10:23

Post by jp.gouigoux » Tue 10 Nov 2009 11:27

Also, I get the CONNECTION FAILURE from the first time I launch my tests after a restart of pgpool and edb when I add pooling=false to the connection string.

jp.gouigoux
Posts: 20
Joined: Tue 09 Jun 2009 10:23

Post by jp.gouigoux » Fri 13 Nov 2009 13:38

Here are the results of my benchmarks :

First, the CONNECTION FAILURE only appears from time to time. It looks like there is already an entry in the forum for this. It is dated back to 2007, but apparently not solved... I have the same behaviour, though, meaning that if I wait a few minutes, it goes back again. I am going to investigate further in PGPOOL, since it may be this component that is the cause of the trouble.

Second, and more annoying : impossible to start a transaction when PGPOOL is active. The following code works fine :

string connStr = "User ID=\"testcomplete\";Password=\"*******\";Database=postgresql_test_jpg;Server=172.16.1.112;Port=9999;";

PgSqlConnection pgConnection = new PgSqlConnection(connStr);
pgConnection.Charset = "UTF8";
pgConnection.Open();
PgSqlCommand pgCommand = null;

pgCommand = new PgSqlCommand("SELECT nomtab, nomcol FROM INCOLTAB_TEST", pgConnection);
pgCommand.UnpreparedExecute = true; // Sinon, on part en timeout quand on est en mode PGPOOL !!!
IDataReader Reader = pgCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (Reader.Read())
Console.WriteLine("Value : " + Reader.GetString(0));
Reader.Close();
Reader.Dispose();

So, reading values is not a problem. But the following program raises an exception on the line that creates the transaction :

string connStr = "User ID=\"testcomplete\";Password=\"*******\";Database=postgresql_test_jpg;Server=172.16.1.112;Port=9999;";

PgSqlConnection pgConnection = new PgSqlConnection(connStr);
pgConnection.Charset = "UTF8";
pgConnection.Open();
PgSqlCommand pgCommand = null;

PgSqlTransaction myTrans = pgConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
pgCommand = new PgSqlCommand();
pgCommand.UnpreparedExecute = true; // Sinon, on part en timeout quand on est en mode PGPOOL !!!
pgCommand.Transaction = myTrans;
pgCommand.Connection = pgConnection;
pgCommand.CommandText = "DELETE FROM INCOLTAB_TEST WHERE nomtab='truc'";
pgCommand.ExecuteNonQuery();

pgCommand = new PgSqlCommand();
pgCommand.UnpreparedExecute = true; // Sinon, on part en timeout quand on est en mode PGPOOL !!!
pgCommand.Transaction = myTrans;
pgCommand.Connection = pgConnection;
pgCommand.CommandText = "INSERT INTO INCOLTAB_TEST (nomtab, nomcol) VALUES ('truc', 'truc')";
pgCommand.ExecuteNonQuery();

myTrans.Commit();
Console.WriteLine("Record is written to database.");
}
catch (Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Neither record was written to database.");
}
finally
{
pgConnection.Close();
pgConnection.Dispose();
pgConnection = null;
}

The detailed exception is as follows :

Exception non gérée : Devart.Data.PostgreSql.PgSqlException: Impossible de lire
les données de la connexion de transport : Une tentative de connexion a échoué c
ar le parti connecté n'a pas répondu convenablement au-delà d'une certaine durée
ou une connexion établie a échoué car l'hôte de connexion n'a pas répondu. --->
System.IO.IOException: Impossible de lire les données de la connexion de transp
ort : Une tentative de connexion a échoué car le parti connecté n'a pas répondu
convenablement au-delà d'une certaine durée ou une connexion établie a échoué ca
r l'hôte de connexion n'a pas répondu. ---> System.Net.Sockets.SocketException:
Une tentative de connexion a échoué car le parti connecté n'a pas répondu conven
ablement au-delà d'une certaine durée ou une connexion établie a échoué car l'hô
te de connexion n'a pas répondu
à System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size,
SocketFlags socketFlags)
à System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 si
ze)
--- Fin de la trace de la pile d'exception interne ---
à System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 si
ze)
à Devart.Common.ag.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Common.f.c(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Common.q.e(Byte[] A_0, Int32 A_1, Int32 A_2)
--- Fin de la trace de la pile d'exception interne ---
à Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
à Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavio
r, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
à System.Data.Common.DbCommand.ExecuteReader()
à Devart.Common.DbCommandBase.ExecuteNonQuery()
à Devart.Data.PostgreSql.PgSqlTransaction..ctor(PgSqlConnection A_0, Isolatio
nLevel A_1)
à Devart.Data.PostgreSql.ae.a(IsolationLevel A_0)
à Devart.Common.DbConnectionBase.BeginDbTransaction(IsolationLevel isolationL
evel)
à System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLe
vel)
à Devart.Data.PostgreSql.PgSqlConnection.BeginTransaction(IsolationLevel il)
à ConsoleTestPgDirect.Program.TestDevart() dans E:\Projets\PostGreSQL\Console
TestPgDirect\Program.cs:ligne 272
à ConsoleTestPgDirect.Program.Main(String[] args) dans E:\Projets\PostGreSQL\
ConsoleTestPgDirect\Program.cs:ligne 31

I am using the latest beta 4.65. Even if the correction takes a lot of time, can you please provide me as sopn as you can with some visibility on the possible dates of correction ? And again, do not hesitate to ask me and test some new betas, as this provider is important for us.

Thanks in advance for your help.

Can you please provide some information

jp.gouigoux
Posts: 20
Joined: Tue 09 Jun 2009 10:23

Post by jp.gouigoux » Mon 16 Nov 2009 10:43

The CONNECTION FAILURE seems to come from the remanent connections. If I wait for a few minutes before launching the benchmark again, I do not get the problem. So, let us forget it for now.

As for the transaction problem, I have been looking for the configuration in PGPOOL to see if it could come from there, but with no success for now. Did you notice anything that could sort the problem in the traces I sent you ?

Thanks in advance

jp.gouigoux
Posts: 20
Joined: Tue 09 Jun 2009 10:23

Post by jp.gouigoux » Mon 16 Nov 2009 14:46

One last thing : trying with PGBouncer works fine, so all the problems described here happen only with the conjunction of Devart PGSQL provider and PGPool.

Using the native NPGSQL with PGPool works fine (except of course for the low stability of the provider).

Using the Devart provider with PGBouncer or without pooling system works fine.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Connection Pooling Question

Post by Shalex » Wed 07 Aug 2013 11:52

The bug with executing prepared statements via pgPool II is fixed. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

As a temporary workaround with a current build, please add the "Unprepared Execute=true;" entry to your connection string.

Post Reply