date:20120404

Re: [HACKERS] Switching to Homebrew as recommended Mac install?

2012-04-04 Thread Dave Page

On Tue, Apr 3, 2012 at 11:12 PM, Greg Stark st...@mit.edu wrote:
 On Wed, Apr 4, 2012 at 1:19 AM, Dave Page dp...@pgadmin.org wrote:
 then, we're talking about making parts of the filesystem
 world-writeable so it doesn't even matter if the user is running as an
 admin for a trojan or some other nasty to attack the system.

 The argument is that a trojan or other nasty doesn't *need* to be
 admin to attack the system since it can just attack the user's account
 since that's where all the interesting data is anyways.

Isn't that what I said?

 But again, this is all beside the point. It's a judgement for Apple
 and Microsoft and individual administrators to make. We can't really
 start reconfiguring people's systems to use a different security model
 than they expect just because they've installed a database software --
 even if we think our security model makes more sense than the one
 their used to.

Exactly - which is why I was objecting to recommending a distribution
of PostgreSQL that came in a packaging system that we were told
changed /usr/local to be world writeable to avoid the use/annoyance of
the standard security measures on the platform.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

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

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

Re: [HACKERS] parallel pg_dump

2012-04-04 Thread Joachim Wieland

On Tue, Apr 3, 2012 at 9:26 AM, Andrew Dunstan and...@dunslane.net wrote:
 First, either the creation of the destination directory needs to be delayed
 until all the sanity checks have passed and we're sure we're actually going
 to write something there, or it needs to be removed if we error exit before
 anything gets written there.

pg_dump also creates empty files which is the analogous case here.
Just try to dump a nonexistant database for example (this also shows
that delaying won't help...).

 Maybe pg_dump -F d should be prepared to accept an empty directory as well as 
 a
 non-existent directory, just as initdb can.

That sounds like a good compromise. I'll implement that.


 Second, all the PrintStatus traces are annoying and need to be removed, or
 perhaps better only output in debugging mode (using ahlog() instead of just
 printf())

Sure, PrintStatus is just there for now to see what's going on. My
plan was to remove it entirely in the final patch.


Joachim

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

[HACKERS] bugfix for cursor arguments in named notation

2012-04-04 Thread Yeb Havinga

Using a cursor argument name equal to another plpgsql variable results 
in the error:

cursor .. has no argument named 

The attached patch fixes that.

Instead of solving the issue like is done in the patch, another way 
would be to expose internal_yylex() so that could be used instead of 
yylex() by read_cursor_args when reading the argument name, and would 
always return the argument name in yylval.str.


--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data

From 47c451cbf188ac2aff9784bff73bc7fb7b846d26 Mon Sep 17 00:00:00 2001
From: Willem  Yeb w...@mgrid.net
Date: Wed, 4 Apr 2012 11:30:41 +0200
Subject: [PATCH] Fix cursor has no argument named  error.

When a cursor argument name coincided with another plpgsql variable name,
yylex() returns it not as str but as a wdatum.
---
 src/pl/plpgsql/src/gram.y |6 --
 src/test/regress/expected/plpgsql.out |   18 ++
 src/test/regress/sql/plpgsql.sql  |   15 +++
 3 files changed, 37 insertions(+), 2 deletions(-)

diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index 5a555af..2d52278 100644
--- a/src/pl/plpgsql/src/gram.y
+++ b/src/pl/plpgsql/src/gram.y
@@ -3447,8 +3447,10 @@ read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected)
 			char   *argname;
 
 			/* Read the argument name, and find its position */
