[HACKERS] Multiple synchronous_standby_names rules

[James Sewell]

Hello,

When working with a production (P) and a DR (D) environment it is often a
requirement to be able to protect against data loss when promoting within a
site, and also when losing A and promoting a master at D.

The current synchronous_standby_names do not allow this.

In a simple example we could take the following nodes:

P1 (current master), P2, P3
D1, D2, D3

Where P1 is replicating to (P2, P3, D1, D2, D3).

The closest synchronous_standby_names  setting you could get to my use case
would be:

1 (D1, D2, D3)

This would allow the loss of either site without losing data - however it
would not allow promotion within site P from P1 -> (P2 | P3)  without the
potential for data loss.

What is needed to support this is the ability to configure Px with
something like:

 1 (P1, P2, P3), 1 (D1, D2, D3)

Would there be any appetite for this - or would it be seen as over
complication of the current rules?

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect



Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com  *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>

-- 

--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Parallel Aggregate

[James Sewell]

Good news!

On Tuesday, 22 March 2016, David Rowley <david.row...@2ndquadrant.com>
wrote:

> On 22 March 2016 at 02:35, Robert Haas <robertmh...@gmail.com
> <javascript:;>> wrote:
> > I have committed this after changing some of the comments.
> >
> > There might still be bugs ... but I don't see them.  And the speedups
> > look very impressive.
> >
> > Really nice work, David.
>
> Thanks for that, and thank you for taking the time to carefully review
> it and commit it.
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org
> <javascript:;>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


-- 

James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Choosing parallel_degree

[James Sewell]

OK cool, thanks.

Can we remove the minimum size limit when the per table degree setting is
applied?

This would help for tables with 2  - 1000 pages combined with a high CPU
cost aggregate.

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Sun, Mar 20, 2016 at 11:23 PM, David Rowley <david.row...@2ndquadrant.com
> wrote:

> On 18 March 2016 at 10:13, James Sewell <james.sew...@lisasoft.com> wrote:
> > This does bring up an interesting point I don't quite understand though.
> If I run parallel agg on a table with 4 rows with 2 workers will it run on
> two workers (2 rows each) or will the first one grab all 4 rows?
> It works on a per page basis, workers just each grab the next page to
> be scanned from a page counter that sits in shared memory, the worker
> just increments the page number, releases the lock on the counter and
> scans that page.
>
> See heap_parallelscan_nextpage()
>
> So the answer to your question is probably no. At least not unless the
> the page only contained 2 rows.
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Choosing parallel_degree

[James Sewell]

On Thu, Mar 17, 2016 at 5:05 AM, Julien Rouhaud 
wrote:

>
> attached v3 drops the GUC part.
>

This looks good good. I do think that some threshold control would be good
in the long term - but you are right Robert it just feels strange.

Maybe once the final formula is implemented in 9.7+ and this gets some real
world use cases it can be revisited?

One thing I really, really like about the way the new patch works is that
you can set parallel_degree on an inheritance parent, then that will set
the minimum workers for all associated children (when accessing from the
parent).

Currently this patch will not fire on small tables even when
parallel_degree is set, can we fix this by adding a check for
ref->parallel_degree  to the table size condition?

Cheers,
James

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Parallel Aggregate

[James Sewell]

Hi again,

This is probably me missing something, but is there a reason parallel
aggregate doesn't seem to ever create append nodes containing Index scans?

SET random_page_cost TO 0.2;
SET max_parallel_degree TO 8;

postgres=# explain SELECT sum(count_i) FROM base GROUP BY view_time_day;
   QUERY PLAN
-
 Finalize GroupAggregate  (cost=310596.32..310598.03 rows=31 width=16)
   Group Key: view_time_day
   ->  Sort  (cost=310596.32..310596.79 rows=186 width=16)
 Sort Key: view_time_day
 ->  Gather  (cost=310589.00..310589.31 rows=186 width=16)
   Number of Workers: 5
   ->  Partial HashAggregate  (cost=310589.00..310589.31
rows=31 width=16)
 Group Key: view_time_day
 ->  Parallel Seq Scan on base  (cost=0.00..280589.00
rows=600 width=12)


SET max_parallel_degree TO 0;

postgres=# explain SELECT sum(count_i) FROM base GROUP BY view_time_day;
QUERY PLAN
---
 GroupAggregate  (cost=0.56..600085.92 rows=31 width=16)
   Group Key: view_time_day
   ->  Index Only Scan using base_view_time_day_count_i_idx on base
 (cost=0.56..450085.61 rows=3000 width=12)
(3 rows)


Cheers,


James Sewell,
Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Thu, Mar 17, 2016 at 8:08 AM, David Rowley <david.row...@2ndquadrant.com>
wrote:

> On 17 March 2016 at 01:29, Robert Haas <robertmh...@gmail.com> wrote:
> > On Wed, Mar 16, 2016 at 8:19 AM, Amit Kapila <amit.kapil...@gmail.com>
> wrote:
> >> Isn't it better to call it as Parallel Aggregate instead of Partial
> >> Aggregate.  Initialy, we have kept Partial for seqscan, but later on we
> >> changed to Parallel Seq Scan, so I am not able to think why it is
> better to
> >> call Partial incase of Aggregates.
> >
> > I think partial is the right terminology.  Unlike a parallel
> > sequential scan, a partial aggregate isn't parallel-aware and could be
> > used in contexts having nothing to do with parallelism.  It's just
> > that it outputs transition values instead of a finalized value.
>
> +1  the reason the partial aggregate patches have been kept separate
> from the parallel aggregate patches is that partial aggregate will
> serve for many other purposes. Parallel Aggregate is just one of many
> possible use cases for this, so it makes little sense to give it a
> name according to a single use case.
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Choosing parallel_degree

[James Sewell]

Hey,

I think are definitely use cases for using parallel agg  on a small table
when the time for each agg operation is very high. PostGIS can be used to
create many examples of low row count and table size but high CPU
operations.

This does bring up an interesting point I don't quite understand though. If
I run parallel agg on a table with 4 rows with 2 workers will it run on two
workers (2 rows each) or will the first one grab all 4 rows?

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Thu, Mar 17, 2016 at 10:30 PM, Julien Rouhaud <julien.rouh...@dalibo.com>
wrote:

