Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Exclamation Unanswered: Global values in PLpgSQL

    I am working on the performance problem with the database application.
    I've got several PLpgSQL functions that are doing bulk processing of the data.
    They are calling some other functions in the loop which, in their own turn, calling third level functions. These, third level functions use some static data.
    It is not constant but for for the particular installation it practically never changes. It is currently stored in couple static tables which are used in joins by queries. If I can eliminate these joins, I can shave off 150-170 ms for every query execution which adds up to 2-2.5 minutes for the top level.

    If someone know a way to somehow reads this static data into a set of environment variables which can later be used by all underlying functions without passing them in as parameters (there are too many to pass),
    please speak up. Your input will be greatly appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by gary002g
    If someone know a way to somehow reads this static data into a set of environment variables which can later be used by all underlying functions without passing them in as parameters (there are too many to pass),
    please speak up. Your input will be greatly appreciated.
    You can define variables in postgresql.conf

    PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Customized Options

    In postgresql.conf you could define:
    Code:
    custom_variable_classes = 'myvars'
    myvars.value_1 = 'foo'
    myvars.value_2 = 'bar'
    This can then be selected inside a SQL statement:
    Code:
    select current_setting('myvars.value_1');
    Setting the variable classes in postgresql.conf requires a server restart though

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by shammat
    You can define variables in postgresql.conf

    PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Customized Options

    In postgresql.conf you could define:
    Code:
    custom_variable_classes = 'myvars'
    myvars.value_1 = 'foo'
    myvars.value_2 = 'bar'
    This can then be selected inside a SQL statement:
    Code:
    select current_setting('myvars.value_1');
    Setting the variable classes in postgresql.conf requires a server restart though
    Hm-m-m, How can I use it in a query, though?
    For example right now I have a query that selects events that occurred within a test period of the specified timestamp:
    Code:
    SELECT e.* FROM events e, metric_parameters p
    WHERE e.event_ts BETWEEN $1 - p.value::INTERVAL AND $1 + p.value::INTERVAL
    AND p.key = 'test_period';

    If I rewrite the query this way:
    Code:
    SELECT e.* FROM events e
    WHERE e.event_ts BETWEEN $1 - INTERVAL '15 minute' AND $1 + INTERVAL '15 minute';

    I am gaining 25% on performance.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by gary002g
    Hm-m-m, How can I use it in a query, though?
    I don't understand your question. I was showing an example using it in a query.

    Did you try casting the variable?
    Code:
    custom_variable_classes = 'myvars'
    myvars.interval_1 = '15 minute'
    Code:
    SELECT e.* FROM events e
    WHERE e.event_ts BETWEEN $1 - (cast(current_settings('myvars.interval_1') as interval) AND $1 + (cast(current_settings('myvars.interval_1') as interval)

  5. #5
    Join Date
    Jun 2009
    Posts
    5
    Quote Originally Posted by shammat
    I don't understand your question. I was showing an example using it in a query.

    Did you try casting the variable?
    Code:
    custom_variable_classes = 'myvars'
    myvars.interval_1 = '15 minute'
    Code:
    SELECT e.* FROM events e
    WHERE e.event_ts BETWEEN $1 - (cast(current_settings('myvars.interval_1') as interval) AND $1 + (cast(current_settings('myvars.interval_1') as interval)
    Thank you, it works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •