Re: strangest thing happened

Quoth johnf(at)jfcomputer(dot)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 be prevented by
REVOKEing INSERT and UPDATE on the relevant columns for all users.
If this causes problems anywhere in 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 for all users. You
may need some additional GRANTs of USAGE on sequences if parts of
the app were relying on UPDATE to 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 backup alone to cause
corruption, but if the backup isn't done right the backed-up copy
may be corrupt.) Have you done 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