Skip to content

Commit dcf7e16

Browse files
anarazelanarazel
committed
Add pg_buffercache_evict_{relation,all} functions
In addition to the added functions, the pg_buffercache_evict() function now shows whether the buffer was flushed. pg_buffercache_evict_relation(): Evicts all shared buffers in a relation at once. pg_buffercache_evict_all(): Evicts all shared buffers at once. Both functions provide mechanism to evict multiple shared buffers at once. They are designed to address the inefficiency of repeatedly calling pg_buffercache_evict() for each individual buffer, which can be time-consuming when dealing with large shared buffer pools. (e.g., ~477ms vs. ~2576ms for 16GB of fully populated shared buffers). These functions are intended for developer testing and debugging purposes and are available to superusers only. Minimal tests for the new functions are included. Also, there was no test for pg_buffercache_evict(), test for this added too. No new extension version is needed, as it was already increased this release by ba2a3c2. Author: Nazir Bilal Yavuz <byavuz81@gmail.com> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Aidar Imamov <a.imamov@postgrespro.ru> Reviewed-by: Joseph Koshakow <koshy44@gmail.com> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com
1 parent d69d45a commit dcf7e16

File tree

8 files changed

+483
-39
lines changed

8 files changed

+483
-39
lines changed

‎contrib/pg_buffercache/expected/pg_buffercache.out

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,3 +55,67 @@ SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
5555
t
5656
(1 row)
5757

58+
RESET role;
59+
------
60+
---- Test pg_buffercache_evict* functions
61+
------
62+
CREATE ROLE regress_buffercache_normal;
63+
SET ROLE regress_buffercache_normal;
64+
-- These should fail because they need to be called as SUPERUSER
65+
SELECT * FROM pg_buffercache_evict(1);
66+
ERROR: must be superuser to use pg_buffercache_evict()
67+
SELECT * FROM pg_buffercache_evict_relation(1);
68+
ERROR: must be superuser to use pg_buffercache_evict_relation()
69+
SELECT * FROM pg_buffercache_evict_all();
70+
ERROR: must be superuser to use pg_buffercache_evict_all()
71+
RESET ROLE;
72+
-- These should return nothing, because these are STRICT functions
73+
SELECT * FROM pg_buffercache_evict(NULL);
74+
buffer_evicted | buffer_flushed
75+
----------------+----------------
76+
|
77+
(1 row)
78+
79+
SELECT * FROM pg_buffercache_evict_relation(NULL);
80+
buffers_evicted | buffers_flushed | buffers_skipped
81+
-----------------+-----------------+-----------------
82+
| |
83+
(1 row)
84+
85+
-- These should fail because they are not called by valid range of buffers
86+
-- Number of the shared buffers are limited by max integer
87+
SELECT 2147483647 max_buffers \gset
88+
SELECT * FROM pg_buffercache_evict(-1);
89+
ERROR: bad buffer ID: -1
90+
SELECT * FROM pg_buffercache_evict(0);
91+
ERROR: bad buffer ID: 0
92+
SELECT * FROM pg_buffercache_evict(:max_buffers);
93+
ERROR: bad buffer ID: 2147483647
94+
-- This should fail because pg_buffercache_evict_relation() doesn't accept
95+
-- local relations
96+
CREATE TEMP TABLE temp_pg_buffercache();
97+
SELECT * FROM pg_buffercache_evict_relation('temp_pg_buffercache');
98+
ERROR: relation uses local buffers, pg_buffercache_evict_relation() is intended to be used for shared buffers only
99+
DROP TABLE temp_pg_buffercache;
100+
-- These shouldn't fail
101+
SELECT buffer_evicted IS NOT NULL FROM pg_buffercache_evict(1);
102+
?column?
103+
----------
104+
t
105+
(1 row)
106+
107+
SELECT buffers_evicted IS NOT NULL FROM pg_buffercache_evict_all();
108+
?column?
109+
----------
110+
t
111+
(1 row)
112+
113+
CREATE TABLE shared_pg_buffercache();
114+
SELECT buffers_evicted IS NOT NULL FROM pg_buffercache_evict_relation('shared_pg_buffercache');
115+
?column?
116+
----------
117+
t
118+
(1 row)
119+
120+
DROP TABLE shared_pg_buffercache;
121+
DROP ROLE regress_buffercache_normal;

‎contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,3 +20,27 @@ REVOKE ALL ON pg_buffercache_numa FROM PUBLIC;
2020

2121
GRANT EXECUTE ON FUNCTION pg_buffercache_numa_pages() TO pg_monitor;
2222
GRANT SELECT ON pg_buffercache_numa TO pg_monitor;
23+
24+
25+
DROP FUNCTION pg_buffercache_evict(integer);
26+
CREATE FUNCTION pg_buffercache_evict(
27+
IN int,
28+
OUT buffer_evicted boolean,
29+
OUT buffer_flushed boolean)
30+
AS 'MODULE_PATHNAME', 'pg_buffercache_evict'
31+
LANGUAGE C PARALLEL SAFE VOLATILE STRICT;
32+
33+
CREATE FUNCTION pg_buffercache_evict_relation(
34+
IN regclass,
35+
OUT buffers_evicted int4,
36+
OUT buffers_flushed int4,
37+
OUT buffers_skipped int4)
38+
AS 'MODULE_PATHNAME', 'pg_buffercache_evict_relation'
39+
LANGUAGE C PARALLEL SAFE VOLATILE STRICT;
40+
41+
CREATE FUNCTION pg_buffercache_evict_all(
42+
OUT buffers_evicted int4,
43+
OUT buffers_flushed int4,
44+
OUT buffers_skipped int4)
45+
AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all'
46+
LANGUAGE C PARALLEL SAFE VOLATILE;

‎contrib/pg_buffercache/pg_buffercache_pages.c

Lines changed: 122 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -9,17 +9,22 @@
99
#include "postgres.h"
1010

1111
#include "access/htup_details.h"
12+
#include "access/relation.h"
1213
#include "catalog/pg_type.h"
1314
#include "funcapi.h"
1415
#include "port/pg_numa.h"
1516
#include "storage/buf_internals.h"
1617
#include "storage/bufmgr.h"
18+
#include "utils/rel.h"
1719

1820

1921
#define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8
2022
#define NUM_BUFFERCACHE_PAGES_ELEM 9
2123
#define NUM_BUFFERCACHE_SUMMARY_ELEM 5
2224
#define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4
25+
#define NUM_BUFFERCACHE_EVICT_ELEM 2
26+
#define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 3
27+
#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 3
2328

2429
#define NUM_BUFFERCACHE_NUMA_ELEM 3
2530

@@ -93,6 +98,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_numa_pages);
9398
PG_FUNCTION_INFO_V1(pg_buffercache_summary);
9499
PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts);
95100
PG_FUNCTION_INFO_V1(pg_buffercache_evict);
101+
PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation);
102+
PG_FUNCTION_INFO_V1(pg_buffercache_evict_all);
96103

97104

98105
/* Only need to touch memory once per backend process lifetime */
@@ -637,21 +644,131 @@ pg_buffercache_usage_counts(PG_FUNCTION_ARGS)
637644
return (Datum) 0;
638645
}
639646

647+
/*
648+
* Helper function to check if the user has superuser privileges.
649+
*/
650+
static void
651+
pg_buffercache_superuser_check(char *func_name)
652+
{
653+
if (!superuser())
654+
ereport(ERROR,
655+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
656+
errmsg("must be superuser to use %s()",
657+
func_name)));
658+
}
659+
640660
/*
641661
* Try to evict a shared buffer.
642662
*/
643663
Datum
644664
pg_buffercache_evict(PG_FUNCTION_ARGS)
645665
{
666+
Datum result;
667+
TupleDesc tupledesc;
668+
HeapTuple tuple;
669+
Datum values[NUM_BUFFERCACHE_EVICT_ELEM];
670+
bool nulls[NUM_BUFFERCACHE_EVICT_ELEM] = {0};
671+
646672
Buffer buf = PG_GETARG_INT32(0);
673+
bool buffer_flushed;
647674

648-
if (!superuser())
649-
ereport(ERROR,
650-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
651-
errmsg("must be superuser to use pg_buffercache_evict function")));
675+
if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE)
676+
elog(ERROR, "return type must be a row type");
677+
678+
pg_buffercache_superuser_check("pg_buffercache_evict");
652679

653680
if (buf < 1 || buf > NBuffers)
654681
elog(ERROR, "bad buffer ID: %d", buf);
655682

