Обсуждение: Are WALs affected by 'duplicate key' errors?

A grim suspicion begins to dawn on me ...

We have an application that attempts many insertions into a table,
detecting the prior existance of the row by whether we get a 'duplicate
key in unique index' error.  When this application is running as multiple
copies and really cranking, we get 130K or more of these in a day.  Are
these filling up (and possibly messing up) our WALs?  We have experienced
a few shared-memory corruption errors, and a full-blown corruption
(although of a different table).


--
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com

"Jeff Boes" <jboes@nexcerpt.com> writes:
> We have an application that attempts many insertions into a table,
> detecting the prior existance of the row by whether we get a 'duplicate
> key in unique index' error.  When this application is running as multiple
> copies and really cranking, we get 130K or more of these in a day.  Are
> these filling up (and possibly messing up) our WALs?

Shouldn't be a problem.

> We have experienced a few shared-memory corruption errors, and a
> full-blown corruption (although of a different table).

The last couple of reports we got like that turned out to be hardware
problems (bad RAM, flaky disk controller, that sort of thing).  It
wouldn't be a waste of time to run some hardware diagnostics.

I have been thinking about your report of trouble with the 7.1.2 to
7.1.3 upgrade, and I have to confess bafflement.  There shouldn't be
any compatibility issue there AFAICS.  Are you sure there wasn't
anything else that changed at the same time?

            regards, tom lane

Hello, I a small database (about 80 new records daily), I'm running postgresql
7.1.2 running in a RedHat Box 7.1, and the primary key is updated by an
automatic secuence, my application is in C languaje and I use the following
instruction to insert each row res = PQexec(conn, data) and the value of data
is "INSERT INTO captura (registro) VALUES ( 'expected data......') ", each
record has 4 columns: (the primary key is consecutivo)

    Attribute   |           Type
|                          Modifier
---------+-------------------+--------------------------------------------
 consecutivo | integer                                | not null default
nextval('"captura_consecutivo_seq"'::text)
 fechahora    | timestamp with time zone | not null default now()
 registro        | character varying(150)     |
 procesado    | character(1)                      | not null default 'N'

everything worked perfectly by about 6 weeks but one day my application didn't
work because 'duplicate key in unique index´  I checked the problem and it was
because the  value of consecutivo in the last record was 3 numbres higher than
the actual value of captura_consecutivo_seq.

maybe the problem was becase somebody reset the computer without doing a
shutdown.

I wan't some help in the following questions:

1) How was this possible do I need to make a commit after each INSERT?? or a
transaction is needed to update at the same time the record and the
secuence???
2) I want to make sure this simple integrity of data is kept always, is there
some way to guarantee this???? (I'm thinking in other applications with
Postgresql)
3) Maybe the simple solution in this case is to get rid of the primary key and
keeping the consecutivo column as a not unique field, (I need the field for
sorting purposes) is these OK??? (I mean normally in databases a primary key
is some kind of recommended, I was thinking that the secuence was the perfect
primary key but now I'm not sure).

Thanks in advace to your helping.

Jesus Sandoval

Jesus Sandoval <meli@mzt.megared.net.mx> writes:
> maybe the problem was becase somebody reset the computer without doing a
> shutdown.

Is that speculation, or do you know that someone actually did that shortly
before you noticed the problem?

I seem to recall one other report of a sequence apparently losing a few
counts during a crash, so we may have something to look into here.  But
we need the clearest description you can give us of the events you
observed.

            regards, tom lane

Tom Lane escribió:

> Jesus Sandoval <meli@mzt.megared.net.mx> writes:
> > maybe the problem was becase somebody reset the computer without doing a
> > shutdown.
>
> Is that speculation, or do you know that someone actually did that shortly
> before you noticed the problem?
>

That is speculation, I had no control of the computer and somebody could do
that besides the computer had no UPS that week, if you wish I can see the
logs and make sure if I can see some proof of that.

>
> I seem to recall one other report of a sequence apparently losing a few
> counts during a crash, so we may have something to look into here.  But
> we need the clearest description you can give us of the events you
> observed.
>
>                         regards, tom lane