On 5 March 2015 at 23:44, Peter Geoghegan <p...@heroku.com> wrote: > On Wed, Mar 4, 2015 at 5:18 PM, Peter Geoghegan <p...@heroku.com> wrote: >> Attached patch series forms what I'm calling V3.0 of the INSERT ... ON >> CONFLICT IGNORE/UPDATE feature. (Sorry about all the threads. I feel >> this development justifies a new thread, though.) >
Hi, I had a play with this, mainly looking at the interaction with RLS. (Note there is some bitrot in gram.y that prevents the first patch from applying cleanly to HEAD) I tested using the attached script, and one test didn't behave as I expected. I believe the following should have been a valid upsert (following the update path) but actually it failed: INSERT INTO t1 VALUES (4, 0) ON CONFLICT (a) UPDATE SET b = 1; AFAICT, it is applying a WITH CHECK OPTION with qual "b > 0 AND a % 2 = 0" to the about-to-be-updated tuple (a=4, b=0), which is wrong because the "b > 0" check (policy p3) should only be applied to the post-update tuple. Possibly I'm missing something though. Regards, Dean
\set ECHO all DROP TABLE IF EXISTS t1; CREATE TABLE t1(a int PRIMARY KEY, b int); CREATE POLICY p1 ON t1 FOR INSERT WITH CHECK (b = 0); CREATE POLICY p2 ON t1 FOR UPDATE USING (a % 2 = 0); CREATE POLICY p3 ON t1 FOR UPDATE WITH CHECK (b > 0); ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; SET row_security = force; -- Valid inserts INSERT INTO t1 VALUES (1, 0); INSERT INTO t1 VALUES (2, 0); -- Insert that should fail policy p1 INSERT INTO t1 VALUES (3, 1); -- Valid update UPDATE t1 SET b = 1 WHERE a = 2; -- Update that should fail policy p2 (not an error, just no rows updated) UPDATE t1 SET b = 1 WHERE a = 1; -- Update that should fail policy p3 UPDATE t1 SET b = 0 WHERE a = 2; -- Valid upserts (insert path) INSERT INTO t1 VALUES (3, 0) ON CONFLICT (a) UPDATE SET b = 0; INSERT INTO t1 VALUES (4, 0) ON CONFLICT (a) UPDATE SET b = 0; -- Upsert (insert path) that should fail policy p1 INSERT INTO t1 VALUES (5, 1) ON CONFLICT (a) UPDATE SET b = 1; -- Upsert (update path) that should fail policy p1 INSERT INTO t1 VALUES (3, 1) ON CONFLICT (a) UPDATE SET b = 1; -- Valid upsert (update path) -- XXX: Should pass, but fails WCO on t1 -- Failing WCO: (b > 0 AND a % 2 = 0) = p3 AND p2 INSERT INTO t1 VALUES (4, 0) ON CONFLICT (a) UPDATE SET b = 1; -- Upsert (update path) that should fail policy p2 INSERT INTO t1 VALUES (3, 0) ON CONFLICT (a) UPDATE SET b = 1; -- Upsert (update path) that should fail policy p3 INSERT INTO t1 VALUES (4, 0) ON CONFLICT (a) UPDATE SET b = 0;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
