Horton
Menu

Thread

  1. Where to store some session based info?

    Durumdara <durumdara@gmail.com> — 2026-03-25T17:20:46Z

    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.

Best regards
dd

  2. Re: Where to store some session based info?

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-25T17:40:06Z

    Hi

st 25. 3. 2026 v 18:21 odesílatel Durumdara <durumdara@gmail.com> napsal:

> 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.
>

There are not native session variables, but you can use workaround - custom
setting

(2026-03-25 18:39:04) postgres=# set myvar.xxx to 'hello';
select current_setting('myvar.xxx');
SET
┌─────────────────┐
│ current_setting │
╞═════════════════╡
│ hello           │
└─────────────────┘
(1 row)

Regards

Pavel


>
> Best regards
> dd
>
>
>

  3. Re: Where to store some session based info?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-25T17:51:56Z

    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.klaver@aklaver.com

  4. Re: Where to store some session based info?

    Durumdara <durumdara@gmail.com> — 2026-03-26T13:53:47Z

    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.klaver@aklaver.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.klaver@aklaver.com
>