Plan caching and serialization for reuse across executions

Hi hackers, I am currently exploring whether it is possible to cache a plan generated by the PostgreSQL planner and reuse it across later executions. I understand that there are existing mechanisms like PREPARE/EXECUTE and CachedPlan in plancache.c, but these are tied to prepared statements and

Re: index prefetching

On Thu, Aug 14, 2025 at 10:12 PM Peter Geoghegan wrote: > As far as I know, we only have the following unambiguous performance > regressions (that clearly need to be fixed): > > 1. This issue. > > 2. There's about a 3% loss of throughput on pgbench SELECT. Update: I managed to fix the performance

Re: VM corruption on standby

Kirill Reshke writes: > [ v1-0001-Do-not-exit-on-postmaster-death-ever-inside-CRIT-.patch ] I do not like this patch one bit: it will replace one set of problems with another set, namely systems that fail to shut down. I think the actual bug here is the use of proc_exit(1) after observing postma

Re: [PATCH] GROUP BY ALL

On Tue, 23 Jul 2024 at 22:02, Peter Eisentraut wrote: > Looks like the main existing implementations take it to mean all entries > in the SELECT list that are not aggregate functions. > > https://duckdb.org/docs/sql/query_syntax/groupby.html#group-by-all > https://docs.databricks.com/en/sql/langua

Re: Obsolete comments in ResultRelInfo struct

