Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP

> Hi
>
> Regarding my previous post, I just successfully created a unique index on
> pg_shadow. DON'T DO THIS!!!
> -------
> CREATE UNIQUE INDEX shadow_index ON pg_shadow (usename)
> -------
> I couldn't create at pg_shadow_index as the pg prefix is reserved for
> system tables.
>
> This BROKE the database. At least I can't connect anymore with a:
> -------
> template1=# \c statements
> FATAL 1:  Index 'pg_shadow_name_index' does not exist
> Previous connection kept
> template1=#
> -------
> If I look at the error log I get :
> -------
> ERROR:  Illegal class name 'pg_shadow_index'
>         The 'pg_' name prefix is reserved for system catalogs
> ERROR:  Index 'pg_shadow_name_index' does not exist
> ERROR:  SearchSysCache: recursive use of cache 23
> ERROR:  SearchSysCache: recursive use of cache 23
> ERROR:  SearchSysCache: recursive use of cache 23
> ERROR:  SearchSysCache: recursive use of cache 23 <-- quite psql here
> FATAL 1:  Index 'pg_shadow_name_index' does not exist <-- restarted again
> FATAL 1:  Index 'pg_shadow_name_index' does not exist
> FATAL 1:  Index 'pg_shadow_name_index' does not exist
> -------
>
> What can I do??? I've got a non-trivial amount of data that I cannot afford
> to lose!! HELP!..

First, here is a patch which will prevent this from happening in the
future.  Do people want this held for 7.2 or applied now?  It disables
the creation of user indexes on system tables.

The user-defined indexes on system columns can not be made to work
easily.  Tom Lane pointed out to me in a phone call that code like:

    CatalogIndexInsert(irelations, Num_pg_class_indices, relrelation, reltup);

assumes it knows the number of indexes on each system table, and a
user-defined one would not be updated by any system catalog change that
did not go through the executor.

As far as recovery, I am not sure.  One issue is that pg_shadow is a
global table, not local to the database.  My guess is that the global
table is still fine, but the index is in the database where you created
the index.  You can't remove the file because pg_index thinks the index
is proper and exists.

I am kind of stumped.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: src/backend/catalog/index.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/catalog/index.c,v
retrieving revision 1.144
diff -c -r1.144 index.c
*** src/backend/catalog/index.c    2001/03/22 06:16:10    1.144
--- src/backend/catalog/index.c    2001/03/30 22:55:54
***************
*** 864,869 ****
--- 864,876 ----
          indexInfo->ii_NumKeyAttrs < 1)
          elog(ERROR, "must index at least one attribute");

+     if (heapRelationName && !allow_system_table_mods &&
+         IsSystemRelationName(heapRelationName) && IsNormalProcessingMode())
+     {
+         elog(ERROR, "You can not create indexes on system tables:  '%s'",
+              heapRelationName);
+     }
+
      /*
       * get heap relation oid and open the heap relation
       */