date:20150121

Hi Pierre,

Looking at your Excel document I think I misinterpreted, and you are
trying to take the stddev of each column separately (which makes a lot
more sense!). In the case you can say this:

select id, stddev(a), stddev(b), stddev(c) from foo group by id;

Paul


On Wed, Jan 21, 2015 at 10:15 AM, Paul Jungwirth
p...@illuminatedcomputing.com wrote:
 Hi Pierre,

 It looks like you're saying that each row has an id plus three numeric
 columns, and you want the stddev calculated from the three numeric
 columns? In that case you could do this:

 create table foo (id integer, a float, b float, c float);
 insert into foo values (1, 2,3,4);
 insert into foo values (2, 2,3,4);
 select id, stddev(x) from (select id, unnest(array[a,b,c]) x from foo)
 bar group by id;
  id | stddev
 +
   1 |  1
   2 |  1
 (2 rows)

 But if that's correct, then I think your table is badly structured for
 a relational database. It might be better to have just two columns: an
 id and *one* numeric value. Or perhaps an id and an array of numeric
 values if you really want all values in one row.

 At a higher level, if you are really taking the stddev of a sample of
 size 3, you should reconsider applying statistical analysis to your
 problem at all.

 I hope this helps!

 Paul








 On Wed, Jan 21, 2015 at 10:09 AM, Raymond O'Donnell r...@iol.ie wrote:
 On 21/01/2015 18:02, Pierre Hsieh wrote:
 Hi Raymond,

 Thanks for your reply. Please see detail as following. Thanks again.

 Can you describe *in words* what sort of calculation you want to do?

 Ray.


 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie


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



 --
 _
 Pulchritudo splendor veritatis.



-- 
_
Pulchritudo splendor veritatis.


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

Re: [GENERAL] Fwd: Ask for a question

Oh sorry, you should leave off the grouping:

select stddev(a), stddev(b), stddev(c) from foo;

Paul

On Wed, Jan 21, 2015 at 10:24 AM, Paul Jungwirth
p...@illuminatedcomputing.com wrote:
 Hi Pierre,

 Looking at your Excel document I think I misinterpreted, and you are
 trying to take the stddev of each column separately (which makes a lot
 more sense!). In the case you can say this:

 select id, stddev(a), stddev(b), stddev(c) from foo group by id;

 Paul


 On Wed, Jan 21, 2015 at 10:15 AM, Paul Jungwirth
 p...@illuminatedcomputing.com wrote:
 Hi Pierre,

 It looks like you're saying that each row has an id plus three numeric
 columns, and you want the stddev calculated from the three numeric
 columns? In that case you could do this:

 create table foo (id integer, a float, b float, c float);
 insert into foo values (1, 2,3,4);
 insert into foo values (2, 2,3,4);
 select id, stddev(x) from (select id, unnest(array[a,b,c]) x from foo)
 bar group by id;
  id | stddev
 +
   1 |  1
   2 |  1
 (2 rows)

 But if that's correct, then I think your table is badly structured for
 a relational database. It might be better to have just two columns: an
 id and *one* numeric value. Or perhaps an id and an array of numeric
 values if you really want all values in one row.

 At a higher level, if you are really taking the stddev of a sample of
 size 3, you should reconsider applying statistical analysis to your
 problem at all.

 I hope this helps!

 Paul








 On Wed, Jan 21, 2015 at 10:09 AM, Raymond O'Donnell r...@iol.ie wrote:
 On 21/01/2015 18:02, Pierre Hsieh wrote:
 Hi Raymond,

 Thanks for your reply. Please see detail as following. Thanks again.

 Can you describe *in words* what sort of calculation you want to do?

 Ray.


 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie


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



 --
 _
 Pulchritudo splendor veritatis.



 --
 _
 Pulchritudo splendor veritatis.



-- 
_
Pulchritudo splendor veritatis.


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

Re: [GENERAL] Fwd: Ask for a question

2015-01-21 Thread Rob Sargent


On 01/21/2015 11:02 AM, Pierre Hsieh wrote:

Hi Raymond,

Thanks for your reply. Please see detail as following. Thanks again.

Pierre


Inline image 1

On Thu, Jan 22, 2015 at 1:48 AM, Raymond O'Donnell r...@iol.ie 
mailto:r...@iol.ie wrote:


On 21/01/2015 17:32, Pierre Hsieh wrote:
 Hi guys,

 Thanks for your replies.

  I certainly can use VBA and ADODB object in Excel to do it. Due to
 performance, I wanna try to do it by SQL command in PG. However,
I am
 not expert in PG, so I need few help from your guys. Let me to
describe
 my question clearly as following.

 The final results which I wanna get
 are STDDEV(a1,a2,a3,...), STDDEV(b1,b2,b3,...), ..etc. I
definitely know
 how to use standard deviation function in PG, but the critical
problem
 for me is how to calculate a1, a2, a3, b1, b2, b3...etc. The rule to
 calculate them is a little complicated because it needs not only
moving
 window function but also few logical rules. Please see details as
 following or attachment. I put some colors in rules. Hopefully, it's
 easier for you guys to read them. Thanks

Sorry, either I'm being stupid or your description isn't clear.
Can you
show your table structure, ideally with some sample data, and what you
hope to get from the query?

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie mailto:r...@iol.ie




If you have a four column table as defined in your spreadsheet, with 
column names A,B,C,D

select rowsum = B - (C*A) +D;
might get you the sums you need but I don't understand the grouping of 
these for the STDDEV function

Re: [GENERAL] Fwd: Ask for a question

2015-01-21 Thread Rob Sargent


On 01/21/2015 11:31 AM, Pierre Hsieh wrote:

updated rule

Inline image 1

On Thu, Jan 22, 2015 at 2:28 AM, Pierre Hsieh pierre.hs...@gmail.com 
mailto:pierre.hs...@gmail.com wrote:


Thanks for your reply.

Let me to describe the purpose for this calculation roughly.

Column B is for the price of stock.
Column C  D are the slope and interception of linear regression
from Column B.
The final result which I need is the standard deviation on the
difference between stock price and the implied price from linear
regression by each 250 historical data(moving window)

Hopefully, it's clear for you to understand this calculation. Thanks

Inline image 1

On Thu, Jan 22, 2015 at 2:15 AM, Paul Jungwirth
p...@illuminatedcomputing.com mailto:p...@illuminatedcomputing.com
wrote:

Hi Pierre,

It looks like you're saying that each row has an id plus three
numeric
columns, and you want the stddev calculated from the three numeric
columns? In that case you could do this:

create table foo (id integer, a float, b float, c float);
insert into foo values (1, 2,3,4);
insert into foo values (2, 2,3,4);
select id, stddev(x) from (select id, unnest(array[a,b,c]) x
from foo)
bar group by id;
 id | stddev
+
  1 |  1
  2 |  1
(2 rows)

But if that's correct, then I think your table is badly
structured for
a relational database. It might be better to have just two
columns: an
id and *one* numeric value. Or perhaps an id and an array of
numeric
values if you really want all values in one row.

At a higher level, if you are really taking the stddev of a
sample of
size 3, you should reconsider applying statistical analysis to
your
problem at all.

I hope this helps!

Paul








On Wed, Jan 21, 2015 at 10:09 AM, Raymond O'Donnell
r...@iol.ie mailto:r...@iol.ie wrote:
 On 21/01/2015 18:02, Pierre Hsieh wrote:
 Hi Raymond,

 Thanks for your reply. Please see detail as following.
Thanks again.

 Can you describe *in words* what sort of calculation you
want to do?

 Ray.


 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie mailto:r...@iol.ie


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



--
_
Pulchritudo splendor veritatis.





You need to define a window function which captures 250 rows, 
seqentially then apply the arithmetic stddev(b - (d * 
(current-window-position % 250) + c)

Re: [GENERAL] Fwd: Ask for a question

Thanks for your reply.

Let me to describe the purpose for this calculation roughly.

Column B is for the price of stock.
Column C  D are the slope and interception of linear regression
from Column B.
The final result which I need is the standard deviation on the difference
between stock price and the implied price from linear regression by each
250 historical data(moving window)

Hopefully, it's clear for you to understand this calculation. Thanks

[image: Inline image 1]

On Thu, Jan 22, 2015 at 2:15 AM, Paul Jungwirth p...@illuminatedcomputing.com
 wrote:

 Hi Pierre,

 It looks like you're saying that each row has an id plus three numeric
 columns, and you want the stddev calculated from the three numeric
 columns? In that case you could do this:

 create table foo (id integer, a float, b float, c float);
 insert into foo values (1, 2,3,4);
 insert into foo values (2, 2,3,4);
 select id, stddev(x) from (select id, unnest(array[a,b,c]) x from foo)
 bar group by id;
  id | stddev
 +
   1 |  1
   2 |  1
 (2 rows)

 But if that's correct, then I think your table is badly structured for
 a relational database. It might be better to have just two columns: an
 id and *one* numeric value. Or perhaps an id and an array of numeric
 values if you really want all values in one row.

 At a higher level, if you are really taking the stddev of a sample of
 size 3, you should reconsider applying statistical analysis to your
 problem at all.

 I hope this helps!

 Paul








 On Wed, Jan 21, 2015 at 10:09 AM, Raymond O'Donnell r...@iol.ie wrote:
  On 21/01/2015 18:02, Pierre Hsieh wrote:
  Hi Raymond,
 
  Thanks for your reply. Please see detail as following. Thanks again.
 
  Can you describe *in words* what sort of calculation you want to do?
 
  Ray.
 
 
  --
  Raymond O'Donnell :: Galway :: Ireland
  r...@iol.ie
 
 
  --
  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-general



 --
 _
 Pulchritudo splendor veritatis.



SQL.xls
Description: MS-Excel spreadsheet

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

Re: [GENERAL] Fwd: Ask for a question

Hi Pierre,

It looks like you're saying that each row has an id plus three numeric
columns, and you want the stddev calculated from the three numeric
columns? In that case you could do this:

create table foo (id integer, a float, b float, c float);
insert into foo values (1, 2,3,4);
insert into foo values (2, 2,3,4);
select id, stddev(x) from (select id, unnest(array[a,b,c]) x from foo)
bar group by id;
 id | stddev
+
  1 |  1
  2 |  1
(2 rows)

But if that's correct, then I think your table is badly structured for
a relational database. It might be better to have just two columns: an
id and *one* numeric value. Or perhaps an id and an array of numeric
values if you really want all values in one row.

At a higher level, if you are really taking the stddev of a sample of
size 3, you should reconsider applying statistical analysis to your
problem at all.

I hope this helps!

Paul








On Wed, Jan 21, 2015 at 10:09 AM, Raymond O'Donnell r...@iol.ie wrote:
 On 21/01/2015 18:02, Pierre Hsieh wrote:
 Hi Raymond,

 Thanks for your reply. Please see detail as following. Thanks again.

 Can you describe *in words* what sort of calculation you want to do?

 Ray.


 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie


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



-- 
_
Pulchritudo splendor veritatis.


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

Re: [GENERAL] Fwd: Ask for a question

On 21/01/2015 17:32, Pierre Hsieh wrote:
 Hi guys,
 
 Thanks for your replies.
 
  I certainly can use VBA and ADODB object in Excel to do it. Due to
 performance, I wanna try to do it by SQL command in PG. However, I am
 not expert in PG, so I need few help from your guys. Let me to describe
 my question clearly as following.
 
 The final results which I wanna get
 are STDDEV(a1,a2,a3,...), STDDEV(b1,b2,b3,...), ..etc. I definitely know
 how to use standard deviation function in PG, but the critical problem
 for me is how to calculate a1, a2, a3, b1, b2, b3...etc. The rule to
 calculate them is a little complicated because it needs not only moving
 window function but also few logical rules. Please see details as
 following or attachment. I put some colors in rules. Hopefully, it's
 easier for you guys to read them. Thanks

Sorry, either I'm being stupid or your description isn't clear. Can you
show your table structure, ideally with some sample data, and what you
hope to get from the query?

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

Re: [GENERAL] Fwd: Ask for a question

Hi Raymond,

Thanks for your reply. Please see detail as following. Thanks again.

Pierre


[image: Inline image 1]

On Thu, Jan 22, 2015 at 1:48 AM, Raymond O'Donnell r...@iol.ie wrote:

 On 21/01/2015 17:32, Pierre Hsieh wrote:
  Hi guys,
 
  Thanks for your replies.
 
   I certainly can use VBA and ADODB object in Excel to do it. Due to
  performance, I wanna try to do it by SQL command in PG. However, I am
  not expert in PG, so I need few help from your guys. Let me to describe
  my question clearly as following.
 
  The final results which I wanna get
  are STDDEV(a1,a2,a3,...), STDDEV(b1,b2,b3,...), ..etc. I definitely know
  how to use standard deviation function in PG, but the critical problem
  for me is how to calculate a1, a2, a3, b1, b2, b3...etc. The rule to
  calculate them is a little complicated because it needs not only moving
  window function but also few logical rules. Please see details as
  following or attachment. I put some colors in rules. Hopefully, it's
  easier for you guys to read them. Thanks

 Sorry, either I'm being stupid or your description isn't clear. Can you
 show your table structure, ideally with some sample data, and what you
 hope to get from the query?

 Ray.


 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie



SQL.xls
Description: MS-Excel spreadsheet

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

Re: [GENERAL] Fwd: Ask for a question

On 21/01/2015 18:02, Pierre Hsieh wrote:
 Hi Raymond,
 
 Thanks for your reply. Please see detail as following. Thanks again.

Can you describe *in words* what sort of calculation you want to do?

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

Re: [GENERAL] splitting up tables based on read/write frequency of columns

2015-01-21 Thread Jonathan Vanasco


On Jan 19, 2015, at 5:07 PM, Stefan Keller wrote:

 Hi
 
 I'm pretty sure PostgreSQL can handle this.
 But since you asked with a theoretic background,
 it's probably worthwhile to look at column stores (like [1]).


Wow. I didn't know there was a column store extension for PG -- this would come 
in handy for some analytic stuff we run!

I know that PG can handle my current system at scale.  I'm really just 
wondering what the possible slowdowns/improvements will be.  

Doing a rewrite of the entire row + updating the various indexes seems to be a 
lot of unnecessary IO.  At some point it will make sense to minimize that and 
isolate the heavy-write columns from impacting the rest of the table's 
performance.

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

Re: [GENERAL] Fwd: Ask for a question

On 21/01/2015 14:38, Pierre Hsieh wrote:
 
 
 Hi,
 
  
 
 Would you please tell me whether PostgreSQL can execute the following
 tasks? If not, please also tell me which one can help me for that. Thanks


Not clear what you're asking, but if you just want to find the standard
deviation of a sample then that's no problem:

http://www.postgresql.org/docs/9.4/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE

Hope this helps,

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

Re: [GENERAL] Fwd: Ask for a question

2015-01-21 Thread Rémi Cura

More bluntly maybe :

if you can do it in Excel,
you can do it in Postgres.

Cheers,
Rémi-C

2015-01-21 16:37 GMT+01:00 Raymond O'Donnell r...@iol.ie:

 On 21/01/2015 14:38, Pierre Hsieh wrote:
 
 
  Hi,
 
 
 
  Would you please tell me whether PostgreSQL can execute the following
  tasks? If not, please also tell me which one can help me for that. Thanks


 Not clear what you're asking, but if you just want to find the standard
 deviation of a sample then that's no problem:


 http://www.postgresql.org/docs/9.4/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE

 Hope this helps,

 Ray.


 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie


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

Re: [GENERAL] Fwd: Ask for a question

2015-01-21 Thread Brian Dunavant

This is not quite true.  I don't believe there are any flight
simulator easter-eggs hidden inside the Postgres code.  :)

On Wed, Jan 21, 2015 at 10:59 AM, Rémi Cura remi.c...@gmail.com wrote:
 More bluntly maybe :

 if you can do it in Excel,
 you can do it in Postgres.

 Cheers,
 Rémi-C

 2015-01-21 16:37 GMT+01:00 Raymond O'Donnell r...@iol.ie:

 On 21/01/2015 14:38, Pierre Hsieh wrote:
 
 
  Hi,
 
 
 
  Would you please tell me whether PostgreSQL can execute the following
  tasks? If not, please also tell me which one can help me for that.
  Thanks


 Not clear what you're asking, but if you just want to find the standard
 deviation of a sample then that's no problem:


 http://www.postgresql.org/docs/9.4/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE

 Hope this helps,

 Ray.


 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie


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




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

Re: [GENERAL] Fwd: Ask for a question

On 21/01/2015 16:06, Brian Dunavant wrote:
 This is not quite true.  I don't believe there are any flight
 simulator easter-eggs hidden inside the Postgres code.  :)

No? Awww. :-)

Ray.



-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

Re: [GENERAL] splitting up tables based on read/write frequency of columns

2015-01-21 Thread David G Johnston

Jonathan Vanasco-7 wrote
This is really a theoretical/anecdotal question, as I'm not at a scale yet
where this would measurable. I want to investigate while this is fresh in
my mind...

I recall reading that unless a row has columns that are TOASTed, an
`UPDATE` is essentially an `INSERT + DELETE`, with the previous row marked
for vacuuming.

A few of my tables have the following characteristics:
- The Primary Key has many other tables/columns that FKEY onto it.
- Many columns (30+) of small data size
- Most columns (90%) are 1 WRITE(UPDATE) for 1000 READS
- Some columns (10%) do a bit of internal bookkeeping and are 1
WRITE(UPDATE) for 50 READS

