Index range search optimization

Hi hackers. _bt_readpage performs key check for each item on the page trying to locate upper boundary. While comparison of simple integer keys are very fast, comparison of long strings can be quite expensive. We can first make check for the largest key on the page and if it is not larger than

Re: Deleting prepared statements from libpq.

On Fri, 23 Jun 2023 at 05:59, Michael Paquier wrote: > [...] > res = PQgetResult(conn); > if (res == NULL) > - pg_fatal("expected NULL result"); > + pg_fatal("expected non-NULL result"); > > This should check for the NULL-ness of the result returned for > PQclosePrepared() rath

patch: improve "user mapping not found" error message

Hi Mild corner-case annoyance while doing Random Experimental Things: postgres=# SELECT * FROM parttest; ERROR: user mapping not found for "postgres" Oky, but which server? postgres=# \det List of foreign tables Schema | Table | Server +---

Re: Deleting prepared statements from libpq.

On Fri, Jun 23, 2023 at 09:39:00AM +0200, Jelte Fennema wrote: > To be clear, it didn't actually change the behaviour. I only changed > the error message, since it said the exact opposite of what it was > expecting. I split this minor fix into its own commit now to clarify > that. I think it would

Re: Do we want a hashset type?

On Fri, Jun 23, 2023, at 08:40, jian he wrote: > I played around array_func.c > many of the code can be used for multiset data type. > now I imagine multiset as something like one dimension array. (nested > is somehow beyond the imagination...). Are you suggesting it might be a better idea to sta

Re: Assert while autovacuum was executing

On Thu, Jun 22, 2023 at 9:16 AM Amit Kapila wrote: > > On Wed, Jun 21, 2023 at 10:57 AM Andres Freund wrote: > > > > As far as I can tell 72e78d831a as-is is just bogus. Unfortunately that > > likely > > also means 3ba59ccc89 is not right. > > > > Indeed. I was thinking of a fix but couldn't fin

Re: Allow pg_archivecleanup to remove backup history files

On 2023-06-22 16:47, Kyotaro Horiguchi wrote: Thanks for your review! 0002: +* Check file name. +* +* We skip files which are not WAL file or partial WAL file. There's no need to spend this many lines describing this, and it's suggested to avoid

Re: Do we want a hashset type?

On Fri, Jun 23, 2023 at 4:23 PM Joel Jacobson wrote: > On Fri, Jun 23, 2023, at 08:40, jian he wrote: > > I played around array_func.c > > many of the code can be used for multiset data type. > > now I imagine multiset as something like one dimension array. (nested > > is somehow beyond the imagi

Re: Support to define custom wait events for extensions

Hi, I updated the patches to handle the warning mentioned by PostgreSQL Patch Tester, and removed unnecessary spaces. Regards, -- Masahiro Ikeda NTT DATA CORPORATIONFrom 1bb78fa2cbe6b030cea7a570bec88bd4d68f314a Mon Sep 17 00:00:00 2001 From: Masahiro Ikeda Date: Fri, 23 Jun 2023 17:38:38 +0900

Re: Improving btree performance through specializing by key shape, take 2

On Fri, Jun 23, 2023 at 2:21 AM Matthias van de Meent wrote: > > == Dynamic prefix truncation (0001) > The code now tracks how many prefix attributes of the scan key are > already considered equal based on earlier binsrch results, and ignores > those prefix colums in further binsrch operations (s

Migration database from mysql to postgress

Hi, I need to move some databases from a MySQL server to Postgresql. Can someone tell me the migration procedure, tools, and recommendations? Thanks

Re: Migration database from mysql to postgress

Alfredo Alcala schrieb am 23.06.2023 um 11:30: > I need to move some databases from a MySQL server to Postgresql. > > Can someone tell me the migration procedure, tools, and recommendations?  Despite its name, "ora2pg" can also migrate MySQL to Postgres https://ora2pg.darold.net/

Re: patch: improve "user mapping not found" error message

On Fri, 2023-06-23 at 16:45 +0900, Ian Lawrence Barwick wrote: > Mild corner-case annoyance while doing Random Experimental Things: > >     postgres=# SELECT * FROM parttest; >     ERROR:  user mapping not found for "postgres" > > Oky, but which server? > >     postgres=# \det >    L

Re: Assert while autovacuum was executing

On Fri, Jun 23, 2023 at 2:04 PM Amit Kapila wrote: > > On Thu, Jun 22, 2023 at 9:16 AM Amit Kapila wrote: > > > > On Wed, Jun 21, 2023 at 10:57 AM Andres Freund wrote: > > > > > > As far as I can tell 72e78d831a as-is is just bogus. Unfortunately that > > > likely > > > also means 3ba59ccc89 is

Re: [PATCH] Extend ALTER OPERATOR to support adding commutator, negator, hashes, and merges

Tom Lane writes: > Please add this to the upcoming commitfest [1], to ensure we don't > lose track of it. I've added a single patch here: https://commitfest.postgresql.org/43/4389/ It wasn't obvious whether I should create a second commitfest entry because I've included 2 patches so I've just d

Re: [PATCH] Extend ALTER OPERATOR to support adding commutator, negator, hashes, and merges

Tommy Pavlicek writes: > I've added a single patch here: https://commitfest.postgresql.org/43/4389/ > It wasn't obvious whether I should create a second commitfest entry > because I've included 2 patches so I've just done 1 to begin with. On > that note, is it preferred here to split patches of t

Re: Migration database from mysql to postgress

On 2023-06-23 Fr 05:30, Alfredo Alcala wrote: Hi, I need to move some databases from a MySQL server to Postgresql. Can someone tell me the migration procedure, tools, and recommendations? Please ask questions on the correct forum. For this question I suggest the pgsql-general mailing lis

Re: eqjoinsel_semi still sucks ...

On 2/5/2012 20:34, Tom Lane wrote: On reflection I think that the idea of clamping ndistinct beforehand is just wrong, and what we ought to do instead is apply a multiplier to the selectivity estimate afterwards. In the case of a base rel we could just multiply by the selectivity of its baserest

Re: Do we want a hashset type?

On 2023-06-23 Fr 04:23, Joel Jacobson wrote: On Fri, Jun 23, 2023, at 08:40, jian he wrote: I played around array_func.c many of the code can be used for multiset data type. now I imagine multiset as something like one dimension array. (nested is somehow beyond the imagination...). Are you sug

Re: Bytea PL/Perl transform

On 2023-06-22 Th 16:56, Greg Sabino Mullane wrote: * Do all of these transforms need to be their own contrib modules? So much duplicated code across contrib/*_plperl already (and *plpython too for that matter) ... Yeah, that's a bit of a mess. Not sure what we can do about it now. chee

Re: Do we want a hashset type?

On 6/23/23 13:47, Andrew Dunstan wrote: > > On 2023-06-23 Fr 04:23, Joel Jacobson wrote: >> On Fri, Jun 23, 2023, at 08:40, jian he wrote: >>> I played around array_func.c >>> many of the code can be used for multiset data type. >>> now I imagine multiset as something like one dimension array.

Re: logical decoding and replication of sequences, take 2

On Tue, Jun 13, 2023 at 11:01 PM Tomas Vondra wrote: > > On 5/18/23 16:23, Ashutosh Bapat wrote: > > Hi, > > Sorry for jumping late in this thread. > > > > I started experimenting with the functionality. Maybe something that > > was already discussed earlier. Given that the thread is being > > dis

Re: logical decoding and replication of sequences, take 2

Regarding the patchsets, I think we will need to rearrange the commits. Right now 0004 has some parts that should have been in 0001. Also the logic to assign XID to a subtrasaction be better a separate commit. That piece is independent of logical decoding of sequences. On Fri, Jun 23, 2023 at 6:48

Re: [PATCH] Reuse Workers and Replication Slots during Logical Replication

Hi, Thanks for your reviews. Hayato Kuroda (Fujitsu) , 13 Haz 2023 Sal, 13:06 tarihinde şunu yazdı: > 01. general > > Why do tablesync workers have to disconnect from publisher for every > iterations? > I think connection initiation overhead cannot be negligible in the postgres's > basic > arch

Re: [PATCH] Reuse Workers and Replication Slots during Logical Replication

Hi Peter, Thanks for your reviews. I tried to apply most of them. I just have some comments below for some of them. Peter Smith , 14 Haz 2023 Çar, 08:45 tarihinde şunu yazdı: > > 9. process_syncing_tables_for_sync > > @@ -378,7 +387,13 @@ process_syncing_tables_for_sync(XLogRecPtr current_lsn) >

[PGdocs] fix description for handling pf non-ASCII characters

Dear hackers, While discussing based on the article[1] with Japanese developers, I found inconsistencies between codes and documents. 45b1a67a[2] changed the behavior when non-ASCII characters was set as application_name, cluster_name and postgres_fdw.application_name, but it seemed not to be

Stampede of the JIT compilers

Hello, We recently brought online a new database cluster, and in the course of ramping up traffic to it encountered a situation where a misplanned query (analyzing helped with this, but I think the issue is still relevant) resulted in that query being compiled with JIT, and soon a large number of

Re: Improving btree performance through specializing by key shape, take 2

On Fri, 23 Jun 2023 at 11:26, Dilip Kumar wrote: > > On Fri, Jun 23, 2023 at 2:21 AM Matthias van de Meent > wrote: > > > > > == Dynamic prefix truncation (0001) > > The code now tracks how many prefix attributes of the scan key are > > already considered equal based on earlier binsrch results, a

Re: Bytea PL/Perl transform

Andrew Dunstan writes: > On 2023-06-22 Th 16:56, Greg Sabino Mullane wrote: >> >> * Do all of these transforms need to be their own contrib modules? So >> much duplicated code across contrib/*_plperl already (and *plpython >> too for that matter) ... >> >> > > Yeah, that's a bit of a mess. Not s

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

On Thu, Jun 22, 2023 at 5:08 PM Tom Lane wrote: > "Jonathan S. Katz" writes: > > 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 tabu

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

"David G. Johnston" writes: > On Thu, Jun 22, 2023 at 5:08 PM Tom Lane wrote: >> * I agree that the "tabular" format looks nicer and has fewer i18n >> issues than the other proposals. > As you are on board with a separate command please clarify whether you mean > the tabular format but still wit

Re: Assert while autovacuum was executing

Hi, On 2023-06-23 14:04:15 +0530, Amit Kapila wrote: > OTOH, if the above theory is wrong or people are not convinced, I am > okay with removing all the changes in commits 72e78d831a and > 3ba59ccc89. I am not convinced. And even if I were, coming up with new justifications in a released version,

Re: Preventing non-superusers from altering session authorization

On Thu, Jun 22, 2023 at 06:39:45PM -0400, Joseph Koshakow wrote: > On Wed, Jun 21, 2023 at 11:48 PM Nathan Bossart > wrote: >> I see that RESET SESSION AUTHORIZATION >> with a concurrently dropped role will FATAL with your patch but succeed >> without it, which could be part of the reason. > > Th

Problems with estimating OR conditions, IS NULL on LEFT JOINs

Hi, I ran into a pretty terrible case of LEFT JOIN estimate, resulting in pretty arbitrary underestimate. The query is pretty trivial, nothing overly complex, and the more I think about it the more I think this is a fairly fundamental flaw in how we estimate this type of joins. Imagine you have t

Re: Making empty Bitmapsets always be NULL

Em qui., 22 de jun. de 2023 às 05:50, Yuya Watari escreveu: > Hello, > > On Thu, Jun 22, 2023 at 1:43 PM David Rowley wrote: > > > 3. Avoid enlargement when nwords is equal wordnum. > > > Can save cycles when in corner cases? > > > > No, you're just introducing a bug here. Arrays in C are z

Re: sslinfo extension - add notbefore and notafter timestamps

> Off the cuff that doesn't seem like a bad idea, but I wonder if we should add > them to pg_stat_ssl (or both) instead if we deem them valuable? I think the same information should be available to pg_stat_ssl as well. pg_stat_ssl can show the client certificate information for all connecting

Re: sslinfo extension - add notbefore and notafter timestamps

> On 23 Jun 2023, at 22:10, Cary Huang wrote: > would this feature be suitable to be added to commitfest? What do you think? Yes, please add it to the July commitfest and feel free to set me as Reviewer, I intend to take a look at it. -- Daniel Gustafsson

Re: Remove deprecation warnings when compiling PG ~13 with OpenSSL 3.0~

On 23.06.23 00:22, Michael Paquier wrote: Also, note that the documentation claims that the minimum version of OpenSSL supported is 0.9.8, which is something that commit 9b7cd59 has done, impacting Postgres 10~. So your argument looks incorrect to me? Considering that, yes.

Re: Add GUC to tune glibc's malloc implementation.

On 22.06.23 15:35, Ronan Dunklau wrote: The thing is, by default, those parameters are adjusted dynamically by the glibc itself. It starts with quite small thresholds, and raises them when the program frees some memory, up to a certain limit. This patch proposes a new GUC allowing the user to adj

Re: [PATCH] pg_regress.c: Fix "make check" on Mac OS X: Pass DYLD_LIBRARY_PATH

On 22.06.23 21:08, David Zhang wrote: Currently, there is a description suggesting a workaround by running a 'make install' command first, but I find it to be somewhat inaccurate. It would be better to update the existing description to provide more precise instructions on how to overcome this

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

On 6/23/23 11:52 AM, David G. Johnston wrote: On Thu, Jun 22, 2023 at 5:08 PM Tom Lane > wrote: "Jonathan S. Katz" mailto:jk...@postgresql.org>> writes: > On 6/15/23 2:47 PM, David G. Johnston wrote: >> Robert - can you please comment on what you are will

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

On 6/23/23 12:16 PM, Tom Lane wrote: "David G. Johnston" writes: On Thu, Jun 22, 2023 at 5:08 PM Tom Lane wrote: * I agree that the "tabular" format looks nicer and has fewer i18n issues than the other proposals. As you are on board with a separate command please clarify whether you mean t

Re: sslinfo extension - add notbefore and notafter timestamps

> Yes, please add it to the July commitfest and feel free to set me as > Reviewer, > I intend to take a look at it. Thank you Daniel, I have added this patch to July commitfest under security category and added you as reviewer. best regards Cary Huang - HighGo Software Inc. (Ca

Re: Problems with estimating OR conditions, IS NULL on LEFT JOINs

Tomas Vondra writes: > The problem is that the selectivity for "IS NULL" is estimated using the > table-level statistics. But the LEFT JOIN entirely breaks the idea that > the null_frac has anything to do with NULLs in the join result. Right. > I wonder how to improve this, say by adjusting the

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

"David G. Johnston" writes: > On Thu, Jun 22, 2023 at 5:08 PM Tom Lane wrote: >> * Personally I could do without the "empty" business, but that seems >> unnecessary in the tabular format; an empty column will serve fine. > I disagree, but not strongly. > I kinda expected you to be on the side o

Re: [PATCH] pg_regress.c: Fix "make check" on Mac OS X: Pass DYLD_LIBRARY_PATH

Hi, On 2023-06-05 22:33:16 -0400, Tom Lane wrote: > Julien Rouhaud writes: > > On Mon, Jun 05, 2023 at 09:47:30AM -0400, Evan Jones wrote: > >> This makes "make check" work on Mac OS X. Without this patch, on Mac OS X a > >> default "./configure; make; make check" fails with errors like: > >> ...

Re: Stampede of the JIT compilers

On Sat, 24 Jun 2023 at 02:28, James Coleman wrote: > There are a couple of issues here. I'm sure it's been discussed > before, and it's not the point of my thread, but I can't help but note > that the default value of jit_above_cost of 10 seems absurdly low. > On good hardware like we have eve

Re: Making empty Bitmapsets always be NULL

On Sat, 24 Jun 2023 at 07:43, Ranier Vilela wrote: > I worked a bit more on the v4 version and made a new v6 version, with some > changes. > I can see some improvement, would you mind testing v6 and reporting back? Please don't bother. I've already mentioned that I'm not going to consider any c

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

On Fri, Jun 23, 2023 at 5:12 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Thu, Jun 22, 2023 at 5:08 PM Tom Lane wrote: > >> * Personally I could do without the "empty" business, but that seems > >> unnecessary in the tabular format; an empty column will serve fine. > > > I disagree,

Re: vac_truncate_clog()'s bogus check leads to bogusness

Hi, On 2023-06-21 21:50:39 -0700, Noah Misch wrote: > On Wed, Jun 21, 2023 at 05:46:37PM -0700, Andres Freund wrote: > > A related issue is that as far as I can tell the determination of what is > > bogus is bogus. > > > > The relevant cutoffs are determined vac_update_datfrozenxid() using: > >

Re: vac_truncate_clog()'s bogus check leads to bogusness

Hi, On 2023-06-23 18:41:58 -0700, Andres Freund wrote: > I guess this might be caused by 78db307bb23 adding the check, but using > GetOldestXmin(NULL, true) to determine lastSaneFrozenXid. That was changed > soon after, in 87f830e0ce03. FWIW, the discussion leading up to 87f830e0ce03 is https://p

Re: Making empty Bitmapsets always be NULL

On Thu, 22 Jun 2023 at 20:59, Yuya Watari wrote: > Table 1: Planning time and its speedup of Join Order Benchmark > (n: the number of partitions of each table) > (Speedup: higher is better) > 64 | 115.7% > 128 | 142.9% > 256 | 187.7% Thanks for benchmarking. It certainly lo