Thread: missing data

Hi,

I'm running postgresql 6.5 on Red Hat Linux 6.2 and have run into a
situation where I am able to open a database connection using psql yet
there is no trace of any tables in the database even though all the data
files are present when I browse the database directory.  This appears to
have occured after running out of disk space.  I've freed up plenty of
space without affecting any files related to the postgresql installation,
yet the problem persists.

Any ideas that would point me in the direction toward resolving this
situation would greatly be appreciated!!

Thanks,
Dave

Dave Perkins writes:

> I'm running postgresql 6.5 on Red Hat Linux 6.2 and have run into a
> situation where I am able to open a database connection using psql yet
> there is no trace of any tables in the database even though all the data
> files are present when I browse the database directory.

Depending on your definition of "no trace", this might work:  Try

select relname, relowner from pg_class;

If this shows table names that sound familiar, then it is likely that the
user indicated by "relowner" disappeared.  Look into the table pg_shadow
to make sure some user's usesysid column matches relowner.  (It's safe to
change these with update commands.)

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

>Depending on your definition of "no trace", this might work:  Try
>
>select relname, relowner from pg_class;
>
>If this shows table names that sound familiar, then it is likely that the
>user indicated by "relowner" disappeared.  Look into the table pg_shadow
>to make sure some user's usesysid column matches relowner.  (It's safe to
>change these with update commands.)

Thanks for the prompt reply.  I did find tables when running the SELECT you
suggested above and also found an entry in pg_shadow that matches relowner
for those tables.

Any thoughts on where this leaves me?

Thanks,
Dave

I've discovered that the database in question was lacking an entry in
pg_database.  I took the liberty of inserting a record with what I believe
to be the appropriate info.  The problem still persists, however.  The
other thing I am noticing is that when I run the  \dt comand I get the
"Couldn't find any tables!" error message, but when I run a select * query
I get an appropriate listing of fields for the table, but no records.  I
sense that I'm close, but yet so far from restoring tranquility.

Thanks,
Dave

Dave Perkins writes:

> >Depending on your definition of "no trace", this might work:  Try
> >
> >select relname, relowner from pg_class;
> >
> >If this shows table names that sound familiar, then it is likely that the
> >user indicated by "relowner" disappeared.  Look into the table pg_shadow
> >to make sure some user's usesysid column matches relowner.  (It's safe to
> >change these with update commands.)
>
> Thanks for the prompt reply.  I did find tables when running the SELECT you
> suggested above and also found an entry in pg_shadow that matches relowner
> for those tables.
>
> Any thoughts on where this leaves me?

Either you update relowner to match an existing user, or you update an
existing user's usesysid to match relowner.  Note that the first option is
more labor if you also have functions and other objects that seem
"missing".

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Dave Perkins <drp@shore.net> writes:
> Thanks for the prompt reply.  I did find tables when running the SELECT you
> suggested above and also found an entry in pg_shadow that matches relowner
> for those tables.

If you see entries in pg_class then I'm confused about what your problem
really is.  Please define "missing data" exactly: what commands have you
issued, what result did you get, what result did you expect to get?
Which tables are affected?

            regards, tom lane

At 03:07 PM 3/29/01 -0500, you wrote:
>Dave Perkins <drp@shore.net> writes:
> > Thanks for the prompt reply.  I did find tables when running the SELECT
> you
> > suggested above and also found an entry in pg_shadow that matches relowner
> > for those tables.
>
>If you see entries in pg_class then I'm confused about what your problem
>really is.  Please define "missing data" exactly: what commands have you
>issued, what result did you get, what result did you expect to get?
>Which tables are affected?
>
>                         regards, tom lane

Thanks for your time and patience . . . The only entries I see in the
pg_class table are relnames with the 'pg_' prefix.  They all have a
relowner (40) that matches the only record listed in the pg_shadow
table.  The fact that the database in question does not have a
corresponding entry in the pg_database seems to be most revealing if only I
understood what it is revealing . . .  The one other piece of evidence I
have is the "Couldn't find any tables!" error message when executing the
\dt command, yet when I run a select * for a relation that use to exist, it
gives an acurate listing of field names, but no records (ie. missing data).

Thanks again,
Dave

Dave Perkins <drp@shore.net> writes:
> Thanks for your time and patience . . . The only entries I see in the
> pg_class table are relnames with the 'pg_' prefix.  They all have a
> relowner (40) that matches the only record listed in the pg_shadow
> table.  The fact that the database in question does not have a
> corresponding entry in the pg_database seems to be most revealing if only I
> understood what it is revealing . . .  The one other piece of evidence I
> have is the "Couldn't find any tables!" error message when executing the
> \dt command, yet when I run a select * for a relation that use to exist, it
> gives an acurate listing of field names, but no records (ie. missing data).

The "couldn't find any tables" complaint makes sense if pg_class isn't
showing any entries for user-defined tables.  I don't quite understand
how it's letting you do a select from a table that's not visible in
pg_class, however.

Is it possible that you've executed more than 4 billion transactions
against this database?  If so you might be suffering from ye infamous
XID wraparound problem.  Look at pg_log in the $PGDATA directory; if
it's a gigabyte or so in size then this might have happened.

            regards, tom lane