Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1d27842

Browse files
committed
Second try at stabilizing query plans in rowsecurity regression test.
This reverts commit5cdf25e,which was almost immediately proven insufficient by the buildfarm.On second thought, the tables involved are not large enough thatautovacuum or autoanalyze would notice them; what seems far morelikely to be the culprit is the database-wide "vacuum analyze"in the concurrent gist test. That thing has given us one headachetoo many, so get rid of it in favor of targeted vacuuming of thattest's own tables only.
1 parent1676e43 commit1d27842

File tree

4 files changed

+24
-28
lines changed

4 files changed

+24
-28
lines changed

‎src/test/regress/expected/gist.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@ delete from gist_point_tbl where id % 2 = 1;
1616
-- attempt to delete pages even when they become empty, but if it did, this
1717
-- would exercise it)
1818
delete from gist_point_tbl where id < 10000;
19-
vacuum gist_point_tbl;
19+
vacuumanalyzegist_point_tbl;
2020
--
2121
-- Test Index-only plans on GiST indexes
2222
--
@@ -26,7 +26,7 @@ select box(point(0.05*i, 0.05*i), point(0.05*i, 0.05*i)),
2626
point(0.05*i, 0.05*i),
2727
circle(point(0.05*i, 0.05*i), 1.0)
2828
from generate_series(0,10000) as i;
29-
vacuum analyze;
29+
vacuum analyze gist_tbl;
3030
set enable_seqscan=off;
3131
set enable_bitmapscan=off;
3232
set enable_indexonlyscan=on;

‎src/test/regress/expected/rowsecurity.out

Lines changed: 20 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -68,8 +68,6 @@ INSERT INTO document VALUES
6868
( 6, 22, 1, 'rls_regress_user2', 'great science fiction'),
6969
( 7, 33, 2, 'rls_regress_user2', 'great technology book'),
7070
( 8, 44, 1, 'rls_regress_user2', 'great manga');
71-
VACUUM ANALYZE category;
72-
VACUUM ANALYZE document;
7371
ALTER TABLE document ENABLE ROW LEVEL SECURITY;
7472
-- user's security level must be higher than or equal to document's
7573
CREATE POLICY p1 ON document
@@ -186,19 +184,20 @@ EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
186184
(7 rows)
187185

188186
EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
189-
QUERY PLAN
190-
----------------------------------------------------------------
191-
Nested Loop
192-
Join Filter: (document.cid = category.cid)
193-
-> Subquery Scan on document
194-
Filter: f_leak(document.dtitle)
195-
-> Seq Scan on document document_1
196-
Filter: (dlevel <= $0)
197-
InitPlan 1 (returns $0)
198-
-> Index Scan using uaccount_pkey on uaccount
199-
Index Cond: (pguser = "current_user"())
187+
QUERY PLAN
188+
----------------------------------------------------------------------
189+
Hash Join
190+
Hash Cond: (category.cid = document.cid)
200191
-> Seq Scan on category
201-
(10 rows)
192+
-> Hash
193+
-> Subquery Scan on document
194+
Filter: f_leak(document.dtitle)
195+
-> Seq Scan on document document_1
196+
Filter: (dlevel <= $0)
197+
InitPlan 1 (returns $0)
198+
-> Index Scan using uaccount_pkey on uaccount
199+
Index Cond: (pguser = "current_user"())
200+
(11 rows)
202201

203202
-- only owner can change policies
204203
ALTER POLICY p1 ON document USING (true); --fail
@@ -276,12 +275,12 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
276275
QUERY PLAN
277276
----------------------------------------------------
278277
Nested Loop
279-
Join Filter: (document.cid = category.cid)
280278
-> Subquery Scan on document
281279
Filter: f_leak(document.dtitle)
282280
-> Seq Scan on document document_1
283281
Filter: (dauthor = "current_user"())
284-
-> Seq Scan on category
282+
-> Index Scan using category_pkey on category
283+
Index Cond: (cid = document.cid)
285284
(7 rows)
286285

287286
-- interaction of FK/PK constraints
@@ -296,12 +295,12 @@ SET SESSION AUTHORIZATION rls_regress_user1;
296295
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
297296
did | cid | dlevel | dauthor | dtitle | cid | cname
298297
-----+-----+--------+-------------------+--------------------+-----+------------
299-
1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel
300298
2 | 11 | 2 | rls_regress_user1 | my second novel | 11 | novel
301-
3 | 22 | 2 | rls_regress_user1 | my science fiction | |
302-
4 | 44 | 1 | rls_regress_user1 | my first manga | |
303-
5 | 44 | 2 | rls_regress_user1 | my second manga | |
299+
1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel
304300
| | | | | 33 | technology
301+
5 | 44 | 2 | rls_regress_user1 | my second manga | |
302+
4 | 44 | 1 | rls_regress_user1 | my first manga | |
303+
3 | 22 | 2 | rls_regress_user1 | my science fiction | |
305304
(6 rows)
306305

307306
DELETE FROM category WHERE cid = 33; -- fails with FK violation
@@ -313,8 +312,8 @@ SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
313312
did | cid | dlevel | dauthor | dtitle | cid | cname
314313
-----+-----+--------+-------------------+-----------------------+-----+-----------------
315314
6 | 22 | 1 | rls_regress_user2 | great science fiction | 22 | science fiction
316-
7 | 33 | 2 | rls_regress_user2 | great technology book | |
317315
8 | 44 | 1 | rls_regress_user2 | great manga | 44 | manga
316+
7 | 33 | 2 | rls_regress_user2 | great technology book | |
318317
(3 rows)
319318

320319
INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');

‎src/test/regress/sql/gist.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ delete from gist_point_tbl where id % 2 = 1;
2222
-- would exercise it)
2323
deletefrom gist_point_tblwhere id<10000;
2424

25-
vacuum gist_point_tbl;
25+
vacuumanalyzegist_point_tbl;
2626

2727

2828
--
@@ -37,7 +37,7 @@ select box(point(0.05*i, 0.05*i), point(0.05*i, 0.05*i)),
3737
circle(point(0.05*i,0.05*i),1.0)
3838
from generate_series(0,10000)as i;
3939

40-
vacuum analyze;
40+
vacuum analyze gist_tbl;
4141

4242
set enable_seqscan=off;
4343
set enable_bitmapscan=off;

‎src/test/regress/sql/rowsecurity.sql

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -82,9 +82,6 @@ INSERT INTO document VALUES
8282
(7,33,2,'rls_regress_user2','great technology book'),
8383
(8,44,1,'rls_regress_user2','great manga');
8484

85-
VACUUM ANALYZE category;
86-
VACUUM ANALYZE document;
87-
8885
ALTERTABLE document ENABLE ROW LEVEL SECURITY;
8986

9087
-- user's security level must be higher than or equal to document's

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp