Thread: duplicate key violates unique constraint pg_toast_635216540

Hi,

For the last 6 months or so we've had an intermittent issue while doing a
data import with a simple update statement.  The fix that we've found for
this issue is to REINDEX TABLE <tablename>;


Has anyone seen this error before?

Again, the error is: duplicate key violates unique constraint pg_toast_<>

Thanks,

Paul

"Paul Laughlin" <plaughlin@gmail.com> writes:
> For the last 6 months or so we've had an intermittent issue while doing a
> data import with a simple update statement.  The fix that we've found for
> this issue is to REINDEX TABLE <tablename>;

What PG version is this?

Are you sure that the REINDEX actually does anything, as opposed to
merely retrying the data import?  I'm thinking you may be having
problems with OID collisions after OID wraparound, which is something
8.1 should defend against but no earlier version does.

What do you get from
    select count(distinct chunk_id) from pg_toast.pg_toast_635216540 ;

            regards, tom lane

warehouse=# select count(distinct chunk_id) from
pg_toast.pg_toast_635216540;
count
-------
74557
(1 row)

We're on version 8.0.7

On 10/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Paul Laughlin" <plaughlin@gmail.com> writes:
> > For the last 6 months or so we've had an intermittent issue while doing
> a
> > data import with a simple update statement.  The fix that we've found
> for
> > this issue is to REINDEX TABLE <tablename>;
>
> What PG version is this?
>
> Are you sure that the REINDEX actually does anything, as opposed to
> merely retrying the data import?  I'm thinking you may be having
> problems with OID collisions after OID wraparound, which is something
> 8.1 should defend against but no earlier version does.
>
> What do you get from
>         select count(distinct chunk_id) from pg_toast.pg_toast_635216540 ;
>
>                         regards, tom lane
>

"Paul Laughlin" <plaughlin@gmail.com> writes:
> warehouse=# select count(distinct chunk_id) from
> pg_toast.pg_toast_635216540;
> count
> -------
> 74557
> (1 row)

> We're on version 8.0.7

Well, 8.0 is definitely at risk for OID collisions in a toast table,
but with so few entries I'd have thought the probability pretty low.
How often do you see these errors?

            regards, tom lane

We got it early last week and again this morning.  Before these two it was
about six months ago.

On 10/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Paul Laughlin" <plaughlin@gmail.com> writes:
> > warehouse=# select count(distinct chunk_id) from
> > pg_toast.pg_toast_635216540;
> > count
> > -------
> > 74557
> > (1 row)
>
> > We're on version 8.0.7
>
> Well, 8.0 is definitely at risk for OID collisions in a toast table,
> but with so few entries I'd have thought the probability pretty low.
> How often do you see these errors?
>
>                         regards, tom lane
>

"Paul Laughlin" <plaughlin@gmail.com> writes:
> We got it early last week and again this morning.  Before these two it was
> about six months ago.

A certain amount of clustering could be expected, if a lot of the
entries were made at the time of initial table load --- they'd have
nearby OIDs.  You can either ignore it (I doubt you need REINDEX, just
retry the update) or else update to PG 8.1 ...

            regards, tom lane