Re: [GENERAL] (collation) Building postgresql on FreeBSD, the pros and cons of icu

2013-10-25 Thread Achilleas Mantzios
On 23/10/2013 16:44, Tom Lane wrote: Patrick Dung writes: By default, FreeBSD ports does not build postgresql with icu (http://www.icu-project.org/). Postgres does not have any option to use ICU, default or otherwise. Nor is it likely to happen in future, judging from previous discussions of

[GENERAL] "Recheck conditions" on indexes

2013-10-25 Thread Ivan Voras
Hi, I'm just wondering: in the execution plan such as this one, is the "Recheck Cond" phase what it apparently looks like: an additional check on the data returned by indexes, and why is it necessary? I would have though that indexes are accurate enough? cms=> explain analyze select * from users

Re: [GENERAL] "Recheck conditions" on indexes

2013-10-25 Thread Albe Laurenz
Ivan Voras wrote: > I'm just wondering: in the execution plan such as this one, is the > "Recheck Cond" phase what it apparently looks like: an additional check > on the data returned by indexes, and why is it necessary? I would have > though that indexes are accurate enough? > > cms=> explain ana

Re: [GENERAL] Replication and fsync

2013-10-25 Thread Albe Laurenz
DDT wrote: > According to manual, when you set "synchronous_commit" to on, the transaction > commits will wait until > master and slave flush the commit record of transaction to the physical > storage, so I think even if > turn off the fsync on master is safe for data consistency and data will no

Re: [GENERAL] "Recheck conditions" on indexes

2013-10-25 Thread Ivan Voras
On 25/10/2013 11:06, Albe Laurenz wrote: > Just because there is an entry in the index does not imply that the > corresponding table entry is visible for this transaction. > To ascertain that, the table row itself has to be checked. Understood. > PostgreSQL 9.2 introduced "index only scan" which

[GENERAL] pg_upgrade 9.1.9 ->9.3.1

2013-10-25 Thread Marc Mamin
Hello, I'm evaluating pg_upgrade and there seems to be something wrong with my test: the data get copied within the old data directory instead of the new one Do I have to explicitely set more option or define some environment variables ? If this is of concern, there are some redirections with sy

Re: [GENERAL] Count of records in a row

2013-10-25 Thread Elliot
On 2013-10-24 17:09, Robert James wrote: On 10/22/13, Elliot wrote: It looks like you already found a solution, but here's one with a CTE. I cobbled this together from an older query I had for doing something similar, for which I unfortunately lost the original source of this approach. Also, th

Re: [GENERAL] pg_upgrade 9.1.9 ->9.3.1

2013-10-25 Thread Peter Eisentraut
On 10/25/13, 7:20 AM, Marc Mamin wrote: > Hello, > > I'm evaluating pg_upgrade and there seems to be something wrong with my test: > the data get copied within the old data directory instead of the new one > > Do I have to explicitely set more option or define some environment variables > ? > >

Re: [GENERAL] pg_upgrade 9.1.9 ->9.3.1

2013-10-25 Thread Marc Mamin
> From: Peter Eisentraut [mailto:pete...@gmx.net] > On 10/25/13, 7:20 AM, Marc Mamin wrote: > > Hello, > > > > I'm evaluating pg_upgrade and there seems to be something wrong with > my test: > > the data get copied within the old data directory instead of the new > > one > > > > Do I have to exp

Re: [GENERAL] "Recheck conditions" on indexes

2013-10-25 Thread Tom Lane
Ivan Voras writes: > I'm just wondering: in the execution plan such as this one, is the > "Recheck Cond" phase what it apparently looks like: an additional check > on the data returned by indexes, and why is it necessary? Bitmap indexscans are potentially lossy. If the bitmap recording all the t

Re: [GENERAL] Need help how to manage a couple of daily DB copies.

2013-10-25 Thread Vincent Veyron
Le vendredi 25 octobre 2013 à 04:50 +0200, Andreas a écrit : > > > well, not quite > > We are not talking about files but databases within the db server. > > Lets keep 3 copies total > > the idea is to start with the database db_test today (2013/10/24) > 2013/10/25: rename db_test to db_tes

Re: [GENERAL] PostgreSQL Point In Time Recovery

2013-10-25 Thread Alan Hodgson
On Thursday, October 24, 2013 11:13:34 PM Jayadevan wrote: > Alan Hodgson wrote > > > That's basically what warm standby's do, isn't it? As long as they keep > > recovery open it should work. > > A warn standby will be almost in sync with the primary, right? So recovery > to point-in-time (like 1

Re: [GENERAL] Count of records in a row

2013-10-25 Thread Robert James
Ingenious! I actually think, however, there was a subtle bug in, though I see you fixed it. The line: - row_number() over () as d needs to be: - row_number() over (order by i asc) as d I discovered this when working your code into my application. I got very, very wei

[GENERAL] Detecting change in event properties

2013-10-25 Thread Robert James
I have a table of (timed) events, and I'm interested in marking events whose properties have changed from the previous event. I believe this can be done with window functions, but I'm not sure how. What window function can give me a field from the _previous_ row? (To elaborate, I'm interested in

[GENERAL] Window functions and relational algebra

2013-10-25 Thread Robert James
I'm just discovering the power of window functions. Is there any mathematical formalism for them, similar to relational algebra? It would seem to me that window functions aren't expressable in pure relational algebra, but that a well defined extension to it would be possible to express them; at

[GENERAL] Increasing CPU usage of PostgreSQL

2013-10-25 Thread Rahila Syed
Hello All, I am using jdbcrunner-1.2 to run PostgreSQL performance tests. For certain tests, i need to increase the CPU usage of the servers especially at user level. I tried using both tpcc and tpcb load with scale factor of 100. Even after setting the number of client connections as high as 420,

Re: [GENERAL] Increasing CPU usage of PostgreSQL

2013-10-25 Thread Scott Marlowe
On Fri, Oct 25, 2013 at 8:29 AM, Rahila Syed wrote: > Hello All, > > I am using jdbcrunner-1.2 to run PostgreSQL performance tests. For > certain tests, i need to increase the CPU usage of the servers > especially at user level. > I tried using both tpcc and tpcb load with scale factor of 100. Eve

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread Robert James
On 10/25/13, Robert James wrote: > I have a table of (timed) events, and I'm interested in marking events > whose properties have changed from the previous event. > > I believe this can be done with window functions, but I'm not sure > how. What window function can give me a field from the _previ

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread Tom Lane
Robert James writes: >> (To elaborate, I'm interested in: >> * Finding field x of the _previous_ row >> * Finding field x of the _next_ row >> * Finding field x of the _previous_ row that meets a certain criteria >> (which the current row may or may not meet) >> ) > The first two are actually tri

Re: [GENERAL] Window functions and relational algebra

2013-10-25 Thread David Johnston
Robert James wrote > I'm just discovering the power of window functions. Is there any > mathematical formalism for them, similar to relational algebra? > > It would seem to me that window functions aren't expressable in pure > relational algebra, but that a well defined extension to it would be >

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread Robert James
On 10/25/13, Tom Lane wrote: > Robert James writes: >>> (To elaborate, I'm interested in: >>> * Finding field x of the _previous_ row >>> * Finding field x of the _next_ row >>> * Finding field x of the _previous_ row that meets a certain criteria >>> (which the current row may or may not meet) >

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread Pavel Stehule
Hello 2013/10/25 Robert James > On 10/25/13, Tom Lane wrote: > > Robert James writes: > >>> (To elaborate, I'm interested in: > >>> * Finding field x of the _previous_ row > >>> * Finding field x of the _next_ row > >>> * Finding field x of the _previous_ row that meets a certain criteria > >

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread Elliot
On 2013-10-25 13:35, Robert James wrote: On 10/25/13, Robert James wrote: I have a table of (timed) events, and I'm interested in marking events whose properties have changed from the previous event. I believe this can be done with window functions, but I'm not sure how. What window function

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread Marc Mamin
>Von: pgsql-general-ow...@postgresql.org >[pgsql-general-ow...@postgresql.org]" im Auftrag von "Elliot >[yields.falseh...@gmail.com] >Gesendet: Freitag, 25. Oktober 2013 20:33 > >On 2013-10-25 13:35, Robert James wrote: >> On 10/25/13, Robert James wrote: >>> I have a table of (timed) events, a

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread David Johnston
Robert James wrote > * Finding field x of the _previous_ row that meets a certain criteria > (which the current row may or may not meet) > ) This question could be phrased better. I provide an answer to my interpretation below. You'll need to play with the frame definition because I don't yet ha

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread David Johnston
Marc Mamin-2 wrote > I would misuse GUC variables for this. > (using the functions current_setting and set_config) > > define a set get and switch fuction (I use operators for better > readability) > something like: > > select 'a' ==> 'foo' > 'a' > select 'b' <==> 'foo' > 'a' > select <== 'foo' >

Re: [GENERAL] Detecting change in event properties

2013-10-25 Thread David Johnston
Elliot wrote > Maybe a custom aggregate that takes the last item in a set? > > CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) > RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ > SELECT $2; > $$; > > CREATE AGGREGATE public.last ( > sfunc= public

Re: [GENERAL] (collation) Building postgresql on FreeBSD, the pros and cons of icu

2013-10-25 Thread Patrick Dung
On Wednesday, October 23, 2013 10:00 PM, Patrick Dung wrote: > On Wednesday, October 23, 2013 9:45 PM, Tom Lane wrote: > Patrick Dung writes: > >  By default, FreeBSD ports does not build postgresql with icu >(http://www.icu-project.org/ > ). > > Postgres does not have any option to u

Re: [GENERAL] (collation) Building postgresql on FreeBSD, the pros and cons of icu

2013-10-25 Thread Patrick Dung
> >On Friday, October 25, 2013 3:12 PM, Achilleas Mantzios > wrote: >On 23/10/2013 16:44, Tom Lane wrote: >> Patrick Dung writes: >>> By default, FreeBSD ports does not build postgresql with icu >>> (http://www.icu-project.org/). >> Postgres does not have any option to use ICU, default or otherw

Re: [GENERAL] PostgreSQL Point In Time Recovery

2013-10-25 Thread Jayadevan
Alan Hodgson wrote > Well, yeah. The point was that you possibly could run it for a while to > "catch > up" without taking a new base backup if you desired. You should also keep > copies of it for PITR. Something like this - delayed replication

Re: [GENERAL] PostgreSQL Point In Time Recovery

2013-10-25 Thread Gregory Haase
Before going through something like delayed replication, you really want to consider using zfs or lvm and taking regular snapshots on your hot or warm standby. In the event of the accidental table drop, you can just roll back to the snapshot prior and then do PITR from there. Greg Haase On Fri,

Re: [GENERAL] Segmentation fault: pg_upgrade 9.1 to 9.3: pg_dump: row number 0 is out of range 0..-1

2013-10-25 Thread Alan Nilsson
I ran into something tonight that seems relevant here, or certainly related: I recently updated my app(s) libpq version from 9.1 to 9.3 and immediately I starting seeing: row number 0 is out of range 0..-1 spewed to stdout. I traced it down to this code: if (PQresultStatus(result) ==