RE: bogus: logical replication rows/cols combinations

On Friday, May 20, 2022 11:06 AM Amit Kapila wrote: > > On Thu, May 19, 2022 at 7:54 PM Tom Lane wrote: > > > Even more to the point, how can we > > have a subscriber do that by relying on view columns that don't exist > > in older versions? > > > > We need a version check like (if >

Re: [BUG] Logical replication failure "ERROR: could not map filenode "base/13237/442428" to relation OID" with catalog modifying txns

On Tue, May 24, 2022 at 7:58 AM Masahiko Sawada wrote: > > On Mon, May 23, 2022 at 2:39 PM Amit Kapila wrote: > > > > On Mon, May 23, 2022 at 10:03 AM Kyotaro Horiguchi > > wrote: > > > > > > At Sat, 21 May 2022 15:35:58 +0530, Amit Kapila > > > wrote in > > > > I think if we don't have any

Re: First draft of the PG 15 release notes

On Mon, May 16, 2022 at 9:18 PM Bruce Momjian wrote: > > Newer wording: > > Improve validation of UTF-8 text (even if only ASCII) by processing > 16 bytes at a time (John Naylor) Thanks! I also think Heikki should be mentioned as a coauthor here -- the ASCII coding was his work

Re: PostgreSQL 15 Beta 1 release announcement draft

On Sat, May 14, 2022 at 2:52 PM Jonathan S. Katz wrote: > Hi, > > Attached is a draft of the release announcement for the PostgreSQL 15 > Beta 1 release. The goal of this announcement is to raise awareness > around many of the new features appearing in PostgreSQL 15 and to > encourage people to

Re: partition wise aggregate wrong rows cost

David Rowley writes: > On Tue, 24 May 2022 at 15:38, bucoo wrote: >> Normal aggregate and partition wise aggregate have a big difference rows >> cost: > I wouldn't say this is a bug. Could you not say that they're both > wrong given that your tables are empty? We try fairly hard to ensure

Re: partition wise aggregate wrong rows cost

On Tue, 24 May 2022 at 15:38, bucoo wrote: > > Normal aggregate and partition wise aggregate have a big difference rows cost: > explain (verbose) > select count(1) from t1 group by id; > HashAggregate (cost=106.20..108.20 rows=200 width=12) --here rows is 200 > set

Re: Patch: Don't set LoadedSSL unless secure_initialize succeeds

Daniel Gustafsson writes: >> On 22 May 2022, at 08:41, Gurjeet Singh wrote: >> The initialization in PostmasterMain() blindly turns on LoadedSSL, >> irrespective of the outcome of secure_initialize(). > This call is invoked with isServerStart set to true so any error in > secure_initialize

partition wise aggregate wrong rows cost

Normal aggregate and partition wise aggregate have a big difference rows cost: begin; create table t1(id integer, name text) partition by hash(id); create table t1_0 partition of t1 for values with(modulus 3, remainder 0); create table t1_1 partition of t1 for values with(modulus 3, remainder 1);

Re: allow building trusted languages without the untrusted versions

On 5/23/22 8:04 PM, Nathan Bossart wrote: On Mon, May 23, 2022 at 07:09:03PM -0400, Stephen Frost wrote: Instead, I'd argue that we should be continuing to work in the direction of splitting up what can only be done by a superuser today using predefined roles and other methods along those

RE: Handle infinite recursion in logical replication setup

On Tue, May 24, 2022 1:34 AM vignesh C wrote: > > Thanks for the comments, the attached v15 patch has the fixes for the same. > Thanks for updating the patch. I have a comment on the document in 0002 patch. @@ -300,6 +310,11 @@ CREATE SUBSCRIPTION subscription_namefalse. +

Re: Patch: Don't set LoadedSSL unless secure_initialize succeeds

On Sun, May 22, 2022 at 09:17:37AM +0200, Daniel Gustafsson wrote: > This call is invoked with isServerStart set to true so any error in > secure_initialize should error out with ereport FATAL (in be_tls_init()). > That > could be explained in a comment though, which is currently isn't. All the

