Re: [GENERAL] Trim performance on 9.5

Op 18/11/2016 om 16:58 schreef William Ivanski: I just ran EXPLAIN ANALYZE, please see images attached. Field doesn't have a index. Em sex, 18 de nov de 2016 às 12:16, vinny > escreveu: On 2016-11-18 15:06, William Ivanski wrote: > Hi, > > I recently

Re: [GENERAL] Strict min and max aggregate functions

> CREATE OR REPLACE FUNCTION strict_min_agg (anyarray,anyelement ) > RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$ > SELECT CASE > WHEN $1 IS NULL THEN ARRAY[$2] > WHEN $1[1] IS NULL THEN $1 > WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve

Re: [GENERAL] [HACKERS] How to change order sort of table in HashJoin

Thanks for response, sir. On 11/20/2016 1:18 AM, Tom Lane wrote: Man Trieu writes: As in the example below, i think the plan which hash table is created on testtbl2 (the fewer tuples) should be choosen. The planner usually prefers to hash on the table that has a flatter MCV histogram, since a

Re: [GENERAL] Strict min and max aggregate functions

2016-11-19 22:12 GMT+01:00 Jeff Janes : > I need "strict" MIN and MAX aggregate functions, meaning they return NULL > upon any NULL input, and behave like the built-in aggregates if none of the > input values are NULL. > > This doesn't seem like an outlandish thing to want, and I'm surprised I > c

Re: [GENERAL] Strict min and max aggregate functions

Jeff Janes schrieb am 19.11.2016 um 22:12: I need "strict" MIN and MAX aggregate functions, meaning they return NULL upon any NULL input, and behave like the built-in aggregates if none of the input values are NULL. This doesn't seem like an outlandish thing to want, and I'm surprised I can't fi

[GENERAL] Autovacuum and frequent pg_bulkload

Hello, i use PostgreSql 8.4.22 and two times a day, i import logs wth pg_bulkload and i delete old data. I move some millions of records in a day. I noticed that only autovacuum seems not to be able to free unused space. I need a periodical vacuum full but this operations takes many hours. Do y

Re: [GENERAL] Autovacuum and frequent pg_bulkload

On 11/20/2016 04:51 AM, Job wrote: Hello, i use PostgreSql 8.4.22 and two times a day, i import logs wth pg_bulkload and i delete old data. I move some millions of records in a day. FYI, Postgres 8.4 is over two years past EOL. I noticed that only autovacuum seems not to be able to free un

R: [GENERAL] Autovacuum and frequent pg_bulkload

Hi Adrian, Thank you for your fast reply! >FYI, Postgres 8.4 is over two years past EOL. Yes, i am aware; we will migrate next year but for application compatibility reason we have at the moment to the remain here.. >What are the steps in the load/delete cycle? We need to load, with pg_bulkloa

Re: R: [GENERAL] Autovacuum and frequent pg_bulkload

On 11/20/2016 07:20 AM, Job wrote: Hi Adrian, Thank you for your fast reply! FYI, Postgres 8.4 is over two years past EOL. Yes, i am aware; we will migrate next year but for application compatibility reason we have at the moment to the remain here.. What are the steps in the load/delete cy

Re: [GENERAL] [HACKERS] How to change order sort of table in HashJoin

Man writes: > Additional information. > In 9.6 the second table (lesser tuple) was choosen (the same testdata). > There are something (cost estimation?) different in previous versions. I'd bet on different statistics in the two installations (either you forgot to ANALYZE, or the random sample ca

Re: [GENERAL] Strict min and max aggregate functions

On Sun, Nov 20, 2016 at 2:45 AM, Pavel Stehule wrote: > > > 2016-11-19 22:12 GMT+01:00 Jeff Janes : > >> I need "strict" MIN and MAX aggregate functions, meaning they return NULL >> upon any NULL input, and behave like the built-in aggregates if none of the >> input values are NULL. >> >> This do

