FireDAC getting tripped up with PostgreSQL transactions

[FLDelphi 1]

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 July 26 by FLDelphi

[Brian Evans 128]

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 26]

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 July 26 by weirdo12

[FLDelphi 1]

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 1]

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. 

[Pat Heuvel 1]

What does the postgresql log say at the "right here" point (and beyond)? I would think that an exception would be thrown here which should have taken you to the rollback, which should have cleared the error.

 

[FLDelphi 1]

All right, pretty interesting- Let me go through the logs as I iterate over the code. BTW - I tried to post this in the post, but I was getting Spam blocks. See attached.

 

To me these logs show a very interesting story. FD does not handle transactions like you think it would. Even if you toggle things in the TxOptions, it makes no difference. At least in PG land.

 

FireDAC_PG_Transactions_LogCollection.txt

Edited July 27 by FLDelphi

[weirdo12 26]

If you use TFDScript you can use macros and and parameters. I tested this text and it works as expected. You end up with 2 rows in the table.

 

DROP TABLE "&table_name" CASCADE;

CREATE TABLE "&table_name"
(
serial_number serial,
ticket_date timestamp NULL DEFAULT LOCALTIMESTAMP,
PRIMARY KEY (serial_number)
);

BEGIN;
INSERT INTO "&table_name" (serial_number, ticket_date) VALUES (1, CAST(:start_date AS TIMESTAMP));
INSERT INTO "&table_name" (serial_number, ticket_date) VALUES (2, CAST(:start_date AS TIMESTAMP));
END;

BEGIN;
INSERT INTO "&table_name" (serial_number, ticket_date) VALUES (3, CAST(:start_date AS TIMESTAMP));
INSERT INTO "&table_name" (serial_number, ticket_date) VALUES (1, CAST(:start_date AS TIMESTAMP));
END;

 

The TFDScript.OnScriptError event is called when an attempt to INSERT serial_number 1 again and serial_number 3 does not exist in the table.

 

[FireDAC][Phys][PG][libpq] ERROR: duplicate key value violates unique constraint "receipt_ticket_pkey".
Key (serial_number)=(1) already exists.

The TDScript.Status value at the completion of the TDScript.ExecuteAll is ssFinishedWithErrors.

 

FDScript->ScriptOptions->CommandSeparator = ";";
FDScript->SQLScripts->Clear();

auto sql_script {FDScript->SQLScripts->Add()};

sql_script->SQL->Assign(cxMemoSQL->Lines);

if (FDScript->Macros->FindMacro("table_name") == nullptr)
{
	auto macro_ {FDScript->Macros->Add()};

	macro_->Name = "table_name";
}

FDScript->Macros->MacroByName("table_name")->AsRaw = FFrameTicketDateFilter->TableName;

if (FDScript->Params->FindParam("start_date") == nullptr) {
	FDScript->Params->Add("start_date", ftString);
}

FDScript->Params->ParamByName("start_date")->AsString = FFrameTicketDateFilter->StartDate();

if (FDScript->Params->FindParam("end_date") == nullptr) {
	FDScript->Params->Add("end_date", ftString);
}

FDScript->Params->ParamByName("end_date")->AsString = FFrameTicketDateFilter->EndDate();

FDScript->ValidateAll();

if (FDScript->Status == ssFinishSuccess)
{
#if defined(_CODESITE)
	// Don't time how long it take to make the previous CodeSite calls.
	_di_ICodeSiteTimer timer_ {CodeSite->Timer({}, TCodeSiteTimingFormat::tfMilliseconds, true, false)};
#endif
	FDScript->ExecuteAll();

#if defined(_CODESITE)
	timer_->Stop();
#endif
	if (FDScript->Status == ssFinishSuccess)
	{
		auto msg_ {"The SQL Source text executed successfully."};

		cxMemoSQLText->Clear();
		cxMemoSQLText->Lines->Add(msg_);
		Dxmessagedialog::dxMessageDlg(msg_, mtInformation, TMsgDlgButtons() << mbOK, 0);
	}
}

void __fastcall TFrameGridLoadSummary::FDScriptError(TObject *ASender, TObject *AInitiator,
		  Exception *&AException)
{
	cxMemoSQLText->Clear();
	cxMemoSQLText->Lines->Add(AException->Message);
	Dxmessagedialog::dxMessageDlg(AException->Message, mtError, TMsgDlgButtons() << mbOK, 0);
}
//---------------------------------------------------------------------------

 

 

Edit:

 

I just wanted to add if I execute this code:

BEGIN;
INSERT INTO "&table_name" (serial_number) VALUES (3);
INSERT INTO "&table_name" (serial_number) VALUES (1);
END;

Using TFDQuery instead of TFDScript I get exactly the same issue that was initially reported if immediately after I execute something like 'SELECT * FROM &table_name'.

 

Quote

[FireDAC][Phys][PG][libpq] ERROR: current transaction is aborted, commands ignored until end of transaction block

 

Edited July 27 by weirdo12
Added an image

[weirdo12 26]

Another thing: I totally get using TFDQuery out of habit but don't forget that there is the more lightweight TFDCommand or TFDConnection.ExecSQL for doing INSERT, UPDATE and DELETE.

[FLDelphi 1]

I have some more testing to do, but I can confirm that using TFDScript behaves like I'd imagine. Thank you very much.

 

So that leaves 2 options so far:

 

* Use TFDScript

* Have a 2nd TFDConnection that's in charge of handling multi command transactions. 

[Rolphy Reyes 0]

Hi!

According to this: https://docwiki.embarcadero.com/Status/en/FireDAC_Database_Support

Postgres 16 is not official supported in Delphi 10.4