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
Show quoted text
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.
Show quoted text
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