Fragged State in 7.0.2

Here's some fun new problems in Pgsql 7.0.2.

My nightly job failed last night because supposedly the tables already
existed. If you do a \d, you can see the tables. If you issue drop
table, look at the output below.

This happened with two important tables, which I create with the
following each night:

CREATE TABLE tbl_mail_arch_dates2 AS SELECT fld_mail_list,
fld_mail_year, fld_mail_month, count(*) FROM tbl_mail_archive GROUP BY
fld_mail_list, fld_mail_year, fld_mail_month;

BEGIN;
DELETE FROM tbl_mail_arch_dates2 WHERE fld_mail_year<1985;
DROP TABLE tbl_mail_arch_dates;
ALTER TABLE tbl_mail_arch_dates2 RENAME TO tbl_mail_arch_dates;
COMMIT;

create index idx_arch_date_list_year_mo on
tbl_mail_arch_dates(fld_mail_list,fld_mail_year,fld_mail_month);

create index idx_arch_date_list on tbl_mail_arch_dates(fld_mail_list);

So I wonder if the problem is because I am doing drop tables/rename
tables inside of a transaction.

Tim

db_geocrawler=# \d
List of relations
Name | Type | Owner
------------------------+----------+----------
monitored_lists | table | postgres
seq_geocrawler_users | sequence | postgres
seq_mail_chunk_no | sequence | postgres
seq_mail_lists | sequence | postgres
seq_mailid | sequence | postgres
seq_posted_messages | sequence | postgres
tbl_activity_log | table | postgres
tbl_geocrawler_users | table | postgres
tbl_mail_arch_dates | table | postgres
tbl_mail_arch_dates2 | table | postgres
tbl_mail_archive | table | postgres
tbl_mail_categories | table | postgres
tbl_mail_chunks | table | postgres
tbl_mail_lists | table | postgres
tbl_mail_subcategories | table | postgres
tbl_posted_messages | table | postgres
(16 rows)

db_geocrawler=# drop table tbl_mail_arch_dates2;
NOTICE: mdopen: couldn't open tbl_mail_arch_dates2: No such file or
directory
NOTICE: RelationIdBuildRelation: smgropen(tbl_mail_arch_dates2): No
such file or directory
NOTICE: mdopen: couldn't open tbl_mail_arch_dates2: No such file or
directory
DROP
db_geocrawler=# ERROR: Query was cancelled.

db_geocrawler=# \d
List of relations
Name | Type | Owner
------------------------+----------+----------
monitored_lists | table | postgres
seq_geocrawler_users | sequence | postgres
seq_mail_chunk_no | sequence | postgres
seq_mail_lists | sequence | postgres
seq_mailid | sequence | postgres
seq_posted_messages | sequence | postgres
tbl_activity_log | table | postgres
tbl_geocrawler_users | table | postgres
tbl_mail_arch_dates | table | postgres
tbl_mail_archive | table | postgres
tbl_mail_categories | table | postgres
tbl_mail_chunks | table | postgres
tbl_mail_lists | table | postgres
tbl_mail_subcategories | table | postgres
tbl_posted_messages | table | postgres
(15 rows)

db_geocrawler=# drop table tbl_mail_arch_dates;
NOTICE: mdopen: couldn't open idx_arch_date_list_year_mo: No such file
or directory
NOTICE: RelationIdBuildRelation: smgropen(idx_arch_date_list_year_mo):
No such file or directory
NOTICE: mdopen: couldn't open idx_arch_date_list: No such file or
directory
NOTICE: RelationIdBuildRelation: smgropen(idx_arch_date_list): No such
file or directory
DROP

--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723