Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Pavel Stehule
2010/4/12 Robert Haas robertmh...@gmail.com: On Sun, Apr 11, 2010 at 5:24 AM, Greg Smith g...@2ndquadrant.com wrote: From the rest of your comments, I'm comfortable that you're in sync with the not necessarily obvious risky spots here I wanted to raise awareness of.  It's unreasonable to

Re: [HACKERS] testing hot standby

2010-04-12 Thread Fujii Masao
On Sat, Apr 10, 2010 at 5:39 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: i'm startint to try Hot Standby Streaming Replication, so i started a replication: Great! but, my main concern is why it was asking for 00010006? is this normal? The standby server tries to

Re: [HACKERS] testing hot standby

2010-04-12 Thread Jaime Casanova
On Fri, Apr 9, 2010 at 3:39 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: but, my main concern is why it was asking for 00010006? is this normal? is this standby's way of saying i'm working but i have nothing to do? when that happens after a standby restart, is normal

Re: [HACKERS] testing hot standby

2010-04-12 Thread Jaime Casanova
On Mon, Apr 12, 2010 at 1:21 AM, Fujii Masao masao.fu...@gmail.com wrote: Didn't the standby accept connections before executing pgbench? nop, and last time i try it was in that state for an hour (without accepting connections)... after that i execute on the primary: CREATE TABLE tt2 AS SELECT

Re: [HACKERS] testing hot standby

2010-04-12 Thread Fujii Masao
On Mon, Apr 12, 2010 at 3:29 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Apr 12, 2010 at 1:21 AM, Fujii Masao masao.fu...@gmail.com wrote: Didn't the standby accept connections before executing pgbench? nop, and last time i try it was in that state for an hour (without

Re: [HACKERS] pg_ctl stop -m immediate on the primary server inflates sequences

2010-04-12 Thread Boszormenyi Zoltan
Martijn van Oosterhout írta: On Sat, Apr 10, 2010 at 02:36:41PM +0200, Boszormenyi Zoltan wrote: The above is quite reproducable, pg_ctl stop -m immediate usually inflated my serial sequence, but I had two occasions when not. The 69 - 70 was one. The inflated increase is always 33:

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Fujii Masao
On Sat, Apr 10, 2010 at 8:23 AM, Erik Rijkers e...@xs4all.nl wrote: I understand that in the scale=1000 case, there is a huge cache effect, but why doesn't that apply to the pgbench runs against the standby?  (and for the scale=10_000 case the differences are still rather large) I guess that

Re: [HACKERS] Streaming replication and a disk full in primary

2010-04-12 Thread Heikki Linnakangas
Fujii Masao wrote: doc/src/sgml/config.sgml -archival or to recover from a checkpoint. If standby_keep_segments +archival or to recover from a checkpoint. If varnamestandby_keep_segments/ The word standby_keep_segments always needs the varname tag, I think. Thanks, fixed.

Re: [HACKERS] testing hot standby

2010-04-12 Thread Heikki Linnakangas
Jaime Casanova wrote: On Fri, Apr 9, 2010 at 3:39 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: but, my main concern is why it was asking for 00010006? is this normal? is this standby's way of saying i'm working but i have nothing to do? Yes. when that happens

Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Robert Haas
On Mon, Apr 12, 2010 at 2:16 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I am sure so dynamical materialised views is bad task for GSoC - it is too large, too complex. Manually refreshed views is adequate to two months work and it has sense. That is my feeling also - though I fear that

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Robert Haas
On Mon, Apr 12, 2010 at 5:06 AM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Apr 10, 2010 at 8:23 AM, Erik Rijkers e...@xs4all.nl wrote: I understand that in the scale=1000 case, there is a huge cache effect, but why doesn't that apply to the pgbench runs against the standby?  (and for

Re: [HACKERS] walreceiver is uninterruptible on win32

2010-04-12 Thread Fujii Masao
On Thu, Apr 8, 2010 at 5:01 PM, Fujii Masao masao.fu...@gmail.com wrote: If it does, there should be some way to get PGXS to execute that rule as well, I'm sure. If we can copy/link the source file defining new PQexec when we compile the dblink, DLL doesn't seem to be required. So I stop

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Erik Rijkers
On Sat, April 10, 2010 01:23, Erik Rijkers wrote: Using 9.0devel cvs HEAD, 2010.04.08. I am trying to understand the performance difference between primary and standby under a standard pgbench read-only test. server has 32 GB, 2 quadcores. primary: tps = 34606.747930 (including

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Jim Mlodgenski
On Mon, Apr 12, 2010 at 7:07 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Apr 12, 2010 at 5:06 AM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Apr 10, 2010 at 8:23 AM, Erik Rijkers e...@xs4all.nl wrote: I understand that in the scale=1000 case, there is a huge cache effect, but

Re: [HACKERS] Streaming replication and a disk full in primary

2010-04-12 Thread Fujii Masao
On Mon, Apr 12, 2010 at 7:41 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: We should remove the document 25.2.5.2. Monitoring? I updated it to no longer claim that the primary can run out of disk space because of a hung WAL sender. The information about calculating the lag

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Erik Rijkers
On Mon, April 12, 2010 14:22, Erik Rijkers wrote: On Sat, April 10, 2010 01:23, Erik Rijkers wrote: Oops, typos in that pseudo loop: of course there was a pgbench init step after that first line. for scale in 10 100 500 1000 pgbench ... # initialise sleep ((scale / 10) * 60)

Re: [HACKERS] Streaming replication and a disk full in primary

2010-04-12 Thread Robert Haas
On Mon, Apr 12, 2010 at 6:41 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Why is standby_keep_segments used even if max_wal_senders is zero? In that case, ISTM we don't need to keep any WAL files in pg_xlog for the standby. True. I don't think we should second guess the

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Robert Haas
On Mon, Apr 12, 2010 at 8:32 AM, Jim Mlodgenski jimm...@gmail.com wrote: I think we need to investigate this more.  It's not going to look good for the project if people find that a hot standby server runs two orders of magnitude slower than the primary. As a data point, I did a read only

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Aidan Van Dyk
* Robert Haas robertmh...@gmail.com [100412 07:10]: I think we need to investigate this more. It's not going to look good for the project if people find that a hot standby server runs two orders of magnitude slower than the primary. Yes, it's not good, but it's a known problem. We've had

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Aidan Van Dyk
And I see now that he's doing a stream of read-only queries on a slave, presumably with no WAL even being replayed... Sorry for the noise a. * Aidan Van Dyk ai...@highrise.ca [100412 09:40]: * Robert Haas robertmh...@gmail.com [100412 07:10]: I think we need to investigate this more.

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Kevin Grittner
Aidan Van Dyk ai...@highrise.ca wrote: We've had people complaining that wal-replay can't keep up with a wal stream from a heavy server. I thought this thread was about the slow performance running a mix of read-only queries on the slave versus the master, which doesn't seem to have anything

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Erik Rijkers
resending this message, as it seems to have bounced. (below, I did fix the typo in the pseudocode loop) Original Message Subject: Re: [HACKERS] testing HS/SR - 1 vs 2 performance From:Erik Rijkers

Re: [HACKERS] testing hot standby

2010-04-12 Thread Jaime Casanova
On Mon, Apr 12, 2010 at 1:48 AM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Apr 12, 2010 at 3:29 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Apr 12, 2010 at 1:21 AM, Fujii Masao masao.fu...@gmail.com wrote: Didn't the standby accept connections before executing

Re: [HACKERS] explain and PARAM_EXEC

2010-04-12 Thread Yeb Havinga
The patch I sent earlier is flaud with respect to subplan parameter numbering, I counted from zero where the parParam list had to be used. Yeb Havinga wrote: See patch below against HEAD. Example of query against catalog: postgres=# explain verbose select oid::int + 1,(select oid from

Re: [HACKERS] walreceiver is uninterruptible on win32

2010-04-12 Thread Joseph Conway
Fujii Masao wrote: If adding new shared library is too big change at this point, I think that we should postpone the fix only for dblink to 9.1 or later. Since no one has complained about this long-term problem of dblink, I'm not sure it really should be fixed right now. Thought? I would

[HACKERS] non-reproducible failure of random test on HEAD

2010-04-12 Thread Kevin Grittner
I just did a checkout from HEAD (a few minutes ago) and ran this: make distclean ; ./configure --prefix=/usr/local/pgsql-serializable --enable-integer-datetimes --enable-debug --enable-cassert --enable-depend --with-libxml make check I got a failure on the random test. Unfortunately I didn't

Re: [HACKERS] testing hot standby

2010-04-12 Thread Jaime Casanova
On Mon, Apr 12, 2010 at 9:27 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Apr 12, 2010 at 1:48 AM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Apr 12, 2010 at 3:29 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Apr 12, 2010 at 1:21 AM, Fujii Masao

Re: [HACKERS] walreceiver is uninterruptible on win32

2010-04-12 Thread Magnus Hagander
On Mon, Apr 12, 2010 at 13:54, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Apr 8, 2010 at 5:01 PM, Fujii Masao masao.fu...@gmail.com wrote: If it does, there should be some way to get PGXS to execute that rule as well, I'm sure. If we can copy/link the source file defining new PQexec

Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Josh Berkus
On 4/9/10 1:36 PM, pavelbaros wrote: 2) change rewriter - usually, view is relation with defined rule and when rewriting, rule is fired and relation (view) is replaced by definition of view. If relation do not have rule, planner and executor behave to it as physical table (relation). In case

Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Robert Haas
On Mon, Apr 12, 2010 at 1:50 PM, Josh Berkus j...@agliodbs.com wrote: On 4/9/10 1:36 PM, pavelbaros wrote: 2) change rewriter - usually, view is relation with defined rule and when rewriting, rule is fired and relation (view) is replaced by definition of view. If relation do not have rule,

Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Greg Smith
Josh Berkus wrote: There are basically 2 major parts for materialized views: A) Planner: Getting the query planner to swap in the MatView for part of a query automatically for query plan portions which the MatView supports; B) Maintenance: maintaining the MatView data according to the programmed

Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Robert Haas
On Mon, Apr 12, 2010 at 3:43 PM, Greg Smith g...@2ndquadrant.com wrote: Josh Berkus wrote: There are basically 2 major parts for materialized views: A) Planner: Getting the query planner to swap in the MatView for part of a query automatically for query plan portions which the MatView

