#647 PostgreSQL UUID primary key editing issue

[Mark Ford]

It looks like updating or deleting a records with a UUID primary key in PostgreSQL fails as it doesn't find the record. It gives "Project UUIDTest.exe raised exception class EZDatabaseError with message '0 record(s) updated. Only one record should have been updated" Demo with delete application attached although update will cause the same issue.

[Mark Ford]

It looks like uuid fields will be retrieved correctly, but get messed up when inserted. Here's a simple test case of inserting a guid and then selecting it back. The results are:
Inserted: {783DAE87-E33E-4314-8730-398ED525F07B}
Retrieved: {87AE3D78-3EE3-1443-8730-398ED525F07B}

    // deleted the table records first.. not shown
    ZQuery.SQL.Text := 'insert into uuid_simple values (:myGuid)';
    var myGuid: TGuid :=  TGuid.Create('{783DAE87-E33E-4314-8730-398ED525F07B}');
    ZQuery.Params[0].AsGUID := myGuid;
    ZQuery.ExecSQL;
    writeln('  Inserted: ' + myGuid.ToString);

    ZQuery.SQL.Text := 'SELECT id FROM uuid_simple';
    ZQuery.Open;
    while not ZQuery.eof do
    begin
      Writeln(' Retrieved: ' + ZQuery.Fields[0].AsString);
      ZQuery.Next;
    end;

Note: In ZDbcPostgreSqlResultSet the getGuid function seems to indicate that Pg's internal uuid structure is different than the standard. Maybe that conversion has to happen when sending a uuid to pg as well? That's purely a guess btw!

[marsupilami79]

Hello Mark,

when comparing your UUIDs, it seems that bytes get swapped somewhere - or need to be swapped again somewhere. Looking at the structure of a TGUID might help:

type TGuid = packed record
  case Integer of
    1: (
        Data1: DWord;                 // First 4 bytes of GUID
        Data2: Word;                  // Bytes 5 and 6 of GUID
        Data3: Word;                  // Bytes 7 and 8 of GUID
        Data4: array [0..7] of Byte;  // Bytes 9-17 of GUID
      );

So it looks like Data1, Data2 and Data3 get (not) byteswapped (little endian to big endian or vice versa), while Data4 doesn't get swapped because it is just a sequence of bytes.

So - if you create a TGUID with a value of "783DAE87-E33E-4314-8730-398ED525F07B", what does PGAdmin show?

What I also wonder about: We have tests for GUID support in the test suite. I wonder why the don't get fired with this bug? Hmmmm.....

Best regards,

Jan

[Mark Ford]

I agree. As mentioned above the getGuid function does:

      if FBinaryValues then begin
        {$IFNDEF ENDIAN_BIG} {$Q-} {$R-}
        Result.D1 := PG2Cardinal(@SrcUUID.D1); //what a *beep* swapped digits! but only on reading
        Result.D2 := (SrcUUID.D2 and $00FF shl 8) or (SrcUUID.D2 and $FF00 shr 8);
        Result.D3 := (SrcUUID.D3 and $00FF shl 8) or (SrcUUID.D3 and $FF00 shr 8);
        PInt64(@Result.D4)^ := PInt64(@SrcUUID.D4)^;
        {$IFDEF RangeCheckEnabled} {$R+} {$ENDIF}
        {$IFDEF OverFlowCheckEnabled} {$Q+} {$ENDIF}
        {$ELSE}
        Result := SrcUUID^;
        {$ENDIF ENDIAN_BIG}
      end else ValidGUIDToBinary(Buffer, @Result.D1);

I'd bet that a converse setGuid is needed.

[Mark Ford]

I got a chance to play around with this and overriding setguid and making that conversion appears to solve all of my issues mentioned above. Here's what I used:

procedure TZPostgreSQLPreparedStatementV3.SetGUID(Index: Integer; const Value: TGUID);
var
  P: PByte;
  D1: Cardinal;
  D2, D3: Word;
begin
  P := PByte(@Value);
  {$IFNDEF ENDIAN_BIG} {$Q-} {$R-}
    D1 := PG2Cardinal(P);
    PCardinal(P)^ := D1;
    D2 := PWord(P + 4)^;
    PWord(P + 4)^ := (D2 and $00FF shl 8) or (D2 and $FF00 shr 8);
    D3 := PWord(P + 6)^;
    PWord(P + 6)^ := (D3 and $00FF shl 8) or (D3 and $FF00 shr 8);
    {$IFDEF RangeCheckEnabled} {$R+} {$ENDIF}
    {$IFDEF OverFlowCheckEnabled} {$Q+} {$ENDIF}
  {$ENDIF ENDIAN_BIG}
  inherited;
end;

-Mark

[marsupilami79]

Hello Mark,

I applied your changes to the 8.0-patches branch.
Thank you :)

Best regards,

Jan

[marsupilami79]

Fixed :)