reproducable bug using postgres 9

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

reproducable bug using postgres 9

Post by albourgz » Fri 25 Aug 2017 11:56

Using unicode, no autocommit, no fetchall. c++ builder XE10, unidac 7.0.2.
I get an exception "The portail « PORTALST471817920 » does not exist" (and the same program works agains mariadb, oracle, ...).

Here is a sample to reproduce the issue:
Script to create table:

Code: Select all

rollback;
drop table t1;
create table t1(id int, primary key(id));
insert into t1(id) values (1);
insert into t1(id) values (2);
insert into t1(id) values (3);
insert into t1(id) values (4);
insert into t1(id) values (5);
insert into t1(id) values (6);
insert into t1(id) values (7);
commit;
on a form, drop a PostgresSQLUniProvider, a TUniConnection (autocommit false, UseUnicode true,and connect it), a TUniQuery q1 (Unidirectional=true, FetchRows=3, SELECT ID FROM T1 ORDER BY id), add the id field, add a TUniSQL qd1 with query "delete from t1 where id=:1".
Add a Button with this code:

Code: Select all

    UniConnection1->StartTransaction();
    q1->Open();
    qd1->Prepare();
    int i=0;


    for (; !q1->Eof; q1->Next()) {
            if (i%5==0) {
                UniConnection1->Commit();
                UniConnection1->StartTransaction();
                }

            qd1->Params->Items[0]->AsInteger=q1id->AsInteger;
            qd1->Execute();
            }
    q1->Close();
    UniConnection1->Commit();
=> Exception ---------------------------
Debugger Exception Notification
---------------------------
Project Project83.exe raised exception class EPgError with message 'le portail « PORTALST471817920 » n'existe pas'
---------------------------
Break Continue Help
---------------------------
occurs when <fetchrows> rows have been read.

This programs works fine with the same db in mariadb. Problem is caused by the transaction restart (commit in the middle). Without these lines it works perfectly.

Can you investigate and include a fix in next release?
Regards.

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

Re: reproducable bug using postgres 9

Post by azyk » Mon 28 Aug 2017 14:46

The described error is obviously not related to the functionality of PgDAC, but to PostgreSQL specificity.

In your sample, both q1 and dq1 are executed within one transaction that is started at the beginning of the sample. When executing q1->Open(); the cursor is opened on the server and the first 3 table records are fetched (q1-> FetchRows == 3). Next, the UniConnection1->Commit(); call occurs in a loop, which closes the transaction and closes the cursor related to q1.
The first three q1->Next(); calls will not cause errors, because 3 table records were fetched from the database when calling q1->Open();. During the fourth q1->Next(); call, q1 dataset will try to fetch the next 3 table records from the server, this will cause the specified error ("The portail ... does not exist"), since cursor was closed when executing UniConnection1->Commit();

To avoid this error, you can use a separate TUniConnection instance for the dataset that fetches data.

albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

Re: reproducable bug using postgres 9

Post by albourgz » Mon 28 Aug 2017 15:20

If calling commit closes cursor, please explain
* Why does it work if fetchrows is 20?
* Why does this error occur only against postgres? The same sample works fine with the same unidac against mariadb and oracle (I have lots of oracle apps that commit every 1000 rows without any cursor problem).

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: reproducable bug using postgres 9

Post by ViktorV » Tue 29 Aug 2017 12:35

1. Yes, you are right, the error will not occur if the FetchRows value is greater than the number of records in the table.
As we wrote earlier, if you set FetchRows = 3 in your sample, then the q1->Open(); call fetches the first 3 records of the table. Therefore, during the first three iterations of the loop, when calling q1->Next(); the request to the server will not occur. During the fourth q1->Next(); call there will be an attempt to get the following 3 table records from the server, this will lead to an error, because the transaction was terminated (UniConnection1->Commit ();).
When the FetchRows = 20, all records will be fetched immediately when calling the q1->Open() method; therefore when calling q1->Next(); there will be no requests to the server and the error will not occur.
2. This behavior is related to the architecture and functionality of PostgreSQL, not to the functionality of UniDAC, and we can not affect it in any way.

albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

Re: reproducable bug using postgres 9

Post by albourgz » Tue 29 Aug 2017 13:42

Even if FetchAll is true in the query, the problem occurs. Meaning: FetchRows is not ignored by a FetchAll. Is this normal?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: reproducable bug using postgres 9

Post by ViktorV » Wed 30 Aug 2017 13:16

Perhaps the cause of such behavior is the Unidirectional property. The Unidirectional and FetchAll properties are mutually exclusive: when setting Unidirectional=True, the FetchAll value will be False, and vice versa.
Please try setting Unidirectional to False and the FetchAll property to True.

albourgz
Posts: 160
Joined: Wed 06 May 2009 12:17
Location: belgium

Re: reproducable bug using postgres 9

Post by albourgz » Wed 30 Aug 2017 13:49

You have one point.
If query.UniDirectional is set to true at design-time, putting it to false in the code doesn't change anything.
If query.UniDirectional is set to false at design-time, it works fine.
If query.UniDirectional is set to false at design-time, then set to true by code at run-time, the exception occurs.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: reproducable bug using postgres 9

Post by ViktorV » Thu 31 Aug 2017 14:12

If query.UniDirectional is set to true at design-time, putting it to false in the code doesn't change anything.
When setting UniDirectional to True in designtime, FetchAll is set automatically to False. When setting UniDirectional to False, FetchAll remains set to False, so an error occurs.
If query.UniDirectional is set to false at design-time, it works fine.
In this situation when the default values are used: UniDirectional = False and FetchAll = True - an error does not occur.
If query.UniDirectional is set to false at design-time, then set to true by code at run-time, the exception occurs.
When setting UniDirectional to True, FetchAll will be automatically set to False - an error occurs.

Post Reply