Re: should we have a fast-path planning for OLTP starjoins?

On Mon, Feb 10, 2025 at 9:32 AM Tomas Vondra wrote: > E.g. imagine we have a join of 8 relations, with F (fact), dimensions D1 > and D2, and then some artibrary tables T1, T2, T3, T4, T5. And let's say > deconstruct_recurse() sees them in this particular order > > [F, T1, T2, D1, D2, T3, T4, T

Re: SQL Property Graph Queries (SQL/PGQ)

On Thu, Feb 6, 2025 at 8:22 PM Junwang Zhao wrote: > > > > > > > I see you have added some negative tests - object not found tests, but > > I didn't see positive tests. Hence I haven't added those changes in > > the attached patchset. But we certainly need those changes. You may > > want to submit

Re: Introduce XID age and inactive timeout based replication slot invalidation

On Fri, Feb 7, 2025 at 4:53 PM Amit Kapila wrote: > > On Fri, Feb 7, 2025 at 8:00 AM Peter Smith wrote: > > > > == > > src/backend/access/transam/xlog.c > > > > 1. > > XLByteToSeg(RedoRecPtr, _logSegNo, wal_segment_size); > > KeepLogSeg(recptr, &_logSegNo); > > - if (InvalidateObsoleteRep

Re: Introduce XID age and inactive timeout based replication slot invalidation

On Sat, Feb 8, 2025 at 12:28 PM Zhijie Hou (Fujitsu) wrote: > > 3. > > + if (cause & RS_INVAL_HORIZON) > + { > + if (!SlotIsLogical(s)) > + goto invalidation_marked; > > I am not sure if

Re: Introduce XID age and inactive timeout based replication slot invalidation

Hi Nisha, Some review comments for v72-0001. == GENERAL My preference was to just keep the enum as per v70 for the *actual* cause, and introduce a separate set of bit flags for *possible* causes to be checked. This creates a clear code separation between the actual and possible. It also elim

Re: Removing unneeded self joins

On 9/2/2025 18:41, Alexander Korotkov wrote: Regarding adjust_relid_set() and replace_relid(). I think they are now strictly equivalent, except for the case then old relid is given and not found. In this case adjust_relid_set() returns the original relids while replace_relid() returns a copy.

Re: Virtual generated columns

On Feb 10, 2025 at 12:53 +0800, jian he , wrote: > > please check attached. > > > BTW, I was curious about what happens if the replacement expression is > > constant, so I tried running the query below. > > > > CREATE TABLE t (a int, b int GENERATED ALWAYS AS (1 + 1)); > > INSERT INTO t VALUES (1);

Re: Conflict detection for update_deleted in logical replication

On Fri, Feb 7, 2025 at 11:17 AM Amit Kapila wrote: > > On Fri, Feb 7, 2025 at 2:18 AM Masahiko Sawada wrote: > > > > I'd like to confirm what users would expect of this > > max_conflict_retention_duration option and it works as expected. IIUC > > users would want to use this option when they want

Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints

Thanks, Alvaro, for the review. I have addressed your comments per the above suggestions in the attached v4 patch. -- Thanks & Regards, Suraj kharage, enterprisedb.com On Wed, Feb 5, 2025 at 12:11 AM Alvaro Herrera wrote: > On 2025-Jan-13, Suraj Kharage wro

Re: Virtual generated columns

On Mon, Feb 10, 2025 at 11:54 AM Richard Guo wrote: > > On Sun, Feb 9, 2025 at 7:02 PM Zhang Mingli wrote: > > On Feb 9, 2025 at 16:00 +0800, Alexander Lakhin , > > wrote: > > Please look at a planner error with a virtual generated column triggered > > by the following script: > > CREATE TABLE t

Re: Showing applied extended statistics in explain Part 2

Hi All, I've organized the discussion so far and improved the patch. The issues and their status are below. * Issues and status (or comment): I've numbered them for ease of management. T6. Changed option to show extended statistics (from VERBOSE to STATS) -> Done already on the previous

Re: Virtual generated columns

On Sun, Feb 9, 2025 at 7:02 PM Zhang Mingli wrote: > On Feb 9, 2025 at 16:00 +0800, Alexander Lakhin , wrote: > Please look at a planner error with a virtual generated column triggered > by the following script: > CREATE TABLE t(a int, b int GENERATED ALWAYS AS (a * 1)); > > SELECT SUM(CASE WHEN t

Re: Trying to build x86 version on windows using meson

On Mon, Jul 22, 2024 at 12:20 AM Thomas Munro wrote: > On Thu, Mar 21, 2024 at 11:21 AM Andres Freund wrote: > > Obviously tests can't run in that environment, but building should be > > possible. I can e.g. build postgres for x86-64 windows on my linux machine, > > but can't run the tests (in th

Uncached buffered IO

Hi Hackers. https://lwn.net/Articles/998783/ , is Uncached buffered IO useful for double cache?PostgreSQL's double caching is often a source of complaints. This new Linux kernel feature seems to help mitigate performance jitters caused by file cache reclaim when memory free memory is too small.

Re: Statistics Import and Export

> > > I would recommend to work a bit more the tests by updating the stats > of a relation with the SQL functions in a transaction and add some > queries on pg_locks for locktype = 'relation' that are able to check > the locks we are taking when running these operations (return pairs of > relation:

Re: Enhance 'pg_createsubscriber' to retrieve databases automatically when no database is provided.

Some review comments for v4-0001. == Commit message 1. This patch enhances the 'pg_createsubscriber' utility by adding the '--all-databases' option, which automatically fetches all non-template databases on the source server (publisher) and creates corresponding subscriptions on the target se

Re: should we have a fast-path planning for OLTP starjoins?

On 2/5/25 09:27, Richard Guo wrote: > On Wed, Feb 5, 2025 at 5:55 AM Tom Lane wrote: >> Right now, if we have four tables to join, we have a joinlist >> (A B C D). (Really they're integer relids, but let's use names here.) >> If we decide to force C to be joined last, it should be sufficient to >

RE: Enhance 'pg_createsubscriber' to retrieve databases automatically when no database is provided.

Dear Shubham, > Fixed the given comments. The attached patch at [1] contains the > suggested changes. Thanks for updates. I registered the thread to the commitfest entry [1]. Few comments. 01. fetch_source_databases ``` + const char *query = "SELECT datname FROM pg_database WHERE datiste

Re: [PATCH] Optionally record Plan IDs to track plan changes for a query

On Thu, Feb 06, 2025 at 07:52:53PM -0600, Sami Imseih wrote: > This fixes the long comments in plannodes.h to make it easier to add the > attribute annotation. It made the most sense to make this the first patch > in the set. A commit that happened last Friday made also this to have conflict. > D

Re: Fix punctuation errors in PostgreSQL documentation

On Saturday, February 8, 2025, 斉藤登 wrote: > > I found some issues in the original English documentation that I > believe need to be corrected. > I concur on all of them. David J.

Re: describe special values in GUC descriptions more consistently

On Sat, Feb 8, 2025 at 9:27 AM Nathan Bossart wrote: > > For many GUCs, special values like -1, "", etc. have some sort of special > meaning, such as disabling the feature. While the documentation seems to > be reasonably good about listing special values, the GUC descriptions are > less consiste

Re: MAX_BACKENDS size (comment accuracy)

> Halfing the size of LWLock and laying > the ground work for making the wait-list lock-free imo would be very well > worth the reduction in an unrealistic limit... BTW, I spent a week or two researching the lock-free queue idea, specifically looking at what it might look like to have a Michael-Sc

Re: UUID v7

On Wednesday 5 February 2025 at 01:07:02 am GMT+3, Masahiko Sawada wrote: On Sun, Feb 2, 2025 at 11:41 AM Sergey Prokhorenko wrote: > > Dear colleagues, > > I would like to present for discussion my attached new draft documentation on > UUID functions (Section 9.14. UUID Functions), which r

Re: add support for the old naming libs convention on windows (ssleay32.lib and libeay32.lib)

On Fri, Feb 7, 2025 at 3:14 PM Daniel Gustafsson wrote: > > On 9 Jan 2025, at 13:50, Daniel Gustafsson wrote: > > > >> On 7 Jan 2025, at 19:25, Darek Ślusarczyk > wrote: > > > >> Thanks for reviewing the patch - I've added a few changes and it looks > as follows: > > > > Thanks for the new vers

Re: Confusing variable naming in LWLockRelease

> I believe it refers to the state of the lock prior to lock acquisition; not prior to subtraction. That definitely makes sense as a way to read this variable in context, but after reviewing other usages of old_state in lwlock.c, I tend to think that this is an outlier usage and maybe the naming w

Re: RFC: Allow EXPLAIN to Output Page Fault Information

On Sun, 9 Feb 2025 at 19:05, Tom Lane wrote: > > Andres Freund writes: > > I'm somewhat against this patch, as it's fairly fundamentally incompatible > > with AIO. There's no real way to get information in this manner if the IO > > isn't executed synchronously in process context... Hmm, I had no

Re: Replace IN VALUES with ANY in WHERE clauses during optimization

Hi! Thank you for your review! On 09.02.2025 18:38, Alexander Korotkov wrote: On Sun, Feb 9, 2025 at 1:58 PM Alexander Korotkov wrote: On Thu, Jan 9, 2025 at 3:11 PM Alena Rybakina wrote: On 04.10.2024 12:05, Andrei Lepikhov wrote: We also have an implementation of VALUES -> ARRAY transform

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

On Fri, Feb 7, 2025 at 4:44 PM Alvaro Herrera wrote: > > Recursion to child tables is incorrectly trying to locate the constraint > by name: > > create table notnull_tbl1 (a int); > alter table notnull_tbl1 add constraint foo not null a not valid; > create table notnull_chld (a int); > alter tabl

Re: RFC: Allow EXPLAIN to Output Page Fault Information

Andres Freund writes: > I'm somewhat against this patch, as it's fairly fundamentally incompatible > with AIO. There's no real way to get information in this manner if the IO > isn't executed synchronously in process context... Even without looking ahead to AIO, there's bgwriter, walwriter, and c

Re: RFC: Allow EXPLAIN to Output Page Fault Information

Hi, On 2025-02-09 12:51:40 +0100, Jelte Fennema-Nio wrote: > On Sat, 8 Feb 2025 at 14:54, Jelte Fennema-Nio wrote: > > I'll play around with it a bit next week. > > Okay, I played around with it and couldn't find any issues. I marked > the patch as "ready for committer" in the commitfest app[1],

Re: UUID v7

I've took into account note from Sergey that "offset" is better name for uuidv7() argument than "shift". > On 5 Feb 2025, at 03:02, Masahiko Sawada wrote: > >> >> I was thinking about incorporating test like this. >> With this patch we can generate correct UUIDs in a very distant future.

Re: Replace IN VALUES with ANY in WHERE clauses during optimization

On Sun, Feb 9, 2025 at 1:58 PM Alexander Korotkov wrote: > > On Thu, Jan 9, 2025 at 3:11 PM Alena Rybakina > wrote: > > On 04.10.2024 12:05, Andrei Lepikhov wrote: > > > We also have an implementation of VALUES -> ARRAY transformation. > > > Because enterprises must deal with users' problems, ma

Fix outdated code comments in nodeAgg.c

Hi, By reading the codes in nodeAgg.c, I found that some comments are outdated. The comments referred to the function lookup_hash_entry(), which has been removed in commit b563594. I've adjusted the comments to refer to the correct function to make it clear. -- Zhang Mingli HashData v0-0001-f

Re: pull-up subquery if JOIN-ON contains refs to upper-query

Hi! I found another example where the transformation worked incorrectly and reconsidered the idea. As for conversion of exists_sublink_to_ANY, we need to get the flattened implicit-AND list of clauses and pull out the chunks of the WHERE clause that belong to the parent query, since we are cal

Re: Thread-safe nl_langinfo() and localeconv()

This patch set is still desirable. Here is a rebased version of the v5 patches. I haven't applied any corrections or review comments. From 94879f695ff8961255c2daa46b81ce378a55732d Mon Sep 17 00:00:00 2001 From: Thomas Munro Date: Tue, 13 Aug 2024 14:15:54 +1200 Subject: [PATCH v6 1/3] Provide t

Re: BitmapHeapScan streaming read user and prelim refactoring

On 1/30/25 21:36, Melanie Plageman wrote: > > ... > > Attached v28 is rebased and has a few updates/cleanup. All patches in > the set need review and I need to do some benchmarking of v28-0003. > > - Melanie Hi, I've been re-running the benchmarks on v28 on Melanie's request. The tests are stil

Re: Proposal to CREATE FOREIGN TABLE LIKE

On Feb 8, 2025 at 12:55 +0800, Sami Imseih , wrote: > > Fix CI failure of doc build in v1 patch. > > > > Thanks for the patch! I am +1 for this, but I have a few comments: Hi, tanks for review. > > > > 1/ In the IDENTITY case, the remote side may not be > > able to handle the DEFAULT value. Yes, an

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

Thanks Alvaro. On Thu, Feb 6, 2025 at 9:58 PM Alvaro Herrera wrote: > Hello Rushabh, > > On 2025-Feb-06, Rushabh Lathia wrote: > > > Commit 14e87ffa5c543b5f30ead7413084c25f7735039f > > < > https://github.com/postgres/postgres/commit/14e87ffa5c543b5f30ead7413084c25f7735039f > > > > added the supp

Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE

On Fri, 13 Dec 2024 at 16:59, Michael Christofides wrote: > > > I've pushed the main patch. > > Woohoo! And thank you. I've already seen quite a lot of positivity around the > commit on Twitter[1][2][3]. I wanted to highlight this other patch[1] by Atsushi Torikoshi, which adds "actual filesyste

Re: Replace IN VALUES with ANY in WHERE clauses during optimization

Hi, Alena! On Thu, Jan 9, 2025 at 3:11 PM Alena Rybakina wrote: > On 04.10.2024 12:05, Andrei Lepikhov wrote: > > We also have an implementation of VALUES -> ARRAY transformation. > > Because enterprises must deal with users' problems, many of these > > users employ automatically generated querie

Re: RFC: Allow EXPLAIN to Output Page Fault Information

On Sat, 8 Feb 2025 at 14:54, Jelte Fennema-Nio wrote: > I'll play around with it a bit next week. Okay, I played around with it and couldn't find any issues. I marked the patch as "ready for committer" in the commitfest app[1], given that all feedback in my previous email was very minor. [1]: ht

Re: Virtual generated columns

On Feb 9, 2025 at 16:00 +0800, Alexander Lakhin , wrote: > > Please look at a planner error with a virtual generated column triggered > by the following script: > CREATE TABLE t(a int, b int GENERATED ALWAYS AS (a * 1)); > > SELECT SUM(CASE WHEN t.b = 1 THEN 1 ELSE 1 END) OVER (PARTITION BY t.a) >

Re: Things I don't like about \du's "Attributes" column

Hello, After reading thread "new commitfest transition guidance"[0], and as patch author, I decided to write short summary for this committfest entry[1]. Last patch has version 8, example output and code are here[2]. Proposed approach to \du command has not found positive responses for several m

Re: Improve CRC32C performance on SSE4.2

On Thu, Feb 6, 2025 at 3:49 AM Devulapalli, Raghuveer wrote: > > This patch improves the performance of SSE42 CRC32C algorithm. The current > SSE4.2 implementation of CRC32C relies on the native crc32 instruction and > processes 8 bytes at a time in a loop. The technique in this paper uses the

Re: Virtual generated columns

Hello Peter, 07.02.2025 14:34, Peter Eisentraut wrote: I've committed it.  Thanks. Please look at a planner error with a virtual generated column triggered by the following script: CREATE TABLE t(a int, b int GENERATED ALWAYS AS (a * 1)); SELECT SUM(CASE WHEN t.b = 1 THEN 1 ELSE 1 END) OVER (