Thread: Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions
> From: Lamar Owen <lamar.owen@wgcr.org> > On Fri, 26 Nov 1999, Mike Mascari wrote: > > > Tom Lane <tgl@sss.pgh.pa.us> writes: > > > What does ORACLE do here? > > > > > Since ORACLE has 70% of the RDBMS market, it is the de facto standard > > > > > > Yes, and Windows is the de facto standard operating system. I don't use > > > Windows, and I'm not willing to follow Oracle's lead when they make a > > > bad decision... > > > So I guess I should file away my other suggestion to use DCOM as > > the object technology of choice instead of CORBA? ;-) > > This is a Free Software project -- PostgreSQL is not bound by the decisions of > the 'market leader' any more than Linux is bound by the standards of Microsoft. The DCOM remark was just a joke ;-). My remark concerning ORACLE was in response to Andreas' comment that implicit COMMITs of DDL statements was absurd. I wanted to simply point out that, since ORACLE has 70% market share, most corporate database developers EXPECT their DDL statements to commit their transactions (if they've RTFM). I also pointed out that it would be GREAT 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... I'll stop swinging at windmills now... Mike Mascari
On Fri, 26 Nov 1999, Mike Mascari wrote: > The DCOM remark was just a joke ;-). My remark concerning ORACLE was in > response to Andreas' comment that implicit COMMITs of DDL statements was > absurd. I wanted to simply point out that, since ORACLE has 70% market > share, I did not see the response to Andreas, nor did I see Andreas' assertion that it was absurd. My apologies. > 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... Yes, that is true. As long as the storage manager relies on the filesystem for table names, this will be a problem, unless filesystem deletions are delayed until COMMIT, and filesystem creates are undone at a ROLLBACK. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
> 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 maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
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
> 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 maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
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
> 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 maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> 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.
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...
regards, tom lane
Bruce Momjian wrote: > > > > > > > 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. Moving table file to/off database dir separately is not right way for backup/restore... On-line/off-line full backup utility will copy _all_ database files to _somewhere_ (tape etc) as well as on-line transaction logs and pg_control (to know when was the last checkpoint made). And to restore things after disk failure administrator will have to copy _all_ files + logs (+logs made as incremental backup) + pg_control back and start postmaster. Vadim
Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> 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
Vadim Mikheev <vadim@krs.ru> writes:
> So, no more nerves needed, Tom, yeh? -:)
> It would be nice if someone else, not me, implement this...
Um, I've got more than enough on my plate already...
>> 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
I saw your message about that after sending mine. Yes, that'd be
a cool way of displaying the relationship. But the main thing to
remember is that it'd only be correct at steady-state when nothing
is being changed. If we tried to guarantee the mapping was correct
100% of the time, we'd be back to square one. Of course, that
makes the whole thing somewhat less useful for debugging purposes,
since Murphy's Law says that the times you really need to know
what's what are just when the system crashed in the middle of
a table rename ;-)
regards, tom lane
> > 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. I look at this and question the value of allowing such fancy things vs. the ability to look at the directory and know exactly what table is which file. Maybe we can use file names like 23423_mytable where 24323 is the table oid and mytable is the table name. That way, we can know the table, and they are unique too to allow RENAME TABLE to work. This doesn't solve Vadim's problem. His additional work would be to write a line to the log file for each table create/delete saying I deleted this table with this oid, and when reading back the log, he has to record the oid_username combination and use that to translate his log oids into actual filenames. In fact, doesn't that information already appear in the WAL log as part of pg_class changes? Or is the problem that the table changes happen before the pg_class is committed? > 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... That is OK, and a possible workaround if the above idea is not good. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> Moving table file to/off database dir separately is not right way for > backup/restore... > > On-line/off-line full backup utility will copy _all_ database files to > _somewhere_ (tape etc) as well as on-line transaction logs > and pg_control (to know when was the last checkpoint made). > And to restore things after disk failure administrator will > have to copy _all_ files + logs (+logs made as incremental backup) > + pg_control back and start postmaster. No, I am talking about restoring a single table without doing the entire database. If you recreate the table empty with the same structure, shutdown db, mv table restored file to data directory and restart, table not has old contents. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> 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 In fact, let me change what I suggested. Instead of 3434_mytable, I suggest mytable_3434 so that the tables even appear in alphabetical order in the directory. The _ may be the wrong character to separate tablename from oid. Not sure, but we may need to use something that can't be used in sql like mytable+234. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> > 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 That's interesting, but I am concerned about the extra overhead of creating two links for every file. The other issue is if the table is accidentally dropped, how do you use that utility to know the oid of the table that was removed? I guess I like the OID_tablename idea. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi all, I propose here that we stop the release of lock before end of transaction. I have been suffering from the early release of lock. Comments ? If we don't allow DDL command inside transaction block,we won't need the release before end of transaction. If we allow DDL command inside transaction block,it may be a problem. But are there any other principles which could guarantee consistency ? Regards. Hiroshi Inoue Inoue@tpf.co.jp
is there a PostgreSQL in win98 or win95?
Chris Ian Capon Fiel wrote: > > is there a PostgreSQL in win98 or win95? http://www.postgresql.org/docs/pgsql/doc/README.NT The NT port will, AFAIK, run on any Win32 implementation, as long as you have the Cygwin stuff loaded (talked about in the README.NT file....). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11