Fragged State in 7.0.2 - Mailing list pgsql-hackers

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    | postgresseq_geocrawler_users   |
sequence| postgresseq_mail_chunk_no      | sequence | postgresseq_mail_lists         | sequence | postgresseq_mailid
        | sequence | postgresseq_posted_messages    | sequence | postgrestbl_activity_log       | table    |
postgrestbl_geocrawler_users  | table    | postgrestbl_mail_arch_dates    | table    | postgrestbl_mail_arch_dates2   |
table   | postgrestbl_mail_archive       | table    | postgrestbl_mail_categories    | table    |
postgrestbl_mail_chunks       | table    | postgrestbl_mail_lists         | table    | postgrestbl_mail_subcategories |
table   | postgrestbl_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    | postgresseq_geocrawler_users   |
sequence| postgresseq_mail_chunk_no      | sequence | postgresseq_mail_lists         | sequence | postgresseq_mailid
        | sequence | postgresseq_posted_messages    | sequence | postgrestbl_activity_log       | table    |
postgrestbl_geocrawler_users  | table    | postgrestbl_mail_arch_dates    | table    | postgrestbl_mail_archive       |
table   | postgrestbl_mail_categories    | table    | postgrestbl_mail_chunks        | table    |
postgrestbl_mail_lists        | table    | postgrestbl_mail_subcategories | table    | postgrestbl_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