Has anyone done testing/benchmarking on potential efficiency/savings by
consolidating the frequent UPDATE columns into their own table?

Consider another in-database attempt to mitigate the need to do this
manually:

HOT (heap-only-tuple)

http://pgsql.tapoueh.org/site/html/misc/hot.html

I haven't done any bench-marking but I do currently use this idea to
segregate read-only fields from read-write fields. Likely there is also a
model reason why these fields have different update frequencies and so can
both logically and physically be partitioned out.

The only harm I see is that it can make using the schema more difficult -
though that can be somewhat mitigated by using (updateable) views in front
of the partitioned tables.

If you can logically partition them I would go for it; if it is a purely
physical concern then I'd probably ponder it for another couple of days and
then go for it anyway. The main additional thing to ponder is the cost of
additional parsing and the additional join. Neither is super expensive but
if only doing this for physical reasons you need to evaluate your planned
usage patterns. With a logical split you are more likely to find situations
where you do not even care about one table or the other and so can avoid the
join entirely.

David J.

--
View this message in context:
http://postgresql.nabble.com/splitting-up-tables-based-on-read-write-frequency-of-columns-tp5834646p5834911.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Fwd: Ask for a question

Hi guys,

Thanks for your replies.

 I certainly can use VBA and ADODB object in Excel to do it. Due to
performance, I wanna try to do it by SQL command in PG. However, I am not
expert in PG, so I need few help from your guys. Let me to describe my
question clearly as following.

The final results which I wanna get
are STDDEV(a1,a2,a3,...), STDDEV(b1,b2,b3,...), ..etc. I definitely know
how to use standard deviation function in PG, but the critical problem for
me is how to calculate a1, a2, a3, b1, b2, b3...etc. The rule to calculate
them is a little complicated because it needs not only moving window
function but also few logical rules. Please see details as following or
attachment. I put some colors in rules. Hopefully, it's easier for you guys
to read them. Thanks

I really need the helps from your guys. Please give me some suggestions.
Thanks.

Pierre

[image: Inline image 1]

On Thu, Jan 22, 2015 at 12:42 AM, Raymond O'Donnell r...@iol.ie wrote:

 On 21/01/2015 16:06, Brian Dunavant wrote:
  This is not quite true.  I don't believe there are any flight
  simulator easter-eggs hidden inside the Postgres code.  :)

 No? Awww. :-)

 Ray.



 --
 Raymond O'Donnell :: Galway :: Ireland
 r...@iol.ie



SQL.xls
Description: MS-Excel spreadsheet

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

[GENERAL] ORDER BY in prepared statements

2015-01-21 Thread Bryn Jeffries

In a number of places on the web I've seen it claimed that ordering can be set 
via prepared statements. Indeed, the expected syntax is accepted on my 9.3 
server without errors:

sandbox=# CREATE TABLE test (
id serial PRIMARY KEY,
gender char
);

sandbox=# INSERT INTO test(gender)  VALUES('m') VALUES('f') VALUES('m') 
VALUES('f') VALUES('m');

sandbox=# PREPARE testplan(text) AS
SELECT * FROM test ORDER BY $1;

But the output is not what one would expect:

sandbox=# EXECUTE testplan('gender');
id | gender
+
  1 | m
  2 | f
  3 | m
  4 | f
  5 | m
  6 | f
(6 rows)

As opposed to:
sandbox=# SELECT * FROM test ORDER BY gender;
 id | gender
+
  2 | f
  4 | f
  6 | f
  1 | m
  3 | m
  5 | m
(6 rows)

It would seem that the ORDER BY clause is simply ignored in the prepared 
statement. Is this deliberate behaviour? I can well understand that supporting 
this kind of query would be tricky, but it would be very handy.

Many thanks,

Bryn

Re: [GENERAL] ORDER BY in prepared statements

 In a number of places on the web I've seen it claimed that ordering can be
 set via prepared statements.
 ...
 sandbox=# PREPARE testplan(text) AS
 SELECT * FROM test ORDER BY $1;

 But the output is not what one would expect:

 sandbox=# EXECUTE testplan('gender');
 ...
 As opposed to:
 sandbox=# SELECT * FROM test ORDER BY gender;

Your prepared statement version is actually comparable to this SQL:

SELECT * FROM test ORDER BY 'gender'

which is effectually ordering by random.

I'm not sure how to make a prepared statement that lets you name a
column when you execute it. Maybe someone else can chime in if that's
possible.

Paul


-- 
_
Pulchritudo splendor veritatis.


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

Re: [GENERAL] ORDER BY in prepared statements

2015-01-21 Thread David G Johnston

Paul Jungwirth wrote
 In a number of places on the web I've seen it claimed that ordering can
 be
 set via prepared statements.
 ...
 sandbox=# PREPARE testplan(text) AS
 SELECT * FROM test ORDER BY $1;

 But the output is not what one would expect:

 sandbox=# EXECUTE testplan('gender');
 ...
 As opposed to:
 sandbox=# SELECT * FROM test ORDER BY gender;
 
 Your prepared statement version is actually comparable to this SQL:
 
 SELECT * FROM test ORDER BY 'gender'
 
 which is effectually ordering by random.
 
 I'm not sure how to make a prepared statement that lets you name a
 column when you execute it. Maybe someone else can chime in if that's
 possible.
 
 Paul

You cannot.  By definition parameters, in this context, are values - not
identifiers.  Queries with variable identifiers are called dynamic SQL and
can only be realized via the EXECUTE statement in pl/pgsql.  Yes, same name
different behavior because it is a different language.

http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

It too has a prepare capability (USING) that is also limited to data
values and not identifiers.  Basically what this gives you is an
easy-to-access language and structure (i.e., function) to execute dynamic
SQL.  You can accomplish the same thing in whatever language and client
library you are using by creating a dynamic SQL statement to pass to SQL
PREPARE.

