Re: BF member drongo doesn't like 035_standby_logical_decoding.pl

2025-01-24 Thread Tom Lane
Alexander Lakhin writes: > There is also a reference to a discussion of the failure there: > https://www.postgresql.org/message-id/657815a2-5a89-fcc1-1c9d-d77a6986b...@gmail.com > (In short, I observed that that test suffers from bgwriter's activity.) Hmm, that's an interesting theory, but it fai

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Jim Jones
Hi On 24.01.25 17:18, Chapman Flack wrote: > Or even: ... An XML Query "document node" is a relaxed version > of XML document structure that corresponds exactly to what > PostgreSQL's one XML type is already allowed to contain, so > any non-null PostgreSQL XML value can be returned unchanged. > Mo

Re: Eagerly scan all-visible pages to amortize aggressive vacuum

2025-01-24 Thread Melanie Plageman
Thanks for the review! Attached v10 addresses the review feedback except that about the GUC/table option meaning and format. On Thu, Jan 23, 2025 at 2:22 PM Robert Haas wrote: > > On Thu, Jan 23, 2025 at 1:31 PM Robert Haas wrote: > > > > On Wed, Jan 22, 2025 at 5:48 PM Melanie Plageman > > wro

Re: Reorder shutdown sequence, to flush pgstats later

2025-01-24 Thread Andres Freund
Hi, Attached is a new version of this patchset: - HandleFatalError now doesn't expect to be called if already in FatalError or ImmediateShutdown and contains a comment and assertions to that effect - Fatal errors when in pmState > PM_WAIT_BACKENDS go to PM_WAIT_DEAD_END directly. The previou

Re: Windows: openssl & gssapi dislike each other

2025-01-24 Thread Andrew Dunstan
On 2025-01-22 We 4:25 AM, Dave Page wrote: On Wed, 22 Jan 2025 at 09:17, Daniel Gustafsson wrote: > On 10 Jul 2024, at 19:06, Imran Zaheer wrote: (Reviving an old thread to give them a chance to finish before v18) > For now maybe we can do the future proofing for gssapi & open

Re: Casts from jsonb to other types should cope with json null

2025-01-24 Thread Tom Lane
I wrote: > OK. Nobody has spoken against the 0001 patch (replace errors with > return-a-null), so I think I'll go ahead and commit that one. > Then I'll return to this thread with a fleshed-out patch for 0002. 0001 is pushed, and as promised, here's a version of 0002 extended to cover all the str

Re: Eager aggregation, take 3

2025-01-24 Thread Robert Haas
On Wed, Jan 22, 2025 at 1:48 AM Richard Guo wrote: > This approach would require injecting multiple intermediate > aggregation nodes into the path tree, for which we currently lack the > necessary architecture. As a result, I didn't pursue this idea > further. However, I'm really glad you mentio

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Chapman Flack
On 01/24/25 14:59, Jim Jones wrote: > In the XML Query standard, a "document node" represents a relaxed > version of an XML document structure. This corresponds to what > PostgreSQL's single XML type allows, meaning that any valid non-null > PostgreSQL XML value can be returned unchanged. Other sys

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Tom Lane
Andrew Dunstan writes: > On 2025-01-24 Fr 10:57 AM, Tom Lane wrote: >> Looks like alligator needs some help here too. > That's an issue with the new TAP test - alligator isn't running the > TestSepgsql module. lapwing has also had a TAP test failure. Hmm. Neither of those animals should be try

Re: BF member drongo doesn't like 035_standby_logical_decoding.pl

2025-01-24 Thread Andres Freund
Hi, On 2025-01-24 14:56:45 -0500, Tom Lane wrote: > Alexander Lakhin writes: > > There is also a reference to a discussion of the failure there: > > https://www.postgresql.org/message-id/657815a2-5a89-fcc1-1c9d-d77a6986b...@gmail.com > > (In short, I observed that that test suffers from bgwriter'

Re: Eagerly scan all-visible pages to amortize aggressive vacuum

2025-01-24 Thread Robert Haas
On Fri, Jan 24, 2025 at 3:02 PM Melanie Plageman wrote: > This thought exercise made me realize something is wrong with my > current patch, though. If you set the failure tolerance > (vacuum_eager_scan_max_fails) to 0 right now, it disables eager > scanning altogether. That might be unexpected. Yo

RE: Proposal for Updating CRC32C with AVX-512 Algorithm.

2025-01-24 Thread Devulapalli, Raghuveer
Hi John, Thanks for your summary and here are responses: > #1 - The choice of AVX-512. There is no such thing as a "CRC instruction > operating > on 8 bytes", and the proposed algorithm is a multistep process using carryless > multiplication and requiring at least 256 bytes of input. The Chrom

Re: vacuumdb changes for stats import/export

2025-01-24 Thread Masahiko Sawada
On Fri, Jan 24, 2025 at 7:44 AM Nathan Bossart wrote: > > On Mon, Jan 06, 2025 at 03:27:18PM -0600, Nathan Bossart wrote: > > On Mon, Dec 30, 2024 at 03:45:03PM -0500, Bruce Momjian wrote: > >> On Mon, Dec 30, 2024 at 12:02:47PM -0800, Jeff Davis wrote: > >>> I suggest that we make a new thread ab

Re: [PATCH] SVE popcount support

2025-01-24 Thread Nathan Bossart
The meson configure check seems to fail on my machine: error: too many arguments to function call, expected 0, have 1 10 | svuint64_t popcnt = svcntb(val); | ~~ ^~~ error: returning '__SVInt64_t' from a function with incompa

Re: Modern SHA2- based password hashes for pgcrypto

2025-01-24 Thread Alvaro Herrera
On 2025-Jan-24, Bernd Helmle wrote: > So we behave exactly the same way as px_crypt_md5(): It stops after the > first '$' after the magic byte preamble. For shacrypt, this could be > the next '$' after the closing one of the non-mandatory 'rounds' > option, but with your example this doesn't happe

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

2025-01-24 Thread Masahiko Sawada
On Wed, Jan 22, 2025 at 11:42 PM Bertrand Drouvot wrote: > > Hi, > > On Wed, Jan 22, 2025 at 04:46:00PM -0800, Masahiko Sawada wrote: > > I would like to summarize the proposed approaches thus far: > > Thanks! > > > Regarding the user interface, there are three approaches: > > > > 1. Implementing

Re: BF member drongo doesn't like 035_standby_logical_decoding.pl

2025-01-24 Thread Andres Freund
Hi, On 2025-01-24 14:23:26 -0500, Andres Freund wrote: > On 2025-01-24 11:42:15 -0500, Tom Lane wrote: > There's a failure before this one: > [05:23:54.643](0.013s) not ok 23 - activeslot slot invalidation is logged > with vacuum on pg_class > > > > This has been happening for some time, in all t

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Andrew Dunstan
On 2025-01-24 Fr 10:57 AM, Tom Lane wrote: Andrew Dunstan writes: On 2025-01-24 Fr 7:25 AM, Peter Eisentraut wrote: This has been committed.  And I understand there is a buildfarm client update available for the affected buildfarm members. This should only be rhinoceros. Joe can pull this f

Re: why -Fdance archive format option works with ./pg_restore but not with ./pg_dump?

2025-01-24 Thread Andrew Dunstan
On 2025-01-24 Fr 10:24 AM, Tom Lane wrote: Andrew Dunstan writes: I don't think we need a new file for this. pg_backup_utils.c is already there for routines common to pg_restore and pg_dump. I'm not even on board with having a new function, because I doubt we should try to share this code in

Re: BF member drongo doesn't like 035_standby_logical_decoding.pl

2025-01-24 Thread Andres Freund
Hi, On 2025-01-24 21:00:00 +0200, Alexander Lakhin wrote: > 24.01.2025 18:42, Tom Lane wrote: > > I realized just now that drongo has been intermittently failing like this: > > > > 147/256 postgresql:recovery / recovery/035_standby_logical_decoding > > ERROR 2116.16s (ex

Re: Modern SHA2- based password hashes for pgcrypto

2025-01-24 Thread Bernd Helmle
Am Donnerstag, dem 23.01.2025 um 21:36 +0800 schrieb Japin Li: Thanks for your review again. I am going to work on the other items, but this one might need further discussion: > 5. > Does the following work as expected? > > postgres=# select crypt('hello', > '$5$$6$rounds=1$/Zg436s2vmTwsoSz'

Re: Return pg_control from pg_backup_stop().

2025-01-24 Thread David Steele
On 11/20/24 17:44, David Steele wrote: On 10/3/24 05:11, David Steele wrote: On 10/3/24 07:45, Michael Paquier wrote: 1) is something that has more value than 2), IMO, because there is no need for a manual step when a backup is taken by the replication protocol.  Well, custom backup solutions

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

2025-01-24 Thread Masahiko Sawada
On Thu, Jan 23, 2025 at 3:24 AM Ashutosh Bapat wrote: > > On Thu, Jan 23, 2025 at 6:16 AM Masahiko Sawada wrote: > > > > On Fri, Jan 10, 2025 at 12:33 AM Masahiko Sawada > > wrote: > > > > > > On Thu, Jan 9, 2025 at 3:29 AM Ashutosh Bapat > > > wrote: > > > > > > > > On Tue, Dec 31, 2024 at 10

Re: BF member drongo doesn't like 035_standby_logical_decoding.pl

2025-01-24 Thread Andres Freund
Hi, Thanks for noticing the pattern. On 2025-01-24 11:42:15 -0500, Tom Lane wrote: > I realized just now that drongo has been intermittently failing like this: > > 147/256 postgresql:recovery / recovery/035_standby_logical_decoding > ERROR 2116.16s (exit status 255 or si

Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)

