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

Tom Lane wrote:
> 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.

Yep, Tom is echoing my reaction. There is a temptation to add things up
onto existing commands, e.g. LOCK, and while it works, it makes for some
very complex user API's. Having COPY behave differently because it is
in a transaction is fine as long as it is user-invisible, but once you
require users to do that to get the speedup, it isn't user-invisible
anymore.

(I can see it now, "Why is pg_dump putting things in transactions?",
"Because it prevents it from being logged." "Oh, should I be doing that
in my code?" "Perhaps, if you want ..." You can see where that
discussion is going. Having them see "ATER TABLE ... RELIBILITY
TRUNCATE" is very clear, and very clear on how it can be used in user
code.)

I think there is great utility in giving users one API, namely
RELIABILITY (or some other keyword), and telling them that is where they
control logging. I realize adding one keyword, LOCK, to an existing
command isn't a big deal, but once you decentralize your API enough
times, you end up with a terribly complex database system. It is this
design rigidity that helps make PostgreSQL so much easier to use than
other database systems.

I do think it is valid concern about someone use the table between the
CREATE and the ALTER TABLE RELIABILITY. One solution would be to allow
the RELIABILITY as part of the CREATE TABLE, another is to tell users to
create the table inside a transaction.

--
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