
Can I get a comment on this patch.  Hold for 7.2 or apply?

initdb works with the patch.  People who create indexes on global tables
get a failed database, while people who create indexes on non-global
system tables get unreliable indexes.  This prevents such index
creation.

I don't know how many people are creating their own system indexes.


> > 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, Pennsylvania 19026

Sat, 31 Mar 2001 19:31:38 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
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@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, Pennsylvania 19026

Mon, 2 Apr 2001 10:34:41 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!!
	HELP