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

Commit6e74c64

Browse files
committed
Teach pg_stat_statements not to ignore FOR UPDATE clauses
Performance of a SELECT FOR UPDATE may be quite distinct from thenon-UPDATE version of the query, so treat all of the FOR UPDATE clauseas being significant for distinguishing queries.Andrew Gierth and Vik Fearing, reviewed by Sergei Kornilov, ThomasMunro, Tom LaneDiscussion:https://postgr.es/m/87h8e4hfwv.fsf@news-spur.riddles.org.uk
1 parent0369f47 commit6e74c64

File tree

3 files changed

+140
-1
lines changed

3 files changed

+140
-1
lines changed

‎contrib/pg_stat_statements/expected/pg_stat_statements.out

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -354,6 +354,93 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
354354
SELECT pg_stat_statements_reset() | 1 | 1
355355
(5 rows)
356356

357+
--
358+
-- queries with locking clauses
359+
--
360+
CREATE TABLE pgss_a (id integer PRIMARY KEY);
361+
CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a);
362+
SELECT pg_stat_statements_reset();
363+
pg_stat_statements_reset
364+
--------------------------
365+
366+
(1 row)
367+
368+
-- control query
369+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id;
370+
id | id | a_id
371+
----+----+------
372+
(0 rows)
373+
374+
-- test range tables
375+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE;
376+
id | id | a_id
377+
----+----+------
378+
(0 rows)
379+
380+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a;
381+
id | id | a_id
382+
----+----+------
383+
(0 rows)
384+
385+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b;
386+
id | id | a_id
387+
----+----+------
388+
(0 rows)
389+
390+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a, pgss_b; -- matches plain "FOR UPDATE"
391+
id | id | a_id
392+
----+----+------
393+
(0 rows)
394+
395+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a;
396+
id | id | a_id
397+
----+----+------
398+
(0 rows)
399+
400+
-- test strengths
401+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR NO KEY UPDATE;
402+
id | id | a_id
403+
----+----+------
404+
(0 rows)
405+
406+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR SHARE;
407+
id | id | a_id
408+
----+----+------
409+
(0 rows)
410+
411+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR KEY SHARE;
412+
id | id | a_id
413+
----+----+------
414+
(0 rows)
415+
416+
-- test wait policies
417+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE NOWAIT;
418+
id | id | a_id
419+
----+----+------
420+
(0 rows)
421+
422+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED;
423+
id | id | a_id
424+
----+----+------
425+
(0 rows)
426+
427+
SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
428+
calls | query
429+
-------+------------------------------------------------------------------------------------------
430+
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id
431+
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR KEY SHARE
432+
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR NO KEY UPDATE
433+
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR SHARE
434+
2 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE
435+
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE NOWAIT
436+
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a
437+
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b
438+
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a
439+
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED
440+
1 | SELECT pg_stat_statements_reset()
441+
(11 rows)
442+
443+
DROP TABLE pgss_a, pgss_b CASCADE;
357444
--
358445
-- utility commands
359446
--

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -333,6 +333,7 @@ static void AppendJumble(pgssJumbleState *jstate,
333333
constunsignedchar*item,Sizesize);
334334
staticvoidJumbleQuery(pgssJumbleState*jstate,Query*query);
335335
staticvoidJumbleRangeTable(pgssJumbleState*jstate,List*rtable);
336+
staticvoidJumbleRowMarks(pgssJumbleState*jstate,List*rowMarks);
336337
staticvoidJumbleExpr(pgssJumbleState*jstate,Node*node);
337338
staticvoidRecordConstLocation(pgssJumbleState*jstate,intlocation);
338339
staticchar*generate_normalized_query(pgssJumbleState*jstate,constchar*query,
@@ -2430,7 +2431,7 @@ JumbleQuery(pgssJumbleState *jstate, Query *query)
24302431
JumbleExpr(jstate, (Node*)query->sortClause);
24312432
JumbleExpr(jstate,query->limitOffset);
24322433
JumbleExpr(jstate,query->limitCount);
2433-
/* we ignorerowMarks */
2434+
JumbleRowMarks(jstate,query->rowMarks);
24342435
JumbleExpr(jstate,query->setOperations);
24352436
}
24362437

@@ -2489,6 +2490,26 @@ JumbleRangeTable(pgssJumbleState *jstate, List *rtable)
24892490
}
24902491
}
24912492

2493+
/*
2494+
* Jumble a rowMarks list
2495+
*/
2496+
staticvoid
2497+
JumbleRowMarks(pgssJumbleState*jstate,List*rowMarks)
2498+
{
2499+
ListCell*lc;
2500+
2501+
foreach(lc,rowMarks)
2502+
{
2503+
RowMarkClause*rowmark=lfirst_node(RowMarkClause,lc);
2504+
if (!rowmark->pushedDown)
2505+
{
2506+
APP_JUMB(rowmark->rti);
2507+
APP_JUMB(rowmark->strength);
2508+
APP_JUMB(rowmark->waitPolicy);
2509+
}
2510+
}
2511+
}
2512+
24922513
/*
24932514
* Jumble an expression tree
24942515
*

‎contrib/pg_stat_statements/sql/pg_stat_statements.sql

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -177,6 +177,37 @@ SELECT PLUS_ONE(1);
177177

178178
SELECT query, calls, rowsFROM pg_stat_statementsORDER BY query COLLATE"C";
179179

180+
--
181+
-- queries with locking clauses
182+
--
183+
CREATETABLEpgss_a (idintegerPRIMARY KEY);
184+
CREATETABLEpgss_b (idintegerPRIMARY KEY, a_idintegerREFERENCES pgss_a);
185+
186+
SELECT pg_stat_statements_reset();
187+
188+
-- control query
189+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id;
190+
191+
-- test range tables
192+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FORUPDATE;
193+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FORUPDATE OF pgss_a;
194+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FORUPDATE OF pgss_b;
195+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FORUPDATE OF pgss_a, pgss_b;-- matches plain "FOR UPDATE"
196+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FORUPDATE OF pgss_b, pgss_a;
197+
198+
-- test strengths
199+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FOR NO KEYUPDATE;
200+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FOR SHARE;
201+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FOR KEY SHARE;
202+
203+
-- test wait policies
204+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FORUPDATE NOWAIT;
205+
SELECT*FROM pgss_aJOIN pgss_bONpgss_b.a_id=pgss_a.id FORUPDATE SKIP LOCKED;
206+
207+
SELECT calls, queryFROM pg_stat_statementsORDER BY query COLLATE"C";
208+
209+
DROPTABLE pgss_a, pgss_b CASCADE;
210+
180211
--
181212
-- utility commands
182213
--

0 commit comments

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