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'::

Re: Pg stuck at 100% cpu, for multiple days

On Mon, Aug 30, 2021 at 11:04:33AM -0400, Joe Conway wrote: > src/backend/utils/hash/dynahash.c:1448 is in the middle of a while loop, > which is apparently not exiting. > There is no check for interrupts in there and it is a fairly tight loop > which would explain both symptoms. > As to how it got

Pg stuck at 100% cpu, for multiple days

Hi, Originally I posted it on -general, but Joe Conway suggested I repost in here for greater visibility... We hit a problem with Pg 12.6 (I know, we should upgrade, but that will take long time to prepare). Anyway - it's 12.6 on aarm64. Couple of days there was replication slot started, and now

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

On Mon, Aug 30, 2021 at 10:11:22AM -0400, Tom Lane wrote: > I don't suppose you could send me a schema-only dump of that > database, off-list? I'm now quite curious. Asked the owners for their permission. > > The thing is - even though it was called 1804 times, dump contains data only > > about

Re: Pg stuck at 100% cpu, for multiple days

On Mon, Aug 30, 2021 at 07:57:26PM +0530, Vijaykumar Jain wrote: > related? i was kind of expecting debezium when i saw cdc and java, but > DBZ-1596 Fix open resources while initializing publication by Naros · Pull > Request #1120 · debezium/debezium (github.com) >

Re: Arrays - selecting (and not removing) duplicates...

On Mon, Aug 30, 2021 at 01:47:19PM +0100, Pól Ua Laoínecháin wrote: > I was just wondering if there is some (already written) function out > there that will drop this functionality into my lap? Sure, here: #v+ create function array_dups(ANYARRAY) returns ANYARRAY as $$ select array( select i

Pg stuck at 100% cpu, for multiple days

Hi, We hit a problem with Pg 12.6 (I know, we should upgrade, but that will take long time to prepare). Anyway - it's 12.6 on aarm64. Couple of days there was replication slot started, and now it seems to be stuck. =# select * from pg_stat_activity where pid = 22697 \gx ─[ RECORD 1 ]┬───

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

On Fri, Aug 27, 2021 at 05:23:23PM -0400, Tom Lane wrote: > I experimented with the attached, very quick-n-dirty patch to collect > format_type results during the initial scan of pg_type, instead. On the > regression database in HEAD, it reduces the number of queries pg_dump > issues from 3260 to

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

On Fri, Aug 27, 2021 at 05:23:23PM -0400, Tom Lane wrote: > Those queries are coming from getFormattedTypeName(), which is used > for function arguments and the like. I'm not quite sure why Hubert > is seeing 5000 such calls in a database with only ~100 functions; > surely they don't all have an a

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

On Fri, Aug 27, 2021 at 05:23:23PM -0400, Tom Lane wrote: > I experimented with the attached, very quick-n-dirty patch to collect > format_type results during the initial scan of pg_type, instead. On the > regression database in HEAD, it reduces the number of queries pg_dump > issues from 3260 to

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

On Thu, Aug 26, 2021 at 10:20:29AM -0400, Tom Lane wrote: > Well, you could move it forward by doing the legwork to identify which > queries are worth merging. Is it really sane to do a global "select > format_type() from pg_type" query and save all the results on the client > side? I wonder whet

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

On Thu, Aug 26, 2021 at 07:46:46AM -0700, Adrian Klaver wrote: > On 8/26/21 7:35 AM, hubert depesz lubaczewski wrote: > > On Thu, Aug 26, 2021 at 07:34:26AM -0700, Adrian Klaver wrote: > > > On 8/26/21 1:44 AM, hubert depesz lubaczewski wrote: > > > > Hi, > &

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

On Thu, Aug 26, 2021 at 07:34:26AM -0700, Adrian Klaver wrote: > On 8/26/21 1:44 AM, hubert depesz lubaczewski wrote: > > Hi, > > I have following case: local pg_dump (v15) connecting to remote > > PostgreSQL (v12). > So you are using a dev version of pg_dump or is that a

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

On Thu, Aug 26, 2021 at 10:20:29AM -0400, Tom Lane wrote: > hubert depesz lubaczewski writes: > > On Thu, Aug 26, 2021 at 10:02:07AM -0400, Tom Lane wrote: > >> hubert depesz lubaczewski writes: > >>> Wouldn't it be possible to get all type formats at once, and

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

On Thu, Aug 26, 2021 at 10:02:07AM -0400, Tom Lane wrote: > hubert depesz lubaczewski writes: > > It seems that for every function, pg_dump is getting it's data, and then > > runs format_type on each parameter/output type? I'm mostly guessing > > here, as I didn&#

Can we get rid of repeated queries from pg_dump?

Hi, I have following case: local pg_dump (v15) connecting to remote PostgreSQL (v12). I'm trying to get just schema (pg_dump -s). It's taking very long, which is kinda OK given that there is long distance and latency, but I got curious and checked queries that the pg_dump was running (select * fro

Re: Getting pg_stat_database data takes significant time

On Thu, Aug 12, 2021 at 06:20:23PM +0200, Magnus Hagander wrote: > On Thu, Aug 12, 2021 at 4:38 PM hubert depesz lubaczewski > wrote: > > > > On Thu, Aug 12, 2021 at 11:32:15AM +0200, Magnus Hagander wrote: > > > Which database are you connected to? If you just want

Re: Getting pg_stat_database data takes significant time

On Thu, Aug 12, 2021 at 11:32:15AM +0200, Magnus Hagander wrote: > Which database are you connected to? If you just want to look at the > global stats, it might help to be connected to a database that is > *not* the one with all the tables in -- e.g. connect to "postgres" and > query pg_stat_databa

Re: Getting pg_stat_database data takes significant time

On Thu, Aug 12, 2021 at 09:08:27AM -0400, Alvaro Herrera wrote: > On 2021-Aug-11, hubert depesz lubaczewski wrote: > > > On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > > > 1. this depends on reading the stats file; that's done once per > > &

Re: Getting pg_stat_database data takes significant time

On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > 1. this depends on reading the stats file; that's done once per > transaction. So if you run the query twice in a transaction, the second > time will take less time. You can know how much time is spent reading > that file by subtra

  1   2   >