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

[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) == PGRES_TUPLES_OK){
if (!PQgetisnull(result, 0, 0)){
..
}
}

if the result contains 0 tuples, the message is spit to stdout.  This is new 
behavior, I went back and rebuilt using lib versions (according to 
PQlibVersion) 90102, 90203, and 90300.  Neither version 90102 & 90203 spewed to 
stdout.

I don't know if this is causing the OP's crash, but it seems to me that there 
is something going on in libpq that needs to be looked at.  If nothing else 
than to get rid of the spewing to stdout.

alan


On Oct 10, 2013, at 9:47 AM, Bruce Momjian  wrote:

> On Sat, Sep 14, 2013 at 09:40:01PM -0400, Robert Nix wrote:
>> Running a pg_upgrade task is causing Segmentation fault:
>> 
>> command: "/usr/lib/postgresql/9.3/bin/pg_dump" --host "/var/lib/postgresql"
>> --port 50432 --username "postgres" --schema-only --quote-all-identifiers
>> --binary-upgrade --format=custom  --file="pg_upgrade_dump_6064585.custom" "u"
 "pg_upgrade_dump_6064585.log" 2>&1
>> pg_dump: row number 0 is out of range 0..-1
>> Segmentation fault (core dumped)
> 
> This error is coming from libpq, specifically
> check_tuple_field_number(), which is called by PQgetvalue(),
> PQgetlength(), and PQgetisnull().  As I have never seen this error
> before, there might be something wrong with the system catalogs on that
> cluster.  It would be good to run pg_dump --binary-upgrade on that
> database and try to identify the object being dumped that is causing the
> crash.
> 
> -- 
>  Bruce Momjian  http://momjian.us
>  EnterpriseDB http://enterprisedb.com
> 
>  + Everyone has their own god. +
> 
> 
> -- 
> 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] PostgreSQL Point In Time Recovery

[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, Oct 25, 2013 at 11:14 PM, Jayadevan wrote:

> 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
>    might
> help. I could say lag by 12 hours, or 1 transactions...
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775997.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] PostgreSQL Point In Time Recovery

[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
   might
help. I could say lag by 12 hours, or 1 transactions... 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775997.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] (collation) Building postgresql on FreeBSD, the pros and cons of icu

