Re: [HACKERS] corrupt pages detected by enabling checksums

2013-05-11 Thread Simon Riggs
On 10 May 2013 23:41, Jeff Davis pg...@j-davis.com wrote:
 On Fri, 2013-05-10 at 18:32 +0100, Simon Riggs wrote:
 We don't write() WAL except with an immediate sync(), so the chances
 of what you say happening are very low to impossible.

 Are you sure? An XLogwrtRqst contains a write and a flush pointer, so I
 assume they can be different.

I'm answering this just to complete the discussion.

Yes, the write and flush pointer can be different. The write/flush are
two actions; we do one first, then the other, very quickly, inside
XLogWrite().

But we cannot rely on that, since there are some times when we don't
do that, such as wal_buffer overflow and when background walwriter
writes are at the end of the ring buffer. Not common, but they do
exist and when they exist they write many blocks. Which is counter to
what I had said earlier.

This part of the proposal isn't necessary for us to get a good answer
95% of the time, so it is dropped.

As mentioned on other post, we can write
UpdateMinRecoveryPoint(LogwrtResult.Flush, false) every time we do
XLogBackgroundFlush() and some other rework to make that happen
correctly. I'll post a separate patch.

--
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
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] Proposal to add --single-row to psql

2013-05-11 Thread Robert Haas
On Thu, May 9, 2013 at 7:36 AM, Michael Paquier
michael.paqu...@gmail.com wrote:

 Some of this is getting solved by making PostgreSQL more pluggable in
 ways that isolate the proprietary stuff, i.e. make people not have to
 touch the PostgreSQL core code much, if at all, in order to provide
 whatever special features they provide.  Hooks and FDWs are two such
 pluggable components.

  Extensions and the lovely background workers as well.

Some kind of extendable parser would be awesome.  It would need to tie
into the rewriter also.

No, I don't have a clue what the design looks like.

-- 
Robert Haas
EnterpriseDB: 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] [PATCH] pgbench --throttle (submission 5)

2013-05-11 Thread Fabien COELHO


Simpler version of 'pgbench --throttle' by handling throttling at the 
beginning of the transaction instead of doing it at the end.


This is for reference to the next commitfest.

--
Fabien.diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index bc01f07..13b33c7 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -137,6 +137,12 @@ int			unlogged_tables = 0;
 double		sample_rate = 0.0;
 
 /*
+ * whether clients are throttled to a given rate, expressed as a delay in us.
+ * 0, the default means no throttling.
+ */
+int64		throttle = 0;
+
+/*
  * tablespace selection
  */
 char	   *tablespace = NULL;
@@ -204,6 +210,8 @@ typedef struct
 	int			nvariables;
 	instr_time	txn_begin;		/* used for measuring transaction latencies */
 	instr_time	stmt_begin;		/* used for measuring statement latencies */
+	int64		trigger;		/* previous/next throttling (us) */
+	bool		throttled;  /* whether current transaction was throttled */
 	int			use_file;		/* index in sql_files for this client */
 	bool		prepared[MAX_FILES];
 } CState;
@@ -361,6 +369,9 @@ usage(void)
 		 -S   perform SELECT-only transactions\n
 	   -t NUM   number of transactions each client runs (default: 10)\n
 		 -T NUM   duration of benchmark test in seconds\n
+		 -H SPEC, --throttle SPEC\n
+		  delay in second to throttle each client\n
+		  sample specs: 0.025 40tps 25ms 25000us\n
 		 -v   vacuum all four standard tables before tests\n
 		   \nCommon options:\n
 		 -d print debugging output\n
@@ -899,6 +910,27 @@ doCustom(TState *thread, CState *st, instr_time *conn_time, FILE *logfile, AggVa
 top:
 	commands = sql_files[st-use_file];
 
+	/* handle throttling once per transaction by inserting a sleep.
+	 * this is simpler than doing it at the end.
+	 */
+	if (throttle  ! st-throttled)
+	{
+		/* compute delay to approximate a Poisson distribution
+		 * 100 = 13.8 .. 0 multiplier
+		 * if transactions are too slow or a given wait shorter than
+		 * a transaction, the next transaction will start right away.
+		 */
+		int64 wait = (int64)
+			throttle * -log(getrand(thread, 1, 100)/100.0);
+		st-trigger += wait;
+		st-sleeping = 1;
+		st-until = st-trigger;
+		st-throttled = true;
+		if (debug)
+			fprintf(stderr, client %d throttling INT64_FORMAT us\n,
+	st-id, wait);
+	}
+
 	if (st-sleeping)
 	{			/* are we sleeping? */
 		instr_time	now;
@@ -1069,6 +1101,7 @@ top:
 			st-state = 0;
 			st-use_file = (int) getrand(thread, 0, num_files - 1);
 			commands = sql_files[st-use_file];
+			st-throttled = false;
 		}
 	}
 
@@ -2086,6 +2119,7 @@ main(int argc, char **argv)
 		{unlogged-tables, no_argument, unlogged_tables, 1},
 		{sampling-rate, required_argument, NULL, 4},
 		{aggregate-interval, required_argument, NULL, 5},
+		{throttle, required_argument, NULL, 'H'},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -2152,7 +2186,7 @@ main(int argc, char **argv)
 	state = (CState *) pg_malloc(sizeof(CState));
 	memset(state, 0, sizeof(CState));
 
-	while ((c = getopt_long(argc, argv, ih:nvp:dqSNc:j:Crs:t:T:U:lf:D:F:M:, long_options, optindex)) != -1)
+	while ((c = getopt_long(argc, argv, ih:nvp:dqSNc:j:Crs:t:T:U:lf:D:F:M:H:, long_options, optindex)) != -1)
 	{
 		switch (c)
 		{
@@ -2307,6 +2341,26 @@ main(int argc, char **argv)
 	exit(1);
 }
 break;
+			case 'H':
+			{
+/* get a double from the beginning of option value */
+double throttle_value = atof(optarg);
+if (throttle_value = 0.0)
+{
+	fprintf(stderr, invalid throttle value: %s\n, optarg);
+	exit(1);
+}
+/* rough handling of possible units */
+if (strstr(optarg, us))
+	throttle = (int64) throttle_value;
+else if (strstr(optarg, ms))
+	throttle = (int64) (1000.0 * throttle_value);
+else if (strstr(optarg, tps))
+	throttle = (int64) (100.0 / throttle_value);
+else /* assume that default is in second */
+	throttle = (int64) (100.0 * throttle_value);
+			}
+break;
 			case 0:
 /* This covers long options which take no argument. */
 break;
@@ -2594,6 +2648,14 @@ main(int argc, char **argv)
 	/* get start up time */
 	INSTR_TIME_SET_CURRENT(start_time);
 
+	/* set initial client throttling trigger */
+	if (throttle)
+	{
+		state[0].trigger = INSTR_TIME_GET_MICROSEC(start_time);
+		for (i = 1; i  nclients; i++)
+			state[i].trigger = state[0].trigger;
+	}
+
 	/* set alarm if duration is specified. */
 	if (duration  0)
 		setalarm(duration);
diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml
index 79b4baf..b61ed39 100644
--- a/doc/src/sgml/pgbench.sgml
+++ b/doc/src/sgml/pgbench.sgml
@@ -310,6 +310,26 @@ pgbench optional replaceableoptions/ /optional replaceabledbname/
  /varlistentry
 
  varlistentry
+  termoption-H/option replaceablerate//term
+  termoption--throttle/option replaceablerate//term
+  listitem
+   para
+	Do client transaction 

Re: [HACKERS] Proposal to add --single-row to psql

2013-05-11 Thread David Fetter
On Sat, May 11, 2013 at 11:17:03AM -0400, Robert Haas wrote:
 On Thu, May 9, 2013 at 7:36 AM, Michael Paquier
 michael.paqu...@gmail.com wrote:
 
  Some of this is getting solved by making PostgreSQL more pluggable in
  ways that isolate the proprietary stuff, i.e. make people not have to
  touch the PostgreSQL core code much, if at all, in order to provide
  whatever special features they provide.  Hooks and FDWs are two such
  pluggable components.
 
   Extensions and the lovely background workers as well.
 
 Some kind of extendable parser would be awesome.  It would need to tie
 into the rewriter also.
 
 No, I don't have a clue what the design looks like.

That's a direction several of the proprietary RDBMS vendors have
proposed.  I think it'd be great :)

Pre-coffee (yeah, I know.  Bad idea.) sketch of an idea: create an API
to pass expression trees in and out.  This could have other benefits
as to clustering space, shortening the planning cycle, etc., but let's
not go there for now.  My knowledge is very, very sketchy, but when I
squint, the expression trees we use look a lot like JSON.  Are they
isomorphic?

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
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] Make psql -1 file.sql work as with -f

