Re: PATCH: Batch/pipelining support for libpq

<div dir="ltr"><div class="gmail_extra">Hi all. I thought I'd share some experience from Npgsql regarding
batching/pipelining- hope this isn't off-topic.</div><div class="gmail_extra"><br /></div><div
class="gmail_extra">Npgsqlhas supported batching for quite a while, similar to what this patch proposes - with a single
Syncmessage is sent at the end.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">It has recently come
tomy attention that this implementation is problematic because it forces the batch to occur within a transaction; in
otherwords, there's no option for a non-transactional batch. This can be a problem for several reasons: users may want
tosent off a batch of inserts, not caring whether one of them fails (e.g. because of a unique constraint violation). In
otherwords, in some scenarios it may be appropriate for later batched statements to be executed when an earlier batched
statementraised an error. If Sync is only sent at the very end, this isn't possible. Another example of a problem
(whichactually happened) is that transactions acquire row-level locks, and so may trigger deadlocks if two different
batchesupdate the same rows in reverse order. Both of these issues wouldn't occur if the batch weren't implicitly
batched.</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">My current plan is to modify the batch
implementationbased on whether we're in an (explicit) transaction or not. If we're in a transaction, then it makes
perfectsense to send a single Sync at the end as is being proposed here - any failure would cause the transaction to
failanyway, so skipping all subsequent statements until the batch's end makes sense. However, if we're not in an
explicittransaction, I plan to insert a Sync message after each individual Execute, making non-transactional batched
statementsmore or less identical in behavior to non-transactional unbatched statements. Note that this mean that a
batchcan generate multiple errors, not just one.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">I'm
sharingthis since it may be relevant to the libpq batching implementation as well, and also to get any feedback
regardinghow Npgsql should act.</div></div>