[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 otherwise.
>> Nor is it likely to happen in future, judging from previous discussions
>> of the idea.
>
>Hi Tom, Patrick
>FreeBSD indeed has a config option to build with ICU, just
># /usr/ports/databases/postgresql93-server
># make config
>and you will be able to see this.
>The relevant README is here : 
>http://people.freebsd.org/~girgen/postgresql-icu/README.html
>Patrick also you may build postgresql by hand and apply the patch manually 
>from : /usr/ports/databases/postgresql93-server
>Although being in a non-english speaking company, i have not tried this 
>neither at work or at home.
>Hope that helps.
>

Hi Achilleas,

Sorry I have hit to send button too fast in the last mail...

Yes, I know FreeBSD has a specific patch to use ICU on Postgresql.
And officially Postgresql, do not come with ICU patch natively.

Thanks.

Patrick

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

[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 use ICU, default or otherwise.
> Nor is it likely to happen in future, judging from previous discussions
> of the idea.
> 
> regards, tom lane
> 

OK, now I understand that FreeBSD case, they have a specific patch to use icu.
The default PostgreSQL does use ICU.
Thanks for pointing that out.

Thanks,

Patrick

Re: [GENERAL] Detecting change in event properties

[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.last_agg,
>  basetype = anyelement,
>  stype= anyelement
> );

Conceptually similar to my array_last_nonnull(array_agg(...)) methodology
and the GUC methodology but has the advantage of saving minimal state
(compared to the array_agg()) and not abusing GUC for storage of the
single-value state.

The example would need the same alteration to the frame clause but otherwise
would appear to work in the manner presumed by the OP's original question.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Detecting-change-in-event-properties-tp5775959p5775977.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] Detecting change in event properties

[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'
> 'b'
> 
> 
> and  in your query:
> 
> SELECT
>  case when test then col <==> 'foo' else <== 'foo' end

Is it possible to alter GUC on a record-by-record basis?

Is this something you have actually done?

Even if it does technically work this seems like a last-resort kind of
solution.  The syntax (though that could be hidden in a wrapper function) is
definitely unusual and the abuse of the GUC system in this manner is
surprising.

How would this interface with a window function?  The main consideration is
dealing with multiple partitions and the fact that a window column
calculation requires the use of a function while this solution would seem to
preclude that.

David J.
 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Detecting-change-in-event-properties-tp5775959p5775975.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] Detecting change in event properties

[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 have that
syntax memorized and am too lazy to figure it out right now.

The following gives, for every row, the last "val" having a value less than
25.  It does this by converting all other values to NULL than returning the
most proximate value that is not null.  The ORDER BY in the OVER() clause
gives you an "unbounded preceding to current row" frame by default so the
current row is a valid value for the final answer.


WITH data (key, val) AS ( VALUES (1,10),(2,20),(3,30),(4,10),(5,25) )
SELECT key, val, array_last_nonnull(array_agg(CASE WHEN val < 25 THEN val
ELSE NULL END) OVER (ORDER BY key)) FROM data


where "array_last_nonnull(...)" is defined as:

CREATE OR REPLACE FUNCTION array_last_nonnull(in_array anyarray) 
RETURNS anyelement
AS $$

SELECT unnest FROM (
SELECT unnest, row_number() OVER () AS array_index FROM (
SELECT unnest($1)
) explode ) filter
WHERE unnest IS NOT NULL 
ORDER BY array_index DESC
LIMIT 1;

$$
LANGUAGE sql
STRICT
IMMUTABLE
;

This is probably not the most preformant solution but it is fairly simple,
easy to debug (i.e., you can always view the array_agg data), and gives you
a benchmark to compare against should you attempt alternatives.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Detecting-change-in-event-properties-tp5775959p5775971.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] Detecting change in event properties

[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, 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:
>>> * 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 trivial - lag(field_x) over (order by [same
>> order as query]) and lead(...).
>>
>> But the last one seems ellusive - How can I find the value of field x
>> on the previous row WHERE a criteria is met? Is it possible to do this
>> at all with a window function?
>>
>>
>
>Maybe a custom aggregate that takes the last item in a set?


Hello,

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'
'b'


and  in your query:

SELECT
 case when test then col <==> 'foo' else <== 'foo' end
 
regards,

Marc Mamin



-- 
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] Detecting change in event properties

[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 can give me a field from the _previous_
row?

(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 trivial - lag(field_x) over (order by [same
order as query]) and lead(...).

But the last one seems ellusive - How can I find the value of field x
on the previous row WHERE a criteria is met? Is it possible to do this
at all with a window function?




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.last_agg,
basetype = anyelement,
stype= anyelement
);

Same set up as last time:
create temp table data (i int, val char);

insert into data (val, i)
values
('A',1),
('A',2),
('A',3),
('B',4),
('C',5),
('A',6),
('D',7),
('A',8),
('A',9),
('D',10),
('D',11),
('B',12),
('C',13),
('C',14)
;

And usage with a case like this? I read somewhere that filtering in 
aggregates is coming soon-ish (or maybe already?) to avoid the case, but 
this should suffice.


select i, val, last(case val when 'B' then i end) over (order by i asc)
from data
order by i asc
;

ivallast
1A
2A
3A
4B4
5C4
6A4
7D4
8A4
9A4
10D4
11D4
12B12
13C12
14C12



--
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] Detecting change in event properties

[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
> >>> (which the current row may or may not meet)
> >>> )
> >
> >> The first two are actually trivial - lag(field_x) over (order by [same
> >> order as query]) and lead(...).
> >
> > Right.
> >
> >> But the last one seems ellusive - How can I find the value of field x
> >> on the previous row WHERE a criteria is met? Is it possible to do this
> >> at all with a window function?
> >
> > I don't see any way to achieve that with any of the built-in window
> > functions, but I believe it could be done by a custom window function.
> > Are you up for some C coding?
>
> Hmmm... certainly nothing I would trust on a production db.
>
> Is there a way to do it without C not using window functions? Perhaps
> with some type of JOIN?
>

you can write a table function with inner loop cycle over cursor

Regards

Pavel



>
>
> --
> 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] Detecting change in event properties

[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)
>>> )
>
>> The first two are actually trivial - lag(field_x) over (order by [same
>> order as query]) and lead(...).
>
> Right.
>
>> But the last one seems ellusive - How can I find the value of field x
>> on the previous row WHERE a criteria is met? Is it possible to do this
>> at all with a window function?
>
> I don't see any way to achieve that with any of the built-in window
> functions, but I believe it could be done by a custom window function.
> Are you up for some C coding?

Hmmm... certainly nothing I would trust on a production db.

Is there a way to do it without C not using window functions? Perhaps
with some type of JOIN?


-- 
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] Window functions and relational algebra

[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
> possible to express them; at least for those of us so inclinded, being
> able to think in terms of an alegbra would make reasoning about them
> much more powerful.

The underlying relation the widow is placed over is fully defined from the
relational algebra in surrounding query; but given that it can only see a
simple relation, and cannot itself perform union/except/intersect nor joins
and restrictions (i.e., it cannot remove rows for the final output), I'm not
sure how relational algebra would even be a valid concept in this context.

The idea of window functions is that you take the an already existing source
relation and simply add columns/attributes whose underlying formulas are
able to see any or all of the existing rows in the source relation.  I guess
you can call this simple projection onto the underlying relation but being
limited to "add only projection" makes calling that operation relational a
stretch.

I find this line of reasoning quite simple and elegant and do not see what
trying to extend relational algebra would provide; but my tendencies in this
area are toward the practical and away from the underlying theory and
foundational math.  SQL is not pure relational and so when reasoning about
SQL it is necessary to incorporate different frameworks than just relational
algebra into your thinking.  In this case the relational aspects are
initially processed then special functional projections are applied to
construct a final relation.  That relation can then be algebraically joined
to other relations or returned to the caller.

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Window-functions-and-relational-algebra-tp5775942p5775957.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] Detecting change in event properties

[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 trivial - lag(field_x) over (order by [same
> order as query]) and lead(...).

Right.

> But the last one seems ellusive - How can I find the value of field x
> on the previous row WHERE a criteria is met? Is it possible to do this
> at all with a window function?

I don't see any way to achieve that with any of the built-in window
functions, but I believe it could be done by a custom window function.
Are you up for some C coding?

regards, tom lane


-- 
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] Detecting change in event properties

[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 _previous_
> row?
>
> (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 trivial - lag(field_x) over (order by [same
order as query]) and lead(...).

But the last one seems ellusive - How can I find the value of field x
on the previous row WHERE a criteria is met? Is it possible to do this
at all with a window function?


-- 
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] Increasing CPU usage of PostgreSQL

[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. Even
> after setting the number of client connections as high as 420, I am
> unable to achieve high CPU usage. It is hardly 3 percent. I think this
> is because most of the client connections are idle.
> The tpcc and tpcb transactions seem to be consuming very less CPU.
>
> In postgresql.conf file, I have lowered checkpoint segments to 1 in
> order to overwhelm the server with checkpoints but no significant
> increase in iowait of the CPU.
>
> Can somebody suggest a better idea to load PostgreSQL servers to
> increase CPU usage.?
>
> Configurations of my machine is:
>
> Processors:   Xeon E5-2650 Processor Kit
>   Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T,
> 20 MB) * 2 nos
>
>
> RAM :  32GB DDR3-1600 REG Memory Kit
> 8x 4GB Registered ECC DIMM, DDR3L-1600(PC3L-12800)
>
> HDD:  450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos
>  1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s
>
> Disk Speed  : 10,000 RPM
>
> RAID Controller (512MB, RAID 0/1)

My guess is that you're maxing out your IO subsystem long before
you're maxing out CPU. What does

iostat -xd 10

have to say about it?

To understand recursion, one must first understand recursion.


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

[GENERAL] Increasing CPU usage of PostgreSQL

[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, I am
unable to achieve high CPU usage. It is hardly 3 percent. I think this
is because most of the client connections are idle.
The tpcc and tpcb transactions seem to be consuming very less CPU.

In postgresql.conf file, I have lowered checkpoint segments to 1 in
order to overwhelm the server with checkpoints but no significant
increase in iowait of the CPU.

Can somebody suggest a better idea to load PostgreSQL servers to
increase CPU usage.?

Configurations of my machine is:

Processors:   Xeon E5-2650 Processor Kit
  Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T,
20 MB) * 2 nos


RAM :  32GB DDR3-1600 REG Memory Kit
8x 4GB Registered ECC DIMM, DDR3L-1600(PC3L-12800)

HDD:  450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos
 1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s

Disk Speed  : 10,000 RPM

RAID Controller (512MB, RAID 0/1)


Thank you,


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

[GENERAL] Window functions and relational algebra

[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 least for those of us so inclinded, being
able to think in terms of an alegbra would make reasoning about them
much more powerful.


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

[GENERAL] Detecting change in event properties

[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:
* 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)
)

I must say that window functions are amazing - they're a whole new
world, really.


-- 
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] Count of records in a row

