Re: An attempt to avoid locally-committed-but-not-replicated-to-standby-transactions in synchronous replication

2022-04-25 Thread Laurenz Albe
On Mon, 2022-04-25 at 19:51 +0530, Bharath Rupireddy wrote: > With synchronous replication typically all the transactions (txns) > first locally get committed, then streamed to the sync standbys and > the backend that generated the transaction will wait for ack from sync > standbys. While waiting f

Re: WIP: WAL prefetch (another approach)

2022-04-25 Thread Thomas Munro
On Tue, Apr 26, 2022 at 6:11 AM Tom Lane wrote: > I believe that the WAL prefetch patch probably accounts for the > intermittent errors that buildfarm member topminnow has shown > since it went in, eg [1]: > > diff -U3 > /home/nm/ext4/HEAD/pgsql/contrib/pg_walinspect/expected/pg_walinspect.out >

RE: Skipping schema changes in publication

2022-04-25 Thread osumi.takami...@fujitsu.com
On Thursday, April 21, 2022 12:15 PM vignesh C wrote: > Updated patch by changing the syntax to use EXCEPT instead of SKIP. Hi This is my review comments on the v2 patch. (1) gram.y I think we can make a unified function that merges preprocess_alltables_pubobj_list with check_except_in_pubobj_

Re: pgsql: Add contrib/pg_walinspect.

2022-04-25 Thread Michael Paquier
On Tue, Apr 26, 2022 at 01:25:14AM -0400, Tom Lane wrote: > I've been wondering if the issue could be traced to topminnow's unusual > hardware properties, specifically that it has MAXALIGN 8 even though > it's only a 32-bit machine per sizeof(void *). I think the only > other active buildfarm anim

Re: CLUSTER on partitioned index

2022-04-25 Thread Alvaro Herrera
On 2022-Apr-26, Michael Paquier wrote: > On Sat, Apr 16, 2022 at 08:58:50PM +0900, Michael Paquier wrote: > > Well, I am a bit annoyed that we don't actually check that a CLUSTER > > command does not block when doing a CLUSTER on a partitioned table > > while a lock is held on one of its partition

Re: pgsql: Add contrib/pg_walinspect.

2022-04-25 Thread Tom Lane
Michael Paquier writes: > Could this be an issue related to the addition of the circular WAL > decoding buffer, aka 3f1ce97? I already whined about this at [1]. I've been wondering if the issue could be traced to topminnow's unusual hardware properties, specifically that it has MAXALIGN 8 even t

Re: Making JIT more granular

