Re: Ways to aggregate table info from variable number of schemas

> On Nov 27, 2023, at 10:16, Dominique Devienne wrote: > Which means you can't do a declarative SQL query for those > metadata across projects, since you can't do static / non-dynamic SQL across > schemas. I'm not sure I understand this. Schemas are just namespaces, and all queries have acc

Re: Help understand why DELETE is so slow

> On Dec 1, 2023, at 09:47, Ping Yao wrote: > Can someone help me understand why my simple DELETE query is so slow to run? Based on the plan, you're running PostgreSQL with the Citus extension, and the delay is in Citus-related code. This is probably a question best directed to either the o

Re: Changing a schema's name with function1 calling function2

> On Dec 25, 2023, at 10:44, Adrian Klaver wrote: > Functions with same name in different schemas would need to be dealt with. I think that's the primary use-case (at least, it would be for me), and I don't see a convenient way of doing that. Even a "get OID of current function" function wo

Re: Clarification regarding managing advisory locks in postgresql

> On Jan 24, 2024, at 19:17, Sasmit Utkarsh wrote: > > Need your support on understanding advisory locks in Postgresql and what is > the best practice to have advisory locks and unlocks to work properly when we > have multiple process forked from single process? Advisory locks are a shared

Re: Seeking help extricating data from Amazon RDS Aurora/Postgres

> On Jan 29, 2024, at 11:22, Bill Mitchell wrote: > > Wondering if any of the other members of this LISTSERV have tried migrating > their data off of Amazon RDS Aurora Postgres with success. Any logical-replication based solution (DMS, fivetran, in-core logical replication) will handle the

Re: Best practices for data buffer cache setting/tuning (v15)

> On Jan 29, 2024, at 11:39, David Gauthier wrote: > > Is there a document which makes recommendations on sizing data buffer cache, > tuning options which evict old/unused data in mem, and cache fragmentation > avoidance for a v15.3 DB ? On any modern system, set shared_buffers to 25% of in

Re: Exclude certain application pgaudit logging?

> On Feb 6, 2024, at 10:11, Ron Johnson wrote: > Thus, I'd like to exclude reads from "Postgresql JDBC Driver". (Currently, I > filter that out using "grep -v" in a shell script that runs hourly from cron, > but I find that unsatisfactory.) pgAudit doesn't currently include filters by appl

Re: How to add columns faster

> On Mar 3, 2024, at 11:06, yudhi s wrote: > as the column addition using the traditional "Alter table" command in > postgres looks to be a full table rewrite That's not always (or, really, usually) true. Adding a new column in any recent version of PostgreSQL just alters the system catalog

Re: How to add columns faster

> On Mar 3, 2024, at 11:40, yudhi s wrote: > Thanks for the clarification. In case of adding the column as volatile > default (like current_timestamp function as default) or say adding NOT NULL > column with some conditional population of existing values will be a full > table rewrite. In su

Re: How to add columns faster

> On Mar 3, 2024, at 12:00, Christophe Pettus wrote: > Remember that dropping the NULL constraint afterwards will require a full > table read (although not a rewrite). Sorry, badly put: Adding a NOT NULl constraint afterwards will require a full table read (although not a rewrite).

Re: extract ddl to devops pipeline

> On Mar 6, 2024, at 13:18, Lorusso Domenico wrote: > So there is a way to automatically generate DDL in the right order? Standard pg_dump creates files that are in the proper order, although if you exclusive some tables or schemas from the backup, those might cause errors if references from

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

> On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud > wrote: > So, I ask, have there been any efforts to bring PL/PGSQL to the terminal? Strictly speaking, of course, you can use PL/pgSQL from the terminal already: just use psql, connect to the database, and create and run functions and

Re: update to 16.2

> On Mar 8, 2024, at 00:53, Matthias Apitz wrote: > It does not say definitely that for all other versions a dump/restore is > required. You cannot just replace the binaries to upgrade from an earlier major version to 16.X. The release notes use "a dump/restore (is/is not) required" to indi

Re: Is it possible to keep track of SELECTs?

> On Mar 12, 2024, at 07:15, Dominique Devienne wrote: > So is it possible to track the last time a SELECT was performed on some TABLE? Directly, no. You could periodically sample the various table-level statistics, and conclude that tables that have had some type of scan since the last sca

Fwd: Receipt for PostgreSQL US Invoice #1840

Begin forwarded message:From: Sadie Bella Subject: Fwd: Receipt for PostgreSQL US Invoice #1840Date: March 12, 2024 at 19:13:40 PDTTo: Christophe -- Forwarded message -From: Date: Tue, Mar 12, 2024, 7:07 PMSubject:

Re: Receipt for PostgreSQL US Invoice #1840

> On Mar 12, 2024, at 19:27, Adrian Klaver wrote: > > Oops? Oops. Apologies for the mis-forward.

Re: select results on pg_class incomplete

> On Mar 15, 2024, at 03:30, Thiemo Kellner wrote: > Thanks for the ideas. As I would want to keep it in the database, dblink > would be the way to go. Maybe, I will create a prodedure that creates a view > in the monitor schema accessing the respective databases with union all to > concaten

Re: How to store in hours:minutes:seconds where hours may be bigger than 24

> On Mar 19, 2024, at 19:56, Celia McInnis wrote: > > Thanks for the suggestion, Steve, but No - when I insert 25:17:07::interval > into my table I get 01:17:07 into the table - i.e., it replaces 25 hours by > (25 mod 24) hours or 1 hour, and this is not what I want. I really need the > num

Re: Dropping a temporary view?

> On Mar 20, 2024, at 09:51, Celia McInnis wrote: > > The view is being used in some web query software that multiple people will > be accessing and the contents of the view depend on what the person is > querying, so I think that temporary views or tables are a good idea. There's nothing w

Re: Timing out A Blocker Based on Time or Count of Waiters

> On Mar 22, 2024, at 09:25, Fred Habash wrote: > > Facing an issue where sometimes humans login to a database and run DDL > statements causing a long locking tree of over 1000 waiters. As a workaround, > we asked developers to always start their DDL sessions with 'SET lock_timeout > = 'Xs'

Re: Seq scan vs index scan

> On Mar 22, 2024, at 20:55, arun chirappurath wrote: > I am trying to force query to use indexes using query hints. PostgreSQL does not have query hints. Enabling index scans using parameters doesn't *disable* other types of query nodes. You can disable sequential scans using: SE

Re: Is this a buggy behavior?

On 3/24/24 08:28, Thiemo Kellner wrote: > Sure, my example has lots more side effect than silently do the right thing. I think the point is that it's not really doing anything "silently." You are asking for a PRIMARY KEY constraint on a column, and it's giving it to you. One of the effects (no

Re: Is this a buggy behavior?

> On Mar 24, 2024, at 09:32, Thiemo Kellner wrote: > Am 24.03.2024 um 17:15 schrieb Christophe Pettus: >> I think the point is that it's not really doing anything "silently." You >> are asking for a PRIMARY KEY constraint on a column, and it's giving i

Re: Is this a buggy behavior?

> On Mar 25, 2024, at 02:50, Thiemo Kellner wrote: > My bad. I was under the impression that the create table statement was an > atomic process/transaction with all its bells and whistles for constraints > and keys, instead of a succession of alter statements. That may be a bit judgmental. :

No warning for a no-op REVOKE

Right now, if you do a REVOKE that doesn't actually revoke anything, it works silently. This can be a bit of a foot-gun. For example: CREATE FUNCTION f() RETURNS int as $$ SELECT 1; $$ LANGUAGE sql; REVOKE EXECUTE ON FUNCTION f() FROM lowpriv; Naively, it might be expected that

Re: No warning for a no-op REVOKE

> On Mar 25, 2024, at 07:20, Daniel Gustafsson wrote: > >> On 25 Mar 2024, at 15:09, Tom Lane wrote: > >> My initial reaction is that we should warn only when the command >> is a complete no-op, that is none of the mentioned privileges >> matched. > > That's my gut reaction too, I think t

Re: User roles for gathering performance metrics data

> On Mar 26, 2024, at 22:30, Siraj G wrote: > I am from Oracle background. In Oracle, we grant select_catalog_role or > select any dictionary role to users who want to study performance data. I am > trying to get similar information on the roles or privileges in PgSQL that we > might want to

Re: How to reference a DB with a period in its name ?

> On Mar 29, 2024, at 14:16, David Gauthier wrote > I tried encapsulating the DB name in double quotes (no good), single quotes > (still no good) escaping with '\' (no good), escaping with ".." (no good). This is probably more about the string handling in the API you are using than Postgre

Re: how to check if the license is expired.

> On Mar 31, 2024, at 09:59, Peter J. Holzer wrote: > Is this an acceptable performance penalty per API call? If not, is it > really necessary to check this on every call? Maybe it can be done just > once per session or once per hour. It's probably not required to check it every API call. Two

Re: What is referential_action?

> On Apr 8, 2024, at 06:37, Ron Johnson wrote: > > Four times, the word "referential_action" is used on this page, but it's > never mentioned what the possible referential actions are. They're defined in CREATE TABLE: https://www.postgresql.org/docs/14/sql-createtable.html

Re: Performance degradation after upgrading from 9.5 to 14

> On Apr 17, 2024, at 10:13, Johnathan Tiamoh wrote: > I performed an upgrade from postgresql-9.5 to postgresql-14 and the > performance has degraded drastically. > > Please, is they any advice on getting performance back ? Run: VACUUM (ANALYZE, VERBOSE); More seriously (although

Re: Question regarding how databases support atomicity

> On May 3, 2024, at 20:02, Siddharth Jain wrote: > > > The way I understand this is that if there is a failure in-between, we start > undoing and reverting the previous operations one by one. But what if there > is a failure and we are not able to revert an operation. How is that > situat

UTC is not a time zone?

PostgreSQL 16.3 on MacOS Sonoma. A long-running process (which held a connection open the entire time) failed with: 2024-05-13 09:12:44.719 PDT,"cyan","cyan",35926,"[local]",664214f9.8c56,3,"SELECT",2024-05-13 06:26:17 PDT,3/60,0,ERROR,22023,"invalid value for parameter ""TimeZone"": ""UT

Re: UTC is not a time zone?

> On May 13, 2024, at 10:48, Adrian Klaver wrote: > Yes: > > https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org > > Answer: > > https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us Thanks! Similar, but I don't think it's that. This

Re: UTC is not a time zone?

> On May 13, 2024, at 10:53, Erik Wienhold wrote: > Can you trigger that error with: > > SET timezone = 'UTC'; No, that works correctly: psql (16.3) Type "help" for help. df=> SET timezone = 'UTC'; SET The error popped up during a long-running connection that had issued that SET many (m

Re: UTC is not a time zone?

> On May 13, 2024, at 10:58, Adrian Klaver wrote: > > You sure the timezone file did not get changed under the connection? Yes (at least, nothing happened on the system that would indicate that). The system wasn't touched during the execution (and, as noted, it worked after as well as befo

Re: UTC is not a time zone?

> On May 13, 2024, at 11:07, Adrian Klaver wrote: > > > What does pg_config --configure show for '--with-system-tzdata' ? It's a local compile, and was built without that. As an experiment, I'm just pounding the server with a single connection doing nothing but SET TIMEZONEs repeatedly. S

Re: UTC is not a time zone?

> On May 13, 2024, at 11:17, Tom Lane wrote: > What's causing that I can't say. It doesn't look like we log the > errno anywhere when failing to read a zone file :-( File descriptor exhaustion? (Of course, that would mean something somewhere is leaking them, which is another problem.)

Re: UTC is not a time zone?

> On May 13, 2024, at 11:26, Adrian Klaver wrote: > May not induce the error unless there are parallel workers involved. Indeed. I'll see about pulling together a test case that forces that.

Re: Updating 457 rows in a table

> On May 19, 2024, at 09:54, Rich Shepard wrote: > > Specifically, in the 'people' table I want to change the column 'active' > from false to true for 457 specific person_id row numbers. UPDATE people SET active=true WHERE id IN (...); The ... can either be an explicit list of the ids, or a

Re: Updating 457 rows in a table

> On May 19, 2024, at 11:30, Rich Shepard wrote: > That's a good idea; I can use a predicate to identify the rows to update. > That would be shorter than a long, comma-separated list. Of course, you can probably also shorten the query to: UPDATE people SET active=true WHERE ... Where

Re: pg_dump and not MVCC-safe commands

> On May 20, 2024, at 08:49, PetSerAl wrote: > Basically, you need application cooperation to make > consistent live database backup. If it is critical that you have a completely consistent backup as of a particular point in time, and you are not concerned about restoring to a different proc

Re: Gaps in PK sequence numbers

> On Jun 10, 2024, at 15:57, Rich Shepard wrote: > When I tried inserting new rows in the companies table psql told me that PK > value 2310 already existed. Selecting max(PK) returned 2341. When entering > multiple new rows is there a way to ignore gaps? Strictly speaking, the sequence underly

Re: Gaps in PK sequence numbers [RESOLVED]

> On Jun 10, 2024, at 18:10, Rich Shepard wrote: > Thanks, Christophe. Is there a way to reset the sequence to the maximum > number +1? I don't recall seeing this in the postgres docs but will look > again. The sequence functions are documented here: https://www.postgresql.org/docs/cu

Re: Accommodating alternative column values

> On Jul 2, 2024, at 14:47, Rich Shepard wrote: > Is there a way > to accommodate multiple email addresses other than entering both with a > forward slash separating them in the varchar(64) email address column? If you are absolutely 100% sure there will never be any metadata associated with

Re: Accommodating alternative column values

> On Jul 2, 2024, at 14:57, Rich Shepard wrote: > Using text rather than varchar() is a good suggestion. To be clear, I wasn't suggesting stuffing them all into a text column with a delimiter, but storing them in a text *array* field, each email address one component of the array.

Re: Accommodating alternative column values

> On Jul 2, 2024, at 15:11, Rich Shepard wrote: > This data set is the > only one I've encountered that has a very few multiple email addresses for a > person. That's pretty common out in the world. Just pulling a small dataset I have available, 4+ email addresses per customer happen frequen

Re: Logical replication with temporary tables

> On Jul 2, 2024, at 17:47, Stuart Campbell > wrote: > My question is whether there is some workaround that will let me create > indexes on a temporary table, and also let me drop the temporary table, in a > way that doesn't end up "degrading" replication? (Presumably that means > avoiding

Re: Logical replication with temporary tables

> On Jul 2, 2024, at 18:16, Stuart Campbell > wrote: > > My understanding was that under the hood, AWS uses the logical replication > features that are present in community Postgres. If that's incorrect then I'm > sorry for the off-topic post. Yes, but: The idea of a "degraded" replication

Re: Description field for tables and views

> On Jul 3, 2024, at 12:28, Kent Dorfman wrote: > > Is there any psql function/command to add a description field to a table or > view definition in the system? Allow me to introduce you to my good friend "COMMENT": https://www.postgresql.org/docs/current/sql-comment.html

Can't dump new-style sequences independently from their tables.

Quick example: xof=# CREATE TABLE t1 (id SERIAL PRIMARY KEY); CREATE TABLE xof=# CREATE TABLE t2 (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY); CREATE TABLE xof=# \d+ List of relations Schema | Name| Type | Owner | Persistence | Access meth

Re: Description field for tables and views

> On Jul 3, 2024, at 13:24, Kent Dorfman wrote: > Is it SQL standard or postgres specific? It's not in the SQL standard (at the bottom of each page for each SQL command is a note regarding its relationship with the SQL standard). Other DBMS implement something similar, however.

v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE

Hi, This is more curiosity than anything else. In the v16 role system, is there actually any reason to grant membership in a role to a different role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role granted membership gain any ability it didn't have before in that case?

Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE

> On Jul 8, 2024, at 12:58, David G. Johnston > wrote: > That scenario is allowed but provides no useful in-server behavior. That was my conclusion as well. Thanks!

Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE

> On Jul 8, 2024, at 13:25, Laurenz Albe wrote: > I didn't test it, but doesn't that allow the member rule to drop objects owned > be the role it is a member of? No, apparently not.

Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE

> On Jul 8, 2024, at 13:29, Christophe Pettus wrote: > > > >> On Jul 8, 2024, at 13:25, Laurenz Albe wrote: >> I didn't test it, but doesn't that allow the member rule to drop objects >> owned >> be the role it is a member of? > > No

Re: PostgreSQL Active-Active Clustering

> On Jul 15, 2024, at 12:06, Sarkar, Subhadeep wrote: > > • Does the Community edition of PostgreSQL provide NATIVE active-active > high availability clustering with objectives of scalability, load balancing > and high availability without using any extensions or external components or >

Re: PostgreSQL Active-Active Clustering

> On Jul 15, 2024, at 12:06, Sarkar, Subhadeep wrote: > > • Does the Community edition of PostgreSQL provide NATIVE active-active high > availability clustering with objectives of scalability, load balancing and > high availability without using any extensions or external components or > u

Re: Qualifying use of separate TABLESPACES (performance/functionality)

> On Jul 16, 2024, at 21:45, imndl...@gmx.com wrote: > Or, does Postgres expect to be able to access any media however it wants > (i.e., R/w), regardless of the expected access patterns of the data stored > there? Well, yes and no. PostgreSQL will not respond well to having media that is liter

Re: Semantic cache capability for Postgresql db

> On Jul 12, 2024, at 06:18, pradeep t wrote: > Shall I use the Postgresql database for a semantic cache like the Redis > semantic cache? If you mean this feature: https://redis.io/docs/latest/integrate/redisvl/user-guide/semantic-caching/ ... there is no direct equivalent in Postg

Re: data checksums

> On Aug 6, 2024, at 08:11, bruno vieira da silva wrote: > > so my question is why data checksums aren't enabled by default on pg? At this point, mostly historical reasons. They're also superfluous if your underlying file system or storage hardware does storage-level corruption checks (whi

Re: data checksums

> On Aug 6, 2024, at 19:45, Laurenz Albe wrote: > I am surprised by that. Would you say that most storage systems will happily > give you a > garbage block if there was a hardware problem somewhere? "Most" is hard for me to judge. HDDs can have uncorrected and undetected errors, definitely

Re: ANALYZE on partitioned tables vs on individual partitions

> On Aug 6, 2024, at 21:13, Michael Harris wrote: > > 1. What is the consequence of not having good statistics on partitioned > table level, if you do have good statistics on the partition level? The child partitions are just tables, and all of the same issues that can arise from bad stat

Re: Vacuum full connection exhaustion

> On Aug 7, 2024, at 10:34, Costa Alexoglou wrote: > > Hey folks, > > I noticed something weird, and not sure if this is the expected behaviour or > not in PostgreSQL. > > So I am running Benchbase (a benchmark framework) with 50 terminals (50 > concurrent connections). > There are 2-3 add

Re: Vacuum full connection exhaustion

> On Aug 8, 2024, at 21:15, Ron Johnson wrote: > > "I see a lock, so let's cause another one!" That's crazy. It's more "Oh, look, I need a connection to service this web request, but my pool is empty, so I'll just fire up a new connection to the server," lather, rinse, repeat. Pretty comm

Re: Soluton on Lock:extend issue

> On Aug 10, 2024, at 09:52, Durgamahesh Manne > wrote: > Lock:extend (version 14.11) PostgreSQL version 16 contains improvements that significantly reduce the amount of contention on extend locks; upgrading will almost certainly help.

Re: Foreign Data Wrappers

> On Sep 6, 2024, at 17:55, Gus Spier wrote: > If I understand the concepts correctly, FDW not only makes other databases > available, FDW also offers access to .csv files, plain text, or just about > anything that can be bullied into some kind of query-able order. There two parts to FDWs: T

Overlapping values (?) in multi-column partitioned tables

Hi, I am clearly not understanding something. Consider: > xof=# create table t (pk bigint not null, ts timestamp not null) partition by > range (ts, pk); > CREATE TABLE > xof=# create table t1 partition of t for values from > ('2024-01-01'::timestamp, minvalue) to ('2024-02-01'::timestamp, max

Re: Overlapping values (?) in multi-column partitioned tables

gt; by range (ts, pk); >> CREATE TABLE >> xof=# create table t1 partition of t for values from >> ('2024-01-01'::timestamp, minvalue) to ('2024-01-31'::timestamp, maxvalue); >> CREATE TABLE >> xof=# create table t2 partition of t for values from

Re: Overlapping values (?) in multi-column partitioned tables

> On Sep 10, 2024, at 15:57, Laurenz Albe wrote: > So the upper limit of the first partition is strictly bigger than the lower > end > of the second partition. > > "record" types have the same lexicographical sorting order as ORDER BY > clauses. Ah, OK, got it. I was thinking of them as a

Re: Overlapping values (?) in multi-column partitioned tables

> On Sep 10, 2024, at 16:02, David Rowley wrote: > > (it seems strange to always have MINVALUE and MAXVALUE as the range. > I'm guessing that was just an example.) Correct.

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

> On Sep 12, 2024, at 06:58, Greg Sabino Mullane wrote: > > But if it works for you, go ahead. As Tom said, it will work 95% of the time. > But it will break things that should work, and it will not prevent the > ability to get the information in other ways. To be clear, we never recommend

Re: IO related waits

> On Sep 16, 2024, at 13:24, veem v wrote: > Architecture team is suggesting to enable asynch io if possible, so that the > streaming client will not wait for the commit confirmation from the database. > So I want to understand , how asynch io can be enabled and if any downsides > of doing t

Re: Is it normal to have too many idle processes in postgresql cluster?

t the "idle" state means. You can consider using a pooler such as pgbouncer to reduce the number of connections the clients have open, although 200 open connections is nothing concerning. -- -- Christophe Pettus x...@thebuild.com

hot_standby_feedback implementation

When a replica sends a hot_standby_feedback message to the primary, does that create an entry in the primary's lock table, or is it flagged to autovacuum some other way?

Re: hot_standby_feedback implementation

> On Jun 15, 2021, at 17:30, Peter Geoghegan wrote: > It pretty much works by making the WAL sender process on the primary > look like it holds a snapshot that's as old as the oldest snapshot on > the replica. > > A replica can block VACUUM on the primary *directly* by holding a > table-level

archive_command / single user mode

Does archive_command run in single user mode? This isn't an actual situation at the moment, but for runbook / disaster planning purposes, I was curious.

Re: archive_command / single user mode

> On Aug 9, 2021, at 18:03, David G. Johnston > wrote: > > On Mon, Aug 9, 2021 at 4:17 PM Christophe Pettus wrote: >> Does archive_command run in single user mode? This isn't an actual >> situation at the moment, but for runbook / disaster plannin

lower() and unaccent() not leakproof

Hi, lower() and unaccent() (and most string functions) are not marked as leakproof. Is this due to possible locale / character encoding errors they might encounter?

statement_timeout vs DECLARE CURSOR

Hi, We've encountered some unexpected behavior with statement_timeout not cancelling a query in DECLARE CURSOR, but only if the DECLARE CURSOR is outside of a transaction: xof=# select version(); version

Re: statement_timeout vs DECLARE CURSOR

> On Sep 27, 2021, at 10:42, Christophe Pettus wrote: > We've encountered some unexpected behavior with statement_timeout not > cancelling a query in DECLARE CURSOR, but only if the DECLARE CURSOR is > outside of a transaction: A bit more poking revealed the reason: The

Re: Streaming replication versus Logical replication

> On Nov 4, 2021, at 12:16, Alanoly Andrews wrote: > > Thanks, Ninad, for the response. > So, am I to understand that when there is a long-running query on the > subscriber, the vacuumed data updates from the publisher are held over on the > subscriber until the query completes? If so, where

Re: Question: Is it possible to get the new xlog position after query execution?

> On Nov 7, 2021, at 19:42, Oleg Serov wrote: > How would you accomplish this otherwise? Synchronous commit. It sounds like you are attempting to build the same kind of guarantees that synchronous commit provides, only in the application by polling LSNs. It might be best just to use synchr

Re: Question: Is it possible to get the new xlog position after query execution?

> On Nov 8, 2021, at 10:03, Oleg Serov wrote: > That does not seem to be feasible for our application. Using synchronous > commit affects performance and really makes replication not really useful... > What we want to achieve is to have a consistent DB state across all > connections for mast

Re: Question: Is it possible to get the new xlog position after query execution?

> On Nov 8, 2021, at 11:47, Oleg Serov wrote: > I'm sorry, when I mean users, I mean application end-user (e.g. > j...@doe.com), not the postgres role. You can also set it as a session-level variable. :-) SET synchronous_commit = 'on'; You could flip it on for the application-le

Re: Advice on efficiently logging outputs to PostgreSQL

> On Oct 15, 2024, at 07:17, Dominique Devienne wrote: > Am I worrying too much? :) Probably. :-) The main things I'd worry about is: 1. What's the ratio of log lines to database updates? You want this to be as high as usefully possible, since in effect you are doing write amplification by

Re: PGBouncer - Load balancing options

Hello, > On Oct 10, 2024, at 03:02, Ravi Varma Addala wrote: > > Hello Team,I wanted to reach out to provide some insight into a request from > my customer regarding their multiple PostgreSQL Flex instances with > PGbouncer. Currently, they are utilizing an Azure Load Balancer which > distrib

Re: Error Building Postgres 17.0 from Source

> On Oct 4, 2024, at 09:35, Corbin Cavolt wrote: > > Hi, > > I'm having a problem building Postgres 17.0 from source. I'm able to build > all the 16.x versions just fine; I'm running into an error specifically with > version 17.0. I use a devcontainer for development with my own postgres >

Re: Error Building Postgres 17.0 from Source

> On Oct 4, 2024, at 12:34, Tom Lane wrote: > > If so, the difference is that up to v16 we > included prebuilt HTML docs in the tarballs, but as of v17 we don't. > So "make world" now triggers a local docs build where before it > didn't. Ah! That would be it.

Re: What are best practices wrt passwords?

> On Oct 16, 2024, at 09:47, Tom Lane wrote: > I believe it depends on your platform --- some BSDen are pretty > permissive about this, if memory serves. On a Linux box it seems > to work for processes owned by yourself even if you're not superuser. I just tried it on an (admittedly kind of o

Re: Question on indexes

> On Oct 10, 2024, at 11:49, sud wrote: > > Hi, > I have never used any 'hash index' but saw documents in the past suggesting > issues around hash index , like WAL doesnt generate for "hash index" which > means we can't get the hash index back after crash also they are not applied > to repl

Re: Error Building Postgres 17.0 from Source

> On Oct 4, 2024, at 12:05, Tom Lane wrote: > > Yeah, that's what it looks like. I'm a bit confused though because > 16.x should have failed the same way: building our docs without local > DTDs has failed for well over a year now [1]. To add confusion to the fire, I was successfully buildin

Setting "ucs_basic" as the default database collation

Is there a way to specify "ucs_basic" (or the other "standard collations" [1]) collation as the default database collation at database creation time, rather than on a per-column or per-operation basis? [1] https://www.postgresql.org/docs/current/collation.html#COLLATION-MANAGING-STANDARD

Re: Setting "ucs_basic" as the default database collation

> On Oct 27, 2024, at 13:56, Ron Johnson wrote: > > The CREATE DATABASE statement has an ENCODING option. Does that work with > 'ucs_basic'? No: xof=# create database test encoding=ucs_basic; ERROR: ucs_basic is not a valid encoding name LINE 1: create database test encoding=ucs_basic;

Re: Can we directly upgrade postgresql from 13 to 15.4

> On Nov 8, 2024, at 10:12, jayesh thakare wrote: > Can we upgrade postresql from 13 to 15.4 directly? > Can we upgrade postgresql from 13 to 16 directly? Yes. > Ideally how many major version can we jump to from older version ? There's no practical limit, assuming the old version is not an

Re: Repeatable Read Isolation Level "transaction start time"

> On Sep 25, 2024, at 13:49, Greg Sabino Mullane wrote: > BEGIN ISOLATION MODE REPEATABLE READ SNAPSHOT NOW; This might well be a failure of imagination on my part, but when would it pragmatically matter that the snapshot is taken at the first statement as opposed to at BEGIN?

Re: Regarding use of single column as primary key on partitioned table

> On Sep 27, 2024, at 21:25, Durgamahesh Manne > wrote: > > Can't we use primary key on singal column(betid) on partitioned table rather > than using composite key (placedon,betid)? No. Any unique constraint on a partitioned table must include the partition key, including a primary key co

Re: PgBackRest and WAL archive expiry

> On Sep 19, 2024, at 22:46, KK CHN wrote: > > 1. In the EPAS serverI have postgres.conf with > archive_command = 'pgbackrest --stanza=EMI_Repo archive-push %p && cp %p > /data/archive/%f' > > The problem is that the /data/archive folder is growing within a few days > to 850GB

Re: CREATE DATABASE command concurrency

> On Sep 17, 2024, at 14:52, Wizard Brony wrote: > > What are the concurrency guarantees of the CREATE DATABASE command? For > example, is the CREATE DATABASE command safe to be called concurrently such > that one command succeeds and the other reliably fails without corruption? The concern

Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication

> On Nov 24, 2024, at 08:51, Subhash Udata wrote: > However, my concern lies in the fact that we are working with production > servers, where downtime is not acceptable. There is no way to upgrade community PostgreSQL, either to a new minor version or a new major version, with absolute zero

<    1   2   3   4   >