Re: [PATCH] Increase the maximum value track_activity_query_size

On Sun, Dec 22, 2019 at 09:06:41AM +0100, Julien Rouhaud wrote:
>On Sun, Dec 22, 2019 at 1:03 AM Tomas Vondra
><tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>>
>> On Sat, Dec 21, 2019 at 04:25:05PM -0500, Tom Lane wrote:
>> >Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> writes:
>> >> Here is what ORMs do:
>> >> select length('SELECT "column_name_1001", "column_name_1002",
>> >> "column_name_1003", "column_name_1004", "column_name_1005",
>> >> "column_name_1006", "column_name_1007", "column_name_1008",
>> >> "column_name_1009", "column_name_1010", "column_name_1011",
>> >> "column_name_1012", "column_name_1013", "column_name_1014",
>> >> "column_name_1015", "column_name_1016", "column_name_1017",
>> >> "column_name_1018", "column_name_1019", "column_name_1020",
>> >> "column_name_1021", "column_name_1022", "column_name_1023",
>> >> "column_name_1024", "column_name_1025", "column_name_1026",
>> >> "column_name_1027", "column_name_1028", "column_name_1029",
>> >> "column_name_1030", "column_name_1031", "column_name_1032",
>> >> "column_name_1033", "column_name_1034", "column_name_1035",
>> >> "column_name_1036", "column_name_1037", "column_name_1038",
>> >> "column_name_1039", "column_name_1040", "column_name_1041",
>> >> "column_name_1042", "column_name_1043", "column_name_1044",
>> >> "column_name_1045", "column_name_1046", "column_name_1047",
>> >> "column_name_1048", "column_name_1049", "column_name_1050" FROM
>> >> "some_table";');
>> >> length
>> >> --------
>> >> 1024
>> >> (1 row)
>> >
>> >> That's it – with default settings, you won't see WHERE clause or
>> >> anything else.
>> >
>> >If that's true, it doesn't offer much of a case for upping the limit
>> >on track_activity_query_size. The longest such a query could reasonably
>> >get is somewhere near NAMEDATALEN times MaxHeapAttributeNumber, which
>> >as it happens is exactly the existing limit on track_activity_query_size.
>> >
>> >> As a result, many queries exceed track_activity_query_size
>> >
>> >How? And if they are, why do you care? Such queries sure seem
>> >pretty content-free.
>> >
>>
>> I believe the example was just a very simplistic example. ORMs can of
>> course generate queries with joins, which can easily exceed the limit
>> you mentioned.
>>
>> >> What is the overhead here except the memory consumption?
>> >
>> >The time to copy those strings out of shared storage, any time
>> >you query pg_stat_activity.
>> >
>>
>> IMO that seems like a reasonable price to pay, if you want to see
>> complete queries and bump the track_activity_query_size value up.
>
>Couldn't be pg_stat_statements (or any similar extension) queryid
>exposure in pg_stat_activity [1] also an alternative? You wouldn't
>have the parameters but maybe the normalized query would be enough for
>most analysis. Now, maybe pg_stat_statements jumble overhead for such
>large statements would be even more problematic.
>

But that would effectively add dependency on pg_stat_statements, no? I
don't think we want that.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services