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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Martijn van Oosterhout <kleptog(at)svana(dot)org>, Greg Stark <gsstark(at)mit(dot)edu>, Rod Taylor <pg(at)rbt(dot)ca>, Qingqing Zhou <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 16:12:11
Message-ID: 7273.1135872731@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> My view would be that this thread has been complex because everybody has
> expressed a somewhat different requirement, which could be broken down
> as:
> 1. The need for a multi-user-accessible yet temporary table
> 2. Loading data into a table immediately after it is created (i.e. in
> same transaction), including but not limited to a reload from pg_dump
> 3. How to load data quickly into an existing table (COPY)
> 4. How to add/modify data quickly in an existing table (INSERT SELECT,
> UPDATE)

> I can see the need for all of those individually; my existing patch
> submission covers (2) and (3) only. I very much like your thought to
> coalesce these various requirements into a single coherent model.

However, you then seem to be arguing for still using the COPY LOCK
syntax, which I think Bruce intended would go away in favor of using
these ALTER commands.  Certainly that's what I'd prefer --- COPY has
got too darn many options already.

> In the patch, pg_dump has *not* been altered to use COPY LOCK, so a
> pg_dump *will* work with any other version of PostgreSQL, which *would
> not* be the case if we added ALTER TABLE ... RELIABILITY statements into
> it.

Wrong --- the good thing about ALTER TABLE is that an old version of
Postgres would simply reject it and keep going.  Therefore we could get
the speedup in dumps without losing compatibility, which is not true
of COPY LOCK.

BTW, this is a perfect example of the use-case for not abandoning a
dump-file load simply because one command fails.  (We have relied on
this sort of reasoning many times before, too, for example by using
"SET default_with_oids" in preference to CREATE TABLE WITH/WITHOUT OIDS.)
I don't think that "wrap the whole load into begin/end" is really a very
workable answer, because there are far too many scenarios where you
can't do that.  Another one where it doesn't help is a data-only dump.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-12-29 16:13:56
Subject: Re: localization problem (and solution)
Previous:From: Bruce MomjianDate: 2005-12-29 16:05:42
Subject: Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and

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