Re: [BUG] Logical replication failure "ERROR: could not map filenode "base/13237/442428" to relation OID" with catalog modifying txns

On Mon, May 23, 2022 at 2:39 PM Amit Kapila wrote: > > On Mon, May 23, 2022 at 10:03 AM Kyotaro Horiguchi > wrote: > > > > At Sat, 21 May 2022 15:35:58 +0530, Amit Kapila > > wrote in > > > I think if we don't have any better ideas then we should go with > > > either this or one of the other

Re: Enforce "max_wal_size/ min_wal_size must be at least twice wal_segment_size" limit while setting GUCs

On Mon, May 23, 2022 at 10:08:54AM -0400, Tom Lane wrote: > In general, you can't do that (i.e. try to enforce constraints between > GUC values via check hooks). It's been tried in the past and failed > miserably, because the hooks can't know whether the other value is > about to be changed. +1.

Re: Add --{no-,}bypassrls flags to createuser

On Mon, May 23, 2022 at 09:37:35AM -0700, Nathan Bossart wrote: > Michael also requested a test for multiple -a switches and for fancier > string patterns. Once that is taken care of, I think this can be marked as > ready-for-committer. Looking at v7, this means to extend the tests to process

Re: fix typos in storing statistics in shared memory

On Mon, May 23, 2022 at 10:22:36AM -0700, Nathan Bossart wrote: > On Mon, May 23, 2022 at 10:46:22PM +0900, torikoshia wrote: >> It seems like there are some duplications of 'the' in pgstat.c and >> pgstat_internal.h. >> Attaching a tiny patch to fix them. > > LGTM Thanks Torikoshi-san, fixed.

Re: Enforce "max_wal_size/ min_wal_size must be at least twice wal_segment_size" limit while setting GUCs

At Tue, 24 May 2022 10:19:53 +0900 (JST), Kyotaro Horiguchi wrote in > At Mon, 23 May 2022 10:08:54 -0400, Tom Lane wrote in > > Bharath Rupireddy writes: > > > Thanks. Having check hooks for min_wal_size and max_wal_size that > > > throw errors if they aren't at least twice the

Re: Allow file inclusion in pg_hba and pg_ident files

On Mon, May 23, 2022 at 07:43:08PM +0800, Julien Rouhaud wrote: > That being said, I'd gladly drop that enum and only handle a single error > message, as the rest of the error context (including the owning file name and > line) should provide enough information to users. > > If so, should I use

Re: Enforce "max_wal_size/ min_wal_size must be at least twice wal_segment_size" limit while setting GUCs

At Mon, 23 May 2022 10:08:54 -0400, Tom Lane wrote in > Bharath Rupireddy writes: > > Thanks. Having check hooks for min_wal_size and max_wal_size that > > throw errors if they aren't at least twice the wal_segment_size has a > > "BIG" problem - ./initdb -D data --wal-segsize=1 (or a value <

Re: PG15 beta1 sort performance regression due to Generation context change

On 5/20/22 1:56 AM, David Rowley wrote: Hackers, Over the past few days I've been gathering some benchmark results together to show the sort performance improvements in PG15 [1]. So it basically looks like I discovered a very bad case that causes a significant slowdown. Yet other cases that

Re: allow building trusted languages without the untrusted versions

On Mon, May 23, 2022 at 07:09:03PM -0400, Stephen Frost wrote: > Instead, I'd argue that we should be continuing to work in the direction > of splitting up what can only be done by a superuser today using > predefined roles and other methods along those lines. How that lines up > with this latest

Re: allow building trusted languages without the untrusted versions

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haas writes: > > It's not just a question of whether it was meant to be SQL-callable -- > > it's also a question of what arguments it was expecting to be called > > with. At the very least, you can cause the server to core dump if you >

Re: PG15 beta1 sort performance regression due to Generation context change

I had another, possibly-crazy idea. I think that the API requirement that the word before a chunk's start point to a MemoryContext is overly strong. What we need is that it point to something in which a MemoryContextMethods pointer can be found (at a predefined offset). Thus, if generation.c is

Re: allow building trusted languages without the untrusted versions

