Re: WIP patch (v2) for updatable security barrier views - Mailing list pgsql-hackers

I've found an issue with updatable security barrier views. Locking is
being pushed down into the subquery. Locking is thus applied before
user-supplied quals are, so we potentially lock too many rows.

I'm looking into the code now, but in the mean time, here's a demo of
the problem:



regress=> CREATE TABLE t1(x integer, y integer);
CREATE TABLE
regress=> INSERT INTO t1(x,y) VALUES (1,1), (2,2), (3,3), (4,4);
INSERT 0 4
regress=> CREATE VIEW v1 WITH (security_barrier) AS SELECT x, y FROM t1
WHERE x % 2 = 0;
CREATE VIEW
regress=> EXPLAIN SELECT * FROM v1 FOR UPDATE;                             QUERY PLAN
-----------------------------------------------------------------------LockRows  (cost=0.00..42.43 rows=11 width=40)
-> Subquery Scan on v1  (cost=0.00..42.32 rows=11 width=40)        ->  LockRows  (cost=0.00..42.21 rows=11 width=14)
         ->  Seq Scan on t1  (cost=0.00..42.10 rows=11 width=14)                    Filter: ((x % 2) = 0)Planning time:
0.140ms
 
(6 rows)


or, preventing pushdown with a wrapper function to demonstrate the problem:

regress=> CREATE FUNCTION is_one(integer) RETURNS boolean AS $$ DECLARE
result integer; BEGIN SELECT $1 = 1

regress=> EXPLAIN SELECT * FROM v1 WHERE is_one(x) FOR UPDATE;                             QUERY PLAN
-----------------------------------------------------------------------LockRows  (cost=0.00..45.11 rows=4 width=40)  ->
Subquery Scan on v1  (cost=0.00..45.07 rows=4 width=40)        Filter: is_one(v1.x)        ->  LockRows
(cost=0.00..42.21rows=11 width=14)              ->  Seq Scan on t1  (cost=0.00..42.10 rows=11 width=14)
  Filter: ((x % 2) = 0)Planning time: 0.147 ms
 
(7 rows)






OK, so it looks like the code:


           /*            * Now deal with any PlanRowMark on this RTE by requesting a
lock            * of the same strength on the RTE copied down to the subquery.            */           rc =
get_plan_rowmark(root->rowMarks,rt_index);           if (rc != NULL)           {               switch (rc->markType)
          {                   /* .... */               }               root->rowMarks = list_delete(root->rowMarks,
rc);          }
 


isn't actually appropriate. We should _not_ be pushing locking down into
the subquery.

Instead, we should be retargeting the rowmark so it points to the new
subquery RTE, marking rows _after_filtering. We want a plan like:



regress=> EXPLAIN SELECT * FROM v1 WHERE is_one(x) FOR UPDATE;                             QUERY PLAN
-----------------------------------------------------------------------LockRows  (cost=0.00..45.11 rows=4 width=40)  ->
Subquery Scan on v1  (cost=0.00..45.07 rows=4 width=40)        Filter: is_one(v1.x)           ->  Seq Scan on t1
(cost=0.00..42.10rows=11 width=14)                  Filter: ((x % 2) = 0)Planning time: 0.147 ms
 
(7 rows)


I'm not too sure what the best way to do that is. Time permitting I'll
see if I can work out the RowMark code and set something up.


-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services