Re: Confusing error message in 15.6

On Tue, 2024-05-21 at 23:58 +, Dmitry O Litvintsev wrote: > > > I am observing the following error which confuses me: > > > > > > # psql -U postgres template1 -c "checkpoint; select > > > pg_backup_start('${dest}.tar.Z', true)" > > > CHECKPOINT > > > pg_backup_start > > > -

Re: vacuum an all frozen table

On Tue, 2024-05-21 at 14:46 -0700, Senor Cervesa wrote: > I'd like to understand what's happening here and whether there is > anything I can do to improve the situation. > > PostgreSQL v11.22 (yeah, I know. Needs upgrade) > > The following 3 autovacuum log entries show a vacuum of an append only

Finding "most recent" using daterange

Coming back to PostgreSQL after a (decades-)long absence...  If I have something like: CREATE TABLE example ( id integer NOT NULL, value text NOT NULL, dates daterange NOT NULL ); INSERT INTO example VALUES (1, 'a', '[2010-01-01,2020-01-01)'), (1, 'b', '[20

Re: Restore of a reference database kills the auto analyze processing.

Classified as: {OPEN} > Just for confirmation your settings are still?: > autovacuum_max_workers = 10 > log_autovacuum_min_duration = 0 Yes. > You said previously: > "The only way I can find of getting the analyzer back is to restart Postgres." > > To be clear this means: > 1) The lfm database

Regarding use case of epoch to generate nanoseconds precision

Hi Postgres supports only upto microseconds (6 decimal precision). How do we generate timestamp with nanoseconds as rds postgres not supported timestamp9 extension ? Is there a way to generate timestamp with nanoseconds precision on pg_partman with epoch without typecasting or with typecasting ?

Missed compiler optimization issue in function select_rtable_names_for_explain

Hi everyone, I'm a compiler developer working on detecting missed optimization in real-world applications. Recently, we found that LLVM missed a dead store elimination optimization in the PostgreSQL code

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

> On 22 May 2024, at 11:27, XChy wrote: > > Hi everyone, > I'm a compiler developer working on detecting missed optimization in > real-world applications. Recently, we found that LLVM missed a dead store > elimination optimization in the PostgreSQL code > (https://github.com/postgres/postgres/

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

How is the memset in select_rtable_names_for_explain a dead-store? Even memset calls could be optimized away from the EXPLAIN codepath I have a feeling it would have to be many in a tight loop for it to be measurable even? -- Daniel Gustafsson For the first question, I don't mean that the mems

Re: Regarding use case of epoch to generate nanoseconds precision

On Wed, May 22, 2024 at 4:21 AM Durgamahesh Manne wrote: > Hi > > Postgres supports only upto microseconds (6 decimal precision). > How do we generate timestamp with nanoseconds as rds postgres not > supported timestamp9 extension ? > Is there a way to generate timestamp with nanoseconds precisio

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

> On 22 May 2024, at 12:12, XChy wrote: > >> How is the memset in select_rtable_names_for_explain a dead-store? Even >> memset calls could be optimized away from the EXPLAIN codepath I have a >> feeling it >> would have to be many in a tight loop for it to be measurable even? > For the first q

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

在 2024/5/22 18:55, Daniel Gustafsson 写道: I mean that the stores with value "0" after the memset are dead: ``` dpns.subplans = NIL; dpns.ctes = NIL; dpns.appendrels = NULL; ``` since the memset has written zeroes to the object "dpns", and these members are known to be zero. They

Re: Finding "most recent" using daterange