> On 17/03/2016 12:21, David Rowley wrote:
> > On 18 March 2016 at 00:13, Julien Rouhaud <julien.rouh...@dalibo.com>
> wrote:
> >> With the current threshold, you need a table bigger than 8 MB to be able
> >> to force parallel workers. I'm not sure there'll be benefits for
> >> multiple workers on a table smaller than 8 MB, since setting up all the
> >> parallel stuff takes time.
> >
> > It would be really nice if it were possible to drop the setting really
> > low, so that combined with a low parallel_setup_cost we could enable
> > parallel query on small tables in the regression test suite.
> >
> >
>
> Indeed. That could also be a use case for moving parallel_threshold to a
> GUC, but not sure what'd be best.
>
> --
> Julien Rouhaud
> http://dalibo.com - http://dalibo.org
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Choosing parallel_degree

[James Sewell]

On Wed, Mar 16, 2016 at 11:26 AM, Julien Rouhaud 
wrote:

>
> I'm not too familiar with parallel planning, but I tried to implement
> both in attached patch. I didn't put much effort into the
> parallel_threshold GUC documentation, because I didn't really see a good
> way to explain it. I'd e happy to improve it if needed. Also, to make
> this parameter easier to tune for users, perhaps we could divide the
> default value by 3 and use it as is in the first iteration in
> create_parallel_path() ?
>
> Also, global max_parallel_degree still needs to be at least 1 for the
> per table value to be considered.
>
>
All applies and works from my end.

Is the max_parallel_degree per table of much use here? It allows the max
number of workers per table to be set - but it's still bound by the same
formula (now from the GUC). So in reality it's only really useful for
limiting the number of workers, not raising it.

Would there be a common use case for limiting parallelism on a subset of
tables in a database you've explicitly set to have a higher amount
of parallel operations via the GUC? I struggle to think of one?

I think in practicality the reverse would be more common, you'd want to set
certain tables to a starting point of a certain number of workers (and ramp
up to more if the formula allowed it). You could set this to 0 for
never use parallel
agg on this table.

Another option is to allow access to the the threshold multiplier
(currently hard coded to 3) per table - but this might become pretty hard
to explain succinctly in the documentation.

Cheers,
James

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Choosing parallel_degree

[James Sewell]

Thanks David,

Eventually it would be great to take into account the cost of the function
doing the agg (pg_proc.procost, which is a multiple of CPU units).

This would allow people to mark specific aggregations as needing more CPU
power, therefore needing more workers per page (or should it be tuple in
this case?).

In the meantime some way to manually influence this would be good. I just
did some testing (on an 8VCPU machine) with a 139MB table, which gets 3
workers currently.

For a count(*) I get a time of 131.754 ms. If I increase this to 8 workers
I get around 86.193 ms.

Obviously this doesn't mean much as YMMV - but it does show that the
ability to manually adjust the scaling would be great, especially in
data warehouse or reporting environments.

I did want to test with some really slow aggs, but even when I take out the
small table test in create_parallel_paths I can't seem to get a parallel
plan for a tiny table. Any idea on why this would be David?


Cheers,













James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Tue, Mar 15, 2016 at 12:25 PM, David Rowley <david.row...@2ndquadrant.com
> wrote:

> Over in [1] James mentioned about wanting more to be able to have more
> influence over the partial path's parallel_degree decision.  At risk
> of a discussion on that hijacking the parallel aggregate thread, I
> thought I'd start this for anyone who would want to discuss making
> changes to that.
>
> I've attached a simple C program which shows the parallel_degree which
> will be chosen at the moment. For now it's based on the size of the
> base relation. Perhaps that will need to be rethought later, perhaps
> based on costs. But I just don't think it's something for 9.6.
>
> Here's the output of the C program.
>
> For 1 pages there will be 1 workers (rel size 0 MB, 0 GB)
> For 3001 pages there will be 2 workers (rel size 23 MB, 0 GB)
> For 9001 pages there will be 3 workers (rel size 70 MB, 0 GB)
> For 27001 pages there will be 4 workers (rel size 210 MB, 0 GB)
> For 81001 pages there will be 5 workers (rel size 632 MB, 0 GB)
> For 243001 pages there will be 6 workers (rel size 1898 MB, 1 GB)
> For 729001 pages there will be 7 workers (rel size 5695 MB, 5 GB)
> For 2187001 pages there will be 8 workers (rel size 17085 MB, 16 GB)
> For 6561001 pages there will be 9 workers (rel size 51257 MB, 50 GB)
> For 19683001 pages there will be 10 workers (rel size 153773 MB, 150 GB)
> For 59049001 pages there will be 11 workers (rel size 461320 MB, 450 GB)
> For 177147001 pages there will be 12 workers (rel size 1383960 MB, 1351 GB)
> For 531441001 pages there will be 13 workers (rel size 4151882 MB, 4054 GB)
> For 1594323001 pages there will be 14 workers (rel size 12455648 MB, 12163
> GB)
>
> [1]
> http://www.postgresql.org/message-id/CANkGpBtUvzpdvF2=_iq64ujmvrpycs6d4i9-wepbusq1sq+...@mail.gmail.com
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Parallel Aggregate

[James Sewell]

On Tue, Mar 15, 2016 at 9:32 AM, Robert Haas  wrote:

>
> I kind of doubt this would work well, but somebody could write a patch
> for it and try it out.


OK I'll give this a go today and report back.

Would the eventual plan be to use pg_proc.procost for the functions from
each aggregate concerned? If so I might have a peek at that too, although I
imagine I won't get far.

Cheers,

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Parallel Aggregate

[James Sewell]

On Tuesday, 15 March 2016, Robert Haas  wrote:
>
> > Does the cost of the aggregate function come into this calculation at
> > all? In PostGIS land, much smaller numbers of rows can generate loads
> > that would be effective to parallelize (worker time much >> than
> > startup cost).
>
> Unfortunately, no - only the table size.  This is a problem, and needs
> to be fixed.  However, it's probably not going to get fixed for 9.6.
> :-(
>

Any chance of getting a GUC (say min_parallel_degree) added to allow
setting the initial value of parallel_degree, then changing the small
relation check to also pass if parallel_degree > 1?

That way you could set min_parallel_degree on a query by query basis if you
are running aggregates which you know will take a lot of CPU.

I suppose it wouldn't make much sense at all to set globally though, so it
could just confuse matters.

Cheers,

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Parallel Aggregate

[James Sewell]

On Mon, Mar 14, 2016 at 3:05 PM, David Rowley 
wrote:

>
> Things to try:
> 1. alter table a add column ts_date date; update a set ts_date =
> date_trunc('DAY',ts); vacuum full analyze ts;
> 2. or, create index on a (date_trunc('DAY',ts)); analyze a;
> 3. or for testing, set the work_mem higher.
>
>
Ah, that makes sense.

Tried with a BTREE index, and it works as perfectly but the index is 428MB
- which is a bit rough.

Removed that and put on a BRIN index, same result for 48kB - perfect!

Thanks for the help,

James

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Parallel Aggregate

[James Sewell]

Hi again,

I've been playing around with inheritance combined with this patch.
Currently it looks like you are taking max(parallel_degree) from all the
child tables and using that for the number of workers.

For large machines it makes much more sense to use sum(parallel_degree) -
but I've just seen this comment in the code:

/*
 * Decide what parallel degree to request for this append path.  For
 * now, we just use the maximum parallel degree of any member.  It
 * might be useful to use a higher number if the Append node were
 * smart enough to spread out the workers, but it currently isn't.
 */

Does this mean that even though we are aggregating in parallel, we are only
operating on one child table at a time currently?

Cheers,

James Sewell,
 Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Mon, Mar 14, 2016 at 2:39 PM, James Sewell <james.sew...@lisasoft.com>
wrote:

> Cool,
>
> I've been testing how this works with partitioning (which seems to be
> strange, but I'll post separately about that) and something odd seems to be
> going on now with the parallel triggering:
>
> postgres=# create table a as select * from base_p2015_11;
> SELECT 2000
>
> postgres=# select * from a limit 1;
>  ts | count |  a  |  b   |  c   |  d   | e
> +---+-+--+--+--+---
>  2015-11-26 21:10:04.856828 |   860 | 946 | 1032 | 1118 | 1204 |
> (1 row)
>
> postgres-# \d a
>  Table "datamart_owner.a"
>  Column |Type | Modifiers
> +-+---
>  ts | timestamp without time zone |
>  count  | integer |
>  a  | integer |
>  b  | integer |
>  c  | integer |
>  d  | integer |
>  e  | integer |
>
> postgres=# select pg_size_pretty(pg_relation_size('a'));
>  pg_size_pretty
> 
>  1149 MB
>
> postgres=# explain  select sum(count) from a group by date_trunc('DAY',ts);
>   QUERY PLAN
>
> --
>  Finalize GroupAggregate  (cost=218242.96..218254.46 rows=200 width=16)
>Group Key: (date_trunc('DAY'::text, ts))
>->  Sort  (cost=218242.96..218245.96 rows=1200 width=16)
>  Sort Key: (date_trunc('DAY'::text, ts))
>  ->  Gather  (cost=218059.08..218181.58 rows=1200 width=16)
>Number of Workers: 5
>->  Partial HashAggregate  (cost=217059.08..217061.58
> rows=200 width=16)
>  Group Key: date_trunc('DAY'::text, ts)
>  ->  Parallel Seq Scan on a  (cost=0.00..197059.06
> rows=405 width=12)
> (9 rows)
>
> postgres=# analyze a;
>
> postgres=# explain  select sum(count) from a group by date_trunc('DAY',ts);
> QUERY PLAN
> --
>  GroupAggregate  (cost=3164211.55..3564212.03 rows=2024 width=16)
>Group Key: (date_trunc('DAY'::text, ts))
>->  Sort  (cost=3164211.55..3214211.61 rows=20000024 width=12)
>  Sort Key: (date_trunc('DAY'::text, ts))
>  ->  Seq Scan on a  (cost=0.00..397059.30 rows=2024 width=12)
> (5 rows)
>
> Unsure what's happening here.
>
>
>
> James Sewell,
> PostgreSQL Team Lead / Solutions Architect
> __
>
>
> Level 2, 50 Queen St, Melbourne VIC 3000
>
> *P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099
>
>
> On Mon, Mar 14, 2016 at 1:31 PM, David Rowley <
> david.row...@2ndquadrant.com> wrote:
>
>> On 14 March 2016 at 14:52, James Sewell <james.sew...@lisasoft.com>
>> wrote:
>> > One question - how is the upper limit of workers chosen?
>>
>> See create_parallel_paths() in allpaths.c. Basically the bigger the
>> relation (in pages) the more workers will be allocated, up until
>> max_parallel_degree.
>>
>> There is also a comment in that function which states:
>> /*
>> * Limit the degree of parallelism logarithmically based on the size of the
>> * relation.  This probably needs to be a good deal more sophisticated,
>> but we
>> * need something here for now.
>> */
>>
>> So this will likely see some revision at some point, after 9.6.
>>
>> --
>>  David Rowley   http://www.

Re: [HACKERS] Parallel Aggregate

[James Sewell]

Cool,

I've been testing how this works with partitioning (which seems to be
strange, but I'll post separately about that) and something odd seems to be
going on now with the parallel triggering:

postgres=# create table a as select * from base_p2015_11;
SELECT 2000

postgres=# select * from a limit 1;
 ts | count |  a  |  b   |  c   |  d   | e
+---+-+--+--+--+---
 2015-11-26 21:10:04.856828 |   860 | 946 | 1032 | 1118 | 1204 |
(1 row)

postgres-# \d a
 Table "datamart_owner.a"
 Column |Type | Modifiers
+-+---
 ts | timestamp without time zone |
 count  | integer |
 a  | integer |
 b  | integer |
 c  | integer |
 d  | integer |
 e  | integer |

postgres=# select pg_size_pretty(pg_relation_size('a'));
 pg_size_pretty

 1149 MB

postgres=# explain  select sum(count) from a group by date_trunc('DAY',ts);
  QUERY PLAN
--
 Finalize GroupAggregate  (cost=218242.96..218254.46 rows=200 width=16)
   Group Key: (date_trunc('DAY'::text, ts))
   ->  Sort  (cost=218242.96..218245.96 rows=1200 width=16)
 Sort Key: (date_trunc('DAY'::text, ts))
 ->  Gather  (cost=218059.08..218181.58 rows=1200 width=16)
   Number of Workers: 5
   ->  Partial HashAggregate  (cost=217059.08..217061.58
rows=200 width=16)
 Group Key: date_trunc('DAY'::text, ts)
 ->  Parallel Seq Scan on a  (cost=0.00..197059.06
rows=405 width=12)
(9 rows)

postgres=# analyze a;

postgres=# explain  select sum(count) from a group by date_trunc('DAY',ts);
QUERY PLAN
--
 GroupAggregate  (cost=3164211.55..3564212.03 rows=2024 width=16)
   Group Key: (date_trunc('DAY'::text, ts))
   ->  Sort  (cost=3164211.55..3214211.61 rows=2024 width=12)
 Sort Key: (date_trunc('DAY'::text, ts))
 ->  Seq Scan on a  (cost=0.00..397059.30 rows=2024 width=12)
(5 rows)

Unsure what's happening here.



James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Mon, Mar 14, 2016 at 1:31 PM, David Rowley <david.row...@2ndquadrant.com>
wrote:

> On 14 March 2016 at 14:52, James Sewell <james.sew...@lisasoft.com> wrote:
> > One question - how is the upper limit of workers chosen?
>
> See create_parallel_paths() in allpaths.c. Basically the bigger the
> relation (in pages) the more workers will be allocated, up until
> max_parallel_degree.
>
> There is also a comment in that function which states:
> /*
> * Limit the degree of parallelism logarithmically based on the size of the
> * relation.  This probably needs to be a good deal more sophisticated, but
> we
> * need something here for now.
> */
>
> So this will likely see some revision at some point, after 9.6.
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Parallel Aggregate

[James Sewell]

Hi,

Happy to test, really looking forward to seeing this stuff in core.

The explain analyze is below:

Finalize HashAggregate  (cost=810142.42..810882.62 rows=59216 width=16)
(actual time=2282.092..2282.202 rows=15 loops=1)
   Group Key: (date_trunc('DAY'::text, pageview_start_tstamp))
   ->  Gather  (cost=765878.46..808069.86 rows=414512 width=16) (actual
time=2281.749..2282.060 rows=105 loops=1)
 Number of Workers: 6
 ->  Partial HashAggregate  (cost=764878.46..765618.66 rows=59216
width=16) (actual time=2276.879..2277.030 rows=15 loops=7)
   Group Key: date_trunc('DAY'::text, pageview_start_tstamp)
   ->  Parallel Seq Scan on celebrus_fact_agg_1_p2015_12
 (cost=0.00..743769.76 rows=4221741 width=12) (actual time=0.066..1631
.650 rows=3618887 loops=7)

One question - how is the upper limit of workers chosen?


James Sewell,
Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Mon, Mar 14, 2016 at 12:30 PM, David Rowley <david.row...@2ndquadrant.com
> wrote:

> On 14 March 2016 at 14:16, James Sewell <james.sew...@lisasoft.com> wrote:
>
>> I've done some testing with one of my data sets in an 8VPU virtual
>> environment and this is looking really, really good.
>>
>> My test query is:
>>
>> SELECT pageview, sum(pageview_count)
>> FROM fact_agg_2015_12
>> GROUP BY date_trunc('DAY'::text, pageview);
>>
>> The query returns 15 rows. The fact_agg table is 5398MB and holds around
>> 25 million records.
>>
>> Explain with a max_parallel_degree of 8 tells me that the query will
>> only use 6 background workers. I have no indexes on the table currently.
>>
>> Finalize HashAggregate  (cost=810142.42..810882.62 rows=59216 width=16)
>>Group Key: (date_trunc('DAY'::text, pageview))
>>->  Gather  (cost=765878.46..808069.86 rows=414512 width=16)
>>  Number of Workers: 6
>>  ->  Partial HashAggregate  (cost=764878.46..765618.66 rows=59216
>> width=16)
>>Group Key: date_trunc('DAY'::text, pageview)
>>->  Parallel Seq Scan on fact_agg_2015_12
>>  (cost=0.00..743769.76 rows=4221741 width=12)
>>
>
> Great! Thanks for testing this.
>
> If you run EXPLAIN ANALYZE on this with the 6 workers, does the actual
> number of Gather rows come out at 105? I'd just like to get an idea of my
> cost estimate for the Gather are going to be accurate for real world data
> sets.
>
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Parallel Aggregate

[James Sewell]

Hi,

I've done some testing with one of my data sets in an 8VPU virtual
environment and this is looking really, really good.

My test query is:

SELECT pageview, sum(pageview_count)
FROM fact_agg_2015_12
GROUP BY date_trunc('DAY'::text, pageview);

The query returns 15 rows. The fact_agg table is 5398MB and holds around 25
million records.

Explain with a max_parallel_degree of 8 tells me that the query will only
use 6 background workers. I have no indexes on the table currently.

Finalize HashAggregate  (cost=810142.42..810882.62 rows=59216 width=16)
   Group Key: (date_trunc('DAY'::text, pageview))
   ->  Gather  (cost=765878.46..808069.86 rows=414512 width=16)
 Number of Workers: 6
 ->  Partial HashAggregate  (cost=764878.46..765618.66 rows=59216
width=16)
   Group Key: date_trunc('DAY'::text, pageview)
   ->  Parallel Seq Scan on fact_agg_2015_12
 (cost=0.00..743769.76 rows=4221741 width=12)


I am getting the following timings (everything was cached before I started
tested). I didn't average the runtime, but I ran each one three times and
took the middle value.

*max_parallel_degree runtime*
0  11693.537 ms
1  6387.937 ms
2 4328.629 ms
3 3292.376 ms
4 2743.148 ms
5 2278.449 ms
6 2000.599 ms


I'm pretty happy!

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Mon, Mar 14, 2016 at 8:44 AM, David Rowley <david.row...@2ndquadrant.com>
wrote:

> On 12 March 2016 at 16:31, David Rowley <david.row...@2ndquadrant.com>
> wrote:
> > I've attached an updated patch which is based on commit 7087166,
> > things are really changing fast in the grouping path area at the
> > moment, but hopefully the dust is starting to settle now.
>
> The attached patch fixes a harmless compiler warning about a possible
> uninitialised variable.
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Random note of encouragement

[James Sewell]

Argh seems like a false alarm for now.

I installed 9.5 from RPM source (the other was one I had installed
previously) and the performance matched 9.6

Sorry about that, I must have *something* screwed up on the other one.

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] Random note of encouragement