2025-01-24 Thread Benoit Lobréau
Hi, Thank you for the patch! I've had a need for this feature several times, so I appreciate the work you’ve put into it. I like the new name VISIBLE/INVISIBLE and the fact that it's a separate flag in pg_index (it's easy to monitor). I don’t feel qualified to provide an opinion on the code itse

Re: create subscription with (origin = none, copy_data = on)

2025-01-24 Thread Sergey Tatarintsev
24.01.2025 07:22, Shlok Kyal пишет: On Thu, 23 Jan 2025 at 17:54, Zhijie Hou (Fujitsu) wrote: On Thursday, January 23, 2025 4:43 PM Shlok Kyal wrote: On Thu, 23 Jan 2025 at 12:35, Shlok Kyal wrote: On Wed, 22 Jan 2025 at 09:00, Zhijie Hou (Fujitsu) wrote: Thanks for the patch. I agree

RE: Windows meson build

2025-01-24 Thread Kohei Harikae (Fujitsu)
Hi, My previous patch had whitespace issues. I recreated the patch. I will create a commitfest with this patch. Regards, Kohei Harikae installation-meson.patch Description: installation-meson.patch

Re: doc: explain pgstatindex fragmentation

2025-01-24 Thread Bertrand Drouvot
Hi, On Fri, Jan 24, 2025 at 12:34:08PM +0100, Frédéric Yhuel wrote: > I don't know if you noticed Laurenz's suggestion, because he forgot to CC > you, but I like it very much. I think we should mention the default > fillfactor (90 for indexes). Thanks for mentioning Laurenz's suggestion! === 1

Re: Enhancing Memory Context Statistics Reporting

2025-01-24 Thread Rahila Syed
Hi, > > Just idea; as an another option, how about blocking new requests to > the target process (e.g., causing them to fail with an error or > returning NULL with a warning) if a previous request is still pending? > Users can simply retry the request if it fails. IMO failing quickly > seems prefe

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Jim Jones
On 24.01.25 13:48, Pavel Stehule wrote: > Lot of users use XML functions without XMLDOCUMENT now. The doc should to > help with a reply to question where and when I need (I can) use this > function. This should > be in context of Postgres where every XML expr returns XML value, so > sentence > cr

Re: Replace current implementations in crypt() and gen_salt() to OpenSSL

2025-01-24 Thread Daniel Gustafsson
> On 23 Jan 2025, at 21:42, Joe Conway wrote: > LGTM After staring at it a bit more and fixing a few small details I committed this. -- Daniel Gustafsson

Re: Replace current implementations in crypt() and gen_salt() to OpenSSL

2025-01-24 Thread Joe Conway
On 1/24/25 08:55, Daniel Gustafsson wrote: On 23 Jan 2025, at 21:42, Joe Conway wrote: LGTM After staring at it a bit more and fixing a few small details I committed this. \o/ Thanks for getting this over the finish line! -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databa

Re: doc: explain pgstatindex fragmentation

2025-01-24 Thread Laurenz Albe
On Fri, 2025-01-24 at 13:34 +, Bertrand Drouvot wrote: > + Since indexes have a default fillfactor of 90, this should be around 0.9 for > + newly built indexes > > I think 0.9 should be replaced by 90 (that's the actual kind of output we'd > get). > > But having said that, I'm not sure we sh

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Andrew Dunstan
On 2025-01-24 Fr 7:25 AM, Peter Eisentraut wrote: On 27.08.24 10:12, Peter Eisentraut wrote: Here is a new patch version. I simplified the uses of sed and awk inside the Perl script.  I also fixed "make installcheck".  I noticed that meson installs sepgsql.sql into the wrong directory, so t

Re: BF member drongo doesn't like 035_standby_logical_decoding.pl

2025-01-24 Thread Alexander Lakhin
Hello Tom, 24.01.2025 18:42, Tom Lane wrote: I realized just now that drongo has been intermittently failing like this: 147/256 postgresql:recovery / recovery/035_standby_logical_decoding ERROR 2116.16s (exit status 255 or signal 127 SIGinvalid) ---

Re: pg_createsubscriber TAP test wrapping makes command options hard to read.

2025-01-24 Thread Dagfinn Ilmari Mannsåker
Tom Lane writes: > Michael Paquier writes: >> On Thu, Jan 23, 2025 at 08:25:45PM +, Dagfinn Ilmari Mannsåker wrote: >>> Here's a patch for that. > >> Thanks. I had a bit of time today and applied it. > > BF member drongo doesn't like the new test for amcheck. > Looks like it has to do with

Re: POC: track vacuum/analyze cumulative time per relation

2025-01-24 Thread Sami Imseih
> I was referring to the order of the fields in the structure itself, > but that's no big deal one way or the other. I understand your point now. I will group them with the related counters in the next rev and will use > This should be one comment for the whole block, or this should use the > sin

Re: Windows: openssl & gssapi dislike each other

2025-01-24 Thread Dave Page
On Fri, 24 Jan 2025 at 20:07, Andrew Dunstan wrote: > > On 2025-01-22 We 4:25 AM, Dave Page wrote: > > > > On Wed, 22 Jan 2025 at 09:17, Daniel Gustafsson wrote: > >> > On 10 Jul 2024, at 19:06, Imran Zaheer wrote: >> >> (Reviving an old thread to give them a chance to finish before v18) >> >>

Re: Windows: openssl & gssapi dislike each other

2025-01-24 Thread Daniel Gustafsson
> On 24 Jan 2025, at 21:07, Andrew Dunstan wrote: > On 2025-01-22 We 4:25 AM, Dave Page wrote: >> Anyway, no fix was committed as far as I know. I would suggest it should be >> back-patched as well. > > I'm quite partial to the approach suggested upthread by Andres (a separate > pg_gssapi.h f

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Andrew Dunstan
On 2025-01-24 Fr 4:07 PM, Tom Lane wrote: Andrew Dunstan writes: On 2025-01-24 Fr 10:57 AM, Tom Lane wrote: Looks like alligator needs some help here too. That's an issue with the new TAP test - alligator isn't running the TestSepgsql module. lapwing has also had a TAP test failure. Hmm. Ne

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Jim Jones
On 24.01.25 22:01, Chapman Flack wrote: > It seems to me the key connection there is that the ISO SQL standard > defines XMLDOCUMENT by equivalence to what `document { $expr }` means > in the W3 XML Query standard. It seems I missed one sentence. My bad. Next try... :) The xmldocument functio

