Re: apply_scanjoin_target_to_paths and partitionwise join

On Thu, Apr 11, 2024 at 12:07 PM Ashutosh Bapat < ashutosh.bapat@gmail.com> wrote: > Hi All, > Per below code and comment in apply_scanjoin_target_to_paths(), the > function zaps all the paths of a partitioned relation. > /* > * If the rel is partitioned, we want to drop its existing paths and

Re: Incorrect handling of IS [NOT] NULL quals on inheritance parents

On Thu, Apr 11, 2024 at 10:23 AM David Rowley wrote: > On Wed, 10 Apr 2024 at 19:12, Richard Guo wrote: > > And I think recording NOT NULL columns for traditional inheritance > > parents can be error-prone for some future optimization where we look > > at an inheritance parent's notnullattnums a

Re: Can't find not null constraint, but \d+ shows that

On Wed, Apr 10, 2024 at 2:10 PM jian he wrote: > > DROP TABLE if exists notnull_tbl2; > CREATE TABLE notnull_tbl2 (c0 int generated by default as IDENTITY, c1 int); > ALTER TABLE notnull_tbl2 ADD CONSTRAINT Q PRIMARY KEY(c0, c1); > ALTER TABLE notnull_tbl2 DROP CONSTRAINT notnull_tbl2_c0_not_null;

apply_scanjoin_target_to_paths and partitionwise join

Hi All, Per below code and comment in apply_scanjoin_target_to_paths(), the function zaps all the paths of a partitioned relation. /* * If the rel is partitioned, we want to drop its existing paths and * generate new ones. This function would still be correct if we kept the * existing paths: we'd

Re: Issue with the PRNG used by Postgres

> On 10 Apr 2024, at 21:48, Parag Paul wrote: > > Yes, the probability of this happening is astronomical, but in production > with 128 core servers with 7000 max_connections, with petabyte scale data, > this did repro 2 times in the last month. We had to move to a local approach > to manage

Re: Synchronizing slots from primary to standby

On Wed, Apr 10, 2024 at 5:28 PM Zhijie Hou (Fujitsu) wrote: > > On Thursday, April 4, 2024 5:37 PM Amit Kapila > wrote: > > > > BTW, while thinking on this one, I > > noticed that in the function LogicalConfirmReceivedLocation(), we first > > update > > the disk copy, see comment [1] and then i

Re: DROP DATABASE is interruptible

On Tue, Mar 12, 2024 at 9:00 PM Alexander Lakhin wrote: > I see two backends waiting: > law 2420132 2420108 0 09:05 ?00:00:00 postgres: node: law > postgres [local] DROP DATABASE waiting > law 2420135 2420108 0 09:05 ?00:00:00 postgres: node: law > postgres [local] st

Re: sql/json remaining issue

On Wed, Apr 10, 2024 at 4:39 PM Amit Langote wrote: > > > Attached is a bit more polished version of that, which also addresses > the error messages in JsonPathQuery() and JsonPathValue(). I noticed > that there was comment I had written at one point during JSON_TABLE() > hacking that said that w

Re: Improve eviction algorithm in ReorderBuffer

On Thu, Apr 11, 2024 at 10:32 AM Masahiko Sawada wrote: > > Hi, > > Sorry for the late reply, I took two days off. > > On Thu, Apr 11, 2024 at 6:20 AM Heikki Linnakangas wrote: > > > > On 10/04/2024 08:31, Amit Kapila wrote: > > > On Wed, Apr 10, 2024 at 11:00 AM Heikki Linnakangas > > > wrote:

Re: Incorrect handling of IS [NOT] NULL quals on inheritance parents

On Wed, 10 Apr 2024 at 19:12, Richard Guo wrote: > And I think recording NOT NULL columns for traditional inheritance > parents can be error-prone for some future optimization where we look > at an inheritance parent's notnullattnums and make decisions based on > the assumption that the included c

Re: Can't find not null constraint, but \d+ shows that

Alvaro Herrera 于2024年4月10日周三 21:58写道： > It turns out that trying to close all holes that lead to columns marked > not-null without a pg_constraint row is not possible within the ALTER > TABLE framework, because it can happen outside it also. Consider this > > CREATE DOMAIN dom1 AS integer; > CRE

Re: Requiring LLVM 14+ in PostgreSQL 18

On Wed, Apr 10, 2024 at 1:38 PM Thomas Munro wrote: > Therefore, some time after the tree re-opens for hacking, we could rip > out a bunch of support code for LLVM 10-13, and then rip out support > for pre-opaque-pointer mode. Please see attached. ... or of course closer to the end of the cycle

RE: Slow catchup of 2PC (twophase) transactions on replica in LR

Dear Amit, > One naive idea is that on the publisher we can remember whether the > prepare has been sent and if so then only send commit_prepared, > otherwise send the entire transaction. On the subscriber-side, we > somehow, need to ensure before applying the first change whether the > correspond

Re: Potential stack overflow in incremental base backup

On Thu, Apr 11, 2024 at 12:11 AM Robert Haas wrote: > On Wed, Apr 10, 2024 at 6:21 AM Thomas Munro wrote: > > Could we just write the blocks directly into the output array, and > > then transpose them directly in place if start_blkno > 0? See > > attached. I may be missing something, but the on

Re: Issue with the PRNG used by Postgres

I wrote: > I'm not worried about it being slower, but about whether it could > report "stuck spinlock" in cases where the existing code succeeds. On fourth thought ... the number of tries to acquire the lock, or in this case number of tries to observe the lock free, is not NUM_DELAYS but NUM_DELAY

Re: post-freeze damage control

On 4/11/24 10:23, Tom Kincaid wrote: The extensive Beta process we have can be used to build confidence we need in a feature that has extensive review and currently has no known issues or outstanding objections. I did have objections, here [1] and here [2]. I think the complexity, space req

RE: Improve eviction algorithm in ReorderBuffer

Dear Heikki, I also prototyped the idea, which has almost the same shape. I attached just in case, but we may not have to see. Few comments based on the experiment. ``` + /* txn_heap is ordered by transaction size */ + buffer->txn_heap = pairingheap_allocate(ReorderBufferTXNSizeCompa

pg_combinebackup does not detect missing files

Hackers, I've been playing around with the incremental backup feature trying to get a sense of how it can be practically used. One of the first things I always try is to delete random files and see what happens. You can delete pretty much anything you want from the most recent incremental ba

Re: SET ROLE documentation improvement

On Tue, Apr 09, 2024 at 09:21:39AM +0300, Andrey M. Borodin wrote: > Can I ask you please to help me with determining status of CF item > [0]. Is it committed or there's something to move to next CF? Only half of the patch has been applied as of 3330a8d1b792. Yurii and Nathan, could you follow up

Re: Improve eviction algorithm in ReorderBuffer

Hi, Sorry for the late reply, I took two days off. On Thu, Apr 11, 2024 at 6:20 AM Heikki Linnakangas wrote: > > On 10/04/2024 08:31, Amit Kapila wrote: > > On Wed, Apr 10, 2024 at 11:00 AM Heikki Linnakangas wrote: > >> > >> On 10/04/2024 07:45, Michael Paquier wrote: > >>> On Tue, Apr 09, 202

Re: Issue with the PRNG used by Postgres

Robert Haas writes: > I just want to mention that I have heard of "stuck spinlock" happening > in production just because the server was busy. And I think that's not > intended. The timeout is supposed to be high enough that you only hit > it if there's a bug in the code. At least AIUI. But it isn

Re: CI and test improvements

On Mon, Apr 08, 2024 at 05:54:10PM +0300, Andrey M. Borodin wrote: > Justin, Peter, I can't determine actual status of the CF entry > [0]. May I ask someone of you to move patch to next CF or close as > committed? 0002 is the only thing committed as of 21a71648d39f. I can see the value in 0001, b

Re: Improve WALRead() to suck data directly from WAL buffers when possible

On Tue, Apr 09, 2024 at 09:33:49AM +0300, Andrey M. Borodin wrote: > As far as I understand CF entry [0] is committed? I understand that > there are some open followups, but I just want to determine correct > CF item status... So much work has happened on this thread with things that has been com

Re: Issue with the PRNG used by Postgres

On Wed, Apr 10, 2024 at 4:40 PM Tom Lane wrote: > I'm not worried about it being slower, but about whether it could > report "stuck spinlock" in cases where the existing code succeeds. > While that seems at least theoretically possible, it seems like > if you hit it you have got problems that need

Re: Is this a problem in GenericXLogFinish()?

On Wed, Apr 10, 2024 at 03:28:22PM +0530, Amit Kapila wrote: > I can understand this comment as I am aware of this code but not sure > it would be equally easy for the people first time looking at this > code. One may try to find the equivalent assertion in > _hash_freeovflpage(). The alternative c

Re: post-freeze damage control

> > > Yeah, that's an excellent practive, but is why I'm less worried for > > this feature. The docs at [1] caution about "not to remove earlier > > backups if they might be needed when restoring later incremental > > backups". Like Alvaro said, should we insist a bit more about the WAL > > reten

Re: Add notes to pg_combinebackup docs

On 4/9/24 19:44, Tomas Vondra wrote: On 4/9/24 09:59, Martín Marqués wrote: Hello, While doing some work/research on the new incremental backup feature some limitations were not listed in the docs. Mainly the fact that pg_combienbackup works with plain format and not tar. Right. The docs mo

Re: post-freeze damage control

On 4/10/24 09:50, Michael Paquier wrote: On Wed, Apr 10, 2024 at 09:29:38AM +1000, David Steele wrote: Even so, only keeping WAL for the last backup is a dangerous move in any case. Lots of things can happen to a backup (other than bugs in the software) so keeping WAL back to the last full (o

Re: Improve eviction algorithm in ReorderBuffer

On 11/04/2024 01:37, Michael Paquier wrote: On Thu, Apr 11, 2024 at 12:20:55AM +0300, Heikki Linnakangas wrote: To move this forward, here's a patch to switch to a pairing heap. In my very quick testing, with the performance test cases posted earlier in this thread [1] [2], I'm seeing no meaning

Re: [HACKERS] make async slave to wait for lsn to be replayed

On 07/04/2024 00:52, Alexander Korotkov wrote: On Fri, Apr 5, 2024 at 9:15 PM Alvaro Herrera wrote: I'm still concerned that WaitLSNCleanup is only called in ProcKill. Does this mean that if a process throws an error while waiting, it'll not get cleaned up until it exits? Maybe this is not a b

Re: Cutting support for OpenSSL 1.0.1 and 1.0.2 in 17~?

On Wed, Apr 10, 2024 at 09:31:16AM +0200, Peter Eisentraut wrote: > I think it might be better to separate this into two steps: > > 1. Move to 1.1.0. This is an API update. Change OPENSSL_API_COMPAT, and > remove a bunch of code that no longer needs to be conditional. We could > check for a rep

Re: Improve eviction algorithm in ReorderBuffer

On Thu, Apr 11, 2024 at 12:20:55AM +0300, Heikki Linnakangas wrote: > To move this forward, here's a patch to switch to a pairing heap. In my very > quick testing, with the performance test cases posted earlier in this thread > [1] [2], I'm seeing no meaningful performance difference between this a

Re: Allow non-superuser to cancel superuser tasks.

On Wed, Apr 10, 2024 at 10:00:34AM -0500, Nathan Bossart wrote: > Isn't it relatively easy to discover this same information today via > pg_stat_progress_vacuum? That has the following code: > > /* Value available to all callers */ > values[0] = Int32GetDatum(beentry->

Re: recovery modules

rebased -- Nathan Bossart Amazon Web Services: https://aws.amazon.com >From 5897631d5f09032565d92d5b8547baf3d24eef87 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 15 Feb 2023 14:28:53 -0800 Subject: [PATCH v21 1/5] introduce routine for checking mutually exclusive string GUCs --- s

Re: psql: Greatly speed up "\d tablename" when not using regexes

Jelte Fennema-Nio writes: > On Wed, 10 Apr 2024 at 22:11, Tom Lane wrote: >> If there's a case you can demonstrate where "\d foo" doesn't optimize >> into an indexscan, we should look into exactly why that's happening, >> because I think the cause must be more subtle than this. > Hmm, okay so I

Re: psql: Greatly speed up "\d tablename" when not using regexes

On Wed, 10 Apr 2024 at 22:11, Tom Lane wrote: > There may be an argument for psql to do what you suggest, > but so far it seems like duplicative complication. > > If there's a case you can demonstrate where "\d foo" doesn't optimize > into an indexscan, we should look into exactly why that's happe

Re: Fix possible dereference null pointer (src/backend/replication/logical/reorderbuffer.c)

On 10/04/2024 21:07, Ranier Vilela wrote: Hi, Per Coverity. The function ReorderBufferTXNByXid, can return NULL when the parameter *create* is false. In the functions ReorderBufferSetBaseSnapshot and ReorderBufferXidHasBaseSnapshot, the second call to ReorderBufferTXNByXid, pass false to *crea

allow changing autovacuum_max_workers without restarting

I frequently hear about scenarios where users with thousands upon thousands of tables realize that autovacuum is struggling to keep up. When they inevitably go to bump up autovacuum_max_workers, they discover that it requires a server restart (i.e., downtime) to take effect, causing further frustr

Re: Table AM Interface Enhancements

Hi, On 2024-04-10 16:50:44 -0400, Melanie Plageman wrote: > This brings up a question about the prefetching. We never had to have > this discussion for sequential scan streaming read because it didn't > (and still doesn't) do prefetching. But, if we push the streaming read > code down into the hea

Re: Improve eviction algorithm in ReorderBuffer

On 10/04/2024 08:31, Amit Kapila wrote: On Wed, Apr 10, 2024 at 11:00 AM Heikki Linnakangas wrote: On 10/04/2024 07:45, Michael Paquier wrote: On Tue, Apr 09, 2024 at 09:16:53PM -0700, Jeff Davis wrote: On Wed, 2024-04-10 at 12:13 +0900, Michael Paquier wrote: Wouldn't the best way forward

Re: Table AM Interface Enhancements

On Wed, Apr 10, 2024 at 4:33 PM Andres Freund wrote: > > Hi, > > On 2024-04-10 16:24:40 -0400, Melanie Plageman wrote: > > This thread has been moving pretty fast, so could someone point out > > which version of the patch has the modifications to > > acquire_sample_rows() that would be relevant fo

Re: ❓ JSON Path Dot Precedence

On Apr 10, 2024, at 10:29, Peter Eisentraut wrote: > So the whole thing is > > > > The syntax of and is then punted to > ECMAScript 5.1. > > 0x2 is a HexIntegerLiteral. (There can be no dots in that.) > > p10 is an Identifier. > > So I think this is all correct. That makes sense, tha

Re: Issue with the PRNG used by Postgres

Andres Freund writes: > On 2024-04-10 16:05:21 -0400, Tom Lane wrote: >> Yeah. So what's the conclusion? Leave it alone? Commit to >> HEAD only? > I think we should certainly fix it. I don't really have an opinion about > backpatching, it's just on the line between the two for me. > Hm. The ne

Re: Table AM Interface Enhancements

Hi, On 2024-04-10 16:24:40 -0400, Melanie Plageman wrote: > This thread has been moving pretty fast, so could someone point out > which version of the patch has the modifications to > acquire_sample_rows() that would be relevant for Bilal (and others > involved in analyze streaming read) to review

Re: Issue with the PRNG used by Postgres

Hi, On 2024-04-10 16:05:21 -0400, Tom Lane wrote: > Andres Freund writes: > > I think it could exascerbate the issue. Parag reported ~7k connections on a > > 128 core machine. The buffer replacement logic in < 16 tries to lock the old > > and new lock partitions at once. That can lead to quite ba

Re: Table AM Interface Enhancements

On Wed, Apr 10, 2024 at 4:03 PM Andres Freund wrote: > > Hi, > > On 2024-04-10 15:19:47 +0300, Alexander Korotkov wrote: > > On Mon, Apr 8, 2024 at 9:54 PM Robert Haas wrote: > > > On Mon, Apr 8, 2024 at 12:33 PM Alexander Korotkov > > > wrote: > > > > Yes, it was my mistake. I got rushing tryi

Re: broken JIT support on Fedora 40

Hi, On 2024-04-10 22:15:27 +0200, Dmitry Dolgov wrote: > > On Wed, Apr 10, 2024 at 12:43:23PM +1200, Thomas Munro wrote: > > On Tue, Apr 9, 2024 at 10:05 PM Dmitry Dolgov <9erthali...@gmail.com> wrote: > > > + /* In assertion builds, run the LLVM verify pass. */ > > > +#ifdef

Re: broken JIT support on Fedora 40

> On Wed, Apr 10, 2024 at 12:43:23PM +1200, Thomas Munro wrote: > On Tue, Apr 9, 2024 at 10:05 PM Dmitry Dolgov <9erthali...@gmail.com> wrote: > > + /* In assertion builds, run the LLVM verify pass. */ > > +#ifdef USE_ASSERT_CHECKING > > + LLVMPassBuilderOptionsS

Re: psql: Greatly speed up "\d tablename" when not using regexes

Jelte Fennema-Nio writes: > On Wed, 10 Apr 2024 at 20:06, Tom Lane wrote: >> Really? ISTM this argument is ignoring an optimization the backend >> has understood for a long time. > Interesting. I didn't know about that optimization. I can't check > right now, but probably the COLLATE breaks tha

Re: Issue with the PRNG used by Postgres

Andres Freund writes: > I think it could exascerbate the issue. Parag reported ~7k connections on a > 128 core machine. The buffer replacement logic in < 16 tries to lock the old > and new lock partitions at once. That can lead to quite bad "chains" of > dependent lwlocks, occasionally putting all

Re: Table AM Interface Enhancements

Hi, On 2024-04-10 15:19:47 +0300, Alexander Korotkov wrote: > On Mon, Apr 8, 2024 at 9:54 PM Robert Haas wrote: > > On Mon, Apr 8, 2024 at 12:33 PM Alexander Korotkov > > wrote: > > > Yes, it was my mistake. I got rushing trying to fit this to FF, even > > > doing significant changes just befo

Re: Table AM Interface Enhancements

On Wed, Apr 10, 2024 at 05:42:51PM +0400, Pavel Borisov wrote: > Hi, Alexander! > In my view, the actual list of what has raised discussion is: > dd1f6b0c17 Provide a way block-level table AMs could re-use > acquire_sample_rows > () > 27bc1772fc Generalize relation analyze in table AM interface >

Re: Issue with the PRNG used by Postgres

Hi, On 2024-04-10 14:02:20 -0400, Tom Lane wrote: > On third thought ... while I still think this is a misuse of > perform_spin_delay and we should change it, I'm not sure it'll do > anything to address Parag's problem, because IIUC he's seeing actual > "stuck spinlock" reports. That implies that

Re: broken JIT support on Fedora 40

st 10. 4. 2024 v 2:44 odesílatel Thomas Munro napsal: > On Tue, Apr 9, 2024 at 10:05 PM Dmitry Dolgov <9erthali...@gmail.com> > wrote: > > + /* In assertion builds, run the LLVM verify pass. */ > > +#ifdef USE_ASSERT_CHECKING > > + LLVMPassBuilderOptionsSetVeri

Re: psql: Greatly speed up "\d tablename" when not using regexes

On Wed, 10 Apr 2024, 23:37 Jelte Fennema-Nio, wrote: > On Wed, 10 Apr 2024 at 20:21, Kirill Reshke > wrote: > > Do we need to force Collaction here like in other branches? > > if (PQserverVersion(conn) >= 12) > >appendPQExpBufferStr(buf, " COLLATE pg_catalog.default"); > > According to t

Re: psql: Greatly speed up "\d tablename" when not using regexes

On Wed, 10 Apr 2024 at 20:21, Kirill Reshke wrote: > Do we need to force Collaction here like in other branches? > if (PQserverVersion(conn) >= 12) >appendPQExpBufferStr(buf, " COLLATE pg_catalog.default"); According to the commit and codecomment that introduced the COLLATE, it was specif

Re: Fix resource leak (src/backend/libpq/be-secure-common.c)

> On 10 Apr 2024, at 20:31, Ranier Vilela wrote: > > Em ter., 2 de abr. de 2024 às 15:31, Daniel Gustafsson > escreveu: >> > On 2 Apr 2024, at 20:13, Ranier Vilela > > > wrote: >> >> > Fix by freeing the pointer, like pclose_check (src/common/

Re: psql: Greatly speed up "\d tablename" when not using regexes

On Wed, 10 Apr 2024 at 20:06, Tom Lane wrote: > Really? ISTM this argument is ignoring an optimization the backend > has understood for a long time. Interesting. I didn't know about that optimization. I can't check right now, but probably the COLLATE breaks that optimization.

Re: Fix resource leak (src/backend/libpq/be-secure-common.c)

Em ter., 2 de abr. de 2024 às 15:31, Daniel Gustafsson escreveu: > > On 2 Apr 2024, at 20:13, Ranier Vilela wrote: > > > Fix by freeing the pointer, like pclose_check (src/common/exec.c) > similar case. > > Off the cuff, seems reasonable when loglevel is LOG. > Per Coverity. Another case of re

Re: psql: Greatly speed up "\d tablename" when not using regexes

Hi > Regex matching is obviously unnecessary when we're looking for an exact > match. This checks for this (common) case and starts using plain > equality in that case. +1 > + appendPQExpBuffer(buf, "(%s OPERATOR(pg_catalog.=) ", namevar); > + appendStringLiteralConn(buf, &namebuf.data[2], conn

Re: Issue with the PRNG used by Postgres

Robert Haas writes: > The blog post to which Parag linked includes this histogram as an > example of a low-Hamming-weight situation: That's an interesting post indeed, but I'm not sure how relevant it is to us, because it is about Xoshiro not Xoroshiro, and the latter is what we use. The last se

Re: Detoasting optionally to make Explain-Analyze less misleading

First of all thanks for bringing this Feature to PostgreSQL. From a regular-user perspective (not everyone is a Pro) it is very misleading that ANALYZE doesn't do what it suggests it does. To run the query into some kind of /dev/null type of destination is feasible and that is what people end up d

Re: Table AM Interface Enhancements

On Wed, Apr 10, 2024 at 1:25 PM Robert Haas wrote: > That is somewhat fair, but it is also a lot of work. There are > multiple people asking for you to revert things on multiple threads, > and figuring out all of the revert requests and trying to come to some > consensus about what should be done

Fix possible dereference null pointer (src/backend/replication/logical/reorderbuffer.c)

Hi, Per Coverity. The function ReorderBufferTXNByXid, can return NULL when the parameter *create* is false. In the functions ReorderBufferSetBaseSnapshot and ReorderBufferXidHasBaseSnapshot, the second call to ReorderBufferTXNByXid, pass false to *create* argument. In the function ReorderBuffer

Re: psql: Greatly speed up "\d tablename" when not using regexes

Jelte Fennema-Nio writes: > Running "\d tablename" from psql could take multiple seconds when > running on a system with 100k+ tables. The reason for this was that > a sequence scan on pg_class takes place, due to regex matching being > used. > Regex matching is obviously unnecessary when we're l

Re: Issue with the PRNG used by Postgres

Andres Freund writes: > On 2024-04-10 13:03:05 -0400, Tom Lane wrote: >> So I think we need something like the attached. > LGTM. On third thought ... while I still think this is a misuse of perform_spin_delay and we should change it, I'm not sure it'll do anything to address Parag's problem, bec

Re: Issue with the PRNG used by Postgres

Hi, On 2024-04-10 13:03:05 -0400, Tom Lane wrote: > After thinking about this some more, it is fairly clear that that *is* > a mistake that can cause a thundering-herd problem. > Assume we have two or more backends waiting in perform_spin_delay, and for > whatever reason the scheduler wakes them

Re: psql: Greatly speed up "\d tablename" when not using regexes

Patch looks good to me. Great idea overall, that forced regex has always bugged me. + char *regexChars = "|*+?()[]{}.^$\\"; One super minor optimization is that we technically do not need to scan for ')' and ']'. If they appear without their partner, the query will fail anyway. :) Ch

Re: Table AM Interface Enhancements

On Wed, Apr 10, 2024 at 12:36 PM Alexander Korotkov wrote: > But I have to mention that even that I've committed table AM stuff > close to the FF, there has been quite amount of depended work > committed. So, revert of these patches is promising to be not > something immediate and easy, which req

Re: Can't find not null constraint, but \d+ shows that

On 2024-Apr-10, Alvaro Herrera wrote: > One thing missing here is pg_dump support. If you just dump this table, > it'll end up with no constraint at all. That's obviously bad, so I > propose we have pg_dump add a regular NOT NULL constraint for those, to > avoid perpetuating the weird situation

Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

Hi! Alexander Korotkov, thanks for the commit of previous fix. Alexander Lakhin, thanks for the problem you found. There are two corrections attached to the letter: 1) v1-0001-Fix-for-SPLIT-MERGE-partitions-of-temporary-table.patch - fix for the problem [1]. 2) v1-0002-Fixes-for-english-text