Re: [HACKERS] Virtual Private Database

2010-04-12 Thread Josh Berkus
On 4/10/10 7:00 AM, Jean-Gérard Pailloncy wrote: Hello, 1) VPD: Virtual Private Database I would appreciate to have a new feature in PostgreSQL. This is an oracle-like feature that implement Row Level Security. This feature may be emulated by using VIEW/RULE but this is very time consuming

Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Josh Berkus
Greg, I'm not saying someone can't jump right into (3), using the current implementations for (1) and (2) that are floating around out there. I just think it would end up wasting a fair amount of work on prototypes that don't work quite the same way as the eventual fully integrated version.

Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Greg Smith
Josh Berkus wrote: What would be the use case for (1) by itself? There isn't any use case for just working on the infrastructure, just like there's no use case for Syntax for partitioning on its own. That why people rarely work on that part of these problems--it's boring and produces no

Re: [HACKERS] ECPG check variables hidden by locals v2

2010-04-12 Thread Bruce Momjian
FYI, I think Michael Meskes applied this patch, though I didn't see you emailed that it was applied. --- Boszormenyi Zoltan wrote: Hi, here's a little beautified patch: - more logical parameter order in

Re: [HACKERS] walreceiver is uninterruptible on win32

2010-04-12 Thread Fujii Masao
On Tue, Apr 13, 2010 at 1:56 AM, Magnus Hagander mag...@hagander.net wrote: If adding new shared library is too big change at this point, I think that we should postpone the fix only for dblink to 9.1 or later. Since no one has complained about this long-term problem of dblink, I'm not sure it

[HACKERS] Naming of new EXCLUDE constraints

