I have one database owned by "user1" which as of 4 days ago the superuser,
pgsql, can't see any tables.

I noticed I had pg_dumpalls from 4 days ago.. stuck.. upon research I
discovered that if I login as the superuser to the problem database that it
can not see any of the tables owned by the regular user. The superuser is
able to see system tables with \dS, but none of the regular ones with \d

If I login as 'user1' all the tables are there.

I tried "grant all on pgsql to database <mydb>", but that did not help.
Also tried to do a grant for particular table, but got error that it was not
found.

Tried a pg_dump as the database owner, but it didn't work.

Basically I have this database that only the DB owner can use.. and the
postgresql superuser can't see any tables and it is freezing the pg_dumpall
process.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Francisco Reyes <lists@stringsutils.com> writes:
> I have one database owned by "user1" which as of 4 days ago the superuser,
> pgsql, can't see any tables.

Transaction ID wraparound ... I take it this is not PG 8.1?

As long as it's at least 7.4, vacuuming the system catalogs should help.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane writes:

> Francisco Reyes <lists@stringsutils.com> writes:
>> I have one database owned by "user1" which as of 4 days ago the superuser,
>> pgsql, can't see any tables.


After furhter researching found that I could not see the tables because I
have them in diferent schemas.. and the superuser didn't have them in the
path.

However the pg_dumpall is still freezing in that database.

> Transaction ID wraparound ... I take it this is not PG 8.1?

8.1.3 Compiled from ports in FreeBSD 6.1 Stable.
select version();
version
----
PostgreSQL 8.1.3 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4
[FreeBSD] 20050518


> As long as it's at least 7.4, vacuuming the system catalogs should help.

vacuum the database in question?
From that database tried vacuum, vacuum analyze and vacuum full. Tried both
as superuser and as the user that owns the database.

Also tried running:
vacuumdb -azv

So the problem is only doing the backup. I am able to see the tables by
doing \d <shema>.*

The database in question is fairly small so if I could somehow dow a backup
I could drop it and reload it.

Trying to pg_dump as superuser or as the database owner, freezes.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

This may sound a bit weird, but have you tried logging in as user1 and
then granting the permission's to the superuser?


Simon Kelly
Java Developer
Information Systems Development
Information Technology Shared Services
Ministry of Health
DDI:
Mobile:

http://www.moh.govt.nz
mailto:Simon_Kelly@moh.govt.nz



pgsql-general-owner@postgresql.org wrote on 12/09/2006 02:02:12 p.m.:

> I have one database owned by "user1" which as of 4 days ago the
superuser,
> pgsql, can't see any tables.
>
> I noticed I had pg_dumpalls from 4 days ago.. stuck.. upon research I
> discovered that if I login as the superuser to the problem database that
it
> can not see any of the tables owned by the regular user. The superuser
is
> able to see system tables with \dS, but none of the regular ones with \d
>
> If I login as 'user1' all the tables are there.
>
> I tried "grant all on pgsql to database <mydb>", but that did not help.
> Also tried to do a grant for particular table, but got error that it was
not
> found.
>
> Tried a pg_dump as the database owner, but it didn't work.
>
> Basically I have this database that only the DB owner can use.. and the
> postgresql superuser can't see any tables and it is freezing the
pg_dumpall
> process.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq


************************************************** **************************
Statement of confidentiality: This e-mail message and any accompanying
attachments may contain information that is IN-CONFIDENCE and subject to
legal privilege.
If you are not the intended recipient, do not read, use, disseminate,
distribute or copy this message or attachments.
If you have received this message in error, please notify the sender
immediately and delete this message.
************************************************** **************************

************************************************** ***********************************
This e-mail message has been scanned for Viruses and Content and cleared
by the Ministry of Health's Content and Virus Filtering Gateway
************************************************** ***********************************

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Francisco Reyes <lists@stringsutils.com> writes:
> Trying to pg_dump as superuser or as the database owner, freezes.

