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 13 hours ago by FLDelphi

[Brian Evans 125]

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?