Re: Do we want a hashset type?

On Tue, Jun 20, 2023, at 02:04, Tomas Vondra wrote: > For UPDATE, it'd be pretty clear too, I think. It's possible to do > >UPDATE table SET col = SET[1,2,3] > > and it's clear the first is the command SET, while the second is a set > constructor. For SELECT there'd be conflict, and for ALTER

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Mon, Jun 19, 2023 at 02:55:34PM -0700, Nathan Bossart wrote: > In v4 of the patch set, I moved the skip_privs flag refactoring to 0001. I > intend to commit this tomorrow unless there is additional feedback. Fine by me. 0001 looks OK seen from here. > These object_ownercheck() calls were

Re: add non-option reordering to in-tree getopt_long

At Fri, 16 Jun 2023 11:28:47 -0700, Nathan Bossart wrote in > On Fri, Jun 16, 2023 at 04:51:38PM +0900, Kyotaro Horiguchi wrote: > > (Honestly, the rearrangement code looks somewhat tricky to grasp..) > > Yeah, I think there's still some room for improvement here. The argv elements get

Re: Deleting prepared statements from libpq.

On Mon, Jun 19, 2023 at 02:49:44PM +0200, Jelte Fennema wrote: > On Mon, 19 Jun 2023 at 14:17, jian he wrote: >> I am not sure the following two following function comments are right > > They were incorrect indeed. Attached is a patch with those two updated. The amount of duplication

Re: Making empty Bitmapsets always be NULL

On Thu, 15 Jun 2023 at 20:57, Yuya Watari wrote: > > On Tue, Jun 13, 2023 at 8:07 PM David Rowley wrote: > > For the fix in the 0004 patch, I think we can do what you did more > > simply. I don't think there's any need to perform the loop to find > > the last non-zero word. We're only deleting

Re: Fix regression tests to work with REGRESS_OPTS=--no-locale

On Thu, Jun 15, 2023 at 03:52:13PM +0900, Michael Paquier wrote: > It took some time to notice that, which makes me wonder how relevant > this stuff is these days.. Anyway, I would like to do like the others > and fix it, so I am proposing the attached. Please find attached a v2 that removes the

Re: [DOC] Update ALTER SUBSCRIPTION documentation v3

FYI - I have created and tested back-patches for Amit's v5 patch, going all the way to REL_10_STABLE. (the patches needed tweaking several times due to minor code/docs differences in the earlier versions) PSA. -- Kind Regards, Peter Smith. Fujitsu Australia

Re: remap the .text segment into huge pages at run time

On Wed, Jun 14, 2023 at 12:40 PM John Naylor wrote: > > On Sat, Nov 5, 2022 at 3:27 PM Andres Freund wrote: > > A real version would have to open /proc/self/maps and do this for at least > > postgres' r-xp mapping. We could do it for libraries too, if they're suitably > > aligned (both in

Re: run pgindent on a regular basis / scripted manner

On Sat, Jun 17, 2023 at 10:08:32AM -0400, Andrew Dunstan wrote: > See > > I have set up a new buildfarm animal called koel which will run the module. That's really cool! Thanks for taking the time to do

Re: [BUG] recovery of prepared transactions during promotion can fail

On Mon, Jun 19, 2023 at 04:27:27PM +0900, Michael Paquier wrote: > Okay, cool. Thanks for double-checking, so let's do something down to > 13, then.. And done for v13 and v14. I have split the test and comment changes into their own commit, doing that for v13~HEAD. -- Michael signature.asc

Re: Document that server will start even if it's unable to open some TCP/IP ports

On Tue, Jun 13, 2023 at 11:11:04PM -0400, Tom Lane wrote: > Kyotaro Horiguchi writes: > > If I had to say, I would feel it rather surprising if server > > successfully starts even when any explicitly-specified port can't be > > opened (which is the current case). > > There is certainly an

Re: Partial aggregates pushdown

On Tue, Jun 13, 2023 at 02:18:15AM +, fujii.y...@df.mitsubishielectric.co.jp wrote: > Hi Mr.Momjian. > > Thank you for advises. > > > From: Bruce Momjian > > Sent: Monday, June 12, 2023 10:38 PM > > > I understand what the problem is. I will put a mechanism maintaining > > > compatibility

Re: Do we want a hashset type?

On 6/20/23 00:50, Joel Jacobson wrote: > On Mon, Jun 19, 2023, at 14:59, Tomas Vondra wrote: >> What unexpected issues you mean? Sure, if someone uses multisets as if >> they were sets (so ignoring the handling of duplicates), things will go >> booom! quickly. > > The unexpected issues I had

Re: Make pgbench exit on SIGINT more reliably

On Mon Jun 19, 2023 at 6:39 AM PDT, Yugo NAGATA wrote: > On Wed, 24 May 2023 08:58:46 -0500 > "Tristan Partin" wrote: > > > On Tue May 23, 2023 at 7:31 PM CDT, Michael Paquier wrote: > > > On Mon, May 22, 2023 at 10:02:02AM -0500, Tristan Partin wrote: > > > > The way that pgbench handled SIGINT

Re: BUG #17946: LC_MONETARY & DO LANGUAGE plperl - BUG

On 18/06/2023 21:27, Joe Conway wrote: I have proposed a targeted fix that I believe is safe to backpatch -- attached. IIUC, Tom was +1, but Heikki was looking for a more general solution. My issue with the more general solution is that it will likely be too invasive to backpatch, and at the

Optimizing "boundary cases" during backward scan B-Tree index descents

An important goal of the work on nbtree that went into PostgreSQL 12 (and to a lesser extent the work that went into 13) was to make sure that index scans deal with "boundary cases" optimally. The simplest way of explaining what this means is through a practical worked example. Recap, worked

Re: Do we want a hashset type?

On Mon, Jun 19, 2023, at 14:59, Tomas Vondra wrote: > What unexpected issues you mean? Sure, if someone uses multisets as if > they were sets (so ignoring the handling of duplicates), things will go > booom! quickly. The unexpected issues I had in mind are subtle bugs due to treating multisets as

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Thu, Jun 15, 2023 at 10:20:25PM -0700, Nathan Bossart wrote: > I noticed that it was possible to make the documentation changes in 0001 > easier to read. Apologies for the noise. Yet more noise... In v4 of the patch set, I moved the skip_privs flag refactoring to 0001. I intend to commit

Re: run pgindent on a regular basis / scripted manner

Andrew Dunstan writes: > I have set up a new buildfarm animal called koel which will run the module. Is koel tracking the right repo? It just spit up with a bunch of diffs that seem to have little to do with the commit it's claiming caused them:

Re: ERROR: wrong varnullingrels (b 3) (expected (b)) for Var 2/1

I wrote: > Richard Guo writes: >> So it seems that we need to do nullingrel adjustments in a more common >> place. > I agree: this suggests that we fixed it in the wrong place. So pursuant to that, 0001 attached reverts the code changes from bfd332b3f and 63e4f13d2 (keeping the test cases and

Re: index prefetching

Hi, I have results from the new extended round of prefetch tests. I've pushed everything to https://github.com/tvondra/index-prefetch-tests-2 There are scripts I used to run this (run-*.sh), raw results and various kinds of processed summaries (pdf, ods, ...) that I'll mention later. As

Re: pg_collation.collversion for C.UTF-8

On Sat, 2023-06-17 at 17:54 +1200, Thomas Munro wrote: > > > Would it be correct to interpret LC_COLLATE=C.UTF-8 as > > LC_COLLATE=C, > > but leave LC_CTYPE=C.UTF-8 as-is? > > Yes.  The basic idea, at least for these two OSes, is that every > category behaves as if set to C, except LC_CTYPE. If

Re: could not extend file "base/5/3501" with FileFallocate(): Interrupted system call

Hi, On June 19, 2023 10:37:45 AM PDT, Tom Lane wrote: >Andres Freund writes: >> Unfortunately, due to some personal life business, it took until for me to >> feel comfortable pushing the fix for >> https://www.postgresql.org/message-id/zezdj1h61ryrm...@msg.df7cb.de >> (FileFallocate() erroring

Re: could not extend file "base/5/3501" with FileFallocate(): Interrupted system call

Andres Freund writes: > Unfortunately, due to some personal life business, it took until for me to > feel comfortable pushing the fix for > https://www.postgresql.org/message-id/zezdj1h61ryrm...@msg.df7cb.de > (FileFallocate() erroring out with EINTR due to running on tmpfs). > Do you want me to

Re: could not extend file "base/5/3501" with FileFallocate(): Interrupted system call

Hi Tom, Unfortunately, due to some personal life business, it took until for me to feel comfortable pushing the fix for https://www.postgresql.org/message-id/zezdj1h61ryrm...@msg.df7cb.de (FileFallocate() erroring out with EINTR due to running on tmpfs). Do you want me to hold off before beta2

Re: Synchronizing slots from primary to standby

Hi, On 6/19/23 12:03 PM, Amit Kapila wrote: On Mon, Jun 19, 2023 at 11:34 AM Drouvot, Bertrand wrote: Also I think we need to handle the case of invalidated replication slot(s): should we drop/recreate it/them? (as the main goal is to have sync slot(s) on the standby). Do you intend to

Re: Implement missing join selectivity estimation for range types

This is a quick correction as the last patch contained a missing semicolon. Regards, Maxime SchoemansFrom ebd62356210eff2f38772a9c46a0a8792c0e9ce3 Mon Sep 17 00:00:00 2001 From: Maxime Schoemans Date: Mon, 20 Mar 2023 11:48:05 -0400 Subject: [PATCH v2] Apply division before adding remainder ---

Re: trying again to get incremental backup

Hi, On 2023-06-19 09:46:12 -0400, Robert Haas wrote: > On Wed, Jun 14, 2023 at 4:40 PM Andres Freund wrote: > > > But I'm not sure that's a great approach, because that LSN gap might be > > > large and then we're duplicating a lot of work that the summarizer has > > > probably already done most

Re: trying again to get incremental backup

On Wed, Jun 14, 2023 at 4:40 PM Andres Freund wrote: > > But I'm not sure that's a great approach, because that LSN gap might be > > large and then we're duplicating a lot of work that the summarizer has > > probably already done most of. > > I guess that really depends on what the summary

Re: Make pgbench exit on SIGINT more reliably

On Wed, 24 May 2023 08:58:46 -0500 "Tristan Partin" wrote: > On Tue May 23, 2023 at 7:31 PM CDT, Michael Paquier wrote: > > On Mon, May 22, 2023 at 10:02:02AM -0500, Tristan Partin wrote: > > > The way that pgbench handled SIGINT changed in > > > 1d468b9ad81b9139b4a0b16b416c3597925af4b0.

Re: Do we want a hashset type?

Andrew Dunstan writes: > Yes, Multisets (a.k.a. bags and a large number of other names) would be > interesting. But I wouldn't like to abandon pure sets either. Maybe a > typmod indicating the allowed multiplicity of the type? I don't think trying to use typmod to carry fundamental semantic

Re: psql: Add role's membership options to the \du+ command

On 6/15/23 2:47 PM, David G. Johnston wrote: Robert - can you please comment on what you are willing to commit in order to close out your open item here.  My take is that the design for this, the tabular form a couple of emails ago (copied here), is ready-to-commit, just needing the actual

Re: Do we want a hashset type?

On 6/19/23 13:33, Joel Jacobson wrote: > On Mon, Jun 19, 2023, at 11:21, Tomas Vondra wrote: >> AFAICS the standard only defines arrays and multisets. Arrays are pretty >> much the thing we have, including the ARRAY[] constructor etc. Multisets >> are similar to hashset discussed here, except

Re: Deleting prepared statements from libpq.

On Mon, 19 Jun 2023 at 14:17, jian he wrote: > I am not sure the following two following function comments are right They were incorrect indeed. Attached is a patch with those two updated. On Mon, 19 Jun 2023 at 14:17, jian he wrote: > > On Mon, Jun 19, 2023 at 5:50 PM Jelte Fennema

Re: Deleting prepared statements from libpq.

On Mon, Jun 19, 2023 at 5:50 PM Jelte Fennema wrote: > > On Mon, 19 Jun 2023 at 11:44, Jelte Fennema wrote: > > Done > > Now with the actual attachment. > > PS. Another connection pooler (PgCat) now also supports prepared > statements, but only using Close not DEALLOCATE: >

Re: Do we want a hashset type?

On 6/19/23 13:50, Andrew Dunstan wrote: > > On 2023-06-19 Mo 05:21, Tomas Vondra wrote: >> On 6/18/23 18:45, Andrew Dunstan wrote: >>> On 2023-06-16 Fr 20:38, Joel Jacobson wrote: New patch is attached, which will henceforth always be a complete patch, to avoid the hassle of having to

Re: Do we want a hashset type?

On Mon, Jun 19, 2023, at 11:49, jian he wrote: > hashset_to_array function should be strict? > > I noticed hashset_symmetric_difference and hashset_difference handle > null in a different way, seems they should handle null in a consistent > way? Yes, I agree, they should be consistent. I've

Re: Do we want a hashset type?

On 2023-06-19 Mo 05:21, Tomas Vondra wrote: On 6/18/23 18:45, Andrew Dunstan wrote: On 2023-06-16 Fr 20:38, Joel Jacobson wrote: New patch is attached, which will henceforth always be a complete patch, to avoid the hassle of having to assemble incremental patches. Cool, thanks. It might

Re: Assert while autovacuum was executing

On Sun, Jun 18, 2023 at 12:18 AM Peter Geoghegan wrote: > > On Sat, Jun 17, 2023 at 11:29 AM Jaime Casanova > wrote: > > I have been testing 16beta1, last commit > > a14e75eb0b6a73821e0d66c0d407372ec8376105 > > I just let sqlsmith do its magic before trying something else, and > > today I found

Re: Do we want a hashset type?

On Mon, Jun 19, 2023, at 11:21, Tomas Vondra wrote: > AFAICS the standard only defines arrays and multisets. Arrays are pretty > much the thing we have, including the ARRAY[] constructor etc. Multisets > are similar to hashset discussed here, except that it tracks the number > of elements for each

Re: [EXTERNAL] Re: Add non-blocking version of PQcancel

I noticed that cfbot was unable to run tests due to some rebase conflict. It seems the pgindent changes from patch 1 have now been made. So adding the rebased patches without patch 1 now to unblock cfbot. v21-0002-Return-2-from-pqReadData-on-EOF.patch Description: Binary data

Missed query planner optimization

Hi all, this is about a limitation of the current query planner implementation which causes big performance declines for certain types of queries. Affected queries will typically execute about 1000 times slower than they could. Examples are given below. After talking about this with David

Re: Synchronizing slots from primary to standby

On Mon, Jun 19, 2023 at 11:34 AM Drouvot, Bertrand wrote: > > Also I think we need to handle the case of invalidated replication slot(s): > should > we drop/recreate it/them? (as the main goal is to have sync slot(s) on the > standby). > Do you intend to ask what happens to logical slots

Re: Deleting prepared statements from libpq.

On Mon, 19 Jun 2023 at 11:44, Jelte Fennema wrote: > Done Now with the actual attachment. PS. Another connection pooler (PgCat) now also supports prepared statements, but only using Close not DEALLOCATE: https://postgresml.org/blog/making-postgres-30-percent-faster-in-production From

Re: Do we want a hashset type?

On Mon, Jun 19, 2023 at 2:51 PM Joel Jacobson wrote: > > On Mon, Jun 19, 2023, at 02:00, jian he wrote: > > select hashset_contains('{1,2}'::int4hashset,NULL::int); > > should return null? > > Hmm, that's a good philosophical question. > > I notice Tomas Vondra in the initial commit opted for

Re: Implement missing join selectivity estimation for range types

Hi, In the selectivity algorithm, the division was applied after adding the remaining histogram buckets of histogram2 that don't overlap with histogram1. This could lead to reducing selectivity by half, e.g., in the case that histogram2 is completely right of histogram1. The correct calculation

Re: Bypassing shared_buffers

Hi! > On 15 Jun 2023, at 03:57, Vladimir Churyukin wrote: > > Hello, > > There is often a need to test particular queries executed in the worst-case > scenario, i.e. right after a server restart or with no or minimal amount of > data in shared buffers. In Postgres it's currently hard to

Re: Deleting prepared statements from libpq.

On Mon, 19 Jun 2023 at 04:52, jian he wrote: > > /* Now that it's closed we should get an error when describing */ > > res = PQdescribePortal(conn, "cursor_one"); > > if (PQresultStatus(res) != PGRES_FATAL_ERROR) > > pg_fatal("expected COMMAND_OK, got %s", PQresStatus(PQresultStatus(res))); >

Re: Deleting prepared statements from libpq.

On Mon, 19 Jun 2023 at 01:57, Michael Paquier wrote: > +static int > +PQsendClose(PGconn *conn, char close_type, const char *close_target) > > Could it be better for this code path to issue an error if using a > non-supported close_type rather than sending it? Okay, you are > consistent with

Re: Support logical replication of DDLs

On Fri, Jun 16, 2023 at 4:01 PM shveta malik wrote: > > With these changes, I hope the patch-set is somewhat easier to review. > Few comments: = 1. +static Jsonb * +deparse_CreateStmt(Oid objectId, Node *parsetree) { ... + /* PERSISTENCE */ + appendStringInfoString(, "CREATE

Re: Do we want a hashset type?

On 6/18/23 18:45, Andrew Dunstan wrote: > > On 2023-06-16 Fr 20:38, Joel Jacobson wrote: >> >> New patch is attached, which will henceforth always be a complete patch, >> to avoid the hassle of having to assemble incremental patches. > > > Cool, thanks. > It might still be convenient to

Re: [PATCH] ltree hash functions

> > FWIW the CREATE OPERATOR documentation only talks about hash joins for HASHES, maybe it should be updated to also mention hash aggregates? I think I might have been a bit unclear here, the hash aggregate does work without altering the operator so it's just the join that's blocked. Sorry

Re: deb’s pg_upgradecluster(1) vs streaming replication

Hi, On Sat, Jun 17, 2023 at 07:10:23PM -0400, James Cloos wrote: > Has anyone recently tried updating a streaming replication cluster using > debian’s pg_upgradecluster(1) on each node? Note that the word "cluster" in upgradecluster refers to a single Postgres instance, a.k.a a cluster of

Re: SQL/JSON revisited

Hi, On Thu, May 4, 2023 at 3:58 AM Matthias Kurz wrote: > On Wed, 3 May 2023 at 20:17, Alvaro Herrera wrote: >> I would suggest to start a new thread with updated patches, and then a new >> commitfest entry can be created with those. > > Whoever starts that new thread, please link link it

Re: Initial Schema Sync for Logical Replication

Hi, Below are my review comments for the PoC patch 0001. In addition, the patch needed rebasing, and, after I rebased it locally in my private environment there were still test failures: a) The 'make check' tests fail but only in a minor way due to changes colname b) the subscription TAP test

Re: Inconsistent results with libc sorting on Windows

On Thu, Jun 15, 2023 at 1:57 AM Thomas Munro wrote: > > Given that the documented behaviour is that ".. the sort key produces > the same order as when the source string is used in CompareString or > CompareStringEx"[1], this seems like a reportable bug, unless perhaps > your test program is

Re: [BUG] recovery of prepared transactions during promotion can fail

On Mon, Jun 19, 2023 at 02:41:54PM +0900, Kyotaro Horiguchi wrote: > At Mon, 19 Jun 2023 14:24:44 +0900, Michael Paquier > wrote in >> On Fri, Jun 16, 2023 at 04:27:40PM +0200, Julian Markwort wrote: >>> I've attached patches for PG 14 and PG 13 that mimic the change in >>> PG15 (commit 811051c

Re: Use generation context to speed up tuplesorts

Le dimanche 18 juin 2023, 20:22:17 CEST Tomas Vondra a écrit : > Hi Ronan, > > We briefly chatted about the glibc-tuning part of this thread at pgcon, > so I wonder if you're still planning to pursue that. If you do, I > suggest we start a fresh thread, so that it's not mixed with the already >

Re: Do we want a hashset type?

On Mon, Jun 19, 2023, at 02:00, jian he wrote: > select hashset_contains('{1,2}'::int4hashset,NULL::int); > should return null? Hmm, that's a good philosophical question. I notice Tomas Vondra in the initial commit opted for allowing NULL inputs, treating them as empty sets, e.g. in

Re: Synchronizing slots from primary to standby

Hi, On 6/16/23 11:56 AM, Amit Kapila wrote: On Mon, Apr 17, 2023 at 7:37 PM Drouvot, Bertrand wrote: Please find attached V5 (a rebase of V4 posted up-thread). In addition to the "rebasing" work, the TAP test adds a test about conflict handling (logical slot invalidation) relying on the