Re: control max length of parameter values logged

On Sun, Mar 15, 2020 at 08:48:33PM -0300, Alvaro Herrera wrote:
> On 2020-Mar-14, Tom Lane wrote:
>
> > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > I am sorry --- I am confused. Why are we truncating or allowing control
> > > of truncation of BIND parameter values, but have no such facility for
> > > queries. Do we assume queries are shorter than BIND parameters, or is
> > > it just that it is easier to trim BIND parameters than values embedded
> > > in non-EXECUTE queries.
> >
> > The cases that Alvaro was worried about were enormous values supplied
> > via bind parameters. We haven't heard comparable complaints about
> > the statement text.
>
> To be more precise, I have seen cases of enormous statement text, but
> those are fixed precisely by moving the bulk to parameters. So the
> ability to trim the parameter is important. I've never seen a very
> large query without the bulk being parameterizable.

I don't claim our use is a common case or a good example but I'm going to offer
a data point.

We have very long query strings, even while using bind parameters.
Our loader process uses upsert and prepared statements.
So we might run: INSERT INTO t (k1,k2,a,b,...) VALUES($1,$2,$3,$4)
ON CONFLICT(k1,k2) DO UPDATE SET a=excluded.a,b=excluded.b
..which is fine, but we also have large number of columns - historically up to
1600. If a query fails, the error might be a query string 2+ pages long.

Looks like we have common cases (and executed many times) with:
24k long message and 86k long param string
70k long message and 10k long param string

Having full log on error is important, more to the client but also in the
server log. But it would be nice if we could reduce the server logs. Most of
the prepare string is of little value if there's no error: VALUES ($1,$2,$3,)
(but prepared query is at least better than repeating the query string).

Related: a year ago, I wrote about the repetition of the "PREPARE" statement in
query logs.
https://www.postgresql.org/message-id/20190208132953.GF29720@telsasoft.com

Ultimately I withdrew that patch and switched to log_statement_min_duration.

> Nod. (I think if we really wanted to trim queries, it would have to be
> something semantically sensible, not just trim whatever is at the end of
> the statement literal.

If it were easy, I would truncate query strings to a few hundred bytes.

--
Justin