Background​

Since the ISAM emulating tables of the PostgreSQL DatabaseEngine are in fact regular SQL tables, it is possible to manipulate them using SQL statements. However, some SQL operations are problematic, as they may lead to inconsistent/incorrect ISAM behavior. In the following, we will outline some ways to detect possible inconsistencies and how to repair them.

RecCount()​

For performance reasons, the record count of an ISAM emulating table is cached in the alaska-software.isam.tables meta table. To verify that meta data, you can simply use the following SQL statements. Both should return the same count for the rows/records in the table.

SQL: 
SELECT record_count FROM "alaska-software.isam.tables" WHERE table_name='<mytable>';
SELECT count(*) FROM <mytable>;

If this is not the case, you can repair the table meta data implicitly using a PACK/DbPack(), or you can repair manually with the following SQL statement

SQL: 
UPDATE "alaska-software.isam.tables"
       SET record_count=(SELECT count(__record) FROM <mytable>)
       WHERE table_name='<mytable>';

RecNo()​

The RecNo() in an ISAM emulating SQL table is managed via the __record column. A new record number is automatically inserted by the PostgreSQL server via a sequence. This way, it is guaranteed that data inserted via a SQL statement is consistent and visible to the ISAM emulation layer. However, since a SQL DELETE command physically removes a row from the table, gaps may be created in the record numbering. These gaps will finally lead to incorrect behavior of the ISAM emulation layer with the following effects:
- SKIP/DbSkip() operations which try to navigate to a non-existing record will fail with a SQL runtime error
- APPEND/DbAppend() operations will insert a blank record with the correct record number, but navigation to the appended record will show invalid data from a different/existing record.

Situations with inconsistent record numbers can be detected using the following SQL statements. If everything is correct, the statements should return the same number of rows.

SQL: 
SELECT max(__record) FROM <mytable>;
SELECT count(*) FROM (SELECT DISTINCT __record FROM <mytable>) AS counted_rows;

To retrieve the list of "deleted" rows, in other words, to document any gaps, the following SQL statement can be used:

SQL: 
SELECT * FROM generate_series(1, (SELECT count(*) FROM <mytable>))
  EXCEPT
SELECT __record FROM <mytable>;

A PACK/DbPack() is required to repair such a inconsistency. The PostgreSQL ISAM emulation ensures with its implementation of the pack operation consistent record numbering as well as proper index-key value and record# matching. In case of large tables with hundreds of thousand or even millions of records this can become a very time consuming operation.