-			yylex();
-			argname = yylval.str;
+			if (yylex() == T_DATUM)
+argname = yylval.wdatum.ident;
+			else
+argname = yylval.str;
 
 			for (argpos = 0; argpos  row-nfields; argpos++)
 			{
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 5455ade..56cfa57 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2420,6 +2420,24 @@ select namedparmcursor_test8();
  0
 (1 row)
 
+-- cursor parameter named the same as other plpgsql variables
+create or replace function namedparmcursor_test9(p1 int) returns int4 as $$
+declare
+  c1 cursor (p1 int, p2 int) for
+select count(*) from tenk1 where thousand = p1 and tenthous = p2;
+  n int4;
+  p2 int4 := 1006;
+begin
+  open c1 (p1 := p1,  p2 := p2);
+  fetch c1 into n;
+  return n;
+end $$ language plpgsql;
+select namedparmcursor_test9(6);
+ namedparmcursor_test9 
+---
+ 1
+(1 row)
+
 --
 -- tests for raise processing
 --
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index f577dc3..6b9795d 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2053,6 +2053,21 @@ begin
 end $$ language plpgsql;
 select namedparmcursor_test8();
 
+-- cursor parameter named the same as other plpgsql variables
+create or replace function namedparmcursor_test9(p1 int) returns int4 as $$
+declare
+  c1 cursor (p1 int, p2 int) for
+select count(*) from tenk1 where thousand = p1 and tenthous = p2;
+  n int4;
+  p2 int4 := 1006;
+begin
+  open c1 (p1 := p1,  p2 := p2);
+  fetch c1 into n;
+  return n;
+end $$ language plpgsql;
+
+select namedparmcursor_test9(6);
+
 --
 -- tests for raise processing
 --
-- 
1.7.1


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

Re: [HACKERS] patch for parallel pg_dump

2012-04-04 Thread Joachim Wieland

On Tue, Apr 3, 2012 at 11:04 AM, Robert Haas robertmh...@gmail.com wrote:
 OK, but it seems like a pretty fragile assumption that none of the
 workers will ever manage to emit any other error messages.  We don't
 rely on this kind of assumption in the backend, which is a lot
 better-structured and less spaghetti-like than the pg_dump code.

Yeah, but even if they don't use exit_horribly(), the user would still
see the output, stdout/stderr aren't closed and everyone can still
write to it.

As a test, I printed out some messages upon seeing a specific dump id
in a worker:

if (strcmp(command, DUMP 3540) == 0)
{
write_msg(NULL, Info 1\n);
printf(Info 2\n);
exit_horribly(modulename, that's why\n);
}


$ ./pg_dump -j 7 ...
pg_dump: Info 1
Info 2
pg_dump: [parallel archiver] that's why


if (strcmp(command, DUMP 3540) == 0)
{
write_msg(NULL, Info 1\n);
printf(Info 2\n);
fprintf(stderr, exiting on my own\n);
exit(1);
}


$ ./pg_dump -j 7 ...
pg_dump: Info 1
Info 2
exiting on my own
pg_dump: [parallel archiver] A worker process died unexpectedly

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

[HACKERS] patch: improve SLRU replacement algorithm

2012-04-04 Thread Robert Haas

On Mon, Apr 2, 2012 at 12:33 PM, Robert Haas robertmh...@gmail.com wrote:
 This particular example shows the above chunk of code taking 13s to
 execute.  Within 3s, every other backend piles up behind that, leading
 to the database getting no work at all done for a good ten seconds.

 My guess is that what's happening here is that one backend needs to
 read a page into CLOG, so it calls SlruSelectLRUPage to evict the
 oldest SLRU page, which is dirty.  For some reason, that I/O takes a
 long time.  Then, one by one, other backends comes along and also need
 to read various SLRU pages, but the oldest SLRU page hasn't changed,
 so SlruSelectLRUPage keeps returning the exact same page that it
 returned before, and everybody queues up waiting for that I/O, even
 though there might be other buffers available that aren't even dirty.

 I am thinking that SlruSelectLRUPage() should probably do
 SlruRecentlyUsed() on the selected buffer before calling
 SlruInternalWritePage, so that the next backend that comes along
 looking for a buffer doesn't pick the same one.  Possibly we should go
 further and try to avoid replacing dirty buffers in the first place,
 but sometimes there may be no choice, so doing SlruRecentlyUsed() is
 still a good idea.

 I'll do some testing to try to confirm whether this theory is correct
 and whether the above fix helps.

Having performed this investigation, I've discovered a couple of
interesting things.  First, SlruRecentlyUsed() is an ineffective way
of keeping a page from getting reused, because it's called extremely
frequently, and on these high-velocity tests it takes almost no time
at all for the most recently used buffer to become the least recently
used buffer.  Therefore, SlruRecentlyUsed() doesn't prevent the lock
pile-up.  In the unpatched code, once a long buffer I/O starts,
everybody immediately goes into the tank until the I/O finishes.  If
you patch the code so that the page is marked recently-used before
beginning the I/O, everybody's next few CLOG requests hit some other
buffer but eventually the long-I/O-in-progress buffer again becomes
least recently used and the next CLOG eviction causes a second backend
to begin waiting for that buffer.  Lather, rinse, repeat, until
literally every backend is once again waiting on that buffer I/O.  You
still get the same problem; it just takes slightly longer to develop.

On reflection, it seems to me that the right fix here is to make
SlruSelectLRUPage() to avoid selecting a page on which an I/O is
already in progress.  In general, those I/Os are all writes.  We don't
end up waiting for reads because all the old CLOG pages we might want
to read are still in the OS cache.  So reads complete quickly, on this
test.  Writes take a long time, because there we have to actually get
the data down to disk, and the disk is busy.  But there's no reason
for a backend doing a replacement to wait for either a read or a write
that is in progress: once the read or write completes, we're going to
loop around and repeat the buffer selection process, and most likely
pick a buffer completely unrelated to the one whose I/O we waited for.
 We might as well just skip the wait and select that other buffer
immediately.  The attached patch implements that.

Applying this patch does in fact eliminate the stalls.  Here are the
top ten places where blocking happens without the patch - these are
counts of times we waited more than 100ms for a lwlock during
30-minute, 32-client pgbench run:

 54 slru.c:311 blocked by slru.c:405
 99 xlog.c:2241 blocked by xlog.c:2090
172 heapam.c:2758 blocked by heapam.c:2758
635 indexam.c:521 blocked by heapam.c:2758
663 xlog.c:2090 blocked by xlog.c:2241
666 varsup.c:65 blocked by varsup.c:65
682 heapam.c:2758 blocked by indexam.c:521
803 xlog.c:1502 blocked by xlog.c:2241
   3002 slru.c:311 blocked by slru.c:529
  23978 xlog.c:909 blocked by xlog.c:909

And with the patch:

 72 hio.c:336 blocked by heapam.c:2758
109 xlog.c:2241 blocked by xlog.c:2090
129 slru.c:311 blocked by slru.c:405
210 heapam.c:2758 blocked by heapam.c:2758
425 heapam.c:2758 blocked by indexam.c:521
710 indexam.c:521 blocked by heapam.c:2758
766 xlog.c:2090 blocked by xlog.c:2241
915 xlog.c:1502 blocked by xlog.c:2241
   1684 varsup.c:65 blocked by varsup.c:65
  27950 xlog.c:909 blocked by xlog.c:909

As you can see, slru.c:311 blocked by slru.c:529 disappears.  It's not
just no longer in the top ten - it's actually completely gone.
Unfortunately, we get more stalls elsewhere as a result, but that's
only to be expected - contention moves around as you fix things.  The
remaining blocking within slru.c is attributable to the line that says
129 slru.c:311 blocked by slru.c:405.  I haven't fully verified
this, but I believe that blocking happens there when somebody needs to
read a page that's already being read - the second guy quite naturally
waits for the first guy's I/O to finish.  Those waits

Re: [HACKERS] parallel pg_dump

2012-04-04 Thread Andrew Dunstan




On 04/04/2012 05:03 AM, Joachim Wieland wrote:

Second, all the PrintStatus traces are annoying and need to be removed, or
perhaps better only output in debugging mode (using ahlog() instead of just
printf())

Sure, PrintStatus is just there for now to see what's going on. My
plan was to remove it entirely in the final patch.





We need that final patch NOW, I think. There is very little time for 
this before it will be too late for 9.2.


cheers

andrew

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

Re: [HACKERS] performance-test farm

2012-04-04 Thread Tomas Vondra

On 4.4.2012 05:35, Greg Smith wrote:
On 03/05/2012 05:20 PM, Tomas Vondra wrote:
What is the current state of this effort? Is there someone else working
on that? If not, I propose this (for starters):

* add a new page Performance results to the menu, with a list of
members that uploaded the perfomance-results

* for each member, there will be a list of tests along with a running
average for each test, last test and indicator if it improved, got
worse or is the same

* for each member/test, a history of runs will be displayed, along
with a simple graph

I am quite certain no one else is working on this.

The results are going to bounce around over time. Last test and
simple computations based on it are not going to be useful. A graph and
a way to drill down into the list of test results is what I had in mind.

Eventually we'll want to be able to flag bad trends for observation,
without having to look at the graph. That's really optional for now,
but here's how you could do that. If you compare a short moving average
to a longer one, you can find out when a general trend line has been
crossed upwards or downwards, even with some deviation to individual
samples. There's a stock trading technique using this property called
the moving average crossover; a good example is shown at
http://eresearch.fidelity.com/backtesting/viewstrategy?category=Trend%20FollowingwealthScriptType=MovingAverageCrossover

Yes, exactly. I've written 'last test' but I actually meant something
like this, i.e. detecting the change of the trend over time. The moving
average crossover looks interesting, although there are other ways to
achieve similar goal (e.g. correlating with a a pattern - a step
function for example, etc.).

It's possible to keep a running weighted moving average without actually
remembering all of the history. The background writer works that way. I
don't think that will be helpful here though, because you need a chunk
of the history to draw a graph of it.

Keeping the history is not a big deal IMHO. And it gives us the freedom
to run a bit more complex analysis anytime later.

I'm not quite sure how to define which members will run the performance
tests - I see two options:

* for each member, add a flag run performance tests so that we can
choose which members are supposed to be safe

* run the tests on all members (if enabled in build-farm.conf) and
then decide which results are relevant based on data describing the
environment (collected when running the tests)

I was thinking of only running this on nodes that have gone out of their
way to enable this, so something more like the first option you gave
here. Some buildfarm animals might cause a problem for their owners
should they suddenly start doing anything new that gobbles up a lot more
resources. It's important that any defaults--including what happens if
you add this feature to the code but don't change the config file--does
not run any performance tests.

Yes, good points. Default should be 'do not run performance test' then.

* it can handle one member running the tests with different settings
(various shared_buffer/work_mem sizes, num of clients etc.) and
various hw configurations (for example magpie contains a regular
SATA drive as well as an SSD - would be nice to run two sets of
tests, one for the spinner, one for the SSD)

* this can handle 'pushing' a list of commits to test (instead of
just testing the HEAD) so that we can ask the members to run the
tests for particular commits in the past (I consider this to be
very handy feature)

I would highly recommend against scope creep in these directions. The
goal here is not to test hardware or configuration changes. You've been
doing a lot of that recently, and this chunk of software is not going to
be a good way to automate such tests.

The initial goal of the performance farm is to find unexpected
regressions in the performance of the database code, running some simple
tests. It should handle the opposite too, proving improvements work out
as expected on multiple systems. The buildfarm structure is suitable
for that job.

Testing hardware configuration changes was not the goal of the proposed
behavior. The goal was to test multiple (sane) PostgreSQL configs. There
are conditions that might demonstrate themselves only in certain
conditions (e.g. very small/large shared buffers, spinners/SSDs etc.).

Those are exacly the 'unexpected regressions' you've mentioned.

If you want to simulate a more complicated test, one where things like
work_mem matter, the first step there is to pick a completely different
benchmark workload. You're not going to do it with simple pgbench calls.

Yes, but I do expect to prepare custom pgbench scripts in the future to
test such things. So I want to design the code so that this is possible
(either

[HACKERS] Question regarding SSL code in backend and frontend

2012-04-04 Thread Tatsuo Ishii

Hi,

While looking into SSL code in secure_read() of be-secure.c and
pqsecure_read() of fe-secure.c, I noticed subtle difference between
them. 

In secure_read:
--
case SSL_ERROR_WANT_READ:
case SSL_ERROR_WANT_WRITE:
if (port-noblock)
{
errno = EWOULDBLOCK;
n = -1;
break;
}
#ifdef WIN32

pgwin32_waitforsinglesocket(SSL_get_fd(port-ssl),

(err == SSL_ERROR_WANT_READ) ?
FD_READ 
| FD_CLOSE : FD_WRITE | FD_CLOSE,

INFINITE);
#endif
goto rloop;
--

while in pqsecure_read:
--
case SSL_ERROR_WANT_READ:
n = 0;
break;
case SSL_ERROR_WANT_WRITE:

/*
 * Returning 0 here would cause caller to wait 
for read-ready,
 * which is not correct since what SSL wants is 
wait for
 * write-ready.  The former could get us stuck 
in an infinite
 * wait, so don't risk it; busy-loop instead.
 */
goto rloop;
--

Those code fragment judges the return value from
SSL_read(). secure_read() does retrying when SSL_ERROR_WANT_READ *and*
SSL_ERROR_WANT_WRITE returned. However, pqsecure_read() does not retry
when SSL_ERROR_WANT_READ. It seems they are not consistent. Comments?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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

Re: [HACKERS] HTTP Frontend? (and a brief thought on materialized views)

2012-04-04 Thread Dobes Vandermeer

On Wed, Apr 4, 2012 at 6:26 AM, Josh Berkus j...@agliodbs.com wrote:


  While I was doing this I always thought this would have been a better
  approach for my previous project, an accounting application.  If I could
  just have stored entities like invoice  customer as a single document
 that
  is inserted, updated, etc. atomically it would be a lot simpler and
 faster
  than having to break things out into columns and rows spread over various
  tables.

 Actually, an accounting application is the *worst* candidate for
 document-oriented storage.


I guess I didn't go enough into detail.  As it's a small business
bookkeeping system the records are added after the fact.  Constraint
checking isn't a priority; we would allow someone to enter sales before
purchases and things like that which means the constraint checking has to
be more about flagging issues (we didn't get around to that yet, either).
 It wasn't an ERP and didn't support inventory so there's no worry about
searching for inventory counts in particular locations.  The idea is to
input source documents like invoices  receipts and generate reports for
stakeholders.

I think there is something to be gained by having a first-class concept of
a document in the database.  It might save some trouble managing
parent/child relations, versioning, things like that.

I hand-craft some materialized views back then, too, since the conversion
from a document (like an invoice) to the actual impact of that on account
ledgers and balances was non-trivial and evolving as the feature set
expanded, so it wasn't something you wanted to try and build into your
reporting queries.

Yes, having documents *in addition* to relational data gives you the
 best of both worlds.  You can use relational structures to store data
 which is well-defined and business-critical, and document structures to
 store data which is undefined and not critical.


Well that's exactly what I was trying to get at in the first place :-).
 I'd love to see this kind of functionality in PostgreSQL and I think
materialized views are a pretty powerful way to do that when you are
automatically pulling fields out of the document to make the relational
tables.


  So I kind of think the document database kind of bridges the gap between
 an
  OODBMS and the RDBMS because the document is like a little cluster of

 OODBMS != DocumentDB


Yes, I know.  I was just saying that a document DB is a bit more OO because
the document itself is stored as an object graph rather than just tuples.
 Thus it fits in between RDBMS and OODBMS in a way.  It makes sense in my
head somehow, no need to agree with me on this one.

Regards,

Dobes

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2012-04-04 Thread Albe Laurenz

Shigeru HANADA wrote:
 During a foreign scan, type input functions are used to convert
 the text representation of values.  If a foreign table is
misconfigured,
 you can get error messages from these functions, like:

 ERROR:  invalid input syntax for type double precision: etwas
 or
 ERROR:  value too long for type character varying(3)

 It might me nice for finding problems if the message were
 something like:

 ERROR:  cannot convert data in foreign scan of tablename, column
col
 in row 42
 DETAIL:  ERROR:  value too long for type character varying(3)
 
 Agreed.  How about showing context information with errcontext() in
 addition to main error message?  Of course, identifiers are quoted if
 necessary.  This way doesn't need additional PG_TRY block, so overhead
 would be relatively cheap.

 postgres=# SELECT * FROM ft1 WHERE c1 = 1;  -- ERROR
 ERROR:  invalid input syntax for integer: 1970-01-02 17:00:00+09
 CONTEXT:  column c4 of foreign table ft1
 
 Showing index of the row seems overkill, because most cause of this
kind
 of error is wrong configuration, as you say, and users would be able
to
 address the issue without knowing which record caused the error.

Agreed.  I think that is a better approach than what I suggested.

 As stated previously, I don't think that using local stats on
 foreign tables is a win.  The other patches work fine for me, and
 I'd be happy if that could go into 9.2.
 
 I have opposite opinion on this issue because we need to do some of
 filtering on local side.  We can leave cost/rows estimation to remote
 side about WHERE expressions which are pushed down, but we need
 selectivity of extra filtering done on local side.  For such purpose,
 having local stats of foreign data seems reasonable and useful.
 
 Of course, it has downside that we need to execute explicit ANALYZE
for
 foreign tables which would cause full sequential scan on remote
tables,
 in addition to ANALYZE for remote tables done on remote side as usual
 maintenance work.

This approach is much better and does not suffer from the
limitations the original analyze patch had.

I think that the price of a remote table scan is something
we should be willing to pay for good local statistics.
And there is always the option not to analyze the foreign
table if you are not willing to pay that price.

Maybe the FDW API could be extended so that foreign data wrappers
can provide a random sample to avoid a full table scan.

 Attached patch contains changes below:
 
 pgsql_fdw_v19.patch
   - show context of data conversion error
   - move codes for fetch_count FDW option to option.c
 (refactoring)
 pgsql_fdw_pushdown_v12.patch
   - make deparseExpr function static (refactoring)
 
 I also attached pgsql_fdw_analyze for only testing the effect of local
 statistics.  It contains both backend's ANALYZE command support and
 pgsql_fdw's ANALYZE support.

I think the idea is promising.

I'll mark the patch as ready for committer.

Yours,
Laurenz Albe

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

72 matches

Mail list logo