2022-04-25 Thread David Rowley
(This is an old thread. See [1] if you're missing the original email.) On Tue, 4 Aug 2020 at 14:01, David Rowley wrote: > At the moment JIT compilation, if enabled, is applied to all > expressions in the entire plan. This can sometimes be a problem as > some expressions may be evaluated lots and

Re: CLUSTER on partitioned index

2022-04-25 Thread Michael Paquier
On Sat, Apr 16, 2022 at 08:58:50PM +0900, Michael Paquier wrote: > Well, I am a bit annoyed that we don't actually check that a CLUSTER > command does not block when doing a CLUSTER on a partitioned table > while a lock is held on one of its partitions. So, attached is a > proposal of patch to imp

Re: pgsql: Add contrib/pg_walinspect.

2022-04-25 Thread Michael Paquier
Hi Jeff, On Fri, Apr 08, 2022 at 07:27:44AM +, Jeff Davis wrote: > Add contrib/pg_walinspect. > > Provides similar functionality to pg_waldump, but from a SQL interface > rather than a separate utility. The tests of pg_walinspect look unstable: https://buildfarm.postgresql.org/cgi-bin/show_l

Re: BUG #17448: In Windows 10, version 1703 and later, huge_pages doesn't work.

2022-04-25 Thread Julien Rouhaud
Hi, Please keep the list in copy, especially if that's about Windows specific as I'm definitely not very knowledgeable about it. On Fri, Apr 01, 2022 at 09:18:03AM +, Wilm Hoyer wrote: > > If you don't wanna go the manifest way, maybe the RtlGetVersion function is > the one you need: > http

Re: [Proposal] vacuumdb --schema only

2022-04-25 Thread Nathan Bossart
On Tue, Apr 26, 2022 at 11:36:02AM +0900, Michael Paquier wrote: > The refactoring logic to build the queries is clear to follow. I have > a few comments about the shape of the patch, though. Thanks for taking a look! > case 'a': > - alldb = true; > + chec

Re: [PATCH] Teach pg_waldump to extract FPIs from the WAL

2022-04-25 Thread Michael Paquier
On Mon, Apr 25, 2022 at 10:11:10AM -0500, David Christensen wrote: > On Mon, Apr 25, 2022 at 1:11 AM Michael Paquier wrote: >> I don't think that there is any need to rely on a new logic if there >> is already some code in place able to do the same work. See >> verify_dir_is_empty_or_create() in

Re: [PATCH] Teach pg_waldump to extract FPIs from the WAL

2022-04-25 Thread Michael Paquier
On Mon, Apr 25, 2022 at 10:24:52AM -0500, David Christensen wrote: > On Mon, Apr 25, 2022 at 6:03 AM Bharath Rupireddy > wrote: >> Thanks for working on this. I'm just thinking if we can use these FPIs >> to repair the corrupted pages? I would like to understand more >> detailed usages of the FPIs

Re: [Proposal] vacuumdb --schema only

2022-04-25 Thread Michael Paquier
On Mon, Apr 25, 2022 at 09:18:53AM -0700, Nathan Bossart wrote: > I've marked it as ready-for-committer. The refactoring logic to build the queries is clear to follow. I have a few comments about the shape of the patch, though. case 'a': - alldb = true; +

Re: Building Postgres with lz4 on Visual Studio

2022-04-25 Thread Michael Paquier
On Wed, Apr 13, 2022 at 05:21:41PM +0300, Melih Mutlu wrote: > I tried to build Postgres from source using Visual Studio 19. It worked all > good. > Then I wanted to build it with some dependencies, started with the ones > listed here [1]. But I'm having some issues with lz4. > > First I downloade

Re: Cryptohash OpenSSL error queue in FIPS enabled builds

2022-04-25 Thread Michael Paquier
On Tue, Apr 26, 2022 at 12:07:32AM +0200, Daniel Gustafsson wrote: > In this particular codepath I think we can afford clearing it on the way out, > with a comment explaining why. It's easily reproducible and adding a call and > a comment is a good documentation for ourselves of this OpenSSL behav

Re: Skipping schema changes in publication

2022-04-25 Thread Peter Smith
On Sat, Apr 23, 2022 at 2:09 AM Bharath Rupireddy wrote: > > On Tue, Mar 22, 2022 at 12:39 PM vignesh C wrote: > > > > Hi, > > > > This feature adds an option to skip changes of all tables in specified > > schema while creating publication. > > This feature is helpful for use cases where the user

Re: Estimating HugePages Requirements?

2022-04-25 Thread Michael Paquier
On Mon, Apr 25, 2022 at 04:55:25PM +0200, Magnus Hagander wrote: > AIUI that was the original use-case for this feature. It certainly was for > me :) Perhaps we'd be fine with relaxing the requirements here knowing that the control file should never be larger than PG_CONTROL_MAX_SAFE_SIZE (aka the

Re: add checkpoint stats of snapshot and mapping files of pg_logical dir

2022-04-25 Thread Michael Paquier
On Mon, Apr 25, 2022 at 01:34:38PM -0700, Nathan Bossart wrote: > I took another look at the example output, and I think I agree that logging > the total time for logical decoding operations is probably the best path > forward. This information would be enough to clue an administrator into > the p

Re: Cryptohash OpenSSL error queue in FIPS enabled builds

2022-04-25 Thread Tom Lane
Daniel Gustafsson writes: > In this particular codepath I think we can afford clearing it on the way out, > with a comment explaining why. Yeah. It seems out of the ordinary for an OpenSSL call to stack two error conditions, so treating a known case of that specially seems reasonable. Patches s

Re: bogus: logical replication rows/cols combinations

2022-04-25 Thread Tomas Vondra
On 4/25/22 17:48, Alvaro Herrera wrote: > I just noticed that publishing tables on multiple publications with > different row filters and column lists has somewhat surprising behavior. > To wit: if a column is published in any row-filtered publication, then > the values for that column are sent to

Re: Cryptohash OpenSSL error queue in FIPS enabled builds

