Subject: Re: Record last SELECT on a row?

From: Ron Johnson <[email-masked]>
Date: Wed, 17 Dec 2025 10:24:32 -0500

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

---

From: Matthias Leisi <[email-masked]>
Date: Wed, 17 Dec 2025 17:25:10 +0100

> 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.

That’s a viable suggestion, thanks a lot. Real-time is indeed not necessary, a daily (or even a weekly) cleaning of unused data is sufficient. pgaudit was anyway on the table for some other use cases, so that would fit in nicely.

Thanks,
— Matthias

---

From: Joe Conway <[email-masked]>
Date: Wed, 17 Dec 2025 11:46:14 -0500

On 12/17/25 11:25, Matthias Leisi wrote:
> 
>> 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.
> 
> That’s a viable suggestion, thanks a lot. Real-time is indeed not 
> necessary, a daily (or even a weekly) cleaning of unused data is 
> sufficient. pgaudit was anyway on the table for some other use cases, so 
> that would fit in nicely.

Possibly try using/abusing RLS?

8<-----------------
psql test
psql (19devel)
Type "help" for help.

create table t1(c1 int, c2 text);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(42,'zp');
grant select on table t1 to public;

create table a1(c1 int, t1 timestamptz);
create or replace function audit(int)
returns bool as
$$
   insert into a1 values($1, now()) returning true
$$ security definer language sql;
create policy audit_t1 ON t1 for select using (audit(c1));
alter table t1 enable row level security;

create user joe;
set session authorization joe;
select * from t1 where c1=42;

  c1 | c2
----+----
  42 | zp
(1 row)

reset session authorization;
select * from a1;

  c1 |              t1
----+-------------------------------
  42 | 2025-12-17 11:42:51.871843-05
(1 row)
8<-----------------

HTH,

-- 
Joe Conway
PostgreSQL Contributors Team
Amazon Web Services: https://aws.amazon.com

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 17 Dec 2025 12:13:33 -0500

On Wed, Dec 17, 2025 at 10:24 AM Ron Johnson <[email-masked]>
wrote:

> 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.
>

That would only give you table-level information, unless it was a
dirt-simple query with no joins and an easy to parse where clause.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

---

From: Tom Lane <[email-masked]>
Date: Wed, 17 Dec 2025 13:37:34 -0500

> Possibly try using/abusing RLS?

Cute idea, but I think it doesn't reliably address the problem of
wanting to identify the specific rows that were read.  In your toy
example it'd work, because the generated plan is

regression=> explain verbose select * from t1 where c1=42;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..343.38 rows=2 width=36)
   Output: c1, c2
   Filter: ((t1.c1 = 42) AND audit(t1.c1))
(3 rows)

so the WHERE clause is applied before the RLS filter.  But in any
slightly-more-complicated situation, like a non-leakproof WHERE
condition, the order would be reversed so the log would overstate
which rows were read.

If the application's behavior is simple and well-defined, this might
be good enough, of course.

I thought of a way that could possibly do this reliably, but it's
vastly more work than the use-case seems worth:

1. Convert the SELECTs into SELECT FOR UPDATE (you could do this
without changing the application, by interposing a view).  SELECT
FOR SHARE might be good enough, not sure.

2. Write a logical replication output plugin that parses the WAL log
well enough to identify the tuple locks taken by FOR UPDATE.

This should work to log only the rows actually read, because FOR
UPDATE is postponed to the top of the query plan, unlike RLS.

			regards, tom lane

---

From: Joe Conway <[email-masked]>
Date: Wed, 17 Dec 2025 16:36:01 -0500

> regression=> explain verbose select * from t1 where c1=42;

Sure, of course we have had requests for a leakproofness check bypass[1] 
for some use cases, and this could be one more such case.

> be good enough, of course.

FWIW when I read the original email in the thread I got the impression 
that the application behavior was pretty simple WRT this table. But of 
course I could easily be wrong...

> well enough to identify the tuple locks taken by FOR UPDATE.

Yeah this seems like a pretty heavy lift.

> UPDATE is postponed to the top of the query plan, unlike RLS.

[1] 
https://www.postgresql.org/message-id/flat/CAMxA3rsGQh9waorObOZyqrFqZ5uQ0b5D7SL6X6nh2kLhX%3D90vg%40mail.gmail.com#4a03eafc8c9660177874e11811c8f410

---

From: Matthias Leisi <[email-masked]>
Date: Thu, 18 Dec 2025 08:17:12 +0100

> FWIW when I read the original email in the thread I got the impression that the application behavior was pretty simple WRT this table. But of course I could easily be wrong...

You are not wrong. The use case is in fact the `userpref` table used by Spamassassin. Left unmaintained, and given a large-enough user base, this has the tendency to grow considerably over time, so we want to gradually remove entries not actually used any more. (And we don’t want to patch Spamassassin core code to do this by itself…)

I’ll give it a try with ab/using RLS, which we use for some other use cases anyway already.

Thanks a lot for the suggestions in the thread, much appreciated!

— Matthias

---

From: Kris Deugau <[email-masked]>
Date: Thu, 18 Dec 2025 10:03:06 -0500

Matthias Leisi wrote:
> You are not wrong. The use case is in fact the `userpref` table used by 
> Spamassassin. Left unmaintained, and given a large-enough user base, 
> this has the tendency to grow considerably over time, so we want to 
> gradually remove entries not actually used any more. (And we don’t want 
> to patch Spamassassin core code to do this by itself…)

For this particular use case, would it be easier to periodically compare 
the list of usernames with userpref data against active accounts on the 
mail system?

-kgd