Re: table not shown - Mailing list pgsql-admin

Hi Duane,

It seems it is not:

mxl=# show search_path;

 search_path

--------------

 $user,public

(1 row)

mxl=# select current_schema(), current_user;

 current_schema | current_user

----------------+--------------

 public         | postgres

(1 row)

mxl=# select * from pg_class where relname = 'mxl_quar_process'; select * from mxl_quar_process limit 1;

 relname | relnamespace | reltype | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl

---------+--------------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------

(0 rows)

  quar_id   | customer_id | domain_id | user_id  | host_id |                                                      path                                                       | module  | attempts | state |            created

------------+-------------+-----------+----------+---------+-----------------------------------------------------------------------------------------------------------------+---------+----------+-------+-------------------------------

 2012787039 |     8022315 |   8022316 | 20018272 | 7953027 | /mxl/quarantine/p01m183.mxlogic.net/hmausa.com/20031212/b6/b6e39df3.7682.021.p01m183-8022316-20018272-0.zip.enc | keyword |        0 |     1 | 2004-01-09 23:49:44.056513-07

(1 row)

Also, when I do:

pg_dump -U postgres -f 615.dump

Password:

pg_dump: invalid adnum value 5 for table mxl_user_wbl

mxl=# \d mxl_user_wbl

                               Table "public.mxl_user_wbl"

 Column  |           Type           |                     Modifiers

---------+--------------------------+----------------------------------------------------

 user_id | integer                  | not null

 sender  | character varying(128)   | not null

 action  | integer                  |

 created | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone

Indexes: mxl_user_wbl_pkey primary key btree (user_wbl_id),

         mxl_user_wbl_uid_action_idx btree (user_id, "action")

Foreign Key constraints: mxl_user_wbl_uid_fkey FOREIGN KEY (user_id) REFERENCES mxl_user(user_id) ON UPDATE NO ACTION ON DELETE CASCADE

Triggers: mxl_user_wbl_u_trg

I know mxl_user_wbl is not same as mxl_quar_process, but this pg_dump error causes our

production database backup failed!

Please help!

Thank you!

-----Original Message-----
From: Duane Lee - EGOVX [mailto:DLee@mail.maricopa.gov]
Sent: Tuesday, June 15, 2004 9:22 AM
To: Lee Wu; Tom Lane
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] table not shown

Could this be a schema issue?

-----Original Message-----
From: Lee Wu [mailto:Lwu@mxlogic.com]
Sent: Tuesday, June 15, 2004 6:56 AM
To: Tom Lane
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] table not shown

Here is screen shot:

psql
Password:
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

mxl=# \d mxl_quar_process
Did not find any relation named "mxl_quar_process".
mxl=# \dt mxl_quar_process
No matching relations found.
mxl=# select * from pg_class where relname = 'mxl_quar_process';
 relname | relnamespace | reltype | relowner | relam | relfilenode |
relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex |
relisshared | relkind | relnatts | relchecks | reltriggers | relukeys |
relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl
---------+--------------+---------+----------+-------+-------------+----
------+-----------+---------------+---------------+-------------+-------
------+---------+----------+-----------+-------------+----------+-------
---+---------+------------+------------+-------------+----------------+-
-------
(0 rows)

mxl=# select * from mxl_quar_process limit 1;
  quar_id   | customer_id | domain_id | user_id  | host_id |
path                                                       | module  |
attempts | state |            created
------------+-------------+-----------+----------+---------+------------
------------------------------------------------------------------------
-----------------------------+---------+----------+-------+-------------
------------------
 2012787039 |     8022315 |   8022316 | 20018272 | 7953027 |
/mxl/quarantine/p01m183.mxlogic.net/hmausa.com/20031212/b6/b6e39df3.7682
.021.p01m183-8022316-20018272-0.zip.enc | keyword |        0 |     1 |
2004-01-09 23:49:44.056513-07
(1 row)

mxl=#

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, June 15, 2004 6:44 AM
To: Lee Wu
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] table not shown

"Lee Wu" <Lwu@mxlogic.com> writes:
> We have table is database which cannot be displayed by
> either \d, or \dt or pg_class.

> But we can select, DML on it.

You could not possibly be selecting from it if it's not in pg_class.
I expect this is pilot error on your part, but there's not enough info
here to guess just what.  Could you show exact examples of a successful
select, the other inquiries you tried, and exactly what you got from
them?

                        regards, tom lane

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