Hello,
I have a table with:
A DEFAULT sequence number
A rule that causes a NOTIFY on insert
A spawned process that does one INSERT on the table per spawning.
Two processes LISTENing for INSERTs, one of which does a single UPDATE
after processing.
Last night I had four consecutive INSERTs on the table consume the
proper four sequence numbers, but the rows themselves did not exist in
the table when I checked up on why processing did not seem to occur.
The first of the four rows apparently made it through processing, but
the other three did not. The situation seemed to clear itself after I
reset the process (and therefore the backend) that does processing and
UPDATEs. I see three possible causes:
1) An INSERT statement that fails before completion can still use up a
sequence #
2) Some kind of Postgres bug?
3) A unique problem caused by my platform/environment? (RH 5.1/Linux
2.2.4, Perl5 interface library from Postgres 6.4.0 (problem?), Libpq
from 6.5.1; the UPDATE code is a Perl5 script.)
There are no DELETE statements in any of the code that manipulates this
database, and the only UPDATE simply changes a flag. So if the cause
isn't #1, then I've definitely got something wierd going on, like those
mixed libraries.
Thanks for any hints or tips.
Brian Curnow