postgresql updates very slow
lazarus / 16-Databases

----------------------------------------------------------------------
99Percent · 2013-08-22 15:26

I have been trying for a while to make my updates and inserts to
postgresql server faster.

For some reason they are extremely slow. I have tried different
components and metthods including TSQLQuery. ExecSQL, TSQLScript,
TPQConnection.ExecuteDirect. Also very slow using Devart UniDac
components. (They were unable to solve my problem).

I know its not a connectivity issue or a workstation problem r because
if I save the SQL statements to a file and then execute them with
PGAdmin3 the execution is almost instantaneous (1000x speed atleast).

Please help on how to troubleshoot this.

----------------------------------------------------------------------
marcov · 2013-08-22 15:58

If you do bulk updates, commit the transaction every 1000 tuples.

----------------------------------------------------------------------
mirce.vladimirov · 2013-08-22 21:33

I had similar problem very recently. I was doing a massive update thru
a "while not query.EOF" loop and that was very very time consuming.
Code was like:
Code: [Select]firstquery.first;
while not firstquery.EOF do begin
        //do something
       secondquery.add('update mytable set field1=:param1 where....');
       secondquery1.parambyname('param1').value:=myvariable;
       secondquery.execSQL;
      firstquery.next;
end;
 I consider myself still a newbie to Lazarus/Delphi and also newbie to
SQL, so I went to do lots of research and lots of tests, and found a
way to do a massive updates thru SQL and i got results >1000 faster. A
Lazarus program was working almost 7 hours, the new approach using
only the SQL finished the same job within ridiculous 15-20 seconds.
Since I solved this one, I also found a way to solve another huge mega
massive time consumig issue which worked about 8 hours (maybe even 8h
and 15 minutes) and that one finishes now in exactly 35 seconds.
After I solved these two I'm proud to say that I'm not newbie to SQL
anymore, now I'm more like a SQL ninja (on my way to become a SQL
jedi, hopefully)  
So I recomend using SQL instead of the programing language whenever
it's possible.

----------------------------------------------------------------------
99Percent · 2013-08-23 01:15

I create a stringlist with many inserts separated with semicolons

Then I submit the stringlist in the objects SQL and execute. It takes
a long time.

I have tried many combinations transactions and its the same.

If I save the stringlist as a file and open it with PGAdmin3 it
executes it in seconds.

----------------------------------------------------------------------
ludob · 2013-08-23 06:07

Quote from: 99Percent on August 23, 2013, 03:15:08 amIf I save the
stringlist as a file and open it with PGAdmin3 it executes it in
seconds.
sqldb executes your big query in one transaction. PGAdmin3 will do it
in autocommit mode and commit every insert separately. Try surrounding
the query with a BEGIN; ... COMMIT; in PGAdmin3 before executing and
compare the timings. 
Inserting or updating in transactions covering large blocks of data is
normally faster than committing individual rows. Except when there is
an interference possible with other transactions that block each
other. Are all inserts in the same table (watch out for triggers)? Are
other programs writing to the same tables or are setting explicit
locks? Are you seeing deadlocks?

----------------------------------------------------------------------
Julius · 2013-08-23 06:35

Hello 99Percent,
Quote from: 99Percent on August 23, 2013, 03:15:08 amI create a
stringlist with many inserts separated with semicolons

Then I submit the stringlist in the objects SQL and execute. It takes
a long time.

I have tried many combinations transactions and its the same.

