
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

Wed, 30 Aug 2000 12:37:23 -0500
Tim Perdue <tperdue@valinux.com>
Fragged State in 7.0.2