Thread: 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 | 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
> From: Tim Perdue <tperdue@valinux.com> > > 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. > Unfortunately, PostgreSQL cannot rollback transactions with DDL statements in them. I suspect that what happened was that the underlying file was unlinked, but the entry from pg_class wasn't marked deleted, because the backend performing the DROP TABLE crashed before the pg_class delete could be committed. Unlike eveything everyone else has told you about transactions, as of 7.0.2, I wouldn't run DDL statements in them, only DML statements. Rolling back DDL statements properly in a MVCC transaction environment is very difficult, as you can imagine. IIRC Oracle cheats, Informix and DEC Rdb lock the DDL target until transaction commit, etc. If the PostgreSQL team implements their stated goal in this area, it will be far superior to its commercial counterparts. Hope that helps, Mike Mascari
"Mike Mascari" <mascarm@mascari.com> writes:
> Rolling back DDL statements properly
> in a MVCC transaction environment is very difficult, as
> you can imagine. IIRC Oracle cheats, Informix and DEC Rdb
> lock the DDL target until transaction commit, etc. If
> the PostgreSQL team implements their stated goal in this
> area, it will be far superior to its commercial counterparts.
AFAIK we intend to keep the current behavior of exclusively
locking any table you try to drop or modify. So it'll be
pretty much like the Informix/RDB behavior.
But yes, at the moment DROP or RENAME inside a transaction is
pretty risky (and 7.0 tells you so, with an annoying NOTICE).
regards, tom lane
It doesn't suprise me that it doesn't work, but I am surprised to get into a half-baked state if an abort does happen for some reason. NOTICE: Caution: DROP TABLE cannot be rolled back, so don't abort now Probably what should happen is it should error out if you try to do things like this in a transaction, or autocommit for you no matter what. Whatever it takes to not be in a half-baked state. Tim Tom Lane wrote: > > "Mike Mascari" <mascarm@mascari.com> writes: > > Rolling back DDL statements properly > > in a MVCC transaction environment is very difficult, as > > you can imagine. IIRC Oracle cheats, Informix and DEC Rdb > > lock the DDL target until transaction commit, etc. If > > the PostgreSQL team implements their stated goal in this > > area, it will be far superior to its commercial counterparts. > > AFAIK we intend to keep the current behavior of exclusively > locking any table you try to drop or modify. So it'll be > pretty much like the Informix/RDB behavior. > > But yes, at the moment DROP or RENAME inside a transaction is > pretty risky (and 7.0 tells you so, with an annoying NOTICE). > > regards, tom lane -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems 408-542-5723