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.
On Mon, 1 Oct 2018 at 21:45, Carl Sverre <sverre.carl@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;
