Re: Improve WALRead() to suck data directly from WAL buffers when possible

On Thu, 2023-11-02 at 22:38 +0530, Bharath Rupireddy wrote: > > I suppose the question is: should reading from the WAL buffers an > > intentional thing that the caller does explicitly by specific > > callers? > > Or is it an optimization that should be hidden from the caller? > > > > I tend toward

Improvement discussion of custom and generic plans

Hi We have one such problem. A table field has skewed data. Statistics: n_distinct | -0.4481973 most_common_vals | {5f006ca25b52ed78e457b150ee95a30c} most_common_freqs | {0.5518474} Data generation: CREATE TABLE s_user ( user_id varchar(32) NOT NULL, corp_id va

Re: Pre-proposal: unicode normalized text

On Fri, 2023-11-03 at 10:51 +1300, Thomas Munro wrote: > bowerbird and hammerkop didn't like commit a02b37fc.  They're still > using the old 3rd build system that is not tested by CI.  It's due > for > removal in the 17 cycle IIUC but in the meantime I guess the new > codegen script needs to be inv

Re: Pre-proposal: unicode normalized text

On Fri, 3 Nov 2023 at 20:49, Jeff Davis wrote: > > On Fri, 2023-11-03 at 10:51 +1300, Thomas Munro wrote: > > bowerbird and hammerkop didn't like commit a02b37fc. They're still > > using the old 3rd build system that is not tested by CI. It's due > > for > > removal in the 17 cycle IIUC but in t

Re: Tab completion regression test failed on illumos

On Fri, Nov 3, 2023 at 3:14 PM Japin Li wrote: > Thanks for your explantation, the termios.c_oflag on Illumos enables TABDLY > by default. It seems that various other open source Unixen dropped that between 29 and 2 years ago, but not illumos. I guess no one ever had IO::Pty installed on an olde

Re: Report search_path value back to the client.

I wanted to revive this thread, since it's by far one of the most common foot guns that people run into with PgBouncer. Almost all session level SET commands leak across transactions, but SET search_path is by far the one with the biggest impact when it is not the setting that you expect. As well a

Re: Report search_path value back to the client.

For completeness attached is a tiny patch implementing this, so this thread can be added to the commit fest. v1-0001-Mark-search_path-as-GUC_REPORT.patch Description: Binary data

Re: Incorrect file reference in comment in procarray.c

On Thu, Nov 2, 2023 at 10:20 PM Daniel Gustafsson wrote: > > On 2 Nov 2023, at 13:40, Etsuro Fujita wrote: > > Attached is a small patch for that: s/heapam_visibility.c/snapmgr.c/. > > No objections to the patch, the change is correct. However, with git grep and > ctags and other ways of discove

Re: Pre-proposal: unicode normalized text

On Sat, Oct 28, 2023 at 4:15 AM Jeff Davis wrote: > > I plan to commit something like v3 early next week unless someone else > has additional comments or I missed a concern. Hi Jeff, is the CF entry titled "Unicode character general category functions" ready to be marked committed?

Regression on pg_restore to 16.0: DOMAIN not available to SQL function

I'm having errors restoring with pg_restore to v16.0, it appears to be a regression or bug. The same file restored to v15.4 without problem. During the restore: pg_restore: error: could not execute query: ERROR: type "hash" does not exist LINE 7: )::hash; [...] CONTEXT: SQL fun

Re: pg_upgrade and logical replication

On Thu, 2 Nov 2023 at 11:05, Peter Smith wrote: > > ~~~ > > 2c. > In a recent similar thread [1], they chose to implement a guc_hook to > prevent a user from overriding this via the command line option during > the upgrade. Shouldn't this patch do the same thing, for consistency? Added GUC hook f

RE: CRC32C Parallel Computation Optimization on ARM

On Date: Thu, 2 Nov 2023 09:35:50AM -0500, Nathan Bossart wrote: >On Thu, Nov 02, 2023 at 06:17:20AM +, Xiang Gao wrote: >> After reading the discussion, I understand that in order to avoid performance >> regression in some instances, we need to try our best to avoid runtime >> checks. > >I d

Re: Popcount optimization using AVX512

On Thu, 2 Nov 2023 at 15:22, Amonson, Paul D wrote: > > This proposal showcases the speed-up provided to popcount feature when using > AVX512 registers. The intent is to share the preliminary results with the > community and get feedback for adding avx512 support for popcount. > > Revisiting the

Re: Synchronizing slots from primary to standby

On Thu, Nov 2, 2023 at 2:35 PM Zhijie Hou (Fujitsu) wrote: > > Here is the new version patch set(V29) which addressed Peter comments[1][2] > and > fixed one doc compile error. > Few comments: == 1. + +failover (boolean) + + + Specifies whether

Re: Intermittent failure with t/003_logical_slots.pl test on windows

On Thu, Nov 2, 2023 at 11:52 AM Kyotaro Horiguchi wrote: > > At Tue, 31 Oct 2023 18:11:48 +0530, vignesh C wrote in > > Few others are also facing this problem with similar code like in: > > https://stackoverflow.com/questions/15882799/fgets-returning-error-for-file-returned-by-popen > > I'm incl

Re: Regression on pg_restore to 16.0: DOMAIN not available to SQL function

On 2023-11-03 Fr 06:17, Mark Hills wrote: I'm having errors restoring with pg_restore to v16.0, it appears to be a regression or bug. The same file restored to v15.4 without problem. During the restore: pg_restore: error: could not execute query: ERROR: type "hash" does not exist LINE

Re: [PoC] Federated Authn/z with OAUTHBEARER

Hi, On Fri, 3 Nov 2023 at 17:14, Jacob Champion wrote: > > v12 implements a first draft of a client hook, so applications can > replace either the device prompt or the entire OAuth flow. (Andrey and > Mahendrakar: hopefully this is close to what you need.) It also cleans > up some of the JSON tec

Re: RFC: Logging plan of the running query

I have following questions related to the functionality. (Please point me to the relevant discussion if this has been already discussed.) 1. When a backend is running nested queries, we will see the plan of the innermost query. That query may not be the actual culprit if the user query is running

Re: Regression on pg_restore to 16.0: DOMAIN not available to SQL function

On Friday, November 3, 2023, Mark Hills wrote: > > pg_restore: error: could not execute query: ERROR: type "hash" does not > exist > LINE 7: )::hash; > [...] > CONTEXT: SQL function "gen_hash" during inlining > > -- > -- Relevant SQL declarations > -- > Those were not all of th