Re: Issue with the PRNG used by Postgres

Andres Freund writes: > Hi, > On 2024-04-10 12:28:10 -0400, Tom Lane wrote: >> I don't think it's correct to re-initialize the SpinDelayStatus each >> time around the outer loop. That state should persist through the >> entire acquire operation, as it does in a regular spinlock acquire. >> As thi

Re: Improve eviction algorithm in ReorderBuffer

On Wed, 2024-04-10 at 08:30 +0300, Heikki Linnakangas wrote: > My #1 choice would be to write a patch to switch the pairing heap, > performance test that, and revert the binary heap changes. Sounds good to me. I would expect it to perform better than the extra hash table, if anything. It also ha

Re: Issue with the PRNG used by Postgres

Parag Paul writes: > Yes, the probability of this happening is astronomical, but in production > with 128 core servers with 7000 max_connections, with petabyte scale data, > this did repro 2 times in the last month. We had to move to a local > approach to manager our ratelimiting counters. > This

Re: Issue with the PRNG used by Postgres

Hi, On 2024-04-10 12:28:10 -0400, Tom Lane wrote: > Actually ... Parag mentioned that this was specifically about > lwlock.c's usage of spinlocks. It doesn't really use a spinlock, > but it does use s_lock.c's delay logic, and I think it's got the > usage pattern wrong: > > while (true) >

