Re: Record last SELECT on a row?

On 12/17/25 13:37, Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)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