Subject: create index concurrently docs ...

From: James Robinson <[email-masked]>
Date: Wed, 6 Dec 2006 10:59:48 -0500

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

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 3 Feb 2007 20:27:43 -0500 (EST)

Does anyone know the answer to this?  I couldn't find it, but it is a
good quesiton.

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

James Robinson wrote:
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

-- 
  Bruce Momjian   [email-masked]
  EnterpriseDB    http://www.enterprisedb.com

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

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 3 Apr 2007 18:40:21 -0400 (EDT)

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

-- 
  Bruce Momjian  <[email-masked]>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

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

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

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