Missing tables in postgresql 7.2.4

Hi,

we recently discovered on our production database an a little bit bizarre
problem (after two years stable operations). Some tables are simply missing, or
sometimes the affected table(s) is/are there but not listed in pg_tables.

An example (I am looking for the table kog_blasthit_tables):

dev_db=# select version();
version
-------------------------------------------------------------------------
PostgreSQL 7.2.4 on sparc-sun-solaris2.8, compiled by GCC gcc (GCC) 3.1
(1 row)

dev_db=# \d kog_ <TAB EXPANSION>
kog_blasthits_template kog_hits_obj_2374_q411_db7
kog_hits_obj_1016_q344_db7 kog_hits_obj_2396_q833_db7
kog_hits_obj_1341_q475_db7 kog_hits_obj_2491_q505_db7
kog_hits_obj_1362_q412_db7 kog_hits_obj_2516_q900_db7
kog_hits_obj_1364_q413_db7 kog_hits_obj_2559_q78_db7
kog_hits_obj_1584_q570_db7 kog_hits_obj_822_q369_db7
kog_hits_obj_1604_q574_db7 kog_hits_obj_834_q371_db7
kog_hits_obj_1660_q608_db7 kog_hits_obj_846_q375_db7
kog_hits_obj_1725_q650_db7 kog_hits_obj_880_q339_db7
kog_hits_obj_1737_q605_db7 kog_info
kog_hits_obj_2186_q686_db7 kog_org

// No kog_blasthit_tables !!!

BUT it seems to be there:

dev_db=# select * FROM kog_blasthit_tables LIMIT 1;
id | object_id | query_set_id | db_set_id | num_of_query_sequences |
table_name | table_desc | number_of_entries |
blast_parameters | blast_prg | evalue_cutoff | owner
| project | created | status |
number_of_unique_entries | reciprocal_first_id | reciprocal_second_id
-----+-----------+--------------+-----------+------------------------+----------------------------+------------+-------------------+-------------------------------------------+-----------+---------------+----------+---------+-------------------------------+----------+--------------------------+---------------------+----------------------
398 | 1341 | 475 | 7 | 26187 |
kog_hits_obj_1341_q475_db7 | na | 0 | -a 1 -m 7 -T F -F
T -p blastx -e 0.100000 | kogblast | 0.1 | mbeckste | 23 |
2004-02-26 18:36:48.048881+01 | finished | 27426 |
-1 | -1
(1 row)

dev_db=#

// The table seems to be there but not in pg_tables

dev_db=# select * FROM pg_tables WHERE tablename='kog_blasthit_tables';
tablename | tableowner | hasindexes | hasrules | hastriggers
-----------+------------+------------+----------+-------------
(0 rows)

dev_db=# \d pg_ta <TAB EXPANSION>
// nothing but

dev_db=# select count(*) FROM pg_tables;
count
-------
875
(1 row)

The case which is even worth, is that the table is missing completely. We have
a catalog table that keeps track of generated tables, so we know that it has to
be there. The postmaster logs (with high debug level) show no DROP TABLE
between the time of the CREATE TABLE statement of the affected table and the
time where we noticed that it is missing.

I know that this is probably not enough information for a precise analysis. May
be someone can give me a hint how to further investigate this issue.

regards
Michael

--

------------------------------------------------------------------------------
Dipl.-Inform. Michael Beckstette Office: M3-129
AG-PI / Technische Fakultaet EMail:mbeckste(at)techfak(dot)uni-bielefeld(dot)de
Universitaet Bielefeld Fon: +49-521-106-2914
Postfach 100131 Fax: +49-521-106-6411
D-33501 BIELEFELD
Germany