Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
Hi Tom: On Fri, Aug 4, 2023 at 3:13 AM Tom Lane wrote: > Andy Fan writes: > >> If you use explicit cast, then the code should not be hard, in the > >> rewrite stage all information should be known. > > > Can you point to me where the code is for the XML stuff? > > I think Pavel means XMLTABLE,

Re: Improve join_search_one_level readibilty (one line change)

2023-08-03 Thread Richard Guo
On Fri, Aug 4, 2023 at 10:36 AM David Rowley wrote: > The whole lnext() stuff all feels a bit old now that Lists are arrays. > I think we'd be better adjusting the code to pass the List index where > we start from rather than the ListCell to start from. That way we can > use for_each_from() to

pgbench: allow to exit immediately when any client is aborted

2023-08-03 Thread Yugo NAGATA
Hi, I would like to propose to add an option to pgbench so that benchmark can quit immediately when any client is aborted. Currently, when a client is aborted due to some error, for example, network trouble, other clients continue their run until a certain number of transactions specified -t is

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
Hi: > > can confirm the patch's jsonb_object_field_numeric is faster than > pg_catalog."numeric"(jsonb). > Thanks for the confirmation. > > This function is not easy to find out... > > select jsonb_numeric(jsonb'{"a":11}'->'a'); --fail > jsonb_numeric is a prosrc rather than a proname,

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Pavel Stehule
čt 3. 8. 2023 v 16:27 odesílatel Andy Fan napsal: > Hi: > > >> If you use explicit cast, then the code should not be hard, in the >> rewrite stage all information should be known. >> > > Can you point to me where the code is for the XML stuff? I thought > this is a bad idea but I may accept it

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread jian he
On Thu, Aug 3, 2023 at 6:04 PM Matthias van de Meent wrote: > > > Is it? Detoasting only happens if the argument was toasted, and I have > serious doubts that the result of (a->'a') will be toasted in our > current system. Sure, we do need to allocate an intermediate result, > but that's in a

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

2023-08-03 Thread Peter Smith
FWIW, I confirmed that my review comments for v22* have all been addressed in the latest v26* patches. Thanks! -- Kind Regards, Peter Smith. Fujitsu Australia

Re: Improve join_search_one_level readibilty (one line change)

2023-08-03 Thread David Rowley
On Tue, 1 Aug 2023 at 01:48, Julien Rouhaud wrote: > Apart from that +1 from me for the patch, I think it helps focusing the > attention on what actually matters here. I think it's worth doing something to improve this code. However, I think we should go a bit further than what the proposed

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread Tom Lane
David Rowley writes: > Thank you for the report. I've just pushed a patch which I'm hoping will fix > it. Passes now on my VM. regards, tom lane

Re: Using defines for protocol characters

2023-08-03 Thread Dave Cramer
On Thu, 3 Aug 2023 at 16:59, Tatsuo Ishii wrote: > > On Thu, 3 Aug 2023 at 13:25, Tatsuo Ishii wrote: > > > >> > Greetings, > >> > > >> > Attached is a patch which introduces a file protocol.h. Instead of > using > >> > the actual characters everywhere in the code this patch names the > >> >

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Peter Geoghegan
On Thu, Aug 3, 2023 at 3:04 PM Tomas Vondra wrote: > Because my patch is all about reducing the heap pages, which are usually > the expensive part of the index scan. But you're right the "index scan" > with index filter may access more index pages, because it has fewer > "access predicates".

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread David Rowley
On Fri, 4 Aug 2023 at 11:54, Nathan Bossart wrote: > I'm seeing some reliable test failures for 32-bit builds on cfbot [0]. At > a glance, it looks like the relations are swapped in the plan. Thank you for the report. I've just pushed a patch which I'm hoping will fix it. David

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread Tom Lane
Nathan Bossart writes: > On Fri, Aug 04, 2023 at 09:28:51AM +1200, David Rowley wrote: >> Thank you for reviewing. I've pushed the patch to master only. > I'm seeing some reliable test failures for 32-bit builds on cfbot [0]. At > a glance, it looks like the relations are swapped in the plan.

Re: Oversight in reparameterize_path_by_child leading to executor crash

