Question re: max_connections and SQL table alteration

[songo-github]

Is your feature request related to a problem? Please describe.

Is it possible in the config options to increase the max_connections to the database? Can you please provide some guidance on altering the query in the additional context section or what im missing in my container? When I try to alter that table as in a previous issue where you describe your installation I get the message:

ERROR:  function isnumeric(text) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883

Like what you mentioned about columns requiring a numeric value in your grafana setup that’s where I am too. Describe the solution you'd like

I have a feeling im just not versed enough in docker and this is an easy change, but I’d like to increase the max connections to the db session to be higher than 20. It appears that each time the container restarts it re tunes itself and overwrites any changes to postgresql.conf that are manually made

With the SQL query, can you guide me on that or point to where I’m going wrong? Thank you, the plugin is very functional for extending the functionality of postgres with timescaledb for grafana’ing

Additional context Add any other context or screenshots about the feature request here. max_connections = 50

ALTER TABLE public.ltss
    ADD COLUMN state_numeric double precision GENERATED ALWAYS AS (
CASE
    WHEN isnumeric((state)::text) THEN (state)::double precision
    ELSE NULL::double precision
END) STORED;

[Expaso]

Hi @songo-github !

Thanks!

I see that the script indeed misses the isnumeric() function. You can create it with the script below, and I added it to the post:

CREATE OR REPLACE FUNCTION public.isnumeric(
    text)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    IMMUTABLE STRICT PARALLEL UNSAFE
AS $BODY$
DECLARE x NUMERIC;
BEGIN
    x = $1::NUMERIC;
    RETURN TRUE;
EXCEPTION WHEN others THEN
    RETURN FALSE;
END;
$BODY$;

ALTER FUNCTION public.isnumeric(text)
    OWNER TO postgres;

As for the connections, it's true that the TimescaleDb tune is tuning on every container restart, but this should not overwritre your manual changes to that file.

But I'm curious, why increasing the max_connections? Are you hitting the limit with Home Assistant? Or do you also run a lot of other stuff on it?

[Expaso]

Hi @songo-github,

I've just release v1.1.6 This added the option to set the max connections from the configuration.

Thanks for bringing this up.

[songo-github]

Thank you, your guidance worked perfectly! Fantastic add on. Since switching to time series for all the graphs I haven't observed any wayward grafana connections to the Postgres db. Those were what was causing the error message "remaining connections reserved for non super user..." message that grafana displayed.

For what it's worth, when I increased the max memory to 2048 the tuning itself changed the max connections to 50 instead of 20