Thread

1. drop/rename table and transactions

   Jaco de Groot <jaco@gospelsjop.com> — 1999-11-25T20:36:01Z

   Hi,
   
   Every now and then I get the following error:
   
     cannot write block 0 of tablename [username] blind
   
   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've been playing with some statements to repeat the error, but I
   haven't
   been able to succesfully repeat the error (only once, but doing the
   same didn't cause the error again). Maybe it has something to do with
   using multiple connections.
   
   Trying some statements I found an other error, using these statements:
   
     create table table1 (col1 text);
     begin work;
     drop table table1;
     alter table table2 rename to table1;
     create table table2 (col1 text);
     commit;
     select * from table1;
   
   Caused:
   
     couldn't open table1: No such file or directory
   
   I'm using postgresql-6.5.2-1.i386.rpm.
   
   Is the posgresql development team aware of these errors and will
   they be fixed?
   
   Gr. Jaco
   
   

2. Re: [GENERAL] drop/rename table and transactions

   Mike Mascari <mascarm@mascari.com> — 1999-11-26T00:25:11Z

   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
   
   
   
   

3. Re: [GENERAL] drop/rename table and transactions

   Lincoln Yeoh <lylyeoh@mecomb.com> — 1999-11-26T01:08:04Z

   >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;
   
   What happens if two different connections try to "drop table table1"? Try
   doing it step by step in two different connections?
   
   I did a vaguely similar thing.
   
   I did a BEGIN, DROP TABLE, ROLLBACK. And yes I know you're not supposed to
   be able to rollback a drop table, but I could not recreate the table- I had
   to do a manual file system delete of the table. If a connection gets broken
   during a transaction, and you get a rollback, you could encounter the same
   problem.
   
   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.
   
   
   

4. Re: [GENERAL] drop/rename table and transactions

   Vadim Mikheev <vadim@krs.ru> — 1999-11-26T05:46:33Z

   Mike Mascari wrote:
   > 
   > 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
   
   And I agreed with this.
   But I would like to preserve ability to CREATE TABLE, mostly
   because I think that SELECT ... INTO TABLE ... is very usefull
   thing.
   
   Vadim
   
   

5. Re: [GENERAL] locking/insert into table and transactions

   Lincoln Yeoh <lylyeoh@mecomb.com> — 1999-11-26T09:04:09Z

   Hi,
   
   I'd like to prevent duplicate ids from being inserted into a table. I can
   let the database enforce it by using UNIQUE or PRIMARY KEY. But assuming I
   prefer to catch such things with the application, what would be the best
   way of doing it?
   
   The only way I figured to do it was to use:
   begin;
   lock table accounts;
   select count(*) from accounts where id=$number;
    if count=0, insert into accounts (id,etc) values ($number,$etc)
   commit;
   
   Is this a good idea? Or is it much better and faster to let the database
   catch things?
   
   Is it faster to use "select count(*) from accounts" or "select id from
   accounts"? 
   
   Apparently count(*) has some speed optimizations in MySQL. So wondering if
   there are similar things in Postgres.
   
   Thanks,
   
   Link.
   
   
   

6. Re: [GENERAL] drop/rename table and transactions

   Jaco de Groot <jaco@gospelsjop.com> — 1999-11-28T11:37:30Z

   > Is it possible to achieve your goals by using things like
   > "delete * from table1 where id!=stuffIwant" instead of dropping it?
   
   Yes, I think I better use delete statements instead of drop statements
   knowing PostgreSQL can't always handle drop/rename statements in
   transactions correctly.
   
   Jaco de Groot