Problem description​

The Xbase++ ISAM emulation uses a native PostgreSQL sequence to calculate the next record number for append operations. Therefore the success of a DbAppend()/APPEND directly depends on the correctness of that PostgreSQL sequence. As a matter of fact the sequence can become inconsistent with the table under the following situations:

  • incorrect or partial restore of a database
  • disabling sequences for that table
  • incorrect usage of ISAM_LOCKMODE_BATCHING

In such cases, a DbAppend() operation fails with a runtime error as outlined below. The error description referes to the primary key constraint of that table which guarantees with ISAM emulating tables the uniqness of the __record column. In fact this error says that the new value for __record choosen is already used as an record number. In short, the PostgreSQL sequence for that table is broken.

Xbase++: 
oError:GenCode // -> 8999
oError:description // -> Description : duplicate key value violates unique constraint "<table-name>_pkey"

In the PostgreSql server log the following information can be found. Which in fact says the same but with more SQL stylish details.
Code: 
<date> [12816]: [1-1] .. ERROR:  duplicate key value violates unique constraint "<name>"
<date> [12816]: [2-1] .. DETAIL: Key (__record)=(<number>) already exists.
<date> [12816]: [3-1] .. STATEMENT:  INSERT INTO "<name>" (...)

Solution​

To solve the problem, the PostgreSql sequence for determining the next record number in a DbAppend() operations on ISAM emulating tables must be fixed. This can be done with an SQL statement to set the starting point of the sequence via the setval( <sequence-name>, <new-value> ) stored procedure. To compose the sequence name, concatenate the tablename with "__record_seq" constant as shown below.

SQL: 
<sequence-name> := <table-name> + '___record_seq'
SELECT setval( '<sequence-name>', (SELECT max(__record) FROM <table-name>) )

Assuming that the sequence for the customer table is out of sync. the following Xbase++ code will fix that

Xbase++: 
// your oSession connect here...
cStmt := "SELECT setval('customer___record_seq', (SELECT max(__record) FROM customer) )"
oStmt := DacSqlStatement():fromChar(cStmt)

oStmt:build():query()
IF !Used()
  ? oSession:getLastError()
  ? oSession:getLastMessage()
ENDIF
// your oSession disconnect here...

Note: All terms inside <...> are to be substituted with the proper names.