Обсуждение: how can I get "initial_value"(setting_name) ?

differentiated memory settings

Hello,

I need to implement differentiate memory settings in order to favor some
given operations.
(see function below)

I miss an admin function to reset the settings to the initial values.

Do I oversight something ?

For the case such a function is not yet available, I guess that
following may prove useful:

- reset_setting (setting_name [, is_local])
- (read)initial_value(setting_name)

best regards,

Marc Mamin



CREATE OR REPLACE FUNCTION public.cic_set_memory_plan (p_plan varchar)
returns int AS
$$
BEGIN

--LSORT, MSORT, LMAINT, RESET

if p_plan  = 'RESET' then

  SELECT set_config('work_mem', ???), false);
  SELECT set_config('maintenance_work_mem', ??, false);

elsif p_plan  = 'LSORT' then

  SELECT set_config('work_mem', current_setting('cic.large_sortmem'),
false);

elsif p_plan  = 'MSORT' then

  SELECT set_config('work_mem', current_setting('cic.mid_sortmem'),
false);

elsif p_plan  = 'LMAINT' then

  SELECT set_config('maintenance_work_mem',
current_setting('cic.large_maintenance_mem'), false);

else

  raise warning 'cic_set_memory_plan called with unsupportted parameter:
%', p_plan;

end if;

return 0;

END;
$$
language 'PLPGSQL' VOLATILE;

On Mon, 2011-09-12 at 11:59 +0200, Marc Mamin wrote:
> differentiated memory settings
>
> Hello,
>
> I need to implement differentiate memory settings in order to favor some
> given operations.
> (see function below)
>
> I miss an admin function to reset the settings to the initial values.
>
> Do I oversight something ?
>
> For the case such a function is not yet available, I guess that
> following may prove useful:
>
> - reset_setting (setting_name [, is_local])

See RESET command
(http://www.postgresql.org/docs/9.0/interactive/sql-reset.html).

> - (read)initial_value(setting_name)
>

See pg_settings catalog
(http://www.postgresql.org/docs/9.0/interactive/view-pg-settings.html,
columns boot_val and reset_val).


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Oops,
not my day :-)

many thanks,

Marc Mamin

-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info] 
Sent: Montag, 12. September 2011 12:16
To: Marc Mamin
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] how can I get "initial_value"(setting_name) ?

On Mon, 2011-09-12 at 11:59 +0200, Marc Mamin wrote:
> differentiated memory settings
> 
> Hello,
> 
> I need to implement differentiate memory settings in order to favor some
> given operations.
> (see function below)
> 
> I miss an admin function to reset the settings to the initial values.
> 
> Do I oversight something ?
> 
> For the case such a function is not yet available, I guess that
> following may prove useful:
> 
> - reset_setting (setting_name [, is_local])

See RESET command
(http://www.postgresql.org/docs/9.0/interactive/sql-reset.html).

> - (read)initial_value(setting_name)
> 

See pg_settings catalog
(http://www.postgresql.org/docs/9.0/interactive/view-pg-settings.html,
columns boot_val and reset_val).


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com