Devart Forums

Posted: **Tue 24 Jul 2018 15:46**

Hello

I'm trying to connect to a PgBouncer-guarded database but it's causing a TPgException with message '' (empty string).

I'm using the following implementation:

Code: Select all

const
  CONNECTSTR = 'Password=%s;User ID=%s;DATABASE=%s;SERVER=%s;PORT=%s;UID=%s;'+
    'PWD=%s;UseUnicode=True;CharSet=WIN1252';
...
procedure TfrmProtoPgDacConnection.Button1Click(Sender: TObject);

  function Cfg(const aConfigName: string): string;
  begin
    Result := mmoConfig.Lines.Values[aConfigName];
    if Result = '' then
      raise Exception.Create('Configuration not found: ' + aConfigName);
  end;

var
  _Conn : TPgConnection;
begin
  _Conn := TPgConnection.Create(nil);
  try
    _Conn.ConnectString := Format(CONNECTSTR,[Cfg('Password'), 
      Cfg('User_Name'), Cfg('Database'), Cfg('Server'),
      Cfg('Port'), Cfg('User_Name'), Cfg('Password')]);

    _Conn.ProtocolVersion := pv20;
    _Conn.ConnectionTimeout := 30;
    _Conn.Pooling := False;
    _Conn.Connected := True;
    ShowMessage('Connection successful');
  finally
    _Conn.Connected := False;
    _Conn.Free;
  end;
end;

PgBouncer (v 1.8.1) is configured in transaction mode, like this:

Code: Select all

auth_type = md5
...
pool_mode = transaction
default_pool_size = 15
reserve_pool_size = 10
server_reset_query = DISCARD ALL
server_reset_query_always = 1

Some considerations:
- It happens the same way in version 4.6.20 and 5.2 trial.
- It works correctly when connecting directly to the database.
- We need PgBouncer to be in transaction mode.
- pgAdmin3 can connect and work through our PgBouncer normally, using the same connection configurations.
- When I use pv30 the error message is like this: 'prepared statement "PRSTMTST733962560" does not exist'.
- Problem happens in Delphi XE, XE3 and Tokyo (all fully patched).

Thank you.

Posted: **Wed 25 Jul 2018 06:49**

Maybe the error cause is not PgDAC, but the following PgBouncer specificity : https://pgbouncer.github.io/faq.html#ho ... n-pooling

To solve the problem try the PostgreSQL protocol without using prepared queries - protocol 2.0. For this before establishing a connection set the pv20 value to the TPgConnection.ProtocolVersion property. To use the pv20 constant, add the PgClasses unit to the uses section.

Posted: **Wed 25 Jul 2018 17:47**

Hi,

See the code I sent, I am already using pv20, it's causing the empty string error on connect.

On the other hand, if I use pv30, I see in the PgDac code (I've got v4.6.20 source code license) it manages to connect, but then it tries to run some statements in prepared mode (TPgSQLConnection.Connect, routines SetClientEncoding and GetByteaOutput), thus causing the "prepared statement does not exist" error. So, is there a way to force it to not prepare the statements before executing?

Thanks.

EDIT: when using pv20, PgBouncer logs the following:

Code: Select all

2018-07-25 14:17:23.223 30307 LOG C-0x14e1580: (nodb)/(nouser)@192.168.2.210:51640 closing because: Old V2 protocol not supported (age=0)

I suppose I'm forced to stick with pv30, so I need to somehow disable statement preparing.

Posted: **Fri 27 Jul 2018 10:36**

The provided information is not enough to reproduce the issue. Please compose a small test project to reproduce the issue and send it to us. In the project, also include the scripts for creating test tables.

You can send us the project using the contact form at our site: https://www.devart.com/company/contactform.html .

Posted: **Tue 31 Jul 2018 16:03**

Hello,

I just sent you, via contact form, a simple test project where it connects and run a simple query. I found the problem is not DB specific so I didn't include any DB script, so you can try use any PostgreSQL 10 database. But I did include the whole PgBouncer configuration file.

Messing with the PgDac source code I've set some flags and could manage to force it into not preparing all statements (including connection ones). If needed I can send you my alterations. But this is not a good solution for me, I'd rather get an official fix.

Thank you.

Posted: **Wed 01 Aug 2018 10:31**

PgBouncer 1.8.1 does not support protocol pv20 and we cannot affect this behavior.

To use protocol pv30 prepared statements with PgBouncer transaction pooling you can manage transactions by yourself. For example:

Code: Select all

begin
  mmoLog.Lines.Clear;

  _Conn := TPgConnection.Create(nil);
  FQuery := TPgQuery.Create(nil);
  try
    _Conn.ConnectString := Format(CONNECTSTR,[Cfg('Password'), Cfg('User_Name'), Cfg('Database'), Cfg('Server'),
      Cfg('Port'), Cfg('User_Name'), Cfg('Password')]);
//     _Conn.ProtocolVersion := pv20;  // Empty string error
    _Conn.ProtocolVersion := pv30;
    _Conn.ConnectionTimeout := 30;
    _Conn.Pooling := False;
    _Conn.Connected := True;
    mmoLog.Lines.Add('Connection successful');

    // <--- add line to start transaction
    _Conn.StartTransaction;

    FQuery.Connection := _Conn;
    FQuery.SQL.Clear;
    FQuery.SQL.Add('select * from emp limit 10');

    mmoLog.Lines.Add('Running query: ' + FQuery.SQL.Text);
    FQuery.Open;
    FQuery.First;
    while not FQuery.Eof do
    begin
      _reg := '';
      for _row := 0 to FQuery.FieldCount - 1 do
      begin
        _reg := _reg + FQuery.Fields[_row].AsString + ';';
      end;
      mmoLog.Lines.Add(_reg);
      FQuery.Next;
    end;

    //  <--- add code block to finish transaction
    try
      if _Conn.InTransaction then
        _Conn.Commit;
    except
      _Conn.Rollback;
    end
    // <--- add code block to finish transaction

  finally
    FQuery.Free;
    _Conn.Connected := False;
    _Conn.Free;
  end;
end;

Learn more about PgDAC transactions management in our online documentation:
https://www.devart.com/pgdac/docs/devar ... tion().htm

Posted: **Wed 01 Aug 2018 18:07**

The main problem happens when I connect in pv30 mode, not when I run a query.

Code: Select all

_Conn.Connected := True;  // Here is where I get an "prepared statement does not exist" error

I can't start a transaction before connecting.

In order to successfully connect I had to edit PgDac source to force flags FForceSimpleProtocol and FUnpreparedExecute to be True.

But again, this is my last resource. Is there a more "official" way to circumvent the connection problem?

Thanks.

Posted: **Thu 02 Aug 2018 07:14**

In the provided example, the error 'prepared statement ... does not exist' occurred when FQuery.Open was called.

Please provide us with a test account for remote connection to the PostgreSQL test database. We will try to connect it from our test computer and reproduce the issue.

Devart Forums

Error '' (empty string) connecting to PgBouncer

Error '' (empty string) connecting to PgBouncer

Re: Error '' (empty string) connecting to PgBouncer

Re: Error '' (empty string) connecting to PgBouncer

Re: Error '' (empty string) connecting to PgBouncer

Re: Error '' (empty string) connecting to PgBouncer

Re: Error '' (empty string) connecting to PgBouncer

Re: Error '' (empty string) connecting to PgBouncer

Re: Error '' (empty string) connecting to PgBouncer