[James Sewell]

I've actually just tested this on 9.3 - and I get roughly the same as
9.6devel.

Now going back to make sure my 9.5 environment is sane.

Hopefully this isn't me jumping the gun.

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Thu, Feb 25, 2016 at 10:50 AM, Thomas Munro <
thomas.mu...@enterprisedb.com> wrote:

> On Thu, Feb 25, 2016 at 12:26 PM, Bruce Momjian <br...@momjian.us> wrote:
> > On Thu, Feb 25, 2016 at 10:06:34AM +1100, James Sewell wrote:
> >> Now when I run the following SQL (multiple times to allow for getting
> >> everything into shared buffers, which is 4GB on my machine):
> >>
> >>
> >> select sum(count_n) from base group by view_time_day;
> >>
> >>
> >> I get the following results:
> >>
> >>
> >> PSQL 9.5 - ~21 seconds
> >> PSQL 9.6 devel - ~8.5 seconds
> >>
> >>
> >> I think that's pretty good!
> >>
> >> I know this is a devel release, things may change, blah blah. But still,
> >> something has changed for the better here!
> >
> > Wow, that is cool.  Can anyone suggest which commit improved this?
>
> Since it sums numerics, maybe integer transition functions from commit
> 959277a4f579da5243968c750069570a58e92b38 helped?
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

