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

Patch applied, with wording modifications by Tom Lane.

> > 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
>        */


--  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,
Pennsylvania19026