Thread: pg_dump inconsistences
To return consistent results pg_dump should run all queries in single transaction, in serializable mode. It's old problem. But now when selects don't block writers we are able to do this. Comments/objections? Vadim
> To return consistent results pg_dump should run all queries > in single transaction, in serializable mode. It's old problem. > But now when selects don't block writers we are able to do this. > > Comments/objections? If I understood what you were saying, I may object, but I don't, so go ahead. :-) -- 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, Pennsylvania19026
Bruce Momjian wrote: > > > To return consistent results pg_dump should run all queries > > in single transaction, in serializable mode. It's old problem. > > But now when selects don't block writers we are able to do this. > > > > Comments/objections? > > If I understood what you were saying, I may object, but I don't, so go > ahead. :-) As far as I see each COPY table TO STDOUT is executed in its own transaction. This may cause referential inconsistences (pg_dump saves foreign keys, then other transaction deletes some foreign and primary keys and commits, now pg_dump saves primary keys and loses some of them, breaking referential integrity). Vadim
> > As far as I see each COPY table TO STDOUT is executed in > its own transaction. This may cause referential inconsistences > (pg_dump saves foreign keys, then other transaction deletes some > foreign and primary keys and commits, now pg_dump saves > primary keys and loses some of them, breaking referential > integrity). Oh, I get it. -- 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, Pennsylvania19026
At 12:09 PM 5/26/99 +0800, Vadim Mikheev wrote: >To return consistent results pg_dump should run all queries >in single transaction, in serializable mode. It's old problem. >But now when selects don't block writers we are able to do this. >Comments/objections? This would remove one of the major barriers to deployment of Postgres in serious, heavy-traffic environments, particularly the Web, where no clock boundaries are respected for globally interesting sites. The use of db's to back web sites is the most intriguing aspect of modern db development, IMO. Why else would an old compiler like me have an interest? :) And Postgres has problems in this regard, one of which you point out in this post. Let me hasten to add that the development direction of the db is congruent with the needs of web site users like myself. The removal of table-level locking, for instance. Removing of fsync after select-only queries would help a lot, too, after experimentation verified by a comment to the effect that postgres does this (from a future enhancements list) I sped my site a lot by including selects in begin/end transactions. YUK. On and on. Anyway, y'all are moving in the right direction(s) and at a good pace, too. Why not do things such as you describe, when doing so better places your product in the mix for continuous use ala the Web? - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, and other goodies at http://donb.photo.net
At 12:57 AM 5/26/99 -0400, Bruce Momjian wrote: >> As far as I see each COPY table TO STDOUT is executed in >> its own transaction. This may cause referential inconsistences >> (pg_dump saves foreign keys, then other transaction deletes some >> foreign and primary keys and commits, now pg_dump saves >> primary keys and loses some of them, breaking referential >> integrity). >Oh, I get it. For some reason, I always thought this was a "feature", which is why Vadim's suggestion to fix it seems ... obvious? This gives a consistent snapshot ability, right? Currently, one must shut down access to the db in order to ensure referential consistencies, a pain on a 24/7 web site, admittedly a relatively new application. Oracle, last I heard, wants about $9,000 for deployment on a web platform (despite the lower price of $1,350 for a five user license). This is a major reason I'm here. Sybase is free at the moment, but has performance problems with the particular web server I'm using (AOLServer), weird because that server's so efficient with other dbs like Oracle and Postgres (the interface 'tween web server and db is different, that's why, Sybase is a good performing db on its own). Postgres could be a major factor in this world IF it can get past its flakey reputation. The recent large memory leak bug fix is a step in the right direction, a large one. So is the notion of a consistent dump by pg_dump. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, and other goodies at http://donb.photo.net