If I save the stringlist as a file and open it with PGAdmin3 it
executes it in seconds.
It's just my quess, but I presume the components have to parse every
statement first before executing. That means the types of the collumns
must be determined and that is done executing other queries, so
instead of executing 1 query many more are executed. That takes time
of course.
For what you write there may be an easy solution: instead of using
many inserts use just one:
Code: [Select]INSERT into foo(field1, field2)
VALUES ('value11', 'value12'),
  ('value21', 'value22'),
  .....
  ('valueN1, valueN2);That should increase the speed of your program a
lot.

Good luck!

----------------------------------------------------------------------
BigChimp · 2013-08-23 07:51

@Julius, yes, that, or a parametrized query like mirce.vladimirov
showed.

----------------------------------------------------------------------
ttomas · 2013-08-23 09:04

I'm not a PostgreSQL user, Firebird and MSSQL is my daily job.
With Firebird best performance code is:
Code: [Select]secondquery.add('update mytable set field1=:param1
where....');
secondquery.Prepare;
p1 := secondquery.parambyname('param1');
p2 := secondquery.parambyname('param2');
...
pN := secondquery.parambyname('paramN');

firstquery.first;
f1 := firstquery.fieldbyname('field1');
f2 := firstquery.fieldbyname('field2');
...
fN := firstquery.fieldbyname('fieldN');
while not firstquery.EOF do begin
        //do something
       p1.value := f1.value;
       pX.value := fY.value;
       secondquery.execSQL;

       committrans1000; // commit every 1000 or 500 or 100 inserts

       firstquery.next;
end;

Don't use fieldbyname or parambyname in loop

----------------------------------------------------------------------
mirce.vladimirov · 2013-08-23 13:16

Quote from: mirce.vladimirov on August 22, 2013, 11:33:15 pm A Lazarus
program was working almost 7 hours, the new approach using only the
SQL finished the same job within ridiculous 15-20 seconds.
Since I solved this one, I also found a way to solve another huge mega
massive time consumig issue which worked about 8 hours (maybe even 8h
and 15 minutes) and that one finishes now in exactly 35 seconds.

Noooo, i was wrong and exaggerated,  that are not the correct
timings,  right now I'm runnig the two programs one after another and
they complete in total of 7 minutes together (4 min.for first one, and
3 for second)   but that is still a lots of improvement compared to
total of >15 hours.

So, previos total timing for the two programs (they depend on each
other) used to be >15 hours and now is 7 minutes.
Do it thru SQL it's much faster. Create one single query which will be
called once, with one "query.execSQL" instead of repeating a loop for
1000's of times

----------------------------------------------------------------------
BigChimp · 2013-08-25 10:39

Quote from: mirce.vladimirov on August 23, 2013, 03:16:17 pmSo,
previos total timing for the two programs (they depend on each other)
used to be >15 hours and now is 7 minutes.
Do it thru SQL it's much faster. Create one single query which will be
called once, with one "query.execSQL" instead of repeating a loop for
1000's of times
Posting this without the SQL/code involved is a bit pointless
though... Are you talking about insert statements? Did you split out
the parameter assignment as ttomas indicated?

----------------------------------------------------------------------
mirce.vladimirov · 2013-08-25 15:19

Quote from: BigChimp on August 25, 2013, 12:39:18 pmPosting this
without the SQL/code involved is a bit pointless though... Are you
talking about insert statements? Did you split out the parameter
assignment as ttomas indicated?

My point is that DB I/O operations are much much faster wher performed
thru pure SQL instead doing complicated combinations between the SQL
and programing language because it's the programing language that
slows it down, specificaly using "for" and "while" loops.
 Do a test: create a SQL query and then go thru it with a
lazarus/pascal/delphi for loop in order to calculate the total of some
fields :
Code: [Select]query1.close;
query1.sql.clear;
query1.sql.text:='select id_code, salary from table1';
query1.open;

query1.first;
while not query1.EOF do begin
          the_total:=the_total +
query1.fieldbyname('salary').asinteger;
                query2.close;
                query2.sql.clear;
                query2.sql.text:='update table2 set total=:thetotal 
where field_this=that and  somtehing_else=the_other_thing  and
field_with_param=:param_number_one ';
                query2.Parambyname('thetotal').asinteger:=the_total;
               
query2.Parambyname('param_number_one').asinteger:=some_value;
                query2.execSQL;
          query1.next;
end;

Here we are calling "update" query for each record from query1, you
can't notice the speed diference if you have small number of records,
so to test the speed you need a table with 200 thousend records in
table1. That way you are actualy calling query2.execSQL 200 000 times.
Alternative to this but this time done thru one single call to pure
SQL would be :

Code: [Select]query1.close;
query1.sql.clear;
query1.sql.add('update table2, table1 set total=sum(salary) where
field_this=something and  somtehing_else=the_other_thing  and
field_with_param=:param_number_one ');
query1.Parambyname('param_number_one').asinteger:=some_value;
query1.execSQL;

I believe that this example is quite simple but good enough to see my
point.
There is a bond between the programin language and the DB, and in a
certain poin the program calls the database passing the contain of the
query. My conclusion is that using this bond slows the program, and
naturaly slowdown multiplies with repeating this operation.
This is not related only to Postgress but whith calling SQL in
generaly.

----------------------------------------------------------------------
BigChimp · 2013-08-25 16:56

@mirce: yes, obviously running set-based SQL statements is gernally
much faster than loops.

Unfortunately, sometimes you cannot avoid running loops in code.

----------------------------------------------------------------------
99Percent · 2013-08-27 01:08

Quote from: ludob on August 23, 2013, 08:07:36 amQuote from: 99Percent
on August 23, 2013, 03:15:08 amIf I save the stringlist as a file and
open it with PGAdmin3 it executes it in seconds.
sqldb executes your big query in one transaction. PGAdmin3 will do it
in autocommit mode and commit every insert separately. Try surrounding
the query with a BEGIN; ... COMMIT; in PGAdmin3 before executing and
compare the timings. 
Inserting or updating in transactions covering large blocks of data is
normally faster than committing individual rows. Except when there is
an interference possible with other transactions that block each
other. Are all inserts in the same table (watch out for triggers)? Are
other programs writing to the same tables or are setting explicit
locks? Are you seeing deadlocks?

if in PGAdmin3 I enclose my many inserts query with begin; and commit;
the query is executed instantly, even faster I think. In lazarus using
sqldb it takes several minutes.

I am thinking it could be a libpq problem. I am using linux mint Nadia
x64 and the libpq version is 9.1.9-0ubuntu12.10 Ubuntu:12.10

Does anyone know if PGAdmin3 uses libpq or does it have its own
internal code to interface to postgres servers?

----------------------------------------------------------------------
Julius · 2013-08-27 06:29

Quote from: 99Percent on August 27, 2013, 03:08:11 amDoes anyone know
if PGAdmin3 uses libpq or does it have its own internal code to
interface to postgres servers?

pgAdmin uses libpq.
http://www.pgadmin.org/docs/1.8/index.html
"Connection to PostgreSQL is made using the native libpq library."

----------------------------------------------------------------------
exdatis · 2013-08-27 06:40

Try: http://sourceforge.net/projects/epgsql/
I used SQLdb, so you can see the code and maybe get some ideas...
Regards

----------------------------------------------------------------------
BigChimp · 2013-08-27 07:47

Quote from: 99Percent on August 27, 2013, 03:08:11 amQuote from: ludob
on August 23, 2013, 08:07:36 amsqldb executes your big query in one
transaction. PGAdmin3 will do it in autocommit mode and commit every
insert separately. Try surrounding the query with a BEGIN; ... COMMIT;
in PGAdmin3 before executing and compare the timings. 
Inserting or updating in transactions covering large blocks of data is
normally faster than committing individual rows. Except when there is
an interference possible with other transactions that block each
other. Are all inserts in the same table (watch out for triggers)? Are
other programs writing to the same tables or are setting explicit
locks? Are you seeing deadlocks?

if in PGAdmin3 I enclose my many inserts query with begin; and commit;
the query is executed instantly, even faster I think. In lazarus using
sqldb it takes several minutes.
Have you looked at ludob's questions/remarks? It's a bit hard to
discuss performance problems without knowing the details.

----------------------------------------------------------------------
99Percent · 2013-08-27 14:53

Quote from: BigChimp on August 27, 2013, 09:47:34 amQuote from:
99Percent on August 27, 2013, 03:08:11 amQuote from: ludob on August
23, 2013, 08:07:36 amsqldb executes your big query in one transaction.
PGAdmin3 will do it in autocommit mode and commit every insert
separately. Try surrounding the query with a BEGIN; ... COMMIT; in
PGAdmin3 before executing and compare the timings. 
Inserting or updating in transactions covering large blocks of data is
normally faster than committing individual rows. Except when there is
an interference possible with other transactions that block each
other. Are all inserts in the same table (watch out for triggers)? Are
other programs writing to the same tables or are setting explicit
locks? Are you seeing deadlocks?

if in PGAdmin3 I enclose my many inserts query with begin; and commit;
the query is executed instantly, even faster I think. In lazarus using
sqldb it takes several minutes.
Have you looked at ludob's questions/remarks? It's a bit hard to
discuss performance problems without knowing the details.

I did respond. Its really quite simple. I have a large stringlist
(~1000 lines) of non parameterized insert statements like this:
Code: [Select]INSERT INTO pedidos
(tiendaid,tipo,fecha,usuario,pendiente) SELECT id,'S', now(),'Juanb',
't' FROM tiendas WHERE lugarid=5;
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D13010486',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D12100033',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D05080185',3,3);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D10080199',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'BDK10TP',3,3);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D12060242',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D11070245',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D07110226',5,5);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D10110225',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D12010305',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D04073278',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D07110056',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D04062322',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'CANDHAUT',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D11030066',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D11100056',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D11071144',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D12021318',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D07023859',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D06063489',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D07050049',1,1);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D10050032',2,2);
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES ((SELECT
last_value FROM "tblPedidos_id_seq"),'D12010091',1,1);
....
If I use SQLDB either by TSQLQuery.ExecSQL, TSQLScript..Execute or
TPQConnection.ExecuteDirect regardless of how I set the transaction
blocks, it updates very slowly.

If I copy and paste the statements in a PGAdmin3 query window and hit
F5 the statemens are executed almost instantly.

I have Linux Mint Nadia x64 Lazarus 1.0.8

----------------------------------------------------------------------
mirce.vladimirov · 2013-08-27 15:13

First, I see you have "SELECT" statement in every line, if the
last_value from tblPedidos is not changed then you should consider of
selecting it once and using it in your insert statement.
Second, instead of 1000 "INSERT" try this way :
Code: [Select]INSERT INTO pedidosdt (hdid,claveart,cant,cantorig)
VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2),
.....
;
or as others have said :
Code: [Select]BEGIN ;
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2),
.....
;
COMMIT ;