Robert Haas writes: > It's not just a question of whether it was meant to be SQL-callable -- > it's also a question of what arguments it was expecting to be called > with. At the very least, you can cause the server to core dump if you > pass something that isn't a valid pointer to a function

Re: PG15 beta1 sort performance regression due to Generation context change

David Rowley writes: > On Tue, 24 May 2022 at 10:02, Tom Lane wrote: >> BTW, shouldn't GenerationCheck be ifdef'd out if MEMORY_CONTEXT_CHECKING >> isn't set? aset.c does things that way. > Isn't it done in generation.c:954? Ah, sorry, didn't look that far up ...

Re: PG15 beta1 sort performance regression due to Generation context change

On Tue, 24 May 2022 at 10:02, Tom Lane wrote: > BTW, shouldn't GenerationCheck be ifdef'd out if MEMORY_CONTEXT_CHECKING > isn't set? aset.c does things that way. Isn't it done in generation.c:954? David

Re: PG15 beta1 sort performance regression due to Generation context change

David Rowley writes: > GenerationRealloc: uses "size" to figure out if the new size is > smaller than the old size. Maybe we could just always move to a new > chunk regardless of if the new size is smaller or larger than the old > size. I had the same idea ... but we need to know the old size

Re: PG15 beta1 sort performance regression due to Generation context change

I wrote: > However, here's a different idea: how badly do we need the "size" > field in GenerationChunk? We're not going to ever recycle the > chunk, IIUC, so it doesn't matter exactly how big it is. When > doing MEMORY_CONTEXT_CHECKING we'll still want requested_size, > but that's not relevant

Re: PG15 beta1 sort performance regression due to Generation context change

Em seg., 23 de mai. de 2022 às 18:01, Tomas Vondra < tomas.von...@enterprisedb.com> escreveu: > FYI not sure why, but your responses seem to break threading quite > often, due to missing headers identifying the message you're responding > to (In-Reply-To, References). Not sure why or how to fix

Re: PG15 beta1 sort performance regression due to Generation context change

On Tue, 24 May 2022 at 09:36, Tom Lane wrote: > However, here's a different idea: how badly do we need the "size" > field in GenerationChunk? We're not going to ever recycle the > chunk, IIUC, so it doesn't matter exactly how big it is. When > doing MEMORY_CONTEXT_CHECKING we'll still want

Re: allow building trusted languages without the untrusted versions

On Mon, May 23, 2022 at 4:46 PM Tom Lane wrote: > Maybe I shouldn't be putting words into Nathan's mouth, but I think > what he is after is a mode intended for use by cloud service providers, > who would like to offer locked-down database services where there's > just no way to get to the disk

Re: PG15 beta1 sort performance regression due to Generation context change

David Rowley writes: > Handy wavy idea: It's probably too complex for now, and it also might > be too much overhead, but having GenerationPointerGetChunk() do a > binary search on a sorted-by-memory-address array of block pointers > might be a fast enough way to find the block that the pointer

Re: PG15 beta1 sort performance regression due to Generation context change

On Tue, 24 May 2022 at 08:52, Tomas Vondra wrote: > > On 5/23/22 22:47, Tom Lane wrote: > > How would you know which context type to consult for that? > > > > D'oh! I knew there has to be some flaw in that idea, but I forgot about > this chicken-or-egg issue. Handy wavy idea: It's probably too

Re: PG15 beta1 sort performance regression due to Generation context change

FYI not sure why, but your responses seem to break threading quite often, due to missing headers identifying the message you're responding to (In-Reply-To, References). Not sure why or how to fix it, but this makes it much harder to follow the discussion. On 5/22/22 21:11, Ranier Vilela wrote: >

Re: PG15 beta1 sort performance regression due to Generation context change

On 5/23/22 22:47, Tom Lane wrote: > Tomas Vondra writes: >> On 5/20/22 12:01, Heikki Linnakangas wrote: >>> Could the 'context' field be moved from GenerationChunk to GenerationBlock? > >> Not easily, because GetMemoryChunkContext() expects the context to be >> stored right before the chunk.