656-
PG_RETURN_BOOL(EvictUnpinnedBuffer(buf));
683+
values[0] = BoolGetDatum(EvictUnpinnedBuffer(buf, &buffer_flushed));
684+
values[1] = BoolGetDatum(buffer_flushed);
685+
686+
tuple = heap_form_tuple(tupledesc, values, nulls);
687+
result = HeapTupleGetDatum(tuple);
688+
689+
PG_RETURN_DATUM(result);
690+
}
691+
692+
/*
693+
* Try to evict specified relation.
694+
*/
695+
Datum
696+
pg_buffercache_evict_relation(PG_FUNCTION_ARGS)
697+
{
698+
Datum result;
699+
TupleDesc tupledesc;
700+
HeapTuple tuple;
701+
Datum values[NUM_BUFFERCACHE_EVICT_RELATION_ELEM];
702+
bool nulls[NUM_BUFFERCACHE_EVICT_RELATION_ELEM] = {0};
703+
704+
Oid relOid;
705+
Relation rel;
706+
707+
int32 buffers_evicted = 0;
708+
int32 buffers_flushed = 0;
709+
int32 buffers_skipped = 0;
710+
711+
if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE)
712+
elog(ERROR, "return type must be a row type");
713+
714+
pg_buffercache_superuser_check("pg_buffercache_evict_relation");
715+
716+
relOid = PG_GETARG_OID(0);
717+
718+
rel = relation_open(relOid, AccessShareLock);
719+
720+
if (RelationUsesLocalBuffers(rel))
721+
ereport(ERROR,
722+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
723+
errmsg("relation uses local buffers, %s() is intended to be used for shared buffers only",
724+
"pg_buffercache_evict_relation")));
725+
726+
EvictRelUnpinnedBuffers(rel, &buffers_evicted, &buffers_flushed,
727+
&buffers_skipped);
728+
729+
relation_close(rel, AccessShareLock);
730+
731+
values[0] = Int32GetDatum(buffers_evicted);
732+
values[1] = Int32GetDatum(buffers_flushed);
733+
values[2] = Int32GetDatum(buffers_skipped);
734+
735+
tuple = heap_form_tuple(tupledesc, values, nulls);
736+
result = HeapTupleGetDatum(tuple);
737+
738+
PG_RETURN_DATUM(result);
739+
}
740+
741+
742+
/*
743+
* Try to evict all shared buffers.
744+
*/
745+
Datum
746+
pg_buffercache_evict_all(PG_FUNCTION_ARGS)
747+
{
748+
Datum result;
749+
TupleDesc tupledesc;
750+
HeapTuple tuple;
751+
Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM];
752+
bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0};
753+
754+
int32 buffers_evicted = 0;
755+
int32 buffers_flushed = 0;
756+
int32 buffers_skipped = 0;
757+
758+
if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE)
759+
elog(ERROR, "return type must be a row type");
760+
761+
pg_buffercache_superuser_check("pg_buffercache_evict_all");
762+
763+
EvictAllUnpinnedBuffers(&buffers_evicted, &buffers_flushed,
764+
&buffers_skipped);
765+
766+
values[0] = Int32GetDatum(buffers_evicted);
767+
values[1] = Int32GetDatum(buffers_flushed);
768+
values[2] = Int32GetDatum(buffers_skipped);
769+
770+
tuple = heap_form_tuple(tupledesc, values, nulls);
771+
result = HeapTupleGetDatum(tuple);
772+
773+
PG_RETURN_DATUM(result);
657774
}

‎contrib/pg_buffercache/sql/pg_buffercache.sql

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,3 +26,45 @@ SET ROLE pg_monitor;
2626
SELECT count(*) > 0 FROM pg_buffercache;
2727
SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
2828
SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
29+
RESET role;
30+
31+
32+
------
33+
---- Test pg_buffercache_evict* functions
34+
------
35+
36+
CREATE ROLE regress_buffercache_normal;
37+
SET ROLE regress_buffercache_normal;
38+
39+
-- These should fail because they need to be called as SUPERUSER
40+
SELECT * FROM pg_buffercache_evict(1);
41+
SELECT * FROM pg_buffercache_evict_relation(1);
42+
SELECT * FROM pg_buffercache_evict_all();
43+
44+
RESET ROLE;
45+
46+
-- These should return nothing, because these are STRICT functions
47+
SELECT * FROM pg_buffercache_evict(NULL);
48+
SELECT * FROM pg_buffercache_evict_relation(NULL);
49+
50+
-- These should fail because they are not called by valid range of buffers
51+
-- Number of the shared buffers are limited by max integer
52+
SELECT 2147483647 max_buffers \gset
53+
SELECT * FROM pg_buffercache_evict(-1);
54+
SELECT * FROM pg_buffercache_evict(0);
55+
SELECT * FROM pg_buffercache_evict(:max_buffers);
56+
57+
-- This should fail because pg_buffercache_evict_relation() doesn't accept
58+
-- local relations
59+
CREATE TEMP TABLE temp_pg_buffercache();
60+
SELECT * FROM pg_buffercache_evict_relation('temp_pg_buffercache');
61+
DROP TABLE temp_pg_buffercache;
62+
63+
-- These shouldn't fail
64+
SELECT buffer_evicted IS NOT NULL FROM pg_buffercache_evict(1);
65+
SELECT buffers_evicted IS NOT NULL FROM pg_buffercache_evict_all();
66+
CREATE TABLE shared_pg_buffercache();
67+
SELECT buffers_evicted IS NOT NULL FROM pg_buffercache_evict_relation('shared_pg_buffercache');
68+
DROP TABLE shared_pg_buffercache;
69+
70+
DROP ROLE regress_buffercache_normal;

0 commit comments

Comments
 (0)