[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Vacuum problem due to temp tables
From:       Bhakti Ghatkar <bghatkar () zedo ! com>
Date:       2011-02-28 5:20:36
Message-ID: AANLkTikONUnz_c3mKXeobh7i3OU-bJjZk+xf1xdwbH4p () mail ! gmail ! com
[Download RAW message or body]

Tom,

The query which you gave returns me 0 rows.

select ctid,xmin,xmax,* from pg_index where indexrelid in
         (select indexrelid from pg_index group by 1 having count(*)>1);

Regards,
Bhakti

On Sat, Feb 26, 2011 at 10:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Bhakti Ghatkar <bghatkar@zedo.com> writes:
> > We were running full vacuum on DB when we encountered the error below;
>
> > INFO:  vacuuming "pg_catalog.pg_index"
> > *vacuumdb: vacuuming of database "rpt_production" failed: ERROR:
>  duplicate
> > key value violates unique constraint "pg_index_indexrelid_index"*
> > DETAIL:  Key (indexrelid)=(2678) already exists.
>
> That's pretty bizarre, but what makes you think it has anything to do
> with temp tables?  OID 2678 is pg_index_indexrelid_index itself.
> It looks to me like you must have duplicate rows in pg_index for that
> index (and maybe others?), and the problem is exposed during vacuum full
> because it tries to rebuild the indexes.
>
> Could we see the output of
>
>        select ctid,xmin,xmax,* from pg_index where indexrelid in
>          (select indexrelid from pg_index group by 1 having count(*)>1);
>
>                        regards, tom lane
>

[Attachment #3 (text/html)]

<font face="trebuchet ms,sans-serif"> Tom,</font><div><font class="Apple-style-span" \
face="&#39;trebuchet ms&#39;, sans-serif"><br></font></div><div><font face="trebuchet \
ms,sans-serif">The query which you gave returns me 0 rows.</font></div>

<div><font class="Apple-style-span" face="&#39;trebuchet ms&#39;, \
sans-serif"><br></font></div><div><font face="trebuchet ms,sans-serif"><span \
class="Apple-style-span" style="font-family: arial; ">select ctid,xmin,xmax,* from \
pg_index where indexrelid in<br>

         (select indexrelid from pg_index group by 1 having \
count(*)&gt;1);</span></font></div><div><font class="Apple-style-span" face="arial, \
sans-serif"><br></font></div><div><font face="trebuchet ms,sans-serif"><font \
class="Apple-style-span" face="arial, sans-serif">Regards,</font></font></div>

<div><font face="trebuchet ms,sans-serif"><font class="Apple-style-span" face="arial, \
sans-serif">Bhakti<br></font></font><br><div class="gmail_quote">On Sat, Feb 26, 2011 \
at 10:55 PM, Tom Lane <span dir="ltr">&lt;<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt;</span> wrote:<br>

<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex;"><div class="im">Bhakti Ghatkar &lt;<a \
href="mailto:bghatkar@zedo.com">bghatkar@zedo.com</a>&gt; writes:<br> &gt; We were \
running full vacuum on DB when we encountered the error below;<br> <br>
</div><div class="im">&gt; INFO:  vacuuming &quot;pg_catalog.pg_index&quot;<br>
&gt; *vacuumdb: vacuuming of database &quot;rpt_production&quot; failed: ERROR:  \
duplicate<br> &gt; key value violates unique constraint \
&quot;pg_index_indexrelid_index&quot;*<br> &gt; DETAIL:  Key (indexrelid)=(2678) \
already exists.<br> <br>
</div>That&#39;s pretty bizarre, but what makes you think it has anything to do<br>
with temp tables?  OID 2678 is pg_index_indexrelid_index itself.<br>
It looks to me like you must have duplicate rows in pg_index for that<br>
index (and maybe others?), and the problem is exposed during vacuum full<br>
because it tries to rebuild the indexes.<br>
<br>
Could we see the output of<br>
<br>
        select ctid,xmin,xmax,* from pg_index where indexrelid in<br>
          (select indexrelid from pg_index group by 1 having count(*)&gt;1);<br>
<br>
                        regards, tom lane<br>
</blockquote></div><br></div>



[prev in list] [next in list] [prev in thread] [next in thread]