This is a good candidate for a window function. Also note that nulls already get sorted correctly by the DESC so no need to get 'infinity' involved, although you could write 'DESC NULLS FIRST' to be explicit about it. with x as (select *, row_number() over (partition by id order by upper(dates) d

search_path and SET ROLE

PG 9.6.24 (Soon, I swear!) It seems that the search_path of the role that you SET ROLE to does not become the new search_path. Am I missing something, or is that PG's behavior? AS USER postgres $ psql -h 10.143.170.52 -Xac "CREATE ROLE dbagrp SUPERUSER INHERIT NOLOGIN;" CREATE

Re: search_path and SET ROLE

On Wednesday, May 22, 2024, Ron Johnson wrote: > > > It seems that the search_path of the role that you SET ROLE to does not > become the new search_path. > > Am I missing something, or is that PG's behavior? > Yes, attaching a setting to a non-login role is basically pointless as those settings

Re: search_path and SET ROLE

On 5/22/24 07:27, Ron Johnson wrote: PG 9.6.24 (Soon, I swear!) It seems that the search_path of the role that you SET ROLE to does not become the new search_path. Am I missing something, or is that PG's behavior? AS USER postgres $ psql -h 10.143.170.52 -Xac "CREATE ROLE d

Re: Finding "most recent" using daterange

On Wed, 22 May 2024 at 10:15, Greg Sabino Mullane wrote: > This is a good candidate for a window function. Also note that nulls > already get sorted correctly by the DESC so no need to get 'infinity' > involved, although you could write 'DESC NULLS FIRST' to be explicit about > it. > > with x as

Re: Finding "most recent" using daterange

Oh, you are right - NULLS LAST, got my -infinity crossed with my infinity. :) Cheers, Greg

Re: Restore of a reference database kills the auto analyze processing.

On 5/22/24 01:33, HORDER Philip wrote: Classified as: {OPEN} 2) There is a round of autovacuum immediately after the lfm is restored. Yes, some tables in the lfm database, but not all, an apparently random selection, anywhere between 2 and 21 tables, across the lfm schemas, public & pg_catal

Re: Restore of a reference database kills the auto analyze processing.

Classified as: {OPEN} > https://www.postgresql.org/docs/15/release-15-5.html#id-1.11.6.7.4 > Fix race condition in database dropping that could lead to the autovacuum > launcher getting stuck Wow, that sounds like our problem! I will investigate. Maybe try and find the orphaned stats entry to

Re: Regarding use case of epoch to generate nanoseconds precision

On Wed, May 22, 2024 at 5:21 AM Durgamahesh Manne wrote: > Hi > > Postgres supports only upto microseconds (6 decimal precision). > How do we generate timestamp with nanoseconds as rds postgres not > supported timestamp9 extension ? > Is there a way to generate timestamp with nanoseconds precisio

Re: Restore of a reference database kills the auto analyze processing.

On 5/22/24 08:55, HORDER Philip wrote: Classified as: {OPEN} https://www.postgresql.org/docs/15/release-15-5.html#id-1.11.6.7.4 Fix race condition in database dropping that could lead to the autovacuum launcher getting stuck Wow, that sounds like our problem! Another thought I had is th

Re: Finding "most recent" using daterange

On Wed, 22 May 2024 at 11:36, Greg Sabino Mullane wrote: > Oh, you are right - NULLS LAST, got my -infinity crossed with my infinity. > :) > NULLS LAST for lower bound, NULLS FIRST for upper bound. The other way around if you were doing an ascending sort.

search_path wildcard?

This doesn't work, and I've found nothing similar: ALTER ROLE foo SET SEARCH_PATH = '*'; Is there a single SQL statement which will generate a search path based on information_schema.schemata, or do I have to write an anonymous DO procedure? SELECT schema_name FROM information_schema.schemata WHE

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

Daniel Gustafsson writes: > They are known to be zero, but that's not entirely equivalent though is it? > NIL is defined as ((List *) NULL) and NULL is typically defined as ((void *) > 0), so sizeof(0) would be the size of an int and sizeof(NULL) would be the > size > of a void pointer. There ar

Re: search_path wildcard?

On Wed, May 22, 2024, 10:36 Ron Johnson wrote: > This doesn't work, and I've found nothing similar: > ALTER ROLE foo SET SEARCH_PATH = '*'; > Correct, you cannot do that. David J.

Re: search_path and SET ROLE

Ron Johnson writes: > It seems that the search_path of the role that you SET ROLE to does not > become the new search_path. It does for me: regression=# create role r1; CREATE ROLE regression=# create schema r1 authorization r1; CREATE SCHEMA regression=# select current_schemas(true), current_us

Re: search_path and SET ROLE

On Wed, May 22, 2024 at 1:10 PM Tom Lane wrote: > Ron Johnson writes: > > It seems that the search_path of the role that you SET ROLE to does not > > become the new search_path. > > It does for me: > > regression=# create role r1; > CREATE ROLE > regression=# create schema r1 authorization r1; >

