FindKey returns true but doesn't move to record

[Mark Williams 14]

I use a Firedac query with a PostgreSQL database

 

I start with an empty query (only option changed from default is CachedUpdates to true) and append records to it as required.

 

To get the required table structure I query the database table using a select statement that I know will return no records (docid=0)

 

Then I add the new records using Append. 

 

Following this I add an index:

 if indexes.FindIndex('doc_id')<>Nil then
   exit;
 Index := Indexes.Add;
 Index.Name := 'doc_id';
 Index.Fields := 'docid';
 Index.Active := true;
 IndexesActive := true;
 IndexName := 'doc_id';

When I try to call FindKey with different docid values it returns true but never changes the actual record. I have the same problem if I use Locate.

 

If I iterate using First, Next I can retrieve the correct records.

 

I should also add that I tried to assign this table's data to a FDMemTable, but the assignment failed.

 

I can only assume there is something wrong with my table structure, but I have been unable to discover what. It also seems odd that both FindKey and Locate report true, but do not move to the correct record which I know exists.

 

I can make this work by looping through the query record by record, but as it as to do this a potentially large number of times there will be a big performance hit.

 

Does anyone have any ideas why FindKey does not work in the above scenario?