PQConnection and SQLDB
lazarus / 16-Databases

----------------------------------------------------------------------
kolevs · 2024-03-20 13:58

Hi,

I am trying to use Postgressql with PQConnection and SQLDB.
Lazarus is 3.3, FPC is 3.2.3

Running this code for test:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  conn:
TPQConnection;  tr: TSQLTransaction;  rs, rspos: TSqlQuery;  ds:
TDataSource;  ...   conn := TPQConnection.Create(self);  tr :=
TSQLTransaction.Create(self);  tr.Options := [stoUseImplicit]; 
conn.Transaction := tr;   conn.HostName := 'xxx';  conn.DatabaseName
:= 'x';  conn.UserName := 'xxx';  conn.Password := 'xxx'; 
conn.Connected := true;   ds := TDataSource.Create(self);  rs :=
 TSqlQuery.Create(self);  rspos :=  TSqlQuery.Create(self); 
ds.DataSet := rs;   rs.DataBase := conn;  rspos.DataBase := conn;  
rs.Sql.Text := 'Select
doc_id,doc_status,doc_type_id,curr_id,doc_no,doc_date From
doc__work';  rspos.Sql.Text := 'Select doc_id,pos,pos_status,parent_do
c_id,parent_pos,prod_id,prod_key,supp_prod_id,prod_name From
doc__work_pos Where doc_id = :doc_id';  rspos.DataSource := ds;  
rs.Open;  rspos.Open;   rs.Next;  rs.Next; 
Log from Postgessql is:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  LOG:
 statement: BEGINLOG:  statement: prepare prepst0  as Select
doc_id,doc_status,doc_type_id,curr_id,doc_no,doc_date From
doc__workLOG:  statement: prepare prepst1  as select ic.relname as
indexname,  tc.relname as tablename, ia.attname, i.indisprimary,
i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc,
pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid =
tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid)
and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname =
lower('doc__work')) order by ic.relname;LOG:  execute prepst1: prepare
prepst1  as select ic.relname as indexname,  tc.relname as tablename,
ia.attname, i.indisprimary, i.indisunique from pg_attribute ta,
pg_attribute ia, pg_class tc, pg_class ic, pg_index i where
(i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid =
i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum =
i.indkey[ia.attnum-1]) and (tc.relname = lower('doc__work')) order by
ic.relname;LOG:  statement: deallocate prepst1LOG:  execute prepst0:
prepare prepst0  as Select
doc_id,doc_status,doc_type_id,curr_id,doc_no,doc_date From
doc__work LOG:  statement: prepare prepst2 ( unknown ) as Select doc_i
d,pos,pos_status,parent_doc_id,parent_pos,prod_id,prod_key,supp_prod_i
d,prod_name From doc__work_pos Where doc_id = $1LOG:  statement:
prepare prepst3  as select ic.relname as indexname,  tc.relname as
tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute
ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where
(i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid =
i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum =
i.indkey[ia.attnum-1]) and (tc.relname = lower('doc__work_pos')) order
by ic.relname;LOG:  execute prepst3: prepare prepst3  as select
ic.relname as indexname,  tc.relname as tablename, ia.attname,
i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia,
pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and
(ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid =
i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname
= lower('doc__work_pos')) order by ic.relname;LOG:  statement:
deallocate prepst3LOG:  execute prepst2: prepare prepst2 ( unknown )
as Select doc_id,pos,pos_status,parent_doc_id,parent_pos,prod_id,prod_
key,supp_prod_id,prod_name From doc__work_pos Where doc_id = $1DETAIL:
 parameters: $1 = '-3497133' LOG:  statement: deallocate prepst2LOG:
 statement: prepare prepst4 (bigint) as Select doc_id,pos,pos_status,p
