------=_Part_62529_7009813.1161015406682
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_62529_7009813.1161015406682
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,<br><br>For the last 6 months or so we've had an intermittent issue while doing a data import with a simple update statement.&nbsp; The fix that we've found for this issue is to REINDEX TABLE &lt;tablename&gt;; <br><br><br>Has anyone seen this error before?&nbsp;
<br><br>Again, the error is: duplicate key violates unique constraint pg_toast_&lt;&gt;<br><br>Thanks,<br><br>Paul<br><b r>

------=_Part_62529_7009813.1161015406682--

"Paul Laughlin" <plaughlin (AT) gmail (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

------=_Part_62889_13508428.1161017135623
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 (AT) sss (DOT) pgh.pa.us> wrote:
------=_Part_62889_13508428.1161017135623
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

warehouse=# select count(distinct chunk_id) from pg_toast.pg_toast_635216540;<br>count<br>-------<br>74557<br>(1 row)<br><br>We're on version 8.0.7<br><br><div><span class="gmail_quote">On 10/16/06, <b class="gmail_sendername">
Tom Lane</b> &lt;<a href="mailto:tgl (AT) sss (DOT) pgh.pa.us">tgl (AT) sss (DOT) pgh.pa.us</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">&quot;Paul Laughlin&quot; &lt;
<a href="mailtolaughlin (AT) gmail (DOT) com">plaughlin (AT) gmail (DOT) com</a>&gt; writes:<br>&gt; For the last 6 months or so we've had an intermittent issue while doing a<br>&gt; data import with a simple update statement.&nbsp;&nbsp;The fix that we've found for
<br>&gt; this issue is to REINDEX TABLE &lt;tablename&gt;;<br><br>What PG version is this?<br><br>Are you sure that the REINDEX actually does anything, as opposed to<br>merely retrying the data import?&nbsp;&nbsp;I'm thinking you may be having
<br>problems with OID collisions after OID wraparound, which is something<br>8.1 should defend against but no earlier version does.<br><br>What do you get from<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;select count(distinct chunk_id) from pg_toast.pg_toast_635216540 ;
<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;regards, tom lane<br></blockquote></div><br>

------=_Part_62889_13508428.1161017135623--

"Paul Laughlin" <plaughlin (AT) gmail (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

------=_Part_63173_20196759.1161018090347
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 (AT) sss (DOT) pgh.pa.us> wrote:
------=_Part_63173_20196759.1161018090347
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

We got it early last week and again this morning.&nbsp; Before these two it was about six months ago.<br><br><div><span class="gmail_quote">On 10/16/06, <b class="gmail_sendername">Tom Lane</b> &lt;<a href="mailto:tgl (AT) sss (DOT) pgh.pa.us">
tgl (AT) sss (DOT) pgh.pa.us</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">&quot;Paul Laughlin&quot; &lt;<a href="mailtolaughlin (AT) gmail (DOT) com">
plaughlin (AT) gmail (DOT) com</a>&gt; writes:<br>&gt; warehouse=# select count(distinct chunk_id) from<br>&gt; pg_toast.pg_toast_635216540;<br>&gt; count<br>&gt; -------<br>&gt; 74557<br>&gt; (1 row)<br><br>&gt; We're on version 8.0.7
<br><br>Well, 8.0 is definitely at risk for OID collisions in a toast table,<br>but with so few entries I'd have thought the probability pretty low.<br>How often do you see these errors?<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;regards, tom lane
<br></blockquote></div><br>

------=_Part_63173_20196759.1161018090347--

"Paul Laughlin" <plaughlin (AT) gmail (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster