public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Record last SELECT on a row?
Date: Wed, 17 Dec 2025 10:24:32 -0500
Message-ID: <CANzqJaAAxnwa+KMiPDJsm=z=1g8HTskE2B2B-ZPG3SJ+=A4+=A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Wed, Dec 17, 2025 at 2:41 AM Matthias Leisi <[email protected]> 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!

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Record last SELECT on a row?
  In-Reply-To: <CANzqJaAAxnwa+KMiPDJsm=z=1g8HTskE2B2B-ZPG3SJ+=A4+=A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox