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

[Ondrej Ivanič]

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 classic OLAP.

I didn't say OLTP. Our workload is "aggregations/drill downs/roll
ups/... " (= OLAP) but we need OLTP like performance i.e. our users
are not keen to wait more than several seconds for the result.
Greenplum or Postgres + Fusion IO can deliver this performance for us.

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.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] Postgres vs other Postgres based MPP implementations

[Tomas Vondra]

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 complex reporting queries that have
>> to
>> aggregate many rows is classic OLAP.
>
> I didn't say OLTP. Our workload is "aggregations/drill downs/roll
> ups/... " (= OLAP) but we need OLTP like performance i.e. our users
> are not keen to wait more than several seconds for the result.
> Greenplum or Postgres + Fusion IO can deliver this performance for us.

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  ...

And OLTP has nothing to do with the amount of time the user is willing to
wait, it's rather about the amount of data the user read/modifies and the
number of tables. OLTP workload usually consists of transactions accessing
small amount of data, usually by primary key (get customer with id X,
update account with id Y, ...).

OLAP/DSS workload is somehow opposite - read large amounts of data,
perform complex analysis etc.

Tomas


-- 
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] Postgres vs other Postgres based MPP implementations

[Ondrej Ivanič]

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 store and compression  speed up queries even more
(sometimes beyond 100x times) comparing to postgres.

>
> Or is the performance different just in better utilisation of the hardware
> under Greenplum?
>>

Yes, looks like that they can better utilise available hardware.

>> Is there
>> a way to get PG backed IO stats using stock CentOS (5.7) kernel and
>> tools? (I can't change my env easily)
>
> Dunno; check postgresql high performance (book), the manual, etc. Useful
> tools are the pg_stat_ tables, "vmstat", "iostat", "iotop", etc.

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 reference it with
postgres and other system logs

> That said, Pg's codebase isn't exactly trivial :S and trying to get involved
> in major re-engineering like parallelisation isn't going to be practical
> when you're just getting started.

That's what I meant ;)

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.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] Streaming Replication woes

[Fujii Masao]

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  |            Attributes             | Member of
> +---+---
> postgres   | Superuser, Create role, Create DB | {}
> replicator | Replication                       | {}

Did you restore the database from the dump file created by pg_dumpall
in 9.0 instead of 9.1? If yes, that dump file would contain the "ALTER ROLE
postgres" command and revoke the replication privilege, I guess.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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] Postgres vs other Postgres based MPP implementations

[Ondrej Ivanič]

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 large dataset (100m+) but the query should return
result (less than 1000 rows, mostly around 100) under 30 sec

My point is that  MPP (same box used) solution can deliver required
performance in most cases (>75%).

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.com)

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

[GENERAL] IP range in pg_hba.conf?

[Chrishelring]

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 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). 

Best regards,

Christian

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/IP-range-in-pg-hba-conf-tp4973998p4973998.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] IP range in pg_hba.conf?

[Boszormenyi Zoltan]

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 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). 

hostallall10.17.64.0/20md5

>
> Best regards,
>
> Christian
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/IP-range-in-pg-hba-conf-tp4973998p4973998.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>


-- 
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
 http://www.postgresql.at/


-- 
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] IP range in pg_hba.conf?

[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 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).

The range above should be written like this: 10.17.64.0/20

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.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] IP range in pg_hba.conf?

[Zhidong]

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 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).
> 
> The range above should be written like this: 10.17.64.0/20
> 
> -- 
> Ondrej Ivanic
> (ondrej.iva...@gmail.com)
> 
> -- 
> 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] IP range in pg_hba.conf?

[Thom Brown]

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.  Bits
17-20 brings it up to 240.  The rest are zeros.

0.0.0.0 = /0
255.0.0.0 = /8
255.255.0.0 = /16
255.255.255.0 = /24
255.255.255.255 = /32

And inbetween you get:

255.255.240.0 = /20

2552552400
   
First 20 binary digits are masked.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] function within a function/rollbacks/exception handling

[Lori Corbani]

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 place in the documentation this discusses the
implicit rollback in more detail?  Or do you know of a good online site
that contains some good examples or best-practices for these
function-to-function calls?

We are starting to port our Sybase database (200 stored procedures) over
to Postgres and I am finding the online Postgres documentation and the
Douglas book a bit lacking in some of the more specific examples that I
am interested in finding.

Thanks.
Lori



