On 03/08/2014 01:56 AM, Tom Lane wrote: > Craig Ringer <cr...@2ndquadrant.com> writes: >> What I'm concerned about is the locking. It looks to me like we're >> causing the user to lock rows that they may not intend to lock, by >> applying a LockRows step *before* the user supplied qual. (I'm going to >> test that tomorrow, it's sleep time in Australia). > > The fact that there are two LockRows nodes seems outright broken. > The one at the top of the plan is correctly placed, but how did the > other one get in there?
I initially thought it was the updatable security barrier views code pushing the RowMark down into the generated subquery. But if I remove the pushdown code the inner LockRows node still seems to get emitted. In fact, it's not a new issue. In vanilla 9.3.1: regress=> select version(); version -------------------------------------------------------------------------------------------------------------- PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.1 20130603 (Red Hat 4.8.1-1), 64-bit (1 row) 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=> CREATE OR REPLACE FUNCTION user_qual() RETURNS boolean AS $$ BEGIN RETURN TRUE; END; $$ LANGUAGE plpgsql; CREATE FUNCTION regress=> EXPLAIN SELECT * FROM v1 WHERE user_qual() 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: user_qual() -> 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) (6 rows) so it looks like security barrier views are locking rows they should not be. I can confirm that on 9.3.1 with: CREATE OR REPLACE FUNCTION row_is(integer, integer) RETURNS boolean as $$ begin return (select $1 = $2); end; $$ language plpgsql; then in two sessions: SELECT * FROM v1 WHERE row_is(x, 2) FOR UPDATE; and SELECT * FROM v1 WHERE row_is(x, 4) FOR UPDATE; These should not block each other, but do. So there's a pre-existing bug here. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers