From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200704032240.l33MeL423849@momjian.us>
Subject: Re: create index concurrently docs ...
In-Reply-To: <5F277D5E-DB72-4BA1-9DFA-175FB749FB21@socialserve.com>
To: James Robinson <jlrobins@socialserve.com>
Date: Tue, 3 Apr 2007 18:40:21 -0400 (EDT)
CC: pgsql-docs@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1175640021-27088-0_"
Content-Transfer-Encoding: 7bit


--ELM1175640021-27088-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"


We have added the documentation you requested --- attached.

---------------------------------------------------------------------------

James Robinson wrote:
> In reading over the caveats regarding create index concurrently, I  
> think it'd be nice to see an example of what the return result will  
> be if an 'invalid' index is created so a dba who read the docs night  
> recognize the error, as well as for searchengine fodder to find out  
> what to do real fast if such an error happens to them.
> 
> Something around this paragraph in http://www.postgresql.org/docs/ 
> current/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY:
> 
> "If a problem arises during the second scan of the table, such as a  
> uniqueness violation in a unique index, the CREATE INDEX command will  
> fail but leave behind an "invalid" index."
> 
> Thanks!
> ----
> James Robinson
> Socialserve.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

-- 
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

--ELM1175640021-27088-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename="/rtmp/1"


A doc patch to add an example of what an invalid index looks like in psql, as
requested by a commenter and Bruce.


Index: doc/src/sgml/ref/create_index.sgml
===================================================================
RCS file: /home/stark/src/REPOSITORY/pgsql/doc/src/sgml/ref/create_index.sgml,v
retrieving revision 1.60
diff -c -r1.60 create_index.sgml
*** doc/src/sgml/ref/create_index.sgml	31 Jan 2007 23:26:03 -0000	1.60
--- doc/src/sgml/ref/create_index.sgml	3 Apr 2007 17:19:25 -0000
***************
*** 332,338 ****
      uniqueness violation in a unique index, the <command>CREATE INDEX</>
      command will fail but leave behind an <quote>invalid</> index. This index
      will be ignored for querying purposes because it might be incomplete;
!     however it will still consume update overhead.  The recommended recovery
      method in such cases is to drop the index and try again to perform
      <command>CREATE INDEX CONCURRENTLY</>.  (Another possibility is to rebuild
      the index with <command>REINDEX</>.  However, since <command>REINDEX</>
--- 332,352 ----
      uniqueness violation in a unique index, the <command>CREATE INDEX</>
      command will fail but leave behind an <quote>invalid</> index. This index
      will be ignored for querying purposes because it might be incomplete;
!     however it will still consume update overhead. The <application>psql</>
!     <command>\d</> command will mark such an index as <literal>INVALID</>:
! 
! <programlisting>
! postgres=# \d tab
!        Table "public.tab"
!  Column |  Type   | Modifiers 
! --------+---------+-----------
!  col    | integer | 
! Indexes:
!     "idx" btree (col) INVALID
! </programlisting>
! 
!     The recommended recovery
      method in such cases is to drop the index and try again to perform
      <command>CREATE INDEX CONCURRENTLY</>.  (Another possibility is to rebuild
      the index with <command>REINDEX</>.  However, since <command>REINDEX</>


-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

--ELM1175640021-27088-0_--