Quoth johnf@jfcomputer.com (John):
> On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote:
> > I would be looking at the log files for the Inserts into that table as a
> > means to track down what is the cause. If there are no log files or
> > don't have enough detail, crank up the logging level and wait for it to
> > happen again???
>
>
> That is scary - let it happen again???? I'm not keeping enough info in the
> log. I actually turned off most of the info the log files are gathering
> because the system has been running for 6-7 months without an issue. I just
> got a call around noon telling me something was going wrong. That's when I
> discovered the sequences were the wrong values. I'm sure there has to be
> some sort of real explanation - but I don't know what it is.
There are several possible causes:
- Something somewhere is inserting values directly into the serial columns, without using the sequence. This can
beprevented by REVOKEing INSERT and UPDATE on the relevant columns for all users. If this causes problems
anywherein your app, those are good places to start looking for bugs.
- Something somewhere is manipulating the sequence. This can be prevented by REVOKEing UPDATE on all sequences
forall users. You may need some additional GRANTs of USAGE on sequences if parts of the app were relying on
UPDATEto call nextval().
Obviously if your app routinely drops and creates tables you will need to
arrange for these permissions to be applied every time.
- The database has become corrupted, perhaps by a badly-done backup and restore. (I would not expect taking a
backupalone to cause corruption, but if the backup isn't done right the backed-up copy may be corrupt.) Have
youdone a restore recently?
- Something I haven't thought of :).
- A bug in Pg. While this is *extremely* unlikely, it must be mentioned as a possibility.
Ben