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

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.

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

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

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.

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

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)

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

Best Regards, Simon Riggs