Re: [GENERAL] drop/rename table and transactions

Lincoln Yeoh wrote:

> >If this happens, all my database connections get this error when trying
> >to access the database and I need to restart postgresql. The problem
> >causing this error needs to be something like this:
> >
> > create table table2 (col1 text);
> > insert into table2 (col1) values ('some data');
> > begin work;
> > drop table table1;
> > alter table table2 rename to table1;
> > commit;
>
> I did what I did coz I was curious. But creating/Dropping tables in a
> transaction does not appear to be a "good thing", and that's not just for
> PostgreSQL. I believe doing data definition stuff in transactions is not
> recommended.
>
> Is it possible to achieve your goals by using things like
> "delete * from table1 where id!=stuffIwant" instead of dropping it?
>
> Cheerio,
>
> Link.

This is one of the few areas that I disagree with the development trend in
PostgreSQL. Every release contains different bugs related to DDL statements in
transactions. The developers appear to want to make them work (i.e., have the
ability to rollback a DROP TABLE, ALTER TABLE ADD COLUMN, etc.). This, in my
opinion, goes far above and beyond the call of duty for a RDBMS. Oracle issues
an implicit COMMIT whenever a DDL statement is found. In fact, one could argue
that those who are porting Oracle apps to PostgreSQL would assume,
incorrectly, than a DROP TABLE in a transaction committed any work done
previously.

I personally believe that PostgreSQL should do the same as Oracle and greatly
simplify the implementation of DDL statements in the backed by issuing an
implicit COMMIT....

Just my opinion, though

Mike Mascari