Sending logical replication data only after synchronous replication happened

Hi! On PgCon 2020 we had been discussing some caveats of synchronous replication [0] related to data durability in HA postgres installations. But also there was raised important concern about streaming logical replication only after it "actually happened" for HA cluster. Is anyone working on it

Enumize logical replication message actions

Hi All, Logical replication protocol uses single byte character to identify different chunks of logical repliation messages. The code uses character literals for the same. These literals are used as bare constants in code as well. That's true for almost all the code that deals with wire protocol.

Re: Parallel Inserts in CREATE TABLE AS

On 16.10.20 08:23, Bharath Rupireddy wrote: On Fri, Oct 16, 2020 at 11:33 AM Luc Vlaming wrote: Really looking forward to this ending up in postgres as I think it's a very nice improvement. Whilst reviewing your patch I was wondering: is there a reason you did not introduce a batch insert in

Re: upcoming API changes for LLVM 12

Hi, On 2020-10-16 02:45:51 -0300, Alvaro Herrera wrote: > Whee, sounds pretty good ... (am I dreaming too much if I hope > execution starts with non-jitted and switches on the fly to jitted > once background compilation finishes?) There's some more work needed to get there, but yes, the basics fo

Re: shared-memory based stats collector

It occurred to my mind the fact that I forgot to mention the most significant outcome of this patch. At Thu, 08 Oct 2020 16:03:26 +0900 (JST), Kyotaro Horiguchi wrote in > At Tue, 06 Oct 2020 10:06:44 +0900 (JST), Kyotaro Horiguchi > wrote in > > The previous version failed to flush local da

Re: Enumize logical replication message actions

> On Oct 16, 2020, at 3:25 PM, Ashutosh Bapat > wrote: > > Hi All, > Logical replication protocol uses single byte character to identify > different chunks of logical repliation messages. The code uses > character literals for the same. These literals are used as bare > constants in code as wel

Re: [PATCH] Add extra statistics to explain for Nested Loop

pá 16. 10. 2020 v 9:43 odesílatel napsal: > Hi, hackers. > For some distributions of data in tables, different loops in nested loop > joins can take different time and process different amounts of entries. > It makes average statistics returned by explain analyze not very useful > for DBA. > To f

Re: Wrong statistics for size of XLOG_SWITCH during pg_waldump.

Thanks for all the suggestion, and new patch attached. >Andres suggested that we don't need that description with per-record >basis. Do you have a reason to do that? (For clarity, I'm not >suggesting that you should reving it.) I think Andres is saying if we just log it in xlog_desc() then we can

Re: Enumize logical replication message actions

At Fri, 16 Oct 2020 08:08:40 +, Li Japin wrote in > > > On Oct 16, 2020, at 3:25 PM, Ashutosh Bapat > > wrote: > > > > Hi All, > > Logical replication protocol uses single byte character to identify > > different chunks of logical repliation messages. The code uses > > character literals

Re: Parallel INSERT (INTO ... SELECT ...)

On Fri, Oct 16, 2020 at 3:43 PM Amit Kapila wrote: > > On Thu, Oct 15, 2020 at 6:13 PM Amit Kapila wrote: > > > > On Thu, Oct 15, 2020 at 9:56 AM Greg Nancarrow wrote: > > > > > > Also, I'm seeing a partition-related error when running > > > installcheck-world - I'm investigating that. > > > > >

Re: Wrong statistics for size of XLOG_SWITCH during pg_waldump.

At Fri, 16 Oct 2020 16:21:47 +0800, "movead...@highgo.ca" wrote in > Thanks for all the suggestion, and new patch attached. > > >Andres suggested that we don't need that description with per-record > >basis. Do you have a reason to do that? (For clarity, I'm not > >suggesting that you should r

Possible typo in nodeAgg.c