Re: Enhancing Memory Context Statistics Reporting

2025-01-24 Thread Tomas Vondra
On 1/24/25 14:47, Rahila Syed wrote: > > Hi, > > > Just idea; as an another option, how about blocking new requests to > the target process (e.g., causing them to fail with an error or > returning NULL with a warning) if a previous request is still pending? > Users can simply

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Tom Lane
Andrew Dunstan writes: > On 2025-01-24 Fr 4:07 PM, Tom Lane wrote: >> Looking at the buildfarm client script, it looks to me like it >> will unconditionally try to run TAP tests in every contrib directory >> that has a "t" subdirectory. Up to now, none of those needed to >> be conditional ... but

Re: [RFC] Lock-free XLog Reservation from WAL

2025-01-24 Thread Japin Li
On Thu, 23 Jan 2025 at 21:44, Japin Li wrote: > On Thu, 23 Jan 2025 at 15:03, Yura Sokolov wrote: >> 23.01.2025 11:46, Japin Li пишет: >>> On Wed, 22 Jan 2025 at 22:44, Japin Li wrote: On Wed, 22 Jan 2025 at 17:02, Yura Sokolov wrote: > I believe, I know why it happens: I was in

Re: Show WAL write and fsync stats in pg_stat_io

2025-01-24 Thread Nazir Bilal Yavuz
Hi, On Wed, 22 Jan 2025 at 03:14, Michael Paquier wrote: > > On Thu, Jan 16, 2025 at 11:40:51AM +0300, Nazir Bilal Yavuz wrote: > > I encountered another problem while rebasing the patch. The problem is > > basically we do not expect any pending stats while restoring the stats > > at the initdb.

Re: why -Fdance archive format option works with ./pg_restore but not with ./pg_dump?

2025-01-24 Thread Srinath Reddy
On Fri, Jan 24, 2025 at 1:42 AM Andrew Dunstan wrote: > > `git blame` tells me that this switch statement goes back to 2001. > Seeking a reason at this distance is unlikely to be productive. Probably > we were not nearly as careful then as we are now about such things. Feel > free to submit a pat

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Jim Jones
Hi On 24.01.25 07:28, Pavel Stehule wrote: > I think documentation should be strongly enhanced. This is probably > the hardest part of this patch - explain well what this function does > and what it doesn't. You mean something like this? Or perhaps something more technical? The xmldocument funct

Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

2025-01-24 Thread Oliver Ford
On Thu, Jan 23, 2025 at 6:27 AM Tatsuo Ishii wrote: > > Another possible problem is, probably the code does not work well if > there are multiple partitions. Since win_nonnulls stores currentpos in > a partition, when the partition ends, win_nonnulls needs to be > reset. Otherwise, it mistakenly r

Re: Enhance 'pg_createsubscriber' to retrieve databases automatically when no database is provided.

2025-01-24 Thread Ashutosh Bapat
On Fri, Jan 24, 2025 at 8:14 AM Peter Smith wrote: > > On Thu, Jan 23, 2025 at 10:33 PM Ashutosh Bapat > wrote: > > > > On Wed, Jan 22, 2025 at 7:29 PM Shubham Khanna > > wrote: > > > > > > Hi all, > > > > > > I am writing to propose an enhancement to the pg_createsubscriber > > > utility that e

Re: [PATCH] Optionally record Plan IDs to track plan changes for a query

2025-01-24 Thread Andrei Lepikhov
On 1/3/25 03:46, Lukas Fittl wrote: My overall perspective is that (1) is best done in-core to keep overhead low, whilst (2) could be done outside of core (or merged with a future pg_stat_statements) and is included here mainly for illustration purposes. Thank you for the patch and your attenti

Re: Purpose of wal_init_zero