From: Lori Corbani [l...@informatics.jax.org]
Sent: Tuesday, November 08, 2011 8:46 AM
To: Richard Huxton
Cc: Lori Corbani; pgsql-general@postgresql.org
Subject: 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 place in the documentation this discusses the
implicit rollback in more detail?  Or do you know of a good online site
that contains some good examples or best-practices for these
function-to-function calls?

We are starting to port our Sybase database (200 stored procedures) over
to Postgres and I am finding the online Postgres documentation and the
Douglas book a bit lacking in some of the more specific examples that I
am interested in finding.

Thanks.
Lori


Richard Huxton wrote:
> On 07/11/11 19:18, Lori Corbani wrote:
>
>>
>> I have a function, call it 'functionMain'.  And I have several tables
>> that each have trigger functions.  Each trigger function needs to call
>> 'functionMain' (with different parameters).
>>
>> table A =>  trigger function A ==>  functionMain
>> table B =>  trigger function B ==>  functionMain
>> table C =>  trigger function C ==>  functionMain
>>
>> 'functionMain' returns VOID (runs an insert statement). and has an
>> exception/raise exception block.
>>
>> An insert transaction for table A is launched (insertA), trigger
>> function A is called,
>> 'functionMain' is called and 'functionMain' fails.  Hence, trigger
>> function A needs to rollback.
>>
>> Questions:
>>
>> a) I am assuming that the trigger functions should use 'PERFORM
>> functionMain()'?
>
>
> If you don't want the result, yes.
>
>> b) if 'functionMain' fails, then 'funtionMain' automatically performs
>> an implicit rollback, correct?
>>
>> c) if 'functionMain' fails, should the trigger function also contain
>> an exception handler
>> or will the rollback from 'functionMain' cascade up to the
>> original transaction (insertA)?
>
>
> Unless you catch the exception, it will roll back the whole transaction,
> so "yes" to b + c. If it helps to visualise what happens, exceptions are
> actually implemented using savepoints in plpgsql.
>

--

Lori E. Corbani
Scientific Software Engineer
The Jackson Laboratory
600 Main Street
Bar Harbor, ME 04609 USA
(207) 288-6425 (V)
**
lori.corb...@jax.org
http://www.informatics.jax.org
**

-- 
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] function within a function/rollbacks/exception handling

[Adrian Klaver]

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 block's BEGIN are automatically rolled back"
> 
> Do you know of any other place in the documentation this discusses the
> implicit rollback in more detail?  Or do you know of a good online site
> that contains some good examples or best-practices for these
> function-to-function calls?

http://www.postgresql.org/docs/9.0/interactive/plpgsql-control-
structures.html#PLPGSQL-ERROR-TRAPPING

> 
> We are starting to port our Sybase database (200 stored procedures) over
> to Postgres and I am finding the online Postgres documentation and the
> Douglas book a bit lacking in some of the more specific examples that I
> am interested in finding.
> 
> Thanks.
> Lori
> 
> 
>

-- 
Adrian Klaver
adrian.kla...@gmail.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] Masquerading a unique index as a primary key in 8.4?

[Vick Khera]

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 the relevant pg_constraint entry.  Is modifying the
> underlying pg_catalog tables like this "Very Bad"?  Will it have mysterious
> and unintended consequences, or can I get away with it?  Thanks!

The badness I see that will eventually come back to bite you is that
your unique constraint is lacking "NOT NULL" and a PK by definition
has NOT NULL.  Therefore some other parts of the system is permitted
to make that assumption, and when stuff fails because you lied to the
system, you will probably never ever figure out or even know.

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

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

[Rajesh Kumar Mallah]

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 not chooses that
index instead it uses an index on uniq_id ( the sorting column).

Since the number of child table is huge i am only posting an excerpt
of the plan .



 Filter: (profile_id = 3528336)
   ->  Index Scan Backward using profile_log_2011_08_pkey
on profile_log_2011_08 profile_log  (cost=0.00..15815.11 rows=3
width=8)
 Filter: (profile_id = 3528336)
   ->  Index Scan Backward using profile_log_2011_09_pkey
on profile_log_2011_09 profile_log  (cost=0.00..17851.91 rows=76
width=8)

-
the index profile_log_2011_09_pkey  is the index on the column uniq_id

the query is *very slow* , 39 seconds


how ever if we just change  limit 5 to limit 15 then the plan changes

   Index Cond: (profile_id = 3528336)
 ->  Bitmap Heap Scan on profile_log_2011_08
profile_log  (cost=4.31..16.13 rows=3 width=8)
   Recheck Cond: (profile_id = 3528336)
   ->  Bitmap Index Scan on
profile_log_2011_08_profile_id  (cost=0.00..4.31 rows=3 width=0)
 Index Cond: (profile_id = 3528336)
 ->  Bitmap Heap Scan on profile_log_2011_09
profile_log  (cost=4.89..285.93 rows=76 width=8)
   Recheck Cond: (profile_id = 3528336)
   ->  Bitmap Index Scan on
profile_log_2011_09_profile_id  (cost=0.00..4.87 rows=76 width=0)
 Index Cond: (profile_id = 3528336)

 the index on profile_id is being used and the query is very fast ( 50 ms)


I am using the most recent released version of postgresql at this
moment which is 9.1.1


can any one please suggest , I think autovaccum is on.


regds
mallah

-- 
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] Masquerading a unique index as a primary key in 8.4?

[Robert Treat]

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" is only reflected in pg_index and isn't
>> accompanied by the relevant pg_constraint entry.  Is modifying the
>> underlying pg_catalog tables like this "Very Bad"?  Will it have mysterious
>> and unintended consequences, or can I get away with it?  Thanks!
>
> The badness I see that will eventually come back to bite you is that
> your unique constraint is lacking "NOT NULL" and a PK by definition
> has NOT NULL.  Therefore some other parts of the system is permitted
> to make that assumption, and when stuff fails because you lied to the
> system, you will probably never ever figure out or even know.
>

Agreed. I'd be more inclined to change londiste, or just ditch it for
something else that will recognize the unique index as a unique enough
identifier to enable replication. That limitation is kind of lame.

Robert Treat
conjecture: xzilla.net
consulting: omniti.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] Recommendations for SSDs in production?

[Vick Khera]

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 of them for redundancy on two servers replicated using
Slony.  Nobody would ever call these things cheap, but the technology
behind them is nothing short of phenomenal.

-- 
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_restore: [custom archiver] unexpected end of file on Postgres 9.1.1

[Cody Caughlan]

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 recovery
DETAIL:  User query might have needed to see row versions that must be
removed.
pg_dump: The command was: COPY public.external_users (id, user_id,
external_id, type) TO stdout;
pg_dump: *** aborted because of error

The pg_dump is being run from a slave set on hot-standby mode. By looking
around this appears to be a fairly common issue with streaming replication.

I have found references to this manual page:

http://www.postgresql.org/docs/9.0/static/hot-standby.html

In my case "external_users" is a pretty "hot" table, so I think it
satisfies this note: "Users should be clear that tables that are regularly
and heavily updated on the primary server will quickly cause cancellation
of longer running queries on the standby"

In my case I have:

max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s

I dont know if adjusting one of the above parameters would help. From the
docs it sounds that increasing "vacuum_defer_cleanup_age" to some larger
value might also do the trick.

Any guidance would be appreciated.

/Cody

On Mon, Nov 7, 2011 at 4:11 PM, Adrian Klaver wrote:

> On Monday, November 07, 2011 11:27:05 am Cody Caughlan wrote:
> > I am trying to restore a dump created with pg_dump, both source and
> > destination are Postgres 9.1.1 albeit different machines (source is
> Linux,
> > destination is OS X).
> >
> > $ pg_restore -U postgres -Fc -d batch_api_production
> > 200708_batch_api_production.dump.sql
> > pg_restore: [custom archiver] unexpected end of file
> >
> > pg_restore does seem like its doing something, as it doesnt error out
> until
> > 10-12 seconds in.
> >
> > I can dump the TOC just fine with pg_restore -l, which I've collected
> here:
> >
> > https://gist.github.com/951e417e7098fdf987d4
> >
> > If I access the DB it appears that all the tables and sequences exist,
> but
> > none of the data or indexes & constraints.
> >
> > Any help would be appreciated.
>
> What do the database logs show when you do the restore?
>
> >
> > /Cody Caughlan
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>

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

[John R Pierce]

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 pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[John R Pierce]

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 reference it with
postgres and other system logs


nmon in data collection mode, with a suitable sample interval.   
generates a .CSV file that you can pick apart and graph with a 
spreadsheet or whatever.


--
john r pierceN 37, W 122
santa cruz ca mid-left coast



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

[GENERAL] Grouping logs by ip and time

[Alex Thurlow]

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 log and it 
will always start with action1.  It may not have every action though.  I 
also know that each session will take no longer than one minute.


What I'd like to do is be able to group these logs by sessions based on 
the IP and the time range so I can figure out the time taken between 
each action.


I know how to script it, although it's very slow.  I was hoping there 
was some way to do this in SQL.  I'm running Postgresql 8.3.7 on this 
machine right now, but if there's something that would make this easier 
and doesn't exist there yet, I would be willing to upgrade.


Thanks,
Alex

--
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] Grouping logs by ip and time

[Raymond O'Donnell]

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 on
> the IP and the time range so I can figure out the time taken between
> each action.

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 timestamp column
instead of separate date and time.

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] Grouping logs by ip and time

[Raymond O'Donnell]

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 like to do is be able to group these logs by sessions based on
>> the IP and the time range so I can figure out the time taken between
>> each action.
> 
> 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 timestamp column
> instead of separate date and time.

Whoops - just re-read your email, and realised that you're looking for
the time between actions; the above just gives you the total duration of
the session from each IP address.

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] Grouping logs by ip and time

[John R Pierce]

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 timestamp column
instead of separate date and time.


he said a session always starts with 'action1', and presumably there can 
be more than one session per day, so this won't work.  the 'end' of a 
session is presumably the previous action2|3|4|5 thats prior to the next 
action1. I have no idea how you'd code this as a SQL query.






--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
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] Grouping logs by ip and time

[Ascarabina]

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 action on other day.
you will have a wrong results.


You should save also the session id and group by sesion id not ip.
Ex. :
Table
--
log_type text,
date date,
"time" time without time zone,
ip inet session_id text -- you can use maybe foreign tables ?

SQL  ( Same as Raynold's but groups session ids)
---
 select ip, max("time") - min("time") as session_duration
 from log_table
 group by session_id;

--
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] Grouping logs by ip and time

[Alex Thurlow]

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
- client has sime ip but he made another action on other day.
you will have a wrong results.


You should save also the session id and group by sesion id not ip.
Ex. :
Table
--
log_type text,
date date,
"time" time without time zone,
ip inet session_id text -- you can use maybe foreign tables ?

SQL  ( Same as Raynold's but groups session ids)
---
 select ip, max("time") - min("time") as session_duration
 from log_table
 group by session_id;

Thanks for the responses guys.  I guess I'll need to figure out how to 
add a session ID if I'm going to do this.


-Alex

--
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] Replication Across Two Servers?

[Martín Marqués]

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.postgresql.org/wiki/Streaming_Replication
>
> Oh great! I didn't see that in the 8.4 manual since that is what
> Debian 6 has as the most stable version in it's package manager.
> Anyone know of a stable Linux distribution that offers 9.0+? I know
> Debian Wheezy (testing) has 9.1 but sadly it's testing and not
> recommended for production utilization. RHEL is years behind as far as
> packages go which makes them stable to an annoying degree.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

-- 
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] function within a function/rollbacks/exception handling

[Merlin Moncure]

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 BEGIN are automatically rolled back"
>
> Do you know of any other place in the documentation this discusses the
> implicit rollback in more detail?  Or do you know of a good online site
> that contains some good examples or best-practices for these
> function-to-function calls?
>
> We are starting to port our Sybase database (200 stored procedures) over
> to Postgres and I am finding the online Postgres documentation and the
> Douglas book a bit lacking in some of the more specific examples that I
> am interested in finding.
>
> Thanks.
> Lori
>
>
> 
> From: Lori Corbani [l...@informatics.jax.org]
> Sent: Tuesday, November 08, 2011 8:46 AM
> To: Richard Huxton
> Cc: Lori Corbani; pgsql-general@postgresql.org
> Subject: 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 place in the documentation this discusses the
> implicit rollback in more detail?  Or do you know of a good online site
> that contains some good examples or best-practices for these
> function-to-function calls?
>
> We are starting to port our Sybase database (200 stored procedures) over
> to Postgres and I am finding the online Postgres documentation and the
> Douglas book a bit lacking in some of the more specific examples that I
> am interested in finding.

Implicit rollback is a fundamental underpinning of transactions in
SQL.  Any error will abort either A. the entire transaction or B. all
activity since the last savepoint.

In all languages except plpgsql savepoints are explicitly set (with
SAVEPOINT command) and you restore to the savepoint with ROLLBACK TO.

Savepoints in plpgsql are implicitly created anytime you enter a
BEGIN/END block with an EXCEPTION handler(s) defined.  Unlike vanilla
SQL savepoints, plpgsql savepoints can nest so that each EXCEPTION
block you enter is adding a error handler onto a stack (which is much
more similar to exceptions in the vein of java or C++).  Whether you
call functions from within functions or not is totally immaterial to
error handling generally; you can have multiple nested handlers in a
single function, or none at all.

merlin

-- 
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] Postgres vs other Postgres based MPP implementations

[Ondrej Ivanič]

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
scalability issues (not card but PG and card); bigger one can't fit
our server due to thermal restrictions. Anyway, both cards just do
scale. Geenplum DCA is on site already.

What I see is that single node Greenplum can utilise available
resources more efficiently...

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.com)

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

[GENERAL] Getting Error On pg_dump

[Prashant Bharucha]

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

[Craig Ringer]

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 a damaged table.

Shut your database down and make a copy of the entire data directory 
(including pg_xlog, pg_clog, and everything else in that folder) to a 
different location. Make that copy read-only.


Most corruption issues are hardware related. Check your hard disks, RAID 
controller, etc. Check your file systems. Check your system logs for 
disk errors. Make sure you are NOT running with "fsync=off" in the 
postgresql configuration, because if you are and you EVER had a crash 
that's why you have the corruption. Check your CPU temperatures and if 
you can run a memory test.


As for recovery: You can use the zero_damaged_pages option on a **COPY** 
of your database to attempt a dump. This is likely to produce a dump 
with some data missing or damaged, but the dump will finish.


Alternately, you can track down which table that toast table is 
associated with using the pg_catalog tables and have a look at the main 
table, see if you can identify and DELETE or UPDATE the damaged row(s).


--
Craig Ringer

--
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] Postgres vs other Postgres based MPP implementations

[Craig Ringer]

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  speed up queries even more
(sometimes beyond 100x times) comparing to postgres.


Yup, that'll happen. Right tool, meet right job.

PostgreSQL isn't a column-oriented DB optimised for OLAP workloads. One 
that is will outperform it in OLAP workloads. This is not surprising. If 
a free compressed column store implementation becomes available for 
PostgreSQL I'm sure that'll change, but 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.


A non-ACID-compliant database will outperform PostgreSQL in transaction 
processing (OLTP) workloads, too; if you don't need ACID compliance then 
PostgreSQL won't be the best choice there either. You won't beat a 
document database for fetching and retrieving free-form records for 
similar reasons. Right tool, right job.


That's not to say Pg can't improve. It can, and not just by adding 
column store or index-structured table support. Improved parallelism 
capabilities are needed in Pg, but like column store support are a LOT 
of HARD work and not something anyone can whip up overnight. Especially 
someone doing it for fun in their spare time who we have no right to 
make any demands of, or someone working on the project for a company 
that has its customers' priorities to think of first.



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 reference it with
postgres and other system logs


I don't do "enterprise" distributions, so I can't help there. Ask their 
support maybe, presumably you pay for it for a reason.


--
Craig Ringer

--
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] Postgres vs other Postgres based MPP implementations

[Ondrej Ivanič]

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 --
lot's of work is involved there!. Anyway, all implementation what I
came across took several shortcuts like no updates(append only) or no
foreign keys, ... but it works!

> That's not to say Pg can't improve. It can, and not just by adding column
> store or index-structured table support. Improved parallelism capabilities
> are needed in Pg

I see most benefits coming from parallelism: 12hr query can finish in
2hr if sliced properly


-- 
Ondrej Ivanic
(ondrej.iva...@gmail.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] Grouping logs by ip and time

[Thomas Markus]

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 ip and date and selects entries 
with there successors. In older versions this is not so easy. It should 
work like this:

create temp sequence s;
create temp table a as select a.*, nextval('s') as rank from ( select 
a.* from log_table a order by a.ip, a.date, a.time) a;
select a.*, b.*, b.time-a.time from a a join a b on a.ip=b.ip and 
a.date=b.date and a.rank+1=b.rank;


Thomas

Am 08.11.2011 18:59, schrieb Alex Thurlow:

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 log and it 
will always start with action1.  It may not have every action though.  
I also know that each session will take no longer than one minute.


What I'd like to do is be able to group these logs by sessions based 
on the IP and the time range so I can figure out the time taken 
between each action.


I know how to script it, although it's very slow.  I was hoping there 
was some way to do this in SQL.  I'm running Postgresql 8.3.7 on this 
machine right now, but if there's something that would make this 
easier and doesn't exist there yet, I would be willing to upgrade.


Thanks,
Alex




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