Re: search_path wildcard?

On Wed, May 22, 2024 at 12:53 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, May 22, 2024, 10:36 Ron Johnson wrote: > >> This doesn't work, and I've found nothing similar: >> ALTER ROLE foo SET SEARCH_PATH = '*'; >> > > Correct, you cannot do that. > That would be a helpful

Re: search_path wildcard?

Ron Johnson writes: > That would be a helpful feature for administrators, when there are multiple > schemas in multiple databases, on multiple servers: superusers get ALTER > ROLE foo SET SEARCH_PATH = '*'; and they're done with it. ... and they're pwned within five minutes by any user with the

Re: search_path and SET ROLE

On Wed, 22 May 2024 at 13:48, Ron Johnson wrote: As a superuser administrator, I need to be able to see ALL tables in ALL > schemas when running "\dt", not just the ones in "$user" and public. And I > need it to act consistently across all the systems. > \dt *.* But I am skeptical how often yo

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

On 2024-May-22, XChy wrote: > Hi everyone, > > I'm a compiler developer working on detecting missed optimization in > real-world applications. Recently, we found that LLVM missed a dead store > elimination optimization in the PostgreSQL code >

Re: Finding "most recent" using daterange

> On 22 May 2024, at 09:58, Rob Foehl wrote: > > Coming back to PostgreSQL after a (decades-)long absence... If I have > something like: > > CREATE TABLE example ( > id integer NOT NULL, > value text NOT NULL, > dates daterange NOT NULL > ); > > INSERT INTO example VALUES > (1, 'a', '[2010-0

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

> On 22 May 2024, at 13:00, Alvaro Herrera wrote: > I think if we want to improve how this code is seen by the compiler by > modifying it, we should just remove the NULL/NIL assignments. *If* the optimization is measurable, IMHO. > It's a > pretty arbitrary (and rather small) subset of fields b

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

> On 22 May 2024, at 18:53, Tom Lane wrote: > > Daniel Gustafsson writes: >> They are known to be zero, but that's not entirely equivalent though is it? >> NIL is defined as ((List *) NULL) and NULL is typically defined as ((void *) >> 0), so sizeof(0) would be the size of an int and sizeof(NULL

Re: search_path wildcard?

On Wed, May 22, 2024 at 1:58 PM Tom Lane wrote: > Ron Johnson writes: > > That would be a helpful feature for administrators, when there are > multiple > > schemas in multiple databases, on multiple servers: superusers get ALTER > > ROLE foo SET SEARCH_PATH = '*'; and they're done with it. > >

Re: search_path wildcard?

st 22. 5. 2024 v 19:54 odesílatel Ron Johnson napsal: > On Wed, May 22, 2024 at 12:53 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wed, May 22, 2024, 10:36 Ron Johnson wrote: >> >>> This doesn't work, and I've found nothing similar: >>> ALTER ROLE foo SET SEARCH_PATH = '*

Re: search_path and SET ROLE

On Wed, May 22, 2024 at 2:02 PM Isaac Morland wrote: > On Wed, 22 May 2024 at 13:48, Ron Johnson wrote: > > As a superuser administrator, I need to be able to see ALL tables in ALL >> schemas when running "\dt", not just the ones in "$user" and public. And I >> need it to act consistently acros

Re: search_path wildcard?

st 22. 5. 2024 v 21:13 odesílatel Ron Johnson napsal: > On Wed, May 22, 2024 at 1:58 PM Tom Lane wrote: > >> Ron Johnson writes: >> > That would be a helpful feature for administrators, when there are >> multiple >> > schemas in multiple databases, on multiple servers: superusers get ALTER >> >

Re: search_path and SET ROLE

st 22. 5. 2024 v 21:38 odesílatel Ron Johnson napsal: > On Wed, May 22, 2024 at 2:02 PM Isaac Morland > wrote: > >> On Wed, 22 May 2024 at 13:48, Ron Johnson >> wrote: >> >> As a superuser administrator, I need to be able to see ALL tables in ALL >>> schemas when running "\dt", not just the one

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

Alvaro Herrera writes: > I think if we want to improve how this code is seen by the compiler by > modifying it, we should just remove the NULL/NIL assignments. It's a > pretty arbitrary (and rather small) subset of fields being initialized, > fields which very obviously have been zeroed by memset

Hash join and picking which result set to build the hash table with.

The query and execution plan are shown below. My question is related to the result set the optimizer is choosing to build the hash table from. My understanding is for a hash join you want to build the hash table out of the smaller result set. If you look at the execution plan below you can se

Re: Hash join and picking which result set to build the hash table with.

"Dirschel, Steve" writes: > The query and execution plan are shown below. My question is > related to the result set the optimizer is choosing to build the > hash table from. My understanding is for a hash join you want to > build the hash table out of the smaller result set. That's *a* conside

Long running query causing XID limit breach

Hello , It's RDS postgres version 15.4. We suddenly saw the "MaximumUsedTransactionIDs" reach to ~1.5billion and got alerted by team members who mentioned the database is going to be in shutdown/hung if this value reaches to ~2billion and won't be able to serve any incoming transactions. It was a p

Re: Finding "most recent" using daterange

On Wed, May 22, 2024 at 11:07 AM Alban Hertroys wrote: > > Sounds like a good candidate for using EXISTS to prove that no more recent > value exists for a given id: > > SELECT e.id, e.value, e.dates > FROM example AS e > WHERE NOT EXISTS ( > SELECT 1 > FROM example AS i >

Re: Long running query causing XID limit breach

Greetings, In high-transaction environments like yours, it may be necessary to supplement this with manual vacuuming. Few Recommendations Monitor Long-Running Queries try to optimize. Optimize Autovacuum. Partitioning. Adopt Vacuum Strategy after peak hours. *Salahuddin (살라후딘**)* On Thu, 23

Json table/column design question

Hi, I was wondering if having unrelated columns in a table is a sound approach when using json. In other words, if I have two collections of unrelated json objects, for example "Users" and "Inventory", would it be ok to have one table with a "Users" column and a "Inventory" column? My concern is

Re: Json table/column design question

On Wednesday, May 22, 2024, Skorpeo Skorpeo wrote: > Hi, > > I was wondering if having unrelated columns in a table is a sound approach > when using json. In other words, if I have two collections of unrelated > json objects, for example "Users" and "Inventory", would it be ok to have > one tabl

Re: Json table/column design question

Greetings, Storing unrelated JSON objects in the same table with distinct columns for each type (e.g., "Users" and "Inventory") is generally not a sound good approach may affect Query Performance and Optimization, Storage Efficiency, scalability and Maintenance, Data Integrity. Recommended approach

Re: Long running query causing XID limit breach

On Thu, May 23, 2024 at 9:00 AM Muhammad Salahuddin Manzoor < salahuddi...@bitnine.net> wrote: > Greetings, > > In high-transaction environments like yours, it may be necessary to > supplement this with manual vacuuming. > > Few Recommendations > > Monitor Long-Running Queries try to optimize. > O

Re: Long running query causing XID limit breach

Greetings, Running `VACUUM table_name;` on a partitioned table will vacuum each partition individually, not the whole table as a single unit. Yes, running `VACUUM table_name;` frequently on tables or partitions with heavy DML is recommended. Regular `VACUUM` does not lock the table for reads or

Re: Long running query causing XID limit breach

On Thu, May 23, 2024 at 10:42 AM Muhammad Salahuddin Manzoor < salahuddi...@bitnine.net> wrote: > Greetings, > > Running `VACUUM table_name;` on a partitioned table will vacuum each > partition individually, not the whole table as a single unit. > > Yes, running `VACUUM table_name;` frequently on

Re: Long running query causing XID limit breach

Also,if i am getting it correct, it means we should not run any transaction (even if it's legitimate one like for e.g. a big Reporting "SELECT" query) beyond 10hrs, as that will end up consuming 10*200million XID per hour= 2billion XID limit saturation and thus causing system failure. Hope my under

Re: Long running query causing XID limit breach

Greetings, Yes, monitoring and alerting for VACUUM operations are crucial. Track VACUUM Duration and Success: SELECT pid, state, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE query LIKE 'VACUUM%' ORDER BY duration DESC; Check Autovacuum Activity: SELECT relnam