2013-05-11 Thread Robert Haas
On Fri, May 10, 2013 at 9:50 AM, Peter Eisentraut pete...@gmx.net wrote:
 On 8/9/12 9:08 AM, Robert Haas wrote:
 On Wed, Aug 8, 2012 at 6:50 PM, David Fetter da...@fetter.org wrote:
 I'm wondering if perhaps -- in addition to what you've done here -- we
 should make psql -1 error out if reading from a terminal.

 +1 for this.

 OK, done.

 I had to revise the original patch pretty heavily before committing;

 My first use of 9.3beta1 in development failed because of changes
 introduced by this patch, specifically because of the newly introduced error

 psql: -1 is incompatible with -c and -l

 I'm not convinced this is correct.  -c and -l are single-transaction
 actions almost by definition.

 This particular aspect of the change wasn't really brought up in the
 original thread.  What was your thinking?

Well, I think my main thinking was to prevent it in interactive mode,
since it doesn't work in interactive mode, and then it also seemed to
make sense to prevent it in the other cases to which it does not
apply.

I think there are cases where you can detect the fact that -1 -c
wasn't actually wrapping the command in a BEGIN and an END, but I
agree it might be a bit pedantic to worry about them.  There have been
previous proposals to allow multiple -c and -f options, and to allow
those to be intermingled; if we did that, then this would surely
matter.  I agree that's hypothetical though since there's no patch to
do any such thing currently on the table.

Generally, I think we're too lax about detecting and complaining about
conflicting combinations of options.  But I'm not going to stand here
and hold my breath if someone else feels that this particular
combination doesn't merit a complaint.

-- 
Robert Haas
EnterpriseDB: 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] Proposal to add --single-row to psql

2013-05-11 Thread Tom Lane
David Fetter da...@fetter.org writes:
 On Sat, May 11, 2013 at 11:17:03AM -0400, Robert Haas wrote:
 Some kind of extendable parser would be awesome.  It would need to tie
 into the rewriter also.
 
 No, I don't have a clue what the design looks like.

 That's a direction several of the proprietary RDBMS vendors have
 proposed.  I think it'd be great :)

 Pre-coffee (yeah, I know.  Bad idea.) sketch of an idea: create an API
 to pass expression trees in and out.  This could have other benefits
 as to clustering space, shortening the planning cycle, etc., but let's
 not go there for now.  My knowledge is very, very sketchy, but when I
 squint, the expression trees we use look a lot like JSON.  Are they
 isomorphic?

By the time you've got an expression tree, the problem is mostly solved,
at least so far as parser extension is concerned.

More years ago than I care to admit, I worked on systems that had
run-time-extensible parsers at Hewlett-Packard, so technology for this
does exist.  But my (vague) memory of those systems is that the parser's
language capabilities were more limited than bison's, perhaps only
LL(1).  Parsing spec-compatible SQL that way might be a challenge.

A larger issue is that if you don't have the whole grammar available
to check, it's difficult to be sure there are no parsing conflicts.
I seem to remember that we hit some conflicts between different
extension ROMs back at HP :-(

Another point is that extensions that are actually interesting require
a lot more than new syntax.  Robert mentioned the rewriter, but you'd
typically need planner and executor additions as well.  It's possible to
see how whole new plan node types might be added by a plugin so far as
the executor is concerned, but I haven't a clue how we'd get the planner
to emit them ...

regards, tom lane


-- 
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] GSOC Student Project Idea

