Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Fri, Aug 22, 2025 at 11:55:18AM -0400, Tom Lane wrote: > hubert depesz lubaczewski writes: > > On Fri, Aug 22, 2025 at 11:21:22AM -0400, Tom Lane wrote: > >> Interesting. That futex call is presumably caused by interaction > >> with some other process within the s

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Fri, Aug 22, 2025 at 11:55:18AM -0400, Tom Lane wrote: > Nonetheless, I'm suspecting an interaction with the startup process, > because there just isn't that much else that this process could be > needing to deal with. Can you try strace'ing both the process doing > the test query and the start

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Fri, Aug 22, 2025 at 11:21:22AM -0400, Tom Lane wrote: > hubert depesz lubaczewski writes: > > I got repeatable case today. Is is breaking on its own everyy > > ~ 5 minutes. > > Interesting. That futex call is presumably caused by interaction > with some other pr

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Fri, Aug 22, 2025 at 08:39:21AM -0700, Adrian Klaver wrote: > On 8/22/25 08:30, hubert depesz lubaczewski wrote: > > On Fri, Aug 22, 2025 at 11:21:22AM -0400, Tom Lane wrote: > > > hubert depesz lubaczewski writes: > > > > I got repeatable case today. Is

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Fri, Aug 22, 2025 at 05:07:09PM +0200, Pavel Stehule wrote: > > Any idea on what could it be? > Few years ago I had a similar experience - some sec mystic lags - the > problem was in virtualization. While I can't discount this idea, after all it is aws ec2, so virtual boxes, what makes me wonde

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Fri, Aug 22, 2025 at 07:33:03AM -0700, Adrian Klaver wrote: > On 8/22/25 05:37, hubert depesz lubaczewski wrote: > > On Thu, Aug 21, 2025 at 11:17:27AM -0700, Adrian Klaver wrote: > > > Have you looked at?: > > > https://www.pgbouncer.org/changelog.html#pgbouncer-12

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Thu, Aug 21, 2025 at 07:38:34PM +0100, Chris Wilson wrote: > If all your queries are coming through pgBouncer, and only those hang (the > server itself responds if you connect directly to it), then it might be > this pgBouncer issue: > > https://github.com/pgbouncer/pgbouncer/issues/1054 > > A

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Thu, Aug 21, 2025 at 11:17:27AM -0700, Adrian Klaver wrote: > Have you looked at?: > https://www.pgbouncer.org/changelog.html#pgbouncer-124x > To see if anything stands out. > Then there is: > https://www.pgbouncer.org/config.html#max_prepared_statements > The below may also be worth looking at:

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Thu, Aug 21, 2025 at 08:59:03AM -0700, Adrian Klaver wrote: > Getting to the bottom of the bag of ideas: > Have you looked at the OS system log for the time period involved? Yes. Mostly dmesg. Nothing interesting logged around the time. > You mentioned this seemed to involve PREPARE and DISCAR

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Thu, Aug 21, 2025 at 08:04:25AM -0700, Adrian Klaver wrote: > > > > > "For ~ 1 second there are no logs going to log (we usually have at > > > > > 5-20 > > > > > messages logged per second), no connection, nothing. And then we get > > > > > bunch (30+) messages with the same milisecond time." >

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Thu, Aug 21, 2025 at 12:41:44PM +0100, Thom Brown wrote: > Ah, yeah I meant transparent hugepage: > cat /sys/kernel/mm/transparent_hugepage/enabled > This should show it being set as "never". Ah. Sorry, couldn't decipher. Yes, it's "never". > > # grep -oP '^2025-08-19 22:09:2\d\.\d+ UTC' > >

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Wed, Aug 20, 2025 at 10:45:13AM -0700, Adrian Klaver wrote: > On 8/20/25 09:08, hubert depesz lubaczewski wrote: > > On Wed, Aug 20, 2025 at 08:14:47AM -0700, Adrian Klaver wrote: > > > Hmm. > > > > > > From initial post: > > > > > > &quo

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Wed, Aug 20, 2025 at 06:30:00PM +0100, Thom Brown wrote: > Do you have THP enabled? Can you use mpstat and see what %steal shows as? Sorry, what is "THP"? I tried searching for "what is thp", and most common search results are related to some chemical compound. mpstat, yes, we have it. Let me

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Wed, Aug 20, 2025 at 11:24:29AM -0600, Rob Sargent wrote: > > On Wed, Aug 20, 2025 at 08:14:47AM -0700, Adrian Klaver wrote: > >> Hmm. > >> > >> From initial post: > >> > >> "For ~ 1 second there are no logs going to log (we usually have at 5-20 > >> messages logged per second), no connection,

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Wed, Aug 20, 2025 at 08:14:47AM -0700, Adrian Klaver wrote: > Hmm. > > From initial post: > > "For ~ 1 second there are no logs going to log (we usually have at 5-20 > messages logged per second), no connection, nothing. And then we get > bunch (30+) messages with the same milisecond time." >

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Tue, Aug 19, 2025 at 08:25:26PM +, Scot Kreienkamp wrote: > Synchronous commit? Or asynchronous? Isn't this potential problem on primary, and not replica? Anyway, synchronous commit is set to "on", and "synchronous_standby_names" is empty. Best regards, depesz