[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 weird results - with one order of columns in the select, I
got the correct answer, but with another one I didn't.  After much
debugging, I realized that the original version ("- row_number over
()") wasn't defined! So, depending on how I wrote the select
statement, Postgres could pick different orders!

But I see your cleaned up version already fixed this!

On 10/25/13, Elliot  wrote:
> Glad I could help. It's easier to understand if you break apart the CTE.
> I'm also moving around the order by i to clean this up a little. Sorry
> for the formatting.
>
> Running this:
> select i,
>val,
>row_number() over (partition by val order by i asc) as class_i,
>row_number() over (order by i asc) as overall_i,
>row_number() over (partition by val order by i asc)
>  - row_number() over () as d
> from data
>
> Yields this:
> ivalclass_ioverall_id
> 1A110
> 2A220
> 3A330
> 4B14-3
> 5C15-4
> 6A46-2
> 7D17-6
> 8A58-3
> 9A69-3
> 10D210-8
> 11D311-8
> 12B212-10
> 13C213-11
> 14C314-11
>
> class_i counts the row number within a class and overall_i counts the
> overall row number in the sequence. Here's just one class extracted to
> emphasize that:
>
> ivalclass_ioverall_id
> 1A110
> 2A220
> 3A330
> 6A46-2
> 8A58-3
> 9A69-3
>
> Within a given consecutive run of a particular class the difference
> between class_i and overall_i will always be the same (because they're
> both increasing by the same amount) but that difference between runs
> will always be different (because each run starts the sequences at
> different offsets). "d" is the difference of the two. Because that value
> segments the runs, all that needs to be done is group by it and count
> the items in the group to get the length of the runs.
>
> The xxx column was junk left over from copying and pasting and
> verifying. Apologies :). This is a cleaned up version:
>
> with x
> as
> (
>select i,
>   val,
>   row_number() over (partition by val order by i asc)
> - row_number() over (order by i asc) as d
>from data
> )
> select val,
> count(*)
> from x
> group by d,
>   val
> order by min(i)
> ;
>
>


-- 
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] PostgreSQL Point In Time Recovery

[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 10 AM this morning) won't be possible. We need a
> base, but it shouldn't be so old that it takes hours to catchup- that was my
> thought. As John mentioned, looking at the WAL/transaction numbers, time to
> recover etc need to be looked at.
> 

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.



-- 
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] Need help how to manage a couple of daily DB copies.

[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_test_13025  and import the latest 
> dump into a new db_test
> 2013/10/26:   rename  db_test  to  db_test_13026 ... import
> 2013/10/27:   rename  db_test  to  db_test_13027 ... import
> 2013/10/28:   rename  db_test  to  db_test_13028 ... import
> Now we've got db_test and 4 older copies.
> Find the oldest copy and drop it.   -->   drop db_test_131025
> 
> or better every day drop every copy but the 3 newest.
> 
> and so on
> 
> this needs to be done by an external cron script or probaply by a 
> function within the postgres database or any other administrative database.
> 
> The point is to give the assistant a test-db where he could mess things up.
> In the event he works longer than a day on a task his work shouldn't be 
> droped completely when the test-db gets automatically replaced.
> 

I assume db_test is created from a dump file? if that's the case, and if
your system allows it,  using logrotate on the dump is very
straithforward; e.g. to rotate an archive everyday, keeping a weekly
archive over 52 two weeks, simply create the
file /etc/logrotate.d/myapp :


#Create rotation for myapp's backups
/var/backups/myapp/myapp.gz {
weekly
missingok
rotate 52
notifempty
}



-- 
Salutations, Vincent Veyron

http://marica.fr/site/demonstration
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance



-- 
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] "Recheck conditions" on indexes

[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
tuple locations reported by the index gets too big, we compress entries by
remembering only that a particular page has to be visited, not the precise
tuples on that page.  Once this happens, the indexed condition has to be
rechecked at each tuple on the page, once we finally get to the point of
visiting it.  The recheck condition isn't used on pages that didn't become
lossy.

Recheck conditions are also used for cases where the index isn't able to
test the query WHERE condition exactly, such as anchored LIKE conditions.
That case doesn't apply to your example, though.

> Also, why is it using the Bitmap Index Scan in both cases?

We don't support ORed index conditions in plain index scans, much less use
of more than one index.  The only mechanism that can implement that is a
BitmapOr.

regards, tom lane


-- 
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] pg_upgrade 9.1.9 ->9.3.1

