Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Cc: Marcin Kowalski <kowalski(at)datrix(dot)co(dot)za>
Subject: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
Date: 2001-04-01 00:31:38
Message-ID: 200104010031.TAA18354@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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(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

pgsql-hackers by date

Next:From: Tom LaneDate: 2001-04-01 00:36:09
Subject: Re: Re: Changing the default value of an inherited column
Previous:From: Tom LaneDate: 2001-04-01 00:14:59
Subject: Re: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group