Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and - Mailing list pgsql-hackers

On Fri, 2005-12-30 at 16:14 -0500, Bruce Momjian wrote:
> Simon Riggs wrote:
> > 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'm very happy for suggestions on what these new modes are called.

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

Right now, this will be a TODO item... it looks like it will take some
thought to implement correctly.

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

ExclusiveLock locks out everything apart from readers, no new lock mode
AFAICS. Implementing that is little additional work for COPY.

Tom had a concern about setting this for I, U, D commands via the
executor. Not sure what the details of that are, as yet.

We can use either of the unlogged modes for pg_dump, so I'd suggest its
this one. Everybody happy with this being the new default in pg_dump, or
should it be an option?

>     ALTER TABLE tab PERSISTENCE DEFAULT
> 
> This would be our current default mode, which is full concurrency and
> persistence.

I'm thinking whether the ALTER TABLE statement might be better with two
bool flags rather than a 3-state char.

flag 1: ENABLE LOGGING | DISABLE LOGGING

flag 2: FULL RECOVERY | TRUNCATE ON RECOVERY

Giving 3 possible sets of options:

-- the default
ALTER TABLE mytable ENABLE LOGGING FULL RECOVERY; (default)

-- EXCLUSIVE mode
ALTER TABLE mytable DISABLE LOGGING FULL RECOVERY;
...which would be used like thisALTER TABLE mytable DISABLE LOGGING;COPY or other bulk data manipulation SQLALTER TABLE
mytableENABLE LOGGING;
 
...since FULL RECOVERY is the default.

-- multiuser temp table mode
ALTER TABLE mytable DISABLE LOGGING TRUNCATE ON RECOVERY;
...which would usually be left on all the time

which only uses one new keyword LOGGING and yet all the modes are fairly
explicit as to what they do.

An alternative might be the slightly more verbose:ALTER TABLE mytable DISABLE LOGGING FORCE EXCLUSIVE TABLE LOCK;
which would be turned off byALTER TABLE mytable ENABLE LOGGING;

Comments?

Best Regards, Simon Riggs