pg_dump potential bug

Поиск
Список
Период
Сортировка
Искать

pg_dump potential bug

От:
Marcin Kowalski <kowalski@datrix.co.za>
Дата:
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@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

Re: pg_dump potential bug

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Marcin Kowalski  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

Re: pg_dump potential bug

От:
Marcin Kowalski <kowalski@datrix.co.za>
Дата:
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  writes:
> > kowalski@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***
-----------------------------

Re: pg_dump potential bug

От:
Marcin Kowalski <kowalski@datrix.co.za>
Дата:
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  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
> 

Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP

От:
Marcin Kowalski <kowalski@datrix.co.za>
Дата:
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

Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!! HELP

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Marcin Kowalski  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
FAQ