Re: Partial aggregates pushdown

2021-10-21 Thread Alexander Pyhalov
Zhihong Yu писал 2021-10-22 00:43: Hi, w.r.t. 0001-Partial-aggregates-push-down-v03.patch Hi. For partial_agg_ok(), + if (agg->aggdistinct || agg->aggvariadic || agg->aggkind != AGGKIND_NORMAL || agg->aggorder != NIL) + ok = false; Since SearchSysCache1() is not called yet, you can

Re: Added schema level support for publication.

2021-10-21 Thread Greg Nancarrow
On Fri, Oct 22, 2021 at 12:41 PM Greg Nancarrow wrote: > > I was also previously concerned about what the behavior should be when > only including just the partitions of a partitioned table in a > publication using ALL TABLES IN SCHEMA and having > publish_via_partition_root=true. It seems to impl

Re: Experimenting with hash tables inside pg_dump

2021-10-21 Thread Andres Freund
Hi, On 2021-10-21 22:13:22 -0400, Tom Lane wrote: > Andres Freund writes: > > I wonder though if for some of them we should instead replace the per-object > > queries with one query returning the information for all objects of a type. > > It > > doesn't make all that much sense that we build and

Re: Parallel vacuum workers prevent the oldest xmin from advancing

2021-10-21 Thread Masahiko Sawada
On Wed, Oct 20, 2021 at 9:27 AM Masahiko Sawada wrote: > > On Wed, Oct 20, 2021 at 3:07 AM Alvaro Herrera > wrote: > > > > On 2021-Oct-19, Alvaro Herrera wrote: > > > > Thank you for the comment. > > > > Hmm, I think this should happen before the transaction snapshot is > > > established in the

Re: Added schema level support for publication.

2021-10-21 Thread Amit Kapila
On Thu, Oct 21, 2021 at 6:47 PM vignesh C wrote: > > > Thanks for the comments, the attached v45 patch has the fix for the same. > The first patch is mostly looking good to me apart from the below minor comments: 1. + + The catalog pg_publication_namespace contains the + mapping between sc

Re: Drop replslot after pgstat_shutdown cause assert coredump

2021-10-21 Thread Kyotaro Horiguchi
At Fri, 22 Oct 2021 11:43:08 +0900 (JST), Kyotaro Horiguchi wrote in > (Honestly, I haven't been able to reproduce the issue itself for > myself yet..) I managed to reproduce it for me. psql "dbname=postgres replication=database" postgres=# CREATE_REPLICATION_SLOT "ts1" TEMPORARY LOGICAL "pgo

Re: row filtering for logical replication

2021-10-21 Thread Peter Smith
On Thu, Sep 23, 2021 at 10:33 PM Tomas Vondra wrote: > > 11) extra (unnecessary) parens in the deparsed expression > > test=# alter publication p add table t where ((b < 100) and (c < 100)); > ALTER PUBLICATION > test=# \dRp+ p >Publication p > Owner | All tables

Re: XTS cipher mode for cluster file encryption

2021-10-21 Thread Sasasu
On 2021/10/22 01:28, Stephen Frost wrote: None of these are actually working with or changing the data though, they're just copying it. I don't think we'd actually want these to decrypt and reencrypt the data. OK, but why ALTER TABLE SET TABLESPACE use smgrread() and smgrextend() instead of c

Re: Drop replslot after pgstat_shutdown cause assert coredump

2021-10-21 Thread Kyotaro Horiguchi
I said: > Considering the coming shared-memory based stats collector, pgstat > must be shutdown before shared memory shutdown. Every operation that > requires stats collector also must be shut down before the pgstat > shutdown. A naive solution would be having before-pgstat-shutdown hook > but I'm

Re: Improve logging when using Huge Pages

2021-10-21 Thread Justin Pryzby
+ ereport(LOG, (errmsg("Anonymous shared memory was allocated %s huge pages.", with_hugepages ? "with" : "without"))); You shouldn't break a sentence into pieces like this, since it breaks translation. You don't want an untranslated "without" to appear in the middle of the translat

Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

