Skip site navigation (1) Skip section navigation (2)

Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>,simon(at)2ndquadrant(dot)com, kleptog(at)svana(dot)org, gsstark(at)mit(dot)edu,pg(at)rbt(dot)ca, zhouqq(at)cs(dot)toronto(dot)edu, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and
Date: 2006-01-03 21:16:55
Message-ID: 20060103211655.GR82560@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Jan 03, 2006 at 12:08:05PM -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > > On Tue, Jan 03, 2006 at 11:26:51AM -0500, Tom Lane wrote:
> > >> Such an ALTER would certainly require exclusive lock on the table,
> > >> so I'm not sure that I see much use-case for doing it like that.
> > >> You'd want to do the ALTER and commit so as not to lock other people
> > >> out of the table entirely while doing the bulk data-pushing.
> > 
> > > Maybe this just isn't clear, but would EXCLUSIVE block writes from all
> > > other sessions then?
> > 
> > I don't think it should (which implies that EXCLUSIVE is a bad name).
> 
> Agreed, EXCLUSIVE was used to mean an _exclusive_ writer.  The new words
> I proposed were PRESERVE or STABLE.

This seems to seriously limit the usefulness, though. You'll only want
to use EXCLUSIVE/PRESERVE/STABLE when you've got a specific set of DML
to do, that you know you can recover from. But if at the same time some
other part of the system could be doing what it thinks will be ACID DML
to that same table, you're now in trouble.

At a minimum that would need to be clearly spelled out in the docs. I
think it also makes a very strong use-case for exposing table-level
shared locks as well, since that would at least allow other backends to
continue reading from the table.

Idealistically, if EXCLUSIVE/PRESERVE/STABLE does it's thing by only
appending new pages, it would be nice if other backends could continue
performing updates at the same time, assuming there's free space
available elsewhere within the table (and that you'd be able to recover
those logged changes regardless of the non-logged operations). But
that's a pretty lofty goal...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2006-01-03 21:20:47
Subject: Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and
Previous:From: Tom LaneDate: 2006-01-03 20:51:11
Subject: Re: cvs tip - stats buffer process consuming 100% cpu

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group