src/bin/gp_session_state/gp_session_state.sql.in

-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- Adjust this setting to control where the objects get created.

CREATE SCHEMA session_state;
SET search_path = session_state;

BEGIN;


-- SessionState views
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- @function: 
--        gp_session_state_memory_entries_f
--
-- @in:
--
-- @out:
--        int - segment id,
--        int - session id, 
--        int - vmem in MB, 
--        int - the runaway status of the session, 
--        int - number of QEs, 
--        int - number of QEs that already freed their memory,
--        int - amount of vmem allocated at the time it was flagged as runaway
--        int - command count running at the time it was flagged as runaway
--
-- @doc:
--        UDF to retrieve memory usage entries for sessions
--        
--------------------------------------------------------------------------------

CREATE FUNCTION session_state_memory_entries_f()
RETURNS SETOF record
AS 'MODULE_PATHNAME', 'gp_session_state_memory_entries'
LANGUAGE C IMMUTABLE;

GRANT EXECUTE ON FUNCTION session_state_memory_entries_f() TO public;

--------------------------------------------------------------------------------
-- @view: 
--        session_level_memory_consumption
--
-- @doc:
--        List of memory usage entries for sessions
--        
--------------------------------------------------------------------------------

CREATE VIEW session_level_memory_consumption AS
WITH all_entries AS (
   SELECT C.*
          FROM gp_toolkit.__gp_localid, session_state_memory_entries_f() AS C (
            segid int,
            sessionid int,
            vmem_mb int,
            runaway_status int,
            qe_count int, 
            active_qe_count int, 
            dirty_qe_count int,
            runaway_vmem_mb int, 
            runaway_command_cnt int
          )
    UNION ALL
    SELECT C.*
          FROM gp_toolkit.__gp_masterid, session_state_memory_entries_f() AS C (
            segid int,
            sessionid int,
            vmem_mb int,
            runaway_status int,
            qe_count int, 
            active_qe_count int, 
            dirty_qe_count int,
            runaway_vmem_mb int, 
            runaway_command_cnt int      
          ))
SELECT S.datname, 
       M.sessionid as sess_id, 
       S.usename, 
       S.current_query as current_query, 
       M.segid, 
       M.vmem_mb,
       case when M.runaway_status = 0 then false else true end as is_runaway,
       M.qe_count,
       M.active_qe_count,
       M.dirty_qe_count,
       M.runaway_vmem_mb, 
       M.runaway_command_cnt
FROM all_entries M LEFT OUTER JOIN 
pg_stat_activity as S
ON M.sessionid = S.sess_id;

GRANT SELECT ON session_level_memory_consumption TO public;

COMMIT;