How to run functions every time postgresql starts?

Question

I am using pg_trgm extension for fuzzy search. The default threshold is 0.3 as show in:

# select show_limit();
 show_limit 
------------
        0.3
(1 row)

I can change it with:

# select set_limit(0.1);
 set_limit 
-----------
       0.1
(1 row)

# select show_limit();
 show_limit 
------------
        0.1
(1 row)

But when I restart my session, the threshold is reset to default value:

# \q

$ psql -Upostgres my_db
psql (9.3.5)
Type "help" for help.

# select show_limit();
 show_limit 
------------
        0.3
(1 row)

I want to execute set_limit(0.1) every time I start postgresql. Or in other words, I want to set 0.1 as default value for threshold of pg_trgm extension. How do I do that?

Just for your sessions (some roles, but not others) or for the whole db? — Erwin Brandstetter, Nov 20 '14 at 08:40
This has been asked before: http://stackoverflow.com/questions/14608081/set-default-limit-for-pg-trgm. The initial setting seems to be hard coded. I can't think of an easy fix other than running `set_limit()` with the start of every session. — Erwin Brandstetter, Nov 20 '14 at 08:51
But is there anything resembling .bashrc for psql session? So when you start psql session, the "select set_limit(0.1)" is executed automatically? — arjunaskykok, Nov 20 '14 at 09:06

score 2 · Accepted Answer · edited May 23 '17 at 12:15

This has been asked before:

Set default limit for pg_trgm

The initial setting is hard coded in the source. One could hack the source and recompile the extension.

To address your comment: You could put a command in your psqlrc or ~/.psqlrc file. A plain and simple SELECT command in a separate line:

SELECT set_limit(0.1)

Be aware that the additional module is installed per database, while psql can connect to any database cluster and any database within that cluster. It will cause an error message when connecting to any database where pg_trgm is not installed. Nothing bad will happen, though.

On the other hand, connecting with any other client will not set the limit, this may be a bit of a trap.

pg_trgm should really provide a config setting ...

How to run functions every time postgresql starts?

1 Answers1