Re: POC: enable logical decoding when wal_level = 'replica' without a server restart

2025-07-22 Thread shveta malik
On Tue, Jul 22, 2025 at 5:03 AM Masahiko Sawada wrote: > > Yes, I agree. The main patch focuses on the part where we > automatically change the effective WAL level upon the logical slot > creation and deletion (and potentially remove 'logical' from > wal_level), and other things are implemented as

RE: stats.sql might fail due to shared buffers also used by parallel tests

2025-07-22 Thread Hayato Kuroda (Fujitsu)
Dear Alexander, > And here it is [1]: > diff --strip-trailing-cr -U3 > c:/build-farm-local/buildroot/HEAD/pgsql/src/test/isolation/expected/stats.ou > t > c:/build-farm-local/buildroot/HEAD/pgsql.build/testrun/isolation/isolation/res > ults/stats.out > --- > c:/build-farm-local/buildroot/HEAD/pgsq

Re: 024_add_drop_pub.pl might fail due to deadlock

2025-07-22 Thread Amit Kapila
On Mon, Jul 21, 2025 at 6:59 PM Ajin Cherian wrote: > > Yes, this is correct. I have also verified this in my testing that > when there is a second subscription, a deadlock can still happen with > my previous patch. I have now modified the patch in tablesync worker > to take locks on both Subscrip

Re: Verify predefined LWLocks tranches have entries in wait_event_names.txt

2025-07-22 Thread Bertrand Drouvot
Hi, On Wed, Jul 23, 2025 at 12:23:37PM +0900, Michael Paquier wrote: > On Tue, Jul 22, 2025 at 02:25:13PM -0500, Nathan Bossart wrote: > > I stared at this patch some more and came up with the attached. The > > biggest change is that I've moved the list of built-in LWLock tranches to > > the exis

Re: [Buildfarm:84] Re: stats.sql might fail due to shared buffers also used by parallel tests

2025-07-22 Thread TAKATSUKA Haruka
Hello Alexander, On Wed, 23 Jul 2025 00:55:37 + Yugo Nagata - Buildfarm wrote: > > Nagata-san, could you please share the configuration of hamerkop? If it's > > running inside VM, what virtualization software is used? It's vmware ESXi 7.0.3 (21930508). This Windows enable auto-synchronize

recoveryStopsAfter is not usable when recovery_target_inclusive is false

