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.