Dean, Thank you for catching that bug, I have updated the StackOverflow answer to account for that issue.
As for the optimization problem I mentioned, the issue seems to be that running a function that acquires a snapshot for each row is much slower than in-lining a nested loop over table b into the query. I have attached a psql session that demonstrates the exact performance issue I am referring to. Carl Sverre http://www.carlsverre.com On Tue, Oct 2, 2018 at 1:28 AM Dean Rasheed <dean.a.rash...@gmail.com> wrote: > On Mon, 1 Oct 2018 at 21:45, Carl Sverre <sverre.c...@gmail.com> wrote: > > Dean, > > Thank you for the pointer towards visibility/volatility. I think that > completely explains the effect that I am seeing in my repro. I > experimented with using a VOLATILE function for the SELECT RLS using > statement and while it completely solves my issue, it incurs too high a > cost for query execution due to the RLS policy no longer being inlined into > the scan. > > > > I have documented your answer and my experimentation on the stack > overflow answer: > > > https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s > > > > I had a quick look at that and found a bug in your implementation. The > RLS check function is defined as follows: > > CREATE OR REPLACE FUNCTION rlsCheck(id text) RETURNS TABLE (id text) AS $$ > select * from b where b.id = id > $$ LANGUAGE sql VOLATILE; > > which is incorrect because of the ambiguous reference to "id". That > final "id" will, by default, refer to the table column b.id, not the > parameter "id". Thus that function will return every row of b, and > your check won't be doing what you want. That's also going to hurt > performance, but you didn't provide enough information to diagnose the > actual performance problem that you are seeing. > > In any case, the above needs to be written as > > CREATE OR REPLACE FUNCTION rlsCheck(text) RETURNS TABLE (id text) AS $$ > select id from b where b.id = $1 > $$ LANGUAGE sql VOLATILE; > > to work as expected. > > Regards, > Dean >
rls_performance_notes
Description: Binary data
