Hi, On Mon, 7 Apr 2025 at 16:38, Andres Freund <and...@anarazel.de> wrote: > > Hi, > > On 2025-04-04 18:36:57 +0300, Nazir Bilal Yavuz wrote: > > v6 is attached. Additional changes prior to v5: > > > > * Tests are added. > > I don't think the mark-dirty stuff is quite ready to commit. Given that, could > you change the split of the patches so that the tests are for the evict > patches alone? I don't really see a need to add the tests separately from the > code introducing the relevant functionality.
Done. > > * EvictRelUnpinnedBuffers() does not call EvictUnpinnedBuffer() now. I > > basically copied EvictUnpinnedBuffer() to the inside of > > EvictRelUnpinnedBuffers() with the needed updates. > > I think I'd instead introduce a EvictBufferInternal() that is used both by > EvictUnpinnedBuffer() and EvictRelUnpinnedBuffers(), that is expected to be > called with the buffer header lock held. Makes sense, done that way. I named it as 'EvictUnpinnedBufferInternal'. > > > > + relation_close(rel, AccessExclusiveLock); > > > > > > Hm. Why are we dropping the lock here early? It's probably ok, but it's > > > not > > > clear to me why we should do so. > > > > We are dropping the lock after we processed the relation. I didn't > > understand what could be the problem here. Why do you think it is > > early? > > Most commonly we close relations without releasing the lock, instead relying > on the lock being released at the end of the transaction. I see. I was looking at pg_prewarm as an example and copied it from there. > > > I wonder if these functions ought to also return the number of buffers > > > that > > > could not be evicted? > > > > I didn't add this as I thought this information could be gathered if wanted. > > How? A separate query will also return buffers that were since newly read in. Yes, correct. I missed that. > I think it'be better to just return all that information, given that we are > already dropping the function. Dropping objects in extension upgrades can be > painful for users, due to other objects potentially depending on the dropped > objects. So we shouldn't do that unnecessarily often. Done that way. pg_buffercache_evict_relation() and pg_buffercache_evict_all() return the total number of buffers processed. > > From 88624ea0f768ebf1a015cc0c06d2c72c822577a8 Mon Sep 17 00:00:00 2001 > > From: Nazir Bilal Yavuz <byavu...@gmail.com> > > Date: Fri, 4 Apr 2025 13:22:00 +0300 > > Subject: [PATCH v6 1/4] Return buffer flushed information in > > pg_buffercache_evict function > > > > pg_buffercache_evict() function shows buffer flushed information now. > > This feature will be used in the following commits. > > > > Author: Nazir Bilal Yavuz <byavu...@gmail.com> > > Suggested-by: Joseph Koshakow <kosh...@gmail.com> > > Reviewed-by: Aidar Imamov <a.ima...@postgrespro.ru> > > Reviewed-by: Andres Freund <and...@anarazel.de> > > Discussion: > > https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com > > --- > > src/include/storage/bufmgr.h | 2 +- > > src/backend/storage/buffer/bufmgr.c | 11 +++++++++- > > src/test/modules/test_aio/test_aio.c | 4 ++-- > > doc/src/sgml/pgbuffercache.sgml | 15 ++++++++----- > > contrib/pg_buffercache/Makefile | 3 ++- > > contrib/pg_buffercache/meson.build | 1 + > > .../pg_buffercache--1.5--1.6.sql | 9 ++++++++ > > I think I'd squash this with the following changes, as it seems unnecessary to > increase the version by multiple steps in immediately successive commits. Done. > > +/* > > + * Try to evict all the shared buffers containing provided relation's > > pages. > > + * > > + * This function is intended for testing/development use only! > > + * > > + * We need this helper function because of the following reason. > > + * ReservePrivateRefCountEntry() needs to be called before acquiring the > > + * buffer header lock but ReservePrivateRefCountEntry() is static and it > > would > > + * be better to have it as static. Hence, it can't be called from outside > > of > > + * this file. This helper function is created to bypass that problem. > > > > + * Also, we should minimize the amount of code outside of storage/buffer > > that > > + * needs to know about BuferDescs etc., it is a layering violation to > > access > > + * that outside. > > + * > > FWIW, I think this explanation is kind of true, but the real reason is that > from a layering perspective this simply is bufmgr.c's responsibility, not > pg_buffercache's. I'd probably just drop these two paragraphs. Done. > > + if (!superuser()) > > + ereport(ERROR, > > + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), > > + errmsg("must be superuser to use %s()", > > + > > "pg_buffercache_evict_relation"))); > > FWIW, I'd upate the existing superuser check in the same file to use the same > string. And perhaps I'd just put the check into a small helper function that > is shared by pg_buffercache_evict() / pg_buffercache_evict() / > pg_buffercache_evict_all(). Done. I added the pg_buffercache_superuser_check() function. > > +/* > > + * Try to evict all shared buffers. > > + */ > > +Datum > > +pg_buffercache_evict_all(PG_FUNCTION_ARGS) > > +{ > > + Datum result; > > + TupleDesc tupledesc; > > + HeapTuple tuple; > > + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; > > + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; > > + > > + int32 buffers_evicted = 0; > > + int32 buffers_flushed = 0; > > + bool flushed; > > + > > + if (get_call_result_type(fcinfo, NULL, &tupledesc) != > > TYPEFUNC_COMPOSITE) > > + elog(ERROR, "return type must be a row type"); > > + > > + if (!superuser()) > > + ereport(ERROR, > > + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), > > + errmsg("must be superuser to use %s()", > > + "pg_buffercache_evict_all"))); > > + > > + for (int buf = 1; buf <= NBuffers; buf++) > > + { > > + if (EvictUnpinnedBuffer(buf, &flushed)) > > + buffers_evicted++; > > + if (flushed) > > + buffers_flushed++; > > + } > > FWIW, I'd put this loop in bufmgr.c, in a similar helper function as for the > other cases. Done. I moved this loop to the EvictAllUnpinnedBuffers() function in bufmgr.c. > > --- 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; > > Not sure what we gain by creating this role? I missed that the role used for running the tests is already a superuser. Done. v7 is attached. I only attached pg_buffercache_evict* patch to run it on cfbot. -- Regards, Nazir Bilal Yavuz Microsoft
From 9e15f54dab47e93dd619a61b5b3eef389f819b34 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz <byavu...@gmail.com> Date: Fri, 4 Apr 2025 13:22:00 +0300 Subject: [PATCH v7] Add pg_buffercache_evict_[relation | all]() functions for testing pg_buffercache_evict() function shows buffer flushed information now. This feature will be used in the following functions. 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., ~790ms vs. ~270ms for 16GB of shared buffers). These functions are intended for developer testing and debugging purposes and are available to superusers only. Tests for this functions are added. Also, there was no test for pg_buffercache_evict(), test for this added too. Author: Nazir Bilal Yavuz <byavu...@gmail.com> Reviewed-by: Andres Freund <and...@anarazel.de> Reviewed-by: Aidar Imamov <a.ima...@postgrespro.ru> Reviewed-by: Joseph Koshakow <kosh...@gmail.com> Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com --- src/include/storage/bufmgr.h | 7 +- src/backend/storage/buffer/bufmgr.c | 145 +++++++++++++++--- src/test/modules/test_aio/test_aio.c | 4 +- doc/src/sgml/pgbuffercache.sgml | 73 ++++++++- contrib/pg_buffercache/Makefile | 3 +- .../expected/pg_buffercache.out | 61 ++++++++ contrib/pg_buffercache/meson.build | 1 + .../pg_buffercache--1.5--1.6.sql | 24 +++ contrib/pg_buffercache/pg_buffercache.control | 2 +- contrib/pg_buffercache/pg_buffercache_pages.c | 126 ++++++++++++++- contrib/pg_buffercache/sql/pg_buffercache.sql | 41 +++++ 11 files changed, 449 insertions(+), 38 deletions(-) create mode 100644 contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index f2192ceb271..29140264c24 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -304,7 +304,12 @@ extern uint32 GetAdditionalLocalPinLimit(void); extern void LimitAdditionalPins(uint32 *additional_pins); extern void LimitAdditionalLocalPins(uint32 *additional_pins); -extern bool EvictUnpinnedBuffer(Buffer buf); +extern bool EvictUnpinnedBuffer(Buffer buf, bool *flushed); +extern void EvictAllUnpinnedBuffers(int32 *buffers_evicted, + int32 *buffers_flushed); +extern void EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_processed, + int32 *buffers_evicted, + int32 *buffers_flushed); /* in buf_init.c */ extern void BufferManagerShmemInit(void); diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index ffaca5ee54d..a77f5c27940 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -6497,37 +6497,27 @@ ResOwnerPrintBufferPin(Datum res) } /* - * Try to evict the current block in a shared buffer. + * Helper function to evict unpinned buffer whose buffer header lock is + * already acquired. * * This function is intended for testing/development use only! - * - * To succeed, the buffer must not be pinned on entry, so if the caller had a - * particular block in mind, it might already have been replaced by some other - * block by the time this function runs. It's also unpinned on return, so the - * buffer might be occupied again by the time control is returned, potentially - * even by the same block. This inherent raciness without other interlocking - * makes the function unsuitable for non-testing usage. - * - * Returns true if the buffer was valid and it has now been made invalid. - * Returns false if it wasn't valid, if it couldn't be evicted due to a pin, - * or if the buffer becomes dirty again while we're trying to write it out. */ -bool -EvictUnpinnedBuffer(Buffer buf) +static bool +EvictUnpinnedBufferInternal(Buffer buf, bool *flushed) { BufferDesc *desc; uint32 buf_state; bool result; - /* Make sure we can pin the buffer. */ - ResourceOwnerEnlarge(CurrentResourceOwner); - ReservePrivateRefCountEntry(); + if (flushed) + *flushed = false; Assert(!BufferIsLocal(buf)); desc = GetBufferDescriptor(buf - 1); - /* Lock the header and check if it's valid. */ - buf_state = LockBufHdr(desc); + buf_state = pg_atomic_read_u32(&(desc->state)); + Assert(buf_state & BM_LOCKED); + if ((buf_state & BM_VALID) == 0) { UnlockBufHdr(desc, buf_state); @@ -6548,6 +6538,8 @@ EvictUnpinnedBuffer(Buffer buf) { LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_SHARED); FlushBuffer(desc, NULL, IOOBJECT_RELATION, IOCONTEXT_NORMAL); + if (flushed) + *flushed = true; LWLockRelease(BufferDescriptorGetContentLock(desc)); } @@ -6559,6 +6551,121 @@ EvictUnpinnedBuffer(Buffer buf) return result; } +/* + * Try to evict the current block in a shared buffer. + * + * This function is intended for testing/development use only! + * + * To succeed, the buffer must not be pinned on entry, so if the caller had a + * particular block in mind, it might already have been replaced by some other + * block by the time this function runs. It's also unpinned on return, so the + * buffer might be occupied again by the time control is returned, potentially + * even by the same block. This inherent raciness without other interlocking + * makes the function unsuitable for non-testing usage. + * + * *flushed is optional. If it is provided, it is set to true if the buffer + * was dirty and has been flushed. However, this does not necessarily mean + * that we flushed the buffer, it could have been flushed by someone else. + * + * Returns true if the buffer was valid and it has now been made invalid. + * Returns false if it wasn't valid, if it couldn't be evicted due to a pin, + * or if the buffer becomes dirty again while we're trying to write it out. + */ +bool +EvictUnpinnedBuffer(Buffer buf, bool *flushed) +{ + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + Assert(!BufferIsLocal(buf)); + LockBufHdr(GetBufferDescriptor(buf - 1)); + + return EvictUnpinnedBufferInternal(buf, flushed); +} + +/* + * Try to evict all the shared buffers. + * + * This function is intended for testing/development use only! + * + * buffers_evicted and buffers_flushed are optional. If they are provided, + * they are set the total number of buffers evicted and flushed respectively. + */ +void +EvictAllUnpinnedBuffers(int32 *buffers_evicted, int32 *buffers_flushed) +{ + if (buffers_evicted) + *buffers_evicted = 0; + if (buffers_flushed) + *buffers_flushed = 0; + + for (int buf = 1; buf <= NBuffers; buf++) + { + bool flushed; + + if (EvictUnpinnedBuffer(buf, &flushed) && buffers_evicted) + (*buffers_evicted)++; + if (buffers_flushed && flushed) + (*buffers_flushed)++; + } +} + +/* + * Try to evict all the shared buffers containing provided relation's pages. + * + * This function is intended for testing/development use only! + * + * The caller must hold at least AccessShareLock on the relation to prevent + * the relation from being dropped. + * + * buffers_processed, buffers_evicted and buffers_flushed are optional. If + * they are provided, they are set the total number of buffers processed, + * evicted and flushed respectively. + */ +void +EvictRelUnpinnedBuffers(Relation rel, int32 *buffers_processed, + int32 *buffers_evicted, int32 *buffers_flushed) +{ + if (buffers_processed) + *buffers_processed = 0; + if (buffers_evicted) + *buffers_evicted = 0; + if (buffers_flushed) + *buffers_flushed = 0; + + Assert(!RelationUsesLocalBuffers(rel)); + + for (int buf = 1; buf <= NBuffers; buf++) + { + uint32 buf_state = 0; + BufferDesc *bufHdr = GetBufferDescriptor(buf - 1); + + /* An unlocked precheck should be safe and saves some cycles. */ + if (!BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) + continue; + + /* Make sure we can pin the buffer. */ + ResourceOwnerEnlarge(CurrentResourceOwner); + ReservePrivateRefCountEntry(); + + buf_state = LockBufHdr(bufHdr); + if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator)) + { + bool flushed; + + if (EvictUnpinnedBufferInternal(buf, &flushed) && buffers_evicted) + (*buffers_evicted)++; + if (buffers_flushed && flushed) + (*buffers_flushed)++; + if (buffers_processed) + (*buffers_processed)++; + } + else + UnlockBufHdr(bufHdr, buf_state); + } +} + /* * Generic implementation of the AIO handle staging callback for readv/writev * on local/shared buffers. diff --git a/src/test/modules/test_aio/test_aio.c b/src/test/modules/test_aio/test_aio.c index bef0ecd9007..f9cf6ce8a26 100644 --- a/src/test/modules/test_aio/test_aio.c +++ b/src/test/modules/test_aio/test_aio.c @@ -237,7 +237,7 @@ modify_rel_block(PG_FUNCTION_ARGS) if (BufferIsLocal(buf)) InvalidateLocalBuffer(GetLocalBufferDescriptor(-buf - 1), true); else - EvictUnpinnedBuffer(buf); + EvictUnpinnedBuffer(buf, NULL); /* * Now modify the page as asked for by the caller. @@ -493,7 +493,7 @@ invalidate_rel_block(PG_FUNCTION_ARGS) if (BufferIsLocal(buf)) InvalidateLocalBuffer(GetLocalBufferDescriptor(-buf - 1), true); - else if (!EvictUnpinnedBuffer(buf)) + else if (!EvictUnpinnedBuffer(buf, NULL)) elog(ERROR, "couldn't evict"); } } diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index 802a5112d77..c7e10f97ab6 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -27,12 +27,22 @@ <primary>pg_buffercache_evict</primary> </indexterm> + <indexterm> + <primary>pg_buffercache_evict_relation</primary> + </indexterm> + + <indexterm> + <primary>pg_buffercache_evict_all</primary> + </indexterm> + <para> This module provides the <function>pg_buffercache_pages()</function> function (wrapped in the <structname>pg_buffercache</structname> view), the <function>pg_buffercache_summary()</function> function, the - <function>pg_buffercache_usage_counts()</function> function and - the <function>pg_buffercache_evict()</function> function. + <function>pg_buffercache_usage_counts()</function> function, the + <function>pg_buffercache_evict()</function>, the + <function>pg_buffercache_evict_relation()</function> function and the + <function>pg_buffercache_evict_all()</function> function. </para> <para> @@ -65,6 +75,19 @@ function is restricted to superusers only. </para> + <para> + The <function>pg_buffercache_evict_relation()</function> function allows all + unpinned shared buffers in the relation to be evicted from the buffer pool + given a relation identifier. Use of this function is restricted to + superusers only. + </para> + + <para> + The <function>pg_buffercache_evict_all()</function> function allows all + unpinned shared buffers to be evicted in the buffer pool. Use of this + function is restricted to superusers only. + </para> + <sect2 id="pgbuffercache-pg-buffercache"> <title>The <structname>pg_buffercache</structname> View</title> @@ -369,12 +392,46 @@ <para> The <function>pg_buffercache_evict()</function> function takes a buffer identifier, as shown in the <structfield>bufferid</structfield> column of - the <structname>pg_buffercache</structname> view. It returns true on success, - and false if the buffer wasn't valid, if it couldn't be evicted because it - was pinned, or if it became dirty again after an attempt to write it out. - The result is immediately out of date upon return, as the buffer might - become valid again at any time due to concurrent activity. The function is - intended for developer testing only. + the <structname>pg_buffercache</structname> view. It returns the + information about whether the buffer is evicted and flushed. evicted + column is true on success, and false if the buffer wasn't valid, if it + couldn't be evicted because it was pinned, or if it became dirty again + after an attempt to write it out. flushed column is true if the buffer is + flushed. This does not necessarily mean that buffer is flushed by us, it + might be flushed by someone else. The result is immediately out of date + upon return, as the buffer might become valid again at any time due to + concurrent activity. The function is intended for developer testing only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-evict-relation"> + <title>The <structname>pg_buffercache_evict_relation</structname> Function</title> + <para> + The <function>pg_buffercache_evict_relation()</function> function is very + similar to the <function>pg_buffercache_evict()</function> function. The + difference is that the <function>pg_buffercache_evict_relation()</function> + takes a relation identifier instead of buffer identifier. Then, it tries + to evict all buffers for all forks in that relation. It returns the number + of evicted and flushed buffers. Flushed buffers aren't necessarily flushed + by us, they might be flushed by someone else. The result is immediately + out of date upon return, as the buffer might become valid again at any time + due to concurrent activity. The function is intended for developer testing + only. + </para> + </sect2> + + <sect2 id="pgbuffercache-pg-buffercache-evict-all"> + <title>The <structname>pg_buffercache_evict_all</structname> Function</title> + <para> + The <function>pg_buffercache_evict_all()</function> function is very + similar to the <function>pg_buffercache_evict()</function> function. The + difference is, the <function>pg_buffercache_evict_all()</function> function + does not take an argument; instead it tries to evict all buffers in the + buffer pool. It returns the number of evicted and flushed buffers. + Flushed buffers aren't necessarily flushed by us, they might be flushed by + someone else. The result is immediately out of date upon return, as the + buffer might become valid again at any time due to concurrent activity. + The function is intended for developer testing only. </para> </sect2> diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile index eae65ead9e5..2a33602537e 100644 --- a/contrib/pg_buffercache/Makefile +++ b/contrib/pg_buffercache/Makefile @@ -8,7 +8,8 @@ OBJS = \ EXTENSION = pg_buffercache DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \ pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \ - pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql + pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql \ + pg_buffercache--1.5--1.6.sql PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time" REGRESS = pg_buffercache diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out index b745dc69eae..b44af0fe21e 100644 --- a/contrib/pg_buffercache/expected/pg_buffercache.out +++ b/contrib/pg_buffercache/expected/pg_buffercache.out @@ -55,3 +55,64 @@ SELECT count(*) > 0 FROM pg_buffercache_usage_counts(); t (1 row) +RESET role; +------ +---- Test pg_buffercache_evict* 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. +-- 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() +RESET ROLE; +-- 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 +-- 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) + +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; diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build index 12d1fe48717..9b2e9393410 100644 --- a/contrib/pg_buffercache/meson.build +++ b/contrib/pg_buffercache/meson.build @@ -23,6 +23,7 @@ install_data( 'pg_buffercache--1.2.sql', 'pg_buffercache--1.3--1.4.sql', 'pg_buffercache--1.4--1.5.sql', + 'pg_buffercache--1.5--1.6.sql', 'pg_buffercache.control', kwargs: contrib_data_args, ) diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql new file mode 100644 index 00000000000..e37fa66930c --- /dev/null +++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql @@ -0,0 +1,24 @@ +\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit + +DROP FUNCTION pg_buffercache_evict(integer); +CREATE FUNCTION pg_buffercache_evict( + IN int, + OUT evicted boolean, + OUT flushed boolean) +AS 'MODULE_PATHNAME', 'pg_buffercache_evict' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_evict_relation( + IN regclass, + OUT buffers_processed int4, + OUT buffers_evicted int4, + OUT buffers_flushed int4) +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_relation' +LANGUAGE C PARALLEL SAFE VOLATILE STRICT; + +CREATE FUNCTION pg_buffercache_evict_all( + OUT buffers_processed int4, + OUT buffers_evicted int4, + OUT buffers_flushed int4) +AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all' +LANGUAGE C PARALLEL SAFE VOLATILE; diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control index 5ee875f77dd..b030ba3a6fa 100644 --- a/contrib/pg_buffercache/pg_buffercache.control +++ b/contrib/pg_buffercache/pg_buffercache.control @@ -1,5 +1,5 @@ # pg_buffercache extension comment = 'examine the shared buffer cache' -default_version = '1.5' +default_version = '1.6' module_pathname = '$libdir/pg_buffercache' relocatable = true diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index 62602af1775..3fe2dbca4bb 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -9,16 +9,21 @@ #include "postgres.h" #include "access/htup_details.h" +#include "access/relation.h" #include "catalog/pg_type.h" #include "funcapi.h" #include "storage/buf_internals.h" #include "storage/bufmgr.h" +#include "utils/rel.h" #define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8 #define NUM_BUFFERCACHE_PAGES_ELEM 9 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4 +#define NUM_BUFFERCACHE_EVICT_ELEM 2 +#define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 3 +#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 3 PG_MODULE_MAGIC_EXT( .name = "pg_buffercache", @@ -67,6 +72,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_pages); PG_FUNCTION_INFO_V1(pg_buffercache_summary); PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts); PG_FUNCTION_INFO_V1(pg_buffercache_evict); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation); +PG_FUNCTION_INFO_V1(pg_buffercache_evict_all); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -352,21 +359,128 @@ pg_buffercache_usage_counts(PG_FUNCTION_ARGS) return (Datum) 0; } +/* + * Helper function to check if the user has superuser priveleges. + */ +static void +pg_buffercache_superuser_check(char *func_name) +{ + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must be superuser to use %s()", + func_name))); +} + /* * Try to evict a shared buffer. */ Datum pg_buffercache_evict(PG_FUNCTION_ARGS) { - Buffer buf = PG_GETARG_INT32(0); + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ELEM] = {0}; - if (!superuser()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to use pg_buffercache_evict function"))); + Buffer buf = PG_GETARG_INT32(0); + bool flushed; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + pg_buffercache_superuser_check("pg_buffercache_evict"); if (buf < 1 || buf > NBuffers) elog(ERROR, "bad buffer ID: %d", buf); - PG_RETURN_BOOL(EvictUnpinnedBuffer(buf)); + values[0] = BoolGetDatum(EvictUnpinnedBuffer(buf, &flushed)); + values[1] = BoolGetDatum(flushed); + + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} + +/* + * Try to evict specified relation. + */ +Datum +pg_buffercache_evict_relation(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_RELATION_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_RELATION_ELEM] = {0}; + + Oid relOid; + Relation rel; + int32 buffers_processed = 0; + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + pg_buffercache_superuser_check("pg_buffercache_evict_relation"); + + relOid = PG_GETARG_OID(0); + + rel = relation_open(relOid, AccessShareLock); + + if (RelationUsesLocalBuffers(rel)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation uses local buffers, %s() is intended to be used for shared buffers only", + "pg_buffercache_evict_relation"))); + + EvictRelUnpinnedBuffers(rel, &buffers_processed, &buffers_evicted, + &buffers_flushed); + + relation_close(rel, AccessShareLock); + + values[0] = Int32GetDatum(buffers_processed); + values[1] = Int32GetDatum(buffers_evicted); + values[2] = Int32GetDatum(buffers_flushed); + + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} + + +/* + * Try to evict all shared buffers. + */ +Datum +pg_buffercache_evict_all(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_EVICT_ALL_ELEM]; + bool nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0}; + + int32 buffers_evicted = 0; + int32 buffers_flushed = 0; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + pg_buffercache_superuser_check("pg_buffercache_evict_all"); + + EvictAllUnpinnedBuffers(&buffers_evicted, &buffers_flushed); + + values[0] = Int32GetDatum(NBuffers); + values[1] = Int32GetDatum(buffers_evicted); + values[2] = Int32GetDatum(buffers_flushed); + + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); } diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql index 944fbb1beae..2c4bd8c1e9e 100644 --- a/contrib/pg_buffercache/sql/pg_buffercache.sql +++ b/contrib/pg_buffercache/sql/pg_buffercache.sql @@ -26,3 +26,44 @@ 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* 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(); + +-- 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(); + +RESET ROLE; + +-- 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); + +-- 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(); +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; -- 2.49.0