2022-04-25 Thread Daniel Gustafsson
> On 25 Apr 2022, at 02:50, Michael Paquier wrote: > On Sat, Apr 23, 2022 at 11:40:19PM +0200, Daniel Gustafsson wrote: >> On 22 Apr 2022, at 19:01, Tom Lane wrote: >>> It seems like that solution means you're leaving an extra error in the >>> queue to >>> break unrelated code. Wouldn't it be

Re: add checkpoint stats of snapshot and mapping files of pg_logical dir

2022-04-25 Thread Nathan Bossart
On Thu, Mar 24, 2022 at 03:22:11PM +0530, Bharath Rupireddy wrote: >> > > > > Both seem still very long. I still am doubtful this level of detail >> > > > > is >> > > > > appropriate. Seems more like a thing for a tracepoint or such. How >> > > > > about just >> > > > > printing the time for the

Re: Add --{no-,}bypassrls flags to createuser

2022-04-25 Thread Nathan Bossart
On Thu, Apr 21, 2022 at 01:21:57PM -0700, David G. Johnston wrote: > I'm ok with -m/--member as well (like with --role only one role can be > specified per switch instance so member, not membership, the later meaning, > at least for me, the collective). > > That -m doesn't match --role-to is no wo

Re: Use "WAL segment" instead of "log segment" consistently in user-facing messages

2022-04-25 Thread Nathan Bossart
It's been a few weeks, so I'm marking the commitfest entry as waiting-on-author. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com

Re: pgsql: Allow db.schema.table patterns, but complain about random garbag

2022-04-25 Thread Andrew Dunstan
On 2022-04-24 Su 15:37, Andrew Dunstan wrote: > On 2022-04-24 Su 14:19, Noah Misch wrote: > >> Even if MinGW has >> some magic to make that work, I suspect we'll want a non-header home. >> Perhaps >> src/common/exec.c? It's best to keep this symbol out of libpq and other >> DLLs, though I bet

Re: pg_walcleaner - new tool to detect, archive and delete the unneeded wal files (was Re: pg_archivecleanup - add the ability to detect, archive and delete the unneeded wal files on the primary)

2022-04-25 Thread Stephen Frost
Greetings, * Bharath Rupireddy (bharath.rupireddyforpostg...@gmail.com) wrote: > On Mon, Apr 18, 2022 at 8:48 PM Stephen Frost wrote: > > * Bharath Rupireddy (bharath.rupireddyforpostg...@gmail.com) wrote: > > > On Mon, Apr 18, 2022 at 7:41 PM Stephen Frost wrote: > > > > * Bharath Rupireddy (bh

Re: WIP: WAL prefetch (another approach)

2022-04-25 Thread Tom Lane
Oh, one more bit of data: here's an excerpt from pg_waldump output after the failed test: rmgr: Btree len (rec/tot): 72/72, tx:727, lsn: 0/01903BC8, prev 0/01903B70, desc: INSERT_LEAF off 111, blkref #0: rel 1663/16384/2673 blk 9 rmgr: Btree len (rec/tot): 72/

Re: WIP: WAL prefetch (another approach)

2022-04-25 Thread Tom Lane
I believe that the WAL prefetch patch probably accounts for the intermittent errors that buildfarm member topminnow has shown since it went in, eg [1]: diff -U3 /home/nm/ext4/HEAD/pgsql/contrib/pg_walinspect/expected/pg_walinspect.out /home/nm/ext4/HEAD/pgsql.build/contrib/pg_walinspect/results/

Re: proposal: possibility to read dumped table's name from file

2022-04-25 Thread Pavel Stehule
Hi fresh rebase Regards Pavel diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index c946755737..3711959fa2 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -779,6 +779,80 @@ PostgreSQL documentation + + --filt

Re: An attempt to avoid locally-committed-but-not-replicated-to-standby-transactions in synchronous replication

2022-04-25 Thread Nathan Bossart
On Mon, Apr 25, 2022 at 07:51:03PM +0530, Bharath Rupireddy wrote: > With synchronous replication typically all the transactions (txns) > first locally get committed, then streamed to the sync standbys and > the backend that generated the transaction will wait for ack from sync > standbys. While wa

Re: [Proposal] vacuumdb --schema only

2022-04-25 Thread Nathan Bossart
On Mon, Apr 25, 2022 at 08:50:09AM +0200, Gilles Darold wrote: > Can I change the commitfest status to ready for committers? I've marked it as ready-for-committer. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com

Re: variable filename for psql \copy

2022-04-25 Thread David G. Johnston
On Mon, Apr 25, 2022 at 1:24 AM Jiří Fejfar wrote: > contrib_regression=# copy (select 1) to :'afile'; > Hopefully you realize that COPY is going to place that file on the server, not send it to the psql client to be placed on the local machine. The best way to do copy in psql is: \set afile '.

bogus: logical replication rows/cols combinations

2022-04-25 Thread Alvaro Herrera
I just noticed that publishing tables on multiple publications with different row filters and column lists has somewhat surprising behavior. To wit: if a column is published in any row-filtered publication, then the values for that column are sent to the subscriber even for rows that don't match th

Move Section 9.27.7 (Data Object Management Functions) to System Information Chapter

2022-04-25 Thread David G. Johnston
Hi, Both the location and name of the linked to section make no sense to me: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT Neither of the tables listed there manage (cause to change) anything. They are pure informational functions - size and path of objec

Re: [PATCH] Teach pg_waldump to extract FPIs from the WAL

2022-04-25 Thread David Christensen
On Mon, Apr 25, 2022 at 6:03 AM Bharath Rupireddy wrote: > Thanks for working on this. I'm just thinking if we can use these FPIs > to repair the corrupted pages? I would like to understand more > detailed usages of the FPIs other than inspecting with pageinspect. My main use case was for being a

Re: [PATCH] Teach pg_waldump to extract FPIs from the WAL

2022-04-25 Thread David Christensen
On Mon, Apr 25, 2022 at 2:00 AM Drouvot, Bertrand wrote: > > Hi, > > On 4/25/22 8:11 AM, Michael Paquier wrote: > > On Sat, Apr 23, 2022 at 01:43:36PM -0500, David Christensen wrote: > >> Hi Matthias, great point. Enclosed is a revised version of the patch > >> that adds the fork identifier to th

Re: [PATCH] Teach pg_waldump to extract FPIs from the WAL

2022-04-25 Thread David Christensen
On Mon, Apr 25, 2022 at 1:11 AM Michael Paquier wrote: > > On Sat, Apr 23, 2022 at 01:43:36PM -0500, David Christensen wrote: > > Hi Matthias, great point. Enclosed is a revised version of the patch > > that adds the fork identifier to the end if it's a non-main fork. > > Like Alvaro, I have seen

Re: Estimating HugePages Requirements?

2022-04-25 Thread Magnus Hagander
On Mon, Apr 25, 2022 at 2:15 AM Michael Paquier wrote: > On Fri, Apr 22, 2022 at 09:49:34AM +0200, Magnus Hagander wrote: > > I agree that thats a very narrow use case. And I'm not sure the use case > of > > a running server is even that important here - it's really the offline > one > > that's i

An attempt to avoid locally-committed-but-not-replicated-to-standby-transactions in synchronous replication

2022-04-25 Thread Bharath Rupireddy
Hi, With synchronous replication typically all the transactions (txns) first locally get committed, then streamed to the sync standbys and the backend that generated the transaction will wait for ack from sync standbys. While waiting for ack, it may happen that the query or the txn gets canceled (

Re: json_object returning jsonb reuslt different from returning json, returning text

2022-04-25 Thread Andrew Dunstan
On 2022-04-25 Mo 01:19, alias wrote: > > seems it's a bug around value 0. > > SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING > jsonb) > FROM (VALUES (1, 1), (10, NULL),(4, null), (5, null),(6, null),(2, 2)) > foo(k, v); > return: > {"1": 1, "2": 2} > > SELECT JSON_OBJECTAGG(k

Re: tweak to a few index tests to hits ambuildempty() routine.

2022-04-25 Thread Tom Lane
Alvaro Herrera writes: > Hmm, so 027_stream_regress.pl is not prepared to deal with any unlogged > tables that may be left in the regression database (which is what my > spgist addition did). I first tried doing a TRUNCATE of the unlogged > table, but that doesn't work either, and it turns out th

Re: tweak to a few index tests to hits ambuildempty() routine.

2022-04-25 Thread Amul Sul
On Mon, Apr 25, 2022 at 7:23 PM Alvaro Herrera wrote: > > On 2022-Apr-25, Alvaro Herrera wrote: > > > On 2022-Apr-25, Alvaro Herrera wrote: > > > > > I added one change to include spgist too, which was uncovered, and > > > pushed this. > > Thanks for the commit with the improvement. Regards, Amu

Re: tweak to a few index tests to hits ambuildempty() routine.

2022-04-25 Thread Alvaro Herrera
On 2022-Apr-25, Alvaro Herrera wrote: > On 2022-Apr-25, Alvaro Herrera wrote: > > > I added one change to include spgist too, which was uncovered, and > > pushed this. > > Looking into the recoveryCheck failure in buildfarm. Hmm, so 027_stream_regress.pl is not prepared to deal with any unlogge

Re: tweak to a few index tests to hits ambuildempty() routine.

2022-04-25 Thread Alvaro Herrera
On 2022-Apr-25, Alvaro Herrera wrote: > I added one change to include spgist too, which was uncovered, and > pushed this. Looking into the recoveryCheck failure in buildfarm. -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/

Re: [PATCH] Compression dictionaries for JSONB

2022-04-25 Thread Aleksander Alekseev
Hi Zhihong, Many thanks for your feedback! > For src/backend/catalog/pg_dict.c, please add license header. Fixed. > + elog(ERROR, "skipbytes > decoded_size - outoffset"); > > Include the values for skipbytes, decoded_size and outoffset. In fact, this code should never be executed, an

Re: tweak to a few index tests to hits ambuildempty() routine.

2022-04-25 Thread Alvaro Herrera
On 2021-Nov-29, Amul Sul wrote: > Attached patch is doing small changes to brin, gin & gist index tests > to use an unlogged table without changing the original intention of > those tests and that is able to hit ambuildempty() routing which is > otherwise not reachable by the current tests. I add

Re: pg_receivewal fail to streams when the partial file to write is not fully initialized present in the wal receiver directory

2022-04-25 Thread Bharath Rupireddy
On Mon, Apr 25, 2022 at 6:38 AM Michael Paquier wrote: > > On Fri, Apr 22, 2022 at 07:17:37PM +0530, Bharath Rupireddy wrote: > > Right. We find enough disk space and go to write and suddenly the > > write operations fail for some reason or the VM crashes because of a > > reason other than disk sp

Re: [PATCH] Teach pg_waldump to extract FPIs from the WAL

2022-04-25 Thread Bharath Rupireddy
On Sat, Apr 23, 2022 at 4:21 AM David Christensen wrote: > > Hi -hackers, > > Enclosed is a patch to allow extraction/saving of FPI from the WAL > stream via pg_waldump. > > Description from the commit: > > Extracts full-page images from the WAL stream into a target directory, > which must be empt

Re: variable filename for psql \copy

2022-04-25 Thread Daniel Verite
Jiří Fejfar wrote: > I have found maybe buggy behaviour (of psql parser?) when using psql \copy > with psql variable used for filename. While it's annoying that it doesn't work as you tried it, this behavior is documented, so in that sense it's not a bug. The doc also suggests a workarou

RE: Perform streaming logical transactions by background workers and parallel apply

2022-04-25 Thread houzj.f...@fujitsu.com
On Friday, April 22, 2022 12:12 PM Peter Smith wrote: > > Hello Hou-san. Here are my review comments for v4-0001. Sorry, there > are so many of them (it is a big patch); some are trivial, and others > you might easily dismiss due to my misunderstanding of the code. But > hopefully, there are at l

variable filename for psql \copy

2022-04-25 Thread Jiří Fejfar
Hi all, I have found maybe buggy behaviour (of psql parser?) when using psql \copy with psql variable used for filename. SQL copy is working fine: contrib_regression=# \set afile '/writable_dir/out.csv' contrib_regression=# select :'afile' as filename; filename --- /w

Re: A problem about partitionwise join

2022-04-25 Thread Richard Guo
On Mon, Nov 22, 2021 at 3:04 PM Richard Guo wrote: > > The suggested changes have already been included in v5 patch. Sorry for > the confusion. > > Verified that the patch still applies and works on latest master. So I'm > moving it to the next CF (which is Commitfest 2022-01). Please correct > m