From: Ben Morrow <ben@morrow.me.uk>
To: johnf@jfcomputer.com
To: pgsql-sql@postgresql.org
Subject: Re: strangest thing happened
Date: Thu, 8 Jul 2010 08:37:42 +0100
Message-ID: <20100708073742.GA23191@osiris.mauzo.dyndns.org> (raw)
In-Reply-To: <201007071541.27248.johnf@jfcomputer.com>
References: <201007071259.03294.johnf@jfcomputer.com>
<20100707204244.GB28181@rice.edu>
<4C34FC50.6070203@magwerks.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ben@morrow.me.uk, johnf@jfcomputer.com
Subject: Re: strangest thing happened
In-Reply-To: <20100708073742.GA23191@osiris.mauzo.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox