Re: [GENERAL] Postgres vs other Postgres based MPP implementations

Hi, >>  mostly heavy read >> workloads but OLTP performance is required (like run query over 100m+ >> dataset in 15 sec) > > that isn't OLTP, its OLAP.  Online Analytic Processing rather than Online > Transaction Processing   large complex reporting queries that have to > aggregate many rows is

Re: [GENERAL] Postgres vs other Postgres based MPP implementations

On 8 Listopad 2011, 10:49, Ondrej Ivanič wrote: > Hi, >>>  mostly heavy read >>> workloads but OLTP performance is required (like run query over 100m+ >>> dataset in 15 sec) >> >> that isn't OLTP, its OLAP.  Online Analytic Processing rather than >> Online >> Transaction Processing   large comp

Re: [GENERAL] Postgres vs other Postgres based MPP implementations

Hi, 2011/11/8 Craig Ringer : > "Spreads reads too much" ? > > Are you saying there's too much random I/O? Is it possible it'd benefit from > a column store? > When you're using Greenplum are you using "Polymorphic Data Storage" column > storage "WITH (orientation=column)" ? yes, exactly. Column s

Re: [GENERAL] Streaming Replication woes

On Tue, Nov 8, 2011 at 3:45 AM, Konstantin Gredeskoul wrote: > The user 'postgres' was created using standard database installation > procedure.  It has superuser, but does not include an explicit replication > role: > > my_db=# \du >                       List of roles > Role name  |            A

Re: [GENERAL] Postgres vs other Postgres based MPP implementations

Hi, 2011/11/8 Tomas Vondra : > Sure you did - you've stated that "mostly heavy read > workloads but OLTP performance is required (like run query over 100m+ > dataset in 15 sec)." That clearly mentions OLTP  ... Whatever :) Let's make it clear: I need to run aggregates/roll ups/drill downs on larg

[GENERAL] IP range in pg_hba.conf?

Hi, properbly a simple question (with a simple answer). Nevertheless I´ve been struggeling with it for some time now. Hope you guys can point me in the right direction! I want to exclude access to our postgresql db using a configuration in the pg_hba.conf file. I have a range of IP adress that sh

Re: [GENERAL] IP range in pg_hba.conf?

2011-11-08 12:33 keltezéssel, Chrishelring írta: > Hi, > > properbly a simple question (with a simple answer). Nevertheless I´ve been > struggeling with it for some time now. Hope you guys can point me in the > right direction! > > I want to exclude access to our postgresql db using a configuration

Re: [GENERAL] IP range in pg_hba.conf?

Hi, On 8 November 2011 22:33, Chrishelring wrote: > I want to exclude access to our postgresql db using a configuration in the > pg_hba.conf file. I have a range of IP adress that should have access, but > how do I do that? > > The range is 10.17.64.1 - 10.17.79.254 (eg. 255.255.240.0 as subnet).

Re: [GENERAL] IP range in pg_hba.conf?

Can you guys explain why it is 10.17.64.0/20? Thanks! Sent from iPad 在 Nov 8, 2011，7:42 PM，Ondrej Ivanič 写道： > Hi, > > On 8 November 2011 22:33, Chrishelring wrote: >> I want to exclude access to our postgresql db using a configuration in the >> pg_hba.conf file. I have a range of IP adress t

Re: [GENERAL] IP range in pg_hba.conf?

2011/11/8 Zhidong : > Can you guys explain why it is 10.17.64.0/20? Thanks! = 255 in binary = 240 in binary So a CIDR mask of 8 would cover the first 8 bits, 16 the next 8, but when we reach 20 we've covered 20 bits. The first 255 is the first 8 bits. The next 255 is bits 9-16

Re: [GENERAL] function within a function/rollbacks/exception handling

Richard, I manage to find one comment about an implicit rollback in a section of the developer's guide when porting from Oracle-to-Postgres: "when an exception is caught by an EXECPTION clause, all database changes since the block's BEGIN are automatically rolled back" Do you know of any other p

Re: [GENERAL] function within a function/rollbacks/exception handling

On Tuesday, November 08, 2011 7:13:03 am Lori Corbani wrote: > Richard, > > I manage to find one comment about an implicit rollback in a section of > the developer's guide when porting from Oracle-to-Postgres: "when an > exception is caught by an EXECPTION clause, all database changes since > the

Re: [GENERAL] Masquerading a unique index as a primary key in 8.4?

On Tue, Oct 18, 2011 at 6:21 PM, David Pirotte wrote: > The underlying purpose is to get Londiste to acknowledge the table's key, > and this strategy seems to work without any problems.  Londiste doesn't seem > to care that the "primary key" is only reflected in pg_index and isn't > accompanied by

[GENERAL] Index Scan Backward on wrong index in partitioned table.

Hi , We have a set of partitioned tables and we run the query on main table the query is select uniq_id ,profile_id from general.profile_log where profile_id=3528336 order by uniq_id desc limit 5; there is a index on profile_id on the child tables of profile_log. The query on profile_id does no

Re: [GENERAL] Masquerading a unique index as a primary key in 8.4?

On Tue, Nov 8, 2011 at 11:28 AM, Vick Khera wrote: > On Tue, Oct 18, 2011 at 6:21 PM, David Pirotte wrote: >> The underlying purpose is to get Londiste to acknowledge the table's key, >> and this strategy seems to work without any problems.  Londiste doesn't seem >> to care that the "primary key"

Re: [GENERAL] Recommendations for SSDs in production?

On Wed, Nov 2, 2011 at 1:01 PM, Benjamin Smith wrote: > I don't mind spending some money. Can anybody comment on a recommended drive > in real world use? We have been using the RamSan-620 from Texas Memory Systems for over a year now on a heavy write load. I have a pair

Re: [GENERAL] pg_restore: [custom archiver] unexpected end of file on Postgres 9.1.1

Ok, I think I've narrowed down the problem. Doing a pg_dump with --verbose and watching it myself (it was in a cron before), I now see: pg_dump: dumping contents of table external_users pg_dump: SQL command failed pg_dump: Error message from server: ERROR: canceling statement due to conflict with

Re: [GENERAL] Postgres vs other Postgres based MPP implementations

On 11/08/11 1:49 AM, Ondrej Ivanič wrote: Greenplum or Postgres + Fusion IO can deliver this performance for us. then, thats your answer! it ain't free, oh well. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsq

Re: [GENERAL] Postgres vs other Postgres based MPP implementations

On 11/08/11 2:36 AM, Ondrej Ivanič wrote: Yeah, I know about those.. I like iotop but enterprise distributions do not ship fresh kernels... I need something which can I "safely" (slightly worse performance is acceptable but machine must survie) run in production for several hours and then cross r

[GENERAL] Grouping logs by ip and time

Hello all, I have a table which stores action logs from users. It looks something like this: log_type text, date date, "time" time without time zone, ip inet The log type can be action1, action2, action3, action4, or action5. I know that each user session will have a max of one of each l

Re: [GENERAL] Grouping logs by ip and time

On 08/11/2011 17:59, Alex Thurlow wrote: > Hello all, > I have a table which stores action logs from users. It looks > something like this: > log_type text, > date date, > "time" time without time zone, > ip inet [snip] > What I'd like to do is be able to group these logs by sessions based o

Re: [GENERAL] Grouping logs by ip and time

On 08/11/2011 18:48, Raymond O'Donnell wrote: > On 08/11/2011 17:59, Alex Thurlow wrote: >> Hello all, >> I have a table which stores action logs from users. It looks >> something like this: >> log_type text, >> date date, >> "time" time without time zone, >> ip inet > > [snip] > >> What I'd

Re: [GENERAL] Grouping logs by ip and time

On 11/08/11 10:48 AM, Raymond O'Donnell wrote: Would something like this work? - select ip, max("time") - min("time") as session_duration from log_table group by ip; This doesn't take the date into account - what happens if the session spans midnight? You can get around this by using a

Re: [GENERAL] Grouping logs by ip and time

Would something like this work? - select ip, max("time") - min("time") as session_duration from log_table group by ip; I don't think this is the right way to do. This is based on ip address, so if - client connect diffrent times with same ip - client has sime ip but he made another a

Re: [GENERAL] Grouping logs by ip and time

On 11/8/2011 1:00 PM, Ascarabina wrote: Would something like this work? - select ip, max("time") - min("time") as session_duration from log_table group by ip; I don't think this is the right way to do. This is based on ip address, so if - client connect diffrent times with same ip -

Re: [GENERAL] Replication Across Two Servers?

Use Debian backports. We have several debian squeeze with backports running PostgreSQL 9.1. 2011/11/4 Carlos Mennens : > On Fri, Nov 4, 2011 at 11:52 AM, Brandon Phelps wrote: >> Carlos, >> >> Streaming replication was introduced in PostgreSQL 9.0 and should do what >> you want. >> >> http://wiki

Re: [GENERAL] function within a function/rollbacks/exception handling

On Tue, Nov 8, 2011 at 9:13 AM, Lori Corbani wrote: > Richard, > > I manage to find one comment about an implicit rollback in a section of > the developer's guide when porting from Oracle-to-Postgres:  "when an > exception is caught by an EXECPTION clause, all database changes since > the block's

Re: [GENERAL] Postgres vs other Postgres based MPP implementations

Hi, On 9 November 2011 04:53, John R Pierce wrote: > On 11/08/11 1:49 AM, Ondrej Ivanič wrote: >> >> Greenplum or Postgres + Fusion IO can deliver this performance for us. > > then, thats your answer!   it ain't free, oh well. FusionIO is little bit problematic: smaller card (2.4TB) has serious

[GENERAL] Getting Error On pg_dump

Hello All Could you please help me ,Getting error when I try to get backup of database pg_dump: SQL command failed pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast value 87303 in pg_toast_27342 Thx Prashant

Re: [GENERAL] Getting Error On pg_dump

On 11/09/2011 05:49 AM, Prashant Bharucha wrote: Hello All Could you please help me ,Getting error when I try to get backup of database pg_dump: SQL command failed pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 87303 in pg_toast_27342 It looks like you have

Re: [GENERAL] Postgres vs other Postgres based MPP implementations

On 11/08/2011 06:36 PM, Ondrej Ivanič wrote: Are you saying there's too much random I/O? Is it possible it'd benefit from a column store? When you're using Greenplum are you using "Polymorphic Data Storage" column storage "WITH (orientation=column)" ? yes, exactly. Column store and compression

Re: [GENERAL] Postgres vs other Postgres based MPP implementations

Hi, > it's a lot of work and right now the only people > who've done that work aren't giving it away for free - or not in any form > that can be integrated into PostgreSQL without removing other capabilities > other users need. One MPP vendor implemented columnar store in roughly six months -- lo

Re: [GENERAL] Grouping logs by ip and time

Hi Alex, in PG9 you can use a query like this: with a as ( select a.*, rank() over (partition by a.ip,a.date order by a.log_type, a.time) from log_table a ) select a.*, b.*, b.time-a.time from a join a b on a.ip=b.ip and a.date=b.date and a.rank+1=b.rank this orders entry by time grouped by