2023-08-03 Thread Richard Guo
On Thu, Aug 3, 2023 at 7:20 PM Ashutosh Bapat wrote: > However, if reparameterization can *not* happen at the planning time, we > have chosen a plan which can not be realised meeting a dead end. So as long > as we can ensure that the reparameterization is possible we can delay > actual

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Peter Geoghegan
On Thu, Aug 3, 2023 at 3:04 PM Tomas Vondra wrote: > When you say "index page accesses" do you mean accesses to index pages, > or accesses to heap pages from the index scan? Yes, that's exactly what I mean. Note that that's the dominant cost for the original BitmapOr plan. As I said upthread,

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread Nathan Bossart
On Fri, Aug 04, 2023 at 09:28:51AM +1200, David Rowley wrote: > Thank you for reviewing. I've pushed the patch to master only. I'm seeing some reliable test failures for 32-bit builds on cfbot [0]. At a glance, it looks like the relations are swapped in the plan. [0]

Re: Adding argument names to aggregate functions

2023-08-03 Thread Nathan Bossart
On Wed, Jul 19, 2023 at 09:38:12PM +0200, Daniel Gustafsson wrote: > Great, thanks! I had a quick look at this while rebasing (as well as your > updated patch) and it seems like a good idea to add this. Unless there are > objections I will look at getting this in. Hey Daniel, are you still

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Peter Geoghegan
On Thu, Aug 3, 2023 at 2:46 PM Tomas Vondra wrote: > Sure, having more choices means a risk of making mistakes. But does > simply postponing the choices to runtime actually solves this? It solves that one problem, yes. This is particularly important in cases where we would otherwise get truly

Re: Using defines for protocol characters

2023-08-03 Thread Tatsuo Ishii
> On Thu, 3 Aug 2023 at 13:25, Tatsuo Ishii wrote: > >> > Greetings, >> > >> > Attached is a patch which introduces a file protocol.h. Instead of using >> > the actual characters everywhere in the code this patch names the >> > characters and removes the comments beside each usage. >> >> >

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Tomas Vondra
On 8/3/23 21:21, Peter Geoghegan wrote: > On Thu, Aug 3, 2023 at 11:17 AM Tomas Vondra > wrote: >> Not sure. I'm a bit confused about what exactly is so risky on the plan >> produced with the patch. > > It's all about the worst case. In the scenarios that I'm concerned > about, we can be

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Tomas Vondra
On 8/3/23 20:50, Peter Geoghegan wrote: > On Thu, Aug 3, 2023 at 4:57 AM Tomas Vondra > wrote: >>> I understand that that's how the patch is structured. It is >>> nevertheless possible (as things stand) that the patch will make the >>> planner shift from a plan that uses "Access Predicates" to

Re: Using defines for protocol characters

2023-08-03 Thread Dave Cramer
On Thu, 3 Aug 2023 at 15:22, Dave Cramer wrote: > > > On Thu, 3 Aug 2023 at 13:25, Tatsuo Ishii wrote: > >> > Greetings, >> > >> > Attached is a patch which introduces a file protocol.h. Instead of using >> > the actual characters everywhere in the code this patch names the >> > characters and

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread David Rowley
On Fri, 4 Aug 2023 at 02:02, Andy Fan wrote: > I have checked the updated patch and LGTM. Thank you for reviewing. I've pushed the patch to master only. David

Re: Using defines for protocol characters

2023-08-03 Thread Dave Cramer
On Thu, 3 Aug 2023 at 13:25, Tatsuo Ishii wrote: > > Greetings, > > > > Attached is a patch which introduces a file protocol.h. Instead of using > > the actual characters everywhere in the code this patch names the > > characters and removes the comments beside each usage. > > > +#define

Re: Track Oldest Initialized WAL Buffer Page

2023-08-03 Thread Daniel Gustafsson
> On 3 Jul 2023, at 15:27, Heikki Linnakangas wrote: > But I don't see the point of tracking OldestInitializedPage. It seems cheap > enough that we could, if there's a need for it, but I don't see the need. Based on the above comments, and the thread stalling, I am marking this returned with

Re: Introduce "log_connection_stages" setting.

2023-08-03 Thread Daniel Gustafsson
> On 3 Jul 2023, at 15:57, Daniel Gustafsson wrote: > >> On 16 May 2023, at 20:51, Sergey Dudoladov >> wrote: > >> I have attached the fourth version of the patch. > > This version fails the ldap_password test on all platforms on pg_ctl failing > to start: > > [14:48:10.544] --- stdout ---

Re: Configurable FP_LOCK_SLOTS_PER_BACKEND

2023-08-03 Thread Tomas Vondra
On 8/3/23 01:51, Matt Smiley wrote: > I thought it might be helpful to share some more details from one of the > case studies behind Nik's suggestion. > > Bursty contention on lock_manager lwlocks recently became a recurring > cause of query throughput drops for GitLab.com, and we got to study

Re: [PoC] Implementation of distinct in Window Aggregates

2023-08-03 Thread Daniel Gustafsson
> On 11 Jul 2023, at 01:06, Andreas Karlsson wrote: > > On 3/12/23 09:17, Ankit Kumar Pandey wrote: >> Attaching updated patch with a fix for an issue in window function. >> I have also fixed naming convention of patch as last patch had incompatible >> name. > > Hi, > > This patch does not

Re: POC, WIP: OR-clause support for indexes

2023-08-03 Thread Alena Rybakina
On 02.08.2023 21:58, Peter Geoghegan wrote: On Wed, Aug 2, 2023 at 8:58 AM Alena Rybakina wrote: No, I haven't thought about it yet. I studied the example and it would really be nice to add optimization here. I didn't notice any problems with its implementation. I also have an obvious example

Re: Using defines for protocol characters

2023-08-03 Thread Tatsuo Ishii
> Greetings, > > Attached is a patch which introduces a file protocol.h. Instead of using > the actual characters everywhere in the code this patch names the > characters and removes the comments beside each usage. > +#define DESCRIBE_PREPARED 'S' > +#define DESCRIBE_PORTAL

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Peter Geoghegan
On Thu, Aug 3, 2023 at 11:17 AM Tomas Vondra wrote: > Not sure. I'm a bit confused about what exactly is so risky on the plan > produced with the patch. It's all about the worst case. In the scenarios that I'm concerned about, we can be quite sure that the saving from not using a BitmapOr will

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Tom Lane
Andy Fan writes: >> If you use explicit cast, then the code should not be hard, in the >> rewrite stage all information should be known. > Can you point to me where the code is for the XML stuff? I think Pavel means XMLTABLE, which IMO is an ugly monstrosity of syntax --- but count on the SQL

Re: Using defines for protocol characters

2023-08-03 Thread Nathan Bossart
On Thu, Aug 03, 2023 at 12:07:21PM -0600, Dave Cramer wrote: > On Thu, 3 Aug 2023 at 11:59, Alvaro Herrera wrote: >> I don't really like the name pattern you've chosen though; I think we >> need to have a common prefix in the defines. Maybe prepending PQMSG_ to >> each name would be enough. And

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Peter Geoghegan
On Thu, Aug 3, 2023 at 4:57 AM Tomas Vondra wrote: > > I understand that that's how the patch is structured. It is > > nevertheless possible (as things stand) that the patch will make the > > planner shift from a plan that uses "Access Predicates" to the maximum > > extent possible when scanning

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Tomas Vondra
On 8/3/23 18:47, Peter Geoghegan wrote: > On Thu, Aug 3, 2023 at 4:20 AM Tomas Vondra > wrote: >> Which is just the 7 buffers ... >> >> Did I do something wrong? > > I think that it might have something to do with your autovacuum > settings. Note that the plan that you've shown for the master

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread Tim Palmer
On Thu, 3 Aug 2023 at 05:50, David Rowley wrote: > I'm currently thinking it's > a bad idea to backpatch this but I'd consider it more if someone else > thought it was a good idea or if more people came along complaining > about poor plan choice in plans containing WindowAggs. Currently, it >

Re: Using defines for protocol characters

2023-08-03 Thread Dave Cramer
On Thu, 3 Aug 2023 at 11:59, Alvaro Herrera wrote: > On 2023-Aug-03, Dave Cramer wrote: > > > Greetings, > > > > Attached is a patch which introduces a file protocol.h. Instead of using > > the actual characters everywhere in the code this patch names the > > characters and removes the comments

Re: Using defines for protocol characters

2023-08-03 Thread Alvaro Herrera
On 2023-Aug-03, Dave Cramer wrote: > Greetings, > > Attached is a patch which introduces a file protocol.h. Instead of using > the actual characters everywhere in the code this patch names the > characters and removes the comments beside each usage. I saw this one last week. I think it's a

Using defines for protocol characters

2023-08-03 Thread Dave Cramer
Greetings, Attached is a patch which introduces a file protocol.h. Instead of using the actual characters everywhere in the code this patch names the characters and removes the comments beside each usage. Dave Cramer 0001-Created-protocol.h.patch Description: Binary data

