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

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, kleptog(at)svana(dot)org, simon(at)2ndquadrant(dot)com, tgl(at)sss(dot)pgh(dot)pa(dot)us, 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: 2005-12-29 17:30:49
Message-ID: 200512291730.jBTHUnn09840@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Greg Stark wrote:
> "Andrew Dunstan" <andrew(at)dunslane(dot)net> writes:
> 
> > Bruce Momjian said:
> > > DROP would drop the table on a restart
> > > after a non-clean shutdown.  It would do _no_ logging on the table and
> > > allow concurrent access, plus index access.  DELETE is the same as
> > > DROP, but it just truncates the table (perhaps TRUNCATE is a better
> > > word).
> > >
> > > EXCLUSIVE would allow only a single session to modify the table, and
> > > would do all changes by appending to the table, similar to COPY LOCK.
> > > EXCLUSIVE would also not allow indexes because those can not be
> > > isolated like appending to the heap.  EXCLUSIVE would write all dirty
> > > shared buffers for the table and fsync them before committing.  SHARE
> > > is the functionality we have now, with full logging.
> > 
> > I an horribly scared that this will be used as a "performance boost" for
> > normal use. I would at least like to see some restrictions that make it
> > harder to mis-use. Perhaps restrict to superuser?
> 
> Well that's its whole purpose. At least you can hardly argue that you didn't
> realize the consequences of "DELETE ROWS ON RECOVERY"... :)

True.  I think we are worried about non-owners using it, but the owner
had to grant permissions for others to modify it, so we might be OK.

> Some thoughts:
> 
> a) I'm not sure I understand the purpose of EXCLUSIVE. When would I ever want to
>    use it instead of DELETE ROWS?

Good question.  The use case is doing COPY into a table that already had
data.  EXCLUSIVE allows additions to the table but preserves the
existing data on a crash.

> b) It seems like the other feature people were talking about of not logging
>    for a table created within the same transaction should be handled by
>    having this flag implicitly set for any such newly created table.
>    Ie, the test for whether to log would look like:
> 
>    if (!table->logged && table->xid != myxid) ...

Yes, the question is whether we want to limit users to having this
optimization _only_ when they have created the table in the same
transaction, and the short answer is we don't.

> c) Every option in ALTER TABLE should be in CREATE TABLE as well.

I looked into that and see that things like:

    ALTER [ COLUMN ] column SET STATISTICS integer
    ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

are not supported by CREATE TABLE, and probably shouldn't be because the
value can be changed after the table is created.  I think the only
things we usually support in CREATE TABLE are those that cannot be
altered.

> d) Yes as someone else mentioned, this should only be allowable on a table
>    with no foreign keys referencing it. 

Right, and EXCLUSIVE can not have an index either.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2005-12-29 17:35:05
Subject: Re: localization problem (and solution)
Previous:From: Andrew DunstanDate: 2005-12-29 17:21:47
Subject: Re: localization problem (and solution)

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