Re: Speedup truncations of temporary relation forks

Hi, On Sat, May 31, 2025 at 7:49 AM Michael Paquier wrote: > > On Fri, May 30, 2025 at 06:01:16PM +0700, Daniil Davydov wrote: > > For now we fully scan local buffers for each fork of the temporary > > relation that we want to truncate (in order to drop its buffers). It > > happens in the functio

Re: POC: Carefully exposing information without authentication

On Fri, May 30, 2025 at 9:34 PM Tom Lane wrote: > I think calling it in the postmaster is a nonstarter. Thanks for the feedback. Please find attached version two, which moves the code to the very start of BackendInitialize in tcop/backend_startup.c. If we handle the request, we simply proc_exit

Re: Non-reproducible AIO failure

Hello Thomas, 25.05.2025 05:45, Thomas Munro wrote: TRAP: failed Assert("ioh->op == PGAIO_OP_INVALID"), File: "aio_io.c", Line: 161, PID: 32355 Can you get a core and print *ioh in the debugger? It looks like I managed to restore all the conditions needed to reproduce that Assert more or less

Re: POC: Carefully exposing information without authentication

Greg Sabino Mullane writes: > Good question. Forking is expensive, and there is also a lot of > housekeeping associated with it that is simply not needed here. We want > this to be lightweight, and simple. No need to fork if we are just going to > do a few strncmp() calls and a send(). send() can

Re: Speedup truncations of temporary relation forks

On Fri, May 30, 2025 at 06:01:16PM +0700, Daniil Davydov wrote: > For now we fully scan local buffers for each fork of the temporary > relation that we want to truncate (in order to drop its buffers). It > happens in the function "DropRelationBuffers". > There used to be the same problem for regula

Re: Add comment explaining why queryid is int64 in pg_stat_statements

On Fri, May 30, 2025 at 11:09:23PM +1200, David Rowley wrote: > Now done. Cool, thanks. Just did the same for the query ID, closing the open item. -- Michael signature.asc Description: PGP signature

Re: POC: Carefully exposing information without authentication

On Fri, May 30, 2025 at 11:02 AM Antonin Houska wrote: > Why is it important not to fork? Good question. Forking is expensive, and there is also a lot of housekeeping associated with it that is simply not needed here. We want this to be lightweight, and simple. No need to fork if we are just go

Re: Reduce DEBUG level of catcache refreshing messages

Jelte Fennema-Nio writes: > On Fri, 30 May 2025 at 16:38, Tom Lane wrote: >> But if we could have *some* kind of explainable basis for >> assigning DEBUGn levels, I think our lives would be better. > Agreed. I'll start with a concrete proposal then: > DEBUG1 is reserved for usage by extensions

Re: Reduce DEBUG level of catcache refreshing messages

On Fri, 30 May 2025 at 16:38, Tom Lane wrote: > I don't have an opinion about the merits of this exact change, but > I wish somebody would go through all our DEBUGn messages and come up > with some coherent proposal for what the various levels should be > used for. Right now I think those choices

Re: Correcting freeze conflict horizon calculation

On Fri, May 30, 2025 at 6:10 PM Peter Geoghegan wrote: > > On Fri, May 30, 2025 at 5:57 PM Melanie Plageman > wrote: > > I don't see how OldestXmin comes into play with the visibility_cutoff_xid. > > Code in heap_page_is_all_visible() (and other place, I guess the other > one is in pruneheap.c no

Re: Correcting freeze conflict horizon calculation

On Fri, May 30, 2025 at 5:57 PM Melanie Plageman wrote: > I don't see how OldestXmin comes into play with the visibility_cutoff_xid. Code in heap_page_is_all_visible() (and other place, I guess the other one is in pruneheap.c now) have a separate OldestXmin test: /* * The inserter definitely co

Re: Correcting freeze conflict horizon calculation

