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

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

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>,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>, 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 23:10:16
Message-ID: 1136329816.5052.239.camel@localhost.localdomain (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Tue, 2006-01-03 at 16:20 -0500, Bruce Momjian wrote:
> Jim C. Nasby wrote:

> > 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...
> "Idealistically", yep.  It would be great if we could put a helmet on
> and the computer would read your mind.  :-)
> 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.

The reason for locking the whole table was to ensure that we do not have
a mixture of logged and non-logged writers writing to the same data
blocks, since that could damage blocks unrecoverably in the event of a
crash. (Though perhaps only if full_block_writes is on)

The ALTER TABLE .. EXCLUSIVE/(insert name) mode would mean that *any*
backend who took a write lock on the table, would lock out the whole
table. So this new mode is not restricted to the job/user who ran the
ALTER TABLE command. (I would note that that is how Oracle and Teradata
do this for pre-load utility table locking, but why should we follow
them on that?)

Currently, when we add a new row when the FSM is empty, we check the
last block of the table. That would cause multiple writers to access the
same blocks and so we would be in danger. The only way to avoid that
would be for logged writers (who would use the FSM if it were not empty)
to notify back to the FSM that they have just added a block - and remove
the behaviour to look for the last block.

Anyway, one step at a time. *Maybe* we can do that in the future, but
right now I'd like to add the basic fast write/load functionality.

Also, I think I will do the docs first this time, just so everyone can
read what we're getting ahead of time, to ensure we all agree.

Best Regards, Simon Riggs

In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2006-01-03 23:23:54
Subject: Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and
Previous:From: Simon RiggsDate: 2006-01-03 22:53:53
Subject: Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and

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