Re: [GENERAL] WindowAgg optimized out of subquery but not out of CTE, generating different results.

2017-01-27 Thread Tom Lane
b...@byeh.net writes: > Is this supposed to happen given the queries above? Why? Is there some > sort of interaction happening between the missing DISTINCT ON ORDER BY > and the window ORDER BY? is this a bug? Yes, your query is underspecified, yes, no. The PG SELECT reference page explains corre

Re: [GENERAL] Recovery Assistance

2017-01-27 Thread Adrian Klaver
On 01/27/2017 05:40 PM, Brian Mills wrote: First of all, Thank you for your time to assist me learning. I really appreciate it. root# ps ax | grep post 1364 ?Ss 0:00 /usr/lib/postfix/master 5198 pts/3S 0:00 su postgres 5221 pts/3S 0:00 /usr/lib/postgresql/9.3/bin

[GENERAL] WindowAgg optimized out of subquery but not out of CTE, generating different results.

2017-01-27 Thread bym
I am having an issue where the subquery version of a query and the CTE version of a query are generating different results. Postgresql version 9.5.5 on Linux Ubuntu 14.04. Table definition:   Table "dealerinventorychange"     Column | Type | Modifiers  ---+

Re: [GENERAL] Recovery Assistance

2017-01-27 Thread Brian Mills
First of all, Thank you for your time to assist me learning. I really appreciate it. root# ps ax | grep post 1364 ?Ss 0:00 /usr/lib/postfix/master 5198 pts/3S 0:00 su postgres 5221 pts/3S 0:00 /usr/lib/postgresql/9.3/bin/postgres -D /etc/postgresql/9.3/main 5222 ?

Re: [GENERAL] Recovery Assistance

2017-01-27 Thread Adrian Klaver
On 01/27/2017 01:31 PM, Brian Mills wrote: Hi, I have a Atlassian Confluence Wiki that depends on postgres, but I haven't much experience with postgres other than for this purpose. A few days ago, the hard disk filled, so all services stopped working. When the admin realised this he increased t

[GENERAL] Recovery Assistance

2017-01-27 Thread Brian Mills
Hi, I have a Atlassian Confluence Wiki that depends on postgres, but I haven't much experience with postgres other than for this purpose. A few days ago, the hard disk filled, so all services stopped working. When the admin realised this he increased the disk size (its in a cloud, so that was eas

[GENERAL] Reading VARTAT EXTERNAL ONDISK value from logical replication decoder

2017-01-27 Thread Adam Dratwiński
Hello everyone, In my application I am using this logical replication decoder: https://github.com/xstevens/decoderbufs A year ago I had a problem with some values being nil due to not implemented decoding of EXTERNAL ONDISK values. I managed to hotfix it, it kind of work until now. Could someon

Re: [GENERAL] Question slow query

2017-01-27 Thread Tom Lane
John R Pierce writes: > On 1/16/2017 11:27 AM, Patrick B wrote: >> CREATE INDEX CONCURRENTLY ON note (timezone('Etc/UTC'::text, d_date)); > why did you prefix your index with timezone('etc/UTC'::text ??? that > doesn't make sense to me at all. is timezone() some function you've > defined? I'

Re: [GENERAL] Question slow query

2017-01-27 Thread John R Pierce
On 1/16/2017 11:27 AM, Patrick B wrote: Sorry about that.. just wanted to understand how to index a timestamp column. I was able to do this way: CREATE INDEX CONCURRENTLY ON note (timezone('Etc/UTC'::text, d_date)); why did you prefix your index with timezone('etc/UTC'::text ??? that

Re: [GENERAL] Question slow query

2017-01-27 Thread Venkata B Nagothi
On Tue, Jan 17, 2017 at 6:27 AM, Patrick B wrote: > > > 2017-01-12 16:48 GMT+13:00 Andreas Joseph Krogh : > >> På torsdag 12. januar 2017 kl. 03:15:59, skrev Patrick B < >> patrickbake...@gmail.com>: >> >> Hi guys, >> >> I've got a slow query, running at 25 seconds. >> >> >>

Re: [GENERAL] Transaction apply speed on the standby

2017-01-27 Thread Venkata B Nagothi
On Fri, Jan 27, 2017 at 3:34 AM, Rakesh Kumar wrote: > Ver 9.6.1 > > In a streaming replication can it be assumed that if both primary and > standby are of the same hardware, then the rate at which transactions are > applied on the standby will be same as that on primary. Or standbys are > always