Re: [RFC] Clang plugin for catching suspicious typedef casting

2023-08-03 Thread Tristan Partin
This is the first I am learning about clang plugins. Interesting concept. Did you give any thought to using libclang instead of a compiler plugin? I am kind of doing similar work, but I went with libclang instead of a plugin. -- Tristan Partin Neon (https://neon.tech)

[RFC] Clang plugin for catching suspicious typedef casting

2023-08-03 Thread Dmitry Dolgov
Hi, In the commit [1] one side change was to fix mixed up usage of BlockNumber and Buffer variables. This was a one-off manual effort, and indeed it hardly seems possible to get compilation warnings for such code, as long as the underlying types could be converted in a standard conforming way. As

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Peter Geoghegan
On Thu, Aug 3, 2023 at 4:20 AM Tomas Vondra wrote: > Which is just the 7 buffers ... > > Did I do something wrong? I think that it might have something to do with your autovacuum settings. Note that the plan that you've shown for the master branch isn't the same one that appears in

Re: Improve const use in zlib-using code

2023-08-03 Thread Tristan Partin
Both patches look good to me. -- Tristan Partin Neon (https://neon.tech)

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
Hi: > If you use explicit cast, then the code should not be hard, in the > rewrite stage all information should be known. > Can you point to me where the code is for the XML stuff? I thought this is a bad idea but I may accept it if some existing code does such a thing already. "such thing"

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread Andy Fan
On Thu, Aug 3, 2023 at 7:29 PM David Rowley wrote: > Thanks for having a look at this. > > On Thu, 3 Aug 2023 at 18:49, Andy Fan wrote: > > 1. ORDER BY or PARTITION BY > > > > select *, count(two) over (order by unique1) from tenk1 limit 1; > > DEBUG: startup_tuples = 1 > > DEBUG:

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Pavel Stehule
Hi čt 3. 8. 2023 v 15:23 odesílatel Andy Fan napsal: > Hi: > > >> More, I believe so lot of people uses more common syntax, and then this >> syntax should to have good performance - for jsonb - (val->'op')::numeric >> works, and then there should not be performance penalty, because this >>

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
Hi: On Thu, Aug 3, 2023 at 8:34 PM Chapman Flack wrote: > On 2023-08-03 03:53, Andy Fan wrote: > > I didn't realize timetime types are binary compatible with SQL, > > so maybe we can have some similar optimization as well. > > (It is a pity that timestamp(tz) are not binary, or else we may > >

Re: Show WAL write and fsync stats in pg_stat_io

2023-08-03 Thread Nazir Bilal Yavuz
Hi, Thanks for the review! Current status of the patch is: - 'WAL read' stats in xlogrecovery.c are added to pg_stat_io. - IOCONTEXT_INIT is added to count 'WAL init'. 'WAL init' stats are added to pg_stat_io. - pg_stat_io shows different op_bytes for the IOOBJECT_WAL operations. - Working on

Re: [PoC] Reducing planning time when tables have many partitions

2023-08-03 Thread Ashutosh Bapat
On Wed, Aug 2, 2023 at 12:11 PM Yuya Watari wrote: > Hello, > > I really appreciate sharing very useful scripts and benchmarking results. > > On Fri, Jul 28, 2023 at 6:51 PM Ashutosh Bapat > wrote: > > Given that most of the developers run assert enabled builds it would > > be good to bring

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
Hi: > More, I believe so lot of people uses more common syntax, and then this > syntax should to have good performance - for jsonb - (val->'op')::numeric > works, and then there should not be performance penalty, because this > syntax will be used in 99%. > This looks like a valid opinion IMO,

Re: Adding a pg_servername() function

2023-08-03 Thread Christoph Moench-Tegeder
## Laetitia Avrot (laetitia.av...@gmail.com): > For my customer, their use case is to be able from an SQL client to double > check they're on the right host before doing things that could become a > production disaster. Why not use cluster_name for that? Even if it may not be relevant for their

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Chapman Flack
On 2023-08-03 03:53, Andy Fan wrote: I didn't realize timetime types are binary compatible with SQL, so maybe we can have some similar optimization as well. (It is a pity that timestamp(tz) are not binary, or else we may just need one operator). Not to veer from the thread, but something about

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
Hi: > > Yes, it's not great, but that's just how this works. We can't > pre-specialize all possible operations that one might want to do in > PostgreSQL - that'd be absurdly expensive for binary and initial > database sizes. > Are any people saying we would pre-specialize all possible

Re: Adding a pg_servername() function

2023-08-03 Thread Laetitia Avrot
Le jeu. 3 août 2023 à 14:20, <066ce...@free.fr> a écrit : > > > > Agreed, depending on how hosts and dns are set, it can be useless. > > But normally, companies have host making standards to avoid that. > > > BTW you already have it : > > select setting from pg_settings where name =

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Tomas Vondra
On 8/3/23 03:32, Peter Geoghegan wrote: > On Wed, Aug 2, 2023 at 6:48 AM Tomas Vondra > wrote: >> How come we don't know that until the execution time? Surely when >> building the paths/plans, we match the clauses to the index keys, no? Or >> are you saying that just having a scan key is not

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

2023-08-03 Thread Melih Mutlu
Hi, Amit Kapila , 3 Ağu 2023 Per, 09:22 tarihinde şunu yazdı: > On Thu, Aug 3, 2023 at 9:35 AM Peter Smith wrote: > > I checked the latest patch v25-0001. > > > > LGTM. > > > > Thanks, I have pushed 0001. Let's focus on the remaining patches. > Thanks! Peter Smith , 3 Ağu 2023 Per, 12:06

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread David Rowley
Thanks for having a look at this. On Thu, 3 Aug 2023 at 18:49, Andy Fan wrote: > 1. ORDER BY or PARTITION BY > > select *, count(two) over (order by unique1) from tenk1 limit 1; > DEBUG: startup_tuples = 1 > DEBUG: startup_tuples = 1 > > select *, count(two) over (partition by unique1) from

Re: Use of additional index columns in rows filtering

2023-08-03 Thread Tomas Vondra
On 8/3/23 07:26, Peter Geoghegan wrote: > On Wed, Aug 2, 2023 at 6:32 PM Peter Geoghegan wrote: >> I don't dispute the fact that this can only happen when the planner >> believes (with good reason) that the expected cost will be lower. But >> I maintain that there is a novel risk to be concerned

Re: Oversight in reparameterize_path_by_child leading to executor crash

2023-08-03 Thread Ashutosh Bapat
On Thu, Aug 3, 2023 at 4:14 PM Richard Guo wrote: > > On Thu, Aug 3, 2023 at 12:38 PM Ashutosh Bapat < > ashutosh.bapat@gmail.com> wrote: > > Sometimes it would help to avoid the translation at all if we postpone > the reparameterization until createplan.c, such as in the case that a >

Re: Synchronizing slots from primary to standby

2023-08-03 Thread shveta malik
On Thu, Aug 3, 2023 at 12:28 AM Bharath Rupireddy wrote: > > On Tue, Aug 1, 2023 at 5:01 PM shveta malik wrote: > > > > > The work division amongst the sync workers can > > > be simple, the logical replication launcher builds a shared memory > > > structure based on number of slots to sync and

Re: Adding a pg_servername() function

2023-08-03 Thread 066ce286
> Agreed, depending on how hosts and dns are set, it can be useless. > But normally, companies have host making standards to avoid that. BTW you already have it : select setting from pg_settings where name = 'listen_addresses' No ?

Re: Oversight in reparameterize_path_by_child leading to executor crash

2023-08-03 Thread Richard Guo
On Thu, Aug 3, 2023 at 12:38 PM Ashutosh Bapat wrote: > On Tue, Aug 1, 2023 at 6:50 PM Tom Lane wrote: > > Alternatively, could we postpone the reparameterization until > > createplan.c? Having to build reparameterized expressions we might > > not use seems expensive, and it would also

Re: [PoC] pg_upgrade: allow to upgrade publisher node

2023-08-03 Thread Amit Kapila
On Wed, Aug 2, 2023 at 1:43 PM Hayato Kuroda (Fujitsu) wrote: > > Thank you for giving comments! PSA new version patchset. > > > 1. Do we really need 0001 patch after the latest change proposed by > > Vignesh in the 0004 patch? > > I removed 0001 patch and revived old patch which serializes slots

Re: Adding a pg_servername() function

2023-08-03 Thread Laetitia Avrot
Le jeu. 3 août 2023, 11:31, Matthias van de Meent < boekewurm+postg...@gmail.com> a écrit : > On Thu, 3 Aug 2023 at 10:37, Laetitia Avrot > wrote: > > > > Dear Hackers, > > > > One of my customers suggested creating a function that could return the > server's hostname. > > Mostly for my

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Matthias van de Meent
On Wed, 2 Aug 2023 at 03:05, Andy Fan wrote: > > Hi Matthias: > > On Wed, Aug 2, 2023 at 7:33 AM Andy Fan wrote: >> >> >> >> On Tue, Aug 1, 2023 at 7:03 PM Matthias van de Meent >> wrote: >>> >>> On Tue, 1 Aug 2023 at 06:39, Andy Fan wrote: >>> > >>> > Hi: >>> > >>> > Currently if we want to

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Pavel Stehule
Hi čt 3. 8. 2023 v 9:53 odesílatel Andy Fan napsal: > Hi Pavel: > > Thanks for the feedback. > > I don't like this solution because it is bloating operators and it is not >> extra readable. >> > > If we support it with cast, could we say we are bloating CAST? It is true > that it is not extra

Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

2023-08-03 Thread tender wang
I think the code to determine that fk of a partition is inherited or not is not enough. For example, in this case, foreign key r_1_p_id_fkey1 is not inherited from parent. If conform->conparentid(in DetachPartitionFinalize func) is valid, we should recheck confrelid(pg_constraint) field. I try

Re: Adding a pg_servername() function

2023-08-03 Thread Matthias van de Meent
On Thu, 3 Aug 2023 at 10:37, Laetitia Avrot wrote: > > Dear Hackers, > > One of my customers suggested creating a function that could return the > server's hostname. Mostly for my curiosity: What would be their use case? I only see limited usability, considering that the local user's hostname

RE: [PoC] pg_upgrade: allow to upgrade publisher node

2023-08-03 Thread Hayato Kuroda (Fujitsu)
Dear Amit, > I see your point related to WALAVAIL_REMOVED status of the slot but > did you test the scenario I have explained in my comment? Basically, I > want to know whether it can impact the user in some way. So, please > check whether the corresponding subscriptions will be allowed to drop.

Re: On disable_cost

2023-08-03 Thread Jian Guo
Hi hackers, I have write an initial patch to retire the disable_cost​ GUC, which labeled a flag on the Path struct instead of adding up a big cost which is hard to estimate. Though it involved in tons of plan changes in regression tests, I have tested on some simple test cases such as eagerly

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

2023-08-03 Thread Peter Smith
Just to clarify my previous post, I meant we will need new v26* patches v24-0001 -> not needed because v25-0001 pushed v24-0002 -> v26-0001 v24-0003 -> v26-0002 On Thu, Aug 3, 2023 at 6:19 PM Peter Smith wrote: > > Hi Melih, > > Now that v25-0001 has been pushed, can you please rebase the

Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

2023-08-03 Thread Alvaro Herrera
On 2023-Aug-03, tender wang wrote: > I think old "sub-FK" should not be dropped, that will be violates foreign > key constraint. Yeah, I've been playing more with the patch and it is definitely not doing the right things. Just eyeballing the contents of pg_trigger and pg_constraint for

Re: Missing comments/docs about custom scan path

2023-08-03 Thread Etsuro Fujita
On Mon, Jul 31, 2023 at 7:05 PM Etsuro Fujita wrote: > While working on [1], I noticed $SUBJECT: commit e7cb7ee14 failed to > update comments for the CustomPath struct in pathnodes.h, and commit > f49842d1e failed to update docs about custom scan path callbacks in > custom-scan.sgml, IIUC.

Re: Adding a LogicalRepWorker type field

2023-08-03 Thread Ashutosh Bapat
On Wed, Aug 2, 2023 at 12:14 PM Peter Smith wrote: > > We can't use the same names for both with/without-parameter functions > because there is no function overloading in C. In patch v3-0001 I've > replaced the "dual set of macros", with a single inline function of a > different name, and one

Adding a pg_servername() function

2023-08-03 Thread Laetitia Avrot
Dear Hackers, One of my customers suggested creating a function that could return the server's hostname. After a quick search, we found [this Wiki page]( https://wiki.postgresql.org/wiki/Pg_gethostname) referring to [that extension](https://github.com/theory/pg-hostname/) from David E. Wheeler.

Re: Inquiry about Functionality Availability in PostgreSQL

2023-08-03 Thread Ashutosh Bapat
Hi Sultan, Thanks for your email and interest in PostgreSQL. On Wed, Aug 2, 2023 at 8:41 AM Sultan Berentaev wrote: > > > Dear PostgreSQL Development Team, > > I am inquiring about the availability of certain functionalities in the standard PostgreSQL database. Could you please confirm if the

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

2023-08-03 Thread Peter Smith
Hi Melih, Now that v25-0001 has been pushed, can you please rebase the remaining patches? -- Kind Regards, Peter Smith. Fujitsu Australia

Re: Extract numeric filed in JSONB more effectively

2023-08-03 Thread Andy Fan
Hi Pavel: Thanks for the feedback. I don't like this solution because it is bloating operators and it is not > extra readable. > If we support it with cast, could we say we are bloating CAST? It is true that it is not extra readable, if so how about a->>'a' return text? Actually I can't

Re: Improve const use in zlib-using code

2023-08-03 Thread Peter Eisentraut
On 02.08.23 16:39, Tristan Partin wrote: I like the idea. Though the way you have it implemented at the moment seems like a trap in that any time zlib.h is included someone also has to remember to add this define. I would recommend adding the define to the build systems instead. Ok, moved to

Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

2023-08-03 Thread tender wang
I think old "sub-FK" should not be dropped, that will be violates foreign key constraint. For example : postgres=# insert into r values(1,1); INSERT 0 1 postgres=# ALTER TABLE r DETACH PARTITION r_1; ALTER TABLE postgres=# delete from p_1 where id = 1; DELETE 1 postgres=# select * from r_1; id |

Re: Faster "SET search_path"

2023-08-03 Thread Jeff Davis
On Tue, 2023-08-01 at 21:52 -0700, Nathan Bossart wrote: > I > typically see this done with two ѕeparate lists and forboth(). Agreed, done. > > Any reason not to use hash_combine() here? Thank you, fixed. > > I changed it to move the hook so that it's called after retrieving > > from > > the

Re: Fix incorrect start up costs for WindowAgg paths (bug #17862)

2023-08-03 Thread Andy Fan
Hi David: Sorry for feedback at the last minute! I study the patch and find the following cases. 1. ORDER BY or PARTITION BY select *, count(two) over (order by unique1) from tenk1 limit 1; DEBUG: startup_tuples = 1 DEBUG: startup_tuples = 1 select *, count(two) over (partition by unique1)

Re: [PoC] pg_upgrade: allow to upgrade publisher node

2023-08-03 Thread Amit Kapila
On Wed, Aug 2, 2023 at 1:43 PM Hayato Kuroda (Fujitsu) wrote: > > Thank you for giving comments! PSA new version patchset. > > > 3. > > + /* > > + * Get replication slots. > > + * > > + * XXX: Which information must be extracted from old node? Currently three > > + * attributes are extracted

Re: ALTER COLUMN ... SET EXPRESSION to alter stored generated column's expression

2023-08-03 Thread jian he
On Thu, Aug 3, 2023 at 1:23 PM Amul Sul wrote: > > > That is not expected & acceptable. But, somehow, I am not able to reproduce > this behavior. Could you please retry this experiment by adding "table_schema" > in your output query? > > Thank you. > > Regards, > Amul > sorry. my mistake. I

Re: Exposing the lock manager's WaitForLockers() to SQL

2023-08-03 Thread Will Mortensen
Updated docs a bit. I'll see about adding this to the next CF in hopes of attracting a reviewer. :-) v3-0001-Add-WAIT-ONLY-option-to-LOCK-command.patch Description: Binary data

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

2023-08-03 Thread Amit Kapila
On Thu, Aug 3, 2023 at 9:35 AM Peter Smith wrote: > > On Wed, Aug 2, 2023 at 11:19 PM Amit Kapila wrote: > > > > On Wed, Aug 2, 2023 at 4:09 PM Melih Mutlu wrote: > > > > > > PFA an updated version with some of the earlier reviews addressed. > > > Forgot to include them in the previous email. >

Re: [PoC] Reducing planning time when tables have many partitions

2023-08-03 Thread Yuya Watari
Hello, On Wed, Aug 2, 2023 at 6:43 PM Andrey Lepikhov wrote: > You introduced list_ptr_cmp as an extern function of a List, but use it > the only under USE_ASSERT_CHECKING ifdef. > Maybe you hide it under USE_ASSERT_CHECKING or remove all the stuff? Thank you for your quick reply and for