On Fri, May 30, 2025 at 5:34 PM Peter Geoghegan wrote: > > On Fri, May 30, 2025 at 5:16 PM Melanie Plageman > wrote: > > > You're right. I forgot that the visibility_cutoff_xid will be older > > than OldestXmin when all the tuples are going to be frozen. > > I added an assertion in a number of pl

Re: Correcting freeze conflict horizon calculation

On Fri, May 30, 2025 at 5:16 PM Melanie Plageman wrote: > On Thu, May 29, 2025 at 11:37 AM Peter Geoghegan wrote: > > How can taking the "Avoids false conflicts when hot_standby_feedback > > in use" path more often result in fewer unnecessary conflicts on > > standbys? Isn't it the other way arou

Re: pg_get_multixact_members not documented

On Fri, May 30, 2025 at 02:23:30PM -0500, Sami Imseih wrote: > While looking into another multixact related topic, I realised that > pg_get_multixact_members > is not documented. I saw the lack of documentation was mentioned here [0], but > this was never committed. > > Any reason it should not be

Re: pg_get_multixact_members not documented

On Fri, May 30, 2025 at 04:24:43PM -0500, Sami Imseih wrote: >> Want to put together a patch? > > Yes, will do For extra credit, maybe we could add a test or two, too... -- nathan

Re: pg_get_multixact_members not documented

Thanks! > blog posts that recommend it. In any case, I can't think of a reason it > ought to remain undocumented. I agree, especially with blogs referencing it. > Want to put together a patch? Yes, will do — Sami

Re: Correcting freeze conflict horizon calculation

On Thu, May 29, 2025 at 11:37 AM Peter Geoghegan wrote: > > Your concern is that the horizon might be overly aggressive/too > conservative. But your patch (for 16) makes us take the > don't-use-snapshotConflictHorizon-twice block *less* frequently (and > the "use OldestXmin conservatively" block *

Re: regdatabase

On Fri, May 30, 2025 at 04:55:58PM -0400, Tom Lane wrote: > Nathan Bossart writes: >> For now, I've just added another case block for REGDATABASEOID to match the >> others. If there are problems with non-pinned objects being considered >> shippable, it's not really the fault of this patch. Also,

Re: regdatabase

Nathan Bossart writes: > For now, I've just added another case block for REGDATABASEOID to match the > others. If there are problems with non-pinned objects being considered > shippable, it's not really the fault of this patch. Also, from reading > around [0], I get the idea that "shippability"

Re: Large expressions in indexes can't be stored (non-TOASTable)

On Wed, May 28, 2025 at 11:43:52AM +0900, Michael Paquier wrote: > Thanks for the refreshed versions. Looks sensible to me overall. Committed. Thanks for reviewing. -- nathan

Re: regdatabase

> > > > For now, I've just added another case block for REGDATABASEOID to match the > others. If there are problems with non-pinned objects being considered > shippable, it's not really the fault of this patch. Also, from reading > around [0], I get the idea that "shippability" might just mean th

Re: CHECKPOINT unlogged data

Re: Andres Freund > Hi, > > On 2025-05-30 18:17:45 +0200, Christoph Berg wrote: > > A customer reported to use CHECKPOINT before shutdowns to make the > > shutdowns themselves faster and asked if it was possible to make > > CHECKPOINT optionally also write out unlogged table data for that > > purp

pg_get_multixact_members not documented

Hi, While looking into another multixact related topic, I realised that pg_get_multixact_members is not documented. I saw the lack of documentation was mentioned here [0], but this was never committed. Any reason it should not be? [0] https://www.postgresql.org/message-id/20150619215231.gt133...

Re: Fixing memory leaks in postgres_fdw

Matheus Alcantara writes: > Sounds reasonable to me. +1 for going forward with these patches. I got cold feet about applying the full patchset to v18 --- it's kind of a large change and it's not fixing any known bug that the minimal patch doesn't, so it feels like something not to do after beta1.

Re: queryId constant squashing does not support prepared statements

> The test additions done in v7-0002 look sensible here. > > --- In the following two queries the operator expressions (+) and (@) have > --- different oppno, and will be given different query_id if squashed, even > though > --- the normalized query will be the same > > In v7-0002, this comment is

Re: regdatabase

On Mon, May 19, 2025 at 04:17:26PM -0500, Nathan Bossart wrote: > On Mon, May 19, 2025 at 10:37:29AM +0800, jian he wrote: >> I saw REGROLEOID in foreign_expr_walker, >> I'm wondering whether REGDATABASEOID is reachable within foreign_expr_walker. >> >> not familiar with this area, also there are

Re: CHECKPOINT unlogged data

Re: Nathan Bossart > This patch also adds an IMMEDIATE option, which I proposed some time ago > [0]. I ended up withdrawing it due to general skepticism about its Thanks for the pointer, I did not go that far back when looking for older threads. When writing the patch, I was also thinking about

Re: CHECKPOINT unlogged data

On 2025-05-30 19:23:04 +0200, Christoph Berg wrote: > Re: Nathan Bossart > > This patch also adds an IMMEDIATE option, which I proposed some time ago > > [0]. I ended up withdrawing it due to general skepticism about its > > Thanks for the pointer, I did not go that far back when looking for > ol

Re: CHECKPOINT unlogged data

On Fri, May 30, 2025 at 12:39:02PM -0400, Andres Freund wrote: > On 2025-05-30 18:17:45 +0200, Christoph Berg wrote: >> A customer reported to use CHECKPOINT before shutdowns to make the >> shutdowns themselves faster and asked if it was possible to make >> CHECKPOINT optionally also write out unlo

Re: CHECKPOINT unlogged data

Hi, On May 30, 2025 12:55:28 PM EDT, Christoph Berg wrote: >Re: Andres Freund >> Hi, >> >> On 2025-05-30 18:17:45 +0200, Christoph Berg wrote: >> > A customer reported to use CHECKPOINT before shutdowns to make the >> > shutdowns themselves faster and asked if it was possible to make >> > CHECK

Re: CHECKPOINT unlogged data

Hi, On 2025-05-30 18:17:45 +0200, Christoph Berg wrote: > A customer reported to use CHECKPOINT before shutdowns to make the > shutdowns themselves faster and asked if it was possible to make > CHECKPOINT optionally also write out unlogged table data for that > purpose. I think the idea makes sens

CHECKPOINT unlogged data

A customer reported to use CHECKPOINT before shutdowns to make the shutdowns themselves faster and asked if it was possible to make CHECKPOINT optionally also write out unlogged table data for that purpose. I think the idea makes sense, so there's the patch. Christoph >From 1d7d7b7fab78312f5423dff

Re: Add “FOR UPDATE NOWAIT” lock details to the log.

On 2025/05/31 0:28, Fujii Masao wrote:   Otherwise, maybe "log_lock_failures" would be better. Yes, this seems better for consistency with log_lock_waits. Patch attached. Regards, -- Fujii Masao NTT DATA Japan Corporation From 71f4c9cf71bff0edeae0077e95670569115ccc0f Mon Sep 17 00:00:00 2

Re: Add CHECK_FOR_INTERRUPTS in polling loop code path in XactLockTableWait

On 2025/05/29 5:40, Kevin K Biju wrote: Hi Fujii, The WaitEvent sounds good to me, I will submit a separate patch for that when I find time. Kevin On Wed, May 28, 2025 at 5:06 PM Fujii Masao mailto:masao.fu...@oss.nttdata.com>> wrote: On 2025/05/27 4:43, Kevin K Biju wrote: > H

Re: Add “FOR UPDATE NOWAIT” lock details to the log.

On 2025/05/30 19:20, Peter Eisentraut wrote: On 14.03.25 16:07, Fujii Masao wrote: Instead, wouldn't it be simpler to update LockAcquireExtended() to take a new argument, like logLockFailure, to control whether a lock failure should be logged directly? I’ve adjusted the patch accordingly and

Re: POC: Carefully exposing information without authentication

Greg Sabino Mullane wrote: > Proposal: Allow a carefully curated selection of information to be shown > without authentication. > > A common task for an HA system or a load balancer is to quickly determine > which of your Postgres clusters is the primary, and which are the > replicas. The cano

Re: Reduce DEBUG level of catcache refreshing messages

On Fri, May 30, 2025 at 9:33 AM Jelte Fennema-Nio wrote: > When testing extensions using pgregress, it can be useful to introduce > some new DEBUG logs which are specific to the extension and change the > log level during part of the of the test. > > There's a problem though: Often a "rehashing c

Re: Reduce DEBUG level of catcache refreshing messages

"Jelte Fennema-Nio" writes: > When testing extensions using pgregress, it can be useful to introduce > some new DEBUG logs which are specific to the extension and change the > log level during part of the of the test. > There's a problem though: Often a "rehashing catalog cache ..." debug > messa

Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE

On 2025/05/30 21:43, Jelte Fennema-Nio wrote: On Fri May 30, 2025 at 1:55 PM CEST, David Rowley wrote: By my count, those who think we should change log_buffers to true are: Robert [1], Michael Christofides [2], Fujii [3]. +1 for me on changing the default. It just seems confusing to have d

Re: Proposal: Job Scheduler

Add me to the +1 for having a built-in scheduler. It's useful for plenty of things like automated partition creation (as noted), scheduling backups, index maintenance, batch processing jobs, etc... I wrote jpgAgent (compatible with pgAgent) ~10 years ago because pgAgent was too unstable (and the o

Re: Proposal: Job Scheduler

On Fri, May 30, 2025 at 02:22 Andrei Lepikhov wrote: > On 5/30/25 03:17, Nikolay Samokhvalov wrote: > > On Thu, Jun 6, 2024 at 5:31 AM Dmitry Dolgov <9erthali...@gmail.com > > +1. The PostgreSQL ecosystem is surprisingly fragmented, when it > comes > > to quite essential components that h

Re: Persist injection points across server restarts

> On 27 May 2025, at 05:51, Michael Paquier wrote: > > > > I've made another round of looking into these patches. I think it's fine that private data is not included. But the feature looks orthogonal, and I do not see specific reasons why it should be omitted when IPs are persisted. An

Re: Optimize shared LWLock acquisition for high-core-count systems

Thanks David! I've added the patch referred by this link: https://commitfest.postgresql.org/patch/5784/ Regards, Zhiguo On 5/30/2025 8:02 PM, David Rowley wrote: On Fri, 30 May 2025 at 23:31, Zhou, Zhiguo wrote: Please review this patch for consideration in upcoming PostgreSQL releases. Pl

Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE

On Fri May 30, 2025 at 1:55 PM CEST, David Rowley wrote: By my count, those who think we should change log_buffers to true are: Robert [1], Michael Christofides [2], Fujii [3]. +1 for me on changing the default. It just seems confusing to have different defaults for auto_explain and EXPLAIN. I'

Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE

On Thu, 29 May 2025 at 16:28, Fujii Masao wrote: > I agree with changing the default value of auto_explain.log_buffers to true. > I think that users who know EXPLAIN ANALYZE includes buffers info in > the result by default since v18 would expect the buffer info also to > be included in auto_explai

Reduce DEBUG level of catcache refreshing messages

When testing extensions using pgregress, it can be useful to introduce some new DEBUG logs which are specific to the extension and change the log level during part of the of the test. There's a problem though: Often a "rehashing catalog cache ..." debug message will also show up in those cases. I

Re: Replication slot is not able to sync up

