Re: Record last SELECT on a row?

On Wed, Dec 17, 2025 at 2:41 AM Matthias Leisi <matthias(at)leisi(dot)net> wrote:

> An application (which we can’t 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 we
> can „age out“ rows which have not been accessed in a certain 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 of
> the table, performance considerations are not top priority.
>
> If we had full control over the application, we could eg use a function to
> select the records and then update some „last read“ column. But since we
> don’t control the application, that’s not an option. On the other hand, we
> have full control over the database, so we could put some other „object“ 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.

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