[Error] PGBouncer and dotConnect

[RaphaelK]

Hi,

we try to use dotConnect for PostgreSQL in combination with PGBouncer.
The connection between the driver and the database is established and the selects/inserts and so on are working.
But if we change the PGBouncer option "pool_mode = session" to "pool_mode = transaction" we get the following error:
"vorbereitete Anweisung >>PRSTMT125624200013156952<< existiert nicht"

Here is our PGBouncer Configuration:

Code: Select all

[databases]
postgres = host=127.0.0.1 port=5432 dbname=testdb1

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

listen_addr = *
listen_port = 6543

unix_socket_dir = /var/run/postgresql

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt ;; "user" "password"

admin_users = user
stats_users = user,stats

pool_mode = session

server_reset_query = DISCARD ALL

ignore_startup_parameters = extra_float_digits, lc_monetary

max_client_conn = 10
default_pool_size = 20

server_idle_timeout = 5

And here is the test script to connect to PGBouncer:

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PostgreSQL_PgBouncer_Connection
{
    class Program
    {
        static void Main(string[] args)
        {
			string myConnString1 = 	"host=127.0.0.1;" + 
									"port=6543;" +
									"database=testdb;" +
									"user id=user;" +
									"password=password;";

			gSqlConnection conn = new PgSqlConnection(myConnString1);
			conn.Open();

            PgSqlCommand cmd = new PgSqlCommand();
            PgSqlTransaction trans;
            trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.Connection = conn;
            
            cmd.CommandText = "INSERT INTO testtable1 values (9);";
                    
            try
            {
                int aff = cmd.ExecuteNonQuery();
                Console.WriteLine(aff + " rows were affected.");
                trans.Commit();
            }
            catch
            {
                Console.WriteLine("Error encountered during INSERT operation.");
            }
            finally
            {
                conn.Close();
            }
		}
	}
}

The error occur in "trans = conn.BeginTransaction();"

Can someone/anybody help us?
Thanks a lot!

Raphael

[Pinturiccio]

The conn.BeginTransaction() call just executes the BEGIN command. Probably this command cannot be executed because of incorrect application interaction with the provider or PGBouncer settings.

Also please try using the following connection string parameters with dotConnect for PostgreSQL:
1) "Protocol=2;" if your PostgreSQL server versions 7.4 or higher. The default value is 3. Protocol 2 is text, protocol 3 - binary;
2) "Pooling=false;". The default value is true;
3) "UnpreparedExecute=true;". The default value is false.
For more information, please refer to http://www.devart.com/dotconnect/postgr ... tring.html.