On Thu, May 29, 2025 at 6:01 PM Robert Haas wrote: > > On Wed, May 28, 2025 at 12:15 AM Zhijie Hou (Fujitsu) > wrote: > > I think the SQL API was mainly intended for testing and debugging purposes > > where controlled sync operations are useful. For production use, the > > slotsync > > worker (w

Re: Replication slot is not able to sync up

On Fri, May 30, 2025 at 4:05 PM Amul Sul wrote: > > Quick question -- due to my limited understanding of this area: why > can't we perform an action similar to pg_logical_slot_get_changes() > implicitly from pg_sync_replication_slots()? Would there be any > implications of doing so? > Yes, there

Re: Optimize shared LWLock acquisition for high-core-count systems

On Fri, 30 May 2025 at 23:31, Zhou, Zhiguo wrote: > Please review this patch for consideration in upcoming PostgreSQL releases. Please add the patch to https://commitfest.postgresql.org/53/ David

Re: Add comment explaining why queryid is int64 in pg_stat_statements

Thanks for making the appropriate changes, David. Now everything make sense. :) On Fri, 30 May 2025 16:39:23 +0530 David Rowley wrote --- On Fri, 30 May 2025 at 11:51, David Rowley < mailto:dgrowle...@gmail.com > wrote: > > On Fri, 30 May 2025 at 11:35, Michael Paquier < ma

Optimize shared LWLock acquisition for high-core-count systems

Hi Hackers, I am reaching out to solicit your insights and comments on this patch addressing a significant performance bottleneck in LWLock acquisition observed on high-core-count systems. During performance analysis of HammerDB/TPCC (192 virtual users, 757 warehouses) on a 384-vCPU Intel sys

Re: Replication slot is not able to sync up

On Fri, May 30, 2025 at 4:32 PM Amit Kapila wrote: > > On Fri, May 30, 2025 at 4:05 PM Amul Sul wrote: > > > > Quick question -- due to my limited understanding of this area: why > > can't we perform an action similar to pg_logical_slot_get_changes() > > implicitly from pg_sync_replication_slots(

Re: Add comment explaining why queryid is int64 in pg_stat_statements

On Fri, 30 May 2025 at 11:51, David Rowley wrote: > > On Fri, 30 May 2025 at 11:35, Michael Paquier wrote: > > Thanks, Nathan. Let's proceed with the change then. David, would you > > prefer handling the patch you have written by yourself for the query > > ID part? > > Yes. I'll look at that ag

Re: Add comment explaining why queryid is int64 in pg_stat_statements

Thanks for the review. On Wed, 21 May 2025 at 03:35, Sami Imseih wrote: > 2/ Should "DatumGetInt64(hash_any_extended(.." be turned into a > macro since it's used in > several places? Also, we can add a comment in the macro such as > " > Output the queryId as an int64 rather than a uint64, to

Speedup truncations of temporary relation forks

Hi, For now we fully scan local buffers for each fork of the temporary relation that we want to truncate (in order to drop its buffers). It happens in the function "DropRelationBuffers". There used to be the same problem for regular tables (i.e. shared buffers) and it was fixed in commit [1] and no

Re: Replication slot is not able to sync up

On Fri, May 30, 2025 at 3:38 PM Zhijie Hou (Fujitsu) wrote: > > On Wed, May 28, 2025 at 2:09 AM Masahiko Sawada wrote: > > > > On Fri, May 23, 2025 at 10:07 PM Amit Kapila > > wrote: > > > > > > In the case presented here, the logical slot is expected to keep > > > forwarding, and in the consecut

Re: Foreign key validation failure in 18beta1

On Fri, May 30, 2025 at 1:37 PM jian he wrote: > > On Thu, May 29, 2025 at 8:58 PM Amul Sul wrote: > > > > > > I just realized we have the same problem with ALTER FOREIGN KEY ENFORCED. > > > for example: > > > > Yeah, I think adding a "currcon->confrelid == pkrelid" check before > > enqueueing va

Re: Add “FOR UPDATE NOWAIT” lock details to the log.