Re: [GENERAL] Strict min and max aggregate functions

2016-11-20 20:18 GMT+01:00 Jeff Janes : > On Sun, Nov 20, 2016 at 2:45 AM, Pavel Stehule > wrote: > >> >> >> 2016-11-19 22:12 GMT+01:00 Jeff Janes : >> >>> I need "strict" MIN and MAX aggregate functions, meaning they return >>> NULL upon any NULL input, and behave like the built-in aggregates if

Re: [GENERAL] Strict min and max aggregate functions

Jeff Janes writes: > On Sun, Nov 20, 2016 at 2:45 AM, Pavel Stehule > wrote: >> you can use composite type instead array too. > I tried a composite type of (flag int, value anyelement) but you can't use > anyelement in a composite type. So the aggregate function couldn't be > polymorphic. Or,

Re: [GENERAL] Feature request: separate logging

On Sun, Nov 20, 2016 at 12:56 PM, Guillaume Lelarge wrote: > Le 18 nov. 2016 2:03 PM, "otheus uibk" a écrit : >> Finally, if these changes can be implemented, is it impossible to backport >> them to prior versions, say 9.1 and up? If I wrote a patch, under what >> conditions would the patch be ac

[GENERAL] pg_basebackup on slave running for a long time

Hi Team, We have setup PostgreSQL master-slave topology with Streaming replication setup. One of the steps for setting up streaming replication is to do pg_basebackup on slave from master. For subsequent update of this database, this step is repeated every time, deleting the existing data copy of

Re: [GENERAL] pg_basebackup on slave running for a long time

On 11/20/2016 9:29 PM, Subhankar Chattopadhyay wrote: We have setup PostgreSQL master-slave topology with Streaming replication setup. One of the steps for setting up streaming replication is to do pg_basebackup on slave from master. For subsequent update of this database, this step is repeated

Re: [GENERAL] pg_basebackup on slave running for a long time

Hi John, Thanks for reply. In situations where slave is behind master, if I don't start over, will it catch up automatically? I am using 9.4 version. On Mon, Nov 21, 2016 at 11:22 AM, John R Pierce wrote: > On 11/20/2016 9:29 PM, Subhankar Chattopadhyay wrote: >> >> We have setup PostgreSQL mast

Re: [GENERAL] pg_basebackup on slave running for a long time

On 11/20/2016 10:00 PM, Subhankar Chattopadhyay wrote: Thanks for reply. In situations where slave is behind master, if I don't start over, will it catch up automatically? I am using 9.4 version. it should stay within a few seconds under normal conditions. why is it falling behind, is your w

Re: [GENERAL] pg_basebackup on slave running for a long time

Hi John, We are using the database in a cloud setup and the nodes are running in VMs. The slave can fall behind for various reasons as you have stated, like slave is shut down or high write workload. We don't use replication slot but the wal_keep_segments is high enough for us, 5000 to be exact.

Re: [GENERAL] pg_basebackup on slave running for a long time

On 11/20/2016 10:37 PM, Subhankar Chattopadhyay wrote: We are using the database in a cloud setup and the nodes are running in VMs. The slave can fall behind for various reasons as you have stated, like slave is shut down or high write workload. We don't use replication slot but the wal_keep_seg

Re: [GENERAL] pg_basebackup on slave running for a long time

Yes so if the slave is behind I need to start over pgbasebackup. I saw according to the documentation this query gives us the replication state. Can somebody tell me if this would be sufficient to know if I need to start over the backup ? On 21 Nov 2016 12:18, "John R Pierce" wrote: > On 11/20/2

Re: [GENERAL] pg_basebackup on slave running for a long time

On 11/20/2016 11:00 PM, Subhankar Chattopadhyay wrote: Yes so if the slave is behind I need to start over pgbasebackup. I saw according to the documentation this query gives us the replication state. Can somebody tell me if this would be sufficient to know if I need to start over the backup ?