2025-07-22 Thread Hayato Kuroda (Fujitsu)
Hi hackers, While working on [1] I found the point. When recovery_target_lsn is specified and recovery_target_inclusive is false, recoveryStopsAfter() cannot return true. ``` /* Check if the target LSN has been reached */ if (recoveryTarget == RECOVERY_TARGET_LSN &&

Re: Question on any plans to use the User Server/User Mapping to provide Logical Replication Subscriptions the user/password in an encrypted manner

2025-07-22 Thread Amit Kapila
On Tue, Jul 22, 2025 at 7:33 PM Vitale, Anthony, Sony Music wrote: > > Yes, it is exactly what I am looking for > > However, it appears that this patch (The Ability to create server with FOR > CONNECTION ONLY) was never implemented OR at least I can't find it. > > Do you know if there is any way

Re: Skipping schema changes in publication

2025-07-22 Thread shveta malik
I further tested inherited tables flow as well wrt ONLY and EXCEPT, it works well. But while reading docs for the saem, I have few concerns. 1) While explaining ONLY for EXCEPT, create-publication doc says this + This does not apply to a partitioned table, however. The partitions of +

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

2025-07-22 Thread Peter Smith
On Wed, Jul 23, 2025 at 1:58 PM Japin Li wrote: > > > Hi, Peter > > On Tue, 22 Jul 2025 at 17:46, Peter Smith wrote: > > Hi. > > > > Here are the latest v14 patches. > > > > Changes include: > > > > PATCH 0002. > > - Fixes the enable_seqscan PANIC bug reported by Japin [1] > > - Adds a new regres

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

2025-07-22 Thread Peter Smith
On Tue, Jul 22, 2025 at 8:12 PM Japin Li wrote: ... > 1. > I encountered another crash while checking VCI's internal relations. > > rm -rf demo > initdb -D demo > cat shared_preload_libraries = 'vci' > max_worker_processes = '20' > EOF > > pg_ctl -D demo start > > cat

Re: Conflict detection for update_deleted in logical replication

2025-07-22 Thread Amit Kapila
On Wed, Jul 23, 2025 at 3:51 AM Masahiko Sawada wrote: > > I've reviewed the 0001 patch and it looks good to me. > Thanks, I have pushed the 0001 patch. The patch still > has XXX comments at several places. Do we want to keep all of them > Yes, those are primarily the ideas for future optimiza

Re: IndexAmRoutine aminsertcleanup function can be NULL?

2025-07-22 Thread Japin Li
On Tue, 22 Jul 2025 at 14:36, Michael Paquier wrote: > On Thu, Jul 17, 2025 at 01:34:42PM +0800, Japin Li wrote: >> On Wed, 16 Jul 2025 at 10:08, Peter Smith wrote: >>> What's going on there? Is it just an accidentally missing "/* can be >>> NULL */" comment? >> >> It appears commit c1ec02be1d79

Re: stats.sql might fail due to shared buffers also used by parallel tests

2025-07-22 Thread Alexander Lakhin
Hello Kuroda-san, 20.07.2025 11:00, Alexander Lakhin wrote: Yeah, I made a simple test for GetSystemTimePreciseAsFileTime() and confirmed that in my VM it provides sub-microsecond precision. Regarding NTP, I think the second failure of this ilk [1] makes this cause close to impossible. (Can't w

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

2025-07-22 Thread Japin Li
Hi, Peter On Tue, 22 Jul 2025 at 17:46, Peter Smith wrote: > Hi. > > Here are the latest v14 patches. > > Changes include: > > PATCH 0002. > - Fixes the enable_seqscan PANIC bug reported by Japin [1] > - Adds a new regression test case for this > > == > [1] > https://www.postgresql.org/mess

Re: Log prefix missing for subscriber log messages received from publisher

2025-07-22 Thread vignesh C
On Tue, 22 Jul 2025 at 18:43, Fujii Masao wrote: > > On Tue, Jul 22, 2025 at 9:49 PM Álvaro Herrera wrote: > > > > On 2025-Jul-22, Fujii Masao wrote: > > > > > On Tue, Jul 22, 2025 at 8:47 PM Álvaro Herrera > > > wrote: > > > > > > Oh yeah, I should have remembered this -- see commit 213c959a29

Re: Verify predefined LWLocks tranches have entries in wait_event_names.txt

2025-07-22 Thread Michael Paquier
On Tue, Jul 22, 2025 at 02:25:13PM -0500, Nathan Bossart wrote: > I stared at this patch some more and came up with the attached. The > biggest change is that I've moved the list of built-in LWLock tranches to > the existing lwlocklist.h file. That simplifies the patch and centralizes > these lis

Re: track generic and custom plans in pg_stat_statements

2025-07-22 Thread Michael Paquier
On Tue, Jul 22, 2025 at 02:52:49PM -0500, Sami Imseih wrote: >> We will need a field to store an enum. let's call it CachedPlanType >> with the types of cached plan. We need to be able to differentiate >> when cached plans are not used, so a simple boolean is not >> sufficient. Guess so. >> We ca

Re: Optimize LISTEN/NOTIFY

2025-07-22 Thread Thomas Munro
On Wed, Jul 23, 2025 at 1:39 PM Joel Jacobson wrote: > In their patch, in asyn.c's SignalBackends(), they do > SendInterrupt(INTERRUPT_ASYNC_NOTIFY, procno) instead of > SendProcSignal(pid, PROCSIG_NOTIFY_INTERRUPT, procnos[i]). They don't > seem to check if the backend is already signalled or not

Re: Re-archive the WAL on standby with archive_mode=always?

2025-07-22 Thread Japin Li
On Wed, 23 Jul 2025 at 09:24, Fujii Masao wrote: > On Mon, Jul 21, 2025 at 2:38 PM Japin Li wrote: >> >> >> Hi, hackers, >> >> I was recently reviewing the KeepFileRestoredFromArchive() function and came >> across a section that raised a question for me: >> >> /* >> * Create .done file f

Re: Optimize LISTEN/NOTIFY

2025-07-22 Thread Joel Jacobson
On Thu, Jul 17, 2025, at 09:43, Joel Jacobson wrote: > On Wed, Jul 16, 2025, at 02:20, Rishu Bagga wrote: >> If we are doing this optimization, why not maintain a list of backends >> for each channel, and only wake up those channels? > > Thanks for a contributing a great idea, it actually turned ou

Re: Metadata and record block access stats for indexes

2025-07-22 Thread Mircea Cadariu
Hi Frédéric, Thanks a lot for trying out my (first) patch! Much appreciated. On 20/07/2025 21:54, Frédéric Yhuel wrote: Your patch applies cleanly and seems to work well. Cool! because most of the index non-leaf pages should be in the cache. Right? Yes indeed, it's an assumption in the

Re: index prefetching

2025-07-22 Thread Peter Geoghegan
On Tue, Jul 22, 2025 at 8:37 PM Tomas Vondra wrote: > > I happen to think that that's a very unrealistic assumption. Most > > standard benchmarks have indexes that almost all look fairly similar > > to pgbench_accounts_pkey, from the point of view of "heap page blocks > > per leaf page". There are

Re: index prefetching

2025-07-22 Thread Thomas Munro
On Wed, Jul 23, 2025 at 1:55 AM Tomas Vondra wrote: > On 7/21/25 14:39, Thomas Munro wrote: > > Here also are some alternative experimental patches for preserving > > accumulated look-ahead distance better in cases like that. Needs more > > exploration... thoughts/ideas welcome... > > Thanks! I'l

Re: index prefetching

2025-07-22 Thread Tomas Vondra
On 7/23/25 02:59, Andres Freund wrote: > Hi, > > On 2025-07-23 02:50:04 +0200, Tomas Vondra wrote: >> But I don't see why would this have any effect on the prefetch distance, >> queue depth etc. Or why decreasing INDEX_SCAN_MAX_BATCHES should improve >> that. I'd have expected exactly the opposite

Re: index prefetching

2025-07-22 Thread Tomas Vondra
On 7/23/25 02:39, Peter Geoghegan wrote: > On Tue, Jul 22, 2025 at 8:08 PM Andres Freund wrote: >> My response was specific to Tomas' comment that for many queries, which tend >> to be more complicated than the toys we are using here, there will be CPU >> costs in the query. > > Got it. That make

Re: track generic and custom plans in pg_stat_statements

2025-07-22 Thread Sami Imseih
> > I know Michael opposed the idea of carrying these structures, > > at least CachedPlan, to Executor hooks ( or maybe just not QueryDesc?? ). > > It will be good to see what he think, or if others an opinion about this, > > about > > adding a pointer to CachedPlanSource in PlannedStmt vs setting

Re: More protocol.h replacements this time into walsender.c

2025-07-22 Thread Nathan Bossart
On Tue, Jul 22, 2025 at 05:54:48PM -0400, Dave Cramer wrote: > Patch attached Thanks. I plan to look into committing this tomorrow. -- nathan

Re: index prefetching

2025-07-22 Thread Andres Freund
Hi, On 2025-07-23 02:50:04 +0200, Tomas Vondra wrote: > But I don't see why would this have any effect on the prefetch distance, > queue depth etc. Or why decreasing INDEX_SCAN_MAX_BATCHES should improve > that. I'd have expected exactly the opposite behavior. > > Could be bug, of course. But it'

Re: stats.sql might fail due to shared buffers also used by parallel tests

2025-07-22 Thread Yugo Nagata
Hello Alexander, > Nagata-san, could you please share the configuration of hamerkop? If it's > running inside VM, what virtualization software is used? > > [1] > https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=hamerkop&dt=2025-07-09%2011%3A02%3A23 I am not the person in charge of it, s

Re: Custom pgstat support performance regression for simple queries

2025-07-22 Thread Michael Paquier
On Tue, Jul 22, 2025 at 10:57:06AM -0400, Andres Freund wrote: > It seems rather unsurprising that that causes a slowdown. > > The pre-check is there to: > /* Don't expend a clock check if nothing to do */ > > but you made it way more expensive than a clock check would have been (not > coun

Re: index prefetching

2025-07-22 Thread Tomas Vondra
On 7/23/25 02:39, Peter Geoghegan wrote: > On Tue, Jul 22, 2025 at 8:08 PM Andres Freund wrote: >> My response was specific to Tomas' comment that for many queries, which tend >> to be more complicated than the toys we are using here, there will be CPU >> costs in the query. > > Got it. That m

Re: simple patch for discussion

2025-07-22 Thread David Rowley
On Mon, 21 Jul 2025 at 06:27, Greg Hennessy wrote: > test=# show parallel_worker_algorithm ; > parallel_worker_algorithm > --- > log3 > (1 row) I don't think having a GUC which allows exactly two settings is anywhere near as flexible as you could make this. You're compla

Re: index prefetching

2025-07-22 Thread Peter Geoghegan
On Tue, Jul 22, 2025 at 8:08 PM Andres Freund wrote: > My response was specific to Tomas' comment that for many queries, which tend > to be more complicated than the toys we are using here, there will be CPU > costs in the query. Got it. That makes sense. > cheaper query

Re: index prefetching

2025-07-22 Thread Tomas Vondra
On 7/22/25 23:35, Peter Geoghegan wrote: > On Tue, Jul 22, 2025 at 4:50 PM Tomas Vondra wrote: >>> Obviously, whatever advantage that the "complex" patch has is bound to >>> be limited to cases where index characteristics are naturally the >>> limiting factor. For example, with the pgbench_account

Re: index prefetching

2025-07-22 Thread Peter Geoghegan
On Tue, Jul 22, 2025 at 5:11 PM Andres Freund wrote: > On 2025-07-18 23:25:38 -0400, Peter Geoghegan wrote: > > > To some degree the table AM will need to care about the index level > > > batching - > > > we have to be careful about how many pages we keep pinned overall. Which > > > is > > > som

Re: Re-archive the WAL on standby with archive_mode=always?

2025-07-22 Thread Fujii Masao
On Mon, Jul 21, 2025 at 2:38 PM Japin Li wrote: > > > Hi, hackers, > > I was recently reviewing the KeepFileRestoredFromArchive() function and came > across a section that raised a question for me: > > /* > * Create .done file forcibly to prevent the restored segment from being > * a

Re: track generic and custom plans in pg_stat_statements

2025-07-22 Thread Michael Paquier
On Tue, Jul 22, 2025 at 03:28:24PM -0500, Sami Imseih wrote: > I know Michael opposed the idea of carrying these structures, > at least CachedPlan, to Executor hooks ( or maybe just not QueryDesc?? ). > It will be good to see what he think, or if others an opinion about this, > about > adding a po

Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment

2025-07-22 Thread David Rowley
On Wed, 23 Jul 2025 at 02:35, Andrei Lepikhov wrote: > The 'Buffers:' way looks more natural to me. I don't like duplicated > text in the explain format - it is already cluttered by multiple > unnecessary elements that distract attention from the problematic plan > elements, such as unplaggable co

Re: index prefetching

2025-07-22 Thread Andres Freund
Hi, On 2025-07-22 19:13:23 -0400, Peter Geoghegan wrote: > On Tue, Jul 22, 2025 at 6:53 PM Andres Freund wrote: > > That may be true with local fast NVMe disks, but won't be true for networked > > storage like in common clouds. Latencies of 0.3 - 4ms leave a lot of CPU > > cycles for actual proce

Re: [PATCH] Use strchr() to search for a single character

2025-07-22 Thread David Rowley
On Wed, 23 Jul 2025 at 11:06, Tom Lane wrote: > > David Rowley writes: > > I'm currently thinking we should just fix the pgmkdirp.c instance and > > call it good. > > +1 Done. David

Re: Support for 8-byte TOAST values (aka the TOAST infinite loop problem)

2025-07-22 Thread Michael Paquier
On Tue, Jul 22, 2025 at 02:56:23PM +0200, Hannu Krosing wrote: > The high-level idea would be to any actual rewrite -- as opposed to > plain vacuum which frees empty space within the TOAST relation -- as > part of the vacuum of the main relation. > Another option would be to store a back-pointer to

Re: Missing NULL check after calling ecpg_strdup

2025-07-22 Thread Michael Paquier
On Tue, Jul 22, 2025 at 04:20:53PM +0300, Aleksander Alekseev wrote: > v7 may have a compilation warning on Linux: > > ``` > warning: unused variable ‘alloc_failed’ [-Wunused-variable] > ``` > > ... because the only use of the variable is hidden under #ifdef's. Yep, thanks, didn't see this one c

Re: Support getrandom() for pg_strong_random() source

2025-07-22 Thread Masahiko Sawada
On Tue, Jul 22, 2025 at 11:46 AM Jacob Champion wrote: > > On Tue, Jul 22, 2025 at 11:32 AM Masahiko Sawada > wrote: > > While getentropy() has better portability, according to the > > getentropy() manual, the maximum length is limited to 256 bytes. It > > works in some cases such as generating

Re: index prefetching

2025-07-22 Thread Peter Geoghegan
On Tue, Jul 22, 2025 at 6:53 PM Andres Freund wrote: > That may be true with local fast NVMe disks, but won't be true for networked > storage like in common clouds. Latencies of 0.3 - 4ms leave a lot of CPU > cycles for actual processing of the data. I don't understand why it wouldn't be a proble

Re: [PATCH] Use strchr() to search for a single character

2025-07-22 Thread Tom Lane
David Rowley writes: > I'm currently thinking we should just fix the pgmkdirp.c instance and > call it good. +1 regards, tom lane

Re: [PATCH] Proposal to Enable/Disable Index using ALTER INDEX

2025-07-22 Thread Sami Imseih
> Now wondering if it would be better to spend the effort looking at > pg_hint_plan and seeing how hard it would be to get global hints added > which are applied to all queries, and then add a way to disable use of > a named index. (I don't have any experience with that extension other > than looki

Re: [PATCH] Use strchr() to search for a single character

2025-07-22 Thread David Rowley
On Wed, 23 Jul 2025 at 10:36, Tom Lane wrote: > > David Rowley writes: > > Looking at [1], it seems even ancient versions of gcc and clang > > rewrite the strstr() into a strchr() call when the search term is a > > single char string. So it might not be worth doing to any trouble > > here. > > I

Re: index prefetching

2025-07-22 Thread Andres Freund
Hi, On 2025-07-22 22:50:00 +0200, Tomas Vondra wrote: > Yes. It's definitely true we could construct examples where the complex > patch beats the simple one for this reason. And I believe some of those > examples could be quite realistic, even if not very common (like when > very few index tuples

Re: [PATCH] Proposal to Enable/Disable Index using ALTER INDEX

2025-07-22 Thread David Rowley
On Tue, 22 Jul 2025 at 05:16, Sami Imseih wrote: > Also, I'd like to ask. what would be the argument against offering both > options, > ALTER and a GUC to override the catalog, as currently proposed in the patch? For me, the reason I don't like ALTER TABLE + the use_invisible_index / force_invis

Re: redis_fdw failure on crake

2025-07-22 Thread Michael Paquier
On Tue, Jul 22, 2025 at 10:02:57AM -0400, Andrew Dunstan wrote: > Yes, fallout from a system upgrade. Fixed, now, sorry for the noise. Thanks! -- Michael signature.asc Description: PGP signature

Re: Proposal: Limitations of palloc inside checkpointer

2025-07-22 Thread Alexander Korotkov
Hi, Xuneng! On Thu, Jun 26, 2025 at 4:31 PM Xuneng Zhou wrote: > Patch v7 modifies CompactCheckpointerRequestQueue() to process requests > incrementally in batches of CKPT_REQ_BATCH_SIZE (10,000), similar to the > approach used in AbsorbSyncRequests(). This limits memory usage from > O(num_req

Re: [PATCH] Use strchr() to search for a single character

2025-07-22 Thread Tom Lane
David Rowley writes: > Looking at [1], it seems even ancient versions of gcc and clang > rewrite the strstr() into a strchr() call when the search term is a > single char string. So it might not be worth doing to any trouble > here. I was wondering if that might be true. However, your godbolt re

Re: Proposal: QUALIFY clause

2025-07-22 Thread David Rowley
On Wed, 23 Jul 2025 at 09:21, Vik Fearing wrote: > I took a quick look at the patch (without applying and testing it) and > it seems to me that parse analysis is the wrong place to do this. We > want ruleutils to be able to spew out the QUALIFY clause as written in a > view and not as transformed.

Re: Conflict detection for update_deleted in logical replication

2025-07-22 Thread Masahiko Sawada
On Mon, Jul 21, 2025 at 8:49 PM Amit Kapila wrote: > > On Mon, Jul 21, 2025 at 11:27 PM Masahiko Sawada > wrote: > > > > On Sun, Jul 20, 2025 at 9:00 PM Amit Kapila wrote: > > > > > > > > If so, I agree > > > with you, we don't need XIDs of other databases as logical WALSender > > > will anyway

Re: [PATCH] Optimize ProcSignal to avoid redundant SIGUSR1 signals

2025-07-22 Thread Joel Jacobson
On Tue, Jul 22, 2025, at 23:54, Thomas Munro wrote: > On Wed, Jul 23, 2025 at 8:08 AM Joel Jacobson wrote: >> Previously, ProcSignal used an array of volatile sig_atomic_t flags, one >> per signal reason. A sender would set a flag and then unconditionally >> send a SIGUSR1 to the target process. T

Re: [PATCH] Use strchr() to search for a single character

2025-07-22 Thread David Rowley
On Wed, 23 Jul 2025 at 09:34, Dmitry Mityugov wrote: > Thank you for your attention to this problem. The code in > contrib/fuzzystrmatch/dmetaphone.c indeed uses several calls to strstr() > to search for a single character, but it also uses strstr() to search > for strings that consist of more tha

More protocol.h replacements this time into walsender.c

2025-07-22 Thread Dave Cramer
Greetings, Patch attached Dave Cramer 0001-replace-protocol-constants-with-named-constants-from.patch Description: Binary data

Re: [PATCH] Optimize ProcSignal to avoid redundant SIGUSR1 signals

2025-07-22 Thread Thomas Munro
On Wed, Jul 23, 2025 at 8:08 AM Joel Jacobson wrote: > Previously, ProcSignal used an array of volatile sig_atomic_t flags, one > per signal reason. A sender would set a flag and then unconditionally > send a SIGUSR1 to the target process. This could result in a storm of > redundant signals if mul

Re: index prefetching

2025-07-22 Thread Peter Geoghegan
On Tue, Jul 22, 2025 at 4:50 PM Tomas Vondra wrote: > > Obviously, whatever advantage that the "complex" patch has is bound to > > be limited to cases where index characteristics are naturally the > > limiting factor. For example, with the pgbench_accounts_pkey table > > there are only ever 6 dist

Re: [PATCH] Use strchr() to search for a single character

2025-07-22 Thread Dmitry Mityugov
Corey Huinker писал(а) 2025-07-22 22:42: On Sun, Jul 20, 2025 at 6:21 PM Dmitry Mityugov wrote: Code in src/port/pgmkdirp.c uses strstr() to find a single character in a string, but strstr() seems to be too generic for this job. Another function, strchr(), might be better suited for this purp

Re: Proposal: QUALIFY clause

2025-07-22 Thread Vik Fearing
On 22/07/2025 20:54, Matheus Alcantara wrote: (this happens on transformQualifyClause() if you want to take a look) I took a quick look at the patch (without applying and testing it) and it seems to me that parse analysis is the wrong place to do this. We want ruleutils to be able to spew

Re: index prefetching

2025-07-22 Thread Andres Freund
Hi, On 2025-07-18 23:25:38 -0400, Peter Geoghegan wrote: > On Fri, Jul 18, 2025 at 10:47 PM Andres Freund wrote: > > > (Within an index AM, there is a 1:1 correspondence between batches and > > > leaf > > > pages, and batches need to hold on to a leaf page buffer pin for a > > > time. None of th

Re: Making jsonb_agg() faster

2025-07-22 Thread Merlin Moncure
On Tue, Jul 22, 2025 at 11:43 AM Merlin Moncure wrote: > On Tue, Jul 22, 2025 at 10:37 AM Tom Lane wrote: > >> >> Thoughts? >> > > Really excited about this -- I'll do some testing. Performance of > serialization (generating json output from non json data) is the main > reason I still recommend

Re: support create index on virtual generated column.

2025-07-22 Thread Tom Lane
Corey Huinker writes: > I'm interested in this feature, specifically whether the optimizer uses the > index in situations where the expression is used rather than the virtual > column name. Hmm, I kinda think we should not do this. The entire point of a virtual column is that its values are not

Re: index prefetching

2025-07-22 Thread Tomas Vondra
On 7/22/25 19:35, Peter Geoghegan wrote: > On Tue, Jul 22, 2025 at 9:06 AM Tomas Vondra wrote: >> Real workloads are likely to have multiple misses in a row, which indeed >> ramps up the distance quickly. So maybe it's not that bad. Could we >> track a longer history of hits/misses, and consider t

Re: support create index on virtual generated column.

2025-07-22 Thread Corey Huinker
> > > hi. > > refactor and rebase. > > fix the regress tests failure in v4. > This may need another rebase, as it doesn't apply to master. I'm interested in this feature, specifically whether the optimizer uses the index in situations where the expression is used rather than the virtual column na

Re: index prefetching

2025-07-22 Thread Peter Geoghegan
On Tue, Jul 22, 2025 at 1:35 PM Peter Geoghegan wrote: > What is the difference between cases like "linear / eic=16 / sync" and > "linear_1 / eic=16 / sync"? I figured this out for myself. > One would imagine that these tests are very similar, based on the fact > that they have very similar name

Re: track generic and custom plans in pg_stat_statements

2025-07-22 Thread Sami Imseih
> > with the types of cached plan. We need to be able to differentiate > > when cached plans are not used, so a simple boolean is not > > sufficient. > Sure. But I modestly hope you would add a CachedPlanSource pointer > solely to the PlannedStmt and restructure it a little as we discussed > above.

Re: track generic and custom plans in pg_stat_statements

2025-07-22 Thread Andrei Lepikhov
On 7/22/25 19:13, Sami Imseih wrote: It may be more efficient to set the is_generic_plan option at the top plan node (PlannedStmt) and reference it wherever necessary. To identify a cached plan, we may consider pushing the CachedPlan/CachedPlanSource pointer down throughout pg_plan_query and main

[PATCH] Optimize ProcSignal to avoid redundant SIGUSR1 signals

2025-07-22 Thread Joel Jacobson
Hi hackers, In the work of trying to optimize async.c, I came across a surprisingly seemingly low hanging fruit in procsignal.c, to $subject. This optimization improves not only LISTEN/NOTIFY, but procsignal.c in general, for all ProcSignalReasons, by avoiding to send redundant signals in the cas

Re: track generic and custom plans in pg_stat_statements

2025-07-22 Thread Sami Imseih
> > It may be more efficient to set the is_generic_plan option at the top > > plan node (PlannedStmt) and reference it wherever necessary. To identify > > a cached plan, we may consider pushing the CachedPlan/CachedPlanSource > > pointer down throughout pg_plan_query and maintaining a reference to

Re: [PATCH] Use strchr() to search for a single character

2025-07-22 Thread Corey Huinker
On Sun, Jul 20, 2025 at 6:21 PM Dmitry Mityugov wrote: > Code in src/port/pgmkdirp.c uses strstr() to find a single character in > a string, but strstr() seems to be too generic for this job. Another > function, strchr(), might be better suited for this purpose, because it > is optimized to searc

Re: Verify predefined LWLocks tranches have entries in wait_event_names.txt

2025-07-22 Thread Nathan Bossart
On Tue, Jul 22, 2025 at 06:23:50AM +, Bertrand Drouvot wrote: > Sure, done in v2 too. I stared at this patch some more and came up with the attached. The biggest change is that I've moved the list of built-in LWLock tranches to the existing lwlocklist.h file. That simplifies the patch and ce

Re: Support getrandom() for pg_strong_random() source

2025-07-22 Thread DINESH NAIR
Hi , On Tue, Jul 22, 2025 at 4:12 AM Dagfinn Ilmari Mannsåker wrote: > > Masahiko Sawada writes: > > > On Tue, Jul 22, 2025 at 12:13 AM Michael Paquier > > wrote: > >> > >> On Mon, Jul 21, 2025 at 11:43:35PM -0700, Masahiko Sawada wrote: > >> > The patch supports the getrandom() f

Re: Proposal: QUALIFY clause

2025-07-22 Thread Matheus Alcantara
On Tue Jul 22, 2025 at 3:11 PM -03, Marcos Pegoraro wrote: > Em ter., 22 de jul. de 2025 às 08:56, Matheus Alcantara < > matheusssil...@gmail.com> escreveu: > >> The current patch supports the following syntaxes: >> SELECT a, b, c >> wf() OVER () as d >> FROM tab >> QUALIFY d = 1 >>

Re: Support getrandom() for pg_strong_random() source

2025-07-22 Thread Jacob Champion
On Tue, Jul 22, 2025 at 11:32 AM Masahiko Sawada wrote: > While getentropy() has better portability, according to the > getentropy() manual, the maximum length is limited to 256 bytes. It > works in some cases such as generating UUID data but seems not > appropriate for our general pg_strong_rando

Re: Parallel heap vacuum

2025-07-22 Thread Masahiko Sawada
On Mon, Jul 21, 2025 at 4:49 PM Andres Freund wrote: > > Hi, > > On 2025-07-21 12:41:49 -0700, Masahiko Sawada wrote: > > The reason why I added some callbacks as table AM callbacks in the > > patch is that I could not find other better places. Currently, > > vacuumparallel.c handles several criti

Re: [PATCH] Let's get rid of the freelist and the buffer_strategy_lock

2025-07-22 Thread Greg Burd
On 7/21/25 14:35, Andres Freund wrote: > Hi, > > On 2025-07-21 13:37:04 -0400, Greg Burd wrote: >> On 7/18/25 13:03, Andres Freund wrote: >> Hello.  Thanks again for taking the time to review the email and patch, >> I think we're onto something good here. >> >>> I'd be curious if anybody wants to a

Re: Support getrandom() for pg_strong_random() source

2025-07-22 Thread Masahiko Sawada
On Tue, Jul 22, 2025 at 4:12 AM Dagfinn Ilmari Mannsåker wrote: > > Masahiko Sawada writes: > > > On Tue, Jul 22, 2025 at 12:13 AM Michael Paquier > > wrote: > >> > >> On Mon, Jul 21, 2025 at 11:43:35PM -0700, Masahiko Sawada wrote: > >> > The patch supports the getrandom() function as a new so

Re: [PATCH] Proposal to Enable/Disable Index using ALTER INDEX

2025-07-22 Thread Sami Imseih
> > This is already an established pattern has been used by other > > RDBMS's. Having worked with such interface in the past, a combo of > > ALTER and GUC, I never thought it was awkward and it's quite simple to > > understand/maintain. But that is subjective. > > > > It's amazing what people are w

Re: Proposal: QUALIFY clause

2025-07-22 Thread Marcos Pegoraro
Em ter., 22 de jul. de 2025 às 08:56, Matheus Alcantara < matheusssil...@gmail.com> escreveu: > The current patch supports the following syntaxes: > SELECT a, b, c > wf() OVER () as d > FROM tab > QUALIFY d = 1 > When using the "QUALIFY d = 1" form, I currently rewrite the expressi

Re: index prefetching

2025-07-22 Thread Peter Geoghegan
On Tue, Jul 22, 2025 at 1:35 PM Peter Geoghegan wrote: > I attach pgbench_accounts_pkey_nhblks.txt, which shows a query that > (among other things) ouputs "nhblks" for each leaf page from a given > index (while showing the details of each leaf page in index key space > order). I just realized tha

Re: Making jsonb_agg() faster

2025-07-22 Thread Merlin Moncure
On Tue, Jul 22, 2025 at 10:37 AM Tom Lane wrote: > There have been some complaints recently about how jsonb_agg() > is a lot slower than json_agg() [1]. That's annoying considering > that the whole selling point of JSONB is to have faster processing > than the original JSON type, so I poked into

Re: index prefetching

2025-07-22 Thread Peter Geoghegan
On Tue, Jul 22, 2025 at 9:06 AM Tomas Vondra wrote: > Real workloads are likely to have multiple misses in a row, which indeed > ramps up the distance quickly. So maybe it's not that bad. Could we > track a longer history of hits/misses, and consider that when adjusting > the distance? Not just th

Re: track generic and custom plans in pg_stat_statements

2025-07-22 Thread Sami Imseih
> I would like to oppose to the current version a little. > > Commits de3a2ea3b264 and 1d477a907e63 introduced elements that are more > closely related to the execution phase. While the parameter > es_parallel_workers_to_launch could be considered a planning parameter, > es_parallel_workers_launche

Making jsonb_agg() faster

2025-07-22 Thread Tom Lane
There have been some complaints recently about how jsonb_agg() is a lot slower than json_agg() [1]. That's annoying considering that the whole selling point of JSONB is to have faster processing than the original JSON type, so I poked into that. What I found is that jsonb_agg() and its variants a

Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions

2025-07-22 Thread Corey Huinker
On Tue, Jul 22, 2025 at 2:45 AM Vik Fearing wrote: > > On 22/07/2025 03:59, jian he wrote: > > Based on my reading of [4], it seems CAST(EXPRESSION AS TYPE DEFAULT > > def_expr ON ERROR) > > is not included in SQL:2023. > > > > [4] > https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finish

Re: amcheck support for BRIN indexes

2025-07-22 Thread Arseniy Mukhin
Hi, While reviewing gist amcheck patch [1] I realized that brin amcheck also must check if current snapshot is OK with index indcheckxmin (as btree, gist do it). Currently this check is contained in btree amcheck code, but other AMs need it for heapallindexed as well, so I moved it from btree to v

Re: Proposal: QUALIFY clause

2025-07-22 Thread Andrew Dunstan
On 2025-07-22 Tu 11:14 AM, Vik Fearing wrote: I agree that its own clause is best; I just greatly dislike QUALIFY. Sorry. If we were making up our own syntax this would be a sensible thing to debate. If we're talking about implementing something we expect to be in the standard, I thi

Re: Proposal: QUALIFY clause

2025-07-22 Thread Vik Fearing
On 22/07/2025 17:14, Nico Williams wrote: It doesn't seem too crazy that extra WHEREs in WHERE clauses should some day function as ANDs, and ditto HAVINGs, which is another reason not to reuse HAVING for this: just to leave that a possibility, remote though it might be. I have a firm finger o

Re: Proposal: QUALIFY clause

2025-07-22 Thread Vik Fearing
On 22/07/2025 17:07, Nico Williams wrote: On Tue, Jul 22, 2025 at 01:14:20AM -0400, Tom Lane wrote: Nico Williams writes: On Mon, Jul 21, 2025 at 09:43:15PM -0600, Merlin Moncure wrote: Hm, HAVING requires to apply 'group by' which windows functions do not require (unlike aggregates). Pave

Re: Proposal: QUALIFY clause

2025-07-22 Thread Nico Williams
I often accidentally write SELECT .. WHERE .. WHERE ..; which is obviously wrong, but what I mean when I do this is SELECT .. WHERE .. AND ..; and if I wrote GROUP BY .. HAVING queries as often as I do ones that don't GROUP BY then I'd probably also accidentally use extra HAVINGs as ANDs.

Re: Proposal: QUALIFY clause

2025-07-22 Thread Nico Williams
On Tue, Jul 22, 2025 at 01:14:20AM -0400, Tom Lane wrote: > Nico Williams writes: > > On Mon, Jul 21, 2025 at 09:43:15PM -0600, Merlin Moncure wrote: > >> Hm, HAVING requires to apply 'group by' which windows functions do not > >> require (unlike aggregates). > > > Pavel's point is precisely to a

Re: Proposal: QUALIFY clause

2025-07-22 Thread Nico Williams
On Mon, Jul 21, 2025 at 11:02:36PM -0600, Merlin Moncure wrote: > On Mon, Jul 21, 2025 at 10:08 PM Nico Williams > wrote: > > I would have a HAVING clause that comes _before_ GROUP BY apply to > > window functions and a second one that comes _after_ GROUP BY apply to > > the grouping. > > I don't

Custom pgstat support performance regression for simple queries

2025-07-22 Thread Andres Freund
Hi, I wanted to run some performance tests for [1] and looked at the profile of a workload with a lot of short queries. And was rather surprised to see pgstat_report_stat() to be the top entry - that certainly didn't use to be the case. For the, obviously rather extreme, workload of a pgbench ses

Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment

2025-07-22 Thread Andrei Lepikhov
On 22/7/2025 00:17, David Rowley wrote: On Fri, 4 Jul 2025 at 20:30, Ilia Evdokimov wrote: I attached rebased v10 patch on 5a6c39b. I've gone over this and made some cosmetic adjustments. A few adjustments to the comments and used Cardinality rather than double for some data types. I also mov

Re: Adding wait events statistics

2025-07-22 Thread Andres Freund
Hi, On 2025-07-22 12:24:46 +, Bertrand Drouvot wrote: > Anyway, let's forget about eBPF, I ran another experiment by counting the > cycles > with: > > static inline uint64_t rdtsc(void) { > uint32_t lo, hi; > __asm__ __volatile__("rdtsc" : "=a" (lo), "=d" (hi)); > return ((uint64_

RE: Question on any plans to use the User Server/User Mapping to provide Logical Replication Subscriptions the user/password in an encrypted manner

2025-07-22 Thread Vitale, Anthony, Sony Music
Hi Yes, it is exactly what I am looking for However, it appears that this patch (The Ability to create server with FOR CONNECTION ONLY) was never implemented OR at least I can't find it. Do you know if there is any way to find out what ever happened to this ? Thanks Can you check the work bei

Re: redis_fdw failure on crake

2025-07-22 Thread Andrew Dunstan
On 2025-07-22 Tu 1:54 AM, Michael Paquier wrote: On Tue, Jul 22, 2025 at 12:12:25PM +0900, Richard Guo wrote: After pushing commit e2debb643 I noticed redis_fdw failure on crake. +ERROR: failed to connect to Redis: 1 +CONTEXT: SQL statement "select (select count(*) from db

Re: Memory consumed by paths during partitionwise join planning

2025-07-22 Thread Andrei Lepikhov
On 18/7/2025 13:48, Ashutosh Bapat wrote: On Mon, Jul 7, 2025 at 8:43 PM Andrei Lepikhov wrote: if (!IsA(new_path, IndexPath)) - pfree(new_path); + free_path(new_path, 0, false); Why don't we free the subpaths if they aren't referenced anymore? During testing, I discovered that we sometimes enc

  1   2   >