In both situations there is no way for the planner to plan and cache a
single query whose order by column varies.  No matter what you do at best
you can have a single plan for each explicit order by column that you wish
to specify.

David J.







--
View this message in context: 
http://postgresql.nabble.com/ORDER-BY-in-prepared-statements-tp5834944p5834948.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] ORDER BY in prepared statements

2015-01-21 Thread Adrian Klaver


On 01/21/2015 12:51 PM, Bryn Jeffries wrote:

In a number of places on the web I've seen it claimed that ordering can
be set via prepared statements.


Can you give a link to one of those examples?



Many thanks,

Bryn



--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] ORDER BY in prepared statements

2015-01-21 Thread Bryn Jeffries

Sorry, I can't find any now. It's cropped up in a few forums, in the context of 
executing queries from web services. Clearly not significantly enough to show 
up in Google...
- Reply message -
From: Adrian Klaver adrian.kla...@aklaver.com
To: Bryn Jeffries bryn.jeffr...@sydney.edu.au, 
pgsql-general@postgresql.org pgsql-general@postgresql.org
Subject: [GENERAL] ORDER BY in prepared statements
Date: Thu, Jan 22, 2015 08:18

On 01/21/2015 12:51 PM, Bryn Jeffries wrote:
 In a number of places on the web I've seen it claimed that ordering can
 be set via prepared statements.

Can you give a link to one of those examples?


 Many thanks,

 Bryn


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: [GENERAL] ORDER BY in prepared statements

2015-01-21 Thread Bryn Jeffries

Paul Jungwirth wrote
 I'm not sure how to make a prepared statement that lets you name a
 column when you execute it. Maybe someone else can chime in if that's
 possible.

David J. responded
 You cannot.  By definition parameters, in this context, are values - not
 identifiers.  
 [...]
 In both situations there is no way for the planner to plan and cache a
 single query whose order by column varies.  No matter what you do at best
 you can have a single plan for each explicit order by column that you wish
 to specify.

That's what I'd figured. The motivation to use prepared statements in 
application layers is not so much having a single plan but more the 
insulation from SQL injection. The intent of the given ORDER BY example was 
to restricts inputs to valid identifiers rather than part of the query 
expression. 

Maybe what we need in ODBC libs and the like is a protected 
statement that follows the same construction as a prepared statement but 
additionally checks catalogs to validate identifiers.

Bryn

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

Re: [GENERAL] ORDER BY in prepared statements

2015-01-21 Thread David Johnston



 On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries 
 bryn.jeffr...@sydney.edu.au wrote:


 Maybe what we need in ODBC libs and the like is a protected
 statement that follows the same construction as a prepared statement but
 additionally checks catalogs to validate identifiers.


I'm not sure whether this would actually be a feasible solution to the
problem.  Note that most frameworks (well, the format solution I outlined
at least) for doing identifier replacement safely require that you actually
tell the system what is expected to be an identifier and what is expected
to be a data value.  The general implementation is that, in the case of
PostgreSQL, double-quotes will be added to the identifier value if required
to make it a valid identifier. Since any injection would rely on supply
mandatory quote identifiers this solves the problem quite neatly.

The one part I am not positive on is dealing with case-folding when using
format's %I placeholder; this seems to be a documentation deficiency though
I may just not have found it yet...or reasoned out the logical outcome
(which I shouldn't need to do)...

Catalog lookups would be expensive to do pro-actively.  The goal is to form
a safe query for the parser and let the planner deal with any identifiers
that end up being invalid either through attempted injection or simply
usage errors.

David J.

Re: [GENERAL] ORDER BY in prepared statements

2015-01-21 Thread David Johnston

On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries bryn.jeffr...@sydney.edu.au
wrote:

 Paul Jungwirth wrote
  I'm not sure how to make a prepared statement that lets you name a
  column when you execute it. Maybe someone else can chime in if that's
  possible.

 David J. responded
  You cannot.  By definition parameters, in this context, are values - not
  identifiers.
  [...]
  In both situations there is no way for the planner to plan and cache a
  single query whose order by column varies.  No matter what you do at best
  you can have a single plan for each explicit order by column that you
 wish
  to specify.

 That's what I'd figured. The motivation to use prepared statements in
 application layers is not so much having a single plan but more the
 insulation from SQL injection. The intent of the given ORDER BY example was
 to restricts inputs to valid identifiers rather than part of the query
 expression.

 Maybe what we need in ODBC libs and the like is a protected
 statement that follows the same construction as a prepared statement but
 additionally checks catalogs to validate identifiers.

 Bryn


The canonical way to do this, in reasonably recent PostgreSQL versions, is
to wrap your desired dynamic SQL statement in a function.  Within that
function construct the SQL string with the assistance of the format(...)
function.  That function has specific placeholders for literals and
identifiers that will ensure that the constructed SQL string is built in a
safe manner.


http://www.postgresql.org/docs/9.4/interactive/functions-string.html#FUNCTIONS-STRING-FORMAT

Then you call the function and pass in the arguments are value parameters;
which the function then converts into either literal or identifiers as
instructed to by the format expression.

David J.

Re: [GENERAL] ORDER BY in prepared statements

2015-01-21 Thread Adrian Klaver


On 01/21/2015 03:09 PM, Bryn Jeffries wrote:

Paul Jungwirth wrote

I'm not sure how to make a prepared statement that lets you name a
column when you execute it. Maybe someone else can chime in if that's
possible.


David J. responded

You cannot.  By definition parameters, in this context, are values - not
identifiers.
[...]
In both situations there is no way for the planner to plan and cache a
single query whose order by column varies.  No matter what you do at best
you can have a single plan for each explicit order by column that you wish
to specify.


That's what I'd figured. The motivation to use prepared statements in
application layers is not so much having a single plan but more the
insulation from SQL injection. The intent of the given ORDER BY example was
to restricts inputs to valid identifiers rather than part of the query
expression.



In addition to what David said, applications/frameworks may provide that 
functionality. For example in Django:


https://docs.djangoproject.com/en/1.7/ref/models/querysets/#order-by




Maybe what we need in ODBC libs and the like is a protected
statement that follows the same construction as a prepared statement but
additionally checks catalogs to validate identifiers.

Bryn




--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] BDR Error restarted

2015-01-21 Thread Craig Ringer

That's a lot of databases and explains why the logs were so busy.

For the purpose of testing it would be interesting to simplify this case
down, if you can, to the minimum required to reproduce the issue. It's
awfully hard to keep track of what's going on with this many concurrent
operations - especially since at the moment background workers don't
respect the %d format parameter in log_line_prefix .

The logs of the same time on the node you didn't restart would be useful
too, i.e. a matching pair.

The ideal is to determine what sequence of actions creates this situation
so we can reproduce what you're seeing.


On 21 January 2015 at 19:33, deans dean.song...@gmail.com wrote:

 Hi Andres,

 Any other conf should I list in addition to the bdr settings below?

 BDR Settings(replaced the real db name here):

 1. on node 01, the replication src one:
 # Generic settings required for BDR
 max_replication_slots = 60
 max_wal_senders = 60
 wal_level = 'logical'
 track_commit_timestamp = on
 shared_preload_libraries = 'bdr'
 max_worker_processes = 100

 # These aren't required, but are useful for diagnosing problems
 #log_error_verbosity = verbose
 #log_min_messages = debug1
 #log_line_prefix = 'd=%d p=%p a=%a%q '

 # Useful options for playing with conflicts
 #bdr.default_apply_delay=2000   # milliseconds
 #bdr.log_conflicts_to_table=on

 bdr.connections =

 'bdr02db1,bdr02db2,bdr02db3,bdr02db4,bdr02db5,bdr02db6,bdr02db7,bdr02db8,bdr02db9,bdr02db10,bdr02db11,bdr02db12,bdr02db13,bdr03db1,bdr03db2,bdr03db3,bdr03db4,bdr03db5,bdr03db6,bdr03db7,bdr03db8,bdr03db9,bdr03db10,bdr03db11,bdr03db12,bdr03db13'

 bdr.bdr02db1_dsn = 'dbname=db1 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db2_dsn = 'dbname=db2 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db3_dsn = 'dbname=db3 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db4_dsn = 'dbname=db4 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db5_dsn = 'dbname=db5 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db6_dsn = 'dbname=db6 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db7_dsn = 'dbname=db7 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db8_dsn = 'dbname=db8 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db8_dsn = 'dbname=db9 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db10_dsn = 'dbname=db10 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db11_dsn = 'dbname=db11 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db12_dsn = 'dbname=db12 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr02db13_dsn = 'dbname=db13 host=pgsql-bdr-cluster-02.ol.xxx.net
 user=postgres'
 bdr.bdr03db1_dsn = 'dbname=db1 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db2_dsn = 'dbname=db2 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db3_dsn = 'dbname=db3 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db4_dsn = 'dbname=db4 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db5_dsn = 'dbname=db5 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db6_dsn = 'dbname=db6 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db7_dsn = 'dbname=db7 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db8_dsn = 'dbname=db8 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db9_dsn = 'dbname=db9 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db10_dsn = 'dbname=db10 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db11_dsn = 'dbname=db11 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db12_dsn = 'dbname=db12 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'
 bdr.bdr03db13_dsn = 'dbname=db13 host=pgsql-bdr-cluster-03.ol.xxx.net
 user=postgres'

 2. on node 02
 # Generic settings required for BDR
 max_replication_slots = 60
 max_wal_senders = 60
 wal_level = 'logical'
 track_commit_timestamp = on
 shared_preload_libraries = 'bdr'
 max_worker_processes = 100

 # These aren't required, but are useful for diagnosing problems
 #log_error_verbosity = verbose
 #log_min_messages = debug1
 #log_line_prefix = 'd=%d p=%p a=%a%q '

 # Useful options for playing with conflicts
 #bdr.default_apply_delay=2000   # milliseconds
 #bdr.log_conflicts_to_table=on

 bdr.connections =

 'bdr01db1,bdr01db2,bdr01db3,bdr01db4,bdr01db5,bdr01db6,bdr01db7,bdr01db8,bdr01db9,bdr01db10,bdr01db11,bdr01db12,bdr01db13,bdr03db1,bdr03db2,bdr03db3,bdr03db4,bdr03db5,bdr03db6,bdr03db7,bdr03db8,bdr03db9,bdr03db10,bdr03db11,bdr03db12,bdr03db13'

 bdr.bdr01db1_dsn = 'dbname=db1 host=pgsql-bdr-cluster-01.ol.xxx.net
 user=postgres'
 bdr.bdr01db2_dsn = 'dbname=db2 host=pgsql-bdr-cluster-01.ol.xxx.net
 user=postgres'
 bdr.bdr01db3_dsn = 'dbname=db3 host=pgsql-bdr-cluster-01.ol.xxx.net
 user=postgres'
 bdr.bdr01db4_dsn = 'dbname=db4 host=pgsql-bdr-cluster-01.ol.xxx.net
 user=postgres'
 bdr.bdr01db5_dsn = 'dbname=db5

Re: [GENERAL] Concurrent Inserts

2015-01-21 Thread Albe Laurenz

sri harsha wrote:
Is there any way to stop concurrent inserts to happen on a single table 
 ??

 If you really want that, it is easy with table locks.

 Actually i am using a FDW , in which the data is written into a single 
 file. So when i do
 concurrent inserts , the data is written into the file simultaneously and 
 this is causing a data
 corruption . Is TABLE LOCK the only option available ??

In that case I would say that the FDW is broken.

If concurrent modifications corrupt the data, the FDW should make sure that not 
more
than one modification to the same data can happen at the same time, perhaps by 
using
PostgreSQL advisory locks or IPC mechanisms.

