From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Marcin Kowalski <kowalski@datrix.co.za>
Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
Date: Fri, 30 Mar 2001 20:14:50 -0500 (EST)
Message-ID: <200103310114.UAA13928@candle.pha.pa.us> (raw)
In-Reply-To: <99us31$cvk$1@news.tht.net>
> 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
Attachments:/bjm/diff (text/plain, 782B) download
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
*/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: pgman@candle.pha.pa.us, kowalski@datrix.co.za, pgsql-hackers@postgreSQL.org
Subject: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
In-Reply-To: <200103310114.UAA13928@candle.pha.pa.us>
References: <99us31$cvk$1@news.tht.net> <200103310114.UAA13928@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox