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

Thanks for working on it. I tested the patch on my system and it resolved the issue with commands running -V (version check). As you mentioned, I am also still seeing intermittent errors even with the patch as below - in 'pg_upgrade/002_pg_upgrade' - # Running: pg_upgrade --no-sync -d

Re: A tiny improvement of psql

On Tue, Dec 26, 2023 at 11:26 AM Kevin Wang wrote: > Hello hackers! > > I am an Oracle/PostgreSQL DBA, I am not a PG hacker. During my daily job, > I find a pain that should be fixed. > > As you know, we can use the UP arrow key to get the previous command to > avoid extra typing. This is a

Re: A tiny improvement of psql

On repeating the execution of last command in psql, we can always use below command to send current query buffer to server. \g \gx (with expanded output mode, that always come handy.) On Tue, Dec 26, 2023 at 9:56 PM Kevin Wang wrote: > Hello hackers! > > I am an Oracle/PostgreSQL DBA, I am

Re: Should "CRC" be in uppercase?

On Mon, Dec 25, 2023 at 12:51 PM Kyotaro Horiguchi wrote: > > A new function check_control_file() in pg_combinebackup.c has the > following message. > > > pg_fatal("%s: crc is incorrect", controlpath); > > I think "crc" should be in all uppercase in general and a brief >

Re: A typo in a messsage?

On Fri, Dec 22, 2023 at 1:50 PM Kyotaro Horiguchi wrote: > > I found the following message introduced by a recent commit. > > > errdetail("The first unsummarized LSN is this range is %X/%X.", > > Shouldn't the "is" following "LSN" be "in"? Pushed.

Re: Tab complete for CREATE SUBSCRIPTION ... CONECTION does not work

On Tue, 26 Dec 2023 at 18:10, Shubham Khanna wrote: > On Tue, Dec 26, 2023 at 3:02 PM Japin Li wrote: >> >> >> Hi hacker, >> >> As $subject detailed, the tab-complete cannot work such as: >> >>CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres port=6543' \t >> >> It seems that the

Re: Synchronizing slots from primary to standby

Hi, Thank you for working on this. On Tue, Dec 26, 2023 at 9:27 PM shveta malik wrote: > > On Tue, Dec 26, 2023 at 4:41 PM Zhijie Hou (Fujitsu) > wrote: > > > > On Wednesday, December 20, 2023 7:37 PM Amit Kapila > > wrote: > > > > > > On Wed, Dec 20, 2023 at 3:29 PM shveta malik > > >

Re: POC: GROUP BY optimization

Andrei Lepikhov writes: > To be clear. In [1], I mentioned we can perform micro-benchmarks and > structure costs of operators. At least for fixed-length operators, it is > relatively easy. I repeat what I said: this is a fool's errand. You will not get trustworthy results even for the cases

[PATCH] pg_dump: Do not dump statistics for excluded tables

Hi hackers, I've attached a patch against master that addresses a small bug in pg_dump. Previously, pg_dump would include CREATE STATISTICS statements for tables that were excluded from the dump, causing reload to fail if any excluded tables had extended statistics. The patch skips the

Re: POC: GROUP BY optimization

On 27/12/2023 11:15, Alexander Korotkov wrote: On Wed, Dec 27, 2023 at 5:23 AM Tom Lane wrote: Alexander Korotkov writes: 2) An accurate estimate of the sorting cost is quite a difficult task. Indeed. What if we make a simple rule of thumb that sorting integers and floats is cheaper than

Re: POC: GROUP BY optimization

Alexander Korotkov writes: > On Wed, Dec 27, 2023 at 5:23 AM Tom Lane wrote: >> I think it's a fool's errand to even try to separate different sort >> column orderings by cost. > Besides sorting column orderings by cost, this patch also tries to > match GROUP BY pathkeys to input pathkeys and

Re: POC: GROUP BY optimization