[HACKERS] Random note of encouragement

[James Sewell]

Hey All,

I've been doing some (futile) work trying to speed up aggregates with a
group by in PostgreSQL 9.5.

I installed PostgreSQL 9.6 on the same machine to see if I could get
anything running in parallel when using partitioning - which didn't work.

But - I did find this:

With the following setup:

CREATE TABLE base(
view_time TIMESTAMP WITHOUT time ZONE,
view_time_day TIMESTAMP WITHOUT time ZONE,
count_n numeric);


INSERT INTO base
SELECT view_time,
   date_trunc('day', view_time),
   COUNT::numeric,
FROM
  (SELECT
  TIMESTAMP '2015-12-01' + random() * interval '30 days' AS view_time,
trunc(random() * 99 + 1) AS COUNT
   FROM generate_series(1,3000)) a;

analyze base;

Now when I run the following SQL (multiple times to allow for getting
everything into shared buffers, which is 4GB on my machine):

select sum(count_n) from base group by view_time_day;


I get the following results:

PSQL 9.5 - *~21 seconds*
PSQL 9.6 devel - *~8.5 seconds*


I think that's pretty good!

I know this is a devel release, things may change, blah blah. But still,
something has changed for the better here!

I get the same plan on both nodes:

 HashAggregate  (cost=670590.56..670590.95 rows=31 width=13)
   Group Key: view_time_day
   ->  Seq Scan on base  (cost=0.00..520590.04 rows=3104 width=13)

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

[HACKERS] Streaming Replication clusters and load balancing

[James Sewell]

Hello all,

I have recently been working with PostgreSQL and HAProxy to provide
seamless load balancing to a group of database servers. This on it's own
isn't a hard thing: I have an implementation finished and am now thinking
about the best way to bring it to a production ready state which could be
used by others, and used in load-balancers other than HAProxy with minimal
config changes.

My initial requirements were:

Given a group of PostgreSQL servers check each x seconds and:

   - Allow read/write access only to the master server (via IPA / portA)
  - Disallow access if there are multiple master servers




   - Allow read access to all servers (via IPB / portB) as long as the
   following holds:
  - They are attached to the current master server via streaming
  replication (or they are the current master server)
  - They can currently contact the master server (safest option,
  disallow all access when master-less)
  - They are in the same timeline as the master server (do I need this
  check?)
  - The master server reports that they have less than x bytes lag



HAProxy can talk to PostgreSQL for a health check via TCP or PSQL
(connection check only). Neither of these allow the logic above - therefore
this logic has to be hosted outside of HAProxy. This might change in the
future if HAProxy gets the ability to send SQL statements (like an F5 can).

Today the best way to provide this information to  HAProxy (and many other
load balancers, application frameworks, proxies, monitoring solutions) is
via HTTP, where HTTP 200 is pass the check and HTTP 503 is fail the check
(and don't load balance to this node). In my case I have a script which
accepts HTTP requests to /read to check if this node is available for read
only and /write which checks if this node is available for read/writes.

The options as I see them are:

   - Implement a script / small app which connects to PostgreSQL and
   executes these checks
  - Implemented and proven today at many independent sites
  - Should it run on HAProxy server or PSQL server?
  - Integrated HTTP server  or x.inetd script?
  - Platform independence?
  - What if it dies?


   - Implement a custom PostgreSQL BGworker which provides this information
   over HTTP
  - No outside of PostgreSQL config needed
  - No reliance on another daemon / interface being up
  - libmicrohttpd or similar should help with  platform independence
  - Security / acceptance by community?
  - Only newer versions of PostgreSQL


   - Spend the time working on getting SQL checks into HAProxy
  - What about other platforms which only support HTTP?

I think all of the options would benefit from a PSQL extension which does
the following:

   - Encapsulates the check logic (easier to upgrade, manipulate)
   - Stores historic check data for a number of hours / days / months
   - Stores defaults (override via HTTP could be possible for things like
   lag)

Does anyone else have any thoughts on this topic?

Eventually many cool features could flow out of this kind of work:

   - Integration with High Availability products - I have this working with
   EnterpriseDB EFM now.
  - Locate the current master using the HA product
 - more than one master doesn't cause loss of service as long as HA
 state is sane
  - Locate all clustered standby servers using the HA product
 - if a standby is removed from the HA cluster, it is removed from
 load balancing
 - if a standby is not part of the cluster, it is removed from load
 balancing (even if it is part of streaming replication)


   - HTTP replication status requests which facilitate dynamically managing
   HAProxy (or other) PostgreSQL server pools
  - Add a node to streaming replication, it automatically shows up in
  the pool and starts being checked to see if it can service reads
  - Great for cloud scale out


   - Allocation of additional load balancer groups based on some criteria
   (?), for example
  - read/write (as above)
  - read only (as above)
  - data warehouse (reporting reads only)
  - DR (replica with no reads or writes - until it becomes a master)

Keen to hear comments.

Cheers,

James Sewell,
Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

[HACKERS] Composite index and min()

[James Sewell]

Hello,

I have the following table:

 \d a
 Table phxconfig.a
Column |  Type   | Modifiers
---+-+---
 phx_run_id| integer |
 cell_id   | integer |
Indexes:
a_phx_run_id_cell_id_idx btree (phx_run_id, cell_id)

When I use a min() query I get the following plans:

test=# explain select min(phx_run_id) from a;
  QUERY PLAN
---
 Result  (cost=0.22..0.23 rows=1 width=0)
   InitPlan 1 (returns $0)
 -  Limit  (cost=0.14..0.22 rows=1 width=4)
   -  Index Only Scan using a_phx_run_id_cell_id_idx on a
 (cost=0.14..7.89 rows=100 width=4)
 Index Cond: (phx_run_id IS NOT NULL)

test=# explain select min(cell_id) from a;
   QUERY PLAN
-
 Aggregate  (cost=2.25..2.26 rows=1 width=4)
   -  Seq Scan on a  (cost=0.00..2.00 rows=100 width=4)

Can anyone comment on why this happens?

The index  kicks in when I do an explicit cell_id comparison. These are
large tables, and they are in a partition layout so it really hurts when I
do the min call on the parent table.

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

[HACKERS] ADD FOREIGN KEY locking

[James Sewell]

Hello all,

When I add a FK with a statement like this:

ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id);