Re: Issue with the PRNG used by Postgres

Hi, On 2024-04-10 09:48:42 -0700, Parag Paul wrote: > Yes, the probability of this happening is astronomical, but in production > with 128 core servers with 7000 max_connections, with petabyte scale data, > this did repro 2 times in the last month. We had to move to a local > approach to manager o

Re: Issue with the PRNG used by Postgres

I wrote: > I don't think it's correct to re-initialize the SpinDelayStatus each > time around the outer loop. That state should persist through the > entire acquire operation, as it does in a regular spinlock acquire. > As this stands, it resets the delay to minimum each time around the > outer lo

Re: Table AM Interface Enhancements

Hi, On 2024-04-08 14:54:46 -0400, Robert Haas wrote: > Exactly how much is getting reverted here? I see these, all since March 23rd: IMO: > dd1f6b0c17 Provide a way block-level table AMs could re-use > acquire_sample_rows() Should be reverted. > 9bd99f4c26 Custom reloptions for table AM Hm.

Re: Issue with the PRNG used by Postgres

Yes, the probability of this happening is astronomical, but in production with 128 core servers with 7000 max_connections, with petabyte scale data, this did repro 2 times in the last month. We had to move to a local approach to manager our ratelimiting counters. This is not reproducible very easil

Re: Issue with the PRNG used by Postgres

