Re: postgres_fdw: Emit message when batch_size is reduced

On Tue, Oct 13, 2026 at 10:25 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Aug 11, 2026 at 3:01 PM Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
> wrote:
> >> Overall, the changes look good to me, and I don't have any further
> comments.
> > Same here, I'd set it to Ready For Committer status if it weren't
> already set.
>
> I have mixed feelings about this patch. I agree that the way that the
> batch size is capped based on the supplied number of parameters could
> be confusing to someone. I also think that the message that are
> emitted here are not necessarily too verbose. I think it would be
> unacceptable to emit a WARNING for every statement, but this only
> emits a WARNING at object definition time and a DEBUG message at usage
> time, which might be OK. All of that basically speaks in favor of the
> patch.
>
> On the flip side, this isn't the only thing in
> postgresGetForeignModifyBatchSize that could trip somebody up. The
> batch size can be taken from the table level option or the server
> level option initially, and then it can be reset to 1 for various
> different reasons, and then it can be capped by the limit at issue
> here. So I feel like it's fair to wonder how many people would really
> be helped by this patch. They'd have to have a confusion about the
> batch size, and then they'd have to have the specific confusion that
> corresponds to what the patch can tell you about, and then (unless
> they have a value so large that it triggers the definition-time
> warning) would have to think of setting log_min_messages to a low
> enough level to let them see the DEBUG message. I'm not sure how many
> people are going to think of that.
>

My interest in the DEBUG messages was giving a curious user an answer to
the question "If I'm not getting the batch_size I asked for, what
batch_size am I actually getting?", but that should infer-able from logs
looking for "FETCH <number> FROM cursor_name", albeit with some additional
effort to line up which FETCH commands correspond to which FDW queries, and
cross referencing that against the fetch_size that they've set, so it may
be that a curious user already has the instrumentation they need.

I think my instinct if I were having a batch_size problem would be to
> check the documentation, which already does mention the fact that the
> batch size can be clamped to avoid passing too many libpq parameters.
>

I agree that's the best course of action once a user knows that they have a
batch_size problem, but I don't think the user affected by this situation
knows that specific detail just yet. They probably know that their FDW
queries have slow and/or stair-step stuttering performance (i.e. why does a
result set of 1372 rows take so much longer than one with 1371? What's
special about that number?), and they might also know that they made some
configuration changes to cut down on network chatter for very large result
sets, but the root cause still eludes them. When in the discovery phase of
problem, it is reasonable to crank up the logging detail and see what's in
there. But that same increase in logging detail will surface the FETCH x
FROM y queries as well

> And all that assumes that there's really a problem here in the first
> place. Large batch sizes are obviously nicer, but a batch size of 10
> rather than 1 by definition eliminates 90% of the overhead that can
> theoretically be eliminated. Is limiting the batch size to a value
> that is likely to still be over 1000 really going to be a problem for
> people? And if is, to Corey's point upthread, what are they going to
> do about it? It's not like the libpq limit can feasibly be raised, so
> you just have to live with the batch size that the system can manage.

I see this as an argument for removing the DEBUG message but keeping the
WARNING in option.c - as it immediately tells the well user "the value you
specified cannot possibly achieve the thing you think it will do",
potentially eliminating a fruitless or at least confusing testing/debug
cycle, and possibly making them aware that there is an upper bound to
fetch_size, thus initiating the voyage of discovery as outlined by Robert
above, as well as providing a strong clue as to why the logs show a
fetch_size of 652 despite the number 652 not showing up in the config files
anywhere.

So overall I'm inclined to feel like this isn't really worth it.

If the reduction in scope I outlined in my previous paragraph isn't
appealing, then I'm fine with us not proceeding on this one.