Re: Record last SELECT on a row?

On 12/17/25 13:37, Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>> 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.


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.


> If the application's behavior is simple and well-defined, this might
> 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...

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


Yeah this seems like a pretty heavy lift.


> This should work to log only the rows actually read, because FOR
> 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

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