On Wed, Apr 10, 2024 at 12:40 PM Parag Paul wrote: > The reason why this could be a problem is a flaw in the RNG with the enlarged > Hamming belt. > I attached an image here, with the RNG outputs from 2 backends. I ran our > code for weeks, and collected ther > values generated by the RNG over m

Re: Issue with the PRNG used by Postgres

hi Robert, We are using xoroshiro128 and not moved to the next state of art. We did see a lot of low values as put in my last message. -Parag On Wed, Apr 10, 2024 at 9:37 AM Robert Haas wrote: > On Wed, Apr 10, 2024 at 12:02 PM Tom Lane wrote: > > As I said to Parag, I see exactly no reason to

Re: Issue with the PRNG used by Postgres

On Wed, Apr 10, 2024 at 12:02 PM Tom Lane wrote: > As I said to Parag, I see exactly no reason to believe that that's a > problem, unless it happens *a lot*, like hundreds of times in a row. > If it does, that's an RNG problem not s_lock's fault. Now, I'm not > going to say that xoroshiro can't p

Re: Table AM Interface Enhancements

On Wed, Apr 10, 2024 at 4:19 PM Robert Haas wrote: > > On Wed, Apr 10, 2024 at 8:20 AM Alexander Korotkov > wrote: > > I agree with the facts. But I have a different interpretation on > > this. The patch was committed as 11470f544e on March 23, 2023, then > > reverted on April 3. I've propose