On 14.03.25 16:07, Fujii Masao wrote: Instead, wouldn't it be simpler to update LockAcquireExtended() to take a new argument, like logLockFailure, to control whether a lock failure should be logged directly? I’ve adjusted the patch accordingly and attached it. Please let me know what you think!

RE: Replication slot is not able to sync up

On Wed, May 28, 2025 at 2:09 AM Masahiko Sawada wrote: > > On Fri, May 23, 2025 at 10:07 PM Amit Kapila > wrote: > > > > In the case presented here, the logical slot is expected to keep > > forwarding, and in the consecutive sync cycle, the sync should be > > successful. Users using logical decod

Re: autoprewarm_dump_now

Some of our clients encountered a problem — they needed to allocate shared_buffers = 700 GB on a server with 1.5 TB RAM, and the error "invalid memory alloc request size 1835008000" occurred. That is, these are not mental exercises. Best regards, Daria Shanina чт, 29 мая 2025 г. в 16:21, Tom

Re: Fix slot synchronization with two_phase decoding enabled

On Fri, May 30, 2025 at 12:55 AM Masahiko Sawada wrote: > > On Thu, May 29, 2025 at 2:00 AM Nisha Moond wrote: > > > > On Wed, May 28, 2025 at 6:10 AM Masahiko Sawada > > wrote: > > > > > > On Sun, May 25, 2025 at 11:34 PM Nisha Moond > > > wrote: > > > > > > > > to > > > > On Fri, May 23, 2

Re: Proposal: Job Scheduler

On 5/30/25 03:17, Nikolay Samokhvalov wrote: On Thu, Jun 6, 2024 at 5:31 AM Dmitry Dolgov <9erthali...@gmail.com +1. The PostgreSQL ecosystem is surprisingly fragmented, when it comes to quite essential components that happen to be outside of the core. But of course it doesn't mean t

Re: Correct documentation for protocol version

On Thu, 29 May 2025 at 13:12, Dave Cramer wrote: > Agreed that nobody upgrades a client from 2.0 but is there value in keeping > the history ? Yeah, maybe. > Perhaps another section which would be "Changes in 3.x" ? If we decide to keep it I think it would be best to have all protocol changes

Re: Foreign key validation failure in 18beta1

On Thu, May 29, 2025 at 8:58 PM Amul Sul wrote: > > > > I just realized we have the same problem with ALTER FOREIGN KEY ENFORCED. > > for example: > > Yeah, I think adding a "currcon->confrelid == pkrelid" check before > enqueueing validation in ATExecAlterConstrEnforceability() would > address th

Re: Warning -Wclobbered in PG_TRY(...)

On Thu, 29 May 2025 at 18:26, Tom Lane wrote: > > Go there and use the "keyword search" for "longjmp" --- they > don't seem to make it possible to bookmark individual pages. > > Indeed, you are fully correct. I didn't analyse the code well enough. The equivalent code for PG_TRY/PG_FINALLY/PG_END_

Re: Persist injection points across server restarts

Hi Michael, On Fri, May 30, 2025 at 5:01 AM Michael Paquier wrote: > On Thu, May 29, 2025 at 01:17:21PM +0530, Rahila Syed wrote: > > After applying the v3-patches, I see failure like these: > > macOS - Sonoma - Meson - Cirrus CI > > > > Windows - Se

Re: Suggestion : support for environment variable in initdb to set the superuser password

Le mer. 28 mai 2025, 13:26, Peter Eisentraut a écrit : > On 27.05.25 11:43, Reda Agaoua wrote: > "Insecure, but it's ok because it's not > used very often" > This isn't what I meant, but I guess it's the same so maybe I didn't think this through as much as I thought I had ! Still, is the concer

Re: ZStandard (with dictionaries) compression support for TOAST compression

On Tue, May 27, 2025 at 02:59:17AM -0700, Nikhil Kumar Veldanda wrote: > typedef struct varatt_external > { > int32 va_rawsize; /* Original data size (includes header) */ > uint32 va_extinfo; /* External size (without header) and > * compression metho