def prettify_sql(sql):
if sql is None:
return None
return format_sql(sql,
keyword_case="upper",
identfier_case="lower",
strip_comments=False,
reindent=True,
indent_tabs=False)
def prettify_sql(sql):
if sql is None:
return None
return format_sql(
sql,
keyword_case="upper",
identfier_case="lower",
strip_comments=False,
reindent=True,
indent_tabs=False)
def execute_compiled_sql(sql, params=None):
rows_affected = 0
with transaction.get_connection().cursor() as cur:
params = params or None
LOG.debug(
format_sql(cur.mogrify(sql, params).decode("utf-8"),
reindent_aligned=True))
cur.execute(sql, params)
rows_affected = cur.rowcount
return rows_affected
def stat_statements(request):
"""Get any blocked and blocking process data"""
data = None
query_bad_threshold = Decimal("5000")
query_warn_threshold = Decimal("2500")
limit, offset = get_limit_offset(request)
with DBPerformanceStats(get_identity_username(request),
CONFIGURATOR,
database_ranking=DATABASE_RANKING) as dbp:
data = dbp.get_statement_stats(limit=limit, offset=offset)
action_urls = []
if "mean_exec_time" in data[0]:
for rec in data:
set_null_display(rec)
rec["_attrs"] = {"query": 'class="pre monospace"'}
rec["query"] = format_sql(rec["query"],
reindent=True,
indent_realigned=True,
keyword_case="upper")
for col in ("min_exec_time", "mean_exec_time", "max_exec_time"):
attrs = ['class="sans"']
if rec[col] > query_bad_threshold:
attrs.append('style="background-color: #d16969;"')
elif rec[col] > query_warn_threshold:
attrs.append('style="background-color: #c7d169;"')
else:
attrs.append('style="background-color: #69d172;"')
rec["_attrs"][col] = " ".join(attrs)
# action_urls.append(reverse("stat_statements_reset"))
page_header = "Statement Statistics"
return HttpResponse(
render_template(
"stmt_stats",
page_header,
tuple(f for f in data[0] if not f.startswith("_")) if data else (),
data,
template="stats_table.html",
action_urls=action_urls,
))
def stat_activity(request):
"""Get any blocked and blocking process data"""
# action_urls = [reverse("db_cancel_connection")]
# if request.query_params.get("terminate") == "enable":
# LOG.info("Enabling the pg_stat_activity terminate action template.")
# template = "t_action_table.html"
# action_urls.append(reverse("db_terminate_connection"))
# elif request.query_params.get("cancel") == "enable":
# template = "action_table.html"
# else:
# template = "gen_table.html"
template = "t_action_table.html"
states = get_parameter_list(request, "state", default=[])
pids = get_parameter_list(request, "pid", default=[])
include_self = get_parameter_bool(request, "include_self", False)
records_per_db = int(request.query_params.get("records_per_db", "100"))
limit, offset = get_limit_offset(request)
data = None
with DBPerformanceStats(get_identity_username(request),
CONFIGURATOR,
database_ranking=DATABASE_RANKING) as dbp:
data = dbp.get_activity(
pid=pids,
state=states,
include_self=include_self,
limit=limit,
offset=offset,
records_per_db=records_per_db,
)
if not data:
data = [{"Response": "No data matching the criteria"}]
targets = ()
else:
targets = ("backend_pid", )
for rec in data:
set_null_display(rec)
rec["_raw_backend_pid"] = rec["backend_pid"]
rec["_attrs"] = {
"query": 'class="pre monospace"',
"state": 'class="monospace"',
"backend_pid": 'class="sans"',
"client_ip": 'class="sans"',
"backend_start": 'class="sans"',
"xact_start": 'class="sans"',
"query_start": 'class="sans"',
"state_change": 'class="sans"',
"active_time": 'class="sans"',
"wait_type_event": 'class="sans"',
}
rec["query"] = format_sql(rec["query"],
reindent=True,
indent_realigned=True,
keyword_case="upper")
fields = tuple(f for f in data[0] if not f.startswith("_")) if data else ()
page_header = "Connection Activity"
return HttpResponse(
render_template("conn_activity",
page_header,
fields,
data,
targets=targets,
template=template)
# render_template(
# "conn_activity",
# page_header,
# fields,
# data,
# targets=("backend_pid",),
# template=template,
# action_urls=action_urls
# )
)
def lockinfo(request):
"""Get any blocked and blocking process data"""
limit, offset = get_limit_offset(request)
data = None
with DBPerformanceStats(get_identity_username(request),
CONFIGURATOR) as dbp:
data = dbp.get_lock_info(limit=limit, offset=offset)
if not data:
data = [{"Result": "No blocking locks"}]
targets = []
if "blocking_pid" in data[0]:
targets.append("blocking_pid")
if "blocked_pid" in data[0]:
targets.append("blocked_pid")
# action_urls = [reverse("db_cancel_connection")]
# if request.query_params.get("terminate") == "enable":
# LOG.info("Enabling the pg_stat_activity terminate action template.")
# template = "t_action_table.html"
# action_urls.append(reverse("db_terminate_connection"))
# elif request.query_params.get("cancel") == "enable":
# template = "action_table.html"
# else:
# template = "gen_table.html"
template = "t_action_table.html"
if targets:
for rec in data:
set_null_display(rec)
rec["_attrs"] = {
"blocked_statement": 'class="pre monospace"',
"blckng_proc_curr_stmt": 'class="pre monospace"',
"blocking_pid": 'class="sans"',
"blocked_pid": 'class="sans"',
}
activity_url = f'{reverse("conn_activity")}?pids={rec["blocking_pid"]},{rec["blocked_pid"]}'
for t in targets:
rec[f"_raw_{t}"] = rec[t]
if template != "gen_table.html":
rec["blocking_pid"] = (
f'<a href="{activity_url}" title="Click this link to see the '
+
f'current activity for pids {rec["_raw_blocking_pid"]}, ' +
f'{rec["_raw_blocked_pid"]}">{rec["_raw_blocking_pid"]}</a>'
)
rec["blocked_pid"] = (
f'<a href="{activity_url}" title="Click this link to see the '
+
f'current activity for pids {rec["_raw_blocked_pid"]}, ' +
f'{rec["_raw_blocking_pid"]}">{rec["_raw_blocked_pid"]}</a>'
)
rec["blocked_statement"] = format_sql(rec["blocked_statement"],
reindent=True,
indent_realigned=True,
keyword_case="upper")
rec["blckng_proc_curr_stmt"] = format_sql(
rec["blckng_proc_curr_stmt"],
reindent=True,
indent_realigned=True,
keyword_case="upper")
page_header = "Lock Information"
return HttpResponse(
render_template(
"lock_info",
page_header,
tuple(f for f in data[0] if not f.startswith("_")) if data else (),
data,
targets=targets,
template=template,
# action_urls=action_urls,
))