Re: meson documentation build open issues

Re: Peter Eisentraut > > "meson compile" doesn't seem to build the docs by default ( see > > ), > > and I'd rather it didn't, building the docs is a separate and optional > > step for the bu

Re: Regression on pg_restore to 16.0: DOMAIN not available to SQL function

Mark Hills writes: > I'm having errors restoring with pg_restore to v16.0, it appears to be a > regression or bug. The same file restored to v15.4 without problem. > During the restore: > pg_restore: error: could not execute query: ERROR: type "hash" does not > exist > LINE 7: )::

Re: Improve WALRead() to suck data directly from WAL buffers when possible

On Fri, Nov 3, 2023 at 12:35 PM Jeff Davis wrote: > > On Thu, 2023-11-02 at 22:38 +0530, Bharath Rupireddy wrote: > > > I suppose the question is: should reading from the WAL buffers an > > > intentional thing that the caller does explicitly by specific > > > callers? > > > Or is it an optimizatio

Re: remaining sql/json patches

Hi! Read Tom Lane's note in previous discussion (quite long, so I've missed it) on pg_proc column - >I strongly recommend against having a new pg_proc column at all. >I doubt that you really need it, and having one will create >enormous mechanical burdens to making the conversion. (For example,

Re: [PATCH] Add XMLText function (SQL/XML X038)

> On 25 Aug 2023, at 17:40, Jim Jones wrote: > On 25.08.23 16:49, Vik Fearing wrote: >> I do not think this should be addressed in this patch because there are >> quite a lot of functions that need to handle this. > > v4 attached. I had a look at v4 of this patch and apart from pgindenting and

Re: Regression on pg_restore to 16.0: DOMAIN not available to SQL function

On Fri, 3 Nov 2023, Tom Lane wrote: > Mark Hills writes: > > I'm having errors restoring with pg_restore to v16.0, it appears to be a > > regression or bug. The same file restored to v15.4 without problem. > > > During the restore: > > > pg_restore: error: could not execute query: ERROR: ty

Re: [PATCH] Add XMLText function (SQL/XML X038)

On 11/3/23 16:30, Daniel Gustafsson wrote: On 25 Aug 2023, at 17:40, Jim Jones wrote: Just like Vik says upthread we can't really claim X038 conformance without a disclaimer, so I've added a 0002 which adds this to the XML spec conformance page in the docs. We should put a short version of

Re: Regression on pg_restore to 16.0: DOMAIN not available to SQL function

Mark Hills writes: > On Fri, 3 Nov 2023, Tom Lane wrote: >> However, then it's not clear why it would've worked >> in 15.4 which does the same thing. I wonder whether you are >> using this function in a column default for the troublesome >> table. > Yes, it's just a simple DEFAULT: > CREATE T

Re: [PATCH] Add XMLText function (SQL/XML X038)

Hi Daniel, hi Vik, Thanks a lot for the review! On 03.11.23 16:45, Vik Fearing wrote: > We should put a short version of the disclaimer in sql_features.txt as > well. You mean to add a disclaimer in the X038 entry? Something along these lines perhaps? X038    XMLText            YES     It does n

Re: meson documentation build open issues

Hi, On 2023-11-03 15:26:05 +0100, Christoph Berg wrote: > Re: Peter Eisentraut > > > "meson compile" doesn't seem to build the docs by default ( see > > > ), > > > and I'd rather it didn't,

Re: MergeJoin beats HashJoin in the case of multiple hash clauses

On 9/11/23 10:04, Lepikhov Andrei wrote: > > > On Mon, Sep 11, 2023, at 11:51 AM, Andy Fan wrote: >> Hi, >> >> On Thu, Jun 15, 2023 at 4:30 PM Andrey Lepikhov >> wrote: >>> Hi, all. >>> >>> Some of my clients use JOIN's with three - four clauses. Quite >>> frequently, I see complaints on unre

Re: Version 14/15 documentation Section "Alter Default Privileges"

On Sat, Oct 28, 2023 at 11:01:59AM +0200, Michael Banck wrote: > On Fri, Oct 27, 2023 at 05:49:42PM +0200, Laurenz Albe wrote: > > True. I have done that in the attached patch. > > In this patch, it is mentioned *twice* that ALTER DEFAULT PRIVILEGES > > only affects objects created by the current

Re: Document aggregate functions better w.r.t. ORDER BY

On Thu, Oct 26, 2023 at 04:05:12PM -0700, David G. Johnston wrote: > On Thu, Oct 26, 2023 at 4:03 PM Bruce Momjian wrote: > > > Sure, done in the attached patch. > > > > WFM.  Thank You! Patch applied to master. -- Bruce Momjian https://momjian.us EDB

Re: [PATCH] Add XMLText function (SQL/XML X038)

On 11/3/23 17:14, Jim Jones wrote: Hi Daniel, hi Vik, Thanks a lot for the review! On 03.11.23 16:45, Vik Fearing wrote: We should put a short version of the disclaimer in sql_features.txt as well. You mean to add a disclaimer in the X038 entry? Something along these lines perhaps? X038    X

Re: meson documentation build open issues

Re: Andres Freund > > > You can control this with the "docs" option for meson, as of recently. > > > > I've been looking into switching the Debian PG 17 build to meson, but > > I'm running into several problems. > > > > * The docs are still not built by default, and -Ddocs=enabled doesn't > > cha

Re: brininsert optimization opportunity

Hi, I took a look at this patch today. I had to rebase the patch, due to some minor bitrot related to 9f0602539d (but nothing major). I also did a couple tiny cosmetic tweaks, but other than that the patch seems OK. See the attached v6. I did some simple performance tests too, similar to those in

Re: Pre-proposal: unicode normalized text

On Fri, 2023-11-03 at 21:01 +1300, David Rowley wrote: > Thomas mentioned this to me earlier today. After looking I also > concluded that unicode_category.c needed to be added to > @pgcommonallfiles. After looking at the time, I didn't expect you to > be around so opted just to push that to fix the

Re: Pre-proposal: unicode normalized text

On Fri, 2023-11-03 at 17:11 +0700, John Naylor wrote: > On Sat, Oct 28, 2023 at 4:15 AM Jeff Davis wrote: > > > > I plan to commit something like v3 early next week unless someone > > else > > has additional comments or I missed a concern. > > Hi Jeff, is the CF entry titled "Unicode character g

Re: DRAFT GIST support for ORDER BY

On Mon, 30 Oct 2023 at 14:39, Michał Kłeczek wrote: >> On 30 Oct 2023, at 13:31, Matthias van de Meent >> wrote: >> >>> The problem is though that right now handling of ORDER BY column clauses is >>> tightly coupled to BTree. >>> It would be good to refactor the code so that semantics of ORDER

Re: meson documentation build open issues

Hi, On 2023-11-03 19:19:17 +0100, Christoph Berg wrote: > Re: Andres Freund > > > > You can control this with the "docs" option for meson, as of recently. > > > > > > I've been looking into switching the Debian PG 17 build to meson, but > > > I'm running into several problems. > > > > > > * The do

Re: brininsert optimization opportunity

On Fri, 3 Nov 2023 at 19:37, Tomas Vondra wrote: > > Hi, > > I took a look at this patch today. I had to rebase the patch, due to > some minor bitrot related to 9f0602539d (but nothing major). I also did > a couple tiny cosmetic tweaks, but other than that the patch seems OK. > See the attached v6

Re: meson documentation build open issues

Re: Andres Freund > The reason for that is simply that the docs take too long to build. That why I'd prefer to be able to separate arch:all and arch:any builds, yes. > The summary does include both. LLVM is 'llvm', man/html docs is 'docs' and pdf > docs as 'docs_pdf'. Sorry, I should have looked

Re: Improve WALRead() to suck data directly from WAL buffers when possible

On Fri, 2023-11-03 at 20:23 +0530, Bharath Rupireddy wrote: > > > OldestInitializedPage is introduced in v14-0001 patch. Please have a > look. I don't see why that's necessary if we move to the algorithm I suggested below that doesn't require a lock. > > > Okay. Current patch doesn't support th

Re: trying again to get incremental backup

On Wed, Nov 1, 2023 at 8:57 AM Jakub Wartak wrote: > Thanks for answering! It all sounds like this > resync-standby-using-primary-incrbackup idea isn't fit for the current > pg_combinebackup, but rather for a new tool hopefully in future. It > could take the current LSN from stuck standby, calcula

Re: Pre-proposal: unicode normalized text

On 2023-10-04 23:32, Chapman Flack wrote: Well, for what reason does anybody run PG now with the encoding set to anything besides UTF-8? I don't really have my finger on that pulse. Could it be that it bloats common strings in their local script, and with enough of those to store, it could matter

Re: [PATCH] Add XMLText function (SQL/XML X038)

On 03.11.23 19:05, Vik Fearing wrote: > I was thinking of something much shorter than that.  Such as > >     X038    XMLText YES supported except for RETURNING v6 attached includes this change and the doc addition from Daniel. Thanks! -- JimFrom 703c882c254826f10f7bc076a2071741d086e8f6

Re: Performance issues with parallelism and LIMIT

On 2/22/23 13:22, Tomas Vondra wrote: > ... > >>> No opinion on these options, but worth a try. Alternatively, we could >>> try the usual doubling approach - start with a low threshold (and set >>> the latch frequently), and then gradually increase it up to the 1/4. >>> >>> That should work both f

Re: meson documentation build open issues

Hi, On 2023-11-03 20:19:18 +0100, Christoph Berg wrote: > Re: Andres Freund > > The reason for that is simply that the docs take too long to build. > > That why I'd prefer to be able to separate arch:all and arch:any > builds, yes. What's stopping you from doing that? I think the only arch:any

Re: Unnecessary confirm work on logical replication

On 4/11/23 14:58, Emre Hasegeli wrote: >> In fact, the WAL sender always starts reading WAL from restart_lsn, >> which in turn is always <= confirmed_flush_lsn. While reading WAL, WAL >> sender may read XLOG_RUNNING_XACTS WAL record with lsn <= >> confirmed_flush_lsn. While processing XLOG_RUNNING_

Draft back-branch release notes are up

I've finished the first draft of 16.1 release notes; see https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1e774846b870a858f8eb88b3f512562009177f45 Please send comments/corrections by Sunday. regards, tom lane

Re: Pre-proposal: unicode normalized text

On Fri, Nov 3, 2023 at 9:01 PM David Rowley wrote: > On Fri, 3 Nov 2023 at 20:49, Jeff Davis wrote: > > On Fri, 2023-11-03 at 10:51 +1300, Thomas Munro wrote: > > > bowerbird and hammerkop didn't like commit a02b37fc. They're still > > > using the old 3rd build system that is not tested by CI.

Re: Inconsistent use of "volatile" when accessing shared memory?

Hi, On 2023-11-02 23:19:03 -0700, Jeff Davis wrote: > I noticed that we occasionally use "volatile" to access shared memory, > but usually not; and I'm not clear on the rules for doing so. For > instance, AdvanceXLInsertBuffer() writes to XLogCtl->xlblocks[nextidx] > through a volatile pointer; bu

Re: [PoC] Federated Authn/z with OAUTHBEARER

On Fri, Nov 3, 2023 at 5:28 AM Shlok Kyal wrote: > Just want to make sure you are aware of these failures. Thanks for the nudge! Looks like I need to reconcile with the changes to JsonLexContext in 1c99cde2. I should be able to get to that next week; in the meantime I'll mark it Waiting on Author

Re: Improve WALRead() to suck data directly from WAL buffers when possible

Hi, On 2023-11-03 20:23:30 +0530, Bharath Rupireddy wrote: > On Fri, Nov 3, 2023 at 12:35 PM Jeff Davis wrote: > > > > On Thu, 2023-11-02 at 22:38 +0530, Bharath Rupireddy wrote: > > > > I suppose the question is: should reading from the WAL buffers an > > > > intentional thing that the caller do

Re: Improve WALRead() to suck data directly from WAL buffers when possible

Hi, On 2023-11-02 22:38:38 +0530, Bharath Rupireddy wrote: > From 5b5469d7dcd8e98bfcaf14227e67356bbc1f5fe8 Mon Sep 17 00:00:00 2001 > From: Bharath Rupireddy > Date: Thu, 2 Nov 2023 15:10:51 + > Subject: [PATCH v14] Track oldest initialized WAL buffer page > > --- > src/backend/access/transa

Re: Inconsistent use of "volatile" when accessing shared memory?

On Fri, 2023-11-03 at 15:59 -0700, Andres Freund wrote: > I don't think so. We used to use volatile for most shared memory > accesses, but > volatile doesn't provide particularly useful semantics - and > generates > *vastly* slower code in a lot of circumstances. Most of that usage > predates > spi

Re: Explicitly skip TAP tests under Meson if disabled

Hi, On 2023-10-30 05:45:52 -0400, Peter Eisentraut wrote: > Under Meson, it is not very easy to see if TAP tests have been enabled or > disabled, if you rely on the default auto setting. You either need to > carefully study the meson setup output, or you notice, what a minute, didn't > there use

Re: Inconsistent use of "volatile" when accessing shared memory?

Hi, On 2023-11-03 17:44:44 -0700, Jeff Davis wrote: > On Fri, 2023-11-03 at 15:59 -0700, Andres Freund wrote: > > I don't think so. We used to use volatile for most shared memory > > accesses, but > > volatile doesn't provide particularly useful semantics - and > > generates > > *vastly* slower co

Re: Add new option 'all' to pg_stat_reset_shared()

Hi, On 2023-11-03 00:55:00 +0530, Bharath Rupireddy wrote: > On Wed, Nov 1, 2023 at 4:24 AM Michael Paquier wrote: > > > > On Tue, Oct 31, 2023 at 04:26:18PM +0900, torikoshia wrote: > > > Yes, calling pg_stat_reset_shared() for all stats types can do what I > > > wanted > > > to do. > > > But c

Re: make pg_ctl more friendly

Hi, On 2023-11-02 14:50:14 +0800, Crisp Lee wrote: > I got a basebackup using pg_basebackup -R. After that, I created a restore > point named test on primary, and set recovery_target_name to test, > recovery_target_action to shutdown in standby datadir. I got a failure > startup message after 'pg

Re: Something seems weird inside tts_virtual_copyslot()

Hi, On 2023-11-01 11:35:50 +1300, David Rowley wrote: > Looking at the Assert inside tts_virtual_copyslot(), it does: > > Assert(srcdesc->natts <= dstslot->tts_tupleDescriptor->natts); I think that assert was intended to be the other way round. > So, that seems to indicate that it's ok for the

Re: Moving forward with TDE [PATCH v3]

Hi, On 2023-11-02 22:09:40 +0100, Matthias van de Meent wrote: > I'm quite surprised at the significant number of changes being made > outside the core storage manager files. I thought that changing out > mdsmgr with an encrypted smgr (that could wrap mdsmgr if so desired) > would be the most obvi

Re: Pre-proposal: unicode normalized text

On Sat, 4 Nov 2023 at 10:57, Thomas Munro wrote: > > On Fri, Nov 3, 2023 at 9:01 PM David Rowley wrote: > > On Fri, 3 Nov 2023 at 20:49, Jeff Davis wrote: > > > I think I just need to add unicode_category.c to @pgcommonallfiles in > > > Mkvcbuild.pm. I'll do a trial commit tomorrow and see if th

Re: Moving forward with TDE [PATCH v3]

On 2023-11-02 19:32:28 -0700, Andres Freund wrote: > > From 327e86d52be1df8de9c3a324cb06b85ba5db9604 Mon Sep 17 00:00:00 2001 > > From: David Christensen > > Date: Fri, 29 Sep 2023 15:16:00 -0400 > > Subject: [PATCH v3 5/5] Add encrypted/authenticated WAL > > > > When using an encrypted cluster, w

Re: Add the ability to limit the amount of memory that can be allocated to backends.

Hi, On 2023-10-31 17:11:26 +, John Morris wrote: > Postgres memory reservations come from multiple sources. > > * Malloc calls made by the Postgres memory allocators. > * Static shared memory created by the postmaster at server startup, > * Dynamic shared memory created by the bac

Re: Version 14/15 documentation Section "Alter Default Privileges"

On Fri, 2023-11-03 at 12:53 -0400, Bruce Momjian wrote: > I have developed the attached patch on top of the alter default patch I > just applied. It is more radical, making FOR ROLE clearer, and also > moving my new FOR ROLE text up to the first paragraph, and reordering > the paragraphs to be cle