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.
On Tue, Oct 2, 2018 at 1:28 AM Dean Rasheed <dean.a.rasheed@xxxxxxxxx> wrote:
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
Attachment:
rls_performance_notes
Description: Binary data
