Re: Bad Query Plans on 10.3 vs 9.6

On 29 March 2018 at 18:26, Cory Tucker wrote: > Hello all. I'm migrating a database from PG 9.6 to 10.3 and have noticed a > particular query that is performing very badly compared to its 9.6 > counterpart. > > The plan on 9.6 v 10.3 are effectively identical except in 9.6 the planner > decides t

Re: Using Lateral

Thanks David and Paul, You have helped me a lot. Regards Johann. On 28 March 2018 at 20:49, David G. Johnston wrote: > On Tuesday, March 27, 2018, Johann Spies wrote: >> >> In the past I could use this in a query: >> >> SELECT >> DISTINCT ut, >> CASE >> WHEN xpa

Bad Query Plans on 10.3 vs 9.6

Hello all. I'm migrating a database from PG 9.6 to 10.3 and have noticed a particular query that is performing very badly compared to its 9.6 counterpart. The plan on 9.6 v 10.3 are effectively identical except in 9.6 the planner decides to use an index only scan on the primary key and in 10.3 it

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On 03/28/2018 07:29 PM, Ken Beck wrote: Comments in line. The current log is 0 length, and indeed empty. I just tried 'sudo pg_ctlcluster 9.3 main start'  and got the following error: Error: Config owner (kcb:1000) and data owner (postgres:116) do not match, and config owner is not root So

Re: Autonomous transaction, background worker

I've experimented with background workers a bit. Yes, normally you'd write some C, but you could also install this (mentioned in the blog post and the source of pg_background_launch): Thanks for your answer. I want my logger to be used with the facilities there are from PostgreSQL so I'll go t

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On Wed, Mar 28, 2018 at 7:29 PM, Ken Beck wrote: > Is it possible a big problem is that the password for user postgres not > longer works for some reason, not sure why. It appears the password can > not be rest or changed without knowing the original, and what I thought > was the original no long

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On 03/28/2018 08:29 PM, Ken Beck wrote: Is it possible a big problem is that the password for user postgres not longer works for some reason, not sure why. It appears the password can not be rest or changed without knowing the original, and what I thought was the original no longer works. Ma

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

The current log is 0 length, and indeed empty. I just tried 'sudo pg_ctlcluster 9.3 main start'  and got the following error: Error: Config owner (kcb:1000) and data owner (postgres:116) do not match, and config owner is not root Changed to user postgres, tried again and got: Warning: the clust

Re: pg_ctl promote causes error "could not read block" (PG 9.5.0 and 9.5.4)

On Wed, Mar 28, 2018 at 09:36:11AM -0700, raj 1988 wrote: > Are we hitting some bug? tried to look around but not able to confirm if we > are hitting a bug or not. For us this is happening consistently on > different servers whenever we do pg_ctl promote and then it block WRITE on > that table. T

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On 03/28/2018 03:45 PM, Ken Beck wrote: Thanks for both responses -- the file contents I sent were from file named postgresql-9.3-main.log.1, and I am assuming this means from the Well that is an archived log and would show old information. The current log would be postgresql-9.3-main.log.

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

Thanks for both responses -- the file contents I sent were from file named postgresql-9.3-main.log.1, and I am assuming this means from the 9.3, not 9.6. Sorry I did not include that earlier. Starting to sousnds like I need to consult Ubuntu user groups. Ken Beck Liberty, Utah, USA On 03/28/2018

Re: Query Crashes PG 10.3 using partitions, works on 9.6

Cory Tucker writes: >> Can you extract a self-contained test case that uses unreasonable amounts >> of memory? It seems from this trace that the wheels are coming off in >> at least two places, but identifying exactly where is impossible without >> more info. > I will try to make a test case. T

Re: Query Crashes PG 10.3 using partitions, works on 9.6

> > You are running the query (DELETE statement) as if the table is not > partitioned which is causing the server to crash. > > Please run that query for each partitions separately in a loop with > dynamic query and you should see the improvement. It should be pretty quick. > > I understand that I

