Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Wow, that is a major pain. Anyone else think so? Using oid's instead of names may give us some ability to fix some other bugs, though. Yes, and yes. I've been trying to nerve myself to propose that, because it seems the only reasonable way to make rollback of RENAME TABLE and DROP TABLE work safely. It'll be a pain in the neck for debugging and admin purposes though. So, no more nerves needed, Tom, yeh? -:) It would be nice if someone else, not me, implement this... Can we make some sort of usually-correct-but-not-guaranteed-correct dump that shows which corresponds to what? Maybe something similar to the textfile dump of pg_shadow that the postmaster uses for password authentication? Then at least you'd have some shot at figuring out which file was what in extremis... As it was proposed - utility to create dir with database name (in addition to dir with database oid where data really live) and symlinks there: table_name -- ../db_oid/table_oid Vadim
Re: [GENERAL] drop/rename table and transactions
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
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
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
if PostgreSQL could successfully rollback DDL statements sanely (and thus diverge from ORACLE). I guess I don't expect that to happen successfully until something the equivalent of TABLESPACES is implemented and there is a disassociation between table names, index names and their filesystem counterparts and to be able to "undo" filesystem operations. That, it seems to me, will be a major undertaking and not going to happen any time soon... Ingres has table names that don't match on-disk file names, and it is a pain to administer because you can't figure out what is going on at the file system level. Table files have names like AAAHFGE. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Bruce Momjian wrote: if PostgreSQL could successfully rollback DDL statements sanely (and thus diverge from ORACLE). I guess I don't expect that to happen successfully until something the equivalent of TABLESPACES is implemented and there is a disassociation between table names, index names and their filesystem counterparts and to be able to "undo" filesystem operations. That, it seems to me, will be a major undertaking and not going to happen any time soon... Ingres has table names that don't match on-disk file names, and it is a pain to administer because you can't figure out what is going on at the file system level. Table files have names like AAAHFGE. I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Vadim
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Vadim Mikheev wrote: Bruce Momjian wrote: if PostgreSQL could successfully rollback DDL statements sanely (and thus diverge from ORACLE). I guess I don't expect that to happen successfully until something the equivalent of TABLESPACES is implemented and there is a disassociation between table names, index names and their filesystem counterparts and to be able to "undo" filesystem operations. That, it seems to me, will be a major undertaking and not going to happen any time soon... Ingres has table names that don't match on-disk file names, and it is a pain to administer because you can't figure out what is going on at the file system level. Table files have names like AAAHFGE. I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Vadim Will that aid in fixing a problem such as this: session 1: CREATE TABLE example1(value int4); BEGIN; session 2: BEGIN; ALTER TABLE example1 RENAME TO example2; session 1: INSERT INTO example1 VALUES (1); END; NOTICE: Abort Transaction and not in in-progress state ERROR: Cannot write block 0 of example1 [test] blind session 2: END; NOTICE: Abort Transaction and not in in-progress state ERROR: Cannot write block 0 of example1 [test] blind Just curious, Mike (implicit commit) Mascari
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Wow, that is a major pain. Anyone else think so? Using oid's instead of names may give us some ability to fix some other bugs, though. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Mike Mascari wrote: Will that aid in fixing a problem such as this: session 1: CREATE TABLE example1(value int4); BEGIN; session 2: BEGIN; ALTER TABLE example1 RENAME TO example2; session 1: INSERT INTO example1 VALUES (1); END; NOTICE: Abort Transaction and not in in-progress state ERROR: Cannot write block 0 of example1 [test] blind session 2: END; NOTICE: Abort Transaction and not in in-progress state ERROR: Cannot write block 0 of example1 [test] blind Seems that oid file names will fix this... Currently, each shared buffer description structure has database/table names for the purposes of "blind" writes (when backend cache hasn't entry for relation and so bufmgr can't use cache to get names from oids). ALTER TABLE ... RENAME renames relation file(s) but doesn't change relation name inside shbuffers... Mike (implicit commit) Mascari -:))) Vadim
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Bruce Momjian wrote: I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Wow, that is a major pain. Anyone else think so? Why it's so painful? We can write utility to construct database dir with table names symlinked to real table files -:) Actually, I don't understand for what would you need to know what is what, (c) -:) Using oid's instead of names may give us some ability to fix some other bugs, though. Yes. Vadim
Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
Bruce Momjian wrote: I have to say that I'm going to change on-disk database/table/index file names to _OID_! This is required by WAL because of inside of log records there will be just database/table/index oids, not names, and after crash recovery will not be able to read pg_class to get database/table/index name using oid ... Wow, that is a major pain. Anyone else think so? Why it's so painful? We can write utility to construct database dir with table names symlinked to real table files -:) Actually, I don't understand for what would you need to know what is what, (c) -:) With Ingres, you can't just look at a file and know the table name, and if you need to reload just one file from a tape, it is a royal pain to know which file to bring back. I have said Ingres make things 100 times harder for adminstrators by doing this. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] 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