2021-10-21 Thread Japin Li
On Fri, 22 Oct 2021 at 08:26, Masahiko Sawada wrote: > On Thu, Oct 21, 2021 at 11:18 PM Japin Li wrote: >> >> How it breaks? > > I don't know the real case but for example, if an application gets > changes via pg_recvlogical with a decoding plugin (say wal2json) from > the database whose DateSt

Re: pgstat_assert_is_up() can fail in walsender

2021-10-21 Thread Amit Langote
On Fri, Oct 22, 2021 at 2:14 AM Fujii Masao wrote: > On 2021/10/19 22:14, Amit Langote wrote: > > Hi, > > > > I can (almost) consistently reproduce $subject by executing the > > attached shell script, which I was using while constructing a test > > case for another thread. > > This seems the same

Re: Experimenting with hash tables inside pg_dump

2021-10-21 Thread Tom Lane
Andres Freund writes: > I wonder though if for some of them we should instead replace the per-object > queries with one query returning the information for all objects of a type. It > doesn't make all that much sense that we build and send one query for each > table and index. The trick is the pr

MDAM techniques and Index Skip Scan patch

2021-10-21 Thread Peter Geoghegan
I returned to the 1995 paper "Efficient Search of Multidimensional B-Trees" [1] as part of the process of reviewing v39 of the skip scan patch, which was posted back in May. It's a great paper, and anybody involved in the skip scan effort should read it thoroughly (if they haven't already). It's ea

RE: Data is copied twice when specifying both child and parent table in publication

2021-10-21 Thread shiy.f...@fujitsu.com
On Tuesday, October 19, 2021 10:47 AM houzj.f...@fujitsu.com wrote: > > On Monday, October 18, 2021 5:03 PM Amit Langote > wrote: > > I can imagine that the behavior seen here may look surprising, but not > > sure if I would call it a bug as such. I do remember thinking about > > this case and

Re: should we allow users with a predefined role to access pg_backend_memory_contexts view and pg_log_backend_memory_contexts function?

2021-10-21 Thread Bharath Rupireddy
On Fri, Oct 22, 2021 at 3:15 AM Bossart, Nathan wrote: > > On 10/20/21, 11:44 PM, "Bharath Rupireddy" > wrote: > > I would like to confine this thread to allowing non-superusers with a > > predefined role (earlier suggestion was to use pg_read_all_stats) to > > access views pg_backend_memory_con

Re: Added schema level support for publication.

2021-10-21 Thread Greg Nancarrow
On Fri, Oct 22, 2021 at 12:19 AM vignesh C wrote: > > I could reproduce the issue by using the following test: > --- Setup > create schema sch1; > create schema sch2; > create table sch1.tbl1 (a int) partition by range (a); > create table sch2.tbl1_part1 partition of sch1.tbl1 for values from (1)

Re: Experimenting with hash tables inside pg_dump

