Re: Record last SELECT on a row?

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