Re: [Question] Window Function Results without ORDER BY Clause
Hi, David G. Johnston" writes: > On Friday, July 11, 2025, Zhang Mingli wrote: >> So, are both result sets technically correct given the absence of an ORDER >> BY clause? > The system is behaving within the requirements of the specification. The > query itself is bugged code that the query author should fix. Well, it's our own regression-test query. I think the actual question being asked here is "do our regression tests need to pass under random non-default GUC settings?". I'd say no; it'd be next door to impossible to guarantee that. If this query gave unstable results in practice, we'd have noticed by now (it's been there since 2010). regards, tom lane Would like to know .. 1. Any particular reason why ORDER BY clause was ignored/removed from windows function 2. if by applying the ORDER BY clause on windows function, were the regression test results become deterministic. Thanks in advance Dinesh From: Tom Lane Sent: Friday, July 11, 2025 9:27 PM To: David G. Johnston Cc: Zhang Mingli ; PostgreSQL Hackers Subject: Re: [Question] Window Function Results without ORDER BY Clause Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments. "David G. Johnston" writes: > On Friday, July 11, 2025, Zhang Mingli wrote: >> So, are both result sets technically correct given the absence of an ORDER >> BY clause? > The system is behaving within the requirements of the specification. The > query itself is bugged code that the query author should fix. Well, it's our own regression-test query. I think the actual question being asked here is "do our regression tests need to pass under random non-default GUC settings?". I'd say no; it'd be next door to impossible to guarantee that. If this query gave unstable results in practice, we'd have noticed by now (it's been there since 2010). regards, tom lane
Re: A concurrent VACUUM FULL?
Hi Eric, Currently the first suggested approach "Rebuild indexes on the temporary heap in TX1 and then swap in the new indexes along with the new heap in TX2." sound good. It would be great if we are able to perform concurrent reads and writes. In OLTP environments will it lead to slowing of the queries or query performance issues Thanks Dinesh Nair From: Erik Nordström Sent: Monday, June 30, 2025 3:19 PM To: PostgreSQL Hackers Subject: A concurrent VACUUM FULL? You don't often get email from e...@timescale.com. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification> Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments. Hi hackers, I've been looking at the code for CLUSTER/VACUUM FULL, and whether it is possible to do a concurrent version of it using a multi-transactional approach similar to concurrent reindexing and partition detach. The idea would be to hold weaker locks in TX1 when doing the heap rewrite (essentially allow reads but prevent writes), and then do the actual heap swap in a second TX2 transaction. Having experimented a bit with this approach, I found that reindexing is an issue because that happens after the new heap has been swapped in. The current reindex during a heap swap effectively blocks reads so if one starts a new transaction after swapping heaps, it will block reads for a long time. This made me think about two ways to handle this: 1. Rebuild indexes on the temporary heap in TX1 and then swap in the new indexes along with the new heap in TX2. 2. Do a concurrent index rebuild after the heap swap. Of the two approaches above, (2) seems easiest to implement, but the downside is that indexes would be invalid while indexes are rebuilt. Therefore, (1) seems to be the more desirable one because all the heavy lifting would be done in TX1 on the temporary heap. Does anyone have a sense of whether approach (1) is feasible or whether there are any major blockers? Is this worth pursuing at all or am I missing something? Best regards, Erik -- Database Architect, Timescale
Re: Composite types for updatable views
Hi , Yes, i agree with the statement below , a column is updatable if it is a simple reference to an updatable column of the underlying base relation . View updatability for composite types (being derived construct) will pose challenges Possible temporary work around is: 1. Avoid composite types in views and use individual columns from base table 2. using a trigger function to update the view for some use cases Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments. While simple views are automatically updatable [1], this does not extend to composite types; that is, if a view constructs a composite type for one or more columns, this column then is not updatable: > A column is updatable if it is a simple reference to an updatable column of > the underlying base relation It would seem simple enough to support composite types if the view column is constructed using row expression in which each field is "a simple reference to an updatable column". As a practical example, this could enable geometric types being exposed at the view layer, but supported in an underlying table by primitive types. [1] https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS Thanks Dinesh Nair From: Malthe Sent: Wednesday, July 16, 2025 5:36 PM To: Postgres hackers Subject: Composite types for updatable views You don't often get email from mbo...@gmail.com. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification> Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments. While simple views are automatically updatable [1], this does not extend to composite types; that is, if a view constructs a composite type for one or more columns, this column then is not updatable: > A column is updatable if it is a simple reference to an updatable column of > the underlying base relation It would seem simple enough to support composite types if the view column is constructed using row expression in which each field is "a simple reference to an updatable column". As a practical example, this could enable geometric types being exposed at the view layer, but supported in an underlying table by primitive types. [1] https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS
Re: Support getrandom() for pg_strong_random() source
Hi , On Tue, Jul 22, 2025 at 4:12 AM Dagfinn Ilmari Mannsåker wrote: > > Masahiko Sawada writes: > > > On Tue, Jul 22, 2025 at 12:13 AM Michael Paquier > > wrote: > >> > >> On Mon, Jul 21, 2025 at 11:43:35PM -0700, Masahiko Sawada wrote: > >> > The patch supports the getrandom() function as a new source of > >> > pg_strong_random(). The getrandom() function uses the same source as > >> > the /dev/urandom device but it seems much faster than opening, > >> > reading, and closing /dev/urandom. Here is the execution time of > >> > generating 1 million UUIDv4 data measured on my environment: > >> > > >> > HEAD(/dev/urandom): 1863.064 ms > >> > Patched(getrandom()): 516.627 ms > >> > >> Interesting. Are there platforms where this is not available? I'd be > >> pretty sure that some animals in the buildfarm would not like this > >> suggestion but I'm saying it anyway. Perhaps we could even drop > >> /dev/urandom? > > > > As far as I know macOS doesn't support getrandom() but supports > > getentropy() instead. And an older glibc version might not support it. > > It's supported since Linux 3.17 and glibc 2.25. > > getrandom() is Linux-specific, while getentropy() is specified by POSIX > (since 2024). It was originally introduced by OpenBSD 5.6 in 2014, and > was added to macOS 10.12 in 2016, glibc 2.25 (same as getrandom()) in > 2017, musl 1.1.20 and FreeBSD 12.0 in 2018, and NetBSD 10.0 in 2024 > > Sources: >While getentropy() has better portability, according to the >getentropy() manual, the maximum length is limited to 256 bytes. It >works in some cases such as generating UUID data but seems not > appropriate for our general pg_strong_random() use cases. The getentropy() function has a limitation of generating a maximum of 256 bytes of entropy per call and is not supported on Windows platforms. For cryptographic operations that require large buffers of high-quality randomness efficiently, it's not recommended to use getentropy(). https://brandur.org/fragments/secure-bytes-without-pgcrypto A few secure, random bytes without `pgcrypto` — brandur.org<https://brandur.org/fragments/secure-bytes-without-pgcrypto> In Postgres it’s common to see the SQL random() function used to generate a random number, but it’s a pseudo-random number generator, and not suitable for cases where real randomness is required critical. Postgres also provides a way of getting secure random numbers as well, but only through the use of the pgcrypto extension, which makes gen_random_bytes available. Pulling pgcrypto into ... brandur.org Thanks Regards Dinesh Nair From: Masahiko Sawada Sent: Wednesday, July 23, 2025 12:02 AM To: Dagfinn Ilmari Mannsåker Cc: Michael Paquier ; PostgreSQL Hackers Subject: Re: Support getrandom() for pg_strong_random() source Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments. On Tue, Jul 22, 2025 at 4:12 AM Dagfinn Ilmari Mannsåker wrote: > > Masahiko Sawada writes: > > > On Tue, Jul 22, 2025 at 12:13 AM Michael Paquier > > wrote: > >> > >> On Mon, Jul 21, 2025 at 11:43:35PM -0700, Masahiko Sawada wrote: > >> > The patch supports the getrandom() function as a new source of > >> > pg_strong_random(). The getrandom() function uses the same source as > >> > the /dev/urandom device but it seems much faster than opening, > >> > reading, and closing /dev/urandom. Here is the execution time of > >> > generating 1 million UUIDv4 data measured on my environment: > >> > > >> > HEAD(/dev/urandom): 1863.064 ms > >> > Patched(getrandom()): 516.627 ms > >> > >> Interesting. Are there platforms where this is not available? I'd be > >> pretty sure that some animals in the buildfarm would not like this > >> suggestion but I'm saying it anyway. Perhaps we could even drop > >> /dev/urandom? > > > > As far as I know macOS doesn't support getrandom() but supports > > getentropy() instead. And an older glibc version might not support it. > > It's supported since Linux 3.17 and glibc 2.25. > > getrandom() is Linux-specific, while getentropy() is specified by POSIX > (since 2024). It was originally introduced by OpenBSD 5.6 in 2014, and > was added to macOS 10.12 in 2016, glibc 2.25 (same as getrandom()) in > 2017, musl 1.1.20 and FreeBSD 12.0 in 2018, and NetBSD 10.0 in 2024 > > Sources: > > https://ind01.safelinks.protection.outlook.com/?url=h
Re: Aggregate Function corr does not always return the correct value
Hi, Try casting it to numeric or use an expression that avoids floating-point rounding off : WITH dataset AS ( SELECT x, CAST(0.125 AS numeric) AS y FROM generate_series(0, 5) AS x ) SELECT corr(x, y) FROM dataset; Thanks & Regards Dinesh Nair From: Tom Lane Sent: Tuesday, August 26, 2025 11:04 PM To: Maxim Orlov Cc: Postgres hackers Subject: Re: Aggregate Function corr does not always return the correct value Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments. Maxim Orlov writes: > One of the clients complained as to why the query for calculating the > correlation coefficient with the CORR function yielded such weird > results. After a little analysis, it was discovered that they were > calculating the correlation coefficient for two sets, one of which is > more or less random and the other of which is simply a set of constant > values (0.09 if that matters). As a result, they were attaining > unexpected results. However, as far as I am aware, they should have > received NULL because it is impossible to calculate the standard > deviation for such a set. [ shrug... ] Calculations with float8 are inherently inexact, so it's unsurprising that we sometimes fail to detect that the input is exactly a horizontal or vertical line. I don't think there is anything to be done here that wouldn't end in making things worse. regards, tom lane