[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 explicitely set more option or define some environment
> variables ?
> >
> > If this is of concern, there are some redirections with symlinks
> > within the old $PGDATA
> >
> >
> > ./pg_upgrade \
> >  --old-datadir "/data/postgresql-data-9"\  --new-datadir
> > "/pgdata/postgresql_93-data-9"\  --old-bindir
> > "/opt/intershop/postgresql-9.1.9-9/bin"\
> >  --new-bindir "/opt/intershop/postgresql-9.3.1-9/bin"
> >
> > =>
> >
> > ll /data/postgresql-data-9/tblspc_data/cicpg_logs/
> >
> > drwx-- 3 isdb9 isgrp9 4096 Oct 21 15:48 PG_9.1_201105231
> > drwx-- 3 isdb9 isgrp9 4096 Oct 25 12:26 PG_9.3_201306121


> It appears you are using tablespaces.  In that case, that's normal.

which means I cannot take advantage of a separate new file system for the copy 
and first have to organize enough free place in the "old" one.
correct ?

thanks,

Marc


-- 
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] pg_upgrade 9.1.9 ->9.3.1

[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 
> ?
> 
> If this is of concern, there are some redirections with symlinks within the 
> old $PGDATA
> 
> 
> ./pg_upgrade \
>  --old-datadir "/data/postgresql-data-9"\
>  --new-datadir "/pgdata/postgresql_93-data-9"\
>  --old-bindir "/opt/intershop/postgresql-9.1.9-9/bin"\
>  --new-bindir "/opt/intershop/postgresql-9.3.1-9/bin"
> 
> =>
> 
> ll /data/postgresql-data-9/tblspc_data/cicpg_logs/
> 
> drwx-- 3 isdb9 isgrp9 4096 Oct 21 15:48 PG_9.1_201105231
> drwx-- 3 isdb9 isgrp9 4096 Oct 25 12:26 PG_9.3_201306121

It appears you are using tablespaces.  In that case, that's normal.



-- 
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] Count of records in a row