Re: PG15 beta1 sort performance regression due to Generation context change

On Tue, 24 May 2022 at 08:32, Tomas Vondra wrote: > > On 5/20/22 12:01, Heikki Linnakangas wrote: > > Could the 'context' field be moved from GenerationChunk to GenerationBlock? > > > > Not easily, because GetMemoryChunkContext() expects the context to be > stored right before the chunk. In

Re: PG15 beta1 sort performance regression due to Generation context change

Tomas Vondra writes: > On 5/20/22 12:01, Heikki Linnakangas wrote: >> Could the 'context' field be moved from GenerationChunk to GenerationBlock? > Not easily, because GetMemoryChunkContext() expects the context to be > stored right before the chunk. In principle we could add "get context" >

Re: allow building trusted languages without the untrusted versions

Robert Haas writes: > I definitely think there's a need for a user who can manipulate > objects in the database much like a superuser (i.e. access all > objects, grant and revoke all privileges, etc.) but who can't break > out into the OS user account and assume it's privileges. I'm not sure >

Re: PG15 beta1 sort performance regression due to Generation context change

On 5/20/22 12:01, Heikki Linnakangas wrote: > On 20/05/2022 08:56, David Rowley wrote: >> The problem is that generation chunks have a larger chunk header than >> aset do due to having to store the block pointer that the chunk >> belongs to so that GenerationFree() can increment the nfree chunks

Re: allow building trusted languages without the untrusted versions

On Mon, May 23, 2022 at 1:17 PM Tom Lane wrote: > There would probably be some interest in a "--disable-disk-access" > configure option that did all of this stuff (and some more things > too), with the aim of locking down *all* known paths to filesystem > access. I don't see much value in retail

Re: allow building trusted languages without the untrusted versions

On Mon, May 23, 2022 at 02:20:02PM -0400, Tom Lane wrote: > Nathan Bossart writes: >> That's a reasonable point. I'll go ahead an explore some options for >> something along those lines. A couple of questions immediately come to >> mind. For example, should this configuration option just cause

Re: allow building trusted languages without the untrusted versions

Nathan Bossart writes: > That's a reasonable point. I'll go ahead an explore some options for > something along those lines. A couple of questions immediately come to > mind. For example, should this configuration option just cause these > functions to ERROR, or should it compile them out?

Re: allow building trusted languages without the untrusted versions

On Mon, May 23, 2022 at 01:17:08PM -0400, Tom Lane wrote: > There would probably be some interest in a "--disable-disk-access" > configure option that did all of this stuff (and some more things > too), with the aim of locking down *all* known paths to filesystem > access. I don't see much value

Re: fix typos in storing statistics in shared memory

On Mon, May 23, 2022 at 10:46:22PM +0900, torikoshia wrote: > It seems like there are some duplications of 'the' in pgstat.c and > pgstat_internal.h. > Attaching a tiny patch to fix them. LGTM -- Nathan Bossart Amazon Web Services: https://aws.amazon.com

Re: allow building trusted languages without the untrusted versions

Nathan Bossart writes: > On Fri, May 20, 2022 at 08:20:11PM -0400, Tom Lane wrote: >> Lastly, you've offered no reason to think this would provide any real >> security improvement. Someone who's gained the ability to issue CREATE >> EXTENSION on untrusted extensions has already got all the

Re: allow building trusted languages without the untrusted versions

