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