2025-01-24 Thread Hannu Krosing
On Wed, Jan 22, 2025 at 10:18 PM Robert Pang wrote: > > On Wed, Jan 15, 2025 at 12:05 PM Andres Freund wrote: > > > > If you have wal_recycle=true, this overhead will only be paid the first > > time a > > WAL segment is used, of course, not after recycling. > > Today, our pg_stat_wal view [1] do

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Pavel Stehule
pá 24. 1. 2025 v 14:48 odesílatel Jim Jones napsal: > > On 24.01.25 13:48, Pavel Stehule wrote: > > Lot of users use XML functions without XMLDOCUMENT now. The doc should to > > help with a reply to question where and when I need (I can) use this > > function. This should > > be in context of Pos

Re: Show WAL write and fsync stats in pg_stat_io

2025-01-24 Thread Nazir Bilal Yavuz
Hi, Thanks for looking into this! On Fri, 24 Jan 2025 at 17:20, Bertrand Drouvot wrote: > > I did not look at the code yet but did a few tests. > I can see diff between pg_stat_wal and pg_stat_io, for example: > > " > postgres=# select pg_stat_reset_shared(); > pg_stat_reset_shared > --

Re: SQL Property Graph Queries (SQL/PGQ)

2025-01-24 Thread Junwang Zhao
On Fri, Jan 24, 2025 at 9:31 PM Ashutosh Bapat wrote: > > On Sun, Jan 19, 2025 at 6:45 PM Junwang Zhao wrote: > > > > Hi Ashutosh, > > > > On Wed, Jan 1, 2025 at 5:02 PM Ashutosh Bapat > > wrote: > > > > > > On Wed, Jan 1, 2025 at 2:22 PM Ashutosh Bapat > > > wrote: > > > > > > > > On Sat, Dec

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Chapman Flack
On 01/24/25 08:48, Jim Jones wrote: > In the SQL/XML specification, the XMLDocument (X030) function is > designed to return a document node from a given XML value expression. Maybe we can take advantage of the way that specifications usually don't mandate an implementation, but only results equiva

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Chapman Flack
On 01/24/25 10:49, Chapman Flack wrote: > The SQL-standard `XMLDOCUMENT` function applied to an XML value > /expr/ has effects equivalent to the XML Query expression > `document { /expr/ }`, specified to replace any document nodes > in the input with their children and wrap the whole result in one

Re: vacuumdb changes for stats import/export

2025-01-24 Thread Corey Huinker
> > Thoughts? > I don't have anything to add to what Nathan said, but thought I should say so since this thread was broken off from my earlier thread. Eagerly awaiting feedback.

Re: Eagerly scan all-visible pages to amortize aggressive vacuum

2025-01-24 Thread Robert Haas
On Fri, Jan 24, 2025 at 9:15 AM Melanie Plageman wrote: > So, in this case, there is only one table in question, so 1 autovacuum > worker (and up to 2 maintenance parallel workers for index vacuuming). > The duration I provided is just the absolute duration from start of > vacuum to finish -- not

Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)

2025-01-24 Thread Benoit Lobréau
I also noticed that \d on an index doesn't warn about the invisible state whereas \d on a table does: [local]:5444 postgres@postgres=# SELECT indexrelid::regclass, indisvalid, indisvisible FROM pg_index WHERE indexrelid = 'repli_pkey'::regclass \gx -[ RECORD 1 ]+--- indexrelid | repli_pk

Re: Non-text mode for pg_dumpall

2025-01-24 Thread jian he
On Thu, Jan 23, 2025 at 6:35 PM Mahendra Singh Thalor wrote: > > On Thu, 23 Jan 2025 at 14:59, jian he wrote: > > > > hi. > > The four patches attached are to solve the > > TODO1: We need to think for --exclude-database=PATTERN for pg_restore. > > it is based on your > > v11_pg_dumpall-with-dire

vacuumdb changes for stats import/export

2025-01-24 Thread Nathan Bossart
On Mon, Jan 06, 2025 at 03:27:18PM -0600, Nathan Bossart wrote: > On Mon, Dec 30, 2024 at 03:45:03PM -0500, Bruce Momjian wrote: >> On Mon, Dec 30, 2024 at 12:02:47PM -0800, Jeff Davis wrote: >>> I suggest that we make a new thread about the vacuumdb changes and >>> focus this thread and patch seri

Re: Casts from jsonb to other types should cope with json null

2025-01-24 Thread Tom Lane
Maxim Orlov writes: > I tend to agree with you here about the semantics of such casts. But > consistency and "predictability" of > behaviour for the casts are a bit more important in my view. At least, > based on my experience. So, I'm > for 0002 patch. OK. Nobody has spoken against the 0001 pat

Re: Orphaned users in PG16 and above can only be managed by Superusers

2025-01-24 Thread Nathan Bossart
On Fri, Jan 24, 2025 at 09:53:09AM -0500, Andrew Dunstan wrote: > On 2025-01-23 Th 4:06 PM, Robert Haas wrote: >> On Thu, Jan 23, 2025 at 3:51 PM Andres Freund wrote: >> > I wonder if it's a mistake that a role membership that has WITH ADMIN on >> > another role is silently removed if the member r