Re: Query Crashes PG 10.3 using partitions, works on 9.6

> > Can you extract a self-contained test case that uses unreasonable amounts > of memory? It seems from this trace that the wheels are coming off in > at least two places, but identifying exactly where is impossible without > more info. > I will try to make a test case. The data in this table i

Re: Query Crashes PG 10.3 using partitions, works on 9.6

Cory Tucker writes: > I was issuing a query on both databases to cleanup some duplicates in > preparation of applying new indexes. On the 9.6 database with all the data > in one table, the query runs fine in about 6 min. On 10.3, with a work_mem > setting of 1GB the query runs for about 7 minute

RE: Query Crashes PG 10.3 using partitions, works on 9.6

Hi Cory, You are running the query (DELETE statement) as if the table is not partitioned which is causing the server to crash. Please run that query for each partitions separately in a loop with dynamic query and you should see the improvement. It should be pretty quick. Regards, Virendra. Fro

RE: Query Crashes PG 10.3 using partitions, works on 9.6

Would be nice if you can attach explain plan of course, explain analyze is not going to work if server is crashing. Regards, Virendra From: Cory Tucker [mailto:cory.tuc...@gmail.com] Sent: Wednesday, March 28, 2018 5:49 PM To: pgsql-gene...@postgresql.org Subject: Query Crashes PG 10.3 using pa

Query Crashes PG 10.3 using partitions, works on 9.6

Hey guys, I am in the middle of testing out a database migration from 9.6 to 10.3. We have a quasi-multi tenant based application and so are utilizing native partitions on some relations to help improve some performance. I was issuing a query on both databases to cleanup some duplicates in prepar

Question about buffers_alloc in pg_stat_bgwriter view for monitoring

Hi all, Can someone tell me, what the value of buffers_alloc in the pg_stat_bgwriter view (https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-BGWRITER-VIEW) is exactly? Is this the amount of shared buffers used by the bgwriter? I’m working on a new PostgreSQL monitori

Using Lateral

On Tuesday, March 27, 2018, Johann Spies wrote: > In the past I could use this in a query: > > SELECT > DISTINCT ut, > CASE > WHEN xpath_exists ('//t:address_spec/t:country/text()', > q.address_spec, > p.ns) > THEN unnest

Re: Using Lateral

On 03/28/2018 05:22 AM, Johann Spies wrote: Thanks Paul. I was hesitant to post my full query. It is a long and complicated query. Ha ha, you aren't joking. :-) With something that long I don't think I'd want to split every xpath call into a new join. I guess you could try it and see though

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

On Wed, Mar 28, 2018 at 10:13 AM, Ron wrote: > On 03/28/2018 03:05 AM, Andreas Kretschmer wrote: > [snip] > >> > This e-mail message, including any attachments, >> >> this is a public mailing list ... >> > > The intended recipient is the public mailing list, no? ​A mailing list doesn't really "

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

On 03/28/2018 03:05 AM, Andreas Kretschmer wrote: [snip] > This e-mail message, including any attachments, this is a public mailing list ... The intended recipient is the public mailing list, no? -- Angular momentum makes the world go 'round.

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

Il 28/03/2018 18:28, Adrian Klaver ha scritto: On 03/28/2018 09:24 AM, Moreno Andreo wrote: Il 27/03/2018 22:57, Adrian Klaver ha scritto: On 03/27/2018 01:46 PM, Ken Beck wrote: I am working on two systems, one running in Oracle VirtualBox on my laptop, the other in a DigitalOcean droplet. I

pg_ctl promote causes error "could not read block" (PG 9.5.0 and 9.5.4)

Hi there, we are running into this weird issue of table getting on READ ONLY mode with below error: ERROR: could not read block 54 in file "base//215619": read only 0 of 8192 bytes We are facing this whenever we promote a streaming standby using pg_ctl promote command, and this is happing on PG

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On 03/28/2018 09:24 AM, Moreno Andreo wrote: Il 27/03/2018 22:57, Adrian Klaver ha scritto: On 03/27/2018 01:46 PM, Ken Beck wrote: I am working on two systems, one running in Oracle VirtualBox on my laptop, the other in a DigitalOcean droplet. I know on one of them I tried to remove the postgr

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

