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

Wed, 26 May 1999 12:09:11 +0800
Vadim Mikheev <vadim@krs.ru>
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?

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, Pennsylvania 19026

Wed, 26 May 1999 00:33:35 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_dump inconsistences
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

Wed, 26 May 1999 12:55:34 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] pg_dump inconsistences
> 
> 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, Pennsylvania 19026

Wed, 26 May 1999 00:57:41 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_dump inconsistences
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

Tue, 25 May 1999 22:03:06 -0700
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] pg_dump inconsistences
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

Tue, 25 May 1999 22:13:21 -0700
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] pg_dump inconsistences