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 Feel free to make edits/suggestions if you feel I missed something in summarizing the solution. Also, this thread is still open to anyone who can provide a solution which does not incur an optimization penalty - however based on my new understanding of the underlying behavior I don't believe this is possible. Thank's to everyone for their contribution in figuring this out - much appreciated. Carl Sverre http://www.carlsverre.com On Mon, Oct 1, 2018 at 4:02 AM Dean Rasheed <dean.a.rash...@gmail.com> wrote: > The real issue here is to do with the visibility of the data inserted > by the trigger function from within the same command. In general, data > inserted by a command is not visible from within that same command. > > The easiest way to see what's going on is with a simple example. > Consider the following (based on the original example, but without any > RLS): > > > DROP TABLE IF EXISTS a,b; > > CREATE TABLE a (id text); > CREATE TABLE b (id text); > > CREATE OR REPLACE FUNCTION reproHandler() RETURNS TRIGGER AS $$ > BEGIN > RAISE NOTICE USING MESSAGE = 'inside trigger handler'; > INSERT INTO b (id) VALUES (NEW.id); > RETURN NEW; > END; > $$ LANGUAGE plpgsql; > > CREATE TRIGGER reproTrigger BEFORE INSERT ON a > FOR EACH ROW EXECUTE PROCEDURE reproHandler(); > > CREATE OR REPLACE FUNCTION check_b1(text) RETURNS boolean AS $$ > BEGIN > RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1)); > END > $$ LANGUAGE plpgsql STABLE; > > CREATE OR REPLACE FUNCTION check_b2(text) RETURNS boolean AS $$ > BEGIN > RETURN (EXISTS (SELECT * FROM b WHERE b.id = $1)); > END > $$ LANGUAGE plpgsql VOLATILE; > > INSERT INTO a VALUES ('xxx') > RETURNING id, check_b1(id), check_b2(id), > (EXISTS (SELECT * FROM b WHERE b.id = a.id)); > > NOTICE: inside trigger handler > id | check_b1 | check_b2 | exists > -----+----------+----------+-------- > xxx | f | t | f > (1 row) > > INSERT 0 1 > > > Notice that the functions check_b1() and check_b2() are identical, > except that check_b1() is declared STABLE and check_b2() is declared > VOLATILE, and that makes all the difference. Quoting from the > documentation for function volatility [1]: > > For functions written in SQL or in any of the standard procedural > languages, there is a second important property determined by the > volatility category, namely the visibility of any data changes that > have been made by the SQL command that is calling the function. A > VOLATILE function will see such changes, a STABLE or IMMUTABLE > function will not. > > [1] https://www.postgresql.org/docs/10/static/xfunc-volatility.html > > Also notice that the inline EXISTS query behaves in the same way as > the STABLE function -- i.e., it does not see changes made in the > current query. > > So returning to the RLS example, because the RLS SELECT policy is > defined using inline SQL, it cannot see the changes made by the > trigger. If you want to see such changes, you need to define a > VOLATILE function to do the RLS check. > > Regards, > Dean >
