Date: Thu, 8 Jul 2010 08:37:42 +0100
From: Ben Morrow <ben@morrow.me.uk>
To: johnf@jfcomputer.com, pgsql-sql@postgresql.org
Subject: Re: strangest thing happened
Message-ID: <20100708073742.GA23191@osiris.mauzo.dyndns.org>
References: <201007071259.03294.johnf@jfcomputer.com>
	<20100707204244.GB28181@rice.edu> <4C34FC50.6070203@magwerks.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <201007071541.27248.johnf@jfcomputer.com>
Organization: Who, me?
User-Agent: Mutt/1.5.20 (2009-06-14)

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 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