and see if that changes something

----------------------------------------------------------------------
BigChimp · 2013-08-27 15:20

Just guessing then:
- there are no other processes that touch the tables involved
- there are no differences in triggers between the 2 methods

If you're indeed using non-parametrized statements inside sqldb that's
probably the bottleneck: these statements have to be prepared each
time.

How pgadmin manages to be quicker: no idea. Perhaps it parses the
entire statement and parametrises/prepares the queries behind the
scenes?

----------------------------------------------------------------------
goodname · 2013-08-27 15:55

Instead of using SELECT you can try the sequence functions.
Code: [Select]INSERT INTO pedidosdt (hdid,claveart,cant,cantorig)
VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
...

INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES
(lastval('"tblPedidos_id_seq"'),'ALMWC001',2,2),
(lastval('"tblPedidos_id_seq"'),'D13010486',2,2),
...
http://www.postgresql.org/docs/8.4/static/functions-sequence.html

A comma separated list of values as mirce.vladimirov suggests should
make the query run in a single commit and should not have to be
explicitly stated.

As for PGAdminIII doing it faster that does not surprise me. The
PGAdminIII authors have a very good knowledge of PostgreSQL and know
all of the best tricks. If anyone can figure out the trick they are
using that would be great.

----------------------------------------------------------------------
99Percent · 2013-08-27 16:02

