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

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

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

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

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

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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: PostgreSQL needs percentage function

On Mon, Dec 18, 2017 at 02:23:38PM +0200, Nick Dro wrote: >Hi, >Why PostgreSQL doesn't have build-in function to calculate percentage? >somthing like percent(number,% >for example: >select percent(100,1) will calculate 1% of 100 = 1 >select percent(25,20) will calculate 20%

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

<    1   2