Обсуждение: 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,

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,

"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

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

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

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

"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

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

"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

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

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