Question 100864 in PostgreSQL Question posted in
The official documentation can be found here.

How can I get postgresql local configuration parameter's current value?

I’m practicing with queries optimization. In particular, tuning work_mem parameter.

There’s the DB diagram: https://postgrespro.com/docs/postgrespro/12/apks02.

I have a sql function get_passengers_and_flights:

CREATE FUNCTION get_passengers_and_flights(d timestamptz)
RETURNS TABLE(passenger_name text, flight_no text)
AS $$
  SELECT t.passenger_name, f.flight_no
  FROM tickets t
    JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
    JOIN flights f ON f.flight_id = tf.flight_id
  WHERE f.scheduled_departure >= date_trunc('month', d)
    AND f.scheduled_departure  < date_trunc('month', d) + interval '1 month'
  ORDER BY f.scheduled_departure, t.passenger_name;
$$ LANGUAGE sql;

And a simple query:

SELECT * FROM get_passengers_and_flights('2017-06-01') LIMIT 3;

By default workmem = ‘4MB’, so sorting takes some time. One of the ways to make it better to increase work_mem from ‘4MB’ to ’32MB’ or more. In the latest versions of postgresql we can do it localy (for function, table and etc.):

ALTER FUNCTION get_passengers_and_flights SET work_mem = '32MB';

So, quicksort was chosen and execution time decreased.

But the question is how can I see work_mem local value for get_passengers_and_flights function?

Obvious way to get work_mem‘s value is:

SELECT current_setting('work_mem');

The output is global value ‘4MB’, so how can I get local ’32MB’? I skimmed through system catalogs description and got nothing.

Tags:

2

Answers


  1. 0

    The following query should give the desired results:

    SELECT
  REGEXP_SUBSTR(c.setting, '[^=]+$') AS work_mem
FROM
  pg_proc
  CROSS JOIN unnest(pg_proc.proconfig) c(setting)
WHERE
  proname = 'get_passengers_and_flights'
  AND c.setting LIKE 'work_mem=%';

    NULL is returned if work_mem has not been locally configured for the function.

    Login or Signup to reply.
  2. 0

    If all you want is to see properties of the created function, use the dedicated system information function pg_get_functiondef():

    SELECT pg_get_functiondef('public.get_passengers_and_flights(timestamptz)'::regprocedure);

    This gets the function OID reliably – assuming it’s created in schema public:

    'public.get_passengers_and_flights(timestamptz)'::regprocedure

    See:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search