On Tue, Aug 12, 2025 at 7:21 PM Etsuro Fujita wrote: > On Tue, Aug 12, 2025 at 2:03 AM Dean Rasheed wrote: > > On Mon, 11 Aug 2025 at 12:25, Etsuro Fujita wrote: > > > > > > /* > > > * Other information needed by child result relations > > > * > > > * RootResultRelInfo gives t

Re: psql: Count all table footer lines in pager setup

Erik Wienhold writes: > On 2025-08-11 20:28 +0200, Greg Sabino Mullane wrote: >> Patch looks good, applies and works. Needs a pgindent run: > Thanks for the review. Here's v2 with proper formatting. This appears to fix the problem it sets out to fix, but it looks to me like there are adjacent p

Re: Make pgoutput documentation easier to find

On Fri, Aug 15, 2025 at 1:46 PM Hayato Kuroda (Fujitsu) wrote: > > Dear Fujii-san, > > Thanks for updating the patch. Your patches look good to me. Thanks for the review! Unless there are objections, I'll commit these two patches. Regards, -- Fujii Masao

max_locks_per_transaction v18

experts, We are planning to database upgrade, and evaluate PGv18 as next new major version. Based on new release notes, one question about, "Improve the locking performance of queries that access many relations ". new share_lock_table size is based on max_locks_per_transaction, our producti

Re: ALTER DOMAIN ADD NOT NULL NOT VALID

On Thu, Aug 14, 2025 at 5:02 PM Kirill Reshke wrote: > > I have few observations. > One is whether we should now support CREATE DOMAIN ... NOT NULL NOT > VALID syntax? This could be a separate patch though. > in gram.y: CreateDomainStmt: CREATE DOMAIN_P any_name opt_as Typename ColQu

RE: Conflict detection for update_deleted in logical replication

On Saturday, August 16, 2025 7:44 AM Masahiko Sawada wrote: > Here are review comments on v62 patch: Thanks for the comments! > > > --- > +else if (IsSet(supported_opts, > SUBOPT_MAX_CONFLICT_RETENTION_DURATION) && > + strcmp(defel->defname, > "

Re: Compilation issues for HASH_STATISTICS and HASH_DEBUG options

David Rowley writes: > On Mon, 18 Aug 2025 at 13:10, Michael Paquier wrote: >> If we do that, I guess that we could just remove HASH_DEBUG, keeping >> only HASH_STATISTICS. > I wondered about that and thought that there might be an above zero > chance that someone would want HASH_DEBUG without U

Re: Compilation issues for HASH_STATISTICS and HASH_DEBUG options

On Sun, 17 Aug 2025 at 18:54, Michael Paquier wrote: > -#ifdef HASH_STATISTICS > -static long hash_accesses, > - hash_collisions, > - hash_expansions; > -#endif > > These global counters are as old as d31084e9d111. Removing them > should not be a proble

Re: Compilation issues for HASH_STATISTICS and HASH_DEBUG options

On Mon, 18 Aug 2025 at 13:10, Michael Paquier wrote: > On Mon, Aug 18, 2025 at 12:56:02PM +1200, David Rowley wrote: > > -#ifdef HASH_DEBUG > > +#if defined(HASH_DEBUG) || defined(USE_ASSERT_CHECKING) > > > > The HASH_DEBUG does not add any extra fields, so the overhead only > > amounts to the elo

Re: Compilation issues for HASH_STATISTICS and HASH_DEBUG options

On Mon, Aug 18, 2025 at 12:56:02PM +1200, David Rowley wrote: > One last thing, in order to inform people of breakages sooner than a > post-commit report from the buildfarm, I wondered is if we should do: > > -#ifdef HASH_DEBUG > +#if defined(HASH_DEBUG) || defined(USE_ASSERT_CHECKING) > > The HA

Proposal: Extending the PostgreSQL Protocol with Command Metadata

Hi hackers, I'd like to propose a backward-compatible extension to the PostgreSQL Frontend/Backend Protocol that would allow servers to send command metadata back to clients alongside query results. ## Background & Motivation Coming from a MySQL background, I've found PostgreSQL's lack of sessio

Re: Memory leak of SMgrRelation object on standby

On Sat, Aug 16, 2025 at 12:50 AM Jingtang Zhang wrote: > Back to v17, commit 21d9c3ee gave SMgrRelation a well-defined lifetime, and > smgrclose nolonger removes SMgrRelation object from the hashtable, leaving > the work to smgrdestroyall. But I find a place that relies on the removing > behavior

Re: Raw parse tree is not dumped to log

Hi Tatsuo san, Thank you very much for your review. On 2025/8/16 13:56, Tatsuo Ishii wrote: I have looked into this patch. diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 20ccb2d6b54..4370e8307f2 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml Aroun

Re: Compilation issues for HASH_STATISTICS and HASH_DEBUG options

On Mon, 18 Aug 2025 at 13:26, Tom Lane wrote: > > David Rowley writes: > > I wondered about that and thought that there might be an above zero > > chance that someone would want HASH_DEBUG without USE_ASSERT_CHECKING. > > I don't really know if that person exists. It certainly isn't me. > > Yeah,

Re: Improve hash join's handling of tuples with null join keys

> On Aug 16, 2025, at 00:52, Tom Lane wrote: > > Chao Li writes: >> With this patch, “isnull” now becomes true because of the change of strict >> op. Then the outer null join key tuple must be stored in a tuplestore. When >> an outer table contains a lot of null join key tuples, then the tup

Re: Conflict detection for update_deleted in logical replication

On Sat, Aug 16, 2025 at 5:15 AM Masahiko Sawada wrote: > > Regarding the subscription-level option vs. GUC, I don't disagree with > the current approach. > > For the record, while I agree that the subscription-level option is > more consistent with the existing retain_dead_tuples option and can >

Re: Compilation issues for HASH_STATISTICS and HASH_DEBUG options

On Mon, Aug 18, 2025 at 02:19:06PM +1200, David Rowley wrote: > On Mon, 18 Aug 2025 at 13:26, Tom Lane wrote: >> Yeah, it's really quite unclear what the existing HASH_DEBUG printout >> is good for. At least in our usage, it doesn't tell you anything >> you can't discover from static code analysi

Re: GB18030-2022 Support in PostgreSQL

On Wed, Aug 13, 2025 at 3:08 PM Chao Li wrote: > Attached is the new patch. It downloads the UCM file in make: > After regenerating the map files, there is no change found in the map files. I can confirm, thanks. We split a patch into multiple patches, it's customary include all of them, since

Re: Compilation issues for HASH_STATISTICS and HASH_DEBUG options

On Mon, Aug 18, 2025 at 05:55:33PM +1200, David Rowley wrote: > I've purposefully left references to HASH_DEBUG in the "Original > comments" section near the top of dynahash.c. That comment also > references function names that no longer exist. Ah, right, the hcreate() and hdestroy() business. LG

Re: Eagerly evict bulkwrite strategy ring

On Wed, 16 Jul 2025 at 04:51, Melanie Plageman wrote: > > Hi, > > While discussing the next steps for AIO writes in Postgres, Andres > suggested that a good starting point would be to begin evicting more > than one buffer at a time in some of the buffer access strategies that > perform writes. Thi

Re: analyze-in-stages post upgrade questions

On Mon, 2025-08-18 at 11:38 +0900, Fujii Masao wrote: > Thanks! So I've updated the patch based on my earlier comments. > Unless there are objections, I'll commit the attached version to master only. I am fine with your patch. One suggestion: > --- a/doc/src/sgml/ref/vacuumdb.sgml > +++ b/doc/sr

Re: VM corruption on standby

> On 17 Aug 2025, at 17:33, Tom Lane wrote: > > So I think the correct fix here is s/proc_exit(1)/_exit(2)/ in the > places that are responding to postmaster death. +1. But should we _exit(2) only in critical section or always in case of postmaster death? Another question that was botherin

Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring

On Sun, Aug 17, 2025 at 01:27:29AM -0500, Naga Appani wrote: > On Thu, Aug 7, 2025 at 7:35 PM Michael Paquier wrote: >> >> I really think that we should move the SQL function parts of multixact.c >> into their own new file, exposing ReadMultiXactCounts() in multixact.h... > > Done. The SQL-callab

When deleting the plpgsql function, release the CachedPlan of the function

Hi, hackers I have observed an issue where the CachedPlan corresponding to a function/procedure is not released when we execute the "DROP FUNCTION\PROCEDURE" command. A patch to resolve this problem is attached. A simple test case is as follows: Step 1 ?? create or replace procedure test_pr

Re: Proposal: Conflict log history table for Logical Replication

On Fri, Aug 15, 2025 at 2:31 PM Dilip Kumar wrote: > > Yet another question is about table names, whether we keep some > standard name like conflict_log_history_$subid or let users pass the > name. > It would be good if we can let the user specify the table_name and if she didn't specify then use

Re: Parallel Apply

On Wed, Aug 13, 2025 at 4:17 PM Zhijie Hou (Fujitsu) wrote: > > Here is the initial POC patch for this idea. > Thank you Hou-san for the patch. I did some performance benchmarking for the patch and overall, the results show substantial performance improvements. Please find the details as follows

Re: max_locks_per_transaction v18

On Mon, 18 Aug 2025 at 18:23, James Pang wrote: > not tested and any regression found either, with 10k connections, and > "max_locks_per_transaction=128", it need about more than 1GB extra > memory,right? per my understanding, max_locks_per_transaction is the max > locked objects in a tr

Re: max_locks_per_transaction v18

On Mon, 18 Aug 2025 at 15:13, James Pang wrote: > We are planning to database upgrade, and evaluate PGv18 as next new major > version. Based on new release notes, one question about, "Improve the locking > performance of queries that access many relations ". > new share_lock_table size i

Re: max_locks_per_transaction v18

not tested and any regression found either, with 10k connections, and "max_locks_per_transaction=128", it need about more than 1GB extra memory,right? per my understanding, max_locks_per_transaction is the max locked objects in a transaction (that's not an average locked objects at the same t

Re: About Custom Aggregates, C Extensions and Memory

Tom Lane t...@sss.pgh.pa.us wrote: > Hm. We do not have in-memory tables, although in some cases a temporary table > is close enough. Yay, I didn't somehow overlook them. > But there is one other pre-existing mechanism that might help you: "expanded > objects". The

make -C src/test/isolation failure in index-killtuples due to btree_gist

Hi all, (CCing committer) The following command fails, because btree_gist is not installed in the context of the isolation tests: make -C src/test/isolation/ This test has been added recently by 377b7ab14524. Some efforts have been done lately to remove any dependency to contrib/ in src/test/reg

Re: [PING] fallocate() causes btrfs to never compress postgresql files

On Fri, Aug 8, 2025 at 1:38 AM Magnus Hagander wrote: > On Tue, Aug 5, 2025 at 3:08 PM Thomas Munro wrote: >> We discussed that a bit earlier in the thread. Some problems about >> layering violations and general weirdness, I recall trying it even. >> On the flip side, is it right to declare very

Re: analyze-in-stages post upgrade questions

On Thu, Aug 7, 2025 at 3:14 AM Nathan Bossart wrote: > I also am leaning towards treating this as v19 material. It's a nontrivial > behavior change, and this option is useful for major version upgrades, > which is an area that we really don't want to surprise users too much. +1 > Furthermore, a

Re: analyze-in-stages post upgrade questions

On Thu, Aug 7, 2025 at 5:52 AM Laurenz Albe wrote: > > I understand your point. But on second thought, since the patch changes > > behavior, I'm leaning toward treating it as an improvement, so it should > > only go to master... > > I agree that this behavior change should not be backpatched. > Th

Re: Compilation issues for HASH_STATISTICS and HASH_DEBUG options

On Mon, 18 Aug 2025 at 17:06, Michael Paquier wrote: > > On Mon, Aug 18, 2025 at 02:19:06PM +1200, David Rowley wrote: > > On Mon, 18 Aug 2025 at 13:26, Tom Lane wrote: > >> Yeah, it's really quite unclear what the existing HASH_DEBUG printout > >> is good for. At least in our usage, it doesn't

RE: memory leak in logical WAL sender with pgoutput's cachectx

Dear Sawada-san, > I've not verified, but even if that's true, IIUC only one relation's > cache entry can set in_use to true at a time. I also think so. > If my understanding is > correct, when the walsender accepts invalidation messages in > logicalrep_write_tuple() as you mentioned, it doesn't

Re: Conflict detection for update_deleted in logical replication

On Mon, Aug 18, 2025 at 10:36 AM Amit Kapila wrote: > > > Given that max_conflict_retention_duration works only when > > retain_dead_tuples is enabled, why not merge these two parameters? For > > example, setting max_conflict_retention_duration=-1 means to disable > > retaining dead tuples behavi

Re: Add support for specifying tables in pg_createsubscriber.

Hi Shubham, The patch claims (e.g. in the PG docs and in the commit message) that "Column lists" and "WHERE clause" are possible, but I don't see how they can work. AFAICT the patch assumes everything to the right of the rightmost dot (.) must be the relation name. ~~~ WHERE Clause