Thread: table not shown

table not shown

From
"Lee Wu"
Date:
Hi all,

We have table is database which cannot be displayed by
either \d, or \dt or pg_class.

But we can select, DML on it.
Also relfilenode is in our base/dboid directory and timestamp does
change.

Please help!

Thanks,

Re: table not shown

From
"Lee Wu"
Date:
PG version is 7.3.2 and OS is Red Hat Linux release 7.3 (Valhalla)

Thanks,

-----Original Message-----
From: Lee Wu
Sent: Monday, June 14, 2004 3:58 PM
To: pgsql-admin@postgresql.org
Subject: table not shown

Hi all,

We have table is database which cannot be displayed by
either \d, or \dt or pg_class.

But we can select, DML on it.
Also relfilenode is in our base/dboid directory and timestamp does
change.

Please help!

Thanks,

Re: table not shown

From
Tom Lane
Date:
"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

Re: table not shown

From
"Lee Wu"
Date:
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

Re: table not shown

From
Duane Lee - EGOVX
Date:

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

Re: table not shown

From
"Lee Wu"
Date:

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

Re: table not shown

From
"Lee Wu"
Date:

Thank you.

 

But this is not my case:

 

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

 relname

---------

(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)

 

mxl=#

 

-----Original Message-----
From: banghe [mailto:banghe@baileylink.net]
Sent: Tuesday, June 15, 2004 10:04 AM
To: pgsql-admin@postgresql.org
Cc: Duane Lee - EGOVX; Lee Wu; Tom Lane
Subject: Re: [ADMIN] table not shown

 

I am using the lower version of postgres. I had some similar experience with my lower version of postgres (v6.5) and have solved the issures.
However, I am not sure if the method I used could work, so just tips for you to try.

You may check the script of your ori. definition of the table, pay attention to the case of  table name spelling, and use the quotations.
e.g. your table name was written in your definition script as "
Mxl_Quar_Process",

use sql commmand:
sql>  \d "Mxl_Quar_Process";

Sometimes the reserved words may also cause such problems.  In this case, I doubt the word "process" may be one of  reserved words, so using 
quotations may help.

Bnaghe

Duane Lee - EGOVX wrote:

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

Re: table not shown

From
Robert Treat
Date:
do a vacuum full on template1 and see if it reappears.

Robert Treat

On Tue, 2004-06-15 at 13:10, Lee Wu wrote:
> 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 <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 <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
>

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: table not shown

From
Tom Lane
Date:
"Lee Wu" <Lwu@mxlogic.com> writes:
> Here is screen shot:

> mxl=# select * from pg_class where relname = 'mxl_quar_process';
> [ no rows ]

> mxl=# select * from mxl_quar_process limit 1;
> [ data ]

That's just plain bizarre.

I'm wondering about corruption of the indexes on pg_class --- though I'd
have expected the system to use an indexscan to look up mxl_quar_process
for the second select, so it's not obvious why the first search would
fail and the second one not.

Your later report about "invalid adnum value 5 for table mxl_user_wbl"
also sounds like it could be due to index corruption.

I'd try doing a REINDEX DATABASE to see if that fixes it.  (Read the
REINDEX man page fully before you start.)

One other thought --- it doesn't change anything if you do
  select * from pg_catalog.pg_class where relname = 'mxl_quar_process';
does it?

            regards, tom lane

Re: table not shown

From
"Lee Wu"
Date:
Thanks Tom!

Here is result:
mxl=# select * from pg_catalog.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=#

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

"Lee Wu" <Lwu@mxlogic.com> writes:
> Here is screen shot:

> mxl=# select * from pg_class where relname = 'mxl_quar_process';
> [ no rows ]

> mxl=# select * from mxl_quar_process limit 1;
> [ data ]

That's just plain bizarre.

I'm wondering about corruption of the indexes on pg_class --- though I'd
have expected the system to use an indexscan to look up mxl_quar_process
for the second select, so it's not obvious why the first search would
fail and the second one not.

Your later report about "invalid adnum value 5 for table mxl_user_wbl"
also sounds like it could be due to index corruption.

I'd try doing a REINDEX DATABASE to see if that fixes it.  (Read the
REINDEX man page fully before you start.)

One other thought --- it doesn't change anything if you do
  select * from pg_catalog.pg_class where relname = 'mxl_quar_process';
does it?

            regards, tom lane

Re: table not shown

From
Tom Lane
Date:
"Lee Wu" <Lwu@mxlogic.com> writes:
> Here is result:
> mxl=# select * from pg_catalog.pg_class where relname =
> 'mxl_quar_process';
> [ still no rows ]

Okay.  I was wondering about bizarre ideas like a non-system table named
pg_class, but that seems ruled out now.  I think you're down to the
REINDEX.  Good luck!

            regards, tom lane

Re: table not shown

From
"Lee Wu"
Date:
I think data dictionary got corrupted:

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

mxl=# select * from mxl_user_wbl where 1=2;
 user_wbl_id | user_id | sender | action | created
-------------+---------+--------+--------+---------
(0 rows)

Note: user_wbl_id is our primary key as from the scripts:
"user_wbl_id" integer DEFAULT nextval('mxl_quar_id_seq'::text) PRIMARY
KEY,

From this database's pg_attribute:
select a.attname from pg_attribute a, pg_class b
where b.relname = 'mxl_user_wbl' and a.attrelid = b.oid;
attname
----------
 tableoid
 cmax
 xmax
 cmin
 xmin
 oid
 ctid
 user_id
 sender
 action
 created
(11 rows)

Tom, I will reindex database to see what happens when time permits.

Thanks,


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

"Lee Wu" <Lwu@mxlogic.com> writes:
> Here is result:
> mxl=# select * from pg_catalog.pg_class where relname =
> 'mxl_quar_process';
> [ still no rows ]

Okay.  I was wondering about bizarre ideas like a non-system table named
pg_class, but that seems ruled out now.  I think you're down to the
REINDEX.  Good luck!

            regards, tom lane

Re: table not shown

From
"Lee Wu"
Date:
Hi,

Just wondering if we can bring up PG after we stop it and
reindex database in the case DD corrupted?

That is our worry.

Thanks,

-----Original Message-----
From: Lee Wu
Sent: Tuesday, June 15, 2004 2:25 PM
To: 'Tom Lane'
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] table not shown

I think data dictionary got corrupted:

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

mxl=# select * from mxl_user_wbl where 1=2;
 user_wbl_id | user_id | sender | action | created
-------------+---------+--------+--------+---------
(0 rows)

Note: user_wbl_id is our primary key as from the scripts:
"user_wbl_id" integer DEFAULT nextval('mxl_quar_id_seq'::text) PRIMARY
KEY,

From this database's pg_attribute:
select a.attname from pg_attribute a, pg_class b
where b.relname = 'mxl_user_wbl' and a.attrelid = b.oid;
attname
----------
 tableoid
 cmax
 xmax
 cmin
 xmin
 oid
 ctid
 user_id
 sender
 action
 created
(11 rows)

Tom, I will reindex database to see what happens when time permits.

Thanks,


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

"Lee Wu" <Lwu@mxlogic.com> writes:
> Here is result:
> mxl=# select * from pg_catalog.pg_class where relname =
> 'mxl_quar_process';
> [ still no rows ]

Okay.  I was wondering about bizarre ideas like a non-system table named
pg_class, but that seems ruled out now.  I think you're down to the
REINDEX.  Good luck!

            regards, tom lane