Re: Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

On Tue, Aug 19, 2025 at 11:39:03AM -0700, Adrian Klaver wrote: > > Every now and then (usually every 3-5 minutes, but not through the whole > > day), we see situations where every query suddently takes ~ 1 second. > Given the subject line, what you are reporting is happening on the replica, > corre

Streaming replica hangs periodically for ~ 1 second - how to diagnose/debug

Hi, we have following situation: Pg 14.17 (yes, I know, but it can't be upgraded now/soon), on Ubuntu focal, in AWS cloud on EC2 server using arm64 architecture. All works, is fine. Every now and then (usually every 3-5 minutes, but not through the whole day), we see situations where every query

Re: Postgres query tool not working when I right click

On Tue, May 20, 2025 at 02:06:33PM -0400, Tessa Niebel wrote: > I really need to get PostgresSQL working because I’m a college student > using this for class. I have never worked with PostgresSQL before please > let me know what I need to do as soon as possible. 1. Doing photo of screen is ... wel

Re: psql and regex not like

On Thu, Mar 06, 2025 at 04:37:56AM -0500, Ron Johnson wrote: > This statement runs great from the psql prompt. Does exactly what I want. > select datname from pg_database WHERE datname !~ 'template|postgres' ORDER > BY datname; > But it doesn't work so well from the bash prompt. Not escaping the

Re: Using psql's \prompt command

On Thu, Jan 30, 2025 at 01:47:59PM -0800, Rich Shepard wrote: > # \i person_view.sql > Enter person_nbr: 468 > psql:person_view.sql:9: ERROR: column "store" does not exist > LINE 3: where person_nbr = store >^ > What's the correct syntax for the \prompt? prompt is ok.

Re: On enforcing default column value, AKA "Bloody nulls"

On Thu, Jan 16, 2025 at 12:14:54PM +, Wiwwo Staff wrote: > Hi all! > In a scenario like this: > > > =# create table tab1(text1 text default 'from table'); > > > > =# create procedure ins_tab1(p_text1 text default 'from proc') language > sql as > > -# $$ > > $# insert into tab1(text1) values (p

Re: About PostgreSQL Query Plan

On Mon, Jan 13, 2025 at 08:01:56PM +0300, Eşref Halıcıoğlu wrote: > Yes, you are right; it seems that only 4 batches had data changes. However, > the query also accessed other batches and then removed > them again. What could be the reason for this and how can it be solved? >   > Obviously, I woul

Re: About PostgreSQL Query Plan

On Mon, Jan 13, 2025 at 07:48:09PM +0300, Eşref Halıcıoğlu wrote: > Hello, >   > Yes, you are right; this query is not a SELECT, it is an UPDATE query, there > was a mistake in expressing it here. The columns I want > to update here only operate on data from the last 3 months time interval. >   >

Re: About PostgreSQL Query Plan

On Mon, Jan 13, 2025 at 05:26:09PM +0300, Eşref Halıcıoğlu wrote: > Hello, >   > I have a query in PostgreSQL and I want this query to retrieve only data from > the last 3 months. However, when I examine the query > plan, I see that all partitions are listed. Please note that your explain is for

Re: pg_repack and locks

On Mon, Jan 13, 2025 at 12:40:06PM +, nicolas wrote: > Hello everyone, > > We are using postgresql v12 and added the pg_repack package > > Since I cannot stop other process, I use the “--no-kill-backend” and > Pg_repack will wait indefinitly until pg_repack get the lock > > I get sometimes

Re: PostgreSQL Log Info

On Fri, Nov 22, 2024 at 01:02:27PM +0530, Jethish Jethish wrote: > Hi David, > > If an select query is fired I need the query returned values needs to be > logged in my PostgreSQL log file. > > > For example if a select query returns 5 rows I need the same in the log > file Please note that you

Re: psql help

On Fri, Jul 05, 2024 at 03:54:56AM +, Murthy Nunna wrote: > Sorry, there is no problem with the following statement and the environment > variable. It works fine. But it terminates only one PID due to LIMIT 1. I > want to terminate all pids that meet this criteria. If I remove LIMIT 1, > pg_

Re: Does trigger only accept functions?

On Wed, Jun 12, 2024 at 12:50:27AM +0530, veem v wrote: > My apology, if interpreting it wrong way. It doesn't make much difference > though, but do you mean something like below? if you really have totally different structures across all tables, and you don't want to use pgaudit (which is the bes

Re: Does trigger only accept functions?

On Wed, Jun 12, 2024 at 12:19:55AM +0530, veem v wrote: > CREATE OR REPLACE FUNCTION log_deletes() > RETURNS TRIGGER AS $$ > BEGIN > IF TG_TABLE_NAME = 'source_table1' THEN > INSERT INTO delete_audit1 ( col1, col2, col3) > VALUES (OLD.col1, OLD.col2, OLD.col3); > ELSIF TG_TA

Re: Does trigger only accept functions?

On Tue, Jun 11, 2024 at 12:47:14AM +0530, veem v wrote: > to be called from ~50 triggers? or any other better approach exists to > handle this? pgaudit extension? Or just write all the changes to single table? Or use dynamic queries that will build the insert based on the name of table the event

Re: \dt shows table but \d says the table doesn't exist ?

On Fri, May 03, 2024 at 04:58:26PM -0400, David Gauthier wrote: > Soo... what am I missing ? > owner is "cron_user". \dt shows cron_user is the owner of the table. Magnus already helped you, but you might want to check this: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_cas

Re: Password forgotten

On Tue, Apr 23, 2024 at 08:14:15AM -0400, Arbol One wrote: > Hello. > In my Debian box, after entering this command to psql-16, *psql -h localhost > -U postgres* You might want to read https://www.depesz.com/2008/11/28/recovering-lost-postgresql-password/ Best regards, depesz

Re: Postgresql went crazy and flooded all the SSD

On Mon, Nov 06, 2023 at 01:11:31PM +0200, Gabriel Dodan wrote: > Not sure exactly what happened but Postgresql flooded all the available SSD > space and obviously crashed. It has written a lot of data in the pg_wal > folder. Most likely it was caused by replication. The postgresql instance > that c

Re: psql \du no more showing "member of" column

On Fri, Oct 13, 2023 at 01:39:17PM +0200, Luca Ferrari wrote: > at least, as it is shown by `psql -E`. > I wonder why this information has been removed, I'm not able to find > this in the documentation. Release notes show: https://why-upgrade.depesz.com/show?from=15.4&to=16&keywords=%5Cdu > Add

Re: Problem perhaps after upgrading to pgadmin4 7.4

On Thu, Jul 13, 2023 at 01:20:03PM +0200, Carl Erik Eriksson wrote: > If I enter a query like select count(*) from table_1I get a correct > response from the server > If I enter select * from table_1 I get an error message that I do not > understand: > Error Message:missing FROM-clause entry

Re: How to manipulate field in New record

On Wed, Jun 07, 2023 at 02:12:58PM +0200, Lorusso Domenico wrote: > Hello, > Looking for a global solution I've write e trigger function that as > optional parameter (argv[0]) receive the name of a specific parameter. > > My need is to get the filed from NEW and OLD record manipulate and set back

Re: Is there a bug in psql? (SELECT ''';)

On Wed, May 31, 2023 at 03:17:14PM +0800, Wen Yi wrote: > Hi team, > when I learn the postgres, I try to store the ' into the database, > > but something unexpected happend. > > > postgres=# CREATE TABLE test (str varchar); > CREATE TABLE > postgres=# INSERT INTO test values ('''); > postgres'#

Re: syntax pb

On Tue, May 30, 2023 at 05:53:30PM +0200, Marc Millas wrote: > Thanks Adrian, but if the query becomes more complex, for example with a > few joins more, then even casting doesn't work. > This comes from a prod environment and even casting NULLs (which is more > than strange, BTW) generates absurd

Re: How can I change replication slot's restart_lsn from SQL?

On Wed, May 17, 2023 at 08:16:41AM +0200, Laurenz Albe wrote: > On Tue, 2023-05-16 at 19:35 +0200, hubert depesz lubaczewski wrote: > > > I'm working on a workaround for a bug in Pg > > > (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com) > >

Re: How can I change replication slot's restart_lsn from SQL?

On Tue, May 16, 2023 at 04:23:02PM +0200, hubert depesz lubaczewski wrote: > Hi, > I'm working on a workaround for a bug in Pg > (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com) > I want to create replication slot, and advance is manually, keeping

How can I change replication slot's restart_lsn from SQL?

Hi, I'm working on a workaround for a bug in Pg (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com) I want to create replication slot, and advance is manually, keeping it always a bit lagging behind real replication slot. I can create slot, no problem: select pg_create_logic

Re: PL/pgSQL doesn't support variables in queries?

On Wed, May 03, 2023 at 10:25:55PM +1000, J.A. wrote: > Heya folks :) > > ms-sql person here migrating over to pgsql. One of the first thing's I > noticed with pgsql (or more specifically, PL/pgSQL) is that it doesn't > support "variables" in a query? > > for example, here's some T-SQL: > > DECL

Re: Getting the exact SQL from inside an event trigger

On Thu, Mar 02, 2023 at 11:12:37AM +, Joe Wildish wrote: > We are using event triggers to capture DDL for subsequent replay on a logical > replica. This might be a bit different answer from what you expect, but have you seen pgl_ddl_deploy project? Best regards, depesz

Re: How to create directory format backup

On Wed, Feb 08, 2023 at 05:00:10PM +0200, Andrus wrote: > Hi! > > Creating backup in directory format using > >     pg_dump -f "sba" -Fdirectory --jobs=32 --verbose sba > > throws error > >     pg_dump: error: could not stat file "sba/282168.data.gz": value too > large > > How to fix it ? > >

Re: A Small psql Suggestion

On Tue, Jan 31, 2023 at 11:17:16AM -0500, Raymond Brinzer wrote: > Greetings, > > There is (for me) a small speed bump in psql. I think it's worth > mentioning, minor though it is, because psql is such a polished tool > generally, and because it's something which affects me many, many times a > d

Re: Best Open Source OS for Postgresql

On Tue, Jan 31, 2023 at 02:09:40PM +0100, Marc Millas wrote: > if you do check the debian postgis repo, you ll find that its NOT possible > to choose a postgis version. > its possible for postgis 2.4 and 2.5, then ALL 3.x versions are > inaccessible but one, that did change from time to time. > (yo

Re: Best Open Source OS for Postgresql

On Tue, Jan 31, 2023 at 01:18:08PM +0100, Marc Millas wrote: > Did you check postgis debian repo? ?? Not sure why: 1. you ask me that 2. you ask me that off list but no, i haven't. depesz

Re: Best Open Source OS for Postgresql

On Tue, Jan 31, 2023 at 09:02:53AM +0100, Giovanni Biscontini wrote: > Hello everyone, > we're looking for a Open Source alternative to Rhel for our VM server > dedicated to Postgresql (14->15) installations. We're testing Alma, Rocky, > and Oracle distributions as they're compatible with Rhel pac

Re: Disallow execution of shell commands from psql

On Tue, Jan 10, 2023 at 07:01:24PM +0100, Wiwwo Staff wrote: > Hi! > Happy new (gregorian calendar) year! > > Somehow related to the proposal of having a `psql --idle` option, is there > a way to disallow the command `\!` (and anything of the likes in psql? > > Sure, I can set the SHELL env var a

Re: Exact same output - pg_stat_statements

On Tue, Jan 03, 2023 at 01:52:17PM +0800, Julien Rouhaud wrote: > Resetting the data adds some noticeable overhead as newly added entries will > need to generate a normalize query string and so on. What most people do is > taking regular snapshots of pg_stat_statements (and other stats) view and t

Re: Exact same output - pg_stat_statements

On Fri, Dec 30, 2022 at 11:04:59AM -0500, Rushikesh socha wrote: > Hi, Whenever I am running the below query on one of my Azure PostgreSQL > PaaS instances I am getting exact same output. I feel it shows old > information but as far as i know pg_stat_statements only shows current > information and

Re: trouble writing plpgsql

On Thu, Dec 22, 2022 at 11:37:22AM -, haman...@t-online.de wrote: > I want to make a function to parsetext and return key-value pairs > create or replace function extractinfo (text) returns table (key char[1], val > text) Please don't use char datatype: https://wiki.postgresql.org/wiki/Don't

How to handle logical replication 12->14, when our max_replication_slots gets overrun by inactive sync workers

Hi, I reported a bug aobut it earlier, and from what I know it has been fixed, but new release will come later. For now I have this situation: 1. max_replication_slots is 50 2. database to replicate has 67 schemas, and ~ 26k tables. 3. schemas are split into 5 slots 4. pg14 side has max_sync_work

Re: [BeginnerQuestion]Why these is 6 rows in my SELECT statement?

On Thu, Sep 08, 2022 at 04:37:00PM +0800, BeginnerC wrote: > Hello community, > I am checking my pg_stat_activity view,but something confused me. > Just like this: > > postgres=# SELECT wait_event_type, wait_event FROM pg_stat_activity; > wait_event_type | wait_event > -+--

Re: How to check if checkpoint is finished in sql script?

On Mon, Sep 05, 2022 at 04:55:28PM +0800, Yi Sun wrote: > How to check if the checkpoint is finished in sql script please? We know > that the log file will show it, but we want to check it in sql then can > easily be used by ansible, thanks Well, if the command "checkpoint" finished, and returned,

Re: CREATE SUBSCRIPTION not picking up .pgpass while psql does

On Wed, Aug 31, 2022 at 04:26:22PM +0300, Kristjan Mustkivi wrote: > And as said, the psql utility has no problems finding the .pgass where > it is. If I lie to it about the pgpass location i.e by giving > passfile=/root/.pgpassx it will ask for password. of course it doesn't have problem, because

Re: CREATE SUBSCRIPTION not picking up .pgpass while psql does

On Wed, Aug 31, 2022 at 04:03:31PM +0300, Kristjan Mustkivi wrote: > Hello, > > I do not understand why CREATE SUBSCRIPTION does not pick up .pgpass > (when psql does): > > root@pg.newtest:/# psql 'host=pg.oldtest port=5432 user=pg_replication > dbname=oldtest' > oldtest=# \q > > root@pg.newtest

Re: Unable to Create or Drop Index Concurrently

On Thu, Aug 18, 2022 at 01:57:48PM +0800, Abdul Qoyyuum wrote: > Hi list, > > We have a running Master-Slave High Availability set up. Naturally, we > can't run any changes on read-only databases on slave, so we have to do it > on the master node. > > When trying to run the following command: >

Re: Is it possible to keep indexes on different disk location?

On Thu, Aug 18, 2022 at 08:39:27AM +0200, W.P. wrote: > Is it possible to move  DB tables etc to this internal storage (sure > connection) and put only    indexes on USB  HDD? Sure. There is a thing called tablespace, which is basically, directory where files for db objects reside. You can specif

Re: Postgres question

On Wed, Aug 17, 2022 at 10:32:26AM +0100, ajay venki wrote: > I am new to PostgreSQL and i have a general question to clarify. is this > the right forum or the mail address to post my questions? Yes, this is the right place. Best regards, depesz

Re: Would it be possible to add functions to tab-completion in psql?

On Tue, Aug 16, 2022 at 10:10:55AM -0400, Tom Lane wrote: > There is nothing principled about assuming that the first word > after SELECT is a function name. It'd be even less principled to > provide tab completion only for function names beginning with > "pg_". So this idea seems like a wart rat

Re: Would it be possible to add functions to tab-completion in psql?

On Tue, Aug 16, 2022 at 08:51:49AM -0500, Ron wrote: > On 8/16/22 08:01, hubert depesz lubaczewski wrote: > > On Tue, Aug 16, 2022 at 07:42:27AM -0500, Ron wrote: > > > On 8/16/22 07:28, hubert depesz lubaczewski wrote: > > > > Hi, > > > > As a dba I h

Re: Would it be possible to add functions to tab-completion in psql?

On Tue, Aug 16, 2022 at 09:55:34PM +0800, Julien Rouhaud wrote: > On Tue, Aug 16, 2022 at 02:28:49PM +0200, hubert depesz lubaczewski wrote: > > Hi, > > As a dba I have to, very often, query system functions, starting with > > pg_last_xact_replay_timestamp and pg_current_wal_

Re: Would it be possible to add functions to tab-completion in psql?

On Tue, Aug 16, 2022 at 07:42:27AM -0500, Ron wrote: > On 8/16/22 07:28, hubert depesz lubaczewski wrote: > > Hi, > > As a dba I have to, very often, query system functions, starting with > > pg_last_xact_replay_timestamp and pg_current_wal_lsn. > > > > Would

Would it be possible to add functions to tab-completion in psql?

Hi, As a dba I have to, very often, query system functions, starting with pg_last_xact_replay_timestamp and pg_current_wal_lsn. Would it be possible/hard/expensive, to change tab-completion so that: select pg_ would work? Best regards, depesz

Re: Allow user to connect to replicas only

On Thu, Aug 04, 2022 at 03:42:00PM +0200, Wiwwo Staff wrote: > Is there any way to create a user allowed to connect to a/any read replica > only, as in "not allowed to connect to primary"? Sure. Modify pg_hba.conf on primary to disallow connections as this user. Best regards, depesz

Re:

On Tue, Jul 26, 2022 at 10:48:47AM -0700, Adrian Klaver wrote: > On 7/26/22 9:29 AM, Ron wrote: > > On 7/26/22 10:22, Adrian Klaver wrote: > > > On 7/26/22 08:15, Rama Krishnan wrote: > > > > Hi Adrian > > > > > > > > > > > > > What is size of table? > > > > > > > > I m having two Database exam

Re: operator does not exist: text = bytea

On Wed, Jul 20, 2022 at 03:02:13PM +0530, Karthik K L V wrote: > *Caused by: org.postgresql.util.PSQLException: ERROR: operator does not > exist: text = bytea Hint: No operator matches the given name and argument > types. You might need to add explicit type casts. Position: 1037* > Could you plea

Re: PLPGSQL - extra column existence in trigger

On Sat, May 07, 2022 at 07:41:44AM -0700, David G. Johnston wrote: > No. I’d probably approach this by generically converting the NEW record to > json and working with that. Non-existent object keys return null when > accessed. One note - in my tests working with hstore was significantly faster

Order of rows in statement triggers NEW/OLD tables

Hi, when defining statement triggers on update I can use: REFERENCING OLD TABLE AS xxx NEW TABLE as YYY these "pseudo" tables contain rows that were before and after. Is the order guaranteed? Can I assume that "first" row returned by select from xxx, will be older version of first row returned

Re: [External]Re: Postgres Crash Issue

On Tue, Mar 15, 2022 at 01:38:04PM +, Menon, Deepak (Deepak) wrote: > Thanks Depesz. Is there anyway to check the source of the command as this HA > setup is managed by using Patroni >From pg perspective it got immediate stop request. There is no way to tell why/how. depesz

Re: [External]Re: Postgres Crash Issue

On Tue, Mar 15, 2022 at 01:30:25PM +, Menon, Deepak (Deepak) wrote: > Hi Depesz, > > Then what does this message mean ? > > 2022-03-11 08:58:42.956 UTC [17115] DETAIL: The postmaster has commanded > this server process to roll back the current transaction and exit, because > another server

Re: Postgres Crash Issue

On Tue, Mar 15, 2022 at 07:20:57AM +, Sankar, Uma (Uma) wrote: > Hi All, > > We have a database crash issue last Friday and it's a patroni HA-based > Postgres database running, we have checked the pg logs and it shows > error as shared memory corruptions. Can someone please check the > attach

Re: Postgres query

On Fri, Mar 11, 2022 at 10:02:39AM +, Ian Dauncey wrote: > Can anyone assist in shedding some light here. > We getting this query popping up in our postgresql log file at the same time > as the connections to the databases starts increasing. > Not sure what is initiating this query, but we get

Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal

On Mon, Mar 07, 2022 at 09:54:22AM -0800, Andres Freund wrote: > > Initially select didn't break anything, but when I tuned down > > jit_above_cost so that it will kick in - got fails immediately. > Could you set jit_debugging_support=on and show a backtrace with that? Here you go: Program receive

Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal

On Mon, Mar 07, 2022 at 12:22:26PM -0500, Tom Lane wrote: > Neither of those configurations fail for me, so either > it's been fixed since 12.9, or (more likely) there is > something to your test case beyond what you've mentioned. Upgraded to 12.10 from pgdg, same problem. > (I guess a long-shot

Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal

On Sun, Mar 06, 2022 at 11:10:00AM -0500, Tom Lane wrote: > > I tore these boxes down, so can't check immediately, but I think > > I remember that you're right - single-row queries didn't use JIT. Got focal box up. Loaded schema for Pg. Initially select didn't break anything, but when I tuned dow

Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal

On Fri, Mar 04, 2022 at 05:03:14PM -0500, Tom Lane wrote: > hubert depesz lubaczewski writes: > > On Fri, Mar 04, 2022 at 02:09:52PM -0500, Tom Lane wrote: > >> I tried and failed to reproduce this on Fedora 35 on aarch64, > >> but that has what I think is a newer

Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal

On Fri, Mar 04, 2022 at 02:09:52PM -0500, Tom Lane wrote: > arm64, eh? I wonder if that's buggier than the Intel code paths. > > I tried and failed to reproduce this on Fedora 35 on aarch64, > but that has what I think is a newer LLVM version: I have suspicion that it also kinda depends on numbe

Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal

On Thu, Mar 03, 2022 at 05:39:21PM +0100, hubert depesz lubaczewski wrote: > On Thu, Mar 03, 2022 at 04:11:56PM +0100, hubert depesz lubaczewski wrote: > > On Thu, Mar 03, 2022 at 04:04:28PM +0100, hubert depesz lubaczewski wrote: > > > and it worked, so I'm kinda at loss

Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal

On Thu, Mar 03, 2022 at 04:11:56PM +0100, hubert depesz lubaczewski wrote: > On Thu, Mar 03, 2022 at 04:04:28PM +0100, hubert depesz lubaczewski wrote: > > and it worked, so I'm kinda at loss here. > > based on some talk on IRC, I was able to get stack trace from fail: Based

Re: Interesting fail when migrating Pg from Ubuntu Bionic to Focal

On Thu, Mar 03, 2022 at 04:04:28PM +0100, hubert depesz lubaczewski wrote: > and it worked, so I'm kinda at loss here. based on some talk on IRC, I was able to get stack trace from fail: (gdb) bt #0 0xfffe4a36e4d8 in ?? () #1 0xbe03ffb8 in ExecProcNode (node=0xe4f8

Interesting fail when migrating Pg from Ubuntu Bionic to Focal

Hi, I know it's going to be most likely due to glibc and locales, but I found interesting case that I can't figure out how to fix. We have pg 12.6 on bionic. Works. Added focal replica (binary). Replicates OK, but then fails when I try to pg_dump -s. Error is: pg_dump: error: query failed: serv

Re: How to schedule running of a script?

On Wed, Jan 19, 2022 at 05:10:29AM +, Shaozhong SHI wrote: > Can a script be scheduled to run within Postgres? https://www.depesz.com/2021/01/15/how-to-run-some-tasks-without-user-intervention-at-specific-times/ https://www.depesz.com/2021/01/28/how-to-run-some-tasks-without-user-intervention-

Re: Can we use sql language to create a function in Postgres?

On Wed, Jan 05, 2022 at 11:22:34AM +, Shaozhong SHI wrote: > If so, can we use Oracle SQL script in DO block? > > Does this mean that all Oracle SQL can be used in Postgres? I highly doubt that. But - I used oracle last time like 2 years ago, so the only thing I can tell you: TRY. instead of

Re: Can we use sql language to create a function in Postgres?

On Wed, Jan 05, 2022 at 11:04:34AM +, Shaozhong SHI wrote: > I was given an Oracle script. Can we use sql language to create a function > in Postgres? Sure: create function z() returns int4 language sql as $$ select 123; $$; depesz

Re: How to set alias data type?

On Wed, Nov 24, 2021 at 01:57:06PM +, Shaozhong SHI wrote: > select 'Total' as Total generate result that set Total as a column name > with unknown type > > When trying to cast > select 'Total' as Total:: text You need to cast value, and not name. select 'Total'::text as Total; Best regards

Re: get last timestamp of table ddl

On Wed, Nov 24, 2021 at 02:53:24PM +0100, Luca Ferrari wrote: > Hi all, > I think I already know the answer, however I came across this table in > Oracle > > that has two columns that triggered my attention: CREAT

Re: create all indexes and triggers in a schema after bulk load

On Wed, Nov 17, 2021 at 05:13:35PM +1000, Akheel Ahmed wrote: > Hi Group > > I am looking at doing a CDC-based upgrade using the following steps: > > > 1. pg_dump schema on source, but exclude indexes and triggers > > 2. recreate schema on target > > 3. Do a full load from source to target >

Re: Can we get rid of repeated queries from pg_dump?

On Wed, Oct 20, 2021 at 05:46:01PM -0400, Tom Lane wrote: > I wrote: > > Anyway, it doesn't look like there's much hope of improving this > > aspect without a significant rewrite. > > Just to close out this thread: I've now posted such a rewrite at > https://www.postgresql.org/message-id/2273648.1

Re: Replication between different architectures

On Wed, Oct 13, 2021 at 05:09:36PM +0100, Phil Endecott wrote: > Is replication going to work from an x86-64 master to an > arm64 replica? Hi, tested, works well. Best regards, depesz

Re: Grant select for all tables of the 12 schemas of my one db ?

On Wed, Oct 13, 2021 at 03:33:20PM +0530, Vijaykumar Jain wrote: > something like this ? Like, but not exactly. Consider what will happen if you have schema named "whatever something else" - with spaces in it. Or "badlyNamedSchema". Generally you'd want to use: execute format('GRANT USAGE ON SC

Re: Grant select for all tables of the 12 schemas of my one db ?

On Wed, Oct 13, 2021 at 11:04:42AM +0200, celati Laurent wrote: > Good morning, > > I work on Postgresql 13 (windows) and Postgis. > For some "basic USERS", i have to grant select/read for all tables of the > 12 schemas of my db ? > > With Postgresql 13, i am obliged to write : > *GRANT SELECT O

Re: Check Replication lag

On Mon, Oct 11, 2021 at 05:58:03PM +0530, Pawan Sharma wrote: > Is there a way to monitor the replication lag, where replica nodes are > configured through aws s3. You can check it on replica, by issuing: select now() - pg_last_xact_replay_timestamp(); and it will show you, more or less, what is

Re: Why would Postgres 11 suddenly ignore the recovery.conf file?

On Mon, Oct 04, 2021 at 12:35:48PM +0200, Dennis Jacobfeuerborn wrote: > Hi, > I just stopped and restarted a Postgres 11 docker container which so far has > happily streamed the WAL from its primary but after the restart it now seems > to completely ignore the recovery.conf file and just outputs t

Re: Faster distinct query?

On Wed, Sep 22, 2021 at 12:05:22PM -0800, Israel Brewster wrote: > I was wondering if there was any way to improve the performance of this query: > > SELECT station,array_agg(distinct(channel)) as channels FROM data GROUP BY > station; > > The explain execution plan can be found here: > https://

Re: Is there any technical reason why "alter table .. set not null" can't use index?

On Fri, Sep 10, 2021 at 01:26:46PM -0400, Gus Spier wrote: > Would it help to create a new not null column in the target table, and > then update the table by copying values from old column to the new, > not null column? Of course you’d have to ignore errors, etc. but > wouldn’t that perform at eno

Re: Is there any technical reason why "alter table .. set not null" can't use index?

On Wed, Sep 08, 2021 at 07:09:31AM +0200, Alexander Kukushkin wrote: > Hi, > > On Wed, 8 Sep 2021, 06:59 hubert depesz lubaczewski, > wrote: > > > Hi, > > we needed recently to add not null constraint on some fields, and it > > struck me that it took long. &g

Is there any technical reason why "alter table .. set not null" can't use index?

Hi, we needed recently to add not null constraint on some fields, and it struck me that it took long. Obviously - pg has to check the data. But it seems that it can't use index. Made 4 test tables: create table test (a int4, b timestamptz); insert into test (a,b) select i, now() - i * '1 minute'::

  1   2   >