Clayton Arends

Member since: Wednesday, 16 November 2016
Last login: 3 years ago
Profile viewed: 380 views

No Rank
Points: 0

Clayton Arends created a new topic ' Possible bug setting WithHold in TFDPhysPgCommand.CreateStmt' in the forum. 3 years ago

While tracking down a sporadic bug in my product I believe I have discovered a logic bug in FireDAC in TFDPhysPgCommand.CreateStmt. The final lines in the method are:

  Result.WithHold := (GetCommandKind <> skSelectForLock) and
    not PgConnection.GetTransaction.Active or (oFtch.CursorKind = ckStatic);

I do not understand the full intention of the logic but I believe there are some missing parentheses such as:
  Result.WithHold := (GetCommandKind <> skSelectForLock) and
    (not PgConnection.GetTransaction.Active or (oFtch.CursorKind = ckStatic));

The problem I'm tracking down is my program will occasionally run a FOR UPDATE SQL command using a WITH HOLD cursor which results in the exception
[FireDAC][Phys][PG][libpq] ERROR: DECLARE CURSOR WITH HOLD ... FOR UPDATE is not supported

The FOR UPDATE is intentional but the WITH HOLD should not be there. I believe what is happening is when CursorKind is set to ckStatic CreateStmt will set WithHold to True regardless of CommandKind being skSelectForLock.

Here is some code to demonstrate the problem:
  query := TFDQuery.Create(nil);
    query.Connection := FDConnection1;
    query.FetchOptions.CursorKind := ckStatic;
    query.Command.CommandKind := skSelectForLock;
    query.SQL.Text :=
      'select * from some_table limit 1 ' +
         'for update skip locked';


Try these links (maybe one of them will help):

After a lot of searching I believe I found the related code at