Thread: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
> 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 <pgman@candle.pha.pa.us> writes:
> 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.
> + if (heapRelationName && !allow_system_table_mods &&
> + IsSystemRelationName(heapRelationName) && IsNormalProcessingMode())
> + {
> + elog(ERROR, "You can not create indexes on system tables: '%s'",
> + heapRelationName);
> + }
> +
I think it would be a real good idea to put in this safeguard, but
I don't much like that error message. How about
elog(ERROR, "User-defined indexes on system catalogs are not supported");
regards, tom lane
Re: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > 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.
>
> > + if (heapRelationName && !allow_system_table_mods &&
> > + IsSystemRelationName(heapRelationName) && IsNormalProcessingMode())
> > + {
> > + elog(ERROR, "You can not create indexes on system tables: '%s'",
> > + heapRelationName);
> > + }
> > +
>
> I think it would be a real good idea to put in this safeguard, but
> I don't much like that error message. How about
>
> elog(ERROR, "User-defined indexes on system catalogs are not supported");
Change made to patch.
-- 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,
Pennsylvania19026
Re: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
From
Peter Eisentraut
Date:
Bruce Momjian writes: > + elog(ERROR, "You can not create indexes on system tables: %s'", > + heapRelationName); One of these days we should decide on a spelling of "indexes" vs "indices". -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Re: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
From
Bruce Momjian
Date:
> Bruce Momjian writes: > > > + elog(ERROR, "You can not create indexes on system tables: %s'", > > + heapRelationName); > > One of these days we should decide on a spelling of "indexes" vs > "indices". Yes. Added to TODO: * Decide on spelling of indexes/indices -- 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, Pennsylvania19026
[Dept of swatting flies with sledgehammers]
Peter Eisentraut <peter_e@gmx.net> writes:
> One of these days we should decide on a spelling of "indexes" vs
> "indices".
I'd vote for "indexes", first on the practical grounds that it's a more
sensible spelling, and secondly on the grounds that the Oxford English
Dictionary agrees. Its entry for the noun index has:
Index: PL indexes and indices. In current use the plural is indices in
senses 8, 9, and usually in other senses except 5, in which indexes is
usual.
1. The fore-finger. (Now chiefly Anat.)
2. A piece of wood, metal, or the like which serves as a pointer.
3. The hand of a clock, watch, or sundial. (Now rare)
4. That which serves to direct ... a guiding principle.
5. (a) A table of contents, preface, or prologue (Obs). (b) An
alphabetical list, placed (usually) at the end of a book, of the names,
subjects, etc. occurring in it, with indication of the places in which
they occur.
6. Spec. (short for Index librorum prohibitorum) The list of books which
Roman Catholics are forbidden to read.
7. A "hand" marker in printing. (Obs)
8. Math. (a) a number placed above and to the right of another quantity
to denote a power or root. (b) the integral part of a logarithm. (Obs)
9. In various sciences, a number or formula expressing some property of
the thing in question. (ex. Index of refraction)
(I've abbreviated the definitions other than sense 5b.)
I'd say that the use of "index" in database work clearly falls under
sense 5b, and so "indexes" is the usual plural according to the OED.
The habit of using "indices" in the Postgres documentation seems to go
back to the Berkeley days. Possibly the Berkeley boys were familiar
with sense 8 and/or 9 and so tended to use that plural.
regards, tom lane
> [Dept of swatting flies with sledgehammers] > > Peter Eisentraut <peter_e@gmx.net> writes: > > One of these days we should decide on a spelling of "indexes" vs > > "indices". > > I'd vote for "indexes", first on the practical grounds that it's a more > sensible spelling, and secondly on the grounds that the Oxford English > Dictionary agrees. Its entry for the noun index has: > I never liked indices. I like indexes. -- 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, Pennsylvania19026
Tom Lane wrote: > [Dept of swatting flies with sledgehammers] > I'd say that the use of "index" in database work clearly falls under > sense 5b, and so "indexes" is the usual plural according to the OED. As a volume of the OED is about the weight of a small sledgehammer, that fly is one dead puppy (to mix my metaphors). Although, sense 4 is also germane, as our index does serve to direct the query executor to the appropriate tuples, and could be considered to be the directing principle for performance enhancement <duck>...... But in reality, it doesn't matter. 'Indexes' is just fine. It's certainly a better plural than 'Vaxen' was in its time; although I am still inclined to use 'boxen' when referring to more than one computer. -- Lamar Owen WGCR Internet Radio Professor of English, Anchor Baptist Bible College --so I'm allowed to play with the language.... :-) 1 Peter 4:11