I see a lock on table b:

select locktype,mode,granted from pg_locks, pg_stat_activity where
relation::regclass::text = 'b' AND pg_locks.pid = pg_stat_activity.pid;

locktype | relation
mode | AccessShareLock
granted  | t
query | SOME LONG RUNNING QUERY WHICH SELECTS FROM b

locktype | relation
mode | AccessExclusiveLock
granted  | f
query | ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id);


This means that my add key won't complete until my long running query does.
That seems a bit odd to me? In this database there are lots of
datawarehouse type queries running, which makes it a bit hard for me to
schedule this operation.

Is this just a manifestation of adding the key being in an ALTER TABLE,
which always needs an AccessExclusiveLock? Or am I missing some edge case
when this lock would be required in this circumstance?

No real urgency on this question, I just found it a bit strange and thought
someone might be able to shed some light.

James Sewell,
Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] ADD FOREIGN KEY locking

[James Sewell]

Oh,

I've just noticed something in the Commit fest post

- Reducing lock strength of trigger and foreign key DDL

Perhaps I just need to be more patient.

Cheers,


James Sewell,
 Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099


On Wed, Feb 18, 2015 at 10:57 AM, James Sewell james.sew...@lisasoft.com
wrote:

 Hello all,

 When I add a FK with a statement like this:

 ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id);

 I see a lock on table b:

 select locktype,mode,granted from pg_locks, pg_stat_activity where
 relation::regclass::text = 'b' AND pg_locks.pid = pg_stat_activity.pid;

 locktype | relation
 mode | AccessShareLock
 granted  | t
 query | SOME LONG RUNNING QUERY WHICH SELECTS FROM b

 locktype | relation
 mode | AccessExclusiveLock
 granted  | f
 query | ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id);


 This means that my add key won't complete until my long running query
 does. That seems a bit odd to me? In this database there are lots of
 datawarehouse type queries running, which makes it a bit hard for me to
 schedule this operation.

 Is this just a manifestation of adding the key being in an ALTER TABLE,
 which always needs an AccessExclusiveLock? Or am I missing some edge case
 when this lock would be required in this circumstance?

 No real urgency on this question, I just found it a bit strange and
 thought someone might be able to shed some light.

 James Sewell,
 Solutions Architect
 __


  Level 2, 50 Queen St, Melbourne VIC 3000

 *P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099



-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] PostgreSQL Failback without rebuild

[James Sewell]

I've just noticed that on PostgreSQL 9.3 I can do the following with a
master node A and a slave node B (as long as I have set
recovery_target_timeline = 'latest'):

   1. Stop Node A
   2. Promote Node B
   3. Attach Node A as slave

This is sufficient for my needs (I know it doesn't cover a crash), can
anyone see any potential problems with this approach?


Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099



On Wed, Feb 5, 2014 at 6:03 PM, Michael Paquier
michael.paqu...@gmail.comwrote:

 On Wed, Feb 5, 2014 at 3:14 PM, Amit Kapila amit.kapil...@gmail.com
 wrote:
  On Wed, Feb 5, 2014 at 10:30 AM, James Sewell james.sew...@lisasoft.com
 
  I've seen some proposals and a tool (pg_rewind), but all seem to have
 draw
  backs.
 
  As far as I remember, one of the main drawbacks for pg_rewind was
 related to
  hint bits which can be avoided by wal_log_hints. pg_rewind is not part of
  core
  PostgreSQL code, however if you wish, you can try that tool to see if
 can it
  solve your purpose.
 For 9.3, pg_rewind is only safe with page checksums enabled. For 9.4,
 yes wal_log_hints or checksums is mandatory. The code contains as well
 some safety checks as well to ensure that a node not using those
 parameters cannot be rewinded.
 Regards,
 --
 Michael


-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] PostgreSQL Failback without rebuild

[James Sewell]

Node A could get ahead even if it has been shut down cleanly BEFORE the
promotion?

I'd always assumed if I shut down the master the slave would be at the same
point after shutdown - is this incorrect?

Cheers,


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099



On Fri, Feb 7, 2014 at 4:58 PM, Michael Paquier
michael.paqu...@gmail.comwrote:




 On Fri, Feb 7, 2014 at 1:57 PM, James Sewell james.sew...@lisasoft.comwrote:

 I've just noticed that on PostgreSQL 9.3 I can do the following with a
 master node A and a slave node B (as long as I have set
 recovery_target_timeline = 'latest'):

1. Stop Node A
2. Promote Node B
3. Attach Node A as slave

 This is sufficient for my needs (I know it doesn't cover a crash), can
 anyone see any potential problems with this approach?

 Yes, node A could get ahead of the point where WAL forked when promoting
 B. In this case you cannot reconnect A to B, and need to actually recreate
 a node from a fresh base backup, or rewind it. pg_rewind targets the
 latter, postgres core is able to to the former, and depending on things
 like your environment and/or the size of your server, you might prefer one
 or the other.
 Regards,
 --
 Michael


-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

[HACKERS] PostgreSQL Failback without rebuild

[James Sewell]

Hello All,

I have been reading through some of the recent discussions about failback
when in a streaming replication setup. I define failback as:


   1. Node A is master, Node B is slave
   2. Node A crashes || Node A is stopped || nothing happens
   3. Promote Node B to Master
   4. Attach Node A as slave

My understanding is currently to achieve step three you need to take a base
backup of Node B and deploy it to Node A before starting streaming
replication (or use rsync etc...).

This is very undesirable for many users, especially if they have a very
large database.

From the discussions I can see that the problem is to do with Node A
writing changes to disk that Node B are not streamed before Node A crashes.

Has there been any consensus on this issue? Are there any solutions which
might make it into 9.4 or 9.5? I've seen some proposals and a tool
(pg_rewind), but all seem to have draw backs.

I've been looking mainly at these threads:

http://www.postgresql.org/message-id/CAF8Q-Gy7xa60HwXc0MKajjkWFEbFDWTG=ggyu1kmt+s2xcq...@mail.gmail.com

http://www.postgresql.org/message-id/caf8q-gxg3pqtf71nvece-6ozraew5pwhk7yqtbjgwrfu513...@mail.gmail.com

http://www.postgresql.org/message-id/519df910.4020...@vmware.com

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] [PATCH] Add an ldapoption to disable chasing LDAP referrals

