** TOO MANY CLIENTS ERROR **

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for PostgreSQL in Delphi and C++Builder
Post Reply
robersonjfa
Posts: 5
Joined: Tue 10 Apr 2018 19:27

** TOO MANY CLIENTS ERROR **

Post by robersonjfa » Tue 10 Apr 2018 19:48

Hello,

I have an application developed with Delphi Seattle using dbExpress driver version 3.8.11 and connecting to a database with PostgreSQL 9.6.2.
One of my users reported that the following error was occurring in the application: "Sorry, too many clients already".
However, the number of connections required in the PostgreSQL configuration file has already been dimensioned and the error persists.
To reproduce the problem, I did the following:

1) I created a new project;
2) I have added the following components do TForm: TSQLConnection and TSQLDataset;
3) In the FormCreate event, I implemented the following code:

1. procedure TForm1.FormCreate(Sender: TObject);
2. begin
3.
4. SQLConnection1.Open;
5. SQLDataSet1.CommandText := 'update table1 set TIP = TIP ';
6. SQLDataSet1.ExecSQL;
7.
8. SQLDataSet1.Close;
9. SQLDataSet1.CommandText := 'select 1 from table2';
10. SQLDataSet1.Open;
11.
12. SQLDataSet1.Close;
13. SQLDataSet1.CommandText := 'update table3 VER = VER ';
14. SQLDataSet1.ExecSQL;
15. end;


I noticed that when line 10 of SQLDataSet1.Open is executed a new connection with new PID is created. Looking at the pg_stat_activity viewof PostgreSQL the SQL for the new connection is a ROLLBACK instruction.

As a new connection is created and the previous one maintained, I believe this is why they are breaking the connection limit of PostgreSQL.
I would like to know why this behavior of TSQLDataset.Open?

Best regards

azyk
Devart Team
Posts: 1071
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: ** TOO MANY CLIENTS ERROR **

Post by azyk » Wed 11 Apr 2018 10:52

The error message "Sorry, too many clients already" is generated not by our driver, but by PostgreSQL. To avoid it, refer to the PostgreSQL documentation.

Also we could not reproduce the creation of a new PID for connection to PostgreSQL when calling SQLDataSet1.Open. Below is the project of the console application that we used for testing. In the project code, specify the credentials that you used to reproduce the issue. Save the project, compile and run it. Please tell us about the results.

Code: Select all

program devart_dbx_too_many_clients_already;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils,
  DBXDevartPostgreSQL,
  MidasLib,
  Data.SqlExpr,
  Windows,
  DB;

procedure WritelnPID(ACon: TSQLConnection);
var DS: TDataSet;
begin
  ACon.Execute('select pg_backend_pid()', nil, DS);
  try
    Writeln('PID=' + DS.Fields[0].AsString);
  finally
    DS.Free;
  end;
end;

var
  Con: TSQLConnection;
  SQLDS: TSQLDataSet;

begin
  try
    SetConsoleOutputCP(CP_UTF8);
    Con := TSQLConnection.Create(nil);

    Con.ConnectionName := 'DevartPostgreSQL';
    Con.DriverName := 'DevartPostgreSQL';

    Con.LoginPrompt := False;
    Con.Params.Values['Hostname'] := 'set up db:port';
    Con.Params.Values['Database'] := 'set up database';
    Con.Params.Values['SchemaName'] := 'set up schema;
    Con.Params.Values['User_Name'] := 'set up user';
    Con.Params.Values['Password'] := 'set up password';

    try
      Writeln('Create test tables');
      Con.Open;
      Con.Execute('CREATE TABLE IF NOT EXISTS devart_table1(field1 varchar(20))', nil);
      Con.Execute('INSERT INTO devart_table1 VALUES(''field1'')', nil);
      Con.Execute('CREATE TABLE IF NOT EXISTS devart_table2(field2 varchar(20))', nil);
      Con.Execute('INSERT INTO devart_table2 VALUES(''field2'')', nil);
      Con.Execute('CREATE TABLE IF NOT EXISTS devart_table3(field3 varchar(20))', nil);
      Con.Execute('INSERT INTO devart_table3 VALUES(''field3'')', nil);
      Con.Close;

      SQLDS := TSQLDataSet.Create(nil);
      SQLDS.SQLConnection := Con;
      try

        Writeln('Press ENTER to start test');
        Readln;

        Con.Open;
        SQLDS.CommandText := 'update devart_table1 set field1 = ''field1'' ';
        SQLDS.ExecSQL;
        WritelnPID(Con);

        SQLDS.Close;
        SQLDS.CommandText := 'select 1 from devart_table2';
        SQLDS.Open;
        WritelnPID(Con);

        SQLDS.Close;
        SQLDS.CommandText := 'update devart_table3 set field3 = ''field3'' ';
        SQLDS.ExecSQL;
        WritelnPID(Con);

      finally
        SQLDS.Free;
      end;

      Writeln('Finish. Press ENTER to exit.');
    finally
      Con.Free;
    end;
  except
    on E: Exception do
    begin
      Writeln(E.ClassName, ': ', E.Message);
    end;
  end;
  Readln;
