Thread: pg_dump potential bug

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

Marcin Kowalski <kowalski@datrix.co.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

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@datrix.co.za> 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***
-----------------------------

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@datrix.co.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

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