Quote from: mirce.vladimirov on August 27, 2013, 05:13:36 pmFirst, I
see you have "SELECT" statement in every line, if the last_value from
tblPedidos is not changed then you should consider of selecting it
once and using it in your insert statement.
Second, instead of 1000 "INSERT" try this way :
Code: [Select]INSERT INTO pedidosdt (hdid,claveart,cant,cantorig)
VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2),
.....
;
or as others have said :
Code: [Select]
BEGIN ;
INSERT INTO pedidosdt (hdid,claveart,cant,cantorig) VALUES
((SELECT last_value FROM "tblPedidos_id_seq"),'ALMWC001',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'D13010486',2,2),
((SELECT last_value FROM "tblPedidos_id_seq"),'TAUUNTP',2,2),
.....
;
COMMIT ;

and see if that changes something

That indeed made a difference. Apparently having many insert
statements slows SQLDB down considerably. What could it be? I am using
PQConnection.ExecuteDirect

----------------------------------------------------------------------
99Percent · 2013-08-27 16:04

Quote from: BigChimp on August 27, 2013, 05:20:03 pmJust guessing
then:
- there are no other processes that touch the tables involved
- there are no differences in triggers between the 2 methods

If you're indeed using non-parametrized statements inside sqldb that's
probably the bottleneck: these statements have to be prepared each
time.

How pgadmin manages to be quicker: no idea. Perhaps it parses the
entire statement and parametrises/prepares the queries behind the
scenes?
Is there a way to disable the preparation of statements? I really just
want to deal with the postgresql directly with no overhead involved.

----------------------------------------------------------------------
BigChimp · 2013-08-27 18:50

Quote from: 99Percent on August 27, 2013, 06:04:14 pmIs there a way to
disable the preparation of statements? I really just want to deal with
the postgresql directly with no overhead involved.
I suspect no.
There is also the low level postgresql library if you want to deal
with things at that level:
http://wiki.lazarus.freepascal.org/postgresql#PostgreSQL_package:_the_
low_level_units

----------------------------------------------------------------------
goodname · 2013-08-27 18:51

It may be possible to figure out what is going on using the EXPLAIN
command. Not sure that explain will work with FPC 2.6.2 but it should
work with FPC 2.7.

http://wiki.postgresql.org/wiki/Using_EXPLAIN

----------------------------------------------------------------------
99Percent · 2013-08-28 00:33

I think I am going to use the low level posgres unit for all inserts
and updates. Doesnt seem to be complicated at all.

