Re: Idempotent DDL Updates

On Mon, Aug 30, 2021 at 11:56 PM Miles Elam wrote: > > Not sure that querying the catalogs is strictly necessary though… Could you > say more? I meant for anything that doesn't have an IF [NOT] EXISTS, including cases where such a clause wouldn't be possible. For instance if you have to provide

Re: vacuum full

On Mon, 30 Aug 2021 at 23:12, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > Just keeping it in a separate email, incase this is thrashed down. > vacuum full has a lot of problem stories, not just because the db gets > locked, but also because it is mostly (mis)used when there are spa

Re: Idempotent DDL Updates

On 8/30/21 8:56 AM, Miles Elam wrote: On Fri, Aug 27, 2021 at 7:14 PM Julien Rouhaud > wrote: Note that the IF EXISTS / IF NOT EXISTS are *not* idempotent.  If you need to write idempotent schema update scripts, you need to query the catalogs to check i

vacuum full

Just keeping it in a separate email, incase this is thrashed down. vacuum full has a lot of problem stories, not just because the db gets locked, but also because it is mostly (mis)used when there are space issues. of course, there are strong warnings in docs and wiki about using a vacuum full, bu

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

hubert depesz lubaczewski writes: > 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. BTW, I think you can skip that part now --- it see

Re: FW: vacuumlo

Ian Dauncey writes: > I did run the vacuumlo against the pg_largeobject table without any issues > but afterwards I ran a vacuum full against this table which caused lots of > issues. > Because the vacuum full takes an exclusive lock (which was my first mistake > as I did not stop the applicat

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

I wrote: > Ah-hah. Those 1729 extension-owned functions account nicely > for the extra probes into pg_proc, and I bet they are causing > the unexplained getFormattedTypeName calls too. So the > *real* problem here seems to be that we're doing too much > work on objects that are not going to be du

FW: vacuumlo

Hi Reposting my query here Regards Ian From: Ian Dauncey Sent: Monday, 30 August 2021 17:09 To: pgsql-ad...@lists.postgresql.org Subject: RE: vacuumlo External email - treat with caution Hi, Just an update on my vacuumlo issue. I did run the vacuumlo against the pg_largeobject table without

Re: Idempotent DDL Updates

On Fri, Aug 27, 2021 at 7:14 PM Julien Rouhaud wrote: > > Note that the IF EXISTS / IF NOT EXISTS are *not* idempotent. If you > need to write idempotent schema update scripts, you need to query the > catalogs to check if the specific change you want to apply has already > been applied or not. >

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: Arrays - selecting (and not removing) duplicates...

Hi all and apologies for the simple question! > I want to "feed" the result of a query into your function - is there > something special I have to do for this? SELECT ARRAY_DUPS ( ( SELECT ARRAY[1,3,4,1, 5,6,5, 67] ) ) AS t; answer: t {1,5} And I can also feed in queries.

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

On 8/30/21 10:36 AM, hubert depesz lubaczewski Anyway - it's 12.6 on aarm64. Couple of days there was replication slot started, and now it seems to be stuck. #0 hash_seq_search (status=status@entry=0xdd90f380) at ./build/../src/backend/utils/hash/dynahash.c:1448 #1 0xc3042060 in

Re: lib and share are installed differently, but why?

On 30.08.21 16:24, Tom Lane wrote: > Mario Emmenlauer writes: >> I've been building and using PostgreSQL from source for a few years >> now, thanks for the awesome work! > >> Since upgrading from 12.5 to 12.8, I've found a problem that I can >> not resolve myself. We build PostgreSQL twice, once

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

hubert depesz lubaczewski writes: > On Mon, Aug 30, 2021 at 10:11:22AM -0400, Tom Lane wrote: >> I speculate that it is collecting and then not printing the info >> about functions that are in extensions --- can you check on >> how many there are of those? > classid │ count > ──

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: Arrays - selecting (and not removing) duplicates...

Hi Hubert, and thanks for your input! > > I was just wondering if there is some (already written) function out > > there that will drop this functionality into my lap? > #v+ > create function array_dups(ANYARRAY) returns ANYARRAY as $$ > select array( select i from unnest($1) i group by i hav

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: Pg stuck at 100% cpu, for multiple days

On Mon, 30 Aug 2021 at 19:15, hubert depesz lubaczewski wrote: > Hi, > We hit a problem with Pg 12.6 (I know, we should upgrade, but that will > take long time to prepare). > > > The other end of the connection was something in kubernetes, and it no > longer exists. > > related? i was kind of exp

Re: lib and share are installed differently, but why?

Mario Emmenlauer writes: > I've been building and using PostgreSQL from source for a few years > now, thanks for the awesome work! > Since upgrading from 12.5 to 12.8, I've found a problem that I can > not resolve myself. We build PostgreSQL twice, once to folder A, and > once to folder B. This i

Re: lib and share are installed differently, but why?

On 8/30/21 3:53 AM, Mario Emmenlauer wrote: Dear All, I've been building and using PostgreSQL from source for a few years now, thanks for the awesome work! Since upgrading from 12.5 to 12.8, I've found a problem that I can not resolve myself. We build PostgreSQL twice, once to folder A, and on

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

hubert depesz lubaczewski writes: > 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. > So, I applied it to brand new HEAD from git, Result: > From

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 ]┬───

get current worker processes count

Hi I set max_worker_processes to 20. How can I check how many are currently running? IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform th

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

Hi all, Suppose I have two arrays (TEXT, INT - not important - prefer INT) as follows: x = {1, 4, 5, 6, 6, 7, 8, 9, 9, 9, 9, 10, 12, 12} I would like a result of: y = {6, 9, 12} I'm aware of the UNNEST function and that I can convert the array into rows and do a COUNT(blah) HAVING COUNT(blah)

lib and share are installed differently, but why?

Dear All, I've been building and using PostgreSQL from source for a few years now, thanks for the awesome work! Since upgrading from 12.5 to 12.8, I've found a problem that I can not resolve myself. We build PostgreSQL twice, once to folder A, and once to folder B. This is because A is more eas

Re: user creation time for audit

On Mon, Aug 30, 2021 at 5:24 PM Vijaykumar Jain wrote: > > On Mon, 30 Aug 2021 at 14:39, Julien Rouhaud wrote: >> >> >> The easy way around that is to track those events yourself with the >> rules that suit your needs, which can be done easily using an event >> trigger. > > > Please correct me if

Re: user creation time for audit

On Mon, 30 Aug 2021 at 14:39, Julien Rouhaud wrote: > > The easy way around that is to track those events yourself with the > rules that suit your needs, which can be done easily using an event > trigger. > Please correct me if I am missing anything, but the doc said, event triggers are not allo

Re: user creation time for audit

On Mon, Aug 30, 2021 at 4:43 PM Boyapalli, Kousal wrote: > > We are looking for the user creation date > > Tried from both from pg admin and psql by using > > select * from pg_catalog.pg_user and /du, /du+ we were able to get the > users but is there a way where we can get the creation date

RE: user creation time for audit

Hi Team, We are looking for the user creation date Tried from both from pg admin and psql by using select * from pg_catalog.pg_user and /du, /du+ we were able to get the users but is there a way where we can get the creation date Can any one please help/any suggestions Thanks in Advance !

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