2021-10-21 Thread Andres Freund
Hi, On 2021-10-21 20:22:56 -0400, Tom Lane wrote: > Andres Freund writes: > Yeah, that. I tried doing a system-wide "perf" measurement, and soon > realized that a big fraction of the time for a "pg_dump -s" run is > being spent in the planner :-(. A trick for seeing the proportions of this easi

Re: Experimenting with hash tables inside pg_dump

2021-10-21 Thread Andres Freund
Hi, On 2021-10-21 16:37:57 -0700, Andres Freund wrote: > On 2021-10-21 18:27:25 -0400, Tom Lane wrote: > > (a) the executable size increases by a few KB --- apparently, even > > the minimum subset of simplehash.h's functionality is code-wasteful. > > Hm. Surprised a bit by that. In an optimized b

Re: Drop replslot after pgstat_shutdown cause assert coredump

2021-10-21 Thread Kyotaro Horiguchi
At Fri, 22 Oct 2021 02:10:21 +0900, Fujii Masao wrote in > Even in prior to the commit, pgstat_shutdown_hook() can be called > before ProcKill() at the backend exit, so ISTM that the problem can > be reproduced. > > Probably we need to make sure that pgstat_shutdown_hook() is called > after Pro

Re: LogicalChanges* and LogicalSubxact* wait events are never reported

2021-10-21 Thread Masahiro Ikeda
On 2021/10/21 17:40, Amit Kapila wrote: > On Wed, Oct 20, 2021 at 3:46 PM Masahiro Ikeda > wrote: >> >> On 2021/10/20 18:17, Amit Kapila wrote: >>> On Wed, Oct 20, 2021 at 10:50 AM Michael Paquier >>> wrote: On Wed, Oct 20, 2021 at 02:12:20PM +0900, Masahiro Ikeda wrote: > If m

Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

2021-10-21 Thread Masahiko Sawada
On Thu, Oct 21, 2021 at 11:18 PM Japin Li wrote: > > > On Thu, 21 Oct 2021 at 19:54, Masahiko Sawada wrote: > > On Thu, Oct 21, 2021 at 3:04 PM Dilip Kumar wrote: > >> > >> On Thu, Oct 21, 2021 at 11:16 AM Masahiko Sawada > >> wrote: > >> > > >> > On Wed, Oct 20, 2021 at 8:12 PM Japin Li wrot

Re: add retry mechanism for achieving recovery target before emitting FATA error "recovery ended before configured recovery target was reached"

2021-10-21 Thread Jeff Davis
On Wed, 2021-10-20 at 21:35 +0530, Bharath Rupireddy wrote: > The FATAL error "recovery ended before configured recovery target > was > reached" introduced by commit at [1] in PG 14 is causing the standby > to go down after having spent a good amount of time in recovery. > There > can be cases whe

Re: Experimenting with hash tables inside pg_dump

2021-10-21 Thread Tom Lane
Andres Freund writes: > Did you measure runtime of pg_dump, or how much CPU it used? I was looking mostly at wall-clock runtime, though I did notice that the CPU time looked about the same too. > I think a lot of > the time the backend is a bigger bottleneck than pg_dump... Yeah, that. I tried

Re: Experimenting with hash tables inside pg_dump

2021-10-21 Thread Bossart, Nathan
On 10/21/21, 4:14 PM, "Bossart, Nathan" wrote: > On 10/21/21, 3:29 PM, "Tom Lane" wrote: >> (b) I couldn't measure any change in performance at all. I tried >> it on the regression database and on a toy DB with 1 simple >> tables. Maybe on a really large DB you'd notice some difference, >>

Re: Experimenting with hash tables inside pg_dump

2021-10-21 Thread Andres Freund
Hi, On 2021-10-21 18:27:25 -0400, Tom Lane wrote: > Today, pg_dump does a lot of internal lookups via binary search > in presorted arrays. I thought it might improve matters > to replace those binary searches with hash tables, theoretically > converting O(log N) searches into O(1) searches. So I

Re: CREATEROLE and role ownership hierarchies

2021-10-21 Thread Mark Dilger
> On Oct 21, 2021, at 4:04 PM, Bossart, Nathan wrote: > > Regarding the "attack vector misfeature" comment, I remember being > surprised when I first learned how much roles with CREATEROLE can do. > When I describe CREATEROLE to others, I am sure to emphasize the note > in the docs about such

Re: Experimenting with hash tables inside pg_dump

2021-10-21 Thread Bossart, Nathan
On 10/21/21, 3:29 PM, "Tom Lane" wrote: > (b) I couldn't measure any change in performance at all. I tried > it on the regression database and on a toy DB with 1 simple > tables. Maybe on a really large DB you'd notice some difference, > but I'm not very optimistic now. I wonder how many ta

Re: CREATEROLE and role ownership hierarchies

2021-10-21 Thread Bossart, Nathan
On 10/20/21, 11:46 AM, "Mark Dilger" wrote: > The purpose of these patches is to fix the CREATEROLE escalation > attack vector misfeature. (Not everyone will see CREATEROLE that > way, but the perceived value of the patch set likely depends on how > much you see CREATEROLE in that light.) Regard

Experimenting with hash tables inside pg_dump

2021-10-21 Thread Tom Lane
Today, pg_dump does a lot of internal lookups via binary search in presorted arrays. I thought it might improve matters to replace those binary searches with hash tables, theoretically converting O(log N) searches into O(1) searches. So I tried making a hash table indexed by CatalogId (tableoid+o

Re: Fixing WAL instability in various TAP tests

2021-10-21 Thread Bossart, Nathan
On 9/28/21, 8:17 PM, "Michael Paquier" wrote: > On Tue, Sep 28, 2021 at 03:00:13PM -0400, Tom Lane wrote: >> Should we back-patch 0002? I'm inclined to think so. Should >> we then also back-patch enablement of the bloom test? Less >> sure about that, but I'd lean to doing so. A test that appea

Re: [RFC] building postgres with meson

2021-10-21 Thread Andres Freund
Hi, On 2021-10-12 15:55:22 -0400, John Naylor wrote: > Also, could utility makefile targets be made to work? I'm thinking in > particular of update-unicode and reformat-dat-files, for example. Implementing reformat-dat-files was trivial: https://github.com/anarazel/postgres/commit/29c1ce1ad473129

Re: Thinking about ANALYZE stats and autovacuum and large non-uniform tables

2021-10-21 Thread Peter Geoghegan
On Thu, Oct 21, 2021 at 2:13 PM Greg Stark wrote: > The problem I'm finding is that the distribution of these small > subsets can swing quickly. And understanding intercolumn correlations > even if we could do it perfectly would be no help at all. > > Consider a table with millions of rows that ar

Re: should we allow users with a predefined role to access pg_backend_memory_contexts view and pg_log_backend_memory_contexts function?

2021-10-21 Thread Bossart, Nathan
On 10/20/21, 11:44 PM, "Bharath Rupireddy" wrote: > I would like to confine this thread to allowing non-superusers with a > predefined role (earlier suggestion was to use pg_read_all_stats) to > access views pg_backend_memory_contexts and pg_shmem_allocations and > functions pg_get_backend_memory

Re: Thinking about ANALYZE stats and autovacuum and large non-uniform tables

2021-10-21 Thread Thomas Munro
On Fri, Oct 22, 2021 at 10:13 AM Greg Stark wrote: > Obviously this could get complex quickly. Perhaps it should be > something users could declare. Some kind of "partitioned statistics" > where you declare a where clause and we generate statistics for the > table where that where clause is true.

Re: Partial aggregates pushdown

2021-10-21 Thread Zhihong Yu
Hi, w.r.t. 0001-Partial-aggregates-push-down-v03.patch For partial_agg_ok(), + if (agg->aggdistinct || agg->aggvariadic || agg->aggkind != AGGKIND_NORMAL || agg->aggorder != NIL) + ok = false; Since SearchSysCache1() is not called yet, you can return false directly. + if (aggform-

Thinking about ANALYZE stats and autovacuum and large non-uniform tables

2021-10-21 Thread Greg Stark
One problem I've seen in multiple databases and is when a table has a mixture of data sets within it. E.g. A queue table where 99% of the entries are "done" but most queries are working with the 1% that are "new" or in other states. Often the statistics are skewed by the "done" entries and give bad

Re: parallelizing the archiver

2021-10-21 Thread Robert Haas
On Thu, Oct 21, 2021 at 4:29 PM Stephen Frost wrote: > restore_command used to be in recovery.conf, which disappeared with v12 > and it now has to go into postgresql.auto.conf or postgresql.conf. > > That's a huge breaking change. Not in the same sense. Moving the functionality to a different con

Re: [RFC] speed up count(*)

2021-10-21 Thread Robert Haas
On Thu, Oct 21, 2021 at 4:29 PM Joe Conway wrote: > meh -- the people who expect this to be impossibly fast don't typically > need or expect it to be exactly correct, and there is no way to make it > "exactly correct" in someone's snapshot without doing all the work. I think it could actually be

Re: [RFC] speed up count(*)

2021-10-21 Thread Andrew Dunstan
On 10/21/21 4:29 PM, Joe Conway wrote: > On 10/21/21 4:23 PM, Robert Haas wrote: >> On Thu, Oct 21, 2021 at 4:19 PM Joe Conway wrote: >>> That is a grossly overstated position. When I have looked, it is often >>> not that terribly far off. And for many use cases that I have heard of >>> at least

Re: [RFC] speed up count(*)

2021-10-21 Thread Joe Conway
On 10/21/21 4:23 PM, Robert Haas wrote: On Thu, Oct 21, 2021 at 4:19 PM Joe Conway wrote: That is a grossly overstated position. When I have looked, it is often not that terribly far off. And for many use cases that I have heard of at least, quite adequate. I don't think it's grossly overstat

Re: parallelizing the archiver

2021-10-21 Thread Stephen Frost
Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Tue, Oct 19, 2021 at 2:50 PM Stephen Frost wrote: > > I keep seeing this thrown around and I don't quite get why we feel this > > is the case. I'm not completely against trying to maintain backwards > > compatibility, but at the same t

Re: [RFC] speed up count(*)

2021-10-21 Thread Robert Haas
On Thu, Oct 21, 2021 at 4:19 PM Joe Conway wrote: > That is a grossly overstated position. When I have looked, it is often > not that terribly far off. And for many use cases that I have heard of > at least, quite adequate. I don't think it's grossly overstated. If you need an approximation it ma

Re: parallelizing the archiver

2021-10-21 Thread Robert Haas
On Tue, Oct 19, 2021 at 2:50 PM Stephen Frost wrote: > I keep seeing this thrown around and I don't quite get why we feel this > is the case. I'm not completely against trying to maintain backwards > compatibility, but at the same time, we just went through changing quite > a bit around in v12 wi

Re: [RFC] speed up count(*)

2021-10-21 Thread Joe Conway
On 10/21/21 4:06 PM, Robert Haas wrote: On Thu, Oct 21, 2021 at 9:09 AM Joe Conway wrote: I think you are exactly correct. People seem to understand that with a predicate it is harder, but they expect select count(*) from foo; to be nearly instantaneous, and they don't really need it to be

Re: CREATE ROLE IF NOT EXISTS

2021-10-21 Thread David Christensen
On Tue, Oct 19, 2021 at 4:29 PM Isaac Morland wrote: > On Tue, 19 Oct 2021 at 16:12, David Christensen < > david.christen...@crunchydata.com> wrote: > >> Greetings -hackers, >> >> Enclosed is a patch that implements CREATE ROLE IF NOT EXISTS (along with >> the same support for USER/GROUP). This

Re: [RFC] speed up count(*)

2021-10-21 Thread Robert Haas
On Thu, Oct 21, 2021 at 9:09 AM Joe Conway wrote: > I think you are exactly correct. People seem to understand that with a > predicate it is harder, but they expect > > select count(*) from foo; > > to be nearly instantaneous, and they don't really need it to be exact. > The stock answer for tha

Re: ThisTimeLineID can be used uninitialized

2021-10-21 Thread Robert Haas
On Tue, Oct 19, 2021 at 4:44 PM Andres Freund wrote: > It's quite confusing. It's *really* not helped by physical replication using > but not really using an xlogreader to keep state. Which presumably isn't > actually used during a physical CreateReplicationSlot(), but is referenced by > a comment

Re: XTS cipher mode for cluster file encryption

2021-10-21 Thread Stephen Frost
Greetings, * Sasasu (i...@sasa.su) wrote: > On 2021/10/20 20:24, Stephen Frost wrote: > > PG does have a block-based IO API, it's just not exposed as hooks. In > > particular, take a look at md.c, though perhaps you'd be more interested > > in the higher level bufmgr.c routines. For the specific

Re: pgstat_assert_is_up() can fail in walsender

2021-10-21 Thread Fujii Masao
On 2021/10/19 22:14, Amit Langote wrote: Hi, I can (almost) consistently reproduce $subject by executing the attached shell script, which I was using while constructing a test case for another thread. This seems the same issue that was reported at the thread [1]. [1] https://www.postgresql

Re: Drop replslot after pgstat_shutdown cause assert coredump

2021-10-21 Thread Fujii Masao
On 2021/10/11 22:15, Greg Nancarrow wrote: On Mon, Oct 11, 2021 at 6:55 PM houzj.f...@fujitsu.com wrote: I can see the walsender tried to release a not-quite-ready repliaction slot that was created when create a subscription. But the pgstat has been shutdown before invoking ReplicationSlotR

Re: Adding CI to our tree

2021-10-21 Thread Andreas Karlsson
On 10/21/21 5:55 PM, Matthias van de Meent wrote: On Sat, 2 Oct 2021 at 17:05, Tom Lane wrote: Andres Freund writes: It's not like this forces you to use cirrus or anything. For people that don't want to use CI, It'll make cfbot a bit more effective (because people can adjust what it tests a

Re: Adding CI to our tree

2021-10-21 Thread Tom Lane
Matthias van de Meent writes: > I don't disagree on that part, but I fail to see what makes the > situations of an unused CI config file in the tree and an unused > `/.idea/` or `/.vs/` specifier in the .gitignore [0][1] distinct > enough for it to be resolved differently. Both are quality-of-life

Re: [PATCH] Fix memory corruption in pg_shdepend.c

2021-10-21 Thread David G. Johnston
On Thu, Oct 21, 2021 at 8:52 AM Tom Lane wrote: > We're fortunate > that cloning a nonempty template database is rare already. > > That, and a major use case for doing so is to quickly stage up testing data in a new database (i.e., not a production use case). Though I could see tenant-based prod

Re: Adding CI to our tree

2021-10-21 Thread Matthias van de Meent
On Sat, 2 Oct 2021 at 17:05, Tom Lane wrote: > > Andres Freund writes: > > It's not like this forces you to use cirrus or anything. For people that > > don't > > want to use CI, It'll make cfbot a bit more effective (because people can > > adjust what it tests as appropriate for $patch), but tha

Re: [PATCH] Fix memory corruption in pg_shdepend.c

2021-10-21 Thread Tom Lane
Alvaro Herrera writes: > I suppose pg_describe_object can be used on the contents of pg_shdepend > to detect it. I'm less sure what to do to correct it -- delete the > bogus entries and regenerate them with some bulk query? Seems that what copyTemplateDependencies wants to do can easily be model

Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

2021-10-21 Thread Japin Li
On Thu, 21 Oct 2021 at 23:10, Tom Lane wrote: > Japin Li writes: >> On Thu, 21 Oct 2021 at 22:46, Tom Lane wrote: >>> There's another issue here: the subscriber can run user-defined code >>> (in triggers), while AFAIK the sender cannot. > >> Sorry, I'm not sure about this. Could you give me a

Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

2021-10-21 Thread Tom Lane
Japin Li writes: > On Thu, 21 Oct 2021 at 22:46, Tom Lane wrote: >> There's another issue here: the subscriber can run user-defined code >> (in triggers), while AFAIK the sender cannot. > Sorry, I'm not sure about this. Could you give me an example? If you're doing logical replication into a t

Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

2021-10-21 Thread Japin Li
On Thu, 21 Oct 2021 at 22:46, Tom Lane wrote: > Japin Li writes: >> On Thu, 21 Oct 2021 at 19:54, Masahiko Sawada wrote: >>> BTW I think we can set the parameters from the subscriber side without >>> additional network round trips by specifying the "options" parameter >>> in the connection str

Re: wait event and archive_command

2021-10-21 Thread Bharath Rupireddy
On Thu, Oct 21, 2021 at 7:28 PM Fujii Masao wrote: > > Hi, > > I'd like to propose to add new wait event reported while archiver process > is executing archive_command. This would be helpful to observe > what archiver is doing and check whether it has some troubles or not. > Thought? PoC patch att

Re: [PATCH] Fix memory corruption in pg_shdepend.c

2021-10-21 Thread Alvaro Herrera
On 2021-Oct-21, Michael Paquier wrote: > On Wed, Oct 20, 2021 at 09:19:51AM -0300, Alvaro Herrera wrote: > > Ouch ... this means that pg_shdepends contents are broken for databases > > created with 14.0? hmm ... yes. > > Yes, it means so :( For the upcoming release notes in 14.1 I think we'd do

Re: Assorted improvements in pg_dump

2021-10-21 Thread Tom Lane
Justin Pryzby writes: > On Wed, Oct 20, 2021 at 05:14:45PM -0400, Tom Lane wrote: >> Arguably, 0003 is a bug fix that we should consider back-patching. >> However, I've not heard field reports of the problems it fixes, >> so maybe there's no need to bother. > FYI, I see this issue happen in produ

Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

2021-10-21 Thread Tom Lane
Japin Li writes: > On Thu, 21 Oct 2021 at 19:54, Masahiko Sawada wrote: >> BTW I think we can set the parameters from the subscriber side without >> additional network round trips by specifying the "options" parameter >> in the connection string, no? > Yes, we can. However, each client should b

Re: Assorted improvements in pg_dump

2021-10-21 Thread Justin Pryzby
On Wed, Oct 20, 2021 at 05:14:45PM -0400, Tom Lane wrote: > Lastly, patch 0003 addresses the concern I raised at [3] that it's > unsafe to call pg_get_partkeydef() and pg_get_expr(relpartbound) > in getTables(). Looking closer I realized that we can't cast > pg_class.reloftype to regtype at that p

Re: lastOverflowedXid does not handle transaction ID wraparound

2021-10-21 Thread Stan Hu
On Wed, Oct 20, 2021 at 9:01 PM Kyotaro Horiguchi wrote: > > lastOverflowedXid is the smallest subxid that possibly exists but > possiblly not known to the standby. So if all top-level transactions > older than lastOverflowedXid end, that means that all the > subtransactions in doubt are known to

Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

2021-10-21 Thread Japin Li
On Thu, 21 Oct 2021 at 19:54, Masahiko Sawada wrote: > On Thu, Oct 21, 2021 at 3:04 PM Dilip Kumar wrote: >> >> On Thu, Oct 21, 2021 at 11:16 AM Masahiko Sawada >> wrote: >> > >> > On Wed, Oct 20, 2021 at 8:12 PM Japin Li wrote: >> > > >> > > >> > > On Mon, 18 Oct 2021 at 17:27, Dilip Kumar

wait event and archive_command

2021-10-21 Thread Fujii Masao
Hi, I'd like to propose to add new wait event reported while archiver process is executing archive_command. This would be helpful to observe what archiver is doing and check whether it has some troubles or not. Thought? PoC patch attached. Also how about adding wait events for other commands lik

Re: ThisTimeLineID can be used uninitialized

2021-10-21 Thread Alvaro Herrera
On 2021-Oct-21, Michael Paquier wrote: > There is already an assumption in walsender.c where an invalid > timeline is 0, by the way? See sendTimeLineNextTLI and sendTimeLine. > Asserting here and there looks like a good thing to do for code paths > where the timeline should, or should not, be set

Re: Added schema level support for publication.

2021-10-21 Thread vignesh C
On Thu, Oct 21, 2021 at 3:29 PM Greg Nancarrow wrote: > > On Thu, Oct 21, 2021 at 3:25 AM vignesh C wrote: > > > > Attached v44 patch as the fixes for the same. > > > > In the v44-0001 patch, I have some doubts about the condition guarding > the following code in pg_get_publication_tables(): > >

Re: [RFC] speed up count(*)

2021-10-21 Thread Joe Conway
On 10/20/21 2:33 PM, John Naylor wrote: On Wed, Oct 20, 2021 at 2:23 PM Tomas Vondra mailto:tomas.von...@enterprisedb.com>> wrote: > > Couldn't we simply inspect the visibility map, use the index data only > for fully visible/summarized ranges, and inspect the heap for the > remaining pag

Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

2021-10-21 Thread Masahiko Sawada
On Thu, Oct 21, 2021 at 3:04 PM Dilip Kumar wrote: > > On Thu, Oct 21, 2021 at 11:16 AM Masahiko Sawada > wrote: > > > > On Wed, Oct 20, 2021 at 8:12 PM Japin Li wrote: > > > > > > > > > On Mon, 18 Oct 2021 at 17:27, Dilip Kumar wrote: > > > > On Mon, Oct 18, 2021 at 1:41 PM Japin Li wrote: >

Re: [Bug] Logical Replication failing if the DateStyle is different in Publisher & Subscriber

2021-10-21 Thread Japin Li
On Thu, 21 Oct 2021 at 14:04, Dilip Kumar wrote: > On Thu, Oct 21, 2021 at 11:16 AM Masahiko Sawada > wrote: >> >> On Wed, Oct 20, 2021 at 8:12 PM Japin Li wrote: >> > >> > >> > On Mon, 18 Oct 2021 at 17:27, Dilip Kumar wrote: >> > > On Mon, Oct 18, 2021 at 1:41 PM Japin Li wrote: >> > > >>

Re: Partial aggregates pushdown

2021-10-21 Thread Alexander Pyhalov
Tomas Vondra писал 2021-10-19 16:25: On 10/19/21 08:56, Alexander Pyhalov wrote: Hi. Tomas Vondra писал 2021-10-15 17:56: As for the proposed approach, it's probably good enough for the first version to restrict this to aggregates where the aggregate result is sufficient, i.e. we don't need an

Re: [PATCH] improve the pg_upgrade error message

2021-10-21 Thread Daniel Gustafsson
> On 14 Jul 2021, at 07:27, Suraj Kharage > wrote: > Overall patch looks good to me. Agreed, I think this is a good change and in line with how the check functions work in general. > Instead of giving suggestion about updating the pg_database catalog, can we > give "ALTER DATABASE ALLOW_CONN

Re: Added schema level support for publication.

2021-10-21 Thread Greg Nancarrow
On Thu, Oct 21, 2021 at 3:25 AM vignesh C wrote: > > Attached v44 patch as the fixes for the same. > In the v44-0001 patch, I have some doubts about the condition guarding the following code in pg_get_publication_tables(): + if (schemarelids) + { +/* + * If the publication publishes part

Re: Data is copied twice when specifying both child and parent table in publication

2021-10-21 Thread Amit Kapila
On Wed, Oct 20, 2021 at 7:11 PM Greg Nancarrow wrote: > > On Wed, Oct 20, 2021 at 9:19 PM Amit Kapila wrote: > > > > I don't see why data need to be replicated again even in that case. > > Can you see any such duplicate data replicated for non-partitioned > > tables? > > > > If my example is slig

Re: relation OID in ReorderBufferToastReplace error message

2021-10-21 Thread Amit Kapila
On Mon, Oct 18, 2021 at 3:59 PM Amit Kapila wrote: > > On Fri, Oct 15, 2021 at 3:40 AM Bossart, Nathan wrote: > > > > On 9/23/21, 11:26 AM, "Alvaro Herrera" wrote: > > > On 2021-Sep-23, Jeremy Schneider wrote: > > > > > >> On 9/22/21 20:11, Amit Kapila wrote: > > >> > > > >> > On Thu, Sep 23, 20

Re: LogicalChanges* and LogicalSubxact* wait events are never reported

2021-10-21 Thread Amit Kapila
On Wed, Oct 20, 2021 at 3:46 PM Masahiro Ikeda wrote: > > On 2021/10/20 18:17, Amit Kapila wrote: > > On Wed, Oct 20, 2021 at 10:50 AM Michael Paquier > > wrote: > >> > >> On Wed, Oct 20, 2021 at 02:12:20PM +0900, Masahiro Ikeda wrote: > >>> If my understanding is right, it's better to remove th

Re: pg_receivewal starting position

2021-10-21 Thread Ronan Dunklau
Le jeudi 21 octobre 2021, 09:21:44 CEST Michael Paquier a écrit : > On Thu, Oct 21, 2021 at 08:29:54AM +0200, Ronan Dunklau wrote: > > Ok, do you want me to propose a different patch for previous versions ? > > That's not necessary. Thanks for proposing. > > > Do you mean restart_lsn as the poin

Re: Failed transaction statistics to measure the logical replication progress

2021-10-21 Thread Amit Kapila
On Thu, Oct 21, 2021 at 6:49 AM Masahiko Sawada wrote: > > On Mon, Oct 18, 2021 at 7:03 PM Amit Kapila wrote: > > > > On Thu, Oct 14, 2021 at 9:23 AM houzj.f...@fujitsu.com > > wrote: > > > > > > On Thursday, September 30, 2021 12:15 PM Amit Kapila > > > > > > > > > > > These all views are rel

Re: pg_receivewal starting position

2021-10-21 Thread Michael Paquier
On Thu, Oct 21, 2021 at 08:29:54AM +0200, Ronan Dunklau wrote: > Ok, do you want me to propose a different patch for previous versions ? That's not necessary. Thanks for proposing. > Do you mean restart_lsn as the pointer argument to the function, or > restart_lsn as the field returned by the c