Re: [GENERAL] drop/rename table and transactions

[Jaco de Groot]

 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

Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions

[Jaco de Groot]

Mike Mascari wrote:
 
 From an otherwise EXTREMELY happy user :-) (full smile...),  I see 3
 scenarios:

 (1) Disallow DDL statements in transactions
 (2) Send NOTICE's asking for the user to not trigger the bug until the bugs
 can be fixed -or-
 (3) Have all DDL statements implicity commit any running transactions.

I think 1 is the best solution as long as there are bugs concerning DDL
statements in transactions. It will prevent people from getting in
trouble. I've been in this trouble for months :-(. I'm using Java
Servlets to connect to PostgreSQL and I'm having DDL statements whitin
transactions wich sometimes cause an error. This error is hard to find
and solve if you don't know PostgreSQL has problems with DDL statements
in transactions. And if the error occures it doesn't simply crash 1
transaction or connection but it crashes all connections wich prevents
my website from running correctly until I've manualy fixed the problem
(mostly restarting PostgreSQL). To prevent others from getting in the
same trouble I'd like to propose that the next release of PosgreSQL
will dissalow DDL statements in transactions and notice the user this
is a feature wich is currently in development.

Jaco de Groot

[GENERAL] drop/rename table and transactions

[Jaco de Groot]

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