2013-05-11 Thread Michael Schuh
On Wed, May 8, 2013 at 1:48 PM, Jim Nasby j...@nasby.net wrote:

 On 5/8/13 3:54 AM, Heikki Linnakangas wrote:

 On 24.04.2013 14:31, Florian Pflug wrote:

 On Apr23, 2013, at 23:25 , Alexander Korotkovaekorot...@gmail.com
 wrote:

 I've taken a brief look on the paper and implementation. As I can
 see iDistance implements some global building strategy. I mean, for
 example, it selects some point, calculates distances from selected
 point to all points in dataset etc. So, it uses the whole dataset
 at the same time.

 However you can try to implement global index building in GiST or
 SP-GiST. In this case I think you should carefully estimate your
 capabilities during single GSoC project. You would need to extend
 GiST or SP-GiST interface and write completely new implementation
 of tree building algorithm. Question of how to exactly extend GiST
 or SP-GiST interface for this case could appear to be very hard
 even theoretically.


 +1. That seemed to be a major roadblock to me too when I read the
 paper.

 You could work around that by making partition identification a
 separate step. You'd have a function

 idist_analyze(cfg name, table name, field name)

 which'd identify suitable partitions for the data distribution in
 table.field and store them somewhere. Such a set of pre-identified
 partitions would be akin to a tsearch configuration, i.e. all other
 parts of the iDistance machinery would use it to map points to index
 keys and queries to ranges of those keys. You'll want to look at how
 tsearch handles that, and check if the method can indeed be applied
 to iDistance.


 You could perform that step as part of the index build. Before the index
 build starts to add tuples to the index, it could scan a random sample of
 the heap and identify the partitions based on that.

 If you need to store the metadata, like a map of partitions, it becomes
 difficult to cajole this into a normal GiST or SP-GiST opclass. The API
 doesn't have any support for storing such metadata.

  In a first cut, you'd probably only allow inserts into index which
 don't change the maximum distances from the partition centers that
 idist_analyze() found.


 That seems like a pretty serious restriction. I'd try to write it so that
 you can insert any value, but if the new values are very different from any
 existing values, it would be OK for the index quality to degrade. For
 example, you could simply add any out-of-bounds values to a separate branch
 in the index, which would have no particular structure and would just have
 to be scanned on every query. You can probably do better than that, but
 that would be a trivial way to deal with it.


 Or you could use the new insert to start a new partition.

 Heck, maybe the focus should actually be on partitions and not individual
 records/points. ISTM the entire challenge here is figuring out a way to
 maintain a set of partitions that:

 - Are limited enough in number that you can quickly perform
 operations/searches across all partitions
 - Yet small enough that once you've narrowed down a set of partitions you
 don't have a ton of raw records to still look at

 Before we had range types I experimented with representing time ranges as
 rectangles of varying size (ie: for (start, end), create
 rectangle(point(start,start), point(end,end)). The problem with that is you
 had to convert timestamp into a float, which was not exact. So when
 querying you could use a GiST index on all the rectangles to narrow your
 scope, but you still needed a set of exact clauses (ie: start = now() - '1
 year' AND end = now()). Partitions would be similar in that they wouldn't
 be exact but could greatly narrow the search space (of course we'd want to
 handle the secondary exact checking internally instead of exposing the user
 to that).



I appreciate all the responses, and I think everyone has more-or-less
confirmed the scope of the project proposal I submitted. It was hard to
find time during the final weeks of the semester to greatly explore the
(SP-)GiST interfaces, but given the responses here, it seems the
integrated implementation is clearly beyond scope for a summer project,
which I agree with. Instead, I proposed my original plan that can surely be
accomplished over the summer. Coincidentally enough, it is in essence, what
Florian Pflug and the rest have discussed here.

In short, I will use only the btree in postgresql to store single
dimensional values mapped to multi-dimensional point data, and then query
ranges of these values in the btree based on partition information stored
separately. The information can be gathered upfront and periodically
updated as needed, which done properly will not require downtime or
reshuffling of the btree. This will result in a fully useable index, and
the project will also include a performance and usability assessment at the
end.

I still believe this would be an excellent GSoC project that I am highly
motivated to make fully accessible to the community 

Re: [HACKERS] Re: [GENERAL] pg_upgrade fails, mismatch of relation OID - 9.1.9 to 9.2.4

2013-05-11 Thread Bruce Momjian
On Fri, May 10, 2013 at 08:03:38PM -0400, Bruce Momjian wrote:
 On Fri, May 10, 2013 at 12:36:21PM -0400, Evan D. Hoffman wrote:
  pg.dropped.16 INTEGER /* dummy */,
  pg.dropped.17 INTEGER /* dummy */,
  pg.dropped.18 INTEGER /* dummy */,
  pg.dropped.19 INTEGER /* dummy */,
  pg.dropped.20 INTEGER /* dummy */,
  pg.dropped.21 INTEGER /* dummy */,
  pg.dropped.22 INTEGER /* dummy */,
  pg.dropped.23 INTEGER /* dummy */,
  pg.dropped.24 INTEGER /* dummy */,
  pg.dropped.25 INTEGER /* dummy */,
  pg.dropped.26 INTEGER /* dummy */,
  ha27 character varying(10) DEFAULT 'UNKNOWN'::character varying,
  pg.dropped.28 INTEGER /* dummy */,
  dr29 character varying(10)
 
 OK, this verifies that the table had a lot of DDL churn.  I have no idea
 how to pursue this further because I am unsure how we are going to
 replicate the operations performed on this table in the past, as you
 mentioned much of this was before your time on the job.
 
 Evan, I suggest you force a toast table on the table by doing:
 
   ALTER TABLE bpm.setupinfo ADD COLUMN dummy TEXT;
 
 Then drop the column.  That will create a toast table and will allow
 pg_upgrade to succeed.

FYI, I did test adding a TEXT column and altering a column to TEXT on
Postgres 9.1, and both created a toast table.  I am still have no clues
about what would have caused the missing toast table.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
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] Proposal to add --single-row to psql

2013-05-11 Thread Robert Haas
On Sat, May 11, 2013 at 12:27 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 By the time you've got an expression tree, the problem is mostly solved,
 at least so far as parser extension is concerned.

Right.

 More years ago than I care to admit, I worked on systems that had
 run-time-extensible parsers at Hewlett-Packard, so technology for this
 does exist.  But my (vague) memory of those systems is that the parser's
 language capabilities were more limited than bison's, perhaps only
 LL(1).  Parsing spec-compatible SQL that way might be a challenge.

If I understand bison correctly, it basically looks at the current
parser state and the next token and decides to either shift that token
onto the stack or reduce the stack using some rule.  If there's no
matching rule, we error out.  If someone wants to inject new rules
into the grammar, those state tables are all going to change.  But if
we could contrive things so that the state tables are built
dynamically and can be change as rules are added and removed, then it
seems to me that we could let a loadable module supply (or delete)
whatever grammar rules it likes.  Whenever it does this, we recompile
the grammar on next use (and complain if we get ambiguities).  This
does not sound all that easy to code, but at least in theory it seems
doable.

We'd also need a way to add keywords.  To be quite frank, I think our
whole approach to keywords is massive screwed up right now.  Aside
from the parser bloat, adding even unreserved keywords breaks stuff,
e.g. SELECT 1 this_is_not_yet_a_keyword.  EnterpriseDB's internal
bug-tracking system must have at least half a dozen bug reports open
right now that are attributable to keywords either existing at all or
being more reserved than they are in Oracle.  That aside, the amount
of trouble we've caused for PostgreSQL users over the years, either by
adding new keywords or disallowing them in contexts where they used to
work, is not small.  I don't have a specific proposal for what we
should do to make this problem less painful, but I think if we ever
revise our parser infrastructure we ought to use that as an
opportunity to try to come up with something better than what exists
today, because the status quo is awfully painful.

 Another point is that extensions that are actually interesting require
 a lot more than new syntax.  Robert mentioned the rewriter, but you'd
 typically need planner and executor additions as well.  It's possible to
 see how whole new plan node types might be added by a plugin so far as
 the executor is concerned, but I haven't a clue how we'd get the planner
 to emit them ...

I don't have any ideas either.  Still, solving some of the problems
would be better than solving none of them.

-- 
Robert Haas
EnterpriseDB: 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