Re: Where to store some session based info?

Dear Adrian, Dear All!

Hmmm... Then I remembered wrong.
I thought that I can't set variables without defining them in the server's
configuration somehow.

So: your example is good for us. I can set a variable and I can read this
value. It's cool.
Thank you for your help!

Best regards
dd

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> ezt írta (időpont: 2026. márc.
25., Sze, 18:51):

> On 3/25/26 10:20 AM, Durumdara wrote:
> > Hello!
> >
> > Sometimes we have to use "Current User ID", and "User Name" in the
> > Triggers to make a log into a table. These values are based on our User,
> > not in the PSQL role.
> >
> > Now we use a temporary table to do this.
> > When the user logged into the application, we created a temporary table
> > with the same name (user_info) and structure. This holds the data (id,
> > name, machine info, ip address).
> >
> > In the trigger we try to find this table (in the LOCAL_TEMPORARY schema).
> > Then we read the row into a JSON record, and then into PLPGSQL variables.
> > Tables can exist with the same name, so this is the safest solution.
> > If the User ID is invalid (none or empty) that means this is a
> > background operation, and then we don't need to log the changes.
> >
> > But maybe there is a better way to somehow store some session based data
> > and use it in the triggers.
> > Because if these selects are slow, the trigger is also slow. So when I
> > start an UPDATE command in a big table, maybe this slows down the whole
> > operation.
> >
> > Note:
> > A table with the PID key is not enough, because the PID is a repeated
> > value.
> > I logged it and in the Windows system there are many of the same values
> > (10001, 10004, etc.).
> > Ok, I can combine with session creation time. But for this I also need
> > to start a select in the pg_stat_activty table.
> >
> > So maybe you have an easier way to point to a record in a session.
> > Important: the PG servers are different, the lesser version is 11, and
> > we have only a Database Owner role. We can't configure the server.
> >
> > What is your opinion? Is there any way to get session based data?
> > As I read before, we can't set the session variables onfly.
>
> Maybe SET?:
>
> https://www.postgresql.org/docs/current/sql-set.html
>
> With LOCAL it is scoped to a transaction.
>
> Otherwise it persists for session unless a transaction is rolled back.
>
> As example:
>
> CREATE OR REPLACE FUNCTION public.session_test()
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> DECLARE
> _test_var varchar := current_setting('test.session_var', 't');
> BEGIN
> RAISE NOTICE 'Variable is %', _test_var;
> END;
> $function$
>
>
> No variable set:
>
> test=# select session_test();
> NOTICE: Variable is <NULL>
> session_test
> --------------
>
> (1 row)
>
> Variable set:
>
> test=# begin ;
> BEGIN
> test=*# set local test.session_var = 'test';
> SET
> test=*# select session_test();
> NOTICE: Variable is test
> session_test
> --------------
>
> (1 row)
>
> >
> > Best regards
> > dd
> >
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>