arent_doc_id,parent_pos,prod_id,prod_key,supp_prod_id,prod_name From
doc__work_pos Where doc_id = $1LOG:  statement: prepare prepst5  as
select ic.relname as indexname,  tc.relname as tablename, ia.attname,
i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia,
pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and
(ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid =
i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname
= lower('doc__work_pos')) order by ic.relname;LOG:  execute prepst5:
prepare prepst5  as select ic.relname as indexname,  tc.relname as
tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute
ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where
(i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid =
i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum =
i.indkey[ia.attnum-1]) and (tc.relname = lower('doc__work_pos')) order
by ic.relname;LOG:  statement: deallocate prepst5LOG:  execute
prepst4: prepare prepst4 (bigint) as Select doc_id,pos,pos_status,pare
nt_doc_id,parent_pos,prod_id,prod_key,supp_prod_id,prod_name From
doc__work_pos Where doc_id = $1DETAIL:  parameters: $1 =
'-3497133' LOG:  statement: deallocate prepst4LOG:  statement: prepare
prepst6 (bigint) as Select doc_id,pos,pos_status,parent_doc_id,parent_
pos,prod_id,prod_key,supp_prod_id,prod_name From doc__work_pos Where
doc_id = $1LOG:  statement: prepare prepst7  as select ic.relname as
indexname,  tc.relname as tablename, ia.attname, i.indisprimary,
i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc,
pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid =
tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid)
and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname =
lower('doc__work_pos')) order by ic.relname;LOG:  execute prepst7:
prepare prepst7  as select ic.relname as indexname,  tc.relname as
tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute
ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where
(i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid =
i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum =
i.indkey[ia.attnum-1]) and (tc.relname = lower('doc__work_pos')) order
by ic.relname;LOG:  statement: deallocate prepst7LOG:  execute
prepst6: prepare prepst6 (bigint) as Select doc_id,pos,pos_status,pare
nt_doc_id,parent_pos,prod_id,prod_key,supp_prod_id,prod_name From
doc__work_pos Where doc_id = $1DETAIL:  parameters: $1 = '-3497131'
So, after every move of master dataset detail dataset is prepared
again and indexes are reading again.
Is this normal and is there any benefit from Prepared statements?
Is there a way to turn it off?

Thank you.

----------------------------------------------------------------------
rvk · 2024-03-20 14:03

Quote from: kolevs on March 20, 2024, 02:58:06 pmCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
rs.Sql.Text := 'Select
doc_id,doc_status,doc_type_id,curr_id,doc_no,doc_date From
doc__work';  rspos.Sql.Text := 'Select doc_id,pos,pos_status,parent_do
c_id,parent_pos,prod_id,prod_key,supp_prod_id,prod_name From
doc__work_pos Where doc_id = :doc_id';  rspos.DataSource := ds;  
rs.Open;  rspos.Open;   rs.Next;  rs.Next;  That's not your original
code because you don't have the set param in there.
So... what is the original code?
Are your sure you're not doing anything else?

Make a small testproject to see if this still happens.
It would be strange that for every rs.Next there would be a prepare,
because then there would also be a separate open, which isn't
happening.
(prepare happens before opening... not after)

O, sorry, I missed the  rspos.DataSource := ds;

Yes, in that case, it's normal.

There is no parambyname set so it takes the param from another
dataset.

----------------------------------------------------------------------
rvk · 2024-03-20 14:10

Can you try a manual Prepare of rspos before opening?

----------------------------------------------------------------------
kolevs · 2024-03-20 14:29

Quote from: rvk on March 20, 2024, 03:10:24 pmCan you try a manual
Prepare of rspos before opening?

Hi rvk,

I think that detail is getting values for params from master. Look at
DETAIL:  parameters: $1 = '-3497133'.
This is 1st master ID, than we have DETAIL:  parameters: $1 =
'-3497131' this is second master ID.
Detail query is executed correctly. Problem is that detail statement
is Deallocated and Prepared on every move from Master.

When I use rspos.Prepare; before Open, Select statement is looking
good, but indexes are rebuild on every move:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i LOG:
 execute <unnamed>: select ic.relname as indexname,  tc.relname as
tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute
ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where
(i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid =
i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum =
i.indkey[ia.attnum-1]) and (tc.relname = lower('doc__work_pos')) order
by ic.relname;LOG:  execute prepst0: prepare prepst0 ( unknown ) as
Select doc_id,pos,pos_status,parent_doc_id,parent_pos,prod_id,prod_key
,supp_prod_id,prod_name From doc__work_pos Where doc_id = $1DETAIL:
 parameters: $1 = '-3497131' LOG:  execute <unnamed>: select
ic.relname as indexname,  tc.relname as tablename, ia.attname,
i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia,
pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and
(ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid =
i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname
= lower('doc__work_pos')) order by ic.relname;LOG:  execute prepst0:
prepare prepst0 ( unknown ) as Select doc_id,pos,pos_status,parent_doc
_id,parent_pos,prod_id,prod_key,supp_prod_id,prod_name From
doc__work_pos Where doc_id = $1DETAIL:  parameters: $1 =
'-3497128' LOG:  execute <unnamed>: select ic.relname as indexname,
 tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique
