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: Marcin Kowalski <kowalski(at)datrix(dot)co(dot)za>
Cc: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
Date: 2001-03-31 01:14:50
Message-ID: 200103310114.UAA13928@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-bugspgsql-generalpgsql-hackerspgsql-sql
> 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

In response to

Responses

pgsql-hackers by date

Next:From: Hiroshi InoueDate: 2001-03-31 01:15:59
Subject: RE: AW: Re: [SQL] possible row locking bug in 7.0.3 & 7.1
Previous:From: Bruce MomjianDate: 2001-03-31 01:07:13
Subject: Updated TODO.detail

pgsql-sql by date

Next:From: Tom LaneDate: 2001-03-31 05:33:10
Subject: Re: Possible 7.1RC1 bug
Previous:From: Tom LaneDate: 2001-03-31 00:01:27
Subject: Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1

pgsql-admin by date

Next:From: Tom LaneDate: 2001-03-31 05:17:12
Subject: Re: Backing up large objects
Previous:From: August ZajoncDate: 2001-03-31 00:17:07
Subject: Recommended VACUM timing...

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-03-31 04:35:31
Subject: Re: BUG: Each UNION | EXCEPT | INTERSECT query must have the same number of columns.
Previous:From: IGB.NETDate: 2001-03-30 20:51:20
Subject:

pgsql-general by date

Next:From: Tom LaneDate: 2001-03-31 01:32:01
Subject: Re: Re[2]: Pgsql-7.1RC1: SET SEED = <something>
Previous:From: will trillichDate: 2001-03-31 00:49:51
Subject: Re: Re: function to operate on same fields, different records?

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