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

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026