from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic,
pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and
(ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and
(ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname =
lower('doc__work_pos')) order by ic.relname;LOG:  execute prepst0:
prepare prepst0 ( unknown ) as Select doc_id,pos,pos_status,parent_doc
_id,parent_pos,prod_id,prod_key,supp_prod_id,prod_name From
doc__work_pos Where doc_id = $1DETAIL:  parameters: $1 = '-3497127'

----------------------------------------------------------------------
rvk · 2024-03-20 15:30

Quote from: kolevs on March 20, 2024, 03:29:20 pmI think that detail
is getting values for params from master. Look at DETAIL:  parameters:
$1 = '-3497133'.
This is 1st master ID, than we have DETAIL:  parameters: $1 =
'-3497131' this is second master ID.
Detail query is executed correctly. Problem is that detail statement
is Deallocated and Prepared on every move from Master.
I still see a prepare of rspos there each time:
The "execute prepst0: prepare prepst0" lines.

The problem is (I think) that on rs.Next, because rspos has rs as
Master, the rspos table is closed and re-opened each time. And the
(internal)close does a UnPrepare, so it must do a Prepare again before
opening. It could potentially just keep the prepare and just set the
params for rspos (from the id-fields from rs). But it doesn't seem to
do that.

The select ic.relname comes from the TPQConnection.UpdateIndexDefs
(which is also called on Open).

For master/client datasets this could be somewhat optimized. But I'm
not sure how much speed you will gain get from it.

Apparently the prepare isn't reused when using open/close for
master/client, only for execute commands.

----------------------------------------------------------------------
kolevs · 2024-03-21 09:12

QuoteI still see a prepare of rspos there each time:
The "execute prepst0: prepare prepst0" lines.

Log from Postgres is a bit confusing. When is "execute prepst0:" it's
means Execute.
When is "statement: prepare prepst0" means Prepare.
So when I use Prepare, before Open, things are a little better except
for UpdateIndexDefs called redundantly on every move.

QuoteThe problem is (I think) that on rs.Next, because rspos has rs as
Master, the rspos table is closed and re-opened each time. And the
(internal)close does a UnPrepare, so it must do a Prepare again before
opening. It could potentially just keep the prepare and just set the
params for rspos (from the id-fields from rs). But it doesn't seem to
do that.
Exactly. Problem is may be, with logic in InternalOpen. May be it need
some check for existing of IndexDef, FieldDefs and Fields.

QuoteBut I'm not sure how much speed you will gain get from it.
Yes, for one statement it's not problem. But you have +3 round-trips
to server for every single Select.
If you have hundred simultaneous users and a lot more queries it is
going to be a problem.
Also, don't understand why every single statement needs to be prepared
and unprepared.
Isn't it better to have property (UsePrepared for example), to manage
things.

Soon someone wonders why people still use ZeosLib. May be this is one
of answers.
The current state of SQLDB is not usable for me.
Now I'm consider whether to write my own successor of TDataset or to
use ZeosLib.

----------------------------------------------------------------------
rvk · 2024-03-21 13:25

Quote from: kolevs on March 21, 2024, 10:12:43 amQuoteI still see a
prepare of rspos there each time:
The "execute prepst0: prepare prepst0" lines.

Log from Postgres is a bit confusing. When is "execute prepst0:" it's
means Execute.
When is "statement: prepare prepst0" means Prepare.
So when I use Prepare, before Open, things are a little better except
for UpdateIndexDefs called redundantly on every move.Yes, but I still
see a prepare on open. "execute prepst0: prepare prepst0"
When using master/client with datasource that prepare also shouldn't
be needed (after the 1st time) because the sql is already prepared. So
both prepare and get-index are not needed. But that's how sqldb does
it.

Quote from: kolevs on March 21, 2024, 10:12:43 amSoon someone wonders
why people still use ZeosLib. May be this is one of answers.
The current state of SQLDB is not usable for me.
Now I'm consider whether to write my own successor of TDataset or to
use ZeosLib.
I never use ZeosLib. It had too little control over the transactions
(at least in the past). You would need to open another db connection
just to do a different transaction. I use IBX but that's Firebird
only.
QuoteZeos supports only one transaction per connection. If one needs
multiple transactions, he / she has to use multiple connections to the
database.

----------------------------------------------------------------------
kolevs · 2024-03-21 14:36

QuoteZeos supports only one transaction per connection. If one needs
multiple transactions, he / she has to use multiple connections to the
database.

This is ok for me. From client I always use single row update/insert
statements.
When more complicated task are needed, they are Functions/Procedures
on server.
Client side just call them and all transactions are managed on server.