On Wed, Dec 27, 2023 at 5:23 AM Tom Lane wrote: > Alexander Korotkov writes: > > 2) An accurate estimate of the sorting cost is quite a difficult task. > > Indeed. > > > What if we make a simple rule of thumb that sorting integers and > > floats is cheaper than sorting numerics and strings with

Re: POC: GROUP BY optimization

Alexander Korotkov writes: > 2) An accurate estimate of the sorting cost is quite a difficult task. Indeed. > What if we make a simple rule of thumb that sorting integers and > floats is cheaper than sorting numerics and strings with collation C, > in turn, that is cheaper than sorting

Re: [PoC] Improve dead tuple storage for lazy vacuum

On Tue, Dec 26, 2023 at 12:43 PM Masahiko Sawada wrote: > > On Thu, Dec 21, 2023 at 4:41 PM John Naylor wrote: > > +TidStoreSetBlockOffsets(TidStore *ts, BlockNumber blkno, OffsetNumber > > *offsets, > > + int num_offsets) > > +{ > > + char buf[MaxBlocktableEntrySize]; > > + BlocktableEntry

Re: POC: GROUP BY optimization

On Tue, Dec 26, 2023 at 1:37 PM Andrei Lepikhov wrote: > On 21/12/2023 17:53, Alexander Korotkov wrote: > > On Sun, Oct 1, 2023 at 11:45 AM Andrei Lepikhov > > wrote: > >> New version of the patch. Fixed minor inconsistencies and rebased onto > >> current master. > > Thank you (and other

Re: Assert failure on 'list_member_ptr(rel->joininfo, restrictinfo)'

On Sun, Dec 24, 2023 at 2:02 PM Alexander Korotkov wrote: > The most noticeable thing for me is that self-join removal doesn't work with > partitioned tables. I think this is the direction for future work on this > subject. In non-partitioned cases, patchset gives a small memory overhead. >

Re: [HACKERS] Changing references of password encryption to hashing

On Tue, Nov 28, 2023 at 10:01:57AM -0500, Robert Haas wrote: > On Tue, Nov 28, 2023 at 9:55 AM Stephen Frost wrote: > > I do think we should use the correct terminology in our documentation > > and would support your working on improving things in this area. > > +1. Attached is a draft patch to

Re: Update docs for default value of fdw_tuple_cost

On Wed, Dec 27, 2023 at 12:27 AM Umair Shahid wrote: > Commit cac169d686eddb277880a0d8a760ac3007b4846a updated the default value > of fdw_tuple_cost from 0.01 to 0.2. The attached patch updates the docs to > reflect this change. > +1. Nice catch. Thanks Richard >

Multidimensional Histograms

Hello Hackers, To improve selectivities of queries I suggest to add support of multidimensional histograms as described in paper [1]. To query multidimensional histograms efficiently we can use H-trees as described in paper [2]. Postgres has limited support of multivariate statistics: * MCV

Re: A tiny improvement of psql

On Tue, 26 Dec 2023 at 22:45, Vik Fearing wrote: > It is kind of something we control. Per the psql docs, setting > > HISTCONTROL=ignoredups > > will do the trick. Yeah, the easiest "fix" (that I know of) for a user is to set HISTCONTROL in ~/.psqlrc to ignoredups using: \set HISTCONTROL

Re: Fix Brin Private Spool Initialization (src/backend/access/brin/brin.c)

On 12/26/23 19:10, Ranier Vilela wrote: > Hi, > > The commit b437571 I > think has an oversight. > When allocate memory and initialize private spool in function: > _brin_leader_participate_as_worker > > The behavior is the bs_spool (heap and

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

Hi, I wanted to take a look at the patch, and I noticed it's broken since 3d51cb5197 renamed a couple pgstat functions in August. I plan to maybe do some benchmarks etc. preferably on current master, so here's a version fixing that minor bitrot. As for the patch, I only skimmed through the

Re: A tiny improvement of psql

On 12/26/23 17:36, Tom Lane wrote: Kevin Wang writes: As you know, we can use the UP arrow key to get the previous command to avoid extra typing. This is a wonderful feature to save the lives of every DBA. However, if I type the commands like this sequence: A, B, B, B, B, B, B, as you can

Re: pg_stat_statements: more test coverage

On 24.12.23 03:03, Michael Paquier wrote: On Sat, Dec 23, 2023 at 03:18:01PM +0100, Peter Eisentraut wrote: +/* LCOV_EXCL_START */ +PG_FUNCTION_INFO_V1(pg_stat_statements); PG_FUNCTION_INFO_V1(pg_stat_statements_1_2); +/* LCOV_EXCL_STOP */ The only reason why I've seen this used at the C

Re: No LINGUAS file yet for pg_combinebackup

On 26.12.23 13:18, Michael Paquier wrote: On Mon, Dec 25, 2023 at 12:48:17PM -0500, Tom Lane wrote: I don't particularly care to see that warning until whenever it is that the translations first get populated. Perhaps we should hack up nls-global.mk to hide the warning, but that might bite

Re: [meson] expose buildtype debug/optimization info to pg_config

On 14.12.23 10:24, Junwang Zhao wrote: On Thu, Dec 14, 2023 at 4:50 PM Peter Eisentraut wrote: On 12.12.23 11:40, Junwang Zhao wrote: build system using configure set VAL_CFLAGS with debug and optimization flags, so pg_config will show these infos. Some extensions depend on the mechanism.

Re: Bug in nbtree optimization to skip > operator comparisons (or < comparisons in backwards scans)

Pavel, On Mon, Dec 25, 2023 at 8:32 PM Pavel Borisov wrote: > I've reviewed both patches: > 0001 - is a pure refactoring replacing argument transfer from via struct > member to transfer explicitly as a function argument. It's justified by the > fact firstPage is localized only to several

Re: Statistics Import and Export

Bruce Momjian writes: > I think we need a robust API to handle two cases: > * changes in how we store statistics > * changes in how how data type values are represented in the statistics > We have had such changes in the past, and I think these two issues are > what have prevented

Two small bugs in guc.c

I investigated the report at [1] about pg_file_settings not reporting invalid values of "log_connections". It turns out it's broken for PGC_BACKEND and PGC_SU_BACKEND parameters, but not other ones. The cause is a bit of premature optimization in this logic: * If a PGC_BACKEND

Re: Moving forward with TDE

On Sun, Dec 17, 2023 at 06:30:50AM +, Chris Travers wrote: > Hi, > > I was re-reading the patches here and there was one thing I didn't > understand. > > There are provisions for a separation of data encryption keys for primary and > replica I see, and these share a single WAL key. > >

Re: Statistics Import and Export

On Tue, Dec 26, 2023 at 02:18:56AM +0100, Tomas Vondra wrote: > interfaces > -- > > When I thought about the ability to dump/load statistics in the past, I > usually envisioned some sort of DDL that would do the export and import. > So for example we'd have EXPORT STATISTICS / IMPORT

Fix Brin Private Spool Initialization (src/backend/access/brin/brin.c)

Hi, The commit b437571 I think has an oversight. When allocate memory and initialize private spool in function: _brin_leader_participate_as_worker The behavior is the bs_spool (heap and index fields) are left empty. The code affected is:

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

On 12/26/23 11:49, Anton A. Melnikov wrote: > Hello! > > Earlier in this thread, the pgbench results were published, where with a > strong memory limit of 100MB > a significant, about 10%, decrease in TPS was observed [1]. > > Using dedicated server with 12GB RAM and methodology described in

Re: A tiny improvement of psql

Kevin Wang writes: > As you know, we can use the UP arrow key to get the previous command to > avoid extra typing. This is a wonderful feature to save the lives of every > DBA. However, if I type the commands like this sequence: A, B, B, B, B, B, > B, as you can see, B is the last command I

Update docs for default value of fdw_tuple_cost

Hi Commit cac169d686eddb277880a0d8a760ac3007b4846a updated the default value of fdw_tuple_cost from 0.01 to 0.2. The attached patch updates the docs to reflect this change. Thanks! Umair Shahid | Founder Professional Services for PostgreSQL https://stormatics.tech/

A tiny improvement of psql

Hello hackers! I am an Oracle/PostgreSQL DBA, I am not a PG hacker. During my daily job, I find a pain that should be fixed. As you know, we can use the UP arrow key to get the previous command to avoid extra typing. This is a wonderful feature to save the lives of every DBA. However, if I type

Re: Track in pg_replication_slots the reason why slots conflict?

On Thu, 21 Dec 2023 at 09:26, Amit Kapila wrote: > A conflicting column where NULL indicates no conflict, and other > > values indicate the reason for the conflict, doesn't seem too bad. > > > > This is fine too. > I prefer this option. There is precedent for doing it this way, for example in

Re: Show WAL write and fsync stats in pg_stat_io

Hi, On Tue, 26 Dec 2023 at 13:10, Michael Paquier wrote: > > On Tue, Dec 26, 2023 at 11:27:16AM +0300, Nazir Bilal Yavuz wrote: > > Maybe it is better to create a pg_stat_io_wal view like you said > > before. We could remove unused columns and add op_bytes for each > > writes and reads. Also, we

Re: Synchronizing slots from primary to standby

On Tue, Dec 26, 2023 at 4:41 PM Zhijie Hou (Fujitsu) wrote: > > On Wednesday, December 20, 2023 7:37 PM Amit Kapila > wrote: > > > > On Wed, Dec 20, 2023 at 3:29 PM shveta malik > > wrote: > > > > > > On Wed, Dec 20, 2023 at 9:12 AM Amit Kapila > > wrote: > > > > > > > > On Tue, Dec 19, 2023

Re: No LINGUAS file yet for pg_combinebackup

On Mon, Dec 25, 2023 at 12:48:17PM -0500, Tom Lane wrote: > I don't particularly care to see that warning until whenever > it is that the translations first get populated. Perhaps > we should hack up nls-global.mk to hide the warning, but > that might bite somebody someday. I'm inclined to just

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

Hi, The same intermittent failure is reproducible on my system. For the intermittent issues I found that many issues are due to errors where commands like 'psql -V' are not returning any output. To reproduce it in an easy way, I wrote a script (.bat file) with '--version' option for different

Re: POC: GROUP BY optimization

On 21/12/2023 17:53, Alexander Korotkov wrote: On Sun, Oct 1, 2023 at 11:45 AM Andrei Lepikhov wrote: New version of the patch. Fixed minor inconsistencies and rebased onto current master. Thank you (and other authors) for working on this subject. Indeed to GROUP BY clauses are

An improvement on parallel DISTINCT

While reviewing Heikki's Omit-junk-columns patchset[1], I noticed that root->upper_targets[] is used to set target for partial_distinct_rel, which is not great because root->upper_targets[] is not supposed to be used by the core code. The comment in grouping_planner() says: * Save the various

Re: Show WAL write and fsync stats in pg_stat_io

On Tue, Dec 26, 2023 at 11:27:16AM +0300, Nazir Bilal Yavuz wrote: > Maybe it is better to create a pg_stat_io_wal view like you said > before. We could remove unused columns and add op_bytes for each > writes and reads. Also, we can track both the number of bytes and the > number of the

Re: Tab complete for CREATE SUBSCRIPTION ... CONECTION does not work

On Tue, Dec 26, 2023 at 3:02 PM Japin Li wrote: > > > Hi hacker, > > As $subject detailed, the tab-complete cannot work such as: > >CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres port=6543' \t > > It seems that the get_previous_words() could not parse the single quote. > > OTOH, it works

Re: Synchronizing slots from primary to standby

On Tue, Dec 26, 2023 at 3:00 PM Hayato Kuroda (Fujitsu) wrote: > > > I think we should be able to detect it if we want but do we want to > > add this restriction considering that users can always install the > > required plugins after standby gets promoted? I think we can do either > > way in

Re: pg_basebackup has an accidentaly separated help message

On Mon, Dec 25, 2023 at 05:07:28PM +0900, Kyotaro Horiguchi wrote: > Yes. So, it turns out that they're found after they have been > committed. No problem. I've just applied what you had. I hope this makes your life a bit easier ;) -- Michael signature.asc Description: PGP signature

Specify description of the SpecialJoinInfo structure

Hi, Working on Asymmetric Join, I found slight inconsistency in the description of SpecialJoinInfo: join type JOIN_ANTI can be accompanied by a zero value of the ojrelid if this join was created by the transformation of the NOT EXISTS subquery. -- regards, Andrei Lepikhov Postgres

RE: Synchronizing slots from primary to standby

Dear Amit, > I think we should be able to detect it if we want but do we want to > add this restriction considering that users can always install the > required plugins after standby gets promoted? I think we can do either > way in this case but as we are not going to use these slots till the >

Re: planner chooses incremental but not the best one

Hi，Tomas Recently, I looked at papers related to estimation of cardinarity with selection. I may be biased towards the scheme provided by the paper "Distinct Sampling for Highly-Accurate Answers to Distinct Values Queries and Event Reports". This paper uses distinct sampling as opposed to the

Re: Track in pg_replication_slots the reason why slots conflict?

Hi, On Tue, Dec 26, 2023 at 05:23:56PM +0900, Michael Paquier wrote: > On Tue, Dec 26, 2023 at 08:44:44AM +0530, Amit Kapila wrote: > > Does anyone else have a preference on whether to change the existing > > column or add a new one? > > Just to be clear here, I'd vote for replacing the existing

Re: Synchronizing slots from primary to standby

On Thu, Dec 21, 2023 at 6:37 PM Hayato Kuroda (Fujitsu) wrote: > > 10. synchronize_one_slot > > IIUC, this function can synchronize slots even if the used plugin on primary > is > not installed on the secondary server. If the slot is created by the slotsync > worker, users will recognize it

Re: Synchronizing slots from primary to standby

On Fri, Dec 22, 2023 at 7:59 PM Bertrand Drouvot wrote: > > Hi, > > On Fri, Dec 22, 2023 at 04:02:21PM +0530, shveta malik wrote: > > PFA v53. Changes are: > > Thanks! > > > patch002: > > 2) Addressed comments in [2] for v52-002. > > 3) Fixed CFBot failure. The failure was caused by an assert in

Re: Show WAL write and fsync stats in pg_stat_io

Hi, On Tue, 26 Dec 2023 at 03:06, Michael Paquier wrote: > > On Mon, Dec 25, 2023 at 04:09:34PM +0300, Nazir Bilal Yavuz wrote: > > On Wed, 9 Aug 2023 at 21:52, Melanie Plageman > > wrote: > >> If there is any combination of BackendType and IOContext which will > >> always read XLOG_BLCKSZ

Re: Track in pg_replication_slots the reason why slots conflict?

On Tue, Dec 26, 2023 at 08:44:44AM +0530, Amit Kapila wrote: > Does anyone else have a preference on whether to change the existing > column or add a new one? Just to be clear here, I'd vote for replacing the existing boolean with a text. -- Michael signature.asc Description: PGP signature

Re: "pgoutput" options missing on documentation

On Thu, Dec 21, 2023 at 7:16 PM Emre Hasegeli wrote: > > Fixed versions are attached. > Pushed! -- With Regards, Amit Kapila.

Re: Change GUC hashtable to use simplehash?

On Wed, Dec 20, 2023 at 1:48 PM John Naylor wrote: > > On Wed, Dec 20, 2023 at 3:23 AM Jeff Davis wrote: > > > > The reason I looked here is that the inner while statement (to find the > > chunk size) looked out of place and possibly slow, and there's a > > bitwise trick we can use instead. > >