From 92d637e1ef31872a5bbaeb284a00e6a98db5985f Mon Sep 17 00:00:00 2001 From: Gilles Darold Date: Sun, 23 Dec 2018 22:27:30 +0100 Subject: [PATCH] Extract information about PL/pgSQL function call in queries of temporary file reports. The information is append to the details display block. --- pgbadger | 48 ++++++++++++++++++++++++++++++++++-------------- 1 file changed, 34 insertions(+), 14 deletions(-) diff --git a/pgbadger b/pgbadger index deadd84..74eb781 100755 --- a/pgbadger +++ b/pgbadger @@ -4001,9 +4001,9 @@ sub convert_time # Stores the top N queries generating the biggest temporary file sub set_top_tempfile_info { - my ($q, $sz, $date, $db, $user, $remote, $app) = @_; + my ($q, $sz, $date, $db, $user, $remote, $app, $info) = @_; - push(@top_tempfile_info, [($sz, $date, $q, $db, $user, $remote, $app)]); + push(@top_tempfile_info, [($sz, $date, $q, $db, $user, $remote, $app, $info)]); my @tmp_top_tempfile_info = sort {$b->[0] <=> $a->[0]} @top_tempfile_info; @top_tempfile_info = (); @@ -4555,6 +4555,7 @@ Report not supported by text format $db .= ", user: $top_tempfile_info[$i]->[4]" if ($top_tempfile_info[$i]->[4]); $db .= ", remote: $top_tempfile_info[$i]->[5]" if ($top_tempfile_info[$i]->[5]); $db .= ", app: $top_tempfile_info[$i]->[6]" if ($top_tempfile_info[$i]->[6]); + $db .= ", info: $top_tempfile_info[$i]->[7]" if ($top_tempfile_info[$i]->[7]); $db =~ s/^, / - /; print $fh ($i + 1), ") ", &comma_numbers($top_tempfile_info[$i]->[0]), " - $ttl$db - ", $top_tempfile_info[$i]->[2], "\n"; @@ -9708,7 +9709,7 @@ sub print_tempfile_report $details .= "User: $normalyzed_info{$k}{samples}{$d}{user}\n" if ($normalyzed_info{$k}{samples}{$d}{user}); $details .= "Remote: $normalyzed_info{$k}{samples}{$d}{remote}\n" if ($normalyzed_info{$k}{samples}{$d}{remote}); $details .= "Application: $normalyzed_info{$k}{samples}{$d}{app}\n" if ($normalyzed_info{$k}{samples}{$d}{app}); - $details .= "Bind query: yes\n" if ($normalyzed_info{$k}{samples}{$d}{bind}); + $details .= "Info: $top_tempfile_info[$i]->[7]" if ($top_tempfile_info[$i]->[7]); print $fh qq{
$query
$md5 @@ -9766,8 +9767,8 @@ sub print_tempfile_report $details .= " - User: $top_tempfile_info[$i]->[4]" if ($top_tempfile_info[$i]->[4]); $details .= " - Remote: $top_tempfile_info[$i]->[5]" if ($top_tempfile_info[$i]->[5]); $details .= " - Application: $top_tempfile_info[$i]->[6]" if ($top_tempfile_info[$i]->[6]); - $details .= " - Bind yes: yes" if ($top_tempfile_info[$i]->[7]); $details .= " ]"; + $details .= "\nInfo: $top_tempfile_info[$i]->[7]" if ($top_tempfile_info[$i]->[7]); my $query = &highlight_code($top_tempfile_info[$i]->[2]); my $md5 = ''; $md5 = 'md5: ' . md5_hex($top_tempfile_info[$i]->[2]) if ($enable_checksum); @@ -12933,7 +12934,9 @@ sub parse_query $cur_cancel_info{$t_pid}{dbuser} = $prefix_vars{'t_dbuser'}; $cur_cancel_info{$t_pid}{dbclient} = $prefix_vars{'t_client'} || $prefix_vars{'t_dbclient'}; $cur_cancel_info{$t_pid}{dbappname} = $prefix_vars{'t_appname'}; - } elsif (exists $cur_cancel_info{$t_pid}) { + } + elsif (exists $cur_cancel_info{$t_pid}) + { &store_temporary_and_lock_infos($t_pid); } } @@ -12977,11 +12980,16 @@ sub parse_query } # Save previous temporary file information with same pid to not overwrite it - if ($prefix_vars{'t_loglevel'} =~ $main_log_regex) { - if (($prefix_vars{'t_query'} !~ /temporary file: path .*, size \d+/) && exists $cur_temp_info{$t_pid}) { + if ($prefix_vars{'t_loglevel'} =~ $main_log_regex) + { + if (($prefix_vars{'t_query'} !~ /temporary file: path .*, size \d+/) + && exists $cur_temp_info{$t_pid}) + { &store_temporary_and_lock_infos($t_pid); } - if (($prefix_vars{'t_query'} !~ /acquired [^\s]+ on [^\s]+ .* after [0-9\.]+ ms/) && exists $cur_lock_info{$t_pid}) { + if (($prefix_vars{'t_query'} !~ /acquired [^\s]+ on [^\s]+ .* after [0-9\.]+ ms/) + && exists $cur_lock_info{$t_pid}) + { &store_temporary_and_lock_infos($t_pid); } } @@ -14161,13 +14169,21 @@ sub store_temporary_and_lock_infos return if (!$t_pid); # Store normalized query temp file size if required - if (exists $cur_temp_info{$t_pid} && ($cur_temp_info{$t_pid}{query} ne '') && $cur_temp_info{$t_pid}{size}) { + if (exists $cur_temp_info{$t_pid} && ($cur_temp_info{$t_pid}{query} ne '') + && $cur_temp_info{$t_pid}{size}) + { + # Remove additional information generated by log function call + if ($cur_temp_info{$t_pid}{query} =~ s/(\s*PL\/pgSQL function.*)//s) + { + $cur_temp_info{$t_pid}{info} = $1; + } # Add a semi-colon at end of the query $cur_temp_info{$t_pid}{query} .= ';' if ($cur_temp_info{$t_pid}{query} !~ /;\s*$/s); # Anonymize query if requested by the user - if ($anonymize) { + if ($anonymize) + { $cur_temp_info{$t_pid}{query} = &anonymize_query($cur_temp_info{$t_pid}{query}); } @@ -14177,16 +14193,20 @@ sub store_temporary_and_lock_infos $normalyzed_info{$normalized}{tempfiles}{size} += $cur_temp_info{$t_pid}{size}; $normalyzed_info{$normalized}{tempfiles}{count}++; - if ($normalyzed_info{$normalized}{tempfiles}{maxsize} < $cur_temp_info{$t_pid}{size}) { + if ($normalyzed_info{$normalized}{tempfiles}{maxsize} < $cur_temp_info{$t_pid}{size}) + { $normalyzed_info{$normalized}{tempfiles}{maxsize} = $cur_temp_info{$t_pid}{size}; } if (!exists($normalyzed_info{$normalized}{tempfiles}{minsize}) - || $normalyzed_info{$normalized}{tempfiles}{minsize} > $cur_temp_info{$t_pid}{size}) { + || $normalyzed_info{$normalized}{tempfiles}{minsize} > $cur_temp_info{$t_pid}{size}) + { $normalyzed_info{$normalized}{tempfiles}{minsize} = $cur_temp_info{$t_pid}{size}; } - &set_top_tempfile_info($cur_temp_info{$t_pid}{query}, $cur_temp_info{$t_pid}{size}, $cur_temp_info{$t_pid}{timestamp}, $cur_temp_info{$t_pid}{dbname}, $cur_temp_info{$t_pid}{dbuser}, $cur_temp_info{$t_pid}{dbclient}, $cur_temp_info{$t_pid}{dbappname}); + &set_top_tempfile_info($cur_temp_info{$t_pid}{query}, $cur_temp_info{$t_pid}{size}, $cur_temp_info{$t_pid}{timestamp}, $cur_temp_info{$t_pid}{dbname}, $cur_temp_info{$t_pid}{dbuser}, $cur_temp_info{$t_pid}{dbclient}, $cur_temp_info{$t_pid}{dbappname}, $cur_temp_info{$t_pid}{info}); + # Check if we don't have sample for this query (occurs when log_min_duration_statement doesn't logged the query) - if (!exists $normalyzed_info{$normalized}{samples}) { + if (!exists $normalyzed_info{$normalized}{samples}) + { &set_top_sample($normalized, $cur_temp_info{$t_pid}{query}, $cur_temp_info{$t_pid}{duration}, $cur_temp_info{$t_pid}{timestamp}, $cur_temp_info{$t_pid}{dbname}, $cur_temp_info{$t_pid}{dbuser}, $cur_temp_info{$t_pid}{dbclient},$cur_temp_info{$t_pid}{dbappname}); } delete $cur_temp_info{$t_pid}; -- 2.40.0