2

The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact. Lets take currency as an example. We store currency as numeric, but when displayed we want it to be of a certain format, say to_char(_numeric_value, 'FM999G999G999G999G990D00').

The obvious solution is to create a table that stores this format and uses is whenever needed. But since this is very commonly called function we don't want it to be slow. The solution I came up with is to re-compile format function each time config is changed. I use on-update trigger for that and also store function source-code. This way performance should be the same as if the format was hardcoded, but it doesn't seem to perform as expected.

What are down sides of this solution, am I missing something? Maybe there's an even better solution?

sqlfiddle is down for me currently, so I will post all necessary DDL/DML here.

CREATE TABLE test_config(money_format text);
CREATE TABLE test_config_fncs(fnc_name text, fnc_source TEXT);

--This is function for performance comparison.
CREATE OR REPLACE FUNCTION test_fnc_common_format_money_no_precompile(_value numeric)
    RETURNS text AS
$BODY$
BEGIN
    RETURN to_char(_value, money_format)FROM test_config;
END;
$BODY$
    LANGUAGE plpgsql STRICT STABLE;

CREATE OR REPLACE FUNCTION test_tfnc_config_fnc_precompiler()
    RETURNS trigger AS
$BODY$
BEGIN
    IF (NOT (NEW.money_format = OLD.money_format)) THEN
        EXECUTE 
        (
            SELECT 
                    replace(fnc_source, '>money_mask<', NEW.money_format)
                FROM test_config_fncs 
                WHERE fnc_name='test_fnc_common_format_money'
        );
    END IF;
    RETURN NEW;
END;
$BODY$
    LANGUAGE plpgsql;

CREATE TRIGGER test_tg_config_aiu_fnc_precompile
    AFTER UPDATE
    ON test_config
    FOR EACH ROW
    EXECUTE PROCEDURE test_tfnc_config_fnc_precompiler();
-- DDL DONE
-- INITIAL INSERTS:
INSERT INTO test_config(money_format, date_format) values (null);

INSERT INTO test_config_fncs(fnc_name, fnc_source) VALUES ('test_fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION test_fnc_common_format_money(_value numeric)
    RETURNS text AS
$BODY$
BEGIN
    RETURN to_char(_value, '>money_mask<');
END;
$BODY$
    LANGUAGE plpgsql STRICT IMMUTABLE;
$fnc$);

--TEST QUERIES:
UPDATE test_config SET money_format='FM999G999G999G999G990D00';

SELECT * FROM test_fnc_common_format_money(11111.2222); -- runtime 1700ms per 1 million queries
SELECT * FROM test_fnc_common_format_money_no_precompile(11111.2222); -- 13100ms per 1 million (!)
1
  • I see the code for fnc_common_format_money(numeric), not test_fnc_common_format_money(numeric). Typo or are you actually testing the wrong function by mistake? Nov 3, 2014 at 0:37

2 Answers 2

1

Your code looks mature, mostly, but see below! Once you fixed that, I don't see much that could go wrong with it. I share the doubts that it will improve performance much, though. And there may be better alternatives, depending on exact requirements.

General advice

For simple functions as displayed:

  1. Use simple SQL function (LANGUAGE sql) which can be inlined in the context of an outer query. SQL is preferable for simple functions that are typically nested.

  2. Remove the STRICT modifier. Contrary to what you might expect, this is probably not going to help with simple SQL functions, because it can prevent said inlining. (STRICT may help with expensive functions, though.)

Multiple users

To accommodate multiple users with different but more or less stable preferences, you could improve your design building on the typical default search_path setting serach_path = "$user",public.
Create a separate version for each user with the same function name in their respective private schemas. Unless schema-qualified, each user will see his own version of the function fnc_common_format_money(). And they can all use the same queries.

Switch schema

Or, if we are dealing with changing requirements for a single user, you could switch the search_path manually (and an arsenal of functions with it):

SET search_path = "$user", verbose_format, public

verbose_format being one name of a schema with specialized functions. Could also come before "$user".
Related:

You may not need a regime for dynamic settings any more. If you still do, extend your table test_config with a column username (or schemaname) to store a separate row for each user (schema) and adapt your code accordingly. Make it a bit simpler and safer while being at it:

Code suggestions

For the "multiple users" scenario.

CREATE OR REPLACE FUNCTION fnc_test_config_precompiler()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE (
      SELECT format(fnc_source, NEW.username, NEW.money_format)
      FROM   test_config_fncs 
      WHERE  fnc_name = 'test_fnc_common_format_money'
      );

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER test_config_precompile
AFTER INSERT OR UPDATE OF username, money_format      -- !!
ON test_config
FOR EACH ROW EXECUTE PROCEDURE fnc_test_config_precompiler();

...

INSERT INTO test_config_fncs(fnc_name, fnc_source)
VALUES ('fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION %$1I.fnc_common_format_money(_value numeric)
  RETURNS text LANGUAGE sql IMMUTABLE AS
'SELECT to_char(_value, %$2L)';
$fnc$);

Major points

  • Build one function per user setting. (Optional, but the rest of the advice applies in any case.)

  • Build conditions into the trigger directly and call trigger function only after relevant columns have been changed or on INSERT (this way you do not have a reference to OLD in the trigger function, which would not work with the additional call ON INSERT. The simple trigger only creates required functions and does not take care of orphaned versions.

  • Parameter name inside the body of an SQL function requires pg 9.2+.

  • Use format() to inject (sanitized!) parameters into the CREATE FUNCTION statement. Closing a possible security issue with SQL injection.

5
  • Yeah, I was planning to make it user specific exactly the way you told when I'm sure everything else is fine. Thank you for a good insight. Wasn't using format because it is way slower than replace, on the other hand it is not that crucial. Will definitely give it a try. When using per-column trigger is it a good idea to use FOR EACH ROW WHEN (OLD.field IS DISTINCT FROM NEW.field)?
    – Nick
    Nov 3, 2014 at 12:28
  • @Nick: Yeah, the condition for the trigger is another way. There are subtle differences to both methods. Read the fine print in the manual Nov 3, 2014 at 15:32
  • Since we're talking inlining a function calls, the approach above should increase performance a great deal when we deal with multiple choices, say some entity has full_name (not null) and short_name (nullable) and we have options to display COALESCE(full_name, short_name) or just full_name (no matter what). In this case we can still use simple LANGUAGE sql function (recompiling it every time the option is changed) and have all the advantages of using simple LANGUAGE sql STABLE functions (in this particular case).
    – Nick
    Nov 3, 2014 at 21:50
  • @Nick: Yup, that could cut some corners. Do you see numbers as expected now in your performance tests now? Nov 3, 2014 at 23:51
  • 1
    Yes. I implemented it in my current projects and it turns out to increase performance of complicated functions by 16-20% (depends on the task). I had no unexpected results in performance tests though. My only concern was the safety of this approach.
    – Nick
    Jan 22, 2015 at 12:10
0

This approach is not something I would recommend. There is way too much that can go wrong and lots of operations in the database that are unnecessary. Have you benchmarked the slowness you claim? Chances are, it's not slow at all.

I'll demonstrate:

create table fmt (k text, f text, constraint pk_fmt primary key (k));
insert into fmt values ('myfmt', 'FM999G999G999G999G990D00');
create table dt (val decimal);

do $$
begin
  for i in 1..100000
  loop
    insert into dt values (round(random()::numeric * 1000000, 2));
  end loop;
end;
$$;

select d.val, to_char(d.val, f.f)
  from dt d
  join fmt f
    -- this type of join just limits fmt to one row
    -- while not affecting the rest of the query
    on f.k = 'myfmt';

select d.val, d.val v2
  from dt d;

The two select statements, for me, return in very nearly the same time. (note the second one just adds an extra field so it's apples-to-apples).

As you can see, you can still store a global format, you just use it in the select statement. Calling your own PLPGSQL function is much slower than calling a built-in SQL function. I'll also point out that if you come from a procedural programming world (as I once did) you need to re-evaluate how you do things in SQL. Avoid procedural code wherever you can. Do it in SQL if at all possible - whatever "it" is. This is a good example.

1
  • It's almost 10 times slower to request the mask from database each time. Added my implementation of function to the topic. Could you please specify what exactly can go wrong?
    – Nick
    Nov 2, 2014 at 1:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.