2010-04-12 Thread Bruce Momjian
Simon Riggs wrote: On Sun, 2010-04-04 at 22:12 -0400, Bruce Momjian wrote: Simon Riggs wrote: How about we call it exclusivity constraints. Not much of a change, but helps to differentiate. Well, the keyword is EXCLUDE so we could call it EXCLUDE contraints. If that is the

Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Josh Berkus
I don't want to see Materialized Views wander down the same path as partitioning, where lots of people produce fun parts patches, while ignoring the grunt work of things like production quality catalog support for the feature. I think Pavel's proposal got that part right by starting with

Re: [HACKERS] testing hot standby

2010-04-12 Thread Fujii Masao
On Mon, Apr 12, 2010 at 11:27 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: 1. start the primary 2. pg_start_backup() 3. copy $PGDATA from the primary to the standby 4. pg_stop_backup(); 5. create the recovery.conf and start the standby execute some WAL-logged action (i've seen

Re: [HACKERS] Naming of new EXCLUDE constraints

2010-04-12 Thread Robert Haas
On Mon, Apr 12, 2010 at 9:28 PM, Bruce Momjian br...@momjian.us wrote: Simon Riggs wrote: On Sun, 2010-04-04 at 22:12 -0400, Bruce Momjian wrote: Simon Riggs wrote: How about we call it exclusivity constraints. Not much of a change, but helps to differentiate. Well, the keyword

Re: [HACKERS] Naming of new EXCLUDE constraints

2010-04-12 Thread Bruce Momjian
Robert Haas wrote: On Mon, Apr 12, 2010 at 9:28 PM, Bruce Momjian br...@momjian.us wrote: Simon Riggs wrote: On Sun, 2010-04-04 at 22:12 -0400, Bruce Momjian wrote: Simon Riggs wrote: How about we call it exclusivity constraints. Not much of a change, but helps to

Re: [HACKERS] Naming of new EXCLUDE constraints

2010-04-12 Thread Robert Haas
On Mon, Apr 12, 2010 at 11:03 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Mon, Apr 12, 2010 at 9:28 PM, Bruce Momjian br...@momjian.us wrote: Simon Riggs wrote: On Sun, 2010-04-04 at 22:12 -0400, Bruce Momjian wrote: Simon Riggs wrote: How about we call it

[HACKERS] debugger question

2010-04-12 Thread Murali M. Krishna
Hello: I am brand new to Postgresql. I ran the following commands. ./configure gmake su gmake install adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postmaster -D

Re: [HACKERS] debugger question

2010-04-12 Thread tomas
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, Apr 12, 2010 at 08:31:38PM -0700, Murali M. Krishna wrote: Hello: I am brand new to Postgresql. I ran the following commands. ./configure gmake su gmake install adduser postgres mkdir /usr/local/pgsql/data chown postgres

Re: [HACKERS] ECPG check variables hidden by locals v2

2010-04-12 Thread Boszormenyi Zoltan
Yes, he applied the first version without seeing this one, then he asked for a re-diff privately. Bruce Momjian írta: FYI, I think Michael Meskes applied this patch, though I didn't see you emailed that it was applied.

Re: [HACKERS] GSoC - proposal - Materialized Views in PostgreSQL

2010-04-12 Thread Greg Smith
Josh Berkus wrote: I just worry about any feature which doesn't get as far as a user-visible implementation. If someone doesn't do the rest of the parts soon, such features tend to atrophy because nobody is using them. While they're limited, there are complexly viable prototype quality

Re: [HACKERS] debugger question

2010-04-12 Thread Murali M. Krishna
The OS is Fedora 12. - Please visit NumberFest.com for educational number puzzles mind exercises for all ages! And please tell your friends about it. Thank You! --- On Mon, 4/12/10, to...@tuxteam.de to...@tuxteam.de wrote:

Re: [HACKERS] testing HS/SR - 1 vs 2 performance

2010-04-12 Thread Heikki Linnakangas
I could reproduce this on my laptop, standby is about 20% slower. I ran oprofile, and what stands out as the difference between the master and standby is that on standby about 20% of the CPU time is spent in hash_seq_search(). The callpath is GetSnapshotDat() - KnownAssignedXidsGetAndSetXmin() -