Re: Intermittent failure with t/003_logical_slots.pl test on windows

Hi, Apart of these I am getting following some intermittent failure as below: 131/272 postgresql:pg_basebackup / pg_basebackup/010_pg_basebackup ERROR30.51s (exit status 255 or signal 127 SIGinvalid) 114/272 postgresql:libpq / libpq/001_uri

Re: Functions to return random numbers in a given range

On Fri, Dec 22, 2023 at 1:07 AM Dean Rasheed wrote: > > Attached is a patch that adds 3 SQL-callable functions to return > random integer/numeric values chosen uniformly from a given range: > > random(min int, max int) returns int > random(min bigint, max bigint) returns bigint > random(min

Re: change regexp_substr first argument make tests more easier to understand.

On Thu, Dec 28, 2023 at 12:13 AM jian he wrote: > > hi. > https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/strings.out#n928 > > SELECT regexp_substr('abcabcabc', 'a.c'); > SELECT regexp_substr('abcabcabc', 'a.c', 2); > SELECT regexp_substr('abcabcabc', 'a.c', 1, 3); >

Re: doc patch: note AttributeRelationId passed to FDW validator function

On Thu, Dec 28, 2023 at 01:55:27PM +0900, Ian Lawrence Barwick wrote: > 2023年6月7日(水) 9:08 Ian Lawrence Barwick : >> The alternative v2 patch adds this to this list of OIDs, and also >> formats it as an >> SGML list, which IMHO is easier to read. >> >> Looks like this has been missing since 9.3. >

Re: warn if GUC set to an invalid shared library

On Thu, Dec 28, 2023 at 10:54 AM Justin Pryzby wrote: > > On Sat, Oct 29, 2022 at 10:40 AM Justin Pryzby wrote: > > > > > On Fri, Sep 02, 2022 at 05:24:58PM -0500, Justin Pryzby wrote: > > > > > > It caused no issue when I changed: > > > > > > > > > > > > /* Check that

Re: doc patch: note AttributeRelationId passed to FDW validator function

2023年6月7日(水) 9:08 Ian Lawrence Barwick : > > Hi > > Here: > > https://www.postgresql.org/docs/current/fdw-functions.html > > the enumeration of OIDs which might be passed as the FDW validator function's > second argument omits "AttributeRelationId" (as passed when altering > a foreign table's

Re: Track in pg_replication_slots the reason why slots conflict?

On Wed, Dec 27, 2023 at 4:16 PM Amit Kapila wrote: > > On Wed, Dec 27, 2023 at 3:08 PM shveta malik wrote: > > > > PFA the patch which attempts to implement this. > > > > This patch changes the existing 'conflicting' field to > > 'conflicting_cause' in pg_replication_slots. > > > > This name

Re: POC PATCH: copy from ... exceptions to: (was Re: VLDB Features)

On Wed, Dec 20, 2023 at 8:27 PM Masahiko Sawada wrote: > > > Why do we need to use SPI? I think we can form heap tuples and insert > them to the error table. Creating the error table also doesn't need to > use SPI. > Thanks for pointing it out. I figured out how to form heap tuples and insert

Re: add function argument names to regex* functions.

On Thu, Dec 28, 2023 at 6:25 AM Peter Eisentraut wrote: > > On 27.12.23 17:53, jian he wrote: > > similar to [1], add function argument names to the following functions: > > regexp_like, regexp_match,regexp_matches,regexp_replace, > >

Re: Statistics Import and Export

On Wed, Dec 27, 2023 at 09:41:31PM -0500, Corey Huinker wrote: > When I thought about the ability to dump/load statistics in the past, I > usually envisioned some sort of DDL that would do the export and import. > So for example we'd have EXPORT STATISTICS / IMPORT STATISTICS commands,

Re: Statistics Import and Export

Corey Huinker writes: > Export functions was my original plan, for simplicity, maintenance, etc, > but it seemed like I'd be adding quite a few functions, so the one view > made more sense for an initial version. Also, I knew that pg_dump or some > other stats exporter would have to inline the

RE: Random pg_upgrade test failure on drongo

Dear Alexander, > I agree with your analysis and would like to propose a PoC fix (see > attached). With this patch applied, 20 iterations succeeded for me. There are no reviewers so that I will review again. Let's move the PoC to the concrete patch. Note that I only focused on fixes of random

Re: Support "Right Semi Join" plan shapes

Hi Richard Guo I did a simple test ,Subqueries of type (in) can be supported, There is a test sql that doesn't support it, and I think that's because it can't pull up the subqueries. ``` test=# explain (costs off) SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE

Re: Statistics Import and Export

> > Yeah, this is pretty much what I meant by "functional" interface. But if > I said maybe the format implemented by the patch is maybe too close to > how we store the statistics, then this has exactly the same issue. And > it has other issues too, I think - it breaks down the stats into >

Re: Statistics Import and Export

> > > As mentioned already, we'd also need some sort of > version identifier, and we'd expect the load_statistics() functions > to be able to transform the data if the old version used a different > representation. I agree with the idea that an explicit representation > of the source table

Re: Statistics Import and Export

On Mon, Dec 25, 2023 at 8:18 PM Tomas Vondra wrote: > Hi, > > I finally had time to look at the last version of the patch, so here's a > couple thoughts and questions in somewhat random order. Please take this > as a bit of a brainstorming and push back if you disagree some of my > comments. > >

Re: cannot abort transaction 2737414167, it was already committed

On Thu, Dec 28, 2023 at 11:33:16AM +1300, Thomas Munro wrote: > I guess the large object usage isn't directly relevant (that module's > EOXact stuff seems to be finished before TRANS_COMMIT, but I don't > know that code well). Everything later is supposed to be about > closing/releasing/cleaning

Re: cannot abort transaction 2737414167, it was already committed

Thomas Munro writes: > In CommitTransaction() there is a stretch of code beginning s->state = > TRANS_COMMIT and ending s->state = TRANS_DEFAULT, from which we call > out to various subsystems' AtEOXact_XXX() functions. There is no way > to roll back in that state, so anything that throws ERROR

Re: cannot abort transaction 2737414167, it was already committed

On Thu, Dec 28, 2023 at 4:02 AM Justin Pryzby wrote: > My main question is why an IO error would cause the DB to abort, rather > than raising an ERROR. In CommitTransaction() there is a stretch of code beginning s->state = TRANS_COMMIT and ending s->state = TRANS_DEFAULT, from which we call out

Re: add function argument names to regex* functions.

On 27.12.23 17:53, jian he wrote: similar to [1], add function argument names to the following functions: regexp_like, regexp_match,regexp_matches,regexp_replace, regexp_substr,regexp_split_to_array,regexp_split_to_table,regexp_count Note that these functions are a quasi-standard that is

Add LSN <-> time conversion functionality

Hi, Elsewhere [1] I required a way to estimate the time corresponding to a particular LSN in the past. I devised the attached LSNTimeline, a data structure mapping LSNs <-> timestamps with decreasing precision for older time, LSN pairs. This can be used to locate and translate a particular time

Re: [HACKERS] Changing references of password encryption to hashing

On 27.12.23 02:04, Bruce Momjian wrote: I did_not_ change the user API, so CREATE/ALTER ROLE still uses [ENCRYPTED] PASSWORD, the GUC is still called password_encryption, and the libpq function is still called PQencryptPasswordConn(). This makes the user interface confusing since the API uses

Assorted typo fixes

Hi folks, I was playing around with the `typos` tool (https://github.com/crate-ci/typos), and thought I'd run it on the posgres repo for fun. After a bit of tweaking to get rid of most false positives (see separately attached .typos.toml file), it came up with a useful set of suggestions, some

Re: Password leakage avoidance

On 12/24/23 12:15 PM, Tom Lane wrote: Maybe we need a PQcreaterole that provide the mechanism to set passwords safely. It'd likely need to take all the options need for creating a role, but that would at least give the underlying mechanism to ensure we're always sending a hashed password to the

Re: Password leakage avoidance

On 12/27/23 16:09, Tom Lane wrote: Joe Conway writes: On 12/27/23 15:39, Peter Eisentraut wrote: On 23.12.23 16:13, Joe Conway wrote: The attached patch set moves the guts of \password from psql into the libpq client side -- PQchangePassword() (patch 0001). I don't follow how you get from

Re: Multidimensional Histograms

Hello Alexander, We actually had histograms in the early patches adding multivariate statistics [1]. We however ended up removing histograms and only kept the simpler types, for a couple reasons. It might be worth going through the discussion - I'm sure one of the reasons was we needed to limit

Re: Password leakage avoidance

On Wed, 27 Dec 2023 at 16:10, Tom Lane wrote: > Joe Conway writes: > > On 12/27/23 15:39, Peter Eisentraut wrote: > >> On 23.12.23 16:13, Joe Conway wrote: > >>> The attached patch set moves the guts of \password from psql into the > >>> libpq client side -- PQchangePassword() (patch 0001). > >

Re: Password leakage avoidance

Joe Conway writes: > On 12/27/23 15:39, Peter Eisentraut wrote: >> On 23.12.23 16:13, Joe Conway wrote: >>> The attached patch set moves the guts of \password from psql into the >>> libpq client side -- PQchangePassword() (patch 0001). >> I don't follow how you get from the problem statement to

Re: Password leakage avoidance

On 12/27/23 15:39, Peter Eisentraut wrote: On 23.12.23 16:13, Joe Conway wrote: I have recently, once again for the umpteenth time, been involved in discussions around (paraphrasing) "why does Postgres leak the passwords into the logs when they are changed". I know well that the canonical

Re: Password leakage avoidance

On 23.12.23 16:13, Joe Conway wrote: I have recently, once again for the umpteenth time, been involved in discussions around (paraphrasing) "why does Postgres leak the passwords into the logs when they are changed". I know well that the canonical advice is something like "use psql with

Re: introduce dynamic shared memory registry

Here is a new version of the patch. In addition to various small changes, I've rewritten the test suite to use an integer and a lock, added a dsm_registry_find() function, and adjusted autoprewarm to use the registry. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com >From

Re: Two small bugs in guc.c

On Tue Dec 26, 2023 at 1:02 PM CST, Tom Lane wrote: I investigated the report at [1] about pg_file_settings not reporting invalid values of "log_connections". It turns out it's broken for PGC_BACKEND and PGC_SU_BACKEND parameters, but not other ones. The cause is a bit of premature optimization

Re: Add support for __attribute__((returns_nonnull))

On Wed Dec 27, 2023 at 6:42 AM CST, Peter Eisentraut wrote: On 19.12.23 21:43, Tristan Partin wrote: > Here is a patch which adds support for the returns_nonnull attribute > alongside all the other attributes we optionally support. > > I recently wound up in a situation where I was checking for

Re: Should we remove -Wdeclaration-after-statement?

On Wed, 27 Dec 2023 at 16:05, Tom Lane wrote: > This has already been debated, and the conclusion was that we would > stick to the existing style for consistency reasons. I looked through the archives quite a bit, but I couldn't find any conclusive debate about the current declaration style.

Re: postgres_fdw fails to see that array type belongs to extension

David Geier writes: > The filter clause of the query SELECT * FROM test WHERE col = 'foo' OR > col = 'bar' is pushed down to the remote, while the filter clause of the > semantically equivalent query SELECT * FROM test WHERE col IN ('foo', > 'bar') is not. > I traced this down to

Re: [PATCH] pg_dump: Do not dump statistics for excluded tables

Rian McGuire writes: > I've attached a patch against master that addresses a small bug in pg_dump. > Previously, pg_dump would include CREATE STATISTICS statements for > tables that were excluded from the dump, causing reload to fail if any > excluded tables had extended statistics. I agree

add function argument names to regex* functions.

Hi. similar to [1], add function argument names to the following functions: regexp_like, regexp_match,regexp_matches,regexp_replace, regexp_substr,regexp_split_to_array,regexp_split_to_table,regexp_count so I call these function in a different notation[2], like: SELECT

postgres_fdw fails to see that array type belongs to extension

Hi hackers, We use postgres_fdw to connect two databases. Both DBs have an extension installed which provides a custom string data type. Our extension is known to the FDW as we created the foreign server with our extension listed in the "extensions" option. The filter clause of the query

Re: Emit fewer vacuum records by reaping removable tuples during pruning

On Sat, Dec 23, 2023 at 9:14 PM Michael Paquier wrote: > > On Mon, Nov 13, 2023 at 07:06:15PM -0500, Melanie Plageman wrote: > > As best I can tell, our best case scenario is Thomas' streaming read API > > goes in, we add vacuum as a user, and we can likely remove the skip > > range logic. > >

[PATCH] Exponential backoff for auth_delay

Hi, we had a conversation with a customer about security compliance a while ago and one thing they were concerned about was avoiding brute-force attemps for remote password guessing. This is should not be a big concern if reasonably secure passwords are used and increasing SCRAM iteration count

change regexp_substr first argument make tests more easier to understand.

hi. https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/strings.out#n928 SELECT regexp_substr('abcabcabc', 'a.c'); SELECT regexp_substr('abcabcabc', 'a.c', 2); SELECT regexp_substr('abcabcabc', 'a.c', 1, 3); SELECT regexp_substr('abcabcabc', 'a.c', 1, 4) IS NULL AS t;

Re: trying again to get incremental backup

On Wed, Dec 27, 2023 at 09:11:02AM -0500, Robert Haas wrote: > Thanks. I don't think there's a real bug, but I pushed a fix, same as > what you had. Thanks! I also noticed that WALSummarizerLock probably needs a mention in wait_event_names.txt. -- Nathan Bossart Amazon Web Services:

Re: Statistics Import and Export

On Wed, Dec 27, 2023 at 01:08:47PM +0100, Tomas Vondra wrote: > On 12/26/23 20:19, Tom Lane wrote: > > Bruce Momjian writes: > >> I think we need a robust API to handle two cases: > > > >> * changes in how we store statistics > >> * changes in how how data type values are represented in the

Re: pg_upgrade failing for 200+ million Large Objects

Robins Tharakan writes: > Applying all 4 patches, I also see good performance improvement. > With more Large Objects, although pg_dump improved significantly, > pg_restore is now comfortably an order of magnitude faster. Yeah. The key thing here is that pg_dump can only parallelize the data

Re: Should we remove -Wdeclaration-after-statement?

Jelte Fennema-Nio writes: > Postgres currently requires all variables to be declared at the top of > the function, because it specifies -Wdeclaration-after-statement. One > of the reasons that we had this warning was because C89 required this > style of declaration. Requiring it everywhere made

cannot abort transaction 2737414167, it was already committed

We had this: < 2023-12-25 04:06:20.062 MST telsasoft >ERROR: could not open file "pg_tblspc/16395/PG_16_202307071/16384/121010871": Input/output error < 2023-12-25 04:06:20.062 MST telsasoft >STATEMENT: commit < 2023-12-25 04:06:20.062 MST telsasoft >WARNING: AbortTransaction while in COMMIT

Re: trying again to get incremental backup

On Sat, Dec 23, 2023 at 4:51 PM Nathan Bossart wrote: > My compiler has the following complaint: > > ../postgresql/src/backend/postmaster/walsummarizer.c: In function > ‘GetOldestUnsummarizedLSN’: > ../postgresql/src/backend/postmaster/walsummarizer.c:540:32: error: > ‘unsummarized_lsn’ may be

Re: pg_stat_statements: more test coverage

On 27.12.23 09:08, Julien Rouhaud wrote: Hi, On Tue, Dec 26, 2023 at 10:03 PM Peter Eisentraut wrote: On 24.12.23 03:03, Michael Paquier wrote: - Use a DO block of a PL function, say with something like that to ensure an amount of N queries? Say with something like that after tweaking

Re: Add support for __attribute__((returns_nonnull))

On 19.12.23 21:43, Tristan Partin wrote: Here is a patch which adds support for the returns_nonnull attribute alongside all the other attributes we optionally support. I recently wound up in a situation where I was checking for NULL return values of a function that couldn't ever return NULL

Re: Bug in nbtree optimization to skip > operator comparisons (or < comparisons in backwards scans)

On Wed, Dec 27, 2023 at 1:18 PM Pavel Borisov wrote: > Thank you for revised comments! I think they are good enough. Pushed, thank you! -- Regards, Alexander Korotkov

Re: Failed assertion in joininfo.c, remove_join_clause_from_rels

On Wed, Dec 27, 2023 at 2:04 PM Richard Guo wrote: > On Wed, Dec 27, 2023 at 8:00 PM Alexander Korotkov > wrote: >> >> On Wed, Dec 27, 2023 at 1:54 PM Andreas Seltenreich >> wrote: >> > SQLsmith found a failing Assertion in joininfo.c on master. I can >> > reproduce it on an

Re: Statistics Import and Export

On 12/26/23 20:19, Tom Lane wrote: > Bruce Momjian writes: >> I think we need a robust API to handle two cases: > >> * changes in how we store statistics >> * changes in how how data type values are represented in the statistics > >> We have had such changes in the past, and I think these two

Re: Failed assertion in joininfo.c, remove_join_clause_from_rels

On Wed, Dec 27, 2023 at 8:00 PM Alexander Korotkov wrote: > On Wed, Dec 27, 2023 at 1:54 PM Andreas Seltenreich > wrote: > > SQLsmith found a failing Assertion in joininfo.c on master. I can > > reproduce it on an assertion-enabled build like this: > > > > create table t1(a int primary key); >

Re: Failed assertion in joininfo.c, remove_join_clause_from_rels

On Wed, Dec 27, 2023 at 1:54 PM Andreas Seltenreich wrote: > SQLsmith found a failing Assertion in joininfo.c on master. I can > reproduce it on an assertion-enabled build like this: > > create table t1(a int primary key); > create table t2(a int); > > select * from t2 right join > (t1

Re: Removing const-false IS NULL quals and redundant IS NOT NULL quals

On Wed, Dec 27, 2023 at 7:38 PM Andy Fan wrote: > I also want to add notnullattnums for the UniqueKey stuff as well, by > comparing your implementation with mine, I found you didn't consider > the NOT NULL generated by filter. After apply your patch: > > create table a(a int); > explain (costs

Failed assertion in joininfo.c, remove_join_clause_from_rels

Hi, SQLsmith found a failing Assertion in joininfo.c on master. I can reproduce it on an assertion-enabled build like this: create table t1(a int primary key); create table t2(a int); select * from t2 right join (t1 as t1a inner join t1 as t1b on t1a.a = t1b.a) on t1a.a is not

Should we remove -Wdeclaration-after-statement?

Postgres currently requires all variables to be declared at the top of the function, because it specifies -Wdeclaration-after-statement. One of the reasons that we had this warning was because C89 required this style of declaration. Requiring it everywhere made backporting easier, since some of

Re: Removing const-false IS NULL quals and redundant IS NOT NULL quals

Hi, David Rowley writes: > > Happy to hear other people's thoughts on this patch. Otherwise, I > currently don't think the missed optimisation is a reason to block > what we've ended up with so far. > > David > > [1] https://postgr.es/m/flat/17540-7aa1855ad5ec18b4%40postgresql.org > > [2.

Re: Fix Brin Private Spool Initialization (src/backend/access/brin/brin.c)

Em ter., 26 de dez. de 2023 às 19:07, Tomas Vondra < tomas.von...@enterprisedb.com> escreveu: > > > On 12/26/23 19:10, Ranier Vilela wrote: > > Hi, > > > > The commit b437571 I > > think has an oversight. > > When allocate memory and initialize

Re: Bug in nbtree optimization to skip > operator comparisons (or < comparisons in backwards scans)

Alexander, On Tue, 26 Dec 2023 at 23:35, Alexander Korotkov wrote: > Pavel, > > On Mon, Dec 25, 2023 at 8:32 PM Pavel Borisov > wrote: > > I've reviewed both patches: > > 0001 - is a pure refactoring replacing argument transfer from via struct > member to transfer explicitly as a function

Shared detoast Datum proposal

Problem: Toast works well for its claimed purposes. However the current detoast infrastructure doesn't shared the detoast datum in the query's lifespan, which will cause some inefficiency like this: SELECT big_jsonb_col->'a', big_jsonb_col->'b', big_jsonb_col->'c' FROM t; In the

Re: Track in pg_replication_slots the reason why slots conflict?

On Wed, Dec 27, 2023 at 3:08 PM shveta malik wrote: > > PFA the patch which attempts to implement this. > > This patch changes the existing 'conflicting' field to > 'conflicting_cause' in pg_replication_slots. > This name sounds a bit odd to me, would it be better to name it as conflict_cause?

Re: Synchronizing slots from primary to standby

On Wed, Dec 27, 2023 at 11:36 AM Masahiko Sawada wrote: > > On Tue, Dec 26, 2023 at 9:27 PM shveta malik wrote: > > > > I would like to revisit the current dependency of slotsync worker on > > dbname used in 002 patch. Currently we accept dbname in > > primary_conninfo and thus the user has to

Re: Synchronizing slots from primary to standby

On Wed, Dec 27, 2023 at 11:36 AM Masahiko Sawada wrote: > > Hi, > > Thank you for working on this. > > On Tue, Dec 26, 2023 at 9:27 PM shveta malik wrote: > > > > On Tue, Dec 26, 2023 at 4:41 PM Zhijie Hou (Fujitsu) > > wrote: > > > > > > On Wednesday, December 20, 2023 7:37 PM Amit Kapila > >

Re: Function to get invalidation cause of a replication slot.

On Thu, Dec 21, 2023 at 2:59 PM Amit Kapila wrote: > > On Thu, Dec 21, 2023 at 12:07 PM Michael Paquier wrote: > > > > On Thu, Dec 21, 2023 at 11:53:04AM +0530, Amit Kapila wrote: > > > On Thu, Dec 21, 2023 at 11:18 AM Michael Paquier > > > wrote: > > > Yeah, if one uses them independently

Re: Track in pg_replication_slots the reason why slots conflict?

On Tue, Dec 26, 2023 at 7:35 PM Isaac Morland wrote: > > On Thu, 21 Dec 2023 at 09:26, Amit Kapila wrote: > >> >> A conflicting column where NULL indicates no conflict, and other >> > values indicate the reason for the conflict, doesn't seem too bad. >> > >> >> This is fine too. > > > I prefer

Revise the Asserts added to bimapset manipulation functions

The Asserts added to bitmapset.c by commits 71a3e8c43b and 7d58f2342b contain some duplicates, such as in bms_difference, bms_is_subset, bms_subset_compare, bms_int_members and bms_join. For instance, @@ -953,6 +1033,15 @@ bms_int_members(Bitmapset *a, const Bitmapset *b) int

Re: pg_stat_statements: more test coverage

Hi, On Tue, Dec 26, 2023 at 10:03 PM Peter Eisentraut wrote: > > On 24.12.23 03:03, Michael Paquier wrote: > > - Use a DO block of a PL function, say with something like that to > > ensure an amount of N queries? Say with something like that after > > tweaking pg_stat_statements.track: > >