----------------------------------------------------------------------
99Percent · 2013-09-06 20:19

I was able to quickly hack up a unit that does updates to my server
very fast. Here is the code

Code: [Select]unit pqdirect;

//super simple postgresql direct execution of update and insert querys
because de SQLDB implementation is unfortunate very slow
//TODO: add ssl

{$mode objfpc}{$H+}

interface

uses
  postgres3,
  pqconnection, db; // needed only for the EPQDatabaseError class

type
  EPQDatabaseError = class(EDatabaseError)
    public
        SEVERITY:string;
        SQLSTATE: string;
        MESSAGE_PRIMARY:string;
        MESSAGE_DETAIL:string;
        MESSAGE_HINT:string;
        STATEMENT_POSITION:string;
    end;

function ExecPQ(Query : String) : Boolean;

implementation

var
  ASQLDatabaseHandle    : PPGConn;
  res                   : PPGresult;
  msg                   : String;

function GetPQDatabaseError(res: PPGresult): EPQDatabaseError;
var
  serr:string;
  E: EPQDatabaseError;
  SEVERITY:string;
  SQLSTATE: string;
  MESSAGE_PRIMARY:string;
  MESSAGE_DETAIL:string;
  MESSAGE_HINT:string;
  STATEMENT_POSITION:string;
begin
  SEVERITY:=PQresultErrorField(res,ord('S'));
  SQLSTATE:=PQresultErrorField(res,ord('C'));
  MESSAGE_PRIMARY:=PQresultErrorField(res,ord('M'));
  MESSAGE_DETAIL:=PQresultErrorField(res,ord('D'));
  MESSAGE_HINT:=PQresultErrorField(res,ord('H'));
  STATEMENT_POSITION:=PQresultErrorField(res,ord('P'));
  serr:=PQresultErrorMessage(res)+LineEnding+
    'Severity: '+ SEVERITY +LineEnding+
    'SQL State: '+ SQLSTATE +LineEnding+
    'Primary Error: '+ MESSAGE_PRIMARY +LineEnding+
    'Error Detail: '+ MESSAGE_DETAIL +LineEnding+
    'Hint: '+ MESSAGE_HINT +LineEnding+
    'Character: '+ STATEMENT_POSITION +LineEnding;
  E:=EPQDatabaseError.CreateFmt('ExecPQDirect: %s)', [serr]);
  E.SEVERITY:=SEVERITY;
  E.SQLSTATE:=SQLSTATE;
  E.MESSAGE_PRIMARY:=MESSAGE_PRIMARY;
  E.MESSAGE_DETAIL:=MESSAGE_DETAIL;
  E.MESSAGE_HINT:=MESSAGE_HINT;
  E.STATEMENT_POSITION:=STATEMENT_POSITION;
  result:=E;
end;

procedure DisconnectPQ;
begin
  if Assigned(ASQLDatabaseHandle) then PQFinish(ASQLDatabaseHandle);
  ASQLDatabaseHandle:=Nil;
end;

function ConnectPQ : Boolean;
var
  FConnectString : String;
begin
  Result:=False;
//enter your connection parameters here
  FConnectString := 'user=[user] password=[password] host=[host]
dbname=[database]';
  ASQLDatabaseHandle := PQconnectdb(pchar(FConnectString));
  if (PQstatus(ASQLDatabaseHandle) = CONNECTION_BAD) then begin
    msg := PQerrorMessage(ASQLDatabaseHandle);
    DisconnectPQ;
  end
  else Result:=True;
end;

function ExecPQ(Query : String) : Boolean;
var
  E : EPQDatabaseError;
begin
  Result:=False;
  if not assigned(ASQLDatabaseHandle) then if not ConnectPQ then exit;
  res := PQexec(ASQLDatabaseHandle,pchar(query));
  try
    if (PQresultStatus(res) <> PGRES_COMMAND_OK) then begin
      E:=GetPQDatabaseError(res);
      DisconnectPQ;
      raise E;
     end else Result:=True;
  finally
    PQclear(res);
  end;
end;

end.

Why cant the ExecuteDirect of the PQConnection Class be like this?

----------------------------------------------------------------------
snorkel · 2013-09-10 19:22

Postgresql will autocommit each time you do a insert in a loop, like
was said earlier you can simply add a counter and then use the MOD
operator on the count, something like if count mod 1000 = 0 then
commit.

So instead of doing a commit per insert you do 1000 inserts at a time,
this will give a huge performance boost.

The downside is if you need to rollback it will rollback 1000 rows
instead of 1.