Skip to content

Commit 6e74c64

Browse files
RhodiumToadRhodiumToad
committed
Teach pg_stat_statements not to ignore FOR UPDATE clauses
Performance of a SELECT FOR UPDATE may be quite distinct from the non-UPDATE version of the query, so treat all of the FOR UPDATE clause as being significant for distinguishing queries. Andrew Gierth and Vik Fearing, reviewed by Sergei Kornilov, Thomas Munro, Tom Lane Discussion: https://postgr.es/m/87h8e4hfwv.fsf@news-spur.riddles.org.uk
1 parent 0369f47 commit 6e74c64

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
const unsigned char *item, Size size);
334334
static void JumbleQuery(pgssJumbleState *jstate, Query *query);
335335
static void JumbleRangeTable(pgssJumbleState *jstate, List *rtable);
336+
static void JumbleRowMarks(pgssJumbleState *jstate, List *rowMarks);
336337
static void JumbleExpr(pgssJumbleState *jstate, Node *node);
337338
static void RecordConstLocation(pgssJumbleState *jstate, int location);
338339
static char *generate_normalized_query(pgssJumbleState *jstate, const char *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 ignore rowMarks */
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+
static void
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, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
179179

180+
--
181+
-- queries with locking clauses
182+
--
183+
CREATE TABLE pgss_a (id integer PRIMARY KEY);
184+
CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a);
185+
186+
SELECT pg_stat_statements_reset();
187+
188+
-- control query
189+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id;
190+
191+
-- test range tables
192+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE;
193+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_a;
194+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b;
195+
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"
196+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a;
197+
198+
-- test strengths
199+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR NO KEY UPDATE;
200+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR SHARE;
201+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR KEY SHARE;
202+
203+
-- test wait policies
204+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE NOWAIT;
205+
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED;
206+
207+
SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
208+
209+
DROP TABLE pgss_a, pgss_b CASCADE;
210+
180211
--
181212
-- utility commands
182213
--

0 commit comments

Comments
 (0)