Define "freezes". What happens exactly --- is the pg_dump or its
backend consuming CPU, or just sitting? What do ps and pg_stat_activity
and pg_locks show that it's doing?

(I'm speculating in particular that someone is holding an exclusive lock
on one of the tables to be dumped --- if so pg_locks would tell the tale.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Tom Lane writes:

> Define "freezes". What happens exactly


doing pg_dump <db> |tee <outfile>
Shows nothing. This database is not very big so I would expect it to be done
quickly. I tried a couple of other databases and they did the pg_dump
without problems.

>is the pg_dump or its backend consuming CPU, or just sitting?

At 90% of my CPU.

> What do ps and pg_stat_activity
> and pg_locks show that it's doing?

To make sure it was not a locking issue I did pg_ctl restart.. checked that
there were no locks or pretty much anything going on.. and then tried again.

> (I'm speculating in particular that someone is holding an exclusive lock
> on one of the tables to be dumped --- if so pg_locks would tell the tale.)

Doing it with a freshly restarted postgresql.

I have loggin set log_min_messages = info and log_statement = 'all'.
Right after I start the pg_dump there is a flury of activity, which I am
putting at http://public.natserv.net/pg_dump_log.txt, but very quickly it
stops producing any output to the log.

I left it for about 10 minutes and nothing was showing to the log.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Francisco Reyes <lists@stringsutils.com> writes:
> Tom Lane writes:
>> is the pg_dump or its backend consuming CPU, or just sitting?

> At 90% of my CPU.

The pg_dump process, or the backend?

> I have loggin set log_min_messages = info and log_statement = 'all'.
> Right after I start the pg_dump there is a flury of activity, which I am
> putting at http://public.natserv.net/pg_dump_log.txt, but very quickly it
> stops producing any output to the log.

The last query shown is

SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2

so apparently something is fishy about the dependency data. Can you
execute this query by hand and get results?

It could be that pg_depend is corrupted in a way that locks up the
backend trying to read it, or it could be that pg_dump is getting
confused and going into a loop trying to process the data. I can't
tell from this description.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane writes:

> Francisco Reyes <lists@stringsutils.com> writes:
>> Tom Lane writes:
>>> is the pg_dump or its backend consuming CPU, or just sitting?
>
>> At 90% of my CPU.
> The pg_dump process, or the backend?

Backend.
pgsql 60769 47.8 1.3 17636 4888 ?? R 11:34AM 761:15.92 postmaster:
pgsql pablar [local] SELECT (postgres)

> SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2
> so apparently something is fishy about the dependency data. Can you
> execute this query by hand and get results?

Nothing happens when I try to run the query.

> It could be that pg_depend is corrupted in a way that locks up the
> backend trying to read it, or it could be that pg_dump is getting
> confused and going into a loop trying to process the data. I can't
> tell from this description.

What additional info can I provide?
Any additional troubleshooting I can try?
This one DB is preventing me from doing a pg_dumpall.

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Francisco Reyes <lists@stringsutils.com> writes:
> Tom Lane writes:
>> SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2
>> so apparently something is fishy about the dependency data. Can you
>> execute this query by hand and get results?

> Nothing happens when I try to run the query.

So pg_dump seems off the hook. Can you run the query, attach to the
backend with gdb, and see what it's doing?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane writes:

>> Nothing happens when I try to run the query.
>
> So pg_dump seems off the hook. Can you run the query, attach to the
> backend with gdb, and see what it's doing?

Tried to set level debug5 to see if any extra output appeared in log.
Nothing.

Going to read up on gdb.. tried ktrace, but strangely enough got no
output.. Tried several other processes to make sure I had the right
syntax.. and every other process I tried produced lots of data with ktrace.
Going to try the FreeBSD lists to see why and when a process would not
show up in ktrace. Specially one using 90% cpu.

---------------------------(end of broadcast)---------------------------
TIP 1: 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