Greg Stark wrote:
> Bruce Momjian <pgman@candle.pha.pa.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.
True, but UPDATE could save 1/2 the log I/O because the new rows would
not have to be logged.
> We could add a LOCK TABLE SHARED feature to allow the appropriate type of lock
> to be acquired.
Do you really want this behavior to be controlled by the locking mode?
That seem strange to me, as well as confusing, especially if you already
have some other kind of lock on the table.
> 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.
That is true.
> > 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.
We could if there is a use-case for it.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