Il 27/03/2018 22:57, Adrian Klaver ha scritto: On 03/27/2018 01:46 PM, Ken Beck wrote: I am working on two systems, one running in Oracle VirtualBox on my laptop, the other in a DigitalOcean droplet. I know on one of them I tried to remove the postgres-9.6, and it must have been my laptop, here

Query performance with cluster

Hi, I'm creating this table : *create table availability(product_id integer, product_name varchar(255), during daterange);* *create index time_idx on availability using gist(during);* *insert into availability* *select p.id , concat('prod ', p.id::text), daterange((now() + concat(r

Re: Problem with postgreSQL

On 03/28/2018 02:36 AM, Gian mario Caggiu wrote: Please also reply to list so this can be seen by more eyes. Ccing list I've already written to you what versions I downloaded, 10.3 and 9.6.8. I clicked in the 'download the installer' section and then I selected the version to download. So thi

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

Hi, the perf profiles look really weird - clearly, there's a lot of lock contention, because the top item is this 13.49%13.40% postmaster postgres [.] LWLockAcquire | ---LWLockAcquire That's a sign of lock contention - not sure which one, though

Re: Using Lateral

Thanks Paul. I was hesitant to post my full query. It is a long and complicated query. But here it is now: WITH p AS ( SELECT ARRAY [ ARRAY [ 't', 'http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord' ] ] AS ns), uts AS ( SELECT s.ut FROM wos

Re: Connection hangs on new created schema

On Wed, Mar 28, 2018 at 2:58 AM, Laurenz Albe wrote: > Juan Manuel Cuello wrote: > > I have a postgresql database with around 4000 schemas. Each schema has > around > > the same 65 tables. There are 8 processes connected to the database (it > is a web app). > > Each process has only one direct co

Re: Fixed chars

Resolved...my missing Thanks Enrico https://www.postgresql.org/docs/10/static/datatype-character.html "Values of type|character|are physically padded with spaces to the specified width/|n|/, and are stored and displayed that way. However, trailing spaces are treated as semantically insigni

Re: Fixed chars

Am 28.03.2018 um 11:11 schrieb Enrico Pirozzi: Hi , I've seen this strange thing. sitedb=# create table test_tb(codice char(7)); CREATE TABLE sitedb=# insert into test_tb values('pippo'); INSERT 0 1 sitedb=# select codice || 'a'::char(1),length(codice) from test_tb  ?column? | length -

Fixed chars

Hi , I've seen this strange thing. sitedb=# create table test_tb(codice char(7)); CREATE TABLE sitedb=# insert into test_tb values('pippo'); INSERT 0 1 sitedb=# select codice || 'a'::char(1),length(codice) from test_tb ?column? | length --+ pippoa | 5 (1 row) On the off

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

Hello, any news ? Thank you, Alessandro. On Fri, Mar 23, 2018 at 8:22 PM, Alessandro Aste wrote: > PS , in the meanwhile I discovered a 2nd workaround(beside disabling > parallel processing) . I added offset 0 to the subquery , and, according > to the documentation, “OFFSET 0 is the same as

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

Am 28.03.2018 um 09:46 schrieb Stefan Petrea: Hi Andreas, Thank you for your suggestion about pausing/resuming the standby! I've tried running on the standby SELECT pg_xlog_replay_pause() Then running the dump, and it threw the same error I had seen before. run pg_dump with --no-synchronize

RE: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

Hi Andreas, Thank you for your suggestion about pausing/resuming the standby! I've tried running on the standby SELECT pg_xlog_replay_pause() Then running the dump, and it threw the same error I had seen before. Seeing that I wasn't able to do it, I just ran SELECT pg_xlog_replay_resume() On the