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:27:50
Message-ID: 20060103212750.GT82560@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Jan 03, 2006 at 04:20:47PM -0500, Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > > > 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.
> 
> We would be creating a new lock type for this.

Sorry if I've just missed this in the thread, but what would  the new
lock type do? My impression is that as it stands you can either do:

BEGIN;
ALTER TABLE EXCLUSIVE;
...
ALTER TABLE SHARE; --fsync
COMMIT;

Which would block all other access to the table as soon as the first
ALTER TABLE happens. Or you can:

ALTER TABLE EXCLUSIVE;
...
ALTER TABLE SHARE;

Which means that between the two ALTER TABLES every backend that does
DML on that table will not have that DML logged, but because there's no
exclusive lock that DML would be allowed to occur.

BTW, there might be some usecase for the second scenario, in which case
it would probably be better to tell the user to aquire a table-lock on
their own rather than do it automatically as part of the update...

> Basically meaning your idea of update while EXCLUSIVE/PRESERVE/STABLE is
> happening is never going to be implemented because it is just too hard
> to do, and too prone to error.

What I figured. Never hurts to ask though. :)
-- 
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: igDate: 2006-01-03 21:31:58
Subject: Windows Installer Bug
Previous:From: Bruce MomjianDate: 2006-01-03 21:20:47
Subject: Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and

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