Обсуждение: drop/rename table and transactions
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
Jaco de Groot wrote: > > 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 seen the same problem. I found that if I always do a vacuum first, it does not seem to happen. It is the 'alter table .. rename..' that causes the problem for me. BTW, are there any plans to implement the rest of the SQL '92 ALTER TABLE stuff (deleting columns, changing constraints). I'm conjuring up SQL to do this for my tables, but it is quite painful -- especially if you have several indices and a SERIAL type in the table. Adriaan