Re: Shared Constants in PLPGSQL

In my case I don't expect these constants to be changed on a regular basis.
They will be set just once and that's it. I was thinking it would be just
as easy to set them in a proc as it would be to set them in a table. By
putting them in an immutable proc I can hopefully save a couple of compute
cycles.

On Wed, Aug 2, 2017 at 1:04 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Tue, Aug 1, 2017 at 4:56 AM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
> > What's the best way to deal with global constants in PLPGSQL. Currently
> I am
> > putting them in a function with out parameters and then calling that
> > function from every other function that needs them like this.
> >
> > CREATE OR REPLACE FUNCTION hashids.constants(
> > OUT min_alphabet_length integer,
> > OUT sep_div numeric,
> > OUT guard_div numeric,
> > OUT default_steps text,
> > OUT default_alphabet text,
> > OUT salt text)
> >
> > I am presuming that if I set this function as immutable the calls to this
> > function will be cached and will not incur much overhead.
>
> Yes. Couple things I'd suggest changing.
> 1. Make a control table, say, hashids.config and put your data there.
>
> CREATE TABLE hashids.config
> (
> min_alphabet_length integer,
> ...
> );
>
> -- one record only, please:
> CREATE UNIQUE INDEX ON hashids.config((1));
>
> 2. let's change your function to return the table type!
> CREATE OR REPLACE FUNCTION hashids.constants()
> RETURNS hashids.config AS
> $$
> SELECT * FROM hashids.config;
> $$ LANGUAGE SQL IMMUTABLE;
>
> ...here we're breaking a rule. This is technically not an immutable
> query. However, if you are calling this all over the place in
> plpgsql, you can save a few cycles since operations of the form of:
>
> DECLARE
> settings hashid.config;
> BEGIN
> settings := hashids.constants();
> ...
>
> ...will be calculated at plan time and not re-evaluated every time the
> function is called. The savings here are pretty minor but I've
> employed this trick many times because there's very little downside to
> doing so. You do have to remember to recreate the constants()
> function every time you change a setting in order to force the plan to
> re-evaluate. The main advantage over your approach is that you don't
> have to modify multiple things every time you add a new config values;
> just add a column and replace the function.
>
> merlin
>