Re: SQL Property Graph Queries (SQL/PGQ)

2025-01-24 Thread Junwang Zhao
On Fri, Jan 24, 2025 at 11:46 PM Junwang Zhao wrote: > > On Fri, Jan 24, 2025 at 9:31 PM Ashutosh Bapat > wrote: > > > > On Sun, Jan 19, 2025 at 6:45 PM Junwang Zhao wrote: > > > > > > Hi Ashutosh, > > > > > > On Wed, Jan 1, 2025 at 5:02 PM Ashutosh Bapat > > > wrote: > > > > > > > > On Wed, Ja

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Tom Lane
Andrew Dunstan writes: > On 2025-01-24 Fr 7:25 AM, Peter Eisentraut wrote: >> This has been committed.  And I understand there is a buildfarm client >> update available for the affected buildfarm members. > This should only be rhinoceros. Joe can pull this fix: > https://github.com/PGBuildFarm/

Re: why -Fdance archive format option works with ./pg_restore but not with ./pg_dump?

2025-01-24 Thread Tom Lane
Andrew Dunstan writes: > I don't think we need a new file for this. pg_backup_utils.c is already > there for routines common to pg_restore and pg_dump. I'm not even on board with having a new function, because I doubt we should try to share this code in the first place. Who's to say that pg_dum

Re: Orphaned users in PG16 and above can only be managed by Superusers

2025-01-24 Thread Nathan Bossart
On Thu, Jan 23, 2025 at 03:10:16PM -0500, Tom Lane wrote: > That line of reasoning leads to the same conclusion, that another > built-in role might be a suitable solution --- unless said role is > so powerful that the service providers might want to block access > to it too. Probably limiting it t

Re: pg_createsubscriber TAP test wrapping makes command options hard to read.

2025-01-24 Thread Tom Lane
Michael Paquier writes: > On Thu, Jan 23, 2025 at 08:25:45PM +, Dagfinn Ilmari Mannsåker wrote: >> Here's a patch for that. > Thanks. I had a bit of time today and applied it. BF member drongo doesn't like the new test for amcheck. Looks like it has to do with SSPI authentication producing

Re: Orphaned users in PG16 and above can only be managed by Superusers

2025-01-24 Thread Ashutosh Sharma
On Fri, Jan 24, 2025 at 8:23 PM Andrew Dunstan wrote: > > > On 2025-01-23 Th 4:06 PM, Robert Haas wrote: > > On Thu, Jan 23, 2025 at 3:51 PM Andres Freund wrote: > >> I wonder if it's a mistake that a role membership that has WITH ADMIN on > >> another role is silently removed if the member role

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Joe Conway
On 1/24/25 09:00, Andrew Dunstan wrote: On 2025-01-24 Fr 7:25 AM, Peter Eisentraut wrote: On 27.08.24 10:12, Peter Eisentraut wrote: Here is a new patch version. I simplified the uses of sed and awk inside the Perl script.  I also fixed "make installcheck".  I noticed that meson installs sep

Re: Eagerly scan all-visible pages to amortize aggressive vacuum

2025-01-24 Thread Melanie Plageman
On Thu, Jan 23, 2025 at 12:16 PM Robert Haas wrote: > > On Wed, Jan 22, 2025 at 5:48 PM Melanie Plageman > wrote: > > Circling back to benchmarking, I've been running the most adversarial > > benchmarks I could devise and can share a bit of what I've found. > > > > I created a "hot tail" benchmar

Re: why -Fdance archive format option works with ./pg_restore but not with ./pg_dump?

2025-01-24 Thread Andrew Dunstan
On 2025-01-24 Fr 3:04 AM, Srinath Reddy wrote: On Fri, Jan 24, 2025 at 1:42 AM Andrew Dunstan wrote: `git blame` tells me that this switch statement goes back to 2001. Seeking a reason at this distance is unlikely to be productive. Probably we were not nearly as careful th

Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)

2025-01-24 Thread Benoit Lobréau
On Fri, Jan 24, 2025 at 11:32 AM Benoit Lobréau wrote: > The completion for the INVISIBLE / VISIBLE keyword is missing in psql. I think this should to the trick ? diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 81cbf10aa28..43ea8e55fd0 100644 --- a/src/bin/psql

Re: Show WAL write and fsync stats in pg_stat_io

2025-01-24 Thread Bertrand Drouvot
Hi, On Fri, Jan 24, 2025 at 11:31:02AM +0300, Nazir Bilal Yavuz wrote: > v9 is rebased and attached as three patches. The first one is a > squashed patch for the current version of Andres' proposed fix to pass > the CI, the second one is for adding WAL stats to pg_stat_io and the > third one is fo

Re: Quadratic planning time for ordered paths over partitioned tables

