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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:

> > BEGIN;
> > LOCK TABLE foo;
> > COPY foo from ...
> > COMMIT;
> >
> > There could be a COPY LOCK option to obtain a lock, but it would be purely for
> > user convenience so they don't have to bother with BEGIN and COMMIt.
> >
> > The only downside is a check to see if an exclusive table lock is present on
> > every copy and insert. That might be significant but perhaps there are ways to
> > finess that. If not perhaps only doing it on COPY would be a good compromise.
>
> Well, again, if we wanted to use EXCLUSIVE only for COPY, this might
> make sense. However, also consider that the idea for EXCLUSIVE was that
> users could continue read-only queries on the table while it is being
> loaded (like COPY allows now), and that in EXCLUSIVE mode, we are only
> going to write into new pages.

Well I pictured the above kicking in for any insert. You can't do it on
deletes and updates anyways since torn pages could cause the table to become
corrupt.

We could add a LOCK TABLE SHARED feature to allow the appropriate type of lock
to be acquired.

But now that I think further on this that doesn't really make this free.
fsyncing a table isn't free. If some other transaction has come and done some
massive updates on the table then I come along and do a single quick insert I
don't necessarily want to fsync all those pending writes, it's cheaper to
fsync the WAL log.

> If someone has an exclusive lock on the table and does a COPY or SELECT
> INTO do we want to assume we are only going to write into new pages, and
> do we want to force an exclusive lock rather than a single-writer lock?
> I don't think so.

And only using new pages is itself a cost as well. Though I think the fact
that it would tend to mean a lot less seeking and more sequential i/o would
tend to make it a worth the extra garbage in the table.

It might be useful having some kind of LOCK TABLE SHARED anyways. It seems
silly to have the functionality in the database and not expose it for users.

--
greg