From 99f8af2cfca5319e2c9d9b9f9518e5c9d2d04e84 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz Date: Fri, 4 Apr 2025 13:40:29 +0300 Subject: [PATCH v6 4/4] Extend pg_buffercache extension's tests Prior commits extended pg_buffercache extension with pg_buffercache_evict_relation(), pg_buffercache_evict_all(), pg_buffercache_mark_dirty() and pg_buffercache_mark_dirty_all() functions. Add test for these. Also, there was no test for pg_buffercache_evict(), add test for this too. Author: Nazir Bilal Yavuz Suggested-by: Aidar Imamov Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- .../expected/pg_buffercache.out | 89 +++++++++++++++++++ contrib/pg_buffercache/sql/pg_buffercache.sql | 51 +++++++++++ 2 files changed, 140 insertions(+) diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out index b745dc69eae..5d1a6f8aefa 100644 --- a/contrib/pg_buffercache/expected/pg_buffercache.out +++ b/contrib/pg_buffercache/expected/pg_buffercache.out @@ -55,3 +55,92 @@ SELECT count(*) > 0 FROM pg_buffercache_usage_counts(); t (1 row) +RESET role; +------ +---- Test pg_buffercache_evict* and pg_buffercache_mark_dirty* functions +------ +CREATE ROLE regress_buffercache_normal; +SET ROLE regress_buffercache_normal; +-- These should fail because these are STRICT functions +SELECT * FROM pg_buffercache_evict(); +ERROR: function pg_buffercache_evict() does not exist +LINE 1: SELECT * FROM pg_buffercache_evict(); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +SELECT * FROM pg_buffercache_evict_relation(); +ERROR: function pg_buffercache_evict_relation() does not exist +LINE 1: SELECT * FROM pg_buffercache_evict_relation(); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +SELECT * FROM pg_buffercache_mark_dirty(); +ERROR: function pg_buffercache_mark_dirty() does not exist +LINE 1: SELECT * FROM pg_buffercache_mark_dirty(); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +-- These should fail because they need to be called as SUPERUSER +SELECT * FROM pg_buffercache_evict(1); +ERROR: must be superuser to use pg_buffercache_evict() +SELECT * FROM pg_buffercache_evict_relation(1); +ERROR: must be superuser to use pg_buffercache_evict_relation() +SELECT * FROM pg_buffercache_evict_all(); +ERROR: must be superuser to use pg_buffercache_evict_all() +SELECT * FROM pg_buffercache_mark_dirty(1); +ERROR: must be superuser to use pg_buffercache_mark_dirty() +SELECT * FROM pg_buffercache_mark_dirty_all(); +ERROR: must be superuser to use pg_buffercache_mark_dirty_all() +RESET ROLE; +CREATE ROLE regress_buffercache_superuser SUPERUSER; +SET ROLE regress_buffercache_superuser; +-- These should fail because they are not called by valid range of buffers +-- Number of the shared buffers are limited by max integer +SELECT 2147483647 max_buffers \gset +SELECT * FROM pg_buffercache_evict(0); +ERROR: bad buffer ID: 0 +SELECT * FROM pg_buffercache_evict(:max_buffers); +ERROR: bad buffer ID: 2147483647 +SELECT * FROM pg_buffercache_mark_dirty(0); +ERROR: bad buffer ID: 0 +SELECT * FROM pg_buffercache_mark_dirty(:max_buffers); +ERROR: bad buffer ID: 2147483647 +-- This should fail because pg_buffercache_evict_relation() doesn't accept +-- local relations +CREATE TEMP TABLE temp_pg_buffercache(); +SELECT * FROM pg_buffercache_evict_relation('temp_pg_buffercache'); +ERROR: relation uses local buffers, pg_buffercache_evict_relation() is intended to be used for shared buffers only +DROP TABLE temp_pg_buffercache; +-- These shouldn't fail +SELECT COUNT(*) > 0 FROM pg_buffercache_evict(1); + ?column? +---------- + t +(1 row) + +SELECT COUNT(*) > 0 FROM pg_buffercache_evict_all(); + ?column? +---------- + t +(1 row) + +SELECT COUNT(*) > 0 FROM pg_buffercache_mark_dirty(1); + ?column? +---------- + t +(1 row) + +SELECT COUNT(*) > 0 FROM pg_buffercache_mark_dirty_all(); + ?column? +---------- + t +(1 row) + +CREATE TABLE shared_pg_buffercache(); +SELECT COUNT(*) > 0 FROM pg_buffercache_evict_relation('shared_pg_buffercache'); + ?column? +---------- + t +(1 row) + +DROP TABLE shared_pg_buffercache; +RESET ROLE; +DROP ROLE regress_buffercache_normal; +DROP ROLE regress_buffercache_superuser; diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql index 944fbb1beae..f859af15220 100644 --- a/contrib/pg_buffercache/sql/pg_buffercache.sql +++ b/contrib/pg_buffercache/sql/pg_buffercache.sql @@ -26,3 +26,54 @@ SET ROLE pg_monitor; SELECT count(*) > 0 FROM pg_buffercache; SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary(); SELECT count(*) > 0 FROM pg_buffercache_usage_counts(); +RESET role; + +------ +---- Test pg_buffercache_evict* and pg_buffercache_mark_dirty* functions +------ + +CREATE ROLE regress_buffercache_normal; +SET ROLE regress_buffercache_normal; + +-- These should fail because these are STRICT functions +SELECT * FROM pg_buffercache_evict(); +SELECT * FROM pg_buffercache_evict_relation(); +SELECT * FROM pg_buffercache_mark_dirty(); + +-- These should fail because they need to be called as SUPERUSER +SELECT * FROM pg_buffercache_evict(1); +SELECT * FROM pg_buffercache_evict_relation(1); +SELECT * FROM pg_buffercache_evict_all(); +SELECT * FROM pg_buffercache_mark_dirty(1); +SELECT * FROM pg_buffercache_mark_dirty_all(); + +RESET ROLE; +CREATE ROLE regress_buffercache_superuser SUPERUSER; +SET ROLE regress_buffercache_superuser; + +-- These should fail because they are not called by valid range of buffers +-- Number of the shared buffers are limited by max integer +SELECT 2147483647 max_buffers \gset +SELECT * FROM pg_buffercache_evict(0); +SELECT * FROM pg_buffercache_evict(:max_buffers); +SELECT * FROM pg_buffercache_mark_dirty(0); +SELECT * FROM pg_buffercache_mark_dirty(:max_buffers); + +-- This should fail because pg_buffercache_evict_relation() doesn't accept +-- local relations +CREATE TEMP TABLE temp_pg_buffercache(); +SELECT * FROM pg_buffercache_evict_relation('temp_pg_buffercache'); +DROP TABLE temp_pg_buffercache; + +-- These shouldn't fail +SELECT COUNT(*) > 0 FROM pg_buffercache_evict(1); +SELECT COUNT(*) > 0 FROM pg_buffercache_evict_all(); +SELECT COUNT(*) > 0 FROM pg_buffercache_mark_dirty(1); +SELECT COUNT(*) > 0 FROM pg_buffercache_mark_dirty_all(); +CREATE TABLE shared_pg_buffercache(); +SELECT COUNT(*) > 0 FROM pg_buffercache_evict_relation('shared_pg_buffercache'); +DROP TABLE shared_pg_buffercache; + +RESET ROLE; +DROP ROLE regress_buffercache_normal; +DROP ROLE regress_buffercache_superuser; -- 2.47.2