Re: NOLOGGING option, or ? - Mailing list pgsql-hackers

On Wed, 2005-06-01 at 10:18 -0400, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Not unless you are proposing to change COPY to acquire a lock strong
> >> enough to lock out other writers to the table for the duration ...
> 
> > Well, if the table is initally empty, what harm is there in locking the
> > table?
> 
> You cannot *know* whether it is empty unless you lock the table before
> you look.  So your argument is circular.
> 
> I think this only makes sense as an explicit option to COPY, one of the
> effects of which would be to take a stronger lock than COPY normally does.

Locking the table is OK. We're loading it for the first time, so we are
expecting to be the only users at this time. 

Here's a more fully worked out plan for wal/concurrency:

1. Lock table, scan until we find our first live row (not deleted,
insert not aborted), in which case throw an error. If no error, then ok
to proceed to next phase. 

2. If there are any dead row versions we truncate the file, since we do
not wish to see those rows ever again. No existing transaction can have
a lock on them, since we hold it, so its ok for them to go. We record
that we have done this in WAL with a short message to say that that we
are loading an empty table, whether or not archiving is not enabled.

3. We load the table, with all TransactionIds set as FROZEN. We do not
generate WAL for each row unless archiving is enabled.

4. Optionally, we set a flag on the table showing the whole table is
frozen. Anybody writing to this table subsequently will spoil this flag.
If the flag is set, all forms of VACUUM will return success immediately
without performing a scan (since it is already in a perfect VACUUM FULL
and VACUUM FREEZE state).

If the server crashes, we replay WAL. If we see a load start message, we
truncate the relation and note that a load has started. If there is WAL
data for the tuples, we replay it. If WAL replay ends without the load
transaction having successfully committed, then we truncate the table.

The above applies to both LOAD/COPY whatever-yer-call-it and in modified
form for CREATE TABLE AS SELECT. For CTAS, no scan is required in (1),
and no truncate is required in (2), otherwise the same.

I'm expecting Alon Goldshuv to join this discussion soon to explain some
other ideas, new developments and discuss the potential for a new
command, so lets wait for him...

Best Regards, Simon Riggs