MIME-Version: 1.0
References: <CAEcMXhmn7UnBC3f_HO2R7DYaKDY7L4v5qgesT6e-tRQGri1Xxw@mail.gmail.com>
In-Reply-To: <CAEcMXhmn7UnBC3f_HO2R7DYaKDY7L4v5qgesT6e-tRQGri1Xxw@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Wed, 25 Mar 2026 18:40:06 +0100
Message-ID: <CAFj8pRCTfHpwesLvF0jVsqEfuLv65WSXaiR-21bmUJEo7jceNQ@mail.gmail.com>
Subject: Re: Where to store some session based info?
To: Durumdara <durumdara@gmail.com>
Cc: Postgres General <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000068eb54064ddcc40c"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRCTfHpwesLvF0jVsqEfuLv65WSXaiR-21bmUJEo7jceNQ%40mail.gmail.com>
Precedence: bulk

--00000000000068eb54064ddcc40c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

st 25. 3. 2026 v 18:21 odes=C3=ADlatel Durumdara <durumdara@gmail.com> naps=
al:

> 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 backgroun=
d
> 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=3D# set myvar.xxx to 'hello';
select current_setting('myvar.xxx');
SET
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=90
=E2=94=82 current_setting =E2=94=82
=E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=A1
=E2=94=82 hello           =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=98
(1 row)

Regards

Pavel


>
> Best regards
> dd
>
>
>

--00000000000068eb54064ddcc40c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi</div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">st 25. 3. 2026 v=C2=A018:21 o=
des=C3=ADlatel Durumdara &lt;<a href=3D"mailto:durumdara@gmail.com">durumda=
ra@gmail.com</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex"><div dir=3D"ltr">Hello!<br><br>Sometimes we have to use &quot;=
Current User ID&quot;, and &quot;User Name&quot; in the Triggers to make a =
log into a table. These values are based on our User, not in the PSQL role.=
<div><br></div><div>Now we use=C2=A0a temporary table to do this.</div><div=
>When the user logged into the application, we created a temporary table wi=
th the same name (user_info) and structure. This holds the data (id, name, =
machine info, ip address).</div><div><br>In the trigger we try to find this=
 table (in the LOCAL_TEMPORARY schema). <br>Then we read the row into a JSO=
N record, and then into PLPGSQL variables. <br>Tables can exist with the sa=
me name, so this is the safest solution.<br>If the User=C2=A0 ID is invalid=
 (none or empty) that means this is a background operation, and then we don=
&#39;t need to log the changes.</div><div><br></div><div>But maybe there is=
 a better way to somehow store some session based data and use it in the tr=
iggers.=C2=A0</div><div>Because if these selects are slow, the trigger is a=
lso slow.=C2=A0So when I start an UPDATE command in a big table, maybe this=
 slows down the whole operation.<br><br>Note:<br>A table with the PID key i=
s not enough, because the PID is a repeated value. <br>I logged it and in t=
he Windows system there are many of the same values (10001, 10004, etc.).</=
div><div>Ok, I can combine with session creation time. But for this I also =
need to start a select in the pg_stat_activty table.<br><br>So maybe you ha=
ve an easier way to point to a record in a session.<br>Important: the PG se=
rvers are different, the lesser version is 11, and we have only a Database =
Owner role. We can&#39;t configure the server.<br><br>What is your opinion?=
 Is there any way to get session based data?<br>As I read before, we can=
9;t set the session variables onfly.<br></div></div></blockquote><div><br><=
/div><div>There are not native session variables, but you can use workaroun=
d - custom setting</div><div><br></div><span style=3D"font-family:monospace=
">(2026-03-25 18:39:04) postgres=3D# set myvar.xxx to &#39;hello&#39;;<br>s=
elect current_setting(&#39;myvar.xxx&#39;);<br>SET<br>=E2=94=8C=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90=
<br>=E2=94=82 current_setting =E2=94=82<br>=E2=95=9E=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1<br>=E2=
=94=82 hello =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=94=82<br>=E2=94=94=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=98<br>(1 row)</span></div><div class=3D"gmail_quote gmail_quote_cont=
ainer"><br></div><div class=3D"gmail_quote gmail_quote_container">Regards</=
div><div class=3D"gmail_quote gmail_quote_container"><div><br></div><div>Pa=
vel</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
><div dir=3D"ltr"><div><br>Best regards<br>dd<br></div><div><br></div><div>=
<br></div></div>
</blockquote></div></div>

--00000000000068eb54064ddcc40c--