2025-01-24 Thread Alexander Kuzmenkov
On Fri, Jan 24, 2025 at 2:37 PM Alvaro Herrera wrote: > I ran Kuzmenkov's test case with Watari-san's patch. Planning time goes > from 2700ms to 600ms or so. Thank you, good to know that it helps this case as well.

Re: doc: explain pgstatindex fragmentation

2025-01-24 Thread Frédéric Yhuel
On 1/24/25 14:58, Laurenz Albe wrote: On Fri, 2025-01-24 at 13:34 +, Bertrand Drouvot wrote: + Since indexes have a default fillfactor of 90, this should be around 0.9 for + newly built indexes I think 0.9 should be replaced by 90 (that's the actual kind of output we'd get). Damn! I m

Re: Orphaned users in PG16 and above can only be managed by Superusers

2025-01-24 Thread Andrew Dunstan
On 2025-01-23 Th 4:06 PM, Robert Haas wrote: On Thu, Jan 23, 2025 at 3:51 PM Andres Freund wrote: I wonder if it's a mistake that a role membership that has WITH ADMIN on another role is silently removed if the member role is removed. We e.g. do *not* do that for pg_auth_members.grantor: ERR

Re: doc: explain pgstatindex fragmentation

2025-01-24 Thread Bertrand Drouvot
Hi Frédéric, On Thu, Jan 23, 2025 at 10:00:27AM +0100, Frédéric Yhuel wrote: > On 1/22/25 12:34, Bertrand Drouvot wrote: > > I'm not sure it's good to describe something as the inverse of "something > > else". See my proposal below. > > > > Yeah... bloat is a more familiar concept, so I wanted t

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Pavel Stehule
pá 24. 1. 2025 v 9:12 odesílatel Jim Jones napsal: > Hi > > On 24.01.25 07:28, Pavel Stehule wrote: > > I think documentation should be strongly enhanced. This is probably > > the hardest part of this patch - explain well what this function does > > and what it doesn't. > > You mean something lik

Re: doc: explain pgstatindex fragmentation

2025-01-24 Thread Frédéric Yhuel
On 1/24/25 11:47, Bertrand Drouvot wrote: Hi Frédéric, On Thu, Jan 23, 2025 at 10:00:27AM +0100, Frédéric Yhuel wrote: On 1/22/25 12:34, Bertrand Drouvot wrote: I'm not sure it's good to describe something as the inverse of "something else". See my proposal below. Yeah... bloat is a more

Re: Improve verification of recovery_target_timeline GUC.

2025-01-24 Thread David Steele
On 1/24/25 01:44, Michael Paquier wrote: On Thu, Jan 23, 2025 at 02:53:39PM +, David Steele wrote: I discovered this while testing on Postgres versions < 12 where The tests are probably excessive but I needed something to show that the verification works as expected. Even with your patch,

Re: Quadratic planning time for ordered paths over partitioned tables

2025-01-24 Thread Alvaro Herrera
On 2025-Jan-24, Aleksander Alekseev wrote: > Did you consider checking if the referenced patchset addresses the > issue you described? I ran Kuzmenkov's test case with Watari-san's patch. Planning time goes from 2700ms to 600ms or so. -- Álvaro HerreraBreisgau, Deutschland — https://

BF member drongo doesn't like 035_standby_logical_decoding.pl

2025-01-24 Thread Tom Lane
I realized just now that drongo has been intermittently failing like this: 147/256 postgresql:recovery / recovery/035_standby_logical_decoding ERROR 2116.16s (exit status 255 or signal 127 SIGinvalid) - 8< --

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Tom Lane
Robins Tharakan writes: > On Sat, 25 Jan 2025 at 08:59, Tom Lane wrote: >> alligator and lapwing are not reporting the >> relevant log file, but what we do see is an install failure that >> could well be down to a compile failure. > You're probably right about that. > This is what I see in the i

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Andrew Dunstan
On 2025-01-24 Fr 7:09 PM, Robins Tharakan wrote: On Sat, 25 Jan 2025 at 10:11, Tom Lane wrote: Yeah, that's about what I expected.  As a workaround until Andrew updates the BF client, you could do -               $libdir = "$tmp_loc/lib/postgresql"; +               $libdir =

Re: Proposal: "query_work_mem" GUC, to distribute working memory to the query's individual operators

2025-01-24 Thread James Hunter
On Wed, Jan 22, 2025 at 1:13 PM Tomas Vondra wrote: > > On 1/10/25 19:00, James Hunter wrote: > > ... > > I wouldn’t change the existing planning logic (at least not in the > > initial implementaton). So the existing planning logic would choose > > between different SQL operators, still on the ass

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Andrew Dunstan
On 2025-01-24 Fr 7:50 PM, Andrew Dunstan wrote: Here's the hot fix (which passed my test with a directory with pgsql in its path): https://github.com/PGBuildFarm/client-code/commit/f6c6dd52d2959814452454890fb9838429c5c3e8 Oops, wrong address on previous email. Julien, please update

Re: Add CASEFOLD() function.

