Pivotal Knowledge Base

Follow

How to register or log the query executed by a function?

Environment

Product Version
Pivotal Greenplum (GPDB) All version
OS RHEL 6.x
Others  

Purpose

By default, the master log does not record any of the query executed by the function onto the logs. How to log queries executed inside the function onto the master log?

Procedure

In case there is a requirement to identify all the queries executed inside the function and want to register it on the master log, the function needs to raise "RAISE LOG" command to register the query onto the master log.

Below is a working example -

  • The function:
CREATE OR REPLACE FUNCTION log_query_to_logs() 
returns   varchar 
AS $BODY$
DECLARE 
    qry VARCHAR;
BEGIN 
  qry = 'SELECT a.oid         AS "Tablespace Oid", 
                       a.spcname     AS "Tablespace name", 
                       b.fsname      AS "Filespace name", 
                       c.fselocation AS "Tablespace Location" 
                FROM   pg_tablespace a, 
                       pg_filespace b, 
                       pg_filespace_entry c 
                WHERE  a.spcfsoid = b.oid 
                       AND b.oid = c.fsefsoid 
                ORDER  BY 2, 4' ;
RAISE LOG 'Query executed: %', qry;
RETURN qry;
END;
$BODY$ 
LANGUAGE plpgsql 
VOLATILE;
  • Execute the function onto the database:
flightdata=# select log_query_to_logs();                                                                                 log_query_to_logs                        
----------------------------------------------------------------
 SELECT a.oid         AS "Tablespace Oid",                      
                        a.spcname     AS "Tablespace name",     
                        b.fsname      AS "Filespace name",      
                        c.fselocation AS "Tablespace Location"  
                 FROM   pg_tablespace a,                        
                        pg_filespace b,                         
                        pg_filespace_entry c                    
                 WHERE  a.spcfsoid = b.oid                      
                        AND b.oid = c.fsefsoid                  
                 ORDER  BY 2, 4
(1 row)
  • The master log now registers the query on its logs:
2016-08-04 11:39:17.798158 IST,"gpadmin","flightdata",p39177,th-127834304,"[local]",,2016-08-04 11:38:30 IST,645728,con45142,cmd7,seg-1,,dx31301,x645728,sx1,"LOG","00000","Query executed: SELECT a.oid         AS ""Tablespace Oid"", 
                       a.spcname     AS ""Tablespace name"", 
                       b.fsname      AS ""Filespace name"", 
                       c.fselocation AS ""Tablespace Location"" 
                FROM   pg_tablespace a, 
                       pg_filespace b, 
                       pg_filespace_entry c 
                WHERE  a.spcfsoid = b.oid 
                       AND b.oid = c.fsefsoid 
                ORDER  BY 2, 4",,,,,,"select log_query_to_logs();",0,,"pl_exec.c",2219,

 

Comments

  • Avatar
    Brendan Stephens

    Note that each function will need to be modified to include the RAISE.
    In this example, the query is captured as a parameter and passed to the RAISE.
    For large and complex functions, it may be easier to generate a generic function which dumps the prosrc for the called function from pg_proc.

    RAISE LOG 'Query executed: ' || select prosc from pg_proc where proname = '...';

Powered by Zendesk