[James Sewell]

Hey All,

I had missed these emails, sorry.

The search+bind mode issue is one of documentation location, I have fixed
it by moving the section to the applied to both list. As the patch is to do
with post-auth response this is correct.

As far as the issue when something other than 0 or 1 is set I am happy
throw an error (although this doesn't seem to be how option such as LDAPTLS
work: 1 if 1 else 0). I assume I would use the ereport() function to do
this (using the second example from this page
http://www.postgresql.org/docs/9.2/static/error-message-reporting.html)?

Cheers,
James


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000 * **W* www.lisasoft.com  *F *(+61) 3 8370 8099



On Thu, Sep 19, 2013 at 12:56 AM, Peter Eisentraut pete...@gmx.net wrote:

 On 7/8/13 9:33 PM, James Sewell wrote:
  New patch attached. I've moved from using a boolean to an enum trivalue.

 When ldapreferrals is set to something other than 0 or 1 exactly, it
 just ignores the option.  That's not good, I think.  It should probably
 be an error.



-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] PSQL return coder

[James Sewell]

I was avoiding ON_ERROR_STOP because I was using ON_ERROR_ROLLBACK, but
have just realised that if I encase my SQL in a transaction then rollback
will still happen.

Perfect!


James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000 * **W* www.lisasoft.com  *F *(+61) 3 8370 8099



On Fri, Oct 11, 2013 at 12:25 AM, Merlin Moncure mmonc...@gmail.com wrote:

 On Thu, Oct 10, 2013 at 1:52 AM, Tom Lane t...@sss.pgh.pa.us wrote:
  James Sewell james.sew...@lisasoft.com writes:
  My question is in a rollback scenario is it possible to get PSQL to
 return
  a non 0 exit status?
 
  Maybe you could use -c instead of -f?
 
  $ psql -c 'select 1; select 1/0' regression
  ERROR:  division by zero
  $ echo $?
  1
 
  You won't need explicit BEGIN/END because this is already a single
  transaction.

 According to the man page,
 EXIT STATUS
psql returns 0 to the shell if it finished normally, 1 if a fatal
 error
of its own (out of memory, file not found) occurs, 2 if the
  connection
to the server went bad and the session was not interactive, and 3
 if an
error occurred in a script and the variable ON_ERROR_STOP was set.

 So for a longer script ON_ERROR_STOP might be the ticket (which is
 usually a good idea anyways).

 merlin


-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

[HACKERS] PSQL return coder

[James Sewell]

Hello,

I am using PSQL to run SQL from a file with the -f flag as follows:

BEGIN
SQL
SQL
...
END

This gives me rollback on error and a nicer output than -1. This works fine.

My question is in a rollback scenario is it possible to get PSQL to return
a non 0 exit status?

Cheers,a
James



-- 

James Sewell,
PostgreSQL Team Lead / Solutions Architect
__


 Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000 * **W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [HACKERS] pg_dump and schema names

[James Sewell]

I was under the impression that every command that references a relation
makes use of the search path, regardless of what it is *doing*. Maybe this
is different in older versions though?

I actually ran into this recently and had to remove all the xx. schema
components using vi before I could run the dump to move to a different
schema.

Just to be sure:

postgres=# create schema test;
CREATE SCHEMA
postgres=# set search_path to test;
SET
postgres=# create table test1(id serial);
NOTICE:  CREATE TABLE will create implicit sequence test1_id_seq for
serial column test1.id
CREATE TABLE
postgres=# alter table test1 owner to postgres;
ALTER TABLE


Cheers,

James Sewell
Solutions Architect
_

[image:
http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]

Level 2, 50 Queen St,
Melbourne, VIC, 3000

P: 03 8370 8000   F: 03 8370 8099  W: www.lisasoft.com



On Fri, Aug 9, 2013 at 2:04 PM, Bruce Momjian br...@momjian.us wrote:

 pg_dump goes to great lengths not to hard-code the schema name into
 commands like CREATE TABLE, instead setting the search_path before
 creating the table;  these commands:

 CREATE SCHEMA xx;
 CREATE TABLE xx.test(x int);

 generates this output:

 SET search_path = xx, pg_catalog;
 CREATE TABLE test (
 x integer
 );

 If you dump a schema and want to reload it into another schema, you
 should only need to update that one search_path line.  However, later in
 the dump file, we hardcode the schema name for setting the object owner:

 ALTER TABLE xx.test OWNER TO postgres;

 Could we use search_path here to avoid the schema designation?  I am not
 sure this possible because while CREATE defaults to the first existing
 schema in the search_path, I am not sure ALTER has the same behavior
 because you are not _creating_ something with ALTER.

 --
   Bruce Momjian  br...@momjian.ushttp://momjian.us
   EnterpriseDB http://enterprisedb.com

   + It's impossible for everything to be true. +


 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.
image001.png

Re: [HACKERS] [PATCH] Add an ldapoption to disable chasing LDAP referrals

[James Sewell]

Hey,

New patch attached. I've moved from using a boolean to an enum trivalue.

Let me know what you think.

Cheers,
James

James Sewell
PostgreSQL Team Lead / Solutions Architect
_

[image:
http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]

Level 2, 50 Queen St,
Melbourne, VIC, 3000

P: 03 8370 8000   F: 03 8370 8099  W: www.lisasoft.com



On Thu, Jul 4, 2013 at 8:23 PM, Magnus Hagander mag...@hagander.net wrote:


 On Thu, Jul 4, 2013 at 2:30 AM, James Sewell james.sew...@lisasoft.comwrote:

 Heya,

 I see what you are saying, the problem as I see it is that the action we
 are taking here is disable chasing ldap referrals. If the name is
 ldapreferrals and we use a boolean then setting it to 1  reads in a counter
 intuitive manner:


 That assumes that the default in the ldap library is always going to be to
 chase them. Does the standard somehow mandate that it should be?


   set ldapreferals=true to disable chasing LDAP referrals.


 You'd obviously reverse the meaning as well. set ldapreferals=false to
 disable chasing LDAP referrals.


 Perhaps you are fine with this though if it's documented? It does work in
 the inverse way to pam_ldap, where setting to true enables referral
 chasing. pam_ldap works like so:

   not set  : library default
   set to 0 : disable referral chasing
   set to 1 : enable referral chasing


 That is exactly what I'm suggesting it should do, and I'm pretty sure
 that's what Peter suggested as well.



 --
  Magnus Hagander
  Me: http://www.hagander.net/
  Work: http://www.redpill-linpro.com/


-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.
image001.png

pgsql_ldapnochaseref_v1.2.diff
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add an ldapoption to disable chasing LDAP referrals

[James Sewell]

Heya,

I see what you are saying, the problem as I see it is that the action we
are taking here is disable chasing ldap referrals. If the name is
ldapreferrals and we use a boolean then setting it to 1  reads in a counter
intuitive manner:

  set ldapreferals=true to disable chasing LDAP referrals.

Perhaps you are fine with this though if it's documented? It does work in
the inverse way to pam_ldap, where setting to true enables referral
chasing. pam_ldap works like so:

  not set  : library default
  set to 0 : disable referral chasing
  set to 1 : enable referral chasing

The other option would be to have the default value (of the parameter) be
true and set the boolean to false to disable it. I can't find any other
examples of this though - I assume having a one off like this in the code
is a bad thing also?

I'm happy to let you guys decide.

Cheers,
James

James Sewell
PostgreSQL Team Lead / Solutions Architect
_

[image:
http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]

Level 2, 50 Queen St,
Melbourne, VIC, 3000

P: 03 8370 8000   F: 03 8370 8099  W: www.lisasoft.com



On Wed, Jul 3, 2013 at 6:12 PM, Magnus Hagander mag...@hagander.net wrote:


 On Wed, Jul 3, 2013 at 3:04 AM, James Sewell james.sew...@lisasoft.comwrote:

 Hey Peter,

 You are correct, it is the same  as the referrals option in pam_ldap.
 It's also the -C (sometimes -R - it seems ldapsearch options are pretty
 non-standard) option in ldapsearch.

 As far as I'm aware you can't pass this in an LDAP URL, primarily because
 this never gets sent to the LDAP server. The server always returns an LDIF
 with inline references, this just determines if you chase them client side
 or just list them as is.

 I could be missing something here, but using:

  ldapreferrals={0|1}

 Would require a three state type, as we need a way of not interfering
 with the library defaults? To 'enable' the new behavior here using a
 boolean you would need to set ldapreferrals=false - which with the normal
 way of dealing with config booleans would alter the default behavior if the
 option was not specified.

 How do you feel about:

   ldapdisablereferrals=(0|1)


 I agree with Peter that the negative thing is bad. l don't see the
 problem, really. If you don't specify it, you rely on library defaults. If
 you do specify it, we lock it to that setting. I don't see the need to
 specifically have a setting to rely on library defaults - just remove it
 from the line and you get that.

 --
  Magnus Hagander
  Me: http://www.hagander.net/
  Work: http://www.redpill-linpro.com/


-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.
image001.png

Re: [HACKERS] [PATCH] Add an ldapoption to disable chasing LDAP referrals

[James Sewell]

Hey Peter,

You are correct, it is the same  as the referrals option in pam_ldap. It's
also the -C (sometimes -R - it seems ldapsearch options are pretty
non-standard) option in ldapsearch.

As far as I'm aware you can't pass this in an LDAP URL, primarily because
this never gets sent to the LDAP server. The server always returns an LDIF
with inline references, this just determines if you chase them client side
or just list them as is.

I could be missing something here, but using:

 ldapreferrals={0|1}

Would require a three state type, as we need a way of not interfering with
the library defaults? To 'enable' the new behavior here using a boolean you
would need to set ldapreferrals=false - which with the normal way of
dealing with config booleans would alter the default behavior if the option
was not specified.

How do you feel about:

  ldapdisablereferrals=(0|1)

Cheers,
James Sewell


James Sewell
PostgreSQL Team Lead / Solutions Architect
_

[image:
http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]

Level 2, 50 Queen St,
Melbourne, VIC, 3000

P: 03 8370 8000   F: 03 8370 8099  W: www.lisasoft.com



On Tue, Jul 2, 2013 at 10:46 PM, Peter Eisentraut pete...@gmx.net wrote:

 On 7/2/13 12:20 AM, James Sewell wrote:
  Hey All,
 
  This patch request grew from this post (of mine) to pgsql-general:
 
 
 http://www.postgresql.org/message-id/cabuevezouae-g1_oejagujjmem675dnystwybp4d_wz6om+...@mail.gmail.com
 
  The patch adds another available LDAP option (ldapnochaseref) for
  search+bind mode in the pg_hba.conf fil. If set to 1 (0 is default) then
  it performs a ldap_set_option which disables chasing of any LDAP
  references which are returned as part of the search LDIF.

 This appears to be the same as the referrals option in pam_ldap
 (http://linux.die.net/man/5/pam_ldap).  So it seems legitimate.

 For consistency, I would name the option ldapreferrals={0|1}.  I prefer
 avoiding double negatives.

 Do you know of a standard way to represent this option in an LDAP URL,
 perhaps as an extension?



-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.
image001.png

[HACKERS] [PATCH] Add an ldapoption to disable chasing LDAP referrals

[James Sewell]

Hey All,

This patch request grew from this post (of mine) to pgsql-general:

http://www.postgresql.org/message-id/cabuevezouae-g1_oejagujjmem675dnystwybp4d_wz6om+...@mail.gmail.com

The patch adds another available LDAP option (ldapnochaseref) for
search+bind mode in the pg_hba.conf fil. If set to 1 (0 is default) then it
performs a ldap_set_option which disables chasing of any LDAP references
which are returned as part of the search LDIF.

I can think of two use cases for this:

   1. (the case which spawned my email) A valid search is performed, but
   for some reason a ref: with a non responsive LDAP server is returned as
   well, which causes the authentication to time out (could be intermittent if
   DNS round robin or similar is used and some of the LDAP servers are not
   functioning / a packet dropping firewall is in the way).
   2. (a case I found when testing with AD) A valid search is performed and
   6 ref: entries are returned, which all must be chased before
   authentication can succeed. Setting ldapnochaseref speeds up authentication
   with no negative cost (assuming you understand your LDAP schema).

I think it's work noting that this setting seems to be the default for
ldapsearch on Linux these days.

Hopefully I found all the documentation that I was meant to update, let me
know if not though.

Cheers,

James Sewell
PostgreSQL Team Lead / Solutions Architect
_

[image:
http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]

Level 2, 50 Queen St,
Melbourne, VIC, 3000

P: 03 8370 8000   F: 03 8370 8099  W: www.lisasoft.com

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.
image001.png

pgsql_ldapnochaseref_v1.diff
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers