MIME-Version: 1.0
References: <287E4DF6-35A2-4062-AEBA-32DB1DE35C5D@leisi.net>
In-Reply-To: <287E4DF6-35A2-4062-AEBA-32DB1DE35C5D@leisi.net>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 17 Dec 2025 10:24:32 -0500
Message-ID: <CANzqJaAAxnwa+KMiPDJsm=z=1g8HTskE2B2B-ZPG3SJ+=A4+=A@mail.gmail.com>
Subject: Re: Record last SELECT on a row?
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000099b05d064627711e"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAAxnwa%2BKMiPDJsm%3Dz%3D1g8HTskE2B2B-ZPG3SJ%2B%3DA4%2B%3DA%40mail.gmail.com>
Precedence: bulk

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

On Wed, Dec 17, 2025 at 2:41=E2=80=AFAM Matthias Leisi <matthias@leisi.net>=
 wrote:

> An application (which we can=E2=80=99t change) is accessing some Postgres=
 table,
> and we would like to record when the rows in that table were last read
> (meaning: appeared in a SELECT result). The ultimate goal would be that w=
e
> can =E2=80=9Eage out=E2=80=9C rows which have not been accessed in a cert=
ain period of time.
>
> The table contains some ten thousand rows, five columns, and we already
> record created / last updated using triggers. Almost all accesses will
> result in zero, one or very few records returned. Given the modest size o=
f
> the table, performance considerations are not top priority.
>
> If we had full control over the application, we could eg use a function t=
o
> select the records and then update some =E2=80=9Elast read=E2=80=9C colum=
n. But since we
> don=E2=80=99t control the application, that=E2=80=99s not an option. On t=
he other hand, we
> have full control over the database, so we could put some other =E2=80=9E=
object=E2=80=9C in
> lieu of the direct table.
>
> Any other ways this could be achieved?
>

pgaudit might satisfy your needs, since it would only log SELECT statements
on that one table.  You'd still have to grep the log file, so the
information wouldn't be real-time, but that's *probably* not important.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Dec 17, 2025 at 2:41=E2=80=AFAM M=
atthias Leisi &lt;<a href=3D"mailto:matthias@leisi.net">matthias@leisi.net<=
/a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex">An application (which we can=E2=
=80=99t change) is accessing some Postgres table, and we would like to reco=
rd when the rows in that table were last read (meaning: appeared in a SELEC=
T result). The ultimate goal would be that we can =E2=80=9Eage out=E2=80=9C=
 rows which have not been accessed in a certain period of time.<br>
<br>
The table contains some ten thousand rows, five columns, and we already rec=
ord created / last updated using triggers. Almost all accesses will result =
in zero, one or very few records returned. Given the modest size of the tab=
le, performance considerations are not top priority.<br>
<br>
If we had full control over the application, we could eg use a function to =
select the records and then update some =E2=80=9Elast read=E2=80=9C column.=
 But since we don=E2=80=99t control the application, that=E2=80=99s not an =
option. On the other hand, we have full control over the database, so we co=
uld put some other =E2=80=9Eobject=E2=80=9C in lieu of the direct table.<br=
>
<br>
Any other ways this could be achieved?<br></blockquote><div><br></div><div>=
pgaudit might satisfy your needs, since it would only log SELECT statements=
 on that one table.=C2=A0 You&#39;d still have to grep the log file, so the=
 information wouldn&#39;t be real-time, but that&#39;s <i>probably</i>=C2=
=A0not important.</div><div><br></div></div><span class=3D"gmail_signature_=
prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me=
, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></=
div></div></div></div>

--00000000000099b05d064627711e--