[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, this implies that there is something that gives an
ordering to these rows (in this case, the field "i").

create temp table data (i int, val char);

insert into data (val, i)
values
('A',1),
('A',2),
('A',3),
('B',4),
('C',5),

with x
as
(
select i,
   row_number() over () as xxx,
   val,
   row_number() over (partition by val order by i asc)
 - row_number() over () as d
from data
order by i
)
select val,
 count(*)
from x
group by d,
   val
order by min(i)
;

Elliot - Thanks for this great solution; I've tested in on my data and
it gives great results.

I'd like to understand your code.  I believe I understand most of it.
Can you explain what 'd' is?

And this clause "row_number() over (partition by val order by i asc) -
row_number() over () as d"?

(Hey, while I'm at it, is there a descriptive name for "x" too?)

Thanks
Glad I could help. It's easier to understand if you break apart the CTE. 
I'm also moving around the order by i to clean this up a little. Sorry 
for the formatting.


Running this:
   select i,
  val,
  row_number() over (partition by val order by i asc) as class_i,
  row_number() over (order by i asc) as overall_i,
  row_number() over (partition by val order by i asc)
- row_number() over () as d
   from data

Yields this:
ivalclass_ioverall_id
1A110
2A220
3A330
4B14-3
5C15-4
6A46-2
7D17-6
8A58-3
9A69-3
10D210-8
11D311-8
12B212-10
13C213-11
14C314-11

class_i counts the row number within a class and overall_i counts the 
overall row number in the sequence. Here's just one class extracted to 
emphasize that:


ivalclass_ioverall_id
1A110
2A220
3A330
6A46-2
8A58-3
9A69-3

Within a given consecutive run of a particular class the difference 
between class_i and overall_i will always be the same (because they're 
both increasing by the same amount) but that difference between runs 
will always be different (because each run starts the sequences at 
different offsets). "d" is the difference of the two. Because that value 
segments the runs, all that needs to be done is group by it and count 
the items in the group to get the length of the runs.


The xxx column was junk left over from copying and pasting and 
verifying. Apologies :). This is a cleaned up version:


with x
as
(
  select i,
 val,
 row_number() over (partition by val order by i asc)
   - row_number() over (order by i asc) as d
  from data
)
select val,
   count(*)
from x
group by d,
 val
order by min(i)
;



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

[GENERAL] pg_upgrade 9.1.9 ->9.3.1

[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 symlinks within the old 
$PGDATA


./pg_upgrade \
 --old-datadir "/data/postgresql-data-9"\
 --new-datadir "/pgdata/postgresql_93-data-9"\
 --old-bindir "/opt/intershop/postgresql-9.1.9-9/bin"\
 --new-bindir "/opt/intershop/postgresql-9.3.1-9/bin"

=>

ll /data/postgresql-data-9/tblspc_data/cicpg_logs/

drwx-- 3 isdb9 isgrp9 4096 Oct 21 15:48 PG_9.1_201105231
drwx-- 3 isdb9 isgrp9 4096 Oct 25 12:26 PG_9.3_201306121


thanks,

Marc Mamin


-- 
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] "Recheck conditions" on indexes

[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 avoids that
> additional step if it is safe to do so.

It doesn't help in this case - the plan for the same query on a copy of
the database on 9.3 is exactly the same.



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Replication and fsync

[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 not 
> be lost if slave physical
> storage is not damaged.

I don't think that this is safe.

What if the master crashes and becomes corrupted as a consequence?

It will start sending corrupted data to the slave, which will
replay it, thus becoming corrupted itself.

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] "Recheck conditions" on indexes

[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 analyze select * from users where
> other_ids->'OIB'='70328909364' or code='0023017009';
> QUERY PLAN
> 
> --
> 
>  Bitmap Heap Scan on users  (cost=8.52..39.21 rows=10 width=330) (actual
> time=0.042..0.044 rows=2 loops=1)
>Recheck Cond: (((other_ids -> 'OIB'::text) = '70328909364'::text) OR
> ((code)::text = '0023017009'::text))
>->  BitmapOr  (cost=8.52..8.52 rows=10 width=0) (actual
> time=0.035..0.035 rows=0 loops=1)
>  ->  Bitmap Index Scan on users_other_ids_oib  (cost=0.00..4.26
> rows=9 width=0) (actual time=0.023..0.023 rows=1 loops=1)
>Index Cond: ((other_ids -> 'OIB'::text) =
> '70328909364'::text)
>  ->  Bitmap Index Scan on users_code  (cost=0.00..4.26 rows=1
> width=0) (actual time=0.012..0.012 rows=1 loops=1)
>Index Cond: ((code)::text = '0023017009'::text)
>  Total runtime: 0.082 ms
> (8 rows)
> 
> Both indexes are plain btrees, the first one is on the expression on the
> hstore field (other_ids->'OIB') and the second one on a plain text
> field. Also, why is it using the Bitmap Index Scan in both cases? A
> plain query for code='foo' uses a plain index scan.
> 
> This is PostgreSQL 9.1.

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.

PostgreSQL 9.2 introduced "index only scan" which avoids that
additional step if it is safe to do so.

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

[GENERAL] "Recheck conditions" on indexes

[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 where
other_ids->'OIB'='70328909364' or code='0023017009';
QUERY PLAN

--
 Bitmap Heap Scan on users  (cost=8.52..39.21 rows=10 width=330) (actual
time=0.042..0.044 rows=2 loops=1)
   Recheck Cond: (((other_ids -> 'OIB'::text) = '70328909364'::text) OR
((code)::text = '0023017009'::text))
   ->  BitmapOr  (cost=8.52..8.52 rows=10 width=0) (actual
time=0.035..0.035 rows=0 loops=1)
 ->  Bitmap Index Scan on users_other_ids_oib  (cost=0.00..4.26
rows=9 width=0) (actual time=0.023..0.023 rows=1 loops=1)
   Index Cond: ((other_ids -> 'OIB'::text) =
'70328909364'::text)
 ->  Bitmap Index Scan on users_code  (cost=0.00..4.26 rows=1
width=0) (actual time=0.012..0.012 rows=1 loops=1)
   Index Cond: ((code)::text = '0023017009'::text)
 Total runtime: 0.082 ms
(8 rows)

Both indexes are plain btrees, the first one is on the expression on the
hstore field (other_ids->'OIB') and the second one on a plain text
field. Also, why is it using the Bitmap Index Scan in both cases? A
plain query for code='foo' uses a plain index scan.

This is PostgreSQL 9.1.





signature.asc
Description: OpenPGP digital signature

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

[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 the idea.


Hi Tom, Patrick
FreeBSD indeed has a config option to build with ICU, just
# /usr/ports/databases/postgresql93-server
# make config
and you will be able to see this.
The relevant README is here : 
http://people.freebsd.org/~girgen/postgresql-icu/README.html
Patrick also you may build postgresql by hand and apply the patch manually from 
: /usr/ports/databases/postgresql93-server
Although being in a non-english speaking company, i have not tried this neither 
at work or at home.
Hope that helps.



regards, tom lane





--
Achilleas Mantzios



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