pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
9+ messages / 4 participants
[nested] [flat]
* Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
@ 2001-03-29 08:35 Marcin Kowalski <kowalski@datrix.co.za>
0 siblings, 3 replies; 9+ messages in thread
From: Marcin Kowalski @ 2001-03-29 08:35 UTC (permalink / raw)
To: pgsql-admin@postgresql.org.pgsql-general
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!..
Regards
MArCin - Thanks
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
@ 2001-03-30 15:52 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Marcin Kowalski <kowalski@datrix.co.za>
2 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2001-03-30 15:52 UTC (permalink / raw)
To: Marcin Kowalski <kowalski@datrix.co.za>; +Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
I can confirm with current sources:
test=> CREATE UNIQUE INDEX shadow_index ON pg_shadow (usename);
CREATE
test=> select * from pg_shadow;
ERROR: Index 'pg_shadow_sysid_index' does not exist
test=> \q
$ psql test
psql: FATAL 1: Index 'pg_shadow_name_index' does not exist
$
gdb shows that the check in heap_create() is working because the
index name does not begin with pg_, just the base table:
Breakpoint 1, heap_create (relname=0x838d1d0 "shadow_index",
tupDesc=0x83915e4, istemp=0 '\000', storage_create=0 '\000',
allow_system_table_mods=0) at heap.c:183
183 bool nailme = false;
First, should we allow user-specified indexes on system tables, and if
so, why does this error happen?
Notice the user wanted an index named shadow_index, but the error
mentioned is pg_shadow_name_index.
> 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!..
>
> Regards
> MArCin - Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
@ 2001-03-31 01:14 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Marcin Kowalski <kowalski@datrix.co.za>
2 siblings, 2 replies; 9+ messages in thread
From: Bruce Momjian @ 2001-03-31 01:14 UTC (permalink / raw)
To: Marcin Kowalski <kowalski@datrix.co.za>; +Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
> 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
*/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
@ 2001-03-31 23:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marcin Kowalski <kowalski@datrix.co.za>
2 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2001-03-31 23:57 UTC (permalink / raw)
To: Marcin Kowalski <kowalski@datrix.co.za>; +Cc: pgsql-admin@postgresql.org
Marcin Kowalski <kowalski@datrix.co.za> writes:
> DON'T DO THIS!!!
> -------
> CREATE UNIQUE INDEX shadow_index ON pg_shadow (usename)
> -------
Indeed, trying to create nonstandard indexes on system catalogs is a BAD
idea. There probably ought to be a check to prevent you from trying.
> What can I do??? I've got a non-trivial amount of data that I cannot afford
> to lose!! HELP!..
I think you'd be OK if you could drop the index and then do
update pg_class set relhasindex = 'f' where relname = 'pg_shadow';
The trick is to be able to do that when the database is busted.
I think you may be able to do this if you restart in "ignore system
indexes" mode (use "-o -P" while starting postmaster). Worth a try
anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
@ 2001-04-01 00:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2001-04-01 00:14 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Marcin Kowalski <kowalski@datrix.co.za>; PostgreSQL-development <pgsql-hackers@postgresql.org>
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> test=> CREATE UNIQUE INDEX shadow_index ON pg_shadow (usename);
> CREATE
> test=> select * from pg_shadow;
> ERROR: Index 'pg_shadow_sysid_index' does not exist
> test=> \q
> $ psql test
> psql: FATAL 1: Index 'pg_shadow_name_index' does not exist
> $
> Notice the user wanted an index named shadow_index, but the error
> mentioned is pg_shadow_name_index.
What's failing is catcache lookups on pg_shadow. The catcache has table
entries that claim that there are indexes on pg_shadow(usename) and
pg_shadow(usesysid). The system would not work at all, except that
catcache's use of these indexes is defeated by sanity-check code that
notices that relhasindex is FALSE for pg_shadow (line 880 of
catcache.c).
As soon as you create an index on pg_shadow, relhasindex becomes TRUE
and catcache.c starts trying to use these nonexistent indexes for
routine operations like ACL permissions checks. So, nothing works
anymore.
We ought to create those indexes someday ;-)
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
@ 2001-04-01 00:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2001-04-01 00:53 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Marcin Kowalski <kowalski@datrix.co.za>; PostgreSQL-development <pgsql-hackers@postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
@ 2001-04-01 00:58 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2001-04-01 00:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marcin Kowalski <kowalski@datrix.co.za>; PostgreSQL-development <pgsql-hackers@postgresql.org>
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
@ 2001-04-02 15:07 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2001-04-02 15:07 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP
@ 2001-04-02 15:15 Peter Eisentraut <peter_e@gmx.net>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Peter Eisentraut @ 2001-04-02 15:15 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2001-04-02 15:15 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-29 08:35 Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP Marcin Kowalski <kowalski@datrix.co.za>
2001-03-30 15:52 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-04-01 00:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-31 01:14 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-04-01 00:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-01 00:58 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-04-02 15:15 ` Peter Eisentraut <peter_e@gmx.net>
2001-04-02 15:07 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-31 23:57 ` Tom Lane <tgl@sss.pgh.pa.us>
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