[Info-Ingres] sequence returns zero

[Database Administrator]

Hi All,



II 9.1.1 (a64.lnx/103)NPTL +patch13001



I have a problem with a table whose key column defaults to
sequence.nextval.



It generally works.



But every now and then an ESQLC program (which normally works perfectly
well) manages to get a zero returned as the nextval. Shortly afterwards
the same program may return the correct nextval or it may continue
giving zeros for a while.



Interactively you always get the correct nextval.



I've used querytext auditing to confirm that the program uses an execute
immediate statement to do the insert and that the query does not
explicitly set a value for the key, it relies on the default value
coming from the sequence.



It *seems* to be related to two things.

1. The program uses a dynamic cursor select on an unrelated table just
before the execute immediate.

2. The database must go idle briefly before the program executes.



I'm not 100% convinced about the first point, but the second one seems
to be good, but difficult to quantify.



My working theory is that the sequence values are cached in the server
and when the database goes idle these values are flushed from the server
with the last used value not being written back to the iisequence
catalog. This seems to be what auditdb is telling me at least.



I suspect that if the program reconnects to the idle database within a
certain window of time that something goes pair-shaped and the
iisequence catalog is not re-read.



Has anyone seen anythin like this before?



I've spent two days on this so-far and I'm just getting to the stage of
building a test case, but I'm struggeling to make it consistent.



Any ideas would help.



Martin Bowes