2025-01-24 Thread Tom Lane
Jeff Davis writes: > v6 attached. I plan to commit this soon. The documentation for this function is giving the PDF docs build indigestion: [WARN] FOUserAgent - Glyph "?" (0x3a3, Sigma) not available in font "Courier". [WARN] FOUserAgent - Glyph "?" (0x3c3, sigma) not available in font "Courier"

Re: Allow NOT VALID foreign key constraints on partitioned tables.

2025-01-24 Thread Alexander Lakhin
Hello Álvaro, 23.01.2025 17:04, Álvaro Herrera wrote: OK thanks, looks good, I have pushed it now with some trivial amendments. Please look at the script that produces an error starting from b663b9436: CREATE TABLE st (a int, primary key (a)); CREATE TABLE pt (a int,   FOREIGN KEY (a) REFERENC

Re: New process of getting changes into the commitfest app

2025-01-24 Thread Akshat Jaimini
This seems like a great idea ! Maybe we can start out by adding some basic CI tests on the mirror repository to sort of 'dry run' the new process? I'll be happy to submit a PR with some basic tests on the repository. Regards, Akshat Jaimini On Thu, Jan 23, 2025 at 6:48 PM Jelte Fennema-Nio wro

Re: XMLDocument (SQL/XML X030)

2025-01-24 Thread Pavel Stehule
pá 24. 1. 2025 v 23:11 odesílatel Jim Jones napsal: > > On 24.01.25 22:01, Chapman Flack wrote: > > It seems to me the key connection there is that the ISO SQL standard > > defines XMLDOCUMENT by equivalence to what `document { $expr }` means > > in the W3 XML Query standard. > > > It seems I mis

Re: Index AM API cleanup

2025-01-24 Thread Peter Eisentraut
I've been working on integrating Mark's "Index AM API cleanup" patch set with the existing gist strategy number mapping from Paul's application time patch set. Here is what I've come up with. The previously committed patch (v19.1) already changed the gist strategy number mapping to use the (R

Re: Separate GUC for replication origins

2025-01-24 Thread Masahiko Sawada
On Wed, Jan 8, 2025 at 7:39 AM Euler Taveira wrote: > > On Thu, Dec 19, 2024, at 10:31 AM, Peter Eisentraut wrote: > > On 10.12.24 19:41, Euler Taveira wrote: > > I'm attaching a patch that adds max_replication_origins. It basically > > replaces > > all of the points that refers to max_replication

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Andrew Dunstan
On 2025-01-24 Fr 6:08 PM, Tom Lane wrote: I wrote: Oh ... wait a second. After further code reading I see that the BF client sets NO_TEMP_INSTALL if check_install_is_complete succeeds. So evidently, that is successfully suppressing "make install" on most animals, but not these two. How come

Re: Inconsistent string comparison using modified ICU collations

2025-01-24 Thread Jeff Davis
On Thu, 2025-01-23 at 13:29 -0800, Jeff Davis wrote: > I had previously proposed[1] a GUC to control abbreviated keys, and > it > was rejected. Now that we've seen both a performance problem and a > bug > in the underlying dependency, perhaps we should reconsider. Users may: * trust libc strxfrm,

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Tom Lane
I wrote: > Yeah, but to get to that point you have to get past "make install", Oh ... wait a second. After further code reading I see that the BF client sets NO_TEMP_INSTALL if check_install_is_complete succeeds. So evidently, that is successfully suppressing "make install" on most animals, but

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Tom Lane
I wrote: > Oh ... wait a second. After further code reading I see that > the BF client sets NO_TEMP_INSTALL if check_install_is_complete > succeeds. So evidently, that is successfully suppressing > "make install" on most animals, but not these two. How come? Got it: we can see on alligator that

Re: pg_createsubscriber TAP test wrapping makes command options hard to read.

2025-01-24 Thread Michael Paquier
On Fri, Jan 24, 2025 at 06:59:42PM +, Dagfinn Ilmari Mannsåker wrote: > Tom Lane writes: >> You might be able to work around this with auth_extra, >> a la 1e3f461e8 and other past fixes. Yes, forgot about this part with SSPI this time. Thanks for the poke. > Here's a (blind, but at least do

Re: Convert sepgsql tests to TAP

2025-01-24 Thread Robins Tharakan
On Sat, 25 Jan 2025 at 10:11, Tom Lane wrote: > Yeah, that's about what I expected. As a workaround until Andrew > updates the BF client, you could do > > - $libdir = "$tmp_loc/lib/postgresql"; > + $libdir = "$tmp_loc/lib"; > > at about line 429 of PGBuild/Utils.pm >

Re: Adding skip scan (including MDAM style range skip scan) to nbtree

2025-01-24 Thread Peter Geoghegan
On Fri, Jan 24, 2025 at 10:07 PM Heikki Linnakangas wrote: > On my laptop, this is the worst case I could come up with: > > create table skiptest as select g / 10 as a, g%10 as b from > generate_series(1, 1000) g; > vacuum freeze skiptest; > create index on skiptest (a, b); > > set enable_seqs

  1   2   >