Explain Plan button is grayed out in PostgreSQL agent (4379739)

The Explain Plan button is grayed out when viewing SQL statements in either the PostgreSQL agent or PostgreSQL PI.

In PostgreSQL PI, a message "Missing permissions. To generate the plan, ensure the Agent properties is configured with database administrator account, "Enable Administration" and "Enable Explain" options are set and you have the PostgreSQL Administrator role in your account" appears when the mouse is held over the grayed out Explain Plan button.

Cause 1 - Missing PostgreSQL permissions or PostgreSQL Administrator role in the FMS

The Explain Plan functionality requires multiple agent status property settings to be enabled and set, as well as having the PostgreSQL Administrator role in the FMS and the monitored database.

Cause 2 - Product defect

Product defect in the SPIRepository 7.3.2 cartridge. This issue is limited to being unable to view the Explain Plan only in PostgreSQL PI.

Resolution 1

To use the Explain Plan, there are several requirements

1. In the agent status properties of the PostgreSQL agent  (not PI)
   

   

   1. Set a Admin username and password with administrator access to the PostgreSQL database
   2. The Enable Administration checkbox must be enabled
   3. The Enable Explain Plans checkbox must be enabled
   4. The Enable Explain Analyze checkbox should be enabled
2. The user logged into the FMS must have the PostgreSQL Administrator role
   

   

   
   
3. The FMS user should log out and back into the FMS to use the new role in Foglight.

It's also recommended to deactivate and then activate the PostgreSQL agent after making the changes to the Agent Status Properties so the settings take effect sooner.

Resolution 2

Workaround 

Download the updated SPIRepository 7.3.2 cartridge with Hotfix HFIX-566, then unzil and install the .car file in the FMS.
Users must be logged into Supportlink to be able to view the attachment.

Status

This issue has been logged as a product defect and will be fixed in an upcoming release of the PostgreSQL PI agent.