On Fri, May 20, 2022 at 08:20:11PM -0400, Tom Lane wrote: > Nathan Bossart writes: >> Presently, if you want to only build trusted PL/Perl and PL/Tcl, you need >> to make a couple of code changes to compile out the untrusted parts. I >> suspect many users (e.g., anyone who wants to disallow file

Re: docs: mention "pg_read_all_stats" in "track_activities" description

On Mon, May 23, 2022 at 08:53:24AM +0900, Michael Paquier wrote: > On Sun, May 22, 2022 at 01:26:08PM -0700, Nathan Bossart wrote: >> ... superusers, roles with privileges of the pg_read_all_stats role, >> and roles with privileges of the user owning the session being reported >> on

Re: Add --{no-,}bypassrls flags to createuser

On Mon, May 23, 2022 at 11:55:43PM +0900, Shinya Kato wrote: > On 2022-05-23 16:29, Michael Paquier wrote: >> May I ask for the addition of tests when one specifies multiple >> switches for --admin and --member? This would check the code path >> where you build a list of role names. You could

Re: Support logical replication of DDLs

> Now, say, the user has added a bar column with "ALTER TABLE foo ADD > COLUMN bar double precision NOT NULL DEFAULT random();" If we compare > with replication of DMLs like (UPDATE ddl_test SET bar = random();), > the replication won't update rows with values (3 and 4) on subscriber > as they

Re: WIP Patch: Precalculate stable functions, infrastructure v1

Hi hackers! I would like to revive this thread. At ServiceNow we recurringly encounter queries that are much slower than they would have to be, because of frequent calls to uncached stable functions with constant arguments (mostly to_date()). We've seen e.g. queries that get more than 8x faster

Re: Add --{no-,}bypassrls flags to createuser

On 2022-05-23 16:29, Michael Paquier wrote: +$node->issues_sql_like( + [ 'createuser', 'regress_role2', '-a', 'regress_user1' ], + qr/statement: CREATE ROLE regress_role2 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ADMIN regress_user1;/, + 'add a role as a member with admin option

Re: Enforce "max_wal_size/ min_wal_size must be at least twice wal_segment_size" limit while setting GUCs

Bharath Rupireddy writes: > Thanks. Having check hooks for min_wal_size and max_wal_size that > throw errors if they aren't at least twice the wal_segment_size has a > "BIG" problem - ./initdb -D data --wal-segsize=1 (or a value < 16) > fails. In general, you can't do that (i.e. try to enforce

fix typos in storing statistics in shared memory

Hi, It seems like there are some duplications of 'the' in pgstat.c and pgstat_internal.h. Attaching a tiny patch to fix them. -- Regards, -- Atsushi Torikoshi NTT DATA CORPORATIONFrom fabf3bc5da8e5ebd4f600b6ba762d4413cbdcd6b Mon Sep 17 00:00:00 2001 From: Atsushi Torikoshi Date: Mon, 23 May

Re: Zstandard support for toast compression

On Fri, May 20, 2022 at 4:17 PM Stephen Frost wrote: > A thought I've had before is that it'd be nice to specify a particular > compression method on a data type basis. Wasn't the direction that this > was taken, for reasons, but I wonder about perhaps still having a data > type compression

Re: Zstandard support for toast compression

On Mon, May 23, 2022 at 12:33 AM Michael Paquier wrote: > On Thu, May 19, 2022 at 04:12:01PM -0400, Robert Haas wrote: > > On Thu, May 19, 2022 at 4:20 AM Michael Paquier wrote: > >> Btw, shouldn't we have something a bit more, err, extensible for the > >> design of an extensible varlena header?

Re: Limiting memory allocation

On 5/20/22 19:08, Tomas Vondra wrote: Well, we already have the memory-accounting built into the memory context infrastructure. It kinda does the same thing as the malloc() wrapper, except that it does not publish the information anywhere and it's per-context (so we have to walk the context

Re: Enforce "max_wal_size/ min_wal_size must be at least twice wal_segment_size" limit while setting GUCs

On Mon, May 23, 2022 at 10:45 AM Kyotaro Horiguchi wrote: > > At Sat, 21 May 2022 19:08:06 +0530, Bharath Rupireddy > wrote in > > How about we add GUC check hooks for both max_wal_size and > > min_wal_size where we can either emit ERROR or WARNING if values are > > not "at least twice as

Re: Support logical replication of DDLs

On Thu, Apr 14, 2022 at 7:45 PM Euler Taveira wrote: > > You should forbid it. Unless you can decompose the command into multiple SQL > commands to make it a safe operation for logical replication. > > Let's say you want to add a column with a volatile default. > > ALTER TABLE foo ADD COLUMN bar

Re: Allow file inclusion in pg_hba and pg_ident files

Hi, On Mon, May 23, 2022 at 03:53:06PM +0900, Michael Paquier wrote: > > + switch (kind) > + { > + case SecondaryAuthFile: > + msglog = "could not open secondary authentication file > \"@%s\" as \"%s\": %m"; > + msgview = "could not open

Re: create_help.pl treats as replaceable

On 19.05.22 04:12, Kyotaro Horiguchi wrote: At Wed, 18 May 2022 18:23:57 +0200, Peter Eisentraut wrote in "Boolean" is correct; see for example. Ok, so, don't we in turn need to replace "boolean"s with "Boolean"? "only boolean operators can have

Re: create_help.pl treats as replaceable

On 18.05.22 18:29, Tom Lane wrote: I think you should have upper-cased MATCH while at it, to make it clear that it acts like a keyword in this context. The current situation is quite unreadable in plain-ASCII output: regression=# \help copy Command: COPY ... HEADER [ boolean | match ]

Re: Frontend error logging style

On 29.03.22 16:24, Peter Eisentraut wrote: I think I would want the program name/location also in front of the detail and hint lines.  I need to think about this a bit more.  This shouldn't hold up this patch; it would be a quick localized change. After experiencing this for a bit now, I

Re: psql now shows zero elapsed time after an error

Probably it would be appropriate to add a test case. I'll propose something later. committed with a test Thanks! -- Fabien.

Re: PG15 beta1 fix pg_database view document

On 23.05.22 06:25, Michael Paquier wrote: Indeed. I have checked the rest of the catalog headers for any inconsistencies with the docs and what you have noticed here is the only one. Committed. Thanks for checking. + datlocprovider char + + + Provider of the

Use Dblink without column defination

Hi Team, Appreciate your time to look into this. To select from another database I try to use dblink or fdw extension of Postgres, like this: mesods =>CREATE EXTENSION dblink; CREATE EXTENSION mesods => CREATE EXTENSION postgres_fdw; CREATE EXTENSION mesods=> select

Re: psql now shows zero elapsed time after an error

On 10.05.22 15:42, Fabien COELHO wrote: Hello, Thanks for the catch and the proposed fix! Indeed, on errors the timing is not updated appropriately. ISTM that the best course is to update the elapsed time whenever a result is obtained, so that a sensible value is always available. See

Re: Add --{no-,}bypassrls flags to createuser

On Fri, May 20, 2022 at 02:45:19PM -0700, Nathan Bossart wrote: > Thanks for the new patch! I attached a new version with a few small > changes. What do you think? So you have settled down to --member to emulate the clause ROLE. Well, this choice is fine by me at the end. >

Re: Allow file inclusion in pg_hba and pg_ident files

On Wed, May 18, 2022 at 03:12:45PM +0800, Julien Rouhaud wrote: > The cfbot reports that the patch doesn't apply anymore, rebased v7 attached. + switch (kind) + { + case SecondaryAuthFile: + msglog = "could not open secondary authentication file \"@%s\" as

Re: postgres_fdw has insufficient support for large object

On Mon, May 23, 2022 at 1:21 PM Tom Lane wrote: > The big picture here is that Postgres is a hodgepodge of features > that were developed at different times and with different quality > standards, over a period that's now approaching forty years. > Some of these features interoperate better than

Re: postgres_fdw has insufficient support for large object

"=?gb18030?B?U2FsYWRpbg==?=" writes: > The output i expected: > pg_largeobject_metadata and pg_largeobject in both database A and database > B should have rows.Shouldn't only in database A.So, i can use large object > functions > to operate large_objectin remote table or foreign table. The big

Re: Add --{no-,}bypassrls flags to createuser

On 2022-05-21 06:45, Nathan Bossart wrote: On Thu, May 19, 2022 at 10:35:23AM +0900, Shinya Kato wrote: I created a new patch to test the new options! Thanks for the new patch! I attached a new version with a few small changes. What do you think? Thanks for updating the patch! It looks

Re: A qsort template

I wrote: > I agree this is only useful in development. Removal sounds fine to me, > so I'll do that soon. This is done. -- John Naylor EDB: http://www.enterprisedb.com