end.

robersonjfa
Posts: 5
Joined: Tue 10 Apr 2018 19:27

Re: ** TOO MANY CLIENTS ERROR **

Post by robersonjfa » Wed 25 Apr 2018 11:39

Hi,

Thank you for your help. I discovered that the problem was associated with FetchAll = false. Combined with FetchAll = false I used the CursorWithHold = true parameter. The error "TOO MANY CLIENTS ERROR" occurs because FetchAll=false creates extra transaction/connection.
However, after modifying the CursorWithHold parameter to true, another problem occurred. When using TStringStream to write to fields of type BLOB or TEXT, special character values ​​are written in the octal format.
To reproduce the problem:
1) Create a project and add a TSQLConnection and a TSQlDataset;
2) Create the following table in the database:
CREATE TABLE tb_teste (
texto text
);

3) In the onCreate event of the form add the following snip code:
oStringStreamXML := TStringStream.Create('TESTE ÉÍÓÇ');
oparams := TParams.Create(nil);
oparams.CreateParam(ftblob, 'blob', ptInput);
oparams.ParamByName('blob').LoadFromStream(oStringStreamXML, ftblob);
PSQLDataSet1.CommandText := 'update tb_teste set texto = :blob';
PSQLDataSet1.params := oparams;
PSQLDataSet1.ExecSQL();

Note that the string 'TESTE ÉÍÓÇ' is converted in database to 'TESTE \311\315\323\307'.

Thank you and waiting for return.

azyk
Devart Team
Posts: 1071
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: ** TOO MANY CLIENTS ERROR **

Post by azyk » Wed 25 Apr 2018 13:23

Please specify:
- the ​​ENCODING, LC_COLLATE and LC_CTYPE values for the PostgreSQL database;
- regional Windows settings on the client machine. For this, open the
'Control Panel\Clock, Language, and Region', click Region, provide the value of the Format combo box on the Formats tab. Go to the Administrative tab and specify the value used in the 'Current language for non-Unicode programs:'.

robersonjfa
Posts: 5
Joined: Tue 10 Apr 2018 19:27

Re: ** TOO MANY CLIENTS ERROR **

Post by robersonjfa » Wed 25 Apr 2018 13:43

Hi,

Database information:
ENCODING=UTF8
LC_COLLATE=Portuguese_Brazil.1252
LC_CTYPE=Portuguese_Brazil.1252

Control Panel\Clock, Language, and Region information:
Format: Português(Brasil)
Current language for non-Unicode programs: Português(Brasil)

azyk
Devart Team
Posts: 1071
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: ** TOO MANY CLIENTS ERROR **

Post by azyk » Fri 27 Apr 2018 11:27

Thank you for the information. We've reproduced the specified behavior and are investigating it now. We will let you know the results.

robersonjfa
Posts: 5
Joined: Tue 10 Apr 2018 19:27

Re: ** TOO MANY CLIENTS ERROR **

Post by robersonjfa » Wed 02 May 2018 12:38

Hi,

Any news?

Thanks.

azyk
Devart Team
Posts: 1071
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: ** TOO MANY CLIENTS ERROR **

Post by azyk » Thu 03 May 2018 12:07

Use Delphi type ftMemo instead of ftBlob to correctly store accented characters with PostgreSQL type TEXT. With this fix, the code you provided for Delphi might look as follows:

Code: Select all

  oStringStreamXML := TStringStream.Create('TESTE ÉÍÓÇ');
  oparams := TParams.Create(nil);
  oparams.CreateParam(ftMemo, 'blob', ptInput);
  oparams.ParamByName('blob').AsMemo := oStringStreamXML.DataString;
  PSQLDataSet1.CommandText := 'update tb_teste set texto = :blob';
  PSQLDataSet1.Params := oparams;
  PSQLDataSet1.ExecSQL();

robersonjfa
Posts: 5
Joined: Tue 10 Apr 2018 19:27

Re: ** TOO MANY CLIENTS ERROR **

Post by robersonjfa » Thu 03 May 2018 14:00

Hi,

OK, thank you. I had already tried switching the type to ftMemo and it worked. But, i want understand why the problem occurs just when CursorWihHold is true in the connection parameters? Is there an explanation for this behavior?

Best regards

azyk
Devart Team
Posts: 1071
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: ** TOO MANY CLIENTS ERROR **

Post by azyk » Fri 04 May 2018 08:35

PostgreSQL does not provide the ability to execute parameterized SQL queries within the DECLARE CURSOR ... WITH HOLD. Therefore, with CursorWihHold=True, our driver converts a parametrized SQL query to a plain text one before executing it on the PostgreSQL side. In the provided sample, you specify the ftBlob type for the parameter and the driver converts the parameter value to BYTEA. This behavior is correct.

You can see the mapping table of PostgreSQL types to Delphi types in the Readme.html file. It can be found in the folder where the driver was installed (e.g. C:\Program Files (x86)\Devart\dbExpress\PostgreSql )

Post Reply