Record last SELECT on a row?

From: Matthias Leisi <matthias(at)leisi(dot)net>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Record last SELECT on a row?
Date: 2025-12-17 07:40:39
Message-ID: 287E4DF6-35A2-4062-AEBA-32DB1DE35C5D@leisi.net
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Thanks,
Matthias

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2025-12-17 08:38:43 Re: [GENERAL] Retrieving query results
Previous Message Laurenz Albe 2025-12-17 07:34:51 Re: libpq simple SELECT