VMware Postgres JIRA CheckList for 'hung' queries

Products

• VMware Tanzu Greenplum

Issue/Introduction

VMware Postgres JIRA CheckList for 'hung' queries

Environment

Product Version: 15.1

Resolution

Checklist:
This is a list of artifacts that R&D would need for investigation of a 'hung' VMware Postgres query.
There is no equivalent of the GPDB 'gpmt analyze_session' tool for VMware Postgres, so the manual steps below are required for artifact collection.

The kb is based on discussion with R&D at https://vmware.slack.com/archives/C0B420B7V/p1697126760586679 
 

• Get as much detail as you can about the involved queries / transactions.
  • 'pg_stat_activity' will show the current / last executed top-level query, but it might not be enough if this is e.g. a 'plpgsql' function, or a transaction that is ultimately responsible for the hang.  
  • So, if the customer can provide the full context it will help R&D.
• 'gcore <pid>' of the query process.
• 'pstack <pid>' for the query process.
• 'strace' of the query process, with the following options (will timestamp the output, translate FDs + sockets, capture child processes, and will push the output to file):

strace -o /tmp/strace.out -yyy -tt -T -f -p <pid>

• Corresponding Postgres logs.
• Corresponding Patroni/'pg_auto_failover' logs, if relevant.
• Dump of 'pg_locks' which can then be loaded into a local DB for analysis:

psql -c "COPY (select * from pg_locks) TO '/tmp/pg_locks.out';"

• Dump of 'pg_stat_activity' which can then be loaded into local DB for analysis:

psql -c "COPY (select * from pg_stat_activity) TO '/tmp/pg_stat_activity.out';"