Re: Issue with the PRNG used by Postgres

Actually ... Parag mentioned that this was specifically about lwlock.c's usage of spinlocks. It doesn't really use a spinlock, but it does use s_lock.c's delay logic, and I think it's got the usage pattern wrong: while (true) { /* always try once to acquire lock directly */

Re: Issue with the PRNG used by Postgres

Hi, On 2024-04-10 07:55:16 -0700, Parag Paul wrote: > This is a little bit more complex than that. The spinlocks are taken in the > LWLock(Mutex) code, when the lock is not available right away. > The spinlock is taken to attach the current backend to the wait list of the > LWLock. This means, tha

Re: Issue with the PRNG used by Postgres

Robert Haas writes: > Oh, yeah ... right. But then why does the comment say that it's > increasing the delay between a random fraction between 1X and 2X? I think the comment is meant to say that the new delay value will be 1X to 2X the old value. If you want to suggest different phrasing, feel f

Re: Issue with the PRNG used by Postgres

Parag Paul writes: > So, if RNG generated 0.001 and cur_delay =1000. > Result will be > 1000 + int(1000*0.01 + 5) = (int)(1000 + (0.1+.5)) = (int)1000.6 = 1000 > <-- back to the same value Yes, with a sufficiently small RNG result, the sleep delay will not increase that time through the

Re: Issue with the PRNG used by Postgres

On Wed, Apr 10, 2024 at 11:09 AM Tom Lane wrote: > No, I think you are misreading it, because the assignment is += not =. > The present coding is > > /* increase delay by a random fraction between 1X and 2X */ > status->cur_delay += (int) (status->cur_delay * >

psql: Greatly speed up "\d tablename" when not using regexes

Running "\d tablename" from psql could take multiple seconds when running on a system with 100k+ tables. The reason for this was that a sequence scan on pg_class takes place, due to regex matching being used. Regex matching is obviously unnecessary when we're looking for an exact match. This check

Re: Speed up clean meson builds by ~25%

Thomas Munro writes: > On Wed, Apr 10, 2024 at 5:03 PM Tom Lane wrote: >> I don't doubt that there are other clang versions where the problem >> bites a lot harder. What result do you get from the test I tried >> (turning mm_strdup into a no-op macro)? > #define mm_strdup(x) (x) does this: > Ap

Re: Issue with the PRNG used by Postgres

hi Tom, First of all thanks for you response. I did not misread it. The 0.5 is added to the result of the multiplication which then uses C integer casting, which does not round off, but just drops the decimal portion. status->cur_delay += (int) (status->cur_delay *

Re: [HACKERS] make async slave to wait for lsn to be replayed

I did some experiments over synchronous replications and got that cascade replication can`t be synchronous. And  pg_wal_replay_wait() allows us to read your writes consistency on cascade replication. Beyond that, I added more tests on multi-standby replication and cascade replications. -- Ivan Ka

Re: Issue with the PRNG used by Postgres

Robert Haas writes: > I'm not convinced that we should try to improve the RNG, but surely we > need to put parentheses around pg_prng_double(&pg_global_prng_state) + > 0.5. IIUC, the current logic is making us multiply the spin delay by a > value between 0 and 1 when what was intended was that it

Re: Can't find not null constraint, but \d+ shows that

On Wed, Apr 10, 2024 at 7:01 PM Alvaro Herrera wrote: > > On 2024-Apr-10, jian he wrote: > > > another related bug, in master. > > > > drop table if exists notnull_tbl1; > > CREATE TABLE notnull_tbl1 (c0 int not null, c1 int); > > ALTER TABLE notnull_tbl1 ADD CONSTRAINT Q PRIMARY KEY(c0, c1); > >

Re: Allow non-superuser to cancel superuser tasks.

On Wed, Apr 10, 2024 at 07:58:39AM +0900, Michael Paquier wrote: > On Wed, Apr 10, 2024 at 12:52:19AM +0300, Kirill Reshke wrote: >> On Tue, 9 Apr 2024 at 08:53, Michael Paquier wrote: >>> The thing is that you cannot rely on a lookup of the backend type for >>> the error information, or you open

Re: Issue with the PRNG used by Postgres

Thank you Robert. I am in the process of patching this. -Parag On Wed, Apr 10, 2024 at 7:43 AM Robert Haas wrote: > On Tue, Apr 9, 2024 at 5:05 PM Andres Freund wrote: > > ISTM that the fix here is to not use a spinlock for whatever the > contention is > > on, rather than improve the RNG. > > I

Re: Issue with the PRNG used by Postgres

Hi Andres, This is a little bit more complex than that. The spinlocks are taken in the LWLock(Mutex) code, when the lock is not available right away. The spinlock is taken to attach the current backend to the wait list of the LWLock. This means, that this cannot be controlled. The repro when it hap

Re: Issue with the PRNG used by Postgres

On Tue, Apr 9, 2024 at 5:05 PM Andres Freund wrote: > ISTM that the fix here is to not use a spinlock for whatever the contention is > on, rather than improve the RNG. I'm not convinced that we should try to improve the RNG, but surely we need to put parentheses around pg_prng_double(&pg_global_p

Re: PostgreSQL 17 Release Management Team & Feature Freeze

On Mon, 8 Apr 2024 at 16:26, Robert Haas wrote: > And maybe we need to think of a way to further mitigate this crush of > last minute commits. e.g. In the last week, you can't have more > feature commits, or more lines of insertions in your commits, than you > did in the prior 3 weeks combined. I

Re: ❓ JSON Path Dot Precedence

On 07.04.24 18:13, David E. Wheeler wrote: Hello Hackers, A question about the behavior of the JSON Path parser. The docs[1] have this to say about numbers: Numeric literals in SQL/JSON path expressions follow JavaScript rules, which are different from both SQL and JSON in some minor detai

  1   2   >