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

Simon Riggs wrote:
> On Fri, 2005-12-30 at 11:49 -0500, Bruce Momjian wrote:
>
> > Yes, I know we agreed to the COPY LOCK, but new features now being
> > requested, so we have to re-evaluate where we are going with COPY LOCK
> > to get a more consistent solution.
>
> Thank you.

Good. I think we can be happy that COPY LOCK didn't get into a release,
so we don't have to support it forever. When we are adding features, we
have to consider not only the current release, but future releases and
what people will ask for in the future so the syntax can be expanded
without breaking previous usage.

> > Ah, but people wanted fast INSERT INTO ... SELECT, and that would use
> > EXCLUSIVE too. What about a massive UPDATE? Perhaps that could use
> > EXCLUSIVE? We don't want to add "LOCK" to every command that might use
> > EXCLUSIVE. ALTER is much better for this.
>
> > I agree if we thought EXCLUSIVE would only be used for COPY, we could
> > use LOCK, but I am thinking it will be used for other commands as well.
>
> Agreed, I will look to implement this.
>
> Could the internals of my recent patch be reviewed? Changing the user
> interface is less of a problem than changing the internals, which is
> where the hard work takes place. I do not want to extend this work
> further only to have that part rejected later.

OK, I will look it over this week or next.

> The implications of EXCLUSIVE are:
> - there will be a check on each and every I, U, D to check the state of
> the relation
> - *every* operation that attempts a write lock will attempt to acquire
> an EXCLUSIVE full table lock instead
> - following successful completion of *each* DML statement, the relation
> will be heap_sync'd involving a full scan of the buffer cache

Yes, I think that is it. What we can do is implement EXCLUSIVE to
affect only COPY at this point, and document that, and later add other
commands.

> Can I clarify the wording of the syntax? Is EXCLUSIVE the right word?
> How about FASTLOAD or BULKLOAD? Those words seem less likely to be
> misused in the future - i.e. we are invoking a special mode, rather than
> invoking a special "go faster" option.

The problem with the FASTLOAD/BULKLOAD words is that EXCLUSIVE mode is
probably not the best for loading. I would think TRUNCATE would be a
better option.

In fact, in loading a table, I think both EXCLUSIVE and TRUNCATE would be
the same, mostly. You would create the table, set its RELIABILITY to
TRUNCATE, COPY into the table, then set the RELIABILITY to SHARE or
DEFAULT. The second ALTER has to sync all the dirty data blocks, which
the same thing EXCLUSIVE does at the conclusion of COPY.

So, we need a name for EXCLUSIVE mode that suggests how it is different
from TRUNCATE, and in this case, the difference is that EXCLUSIVE
preserves the previous contents of the table on recovery, while TRUNCATE
does not. Do you want to call the mode PRESERVE, or EXCLUSIVE WRITER?
Anyway, the keywords are easy to modify, even after the patch is
submitted. FYI, I usually go through keywords.c looking for a keyword
we already use.

> > I don't consider the single-transaction to be a no-cost solution. You
> > are adding flags to commands, and you are using a dump layout for
> > performance where the purpose for the layout is not clear. The ALTER is
> > clear to the user, and it allows nologging operations to happen after
> > the table is created.
> >
> > In fact, for use in pg_dump, I think DROP is the proper operation for
> > loading, not your transaction wrapping solution. We already agree we
> > need DROP (or TRUNCATE), so why not use that rather than the transaction
> > wrap idea?
>
> This was discussed on-list by 2 core team members, a committer and
> myself, but I see no requirements change here. You even accepted the
> invisible COPY optimization in your last post - why unpick that now?
> Please forgive my tone, but I am lost for reasonable yet expressive
> words.

Do you think you are the only one who has rewritten a patch multiple
times? We all have. The goal is to get the functionality into the
system in the most seamless way possible. Considering the number of
people who use PostgreSQL, if it takes use 10 tries, it is worth it
considering the thousands of people who will use it. Would you have us
include a sub-optimal patch and have thousands of people adjust to its
non-optimal functionality? I am sure you would not. Perhaps a company
would say, "Oh, just ship it", but we don't.

> The --single-transaction mode would apply even if the dump was created
> using an earlier version of pg_dump. pg_dump has *not* been altered at
> all. (And I would again add that the idea was not my own)

I assume you mean this:

http://archives.postgresql.org/pgsql-patches/2005-12/msg00257.php

I guess with the ALTER commands I don't see much value in the
--single-transaction flag. I am sure others suggested it, but would
they suggest it now given our current direction. The fact that the
patch was submitted does not give it any more weight --- the question is
does this feature make sense for 8.2. The goal is not to cram as many
optimizations into PostgreSQL as possible, the goal is to present a
consistent usable system to users.

> > So, to summarize, I think we should add DROP/TRUNCATE, and use that by
> > default (or optionally off?) in pg_dump, and, assuming we want EXCLUSIVE
> > for more than just COPY, we need to add ALTER TABLE EXCLUSIVE.
>
> Would you mind stating again what you mean, just so I can understand
> this? Your summary isn't enough.

New ALTER TABLE mode, perhaps call it PERSISTENCE:

ALTER TABLE tab PERSISTENCE DROP ON RECOVERY
ALTER TABLE tab PERSISTENCE TRUNCATE ON RECOVERY

These would drop or truncate all tables with this flag on a non-clean
start of the postmaster, and write something in the server logs.
However, I don't know that we have the code in place to DROP/TRUNCATE in
recovery mode, and it would affect all databases, so it could be quite
complex to implement. In this mode, no WAL logs would be written for
table modifications, though DDL commands would have to be logged.

ALTER TABLE tab PERSISTENCE PRESERVE (or STABLE?)

Table contents are preserved across recoveries, but data modifications
can happen only one at a time. I don't think we have a lock mode that
does this, so I am worried a new lock mode will have to be created. A
simplified solution at this stage would be to take an exclusive lock on
the table, but really we just need a single-writer table lock, which I
don't think we have. initially this can implemented to only affect COPY
but later can be done for other commands.

ALTER TABLE tab PERSISTENCE DEFAULT

This would be our current default mode, which is full concurrency and
persistence.

It took me over an hour to write this, but I feel the time is worth it
because of the number of users who use our software.

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