query has no destination for result data

I’m using 10.7. Does an empty result set generate this error by any chance. One of my plpgsql functions is now throwing this error: select * from genome_threshold_mono('11-O3C.pbs','1-O3C_chr',1.96, 100); NOTICE: group id is 5eed8d65-d39a-4f72-97a3-ca391b84880d NOTICE: New threshold: 661281

Re: Non-pausing table scan on 9.6 replica?

On Wed, Mar 6, 2019 at 1:41 PM Mark Fletcher wrote: > Thank you for responding to my email. > > On Tue, Mar 5, 2019 at 9:20 PM Andreas Kretschmer > wrote: > >> >> have you set ```max_standby_streaming_delay``? The default is 30 >> seconds, which means that this will be the maximum time allowed f

Re: Non-pausing table scan on 9.6 replica?

Am 06.03.19 um 06:41 schrieb Mark Fletcher: Thank you for responding to my email. On Tue, Mar 5, 2019 at 9:20 PM Andreas Kretschmer mailto:andr...@a-kretschmer.de>> wrote: have you set ```max_standby_streaming_delay``? The default is 30 seconds, which means that this will be the m

Re: Non-pausing table scan on 9.6 replica?

Thank you for responding to my email. On Tue, Mar 5, 2019 at 9:20 PM Andreas Kretschmer wrote: > > have you set ```max_standby_streaming_delay``? The default is 30 > seconds, which means that this will be the maximum time allowed for a > replication lag caused by a conflicting query. > Yes, we'

Re: write on standby

On 6 March 2019 06:26:45 CET, Julie Nishimura wrote: >Thank you. Are you going to have any presentations on Postgresql >conference in NYC soon? > > >From: Andreas Kretschmer >Sent: Tuesday, March 5, 2019 9:16 PM >To: pgsql-general@lists.postgresql.org >Subject: Re:

Re: write on standby

Thank you. Are you going to have any presentations on Postgresql conference in NYC soon? From: Andreas Kretschmer Sent: Tuesday, March 5, 2019 9:16 PM To: pgsql-general@lists.postgresql.org Subject: Re: write on standby Am 06.03.19 um 00:34 schrieb Julie Nishi

Re: Non-pausing table scan on 9.6 replica?

Am 06.03.19 um 01:26 schrieb Mark Fletcher: Hi All, On a 9.6 streaming replica, we do table scans for stats and other things. During these scans, the replication is paused (the 'recovering' postgres process has 'waiting' appended to it). We're not using transactions with these scans. Is th

Re: write on standby

Am 06.03.19 um 00:34 schrieb Julie Nishimura: Hello there, Is it possible for a test app to connect to the standby dB of an active-Standby dB pair? that's possible, but ... While both continue to be connected and replicating? What if it’s needed to write tmp tables that are later dropped

Re: Connection Drop from PostgreSQL Database Server

On 3/5/19 7:13 PM, Mohit Kumar Sahni wrote: Hi Adrian, I have checked my log files. Here are some of the lines that I think were logged at the time of connection drop: 2019-03-03 15:57:33.444 UTC [7146] azpostgres@master_configuration_db LOG: could not receive data from client: Connection tim

Re: How to make PAF use psql to login with password

On 3/5/19 5:57 PM, 范国腾 wrote: Hi, We use the PAF (https://dalibo.github.io/PAF/​) to manage the postgresql. According to user's requirement, we could not use trust mode in the pg_hba.conf​ file. So when running psql, it will ask us to input the password and we have to input the password manu

How to make PAF use psql to login with password

Hi, We use the PAF (https://dalibo.github.io/PAF/?) to manage the postgresql. According to user's requirement, we could not use trust mode in the pg_hba.conf? file. So when running psql, it will ask us to input the password and we have to input the password manually. So the pcs status show t

Re: Connection Drop from PostgreSQL Database Server

On 3/5/19 4:19 AM, Mohit Kumar Sahni wrote: I have done a setup over Azure Cloud for my PostgreSQL DB. It's Ubuntu 18.04 LTS (4vCPU, 8GB RAM) machine with PostgreSQL 9.6 version. The problem that occurring is, when the connection to the postgresql db stays idle for some time let's say 2 to 10 mi

Connection Drop from PostgreSQL Database Server

I have done a setup over Azure Cloud for my PostgreSQL DB. It's Ubuntu 18.04 LTS (4vCPU, 8GB RAM) machine with PostgreSQL 9.6 version. The problem that occurring is, when the connection to the postgresql db stays idle for some time let's say 2 to 10 minutes then the connection to the db does not

Non-pausing table scan on 9.6 replica?

Hi All, On a 9.6 streaming replica, we do table scans for stats and other things. During these scans, the replication is paused (the 'recovering' postgres process has 'waiting' appended to it). We're not using transactions with these scans. Is there anything we can do to prevent the pausing? Than

Re: [External] LIMIT not showing all results

On Tue, 5 Mar 2019 at 18:39, Adrian Klaver wrote: > > > > The replication documentation, and more specifically the pg_basebackup > > documentation, makes no mention of cross-OS replication as being a > > problem for any reason. If that is expected to be a problem, then > > perhaps that should be

Re: write on standby

On 3/5/19 3:34 PM, Julie Nishimura wrote: Hello there, Is it possible for a test app to connect to the standby dB of an active-Standby dB pair?  While both continue to be connected and replicating? What if it’s needed to write tmp tables that are later dropped? Can it be done in hot standby sc

Re: [External] LIMIT not showing all results

On 3/5/19 3:18 PM, Matthew Pounsett wrote: On Tue, 5 Mar 2019 at 18:09, Tom Lane > wrote: If you're planninng to install (the same version of) FreeBSD on the original server hardware, then rsync'ing back from the new system should be fine.  But Debian<-

Re: [External] LIMIT not showing all results

Matthew Pounsett writes: > On Tue, 5 Mar 2019 at 18:09, Tom Lane wrote: >> If you're planninng to install (the same version of) FreeBSD on >> the original server hardware, then rsync'ing back from the new >> system should be fine. But Debian<->FreeBSD is gonna be trouble >> in either direction.

write on standby

Hello there, Is it possible for a test app to connect to the standby dB of an active-Standby dB pair? While both continue to be connected and replicating? What if it’s needed to write tmp tables that are later dropped? Can it be done in hot standby scenario on standby? Version is 9.6.2 Thanks

Re: [External] LIMIT not showing all results

On Tue, 5 Mar 2019 at 18:09, Tom Lane wrote: > > If you're planninng to install (the same version of) FreeBSD on > the original server hardware, then rsync'ing back from the new > system should be fine. But Debian<->FreeBSD is gonna be trouble > in either direction. > But I'm specifically NOT t

Re: [External] LIMIT not showing all results

Matthew Pounsett writes: > On Tue, 5 Mar 2019 at 13:55, Tom Lane wrote: >> Yeah, that would fit the theory :-(. Debian would be using glibc >> and FreeBSD would not be. > The rsync migration was because we needed to do a cross-country copy before > putting the original DB server on a truck, but

Re: [External] LIMIT not showing all results

On Tue, 5 Mar 2019 at 13:55, Tom Lane wrote: > > Yeah, that would fit the theory :-(. Debian would be using glibc > and FreeBSD would not be. If you were using C collation in the > database, you'd be all right because that's standardized, but I'll > bet you were using something else. What does

Re: [External] LIMIT not showing all results

> On Mar 5, 2019, at 11:55 AM, Tom Lane wrote: > > Matthew Pounsett writes: >> On Tue, 5 Mar 2019 at 12:54, Tom Lane wrote: >>> Given that (a) this was triggered by a server migration and (b) >>> the leading column of the index looks like it's probably varchar, >>> I'm suspicious that the ne

Re: Server upgrade advice

> > > Is there a reason not to consider an all flash solution? The AMD EPYC > > processor series supports enough NVMe channels to support your sizing. > > The 7401P single processor is a good value proposition. > > Hi Ken > > Thanks very much for your response. > > I'm completely naive about th

Re: Server upgrade advice

On 05/03/19, Kenneth Marshall (k...@rice.edu) wrote: > > > > Consequently we're thinking of the following replacement servers: > > > > postgres 11 (planned) > > supermicro 113TQ-R700W > > LSI MegaRAID 9271-8i SAS/SATA RAID Controller, 1Gb DDR3 Cache (PCIE- > > Gen 3) > > 500

Re: Server upgrade advice

> > Consequently we're thinking of the following replacement servers: > > postgres 11 (planned) > supermicro 113TQ-R700W > LSI MegaRAID 9271-8i SAS/SATA RAID Controller, 1Gb DDR3 Cache (PCIE- Gen > 3) > 500gb raid 1 / > 2tb raid 10 /db > with "zero maintenanc

Re: Slave server sometimes locks up

Hi Thomas, thank you for your quick reply, much appreciated. 1. "dsa_area could not attach to segment": dsm.c, fixed in commit 6c0fb941. > 2. "cannot unpin a segment that is not pinned": dsm.c, fixed in commit > 0b55aaac. > > Yes, I found both entries in our logs, each once per incident. > The

Server upgrade advice

We've been happy running a database server and replica for some years with the following details and specs: postgres 9.5 (currently) supermicro X9DRD-7LN4F LSI Megaraid MR9261-8i with BBU 250gb raid 1 / 224gb raid 10 /db 126GB RAM (1066Mhz DDR3) 2 x Xeon E5-2609

Re: [External] LIMIT not showing all results

Matthew Pounsett writes: > On Tue, 5 Mar 2019 at 12:54, Tom Lane wrote: >> Given that (a) this was triggered by a server migration and (b) >> the leading column of the index looks like it's probably varchar, >> I'm suspicious that the new server has different collation behavior. > The migration

Re: LIMIT not showing all results

Am 05.03.19 um 19:41 schrieb Casey Deccio: On Mar 5, 2019, at 10:37 AM, Andreas Kretschmer mailto:andr...@a-kretschmer.de>> wrote: no, but you can set enable_indexscan to off and maybe also enable_bitmapscan to off to force the planner to choose a seq-scan. I'm sure in this case you will

Re: [External] LIMIT not showing all results

Am 05.03.19 um 19:09 schrieb Matthew Pounsett: On Tue, 5 Mar 2019 at 12:54, Tom Lane > wrote: Andreas Kretschmer mailto:andr...@a-kretschmer.de>> writes: > the other thing is, it would be nice to to know why the index is corrupt. Given that (a) t

Re: LIMIT not showing all results

> On Mar 5, 2019, at 10:37 AM, Andreas Kretschmer > wrote: > > no, but you can set enable_indexscan to off and maybe also enable_bitmapscan > to off to force the planner to choose a seq-scan. > I'm sure in this case you will get a correct result. So this (setting enable_indexscan to off) migh

Re: [External] LIMIT not showing all results

On Tue, 5 Mar 2019 at 12:54, Tom Lane wrote: > Andreas Kretschmer writes: > > the other thing is, it would be nice to to know why the index is corrupt. > > Given that (a) this was triggered by a server migration and (b) > the leading column of the index looks like it's probably varchar, > I'm su

Re: [External] LIMIT not showing all results

On 5 March 2019 18:54:33 CET, Tom Lane wrote: >Andreas Kretschmer writes: >> the other thing is, it would be nice to to know why the index is >corrupt. > >Given that (a) this was triggered by a server migration and (b) >the leading column of the index looks like it's probably varchar, >I'm suspic

Re: [External] LIMIT not showing all results

Andreas Kretschmer writes: > the other thing is, it would be nice to to know why the index is corrupt. Given that (a) this was triggered by a server migration and (b) the leading column of the index looks like it's probably varchar, I'm suspicious that the new server has different collation behav

Re: [External] LIMIT not showing all results

Am 05.03.19 um 17:51 schrieb Vijaykumar Jain: Thanks Tom. I mean if the instance is a test instance, probably analysis_name_date_key can be dropped and the query can be run again so as to check if it still returns the correct rows. or create an index in parallel with the same col as analysis

Re: [External] LIMIT not showing all results

Thanks Tom. I mean if the instance is a test instance, probably analysis_name_date_key can be dropped and the query can be run again so as to check if it still returns the correct rows. or create an index in parallel with the same col as analysis_name_date_key and check if the optimizer choses th

Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

On Wed, Mar 6, 2019 at 4:22 AM Nicola Contu wrote: > > Not sure what you are requesting exactly but here is the strace for the start > of the pg_ctl I meant that you could run the server itself in the foreground under strace, like so: $ strace -f -c /usr/local/pgsql11.2/bin/postgres -D /db/pgsq

Re: [External] LIMIT not showing all results

Casey Deccio writes: >> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain wrote: >> Can you run both the queries with >> “explain analyze select ” and paste the output. > dnsviz=> explain analyze select id,name,date from analysis where name = 'foo' > order by date desc limit 20; >

Re: Question about pg_upgrade from 9.2 to X.X

On Tue, Mar 05, 2019 at 08:09:12AM -0800, Perumal Raj wrote: > Thanks Sergei/Justin for the continues update. > > So reorg Schema might be created as part of some scripts prior to 9.2 > Version ? I'm guessing they were probably created in 9.2. > These are the functions in DB not the Extension. H

Re: [External] LIMIT not showing all results

> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain wrote: > > Can you run both the queries with > “explain analyze select ” and paste the output. dnsviz=> explain analyze select id,name,date from analysis where name = 'foo' order by date desc limit 20;

Re: Slave server sometimes locks up

On Wed, Mar 6, 2019 at 1:39 AM Boris Sagadin wrote: > PgSQL 10.7, Ubuntu 16.04 LTS > > Symptoms: > > - server accepts new queries until connections exhausted (all queries are > SELECT) > - queries are active, never end, but no disk IO > - queries can't be killed with kill -TERM or pg_terminate_ba

Re: [External] LIMIT not showing all results

Can you run both the queries with “explain analyze select ” and paste the output. On Tue, 5 Mar 2019 at 9:41 PM Casey Deccio wrote: > Okay, the subject is a little misleading because of course LIMIT isn't > supposed to all results, but I've got an issue where LIMIT isn't showing > the numbe

LIMIT not showing all results

Okay, the subject is a little misleading because of course LIMIT isn't supposed to all results, but I've got an issue where LIMIT isn't showing the number of results I would expect. For example: mydb=> select id,name,date from analysis where name = 'foo' order by date desc limit 3; id |

Re: Question about pg_upgrade from 9.2 to X.X

Thanks Sergei/Justin for the continues update. So reorg Schema might be created as part of some scripts prior to 9.2 Version ? These are the functions in DB not the Extension. However these functions will not run as the associated libraries are not exists in System now (9.2) and I hope no impact t

Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

Not sure what you are requesting exactly but here is the strace for the start of the pg_ctl Il giorno lun 4 mar 2019 alle ore 21:55 Thomas Munro ha scritto: > On Tue, Mar 5, 2019 at 5:01 AM Nicola Contu > wrote: > > Attached a part of the strace running the pgbench command for pg11 > > Also a

Slave server sometimes locks up

PgSQL 10.7, Ubuntu 16.04 LTS Symptoms: - server accepts new queries until connections exhausted (all queries are SELECT) - queries are active, never end, but no disk IO - queries can't be killed with kill -TERM or pg_terminate_backend() - system load is minimal (vmstat shows 100% idle) - perf top