Hi In /src/backend/executor/nodeAgg.c I found the following comment still use work mem, Since hash_mem has been introduced, Is it more accurate to use hash_mem here ? @@ -1827,7 +1827,7 @@ hash_agg_set_limits(double hashentrysize, double input_groups, int used_bits, /* * Don't

Re: [PATCH] Add extra statistics to explain for Nested Loop

Le ven. 16 oct. 2020 à 16:12, Pavel Stehule a écrit : > > > pá 16. 10. 2020 v 9:43 odesílatel napsal: > >> Hi, hackers. >> For some distributions of data in tables, different loops in nested loop >> joins can take different time and process different amounts of entries. >> It makes average stati

Re: gs_group_1 crashing on 13beta2/s390x

Re: Andres Freund > I had a successful check-world run with maximum jittery on s390x. But I > did hit the issue in different places than you did, so it'd be cool if > you could re-enable JIT for s390x - I think you have a package tracking > HEAD? Cool, thanks! I'm tracking PG14 head with apt.post

Re: Enumize logical replication message actions

On Fri, 16 Oct 2020 at 14:06, Kyotaro Horiguchi wrote: > At Fri, 16 Oct 2020 08:08:40 +, Li Japin wrote > in > > > > > On Oct 16, 2020, at 3:25 PM, Ashutosh Bapat < > ashutosh.bapat@gmail.com> wrote: > > > > > What about ’N’ for new tuples, ‘O’ for old tuple follows, ‘K’ for old > key fo

Re: Wrong statistics for size of XLOG_SWITCH during pg_waldump.

>It looks to me "We can know that length by subtracting the LSN of >XLOG_SWITCH from the next record's LSN so it doesn't add any >information." Sorry,maybe I miss this before. But I think it will be better to show it clearly. >So the length of in this case is: > >LOC(SEG A+1) - ReadRecPtr - LEN

Re: Parallel INSERT (INTO ... SELECT ...)

On Fri, Oct 16, 2020 at 2:16 PM Greg Nancarrow wrote: > > On Fri, Oct 16, 2020 at 3:43 PM Amit Kapila wrote: > > > > > Also, I noticed that you have allowed for > > parallelism only when all expressions/functions involved with Insert > > are parallel-safe, can't we allow parallel-restricted case

Re: Implementing Incremental View Maintenance

Hi, I have reviewed the past discussions in this thread on IVM implementation of the proposed patch[1], and summarized it as following . We would appreciate any comments or suggestions on the patch as regard of them. * Aggregate support The current patch supports several built-in aggregates, tha

Re: Feature improvement for pg_stat_statements

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, failed Spec compliant: tested, passed Documentation:tested, failed The patch applies cleanly and looks fine to me.I'm going to l

Re: New statistics for tuning WAL buffer size

On 2020-10-15 19:49, Fujii Masao wrote: On 2020/10/13 11:57, Masahiro Ikeda wrote: On 2020-10-06 15:57, Masahiro Ikeda wrote: 2.  Number of when new WAL file is created and zero-filled. As Fujii-san already commented, I think it's good for tuning. Just idea; it may be worth exposing the numbe

Re: Enumize logical replication message actions

On Fri, Oct 16, 2020 at 12:55 PM Ashutosh Bapat wrote: > > Hi All, > Logical replication protocol uses single byte character to identify > different chunks of logical repliation messages. The code uses > character literals for the same. These literals are used as bare > constants in code as well.

Re: Add session statistics to pg_stat_database

Hi Laurenz, I have applied the latest patch on master, all the regression test cases are passing and the implemented functionality is also looking fine. The point that I raised about idle connection not included is also addressed. thanks, Ahsan On Wed, Oct 14, 2020 at 2:28 PM Laurenz Albe wrote

Re: Feature improvement for pg_stat_statements

At Fri, 16 Oct 2020 10:47:50 +, Yuki Seino wrote in > The following review has been posted through the commitfest application: > make installcheck-world: tested, passed > Implements feature: tested, failed > Spec compliant: tested, passed > Documentation:tested,

Potential use of uninitialized context in pgcrypto

In px_crypt_md5() we have this section, with the second assignment to err being unchecked: /* */ err = px_find_digest("md5", &ctx); if (err) return NULL; err = px_find_digest("md5", &ctx1); Even though we know that the digest algorithm exists when we reach the second call, we m

Re: partition routing layering in nodeModifyTable.c

On Thu, Oct 15, 2020 at 11:59 PM Heikki Linnakangas wrote: > On Wed, Oct 14, 2020 at 6:04 PM Heikki Linnakangas wrote: > > I'll continue with the last couple of patches in this thread. > > I committed the move of the cross-partition logic to new > ExecCrossPartitionUpdate() function, with just mi

Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers

On 2020-Sep-30, Justin Pryzby wrote: > postgres=# SELECT tgrelid::regclass, tgenabled FROM pg_trigger WHERE > tgrelid::regclass::text IN ('t1','t2'); > tgrelid | tgenabled > -+--- > t1 | D > t2 | O > (2 rows) > > I consider this a bug, Yeah. > but CreateTrigStmt d

Re: Improper use about DatumGetInt32

On 2020-Sep-23, Ashutosh Bapat wrote: > > You're ignoring the xid use-case, for which DatumGetUInt32 actually is > > the right thing. > > There is DatumGetTransactionId() which should be used instead. > That made me search if there's PG_GETARG_TRANSACTIONID() and yes it's > there but only defined

Re: upcoming API changes for LLVM 12

Andres Freund writes: > On 2020-10-16 02:45:51 -0300, Alvaro Herrera wrote: >>> 2) When do we want to add LLVM 12 support? PG will soon stop compiling >>> against LLVM 12, which will be released in about 6 months. I worked >>> with Lang to make most of the breaking changes in a branch (to be >>> m

Re: partition routing layering in nodeModifyTable.c

On 2020-Oct-16, Amit Langote wrote: > On Thu, Oct 15, 2020 at 11:59 PM Heikki Linnakangas wrote: > > On Wed, Oct 14, 2020 at 6:04 PM Heikki Linnakangas wrote: > > And if we removed > > ri_PartitionInfo->pi_PartitionToRootMap, and always used > > ri_ChildToRootMap for it. > > Done in the attach

Re: [patch] Fix checksum verification in base backups for zero page headers

On 07.10.2020 11:18, Michael Paquier wrote: On Fri, Sep 25, 2020 at 08:53:27AM +0200, Michael Banck wrote: Oh right, I've fixed up the commit message in the attached V4. Not much a fan of what's proposed here, for a couple of reasons: - If the page is not new, we should check if the header is s

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

On Thu, Oct 15, 2020 at 11:41:23AM +0800, Julien Rouhaud wrote: > On Wed, Oct 14, 2020 at 10:40 PM Bruce Momjian wrote: > > There is that, and log_line_prefix, which I can imaging being useful. > > My point is that if the queryid is visible, there should be a reason it > > defaults to show empty.

Re: Potential use of uninitialized context in pgcrypto

Daniel Gustafsson writes: > Even though we know that the digest algorithm exists when we reach the second > call, we must check the returnvalue from each call to px_find_digest to handle > allocation errors. Agreed, it's a bug. Will push in a bit. regards, tom lane

Re: Sometimes the output to the stdout in Windows disappears

Hello hackers, 13.09.2020 21:37, Tom Lane wrote: > I happened to try googling for other similar reports, and I found > a very interesting recent thread here: > > https://github.com/nodejs/node/issues/33166 > > It might not have the same underlying cause, of course, but it sure > sounds familiar.

Re: [Patch] Using Windows groups for SSPI authentication

Greetings, * Russell Foster (russell.foster.cod...@gmail.com) wrote: > On Thu, Oct 15, 2020 at 11:31 AM Stephen Frost wrote: > > > Please don't top-post on these lists.. > Didn't even know what that was, had to look it up. Hopefully it is > resolved. Gmail does too many things for you! Indeed!

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

On 2020-Oct-16, Bruce Momjian wrote: > On Thu, Oct 15, 2020 at 11:41:23AM +0800, Julien Rouhaud wrote: > > I did some naive benchmarking. Using a custom pgbench script with this > > query: > > I can see around 2% overhead (this query is reported with ~ 3ms > > latency average). Adding a few j

Re: Sometimes the output to the stdout in Windows disappears

Alexander Lakhin writes: > I've managed to make a simple reproducer. Please look at the patch attached. > There are two things crucial for reproducing the bug: >     ioctlsocket(sock, FIONBIO, &ioctlsocket_ret); // from pgwin32_socket() > and >     WSACleanup(); Oh, very interesting. Now that yo

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Alvaro Herrera writes: > In this case, I suppose using pg_stat_statement would require to have it > enabled, and it'd just not collect anything if disabled. Alternatively, pg_stat_statement might be able to force it on (applying a non-overridable PGC_INTERNAL-level setting) on load? Not sure if t

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

On Fri, Oct 16, 2020 at 01:03:55PM -0300, Álvaro Herrera wrote: > On 2020-Oct-16, Bruce Momjian wrote: > > > On Thu, Oct 15, 2020 at 11:41:23AM +0800, Julien Rouhaud wrote: > > > > I did some naive benchmarking. Using a custom pgbench script with this > > > query: > > > > I can see around 2% o

Re: allow online change primary_conninfo

On 2020-03-28 02:39, Alvaro Herrera wrote: On 2020-Mar-27, Alvaro Herrera wrote: On 2020-Mar-27, Alvaro Herrera wrote: > I pushed the wal_receiver_create_temp_slot bugfix, because I realized > after looking for long enough at WalReceiverMain() that the code was > beyond saving. I'll be pushin

Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers

Same, with a little test. I also just noticed that ALTER TABLE ONLY recurses to children, which it should not. >From 2fb3a3122bdbbb1eb5aa6608b5132b8ab07096d4 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 16 Oct 2020 10:58:54 -0300 Subject: [PATCH] When cloning triggers, preserve enabli

Re: Assertion failure with LEFT JOINs among >500 relations

I wrote: > David Rowley writes: >> I've ended up leaving the NaN checks in the join costing functions. >> There was no case mentioned in [1] that showed how we hit that >> reported test case, so I'm not really confident enough to know I'm not >> just reintroducing the same problem again by removin

Re: Commitfest manager 2020-11

On 24.08.2020 16:08, gkokola...@pm.me wrote: Hi all, Admittedly quite ahead of time, I would like to volunteer as Commitfest manager for 2020-11. If the role is not filled and there are no objections, I can reach out again in October for confirmation. //Georgios Wow, that was well in advan

Re: speed up unicode decomposition and recomposition

John Naylor wrote: > I'd be curious how it compares to ICU now I've made another run of the test in [1] with your v2 patches from this thread against icu_ext built with ICU-67.1. The results show the times in milliseconds to process about 10 million short strings: operation | unpatched

Re: Commitfest manager 2020-11

Anastasia Lubennikova writes: > I was looking for this message, to find out who is the current CFM. > Apparently, the November commitfest is not in progress yet. Nope, nor have we officially appointed a CFM for it yet. We're seldom organized enough to do that much in advance of the CF's start.

Re: Deleting older versions in unique indexes to avoid page splits

On Wed, Oct 14, 2020 at 7:40 AM Peter Geoghegan wrote: > Right. The trick is to pay only a fixed low cost (maybe as low as one > heap page access) when we start out, and ratchet it up only if the > first heap page access looks promising. Just as an example of how the patch can help, consider the

Re: More aggressive vacuuming of temporary tables

On Wed, Sep 9, 2020 at 11:02 AM Andres Freund wrote: > Obviously lookup in such a more complicated structure isn't free. Nor is > building it. So we'd need some heuristics about when to do so. It'd > probably be OK to occasionally look at the age of the oldest in-progress > statement, to infer the

Re: Deleting older versions in unique indexes to avoid page splits

пт, 16 окт. 2020 г. в 21:12, Peter Geoghegan : > I ran the script for two hours and 16 clients with the patch, then for > another two hours with master. After that time, all 3 indexes were > exactly the same size with the patch, but had almost doubled in size > on master: > > aid_pkey_include_abal

Re: allow online change primary_conninfo

Hello Yep, I think it's useful and I already posted patch in this thread: https://www.postgresql.org/message-id/flat/3090621585393698%40myt5-1466095fe4e5.qloud-c.yandex.net#ee6574e93982b5628d140f15cb44 Currently without consensus regards, Sergei

Re: Commitfest manager 2020-11

On 16.10.2020 21:57, Tom Lane wrote: Anastasia Lubennikova writes: On the other hand, I noticed a lot of stall threads, that weren't updated in months. Some of them seem to pass several CFs without any activity at all. I believe that it is wrong for many reasons, the major of which IMHO is a fr

Re: Internal key management system

On Fri, Jul 31, 2020 at 04:06:38PM +0900, Masahiko Sawada wrote: > > Given that the purpose of the key manager is to help TDE, discussing > > the SQL interface part (i.g., the second patch) deviates from the > > original purpose. I think we should discuss the design and > > implementation of the ke

Re: upcoming API changes for LLVM 12

Hi, On 2020-10-16 10:22:57 -0400, Tom Lane wrote: > Yeah. As long as we're not breaking the ability to build against older > LLVM, I can't see a reason not to apply and back-patch these changes. > We usually want all supported PG versions to build against newer tool > chains, and this seems to fa

Re: Internal key management system

Bruce Momjian writes: > Second, in testing starting/stopping the server, pg_ctl doesn't allow > the cluster_passphrase_command to read from /dev/tty, which I think is a > requirement because the command could likely require a user-supplied > unlock key, even if that is not the actual passphrase, j

Re: Deleting older versions in unique indexes to avoid page splits

On Fri, Oct 16, 2020 at 1:00 PM Victor Yegorov wrote: > I really like these results, great work! Thanks Victor! > I'm also wondering how IO numbers changed due to these improvements, > shouldn't be difficult to look into. Here is the pg_statio_user_indexes for patch for the same run: scheman

Re: upcoming API changes for LLVM 12

Andres Freund writes: > A related question is whether it'd be time to prune the oldest supported > LLVM version. 3.9.0 was released 2016-08-31 (and 3.9.1, the only point > release, was 2016-12-13). There's currently no *pressing* reason to > reduce it, but it is the cause of few #ifdefs - but more

Re: should INSERT SELECT use a BulkInsertState?

On Sat, Sep 19, 2020 at 08:32:15AM -0500, Justin Pryzby wrote: > On Sun, Jul 12, 2020 at 08:57:00PM -0500, Justin Pryzby wrote: > > On Thu, Jun 04, 2020 at 10:30:47AM -0700, Andres Freund wrote: > > > On 2020-05-08 02:25:45 -0500, Justin Pryzby wrote: > > > > Seems to me it should, at least conditi

Re: [PATCH] Add extra statistics to explain for Nested Loop

On 16.10.2020 12:07, Julien Rouhaud wrote: Le ven. 16 oct. 2020 à 16:12, Pavel Stehule > a écrit : pá 16. 10. 2020 v 9:43 odesílatel mailto:e.sokol...@postgrespro.ru>> napsal: Hi, hackers. For some distributions of data in tables, different

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

On 2020-Sep-24, Amit Langote wrote: Hello Amit, > Sorry I totally failed to see the v2 you had posted and a couple of > other emails where you mentioned the issues I brought up. No worries, I appreciate you reviewing this. > However, I am a bit curious about including detached partitions in > s

Re: upcoming API changes for LLVM 12

On 2020-Oct-16, Andres Freund wrote: > A related question is whether it'd be time to prune the oldest supported > LLVM version. 3.9.0 was released 2016-08-31 (and 3.9.1, the only point > release, was 2016-12-13). There's currently no *pressing* reason to > reduce it, but it is the cause of few #if

Stats collector's idx_blks_hit value is highly misleading in practice

It occurs to mean that statistics collector stats such as pg_statio_*_tables.idx_blks_hit are highly misleading in practice because they fail to take account of the difference between internal pages and leaf pages in B-Tree indexes. These two types of pages are in fundamentally different categories

Re: Internal key management system

On Fri, Oct 16, 2020 at 04:56:47PM -0400, Tom Lane wrote: > Bruce Momjian writes: > > Second, in testing starting/stopping the server, pg_ctl doesn't allow > > the cluster_passphrase_command to read from /dev/tty, which I think is a > > requirement because the command could likely require a user-s

Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers

On 2020-Oct-16, Alvaro Herrera wrote: > I also just noticed that ALTER TABLE ONLY recurses to children, which it > should not. Apparently I wrote (bogus) bespoke code to handle recursion in EnableDisableTrigger instead of using ATSimpleRecursion. This patch seems to fix this problem. diff --git

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

On Sat, Oct 17, 2020 at 12:23 AM Tom Lane wrote: > > Alvaro Herrera writes: > > In this case, I suppose using pg_stat_statement would require to have it > > enabled, and it'd just not collect anything if disabled. Yes, my idea was to be able to have pg_stat_statements enabled even if no queryid

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

On Fri, Oct 16, 2020 at 11:04 PM Bruce Momjian wrote: > > On Thu, Oct 15, 2020 at 11:41:23AM +0800, Julien Rouhaud wrote: > > On Wed, Oct 14, 2020 at 10:40 PM Bruce Momjian wrote: > > > There is that, and log_line_prefix, which I can imaging being useful. > > > My point is that if the queryid is

Re: [PATCH] Add extra statistics to explain for Nested Loop

so 17. 10. 2020 v 0:11 odesílatel Anastasia Lubennikova < a.lubennik...@postgrespro.ru> napsal: > On 16.10.2020 12:07, Julien Rouhaud wrote: > > Le ven. 16 oct. 2020 à 16:12, Pavel Stehule a > écrit : > >> >> >> pá 16. 10. 2020 v 9:43 odesílatel napsal: >> >>> Hi, hackers. >>> For some distribut

Re: [PATCH] Add extra statistics to explain for Nested Loop

On Sat, Oct 17, 2020 at 12:15 PM Pavel Stehule wrote: > > so 17. 10. 2020 v 0:11 odesílatel Anastasia Lubennikova > napsal: >> >> On 16.10.2020 12:07, Julien Rouhaud wrote: >> >> Le ven. 16 oct. 2020 à 16:12, Pavel Stehule a >> écrit : >>> >>> >>> >>> pá 16. 10. 2020 v 9:43 odesílatel napsal:

Re: [PATCH] Add extra statistics to explain for Nested Loop

so 17. 10. 2020 v 6:26 odesílatel Julien Rouhaud napsal: > On Sat, Oct 17, 2020 at 12:15 PM Pavel Stehule > wrote: > > > > so 17. 10. 2020 v 0:11 odesílatel Anastasia Lubennikova < > a.lubennik...@postgrespro.ru> napsal: > >> > >> On 16.10.2020 12:07, Julien Rouhaud wrote: > >> > >> Le ven. 16 o

warn_unused_results

Forgetting to assign the return value of list APIs such as lappend() is a perennial favorite. The compiler can help point out such mistakes. GCC has an attribute warn_unused_results. Also C++ has standardized this under the name "nodiscard", and C has a proposal to do the same [0]. In my pat