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:
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.
On Mon, Oct 1, 2018 at 4:02 AM Dean Rasheed <dean.a.rasheed@xxxxxxxxx> 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
