Re: Indexes vs indices

Hi All...

I've got a slight problem with pg_dump in Postgres v7.0.3, in basically
duplicates all the data that it extracts

I do a
testdatabase>CREATE TABLE bob (number int4,description text);
testdatabase>INSERT INTO TABLE bob VALUES (4453,'This is just a test of
pg_dump');

then

kowalski(at)dagoba > pg_dump -t bob testdatabase
\connect - kowalski
CREATE TABLE "bob" (
"number" int4,
"description" text
);
CREATE TABLE "bob" (
"number" int4,
"description" text
);
COPY "bob" FROM stdin;
4453 This is just a test of pg_dump
\.
COPY "bob" FROM stdin;
4453 This is just a test of pg_dump
\.

As you can see the records are duplicated. I discovered this when I tried
to migrate from 7.0.3 to 7.1 and found performance suddenly took a terrible
dive. Is there a patch for pg_dump ??

Thanks for any help
MarCin

Hi

I would have expected this problem to have been brought up long ago with
people doing database backups and restores. Anyway below are the details

I am running PostgreSQL v 7.0.3 (upgraded yesterday from 7.0.2) on SuSE 6.3
with 2.2.17(SMP) kernel with S/W raid patch.
The actual database is running on a 40 GIG Software RAID0 Ext2fs partition.
It has approximately 13million records in 9 tables with most of the data
residing in 2 tables(+- 6Mill Each). The machine is a Dual PII-350 with 256
meg of Ram. Each table has two indices, both on two fields.

Is it possible that one of the system tables has been corrupted and shows
multiple entries for the tables?? This makes me a bit worried about system
integrity. When I do a simple select * from tablename it works fine, what
does pg_dump do that I don't ???

Thanks in ADvance
MarCin

> Marcin Kowalski <kowalski(at)datrix(dot)co(dot)za> writes:
> > kowalski(at)dagoba > pg_dump -t bob testdatabase
> > \connect - kowalski
> > CREATE TABLE "bob" (
> > "number" int4,
> > "description" text
> > );
> > CREATE TABLE "bob" (
> > "number" int4,
> > "description" text
> > );
> > COPY "bob" FROM stdin;
> > 4453 This is just a test of pg_dump
> > \.
> > COPY "bob" FROM stdin;
> > 4453 This is just a test of pg_dump
> > \.
>
> Strange. I can't duplicate this (and neither can anyone else, or we'd
> have heard about it long since). What platform are you on? How did you
> build or obtain your executables?
>
> regards, tom lane

--
-----------------------------
Marcin Kowalski
Linux/Perl Developer
Datrix Solutions
Cel. 082-400-7603
***Open Source Kicks Ass***
-----------------------------

Marcin Kowalski <kowalski(at)datrix(dot)co(dot)za> writes:
> Is it possible that one of the system tables has been corrupted and shows
> multiple entries for the tables?

Come to think of it, this is a fairly likely behavior if you have
multiple entries in pg_shadow with the same usesysid.

regards, tom lane

Hi

Thanks for the help, in fact that is Exactly what is wrong. The pg_shadow
table has duplicated entries in it, I think I'm going to create a unique
index on it.
BTW I've migrated the entrie database to PG7.1RC1, running quite a big
search on the database basically involving a huge amount of selects.
Currently I'm curising at 1250 selects per second (simple select, no Joins)
from multiple tables with mutliple data, pretty fast I think. (But I still
have +- 10 Million to do :-) ).

ANyone have any ideas on how to improve performace, currently have indices
on key fields and am clustering (vacuum + vacuum analyze done)?? Are there
any command line parameters I can try to increase performance..??

Thanks in ADvance
MarCIn

Tom Lane wrote:

> Marcin Kowalski <kowalski(at)datrix(dot)co(dot)za> writes:
>> Is it possible that one of the system tables has been corrupted and shows
>> multiple entries for the tables?
>
> Come to think of it, this is a fairly likely behavior if you have
> multiple entries in pg_shadow with the same usesysid.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

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

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
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

> 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

Marcin Kowalski <kowalski(at)datrix(dot)co(dot)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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(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

> 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(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

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(at)gmx(dot)net http://yi.org/peter-e/

[Dept of swatting flies with sledgehammers]

Peter Eisentraut <peter_e(at)gmx(dot)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(at)gmx(dot)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(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

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