Yours,
Laurenz Albe

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

Re: [GENERAL] Concurrent Inserts

2015-01-21 Thread sri harsha

Hi,

Actually i am using a FDW , in which the data is written into a single
file. So when i do concurrent inserts , the data is written into the file
simultaneously and this is causing a data corruption . Is TABLE LOCK the
only option available ??

--Harsha



On Wed, Jan 21, 2015 at 3:22 PM, Albe Laurenz laurenz.a...@wien.gv.at
wrote:

 sri harsha wrote:
 Is there any way to stop concurrent inserts to happen on a single
 table ??
 
  Query 1 : INSERT INTO TABLE_A SELECT * FROM TABLE1;
  Query 2 : INSERT INTO TABLE_A SELECT * FROM TABLE2;
  Query 3 : SELECT * FROM TABLE_A;
 
  Assume i have the above queries. Query 1 and Query 3 can occur
 concurrently . If one insert is taking
  place , the other should wait. How do i achieve this ??

 Is that a typo and you really mean query 1 an query 2 above?

 Why would you want to prevent concurrent inserts?
 Maybe you don't really need to hobble your performance like that.

 If you really want that, it is easy with table locks.
 Your INSERT could look like that:

 BEGIN;
 LOCK table_a IN EXCLUSIVE MODE;
 INSERT INTO table_a ...
 COMMIT;

 How do the concurrent inserts take place in postgres ?? Is data
 stored temporarily for both queries
  separately and then written into the table ??

 No, it is written to the table concurrently.
 A table is organized in pages of 8KB each, and there is no problem with
 writing to
 different pages concurrently.

 Yours,
 Laurenz Albe

Re: [GENERAL] BDR Error restarted

2015-01-21 Thread deans

Hi Andres,

Any other conf should I list in addition to the bdr settings below?

BDR Settings(replaced the real db name here): 

1. on node 01, the replication src one: 
# Generic settings required for BDR
max_replication_slots = 60
max_wal_senders = 60
wal_level = 'logical'
track_commit_timestamp = on
shared_preload_libraries = 'bdr'
max_worker_processes = 100

# These aren't required, but are useful for diagnosing problems
#log_error_verbosity = verbose
#log_min_messages = debug1
#log_line_prefix = 'd=%d p=%p a=%a%q '

# Useful options for playing with conflicts
#bdr.default_apply_delay=2000   # milliseconds
#bdr.log_conflicts_to_table=on

bdr.connections =
'bdr02db1,bdr02db2,bdr02db3,bdr02db4,bdr02db5,bdr02db6,bdr02db7,bdr02db8,bdr02db9,bdr02db10,bdr02db11,bdr02db12,bdr02db13,bdr03db1,bdr03db2,bdr03db3,bdr03db4,bdr03db5,bdr03db6,bdr03db7,bdr03db8,bdr03db9,bdr03db10,bdr03db11,bdr03db12,bdr03db13'

bdr.bdr02db1_dsn = 'dbname=db1 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db2_dsn = 'dbname=db2 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db3_dsn = 'dbname=db3 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db4_dsn = 'dbname=db4 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db5_dsn = 'dbname=db5 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db6_dsn = 'dbname=db6 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db7_dsn = 'dbname=db7 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db8_dsn = 'dbname=db8 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db8_dsn = 'dbname=db9 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db10_dsn = 'dbname=db10 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db11_dsn = 'dbname=db11 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db12_dsn = 'dbname=db12 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr02db13_dsn = 'dbname=db13 host=pgsql-bdr-cluster-02.ol.xxx.net
user=postgres'
bdr.bdr03db1_dsn = 'dbname=db1 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db2_dsn = 'dbname=db2 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db3_dsn = 'dbname=db3 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db4_dsn = 'dbname=db4 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db5_dsn = 'dbname=db5 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db6_dsn = 'dbname=db6 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db7_dsn = 'dbname=db7 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db8_dsn = 'dbname=db8 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db9_dsn = 'dbname=db9 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db10_dsn = 'dbname=db10 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db11_dsn = 'dbname=db11 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db12_dsn = 'dbname=db12 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'
bdr.bdr03db13_dsn = 'dbname=db13 host=pgsql-bdr-cluster-03.ol.xxx.net
user=postgres'

2. on node 02
# Generic settings required for BDR
max_replication_slots = 60
max_wal_senders = 60
wal_level = 'logical'
track_commit_timestamp = on
shared_preload_libraries = 'bdr'
max_worker_processes = 100

# These aren't required, but are useful for diagnosing problems
#log_error_verbosity = verbose
#log_min_messages = debug1
#log_line_prefix = 'd=%d p=%p a=%a%q '

# Useful options for playing with conflicts
#bdr.default_apply_delay=2000   # milliseconds
#bdr.log_conflicts_to_table=on

bdr.connections =
'bdr01db1,bdr01db2,bdr01db3,bdr01db4,bdr01db5,bdr01db6,bdr01db7,bdr01db8,bdr01db9,bdr01db10,bdr01db11,bdr01db12,bdr01db13,bdr03db1,bdr03db2,bdr03db3,bdr03db4,bdr03db5,bdr03db6,bdr03db7,bdr03db8,bdr03db9,bdr03db10,bdr03db11,bdr03db12,bdr03db13'

bdr.bdr01db1_dsn = 'dbname=db1 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db2_dsn = 'dbname=db2 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db3_dsn = 'dbname=db3 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db4_dsn = 'dbname=db4 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db5_dsn = 'dbname=db5 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db6_dsn = 'dbname=db6 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db7_dsn = 'dbname=db7 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db8_dsn = 'dbname=db8 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db8_dsn = 'dbname=db9 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db10_dsn = 'dbname=db10 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db11_dsn = 'dbname=db11 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db12_dsn = 'dbname=db12 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db13_dsn = 'dbname=db13 host=pgsql-bdr-cluster-01.ol.xxx.net
user=postgres'
bdr.bdr01db1_dsn = 'dbname=db1

[GENERAL] Concurrent Inserts

2015-01-21 Thread sri harsha

Hi,

   Is there any way to stop concurrent inserts to happen on a single table
??

Query 1 : INSERT INTO TABLE_A SELECT * FROM TABLE1;
Query 2 : INSERT INTO TABLE_A SELECT * FROM TABLE2;
Query 3 : SELECT * FROM TABLE_A;

Assume i have the above queries. Query 1 and Query 3 can occur concurrently
. If one insert is taking place , the other should wait. How do i achieve
this ??

   How do the concurrent inserts take place in postgres ?? Is data stored
temporarily for both queries separately and then written into the table ??

Thanks,
Harsha

Re: [GENERAL] Concurrent Inserts

2015-01-21 Thread Albe Laurenz

sri harsha wrote:
Is there any way to stop concurrent inserts to happen on a single table ??
 
 Query 1 : INSERT INTO TABLE_A SELECT * FROM TABLE1;
 Query 2 : INSERT INTO TABLE_A SELECT * FROM TABLE2;
 Query 3 : SELECT * FROM TABLE_A;
 
 Assume i have the above queries. Query 1 and Query 3 can occur concurrently . 
 If one insert is taking
 place , the other should wait. How do i achieve this ??

Is that a typo and you really mean query 1 an query 2 above?

Why would you want to prevent concurrent inserts?
Maybe you don't really need to hobble your performance like that.

If you really want that, it is easy with table locks.
Your INSERT could look like that:

BEGIN;
LOCK table_a IN EXCLUSIVE MODE;
INSERT INTO table_a ...
COMMIT;

How do the concurrent inserts take place in postgres ?? Is data stored 
 temporarily for both queries
 separately and then written into the table ??

No, it is written to the table concurrently.
A table is organized in pages of 8KB each, and there is no problem with writing 
to
different pages concurrently.

Yours,
Laurenz Albe

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

Re: [GENERAL] partitioning query planner almost always scans all tables

2015-01-21 Thread Spiros Ioannou

@Rob Sargent: sorry Rob, not sure what you are asking.

@Kyotaro HORIGUCHI
thanks for your reply and time Kyotaro,

Using the following query
EXPLAIN ANALYZE  SELECT * FROM measurement_events WHERE
measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' AND
measurement_time = '2015-01-01 00:00:00+0' LIMIT 1;

produces this plan:

 Limit  (cost=0.00..4.02 rows=1 width=67) (actual time=49.125..49.125
rows=1 loops=1)
   -  Append  (cost=0.00..3644.05 rows=907 width=67) (actual
time=49.122..49.122 rows=1 loops=1)
 -  Seq Scan on measurement_events  (cost=0.00..0.00 rows=1
width=966) (actual time=0.003..0.003 rows=0 loops=1)
   Filter: ((measurement_time = '2015-01-01
02:00:00+02'::timestamp with time zone) AND (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid))
 -  Bitmap Heap Scan on *measurement_events_p2015_01*
 (cost=41.73..3546.10 rows=894 width=54) (actual time=49.119..49.119 rows=1
loops=1)
   Recheck Cond: ((measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time =
'2015-01-01 02:00:00+02'::timestamp with time zone))
   -  Bitmap Index Scan on *measurement_events_p2015_01_pkey*
 (cost=0.00..41.51 rows=894 width=0) (actual time=41.836..41.836 rows=997
loops=1)
 Index Cond: ((measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time =
'2015-01-01 02:00:00+02'::timestamp with time zone))
 -  Index Scan using *measurement_events_p2015_02_pkey* on
*measurement_events_p2015_02*  (cost=0.14..8.16 rows=1 width=966) (never
executed)
   Index Cond: ((measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time =
'2015-01-01 02:00:00+02'::timestamp with time zone))
 -  Index Scan using *measurement_events_p2015_03_pkey* on
*measurement_events_p2015_03*  (cost=0.14..8.16 rows=1 width=966) (never
executed)
   Index Cond: ((measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time =
'2015-01-01 02:00:00+02'::timestamp with time zone))
... (cut for brevity)

1) Do you know if this means that the query will stop on 1st find (since it
is limit 1), or will it search all tables regardless results?

2) To improve on the above, do you (or anyone else) have any input on this:

to get the latest value from all tables, we were using the following query
(before partitioning):

EXPLAIN ANALYZE  SELECT * FROM measurement_events WHERE
measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' ORDER BY
measurement_time DESC LIMIT 1;

This seems to fail, scanning all tables. Do you think this can be improved
at all ? The query plan of the above query is as follows:

--
 Limit  (cost=5.57..9.99 rows=1 width=921) (actual time=5.361..5.361 rows=1
loops=1)
   -  Merge Append  (cost=5.57..451374.16 rows=102155 width=921) (actual
time=5.359..5.359 rows=1 loops=1)
 Sort Key: measurement_events.measurement_time
 -  Index Scan Backward using measurement_events_pkey on
*measurement_events*  (cost=0.12..8.14 rows=1 width=966) (actual
time=0.004..0.004 rows=0 loops=1)
   Index Cond: (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
 -  Index Scan Backward using *measurement_events_p2014_01_pkey*
on *measurement_events_p2014_01*  (cost=0.14..8.16 rows=1 width=966)
(actual time=0.002..0.002 rows=0 loops=1)
   Index Cond: (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
 -  Index Scan Backward using *measurement_events_p2014_02_pkey*
on *measurement_events_p2014_02*  (cost=0.14..8.16 rows=1 width=966)
(actual time=0.001..0.001 rows=0 loops=1)
   Index Cond: (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
 -  Index Scan Backward using *measurement_events_p2014_03_pkey*
on *measurement_events_p2014_03*  (cost=0.14..8.16 rows=1 width=966)
(actual time=0.001..0.001 rows=0 loops=1)
   Index Cond: (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)

. (cut for brevity)

[GENERAL] Fwd: Ask for a question