FireDAC getting tripped up with PostgreSQL transactions

[FLDelphi 0]

Delphi 10.4 - PostgreSQL 16

 

We have a single TFDConnection object running the whole application. 

 

Certain areas of the program need a transaction so that all of the table updates happen or none of them do. Typically we handle it like this:

 

  DevdatalargeConnection.StartTransaction;

  QryFDTemp.Close;
  QryFDTemp.SQL.Clear;
  QryFDTemp.SQL.Add('INSERT INTO J (JournalPkey, type, venddocno) ' +
                    'SELECT :NewJournalPkey, type, :VendDocNo '+
                    'FROM J WHERE JournalPkey=:OldJournalPkey ');
  QryFDTemp.ParamByName('NewJournalPkey').AsString := lNewJournalPkey;
  QryFDTemp.ParamByName('OldJournalPkey').AsString := '207AF8c_2036q\f6';
  QryFDTemp.ParamByName('VendDocNo').AsString := 'ABC123';
  try
    QryFDTemp.ExecSQL;
  except
    on E: Exception do
    begin
      DevdatalargeConnection.Rollback;
      MessageDlg('Error inserting Journal: ' + E.Message, mtError, [mbOK], 0);
      Exit;
    end;
  end;

  QryFDTemp.Close;
  QryFDTemp.SQL.Clear;
  QryFDTemp.SQL.Add('INSERT INTO JD (journaldetailpkey, type, JournalPkey) '+
                 'SELECT :NewJournalDetailPkey AS JournalDetailPkey, type, :JournalPkey ' +
                 'FROM JD CAUSESYNTAXERROR JournalDetailPkey=:JournalDetailPkey ');
  QryFDTemp.ParamByName('JournalPkey').AsString := lNewJournalPkey;
  QryFDTemp.ParamByName('NewJournalDetailPkey').AsString := '1233456789';
  QryFDTemp.ParamByName('JournalDetailPkey').AsString := '13242314234';
  try
    QryFDTemp.ExecSQL;
  except
    on E: Exception do
    begin
      DevdatalargeConnection.Rollback;
      MessageDlg('Error inserting detail: ' + E.Message, mtError, [mbOK], 0);
      Exit;
    end;
  end;

 

Notice how we have a SQL syntax error on that second ExecSQL? That's on purpose to test the transaction. When that error fires and it excepts out and does the .RollBack, we get error "current transaction is aborted, commands ignored until end of transaction block'."

 

Monitoring active transactions with pg_stat_activity, we can see that FD has somehow closed the transaction on QryFDTemp.ExecSQL - It does not matter what we set TFDConnections.TxOptions.AutoCommit or AutoStop to. 

 

This isn't the end of the world though, if FD wants to auto roll back our transaction. The problem is that it bricks every single FDQuery or FDTable in the rest of the application. Anytime we try to refresh/reopen them we get the "current transaction is aborted" error. 

 

We've tried issuing our own transactions with QryFDTemp.ExecSQL('BEGIN'), etc. No luck with that. Like I mentioned, toggling the various TxOptions don't seem to matter. We've tried the FD help method of:

 

FDConnection1.StartTransaction;
try
  FDQuery1.ExecSQL;
  ....
  FDQuery1.ExecSQL;
  FDConnection1.Commit;
except
  FDConnection1.Rollback;
  raise;
end;

But it's the same issue.

 

BTW - Make sure you open other (unrelated queries/tables) before you attempt the code(s) above. This only happens if something else is open first. 

Edited 22 hours ago by FLDelphi

[Brian Evans 126]

Start with gathering or showing more detailed database exception information. See: https://docwiki.embarcadero.com/RADStudio/Sydney/en/Handling_Errors_(FireDAC)

A TFDGUIxErrorDialog that is enabled during debugging can make things a lot easier.

 

Nothing stands out  - often if an code block looks clean the error is elsewhere or some assumption being made doesn't hold true. 

[Pat Heuvel 1]

Why don't you look into using postgresql's own begin/commit/rollback with an explicit execsql, rather than relying on FireDac's limited transaction implementation? 

[weirdo12 24]

Edit: I'm going check this again tomorrow but I am pretty sure I am actually running code like below in a TFDQuery 😉 

 

You could use TFDScript. For example, you could put these commands in the script:

 

BEGIN;

INSERT INTO t1(id) VALUES ('1');

INSERT INTO t1(id) VALUES ('2');

-- to test it cause an error

INSERT INTO t1(id) VALUES ('1');

END;

 

And the in the exception handler call ExecSQL("ROLLBACK');

 

 

Edited 2 hours ago by weirdo12

[FLDelphi 0]

I mentioned this offhandedly but, yes we've tried doing something like this (sticking with the same overall pattern):

 

var
   lNewJournalPkey : String;
begin
  lNewJournalPkey := 'test123456778';

  QryFDTemp.ExecSQL('BEGIN; ');
  try
    QryFDTemp.Close;
    QryFDTemp.SQL.Clear;
    QryFDTemp.SQL.Add('INSERT INTO Journal (JournalPkey) ' +
                      'SELECT :NewJournalPkey '+
                      'FROM Journal WHERE JournalPkey=:OldJournalPkey ');
    QryFDTemp.ParamByName('NewJournalPkey').AsString := lNewJournalPkey;
    QryFDTemp.ParamByName('OldJournalPkey').AsString := '207AF8c_2036q\f6';   
    QryFDTemp.ExecSQL;

    QryFDTemp.Close;
    QryFDTemp.SQL.Clear;
    QryFDTemp.SQL.Add('INSERT INTO JournalDetail (journaldetailpkey, type) '+
                   'SELECT :NewJournalDetailPkey AS JournalDetailPkey, type ' +
                   'FROM JournalDetail WHEE JournalDetailPkey=:JournalDetailPkey ');
    QryFDTemp.ParamByName('NewJournalDetailPkey').AsString := '1233456789';
    QryFDTemp.ParamByName('JournalDetailPkey').AsString := '13242314234';
    QryFDTemp.ExecSQL; //right here (the syntax error) is when PG reports the transaction as done - monitor with SELECT pid, backend_xid FROM pg_stat_activity WHERE backend_xid IS NOT NULL;

    QryFDTemp.ExecSQL('COMMIT;');
  except
    QryFDTemp.ExecSQL('ROLLBACK;');
  end;

//at this point any other table/query on the TFDConnection fails with "[FireDAC][Phys][PG][libpq] ERROR: current transaction is aborted, commands ignored until end of transaction block"

Weirdo - The problem with your example is that it breaks down with use of parameters. PG does not allow multiple commands in a prepared statement. And parameters force it to be a prepared statement. 

 

The only other solution we've found is to have an FDConnection on the side just for transactions. That seems so very unnecessary. We could also create PG Stored Procedures but we have dozens and dozens of code blocks like this with so many parameters. This worked fine in Advantage DB. 

[FLDelphi 0]

12 hours ago, Pat Heuvel said:

Why don't you look into using postgresql's own begin/commit/rollback with an explicit execsql, rather than relying on FireDac's limited transaction implementation?  

 

We rely on parameters - Which cause it to be a prepared statement, which in turn PG does not allow multiple commands in a prepared statement.