Re: [HACKERS] Range Types, constructors, and the type system

2011-06-27 Thread Darren Duncan

Jeff Davis wrote:

On Sun, 2011-06-26 at 00:57 -0700, Darren Duncan wrote:
I believe that the best general solution here is for every ordered base type to 
just have a single total order, which is always used with that type in any 
generic order-sensitive operation, including any ranges defined over it, or any 
ORDER BY or any ,,etc.  The built-in generic text type would have exactly 1 
system-defined collation that can't be changed, and it would be something simple 
and generic, such as simply sorting on the codepoint as integers.


Well, we're trying to support SQL, and SQL supports collations, so I
don't think we can just ignore that.


I'm not saying you can't support collations.  See also my reply to Tom.


I also agree with Tom that it's not a good idea. My reasons are:

 * Practical considerations, such as having a bunch of cruft from
duplicated types all over the system. With sufficient changes to the
type system, maybe that could be overcome. Or perhaps domains could be
used to make that work for range types (sort of), but the result would
not be very consistent with the rest of the system.


Yes, duplication can be avoided.


 * It doesn't seem to be based in any mathematical argument. A type is a
set of values, and there's no reason it can't have several total orders;
or no total order at all. So it appears to just be piggybacking on the
type system infrastructure as a place to hold the metadata for a total
order.


Yes, I agree that a type is a set of values, and a type can have 0..N total 
orders.  My proposal is just that, for those types that have at least 1 total 
order, exactly 1 of those is defined to be used implicitly in contexts where a 
total order is desired and no explicit collation is given, such as in ranges.



 * Who's to say that a compare function is the only way to specify a
total order? There might be other interfaces that would support
something closer to a lexicographic sort. So, from a theoretical
standpoint, trying to attach a single notion of total order to a type
seems strange, because there might be multiple interfaces for specifying
even one total order.


Thank you for bringing this up, the notion of multiple interfaces for specifying 
even one total order.  My example of a compare function was just an example, and 
it is valuable to consider that this may not be the only way to do it.



 * It would require extra explicit type annotations. If you have 12 text
types, the only way to practically use any text type is to constantly
specify which more-specific text type it actually is (probably using
the :: operator). That is not necessarily a bad choice if starting a
language from scratch and forming the syntax in a way that it's
reasonable to do. But this is SQL, and lots of type annotations are
un-SQL-like.


Well sometimes it doesn't hurt to suggest solutions from the point of view that 
one can start the language from scratch, because that provides a clean way to 
conceptualize and explain a feature.  And then people can find some middle 
ground that adapts benefits from that idea for the feature without changing SQL 
more than needed.  Witness the various improvements to Perl 5 that were first 
expressed in terms of Perl 6.


-- Darren Duncan

--
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] Re: starting to review the Extend NOT NULL representation to pg_constraint patch

2011-06-27 Thread Dean Rasheed
On 27 June 2011 03:31, Robert Haas robertmh...@gmail.com wrote:
 On Sat, Jun 25, 2011 at 2:15 AM, Dean Rasheed dean.a.rash...@gmail.com 
 wrote:
 Really? I would expect the reverse, namely that the not-nullness is
 part of the PK constraint and dropping the PK *would* then start
 allowing NULLs.

 Hmm, OK.  I had assumed we were only trying to fix the problem that
 parent and child inheritance tables could get out of step, but maybe
 you're right.

 If we go with that approach, then consider:

 CREATE TABLE foo (a int);
 CREATE TABLE bar () INHERITS (foo);
 Now if someone adds a primary key foo (a), what happens currently is
 that foo.a becomes NOT NULL, but bar.a still allows NULLs.  Should
 that remain true (on the theory that a primary key constraint is not
 inherited) or become false (on the theory that parent and child tables
 should match)?


I'm not sure, but my real problem with the current behaviour is its
inconsistency. Consider this case:

CREATE TABLE foo (a int PRIMARY KEY);
CREATE TABLE bar () INHERITS (foo);

Currently this results in bar not allowing NULLs, which is
inconsistent with adding the PK after defining the inheritance. Then
if the PK is dropped, the non-nullness is left behind on both foo and
bar.

I would summarise the consistency requirements as:

1). ADD CONSTRAINT should leave both parent and child tables in the
same state as they would have been if the constraint had been defined
at table creation time.

2). DROP CONSTRAINT should leave both parent and child tables in the
same state as if the constraint had never existed (completely
reversing the effects of ADD CONSTRAINT).

I don't have a strong opinion as to whether or not the NOT NULL part
of a PK should be inherited, provided that it is consistent with the
above.

I guess that if I were forced to choose, I would say that the NOT NULL
part of a PK should not be inherited, since I do think of it as part
of the PK, and PKs are not inherited. But I wouldn't be too upset if
it were inherited (consistently!) and I can't think of a use case
where that would be a problem.

Regards,
Dean

-- 
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] silent_mode and LINUX_OOM_ADJ

2011-06-27 Thread Magnus Hagander
On Fri, Jun 24, 2011 at 16:37, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Heikki Linnakangas's message of vie jun 24 07:01:57 -0400 2011:
 While reviewing Peter Geoghegan's postmaster death patch, I noticed that
 if you turn on silent_mode, the LINUX_OOM_ADJ code in fork_process()
 runs when postmaster forks itself into background. That re-enables the
 OOM killer in postmaster, if you've disabled it in the startup script by
 adjusting /proc/self/oom_adj. That seems like a bug, albeit a pretty
 minor one.

 This may be a dumb question, but what is the purpose of silent_mode?
 Can't you just use nohup?

 I think silent_mode is an artifact from when our daemon handling in
 general was a lot more primitive (I bet there wasn't even pg_ctl then).
 Maybe we could discuss removing it altogether.

If I'm not entirely mistaken, it's on by default in SuSE RPMs. I don't
have a box with access right now, but I've come across it a couple of
times recently with clients, and I think that's how it is. Might want
to doublecheck with the suse maintainer if there's a particular reason
they do that...


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
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] ALTER TABLE lock strength reduction patch is unsafe

2011-06-27 Thread Simon Riggs
On Fri, Jun 17, 2011 at 8:45 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 Department of second thoughts: I think I see a problem.

 Um, yeah, so that doesn't really work any better than my idea.

 On further reflection, there's a problem at a higher level than this
 anyway.  Even if we can get a single SnapshotNow scan to produce
 guaranteed-self-consistent results, that doesn't ensure consistency
 between the results of scans occurring serially.  An example here is
 ALTER COLUMN DROP DEFAULT, which is currently imagined to impact only
 writers.  However, suppose that a concurrent relcache load fetches the
 pg_attribute row, notes that it has atthasdef = true, and then the ALTER
 commits before we start to scan pg_attrdef.  The consistency checks in
 AttrDefaultFetch() will complain about a missing pg_attrdef entry, and
 rightly so.  We could lobotomize those checks, but it doesn't feel right
 to do so; and anyway there may be other cases that are harder to kluge up.

Locking the whole definition is at least one way of solving this
problem. My locking fix does that.

 So really we need consistency across *at least* one entire relcache load
 cycle.  We could maybe arrange to take an MVCC snap (or some lighter
 weight version of that) at the start, and use that for all the resulting
 scans, but I think that would be notationally messy.  It's not clear
 that it'd solve everything anyhow.  There are parts of a relcache entry
 that we fetch only on-demand, so they are typically loaded later than
 the core items, and probably couldn't use the same snapshot.  Worse,
 there are lots of places where we assume that use of catcache entries or
 direct examination of the catalogs will yield results consistent with
 the relcache.

 I suspect these latter problems will impact Simon's idea as well.

I think you're probably right, or at least, the suspicion is not
something I can address quickly enough to be safe.

I will revert to the AccessExclusiveLocks.

-- 
 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] Another issue with invalid XML values

2011-06-27 Thread Noah Misch
On Mon, Jun 27, 2011 at 12:45:02AM +0200, Florian Pflug wrote:
 Updated patch attached. Do you think this is Ready for Committer?

Thanks.  Yes; I have just marked it that way.

-- 
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] ALTER TABLE lock strength reduction patch is unsafe

2011-06-27 Thread Simon Riggs
On Fri, Jun 17, 2011 at 6:22 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Thu, Jun 16, 2011 at 6:54 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 I believe that this is fundamentally unavoidable so long as we use
 SnapshotNow to read catalogs --- which is something we've talked about
 changing, but it will require a pretty major RD effort to make it
 happen.

 Ouch.

 I wonder if we could avoid this anomaly by taking a throwaway MVCC
 snapshot at the beginning of each system catalog scan and using it
 just for the duration of that scan.  If nothing that has touched the
 catalog commits while the scan is open, then this is logically
 equivalent to SnapshotNow.  If something does commit in mid-scan, then
 we might not get the latest version of the row, but we should end up
 with exactly one.  If it's not the latest one, we'll do the rebuild
 again upon seeing the next sinval message; in the meantime, the
 version we're using mustn't be too intolerably bad or it was an error
 not to use AccessExclusiveLock in the first place.

 Yeah, this seems like a possibly workable direction to explore.  I like
 this better than what Simon is proposing, because it would fix the
 generic issue for all types of catalog SnapshotNow scans.

 IIUC, the problem with this approach is not correctness but
 performance.  Taking snapshots is (currently) expensive.

 Yeah.  After mulling it for awhile, what about this idea: we could
 redefine SnapshotNow as a snapshot type that includes a list of
 transactions-in-progress, somewhat like an MVCC snapshot, but we don't
 fill that list from the PGPROC array.  Instead, while running a scan
 with SnapshotNow, anytime we determine that a particular XID is
 still-in-progress, we add that XID to the snapshot's list.
 Subsequently, the SnapshotNow code assumes that XID to be
 still-in-progress without consulting its actual state.  We reset the XID
 list to empty when starting a new SnapshotNow scan.  (We might be able
 to do so less often than that, like only when we do
 AcceptInvalidationMessages, but it's not clear to me that there's any
 real benefit in hanging onto the state longer.)

 This costs no performance; if anything it should be faster than now,
 because we'll be replacing expensive transaction state probes with
 relatively-cheap searches of an XID array that should almost always
 be quite short.

 With this approach, we would have no serialization anomalies from single
 transactions committing while a scan is in progress.  There could be
 anomalies resulting from considering an earlier XID to be in-progress
 while a later XID is considered committed (because we didn't observe
 it until later).  So far as I can see offhand, the impact of that would
 be that there might be multiple versions of a tuple that are considered
 good, but never that there would be no version considered good (so long
 as the other XIDs simply updated the tuple and didn't delete it).  I
 think this would be all right, since the scan would just seize on the
 first good version it finds.  As you argue above, if that's not good
 enough for our purposes then the updater(s) should have taken a stronger
 lock.

I liked this idea, so began to prototype the code. My rough hack is
attached, for the record.

One thing that occurs to me about this is that SnapshotNow with or
without these changes returns the latest committed row and ignores
in-progress changes.

Accepting an older version of the definition will always be
potentially dangerous. I can't see a way of doing this that doesn't
require locking - for changes such as new constraints we need to wait
until in progress changes are complete.

So maybe this idea is worth doing, but I don't think it helps us much
reduce lock levels for DDL.

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


snapshotnow_consistent.v1.patch
Description: Binary data

-- 
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] silent_mode and LINUX_OOM_ADJ

2011-06-27 Thread Heikki Linnakangas

On 27.06.2011 10:23, Magnus Hagander wrote:

On Fri, Jun 24, 2011 at 16:37, Alvaro Herrera
alvhe...@commandprompt.com  wrote:

Excerpts from Heikki Linnakangas's message of vie jun 24 07:01:57 -0400 2011:

While reviewing Peter Geoghegan's postmaster death patch, I noticed that
if you turn on silent_mode, the LINUX_OOM_ADJ code in fork_process()
runs when postmaster forks itself into background. That re-enables the
OOM killer in postmaster, if you've disabled it in the startup script by
adjusting /proc/self/oom_adj. That seems like a bug, albeit a pretty
minor one.

This may be a dumb question, but what is the purpose of silent_mode?
Can't you just use nohup?


I think silent_mode is an artifact from when our daemon handling in
general was a lot more primitive (I bet there wasn't even pg_ctl then).
Maybe we could discuss removing it altogether.


If I'm not entirely mistaken, it's on by default in SuSE RPMs. I don't
have a box with access right now, but I've come across it a couple of
times recently with clients, and I think that's how it is. Might want
to doublecheck with the suse maintainer if there's a particular reason
they do that...


Yep, seems to be so. Max, you're the maintainer of the PostgreSQL SuSE 
RPMs, right? Can you comment on the above?


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
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] silent_mode and LINUX_OOM_ADJ

2011-06-27 Thread Reinhard Max

Hi Heikki,

On Mon, 27 Jun 2011 at 12:10, Heikki Linnakangas wrote:


Max, you're the maintainer of the PostgreSQL SuSE RPMs, right?


my first name is Reinhard, but aside from that, you are right. ;)


Can you comment on the above?


I enabled it many years ago when (IIRC) it was needed in conjunction 
with logging_collector = on to get proper logging and especially log 
rotation. It might very well be that it is not needed anymore and 
suggestions for doing it better are welcome.


cu
Reinhard

--
SUSE LINUX Products GmbH, Maxfeldstraße 5, 90409 Nürnberg, Germany
GF: Jeff Hawn, Jennifer Guild, Felix Imendörffer, HRB 16746 (AG Nürnberg)

--
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] Range Types, constructors, and the type system

2011-06-27 Thread Florian Pflug
On Jun27, 2011, at 02:48 , Jeff Davis wrote:
 On Mon, 2011-06-27 at 00:56 +0200, Florian Pflug wrote:
 Well, there actually *is* some precedence for that kind of top-down
 (form a syntactic perspective) type inference. We *enforce* the cast
 in 
  array[]::arraytype
 and actually for a very similar reason - without the case, there's no
 way of knowing which type of empty array was meant. I think we also
 
 That's a good point.
 
 Although, I'm not sure whether that's an argument that we can make the
 type system work as-is, or if it means that we should add syntax like
 ARRAY[].

It was meant as an argument for the former, i.e. for extending the type
system (or rather the function call syntax, as I argue below).

 special-case
  'literal'::type
 to use the input function of type directly, instead of first creating
 a text value and later casting it to type.
 
 That is certainly true. Quoted strings never start out as text, they
 start out as unknown and wait for the type inference to determine the
 type. I'm not entirely sure whether a quoted string followed by a cast
 is briefly unknown and then cast, or if it's directly interpreted using
 the cast's type input function.

It's at least labelled with type unknown for a while AFAIK.

 I don't know if that's a good example though because it's near the end
 of the line and there's no function call in between the arguments and
 the cast. It might get more complex with cases like:
 
  range(lower(range(1,2)),upper(range(1,2)))::int8range
 
 but maybe that can be done more easily than I think?

I wouldn't take it that far. What I had in mind was to *only* support
the case where the cast directly follows the function call, i.e. the case
  f(...)::type

I view this more as an extension of the function call syntax than of
type inference. In other languages with polymorphism, there usually is
an explicit syntactic construct for specifying the type arguments to
a polymorphic function. For example, C++ you'd write
  make_rangeint(3,4)
to call the polymorphic function make_range() with it's (first)
type argument set to int. I think of
  f(...)::type
as essentially the same thing, but re-using already existing syntax
instead of inventing new one. 

I just checked - we currently special case array[]::type in transformExpr()
by detecting the case of an array expression being the immediate child
of a cast expression. I suggest we do the same for f(...)::type, i.e.
also special case a function call being the immediate child of a cast
expression and pass down the forced result type to the function call node.

Function call nodes would then usually ignore that passed-down result type,
except in the case of a polymorphic functions whose argument types don't
uniquely define its result type.

But I haven't tried doing that, so there might be stumbling block down
that road that I missed...

best regards,
Florian Pflug


-- 
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] Range Types and length function

2011-06-27 Thread Florian Pflug
On Jun27, 2011, at 03:12 , Jeff Davis wrote:
 But I think you're right, it shouldn't be the responsibility of range
 types. Perhaps I should leave length() as some inlinable SQL functions
 like I mentioned, or perhaps I should remove them completely.

Does the current definition of length(range), i.e.
  upper(range) - lower(range)
deal correctly with open vs. closed ranges and unbounded ranges? I'm thinking
that it probably doesn't - what would be the results of
  length('[0,1]'::intrange) -- Should be 2
  length('[0,1)'::intrange) -- Should be 1
  length('[0,inf]'::intrange) -- Should be infinity, but ints can't
 represent that, can't they?

If it cannot be easily made to support these cases, than I vote for
removing it all together.

best regards,
Florian Pflug


-- 
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] WIP: Fast GiST index build

2011-06-27 Thread Alexander Korotkov
I've added information about testing on some real-life dataset to wiki page.
This dataset have a speciality: data is ordered inside it. In this case
tradeoff was inverse in comparison with expectations about fast build
algrorithm. Index built is longer but index quality is significantly better.
I think high speed of regular index built is because sequential inserts are
into near tree parts. That's why number of actual page reads and writes is
low. The difference in tree quality I can't *convincingly explain now.*
I've also maked tests with shuffled data of this dataset. In this case
results was similar to random generated data.

--
With best regards,
Alexander Korotkov.


Optimizing box_penalty (Re: [HACKERS] WIP: Fast GiST index build)

2011-06-27 Thread Heikki Linnakangas

On 27.06.2011 13:45, Alexander Korotkov wrote:

I've added information about testing on some real-life dataset to wiki page.
This dataset have a speciality: data is ordered inside it. In this case
tradeoff was inverse in comparison with expectations about fast build
algrorithm. Index built is longer but index quality is significantly better.
I think high speed of regular index built is because sequential inserts are
into near tree parts. That's why number of actual page reads and writes is
low. The difference in tree quality I can't *convincingly explain now.*
I've also maked tests with shuffled data of this dataset. In this case
results was similar to random generated data.


Hmm, I assume the CPU overhead is coming from the penalty calls in this 
case too. There's some low-hanging optimization fruit in 
gist_box_penalty(), see attached patch. I tested this with:


CREATE TABLE points (a point);
CREATE INDEX i_points ON points using gist (a);
INSERT INTO points SELECT point(random(), random()) FROM 
generate_series(1,100);


and running checkpoint; reindex index i_points; a few times with and 
without the patch. The patch reduced the runtime from about 17.5 s to 
15.5 s. oprofile confirms that the time spent in gist_box_penalty() and 
rt_box_union() is reduced significantly.


This is all without the fast GiST index build patch, so this is 
worthwhile on its own. If penalty function is called more, then this 
becomes even more significant.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
*** a/src/backend/access/gist/gistproc.c
--- b/src/backend/access/gist/gistproc.c
***
*** 23,29 
  
  static bool gist_box_leaf_consistent(BOX *key, BOX *query,
  		 StrategyNumber strategy);
! static double size_box(Datum dbox);
  static bool rtree_internal_consistent(BOX *key, BOX *query,
  		  StrategyNumber strategy);
  
--- 23,29 
  
  static bool gist_box_leaf_consistent(BOX *key, BOX *query,
  		 StrategyNumber strategy);
! static double size_box(BOX *box);
  static bool rtree_internal_consistent(BOX *key, BOX *query,
  		  StrategyNumber strategy);
  
***
*** 32,63  static bool rtree_internal_consistent(BOX *key, BOX *query,
   * Box ops
   **/
  
! static Datum
! rt_box_union(PG_FUNCTION_ARGS)
  {
- 	BOX		   *a = PG_GETARG_BOX_P(0);
- 	BOX		   *b = PG_GETARG_BOX_P(1);
- 	BOX		   *n;
- 
- 	n = (BOX *) palloc(sizeof(BOX));
- 
  	n-high.x = Max(a-high.x, b-high.x);
  	n-high.y = Max(a-high.y, b-high.y);
  	n-low.x = Min(a-low.x, b-low.x);
  	n-low.y = Min(a-low.y, b-low.y);
- 
- 	PG_RETURN_BOX_P(n);
  }
  
! static Datum
! rt_box_inter(PG_FUNCTION_ARGS)
  {
- 	BOX		   *a = PG_GETARG_BOX_P(0);
- 	BOX		   *b = PG_GETARG_BOX_P(1);
- 	BOX		   *n;
- 
- 	n = (BOX *) palloc(sizeof(BOX));
- 
  	n-high.x = Min(a-high.x, b-high.x);
  	n-high.y = Min(a-high.y, b-high.y);
  	n-low.x = Max(a-low.x, b-low.x);
--- 32,56 
   * Box ops
   **/
  
! /*
!  * Calculates union of two boxes, a and b. The result is stored in *n.
!  */
! static void
! rt_box_union(BOX *n, BOX *a, BOX *b)
  {
  	n-high.x = Max(a-high.x, b-high.x);
  	n-high.y = Max(a-high.y, b-high.y);
  	n-low.x = Min(a-low.x, b-low.x);
  	n-low.y = Min(a-low.y, b-low.y);
  }
  
! /*
!  * Calculates intersection of two boxes, a and b. The result is stored in *n.
!  * Returns false if the boxes don't intersect;
!  */
! static bool
! rt_box_inter(BOX *n, BOX *a, BOX *b)
  {
  	n-high.x = Min(a-high.x, b-high.x);
  	n-high.y = Min(a-high.y, b-high.y);
  	n-low.x = Max(a-low.x, b-low.x);
***
*** 65,76  rt_box_inter(PG_FUNCTION_ARGS)
  
  	if (n-high.x  n-low.x || n-high.y  n-low.y)
  	{
! 		pfree(n);
! 		/* Indicate no intersection by returning NULL pointer */
! 		n = NULL;
  	}
! 
! 	PG_RETURN_BOX_P(n);
  }
  
  /*
--- 58,67 
  
  	if (n-high.x  n-low.x || n-high.y  n-low.y)
  	{
! 		/* Indicate no intersection by returning false */
! 		return false;
  	}
! 	return true;
  }
  
  /*
***
*** 187,196  gist_box_penalty(PG_FUNCTION_ARGS)
  	GISTENTRY  *origentry = (GISTENTRY *) PG_GETARG_POINTER(0);
  	GISTENTRY  *newentry = (GISTENTRY *) PG_GETARG_POINTER(1);
  	float	   *result = (float *) PG_GETARG_POINTER(2);
! 	Datum		ud;
  
! 	ud = DirectFunctionCall2(rt_box_union, origentry-key, newentry-key);
! 	*result = (float) (size_box(ud) - size_box(origentry-key));
  	PG_RETURN_POINTER(result);
  }
  
--- 178,189 
  	GISTENTRY  *origentry = (GISTENTRY *) PG_GETARG_POINTER(0);
  	GISTENTRY  *newentry = (GISTENTRY *) PG_GETARG_POINTER(1);
  	float	   *result = (float *) PG_GETARG_POINTER(2);
! 	BOX		   *origbox = DatumGetBoxP(origentry-key);
! 	BOX		   *newbox = DatumGetBoxP(newentry-key);
! 	BOX			unionbox;
  
! 	rt_box_union(unionbox, origbox, newbox);
! 	*result = (float) (size_box(unionbox) - size_box(origbox));
  	PG_RETURN_POINTER(result);
  }
  
***

Re: [HACKERS] libpq SSL with non-blocking sockets

2011-06-27 Thread Robert Haas
On Fri, Jun 24, 2011 at 5:14 PM, Steve Singer ssinger...@sympatico.ca wrote:
 A few things I noticed (that you might be aware of since you mentioned it
 needs cleanup)

 -The patch doesn't compile with non-ssl builds,  the debug at the bottom of
 PQSendSome isn't in an #ifdef

 -I don't think your handling the return code properly.   Consider this case.

 pqSendSome(some data)
   sslRetryBuf = some Data
   return 1
 pqSendSome(more data)
   it sends all of 'some data'
   returns 0

 I think 1 should be returned because all of 'more data' still needs to be
 sent.  I think returning a 0 will break PQsetnonblocking if you call it when
 there is data in both sslRetryBuf and outputBuffer.
 We might even want to try sending the data in outputBuffer after we've sent
 all the data sitting in sslRetryBuf.


 If you close the connection with an outstanding sslRetryBuf you need to free
 it.

Based on these comments, I have updated the status of the patch to
Waiting on Author.

https://commitfest.postgresql.org/action/patch_view?id=594

-- 
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] heap_hot_search_buffer refactoring

2011-06-27 Thread Robert Haas
On Sat, Jun 25, 2011 at 6:24 AM, Jeff Davis pg...@j-davis.com wrote:
 On Fri, 2011-06-24 at 15:32 -0400, Robert Haas wrote:
 On Sun, Jun 19, 2011 at 2:16 PM, Robert Haas robertmh...@gmail.com wrote:
  New patch attached, with that one-line change.

 Jeff, are you planning to review this further?  Do you think it's OK to 
 commit?

 1. Patch does not apply to master cleanly, and it's in unified format
 (so I can't compare it against the old patch very easily). This review
 is for the first patch, disregarding the skip = !first_call issue that
 you already fixed. If you had other changes in the latest version,
 please repost the patch.

That is strange, because it applies for me.  But I had no other changes.

 2. Comment above heap_hot_search_buffer should be updated to document
 that heapTuple is an out-parameter and document the behavior of
 first_call

 3. The logic around skip is slightly confusing to me. Here's my
 description: if it's not an MVCC snapshot and it's not the first call,
 then you don't actually want to fetch the tuple with the given tid or a
 later one in the chain -- you want to fetch the _next_ tuple in the
 chain or a later one in the chain. Some wording of that description in a
 comment (either in the function's comment or near the use of skip)
 would help a lot. Also, if skip is true, then the tid _must_ be visible
 according to the (non-MVCC) snapshot, correct? It might help if that was
 apparent from the code/comments.

 Other than that, it looks good.

OK, I've applied this with some additional comment changes.

-- 
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] WIP: Fast GiST index build

2011-06-27 Thread Heikki Linnakangas

On 27.06.2011 13:45, Alexander Korotkov wrote:

I've added information about testing on some real-life dataset to wiki page.
This dataset have a speciality: data is ordered inside it. In this case
tradeoff was inverse in comparison with expectations about fast build
algrorithm. Index built is longer but index quality is significantly better.
I think high speed of regular index built is because sequential inserts are
into near tree parts. That's why number of actual page reads and writes is
low. The difference in tree quality I can't *convincingly explain now.*
I've also maked tests with shuffled data of this dataset. In this case
results was similar to random generated data.


Once again, interesting results.

The penalty function is called whenever a tuple is routed to the next 
level down, and the final tree has the same depth with and without the 
patch, so I would expect the number of penalty calls to be roughly the 
same. But clearly there's something wrong with that logic; can you 
explain in layman's terms why the patch adds so many gist penalty calls? 
And how many calls does it actually add, can you gather some numbers on 
that? Any ides on how to mitigate that, or do we just have to live with 
it? Or maybe use some heuristic to use the existing insertion method 
when the patch is not expected to be helpful?


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
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] Re: starting to review the Extend NOT NULL representation to pg_constraint patch

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 3:08 AM, Dean Rasheed dean.a.rash...@gmail.com wrote:
 On 27 June 2011 03:31, Robert Haas robertmh...@gmail.com wrote:
 On Sat, Jun 25, 2011 at 2:15 AM, Dean Rasheed dean.a.rash...@gmail.com 
 wrote:
 Really? I would expect the reverse, namely that the not-nullness is
 part of the PK constraint and dropping the PK *would* then start
 allowing NULLs.

 Hmm, OK.  I had assumed we were only trying to fix the problem that
 parent and child inheritance tables could get out of step, but maybe
 you're right.

 If we go with that approach, then consider:

 CREATE TABLE foo (a int);
 CREATE TABLE bar () INHERITS (foo);
 Now if someone adds a primary key foo (a), what happens currently is
 that foo.a becomes NOT NULL, but bar.a still allows NULLs.  Should
 that remain true (on the theory that a primary key constraint is not
 inherited) or become false (on the theory that parent and child tables
 should match)?


 I'm not sure, but my real problem with the current behaviour is its
 inconsistency. Consider this case:

 CREATE TABLE foo (a int PRIMARY KEY);
 CREATE TABLE bar () INHERITS (foo);

 Currently this results in bar not allowing NULLs, which is
 inconsistent with adding the PK after defining the inheritance. Then
 if the PK is dropped, the non-nullness is left behind on both foo and
 bar.

 I would summarise the consistency requirements as:

 1). ADD CONSTRAINT should leave both parent and child tables in the
 same state as they would have been if the constraint had been defined
 at table creation time.

 2). DROP CONSTRAINT should leave both parent and child tables in the
 same state as if the constraint had never existed (completely
 reversing the effects of ADD CONSTRAINT).

 I don't have a strong opinion as to whether or not the NOT NULL part
 of a PK should be inherited, provided that it is consistent with the
 above.

 I guess that if I were forced to choose, I would say that the NOT NULL
 part of a PK should not be inherited, since I do think of it as part
 of the PK, and PKs are not inherited.

OK, I see your point, and I agree with you.

-- 
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] Small 9.1 documentation fix (SSPI auth)

2011-06-27 Thread Robert Haas
On Fri, Jun 24, 2011 at 6:07 PM, Christian Ullrich ch...@chrullrich.net wrote:
 When Magnus fixed and applied my SSPI-via-GSS patch in January, we forgot to
 fix to the documentation. Suggested patch attached; should I also put that
 four-liner into any CFs?

I have committed a slightly different wording change to fix this
problem.  Let me know whether it looks OK...

-- 
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] spinlock contention

2011-06-27 Thread Robert Haas
On Sat, Jun 25, 2011 at 8:26 PM, Greg Stark st...@mit.edu wrote:
 On Thu, Jun 23, 2011 at 4:42 PM, Robert Haas robertmh...@gmail.com wrote:
 ProcArrayLock looks like a tougher nut to crack - there's simply no
 way, with the system we have right now, that you can take a snapshot
 without locking the list of running processes.  I'm not sure what to
 do about that, but we're probably going to have to come up with
 something, because it seems clear that once we eliminate the lock
 manager LWLock contention, this is a major bottleneck.

 Well as Tom observed earlier the kernel of a snapshot is actually a
 LSN. A snapshot contains a set of xids which all committed before some
 LSN and none which committed after it.

 So if we had a record of what log sequence number the commit record
 for any given transaction is we could build the snapshot at our
 leisure without any exclusive lock. In fact we could even build it
 lazily as a kind of cache only when we actually are interested in a
 given xid.

Yeah, I've been thinking about that.  I think what we might do is set
up a new SLRU that works like CLOG, but each entry is an LSN rather
than just two bits.  When a transaction commits, we save the commit
LSN under the entry for that XID.  We truncate away SLRU pages that
contain no still-running XIDs.  When we need to check whether an XID
is visible to our snapshot, we just look up the commit LSN and compare
it with our snapshot LSN.  If it's before and non-zero, we can see it.
 If it's after or all-zeroes, we can't.

But I'm not sure how much this would really help.  It might (subject
to working out the details) make the actual process of taking a
snapshot faster.  But it's not clear that taking snapshots more
quickly will actually help anything, because the problem is not the
amount of time spending taking the snapshot.  The problem is rather
that doing so requires acquiring and releasing an LWLock, and each of
those operations requires taking and releasing a spinlock.  And it is
the spinlock contention that is killing us.   That makes me think we
need a way to take a snapshot without taking a spinlock.  Or if we
must take spinlocks, we at least have to avoid every backend that
needs a snapshot lusting after the *same* spinlock.

What I've been thinking about this weekend is whether it might be
possible to create a sort of lock-free queue infrastructure.  When a
backend starts up, it would add an entry to the queue saying I'm
running.  When it commits, it would add an entry to the queue saying
I'm committed.  All entries would be added at the *end* of the
queue, so a backend scanning the queue to build up a snapshot wouldn't
ever be able to see commits out of order.  We would need some memory
barrier operations on weak-memory-ordering machines to make sure that
the queue writes became visible before the end-of-queue pointer bump.

The trick is figuring out how to clean up the queue.  Since commit
entries exist only to guard against running entries earlier in the
queue, the start-of-queue pointer can be advanced whenever it points
to a commit entry.  Also, if it points to a running entry for
which there is a later commit entry, then the start-of-queue pointer
can be advanced over that as well.  However, just because we can
advance the point at which backends start reading doesn't mean that we
can actually recycle space, because while we know that new scans
needn't worry about those entries, we *don't* know that there isn't
already a scan in flight that still needs them.  Furthermore, if a
transaction runs for a long time, we can never advance the
start-of-queue pointer past the running entry for its XID, which is
obviously problematic since the queue would get very long.

To work around that problem, I think we could use Florian's idea
upthread of an RCU system.  We keep two copies of the queue around, an
A copy and a B copy.  When the currently active copy fills up, we
rewrite it into the other queue, omitting all committed entries and
any running entries that have matching committed entries, and then
tell everyone to start looking at that copy instead.   We would need
some kind of gymnastics to make sure that we don't flip from the A
copy to the B copy and back to the A copy while some laggardly backend
is still hoping to scan the old A copy.  A simple algorithm (there's
probably a smarter one) would be to have each backend take a spinlock
while it's scanning either copy, and to have the backend that is doing
the rewrite take and release all of those spinlocks one at a time
before beginning the rewrite, thus guaranteeing that any scans still
in progress when the rewrite is requested have completed before it's
actually performed.  Any new scans started in the meanwhile will
certainly be looking at the current copy rather than the old copy
we're about to overwrite.

We would still need a lock around the operation of adding new items to
the queue; if two backends try to do that at the same time, chaos will
ensue.  But 

Re: [HACKERS] Deriving release notes from git commit messages

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 11:49 AM, Jonathan Corbet cor...@lwn.net wrote:
 On Fri, 24 Jun 2011 13:42:04 -0400
 Robert Haas robertmh...@gmail.com wrote:

 As for annotating the commit messages, I think something like:

 Reporter: Sam Jones
 Author: Beverly Smith
 Author: Jim Davids
 Reviewer: Fred Block
 Reviewer: Pauline Andrews

 Can I just toss in one little note from the sidelines?  Various other
 projects (Linux kernel at the top of the list) have adopted tags like
 Reported-by and Reviewed-by for metadata like this.  (Authorship lives in
 git itself, with additional authors sometimes ambiguously indicated with
 additional Signed-off-by lines).  There are tools out there which make use
 of those tags now.  It would seem that, in the absence of a reason to make
 up your own tags, it might make sense to be consistent with other projects?

I'm not averse to inventing our own tags that fit our particular
needs, but I don't think it would be a bad idea to maximize the
intersection of what we do with what other people do.

I think the biggest difference is probably that we (or at least I)
don't really like the idea of Signed-off-by, and certainly not as a
way of ambiguously indicating additional authors.  Many patches are
collaborative efforts, and the metadata should make that clear.

-- 
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] Word-smithing doc changes

2011-06-27 Thread Robert Haas
On Sat, Jun 25, 2011 at 9:01 PM, Greg Stark st...@mit.edu wrote:
 I think this commit was ill-advised:
 http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=a03feb9354bda5084f19cc952bc52ba7be89f372

     In a concurrent index build, the index is actually entered into the
     system catalogs in one transaction, then the two table scans occur in a
 -    second and third transaction.
 +    second and third transaction.  All active transactions at the time the
 +    second table scan starts, not just ones that already involve the table,
 +    have the potential to block the concurrent index creation until they
 +    finish.  When checking for transactions that could still use the original
 +    index, concurrent index creation advances through potentially interfering
 +    older transactions one at a time, obtaining shared locks on their virtual
 +    transaction identifiers to wait for them to complete.


 Seems way to implementation-specific and detailed for a user to make
 heads or tails of. Except in the sections talking about locking
 internals we don't talk about shared locks on virtual transactions
 identifiers we just talk about waiting for a transaction to complete.
 And looping over the transactions one by one is purely an
 implementation detail and uninteresting to users. Also it uses
 ill-defined terms like active transactions, potentially interfering
 older transactions, and  original index -- from the user's point of
 view there's only one index and it just isn't completely built yet.

 Are we not yet in string-freeze though? I'll go ahead and edit it if
 people don't mind. I'm curious to see the original complaint though.

We don't have a string freeze, and certainly not for the
documentation, so if you'd like to wordsmith some more, have at it.
But it would probably be best to post your revised version and solicit
feedback before committing, since there was quite a bit of discussion
about that change before it was made.  (Sorry, don't have the pointer
at the moment...)

-- 
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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Robert Haas
On Sun, Jun 26, 2011 at 10:33 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Robert Haas's message of vie jun 24 22:22:55 -0400 2011:
 On Fri, Jun 24, 2011 at 7:47 PM, Bruce Momjian br...@momjian.us wrote:

  You want the environment variable support removed?

 I don't.  It's production usefulness is questionable, but it's quite
 handy for testing IMO.

 If that's what you want, I think being able to read a file (whose
 filename you pass with a switch to pg_upgrade) with a bunch of settings
 is even more convenient.  Heck, maybe it's more convenient for the user
 too.

If someone wants to do the work, I'm all in favor.  But I don't feel
that we should insist that Bruce do it.

-- 
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] Deriving release notes from git commit messages

2011-06-27 Thread Jonathan Corbet
On Fri, 24 Jun 2011 13:42:04 -0400
Robert Haas robertmh...@gmail.com wrote:

 As for annotating the commit messages, I think something like:
 
 Reporter: Sam Jones
 Author: Beverly Smith
 Author: Jim Davids
 Reviewer: Fred Block
 Reviewer: Pauline Andrews

Can I just toss in one little note from the sidelines?  Various other
projects (Linux kernel at the top of the list) have adopted tags like
Reported-by and Reviewed-by for metadata like this.  (Authorship lives in
git itself, with additional authors sometimes ambiguously indicated with
additional Signed-off-by lines).  There are tools out there which make use
of those tags now.  It would seem that, in the absence of a reason to make
up your own tags, it might make sense to be consistent with other projects?

Thanks,

jon

-- 
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] Small 9.1 documentation fix (SSPI auth)

2011-06-27 Thread Christian Ullrich

* Robert Haas wrote:


On Fri, Jun 24, 2011 at 6:07 PM, Christian Ullrichch...@chrullrich.net  wrote:

When Magnus fixed and applied my SSPI-via-GSS patch in January, we forgot to
fix to the documentation. Suggested patch attached; should I also put that
four-liner into any CFs?



I have committed a slightly different wording change to fix this
problem.  Let me know whether it looks OK...


It does. Thanks for fixing.

--
Christian

--
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] Range Types and length function

2011-06-27 Thread Jeff Davis
On Mon, 2011-06-27 at 12:25 +0200, Florian Pflug wrote:
 Does the current definition of length(range), i.e.
   upper(range) - lower(range)
 deal correctly with open vs. closed ranges and unbounded ranges? I'm thinking
 that it probably doesn't - what would be the results of
   length('[0,1]'::intrange) -- Should be 2
   length('[0,1)'::intrange) -- Should be 1

I alluded to this problem in an earlier email.

I think this would need to be handled by the canonical function. If
the canonical function is specified to return values in [) or (] form,
then we'd get the behavior above.

However, it's a little strange, because for discrete ranges you probably
want cardinality, not length. I don't have a clear idea on exactly what
behavior users will expect in this case, which is a pretty good argument
to leave length() out.

   length('[0,inf]'::intrange) -- Should be infinity, but ints can't
  represent that, can't they?

That would throw an exception currently, for exactly the reason you
mention.

 If it cannot be easily made to support these cases, than I vote for
 removing it all together.

I now agree. I think you've brought up some good reasons for that. If
users write upper(r)-lower(r), then they know what the semantics will
be; or they can easily write their own length() function (perhaps
specific to a range type).

Regards,
Jeff Davis


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


[HACKERS] beta3?

2011-06-27 Thread Robert Haas
We have a couple of open items outstanding right now, but I'm
wondering if it's about time we should be thinking about a date for
beta3.

We tagged beta1 on April 27th, and beta2 on June 9th, so about six weeks apart.

But perhaps we shouldn't wait quite so long before putting out beta3?

-- 
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] Range Types, constructors, and the type system

2011-06-27 Thread Jeff Davis
On Mon, 2011-06-27 at 12:16 +0200, Florian Pflug wrote:
 I wouldn't take it that far. What I had in mind was to *only* support
 the case where the cast directly follows the function call, i.e. the case
   f(...)::type

OK, so instead of writing:
range(lower(range(1,2)),upper(range(1,2)))::int8range

users would write:
range(lower(range(1,2)::int8range),upper(range(1,2)::int8range))::int8range

A little more verbose, but it seems like it wouldn't be a practical
problem in very many cases. Multiple levels of constructors seem like
they'd be fairly uncommon, and probably a case where a function should
be written anyway.

OK, I'll have to think about this a little more, but it seems like a
reasonable approach.

Regards,
Jeff Davis


-- 
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] Range Types, constructors, and the type system

2011-06-27 Thread Jeff Davis
On Sun, 2011-06-26 at 22:29 -0700, Darren Duncan wrote:
 Tom Lane wrote:
  Darren Duncan dar...@darrenduncan.net writes:
  I believe that the best general solution here is for every ordered base 
  type to 
  just have a single total order, which is always used with that type in any 
  generic order-sensitive operation, including any ranges defined over it, 
  or any 
  ORDER BY or any ,,etc.
  
  We've spent years and blood on making sure that Postgres could support
  multiple orderings for any datatype; and there are plenty of natural
  examples for the usefulness of that.  So I'm not at all impressed by
  any line of reasoning that starts out by baldly throwing that away.
 
 I'm not saying that you can't use multiple orderings with a data type.  I'm 
 just 
 saying that the type only has *at most* one (possibly none) *native* 
 ordering, 
 which is what is used when you do something ordered-sensitive with the type, 
 such as have a range.

So, are you saying that it would be impossible to have a range that uses
a different ordering? What about ORDER BY? What about BTrees?

And if those things can use different orders for the same type, then
what is the difference between what you are suggesting and a default
ordering for the type (which we already support)?

I suppose it's hard to tell what you mean by native.

Regards,
Jeff Davis


-- 
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] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address

2011-06-27 Thread Robert Haas
On Wed, Jun 22, 2011 at 1:36 PM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Jun 22, 2011 at 12:51 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
 Excerpts from Robert Haas's message of mié jun 22 08:56:02 -0400 2011:

 Another option might be to leave heap_openrv() and relation_openrv()
 alone and add a missing_ok argument to try_heap_openrv() and
 try_relation_openrv().  Passing true would give the same behavior as
 presently; passing false would make them behave like the non-try
 version.

 That would be pretty weird, having two functions, one of them sometimes
 doing the same thing as the other one.

 I understand Noah's concern but I think your original proposal was saner
 than both options presented so far.

 I agree with you.  If we had a whole pile of options it might be worth
 having heap_openrv() and heap_openrv_extended() so as not to
 complicate the simple case, but since there's no forseeable need to
 add anything other than missing_ok, my gut is to just add it and call
 it good.

On further review, my gut is having second thoughts.  This patch is an
awful lot smaller and easier to verify correctness if I just mess with
the try calls and not the regular ones; and it avoids both
back-patching hazards for us and hoops for third-party loadable
modules that are using the non-try versions of those functions to jump
through.

Third try attached...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


there-is-no-try-v3.patch
Description: Binary data

-- 
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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Bruce Momjian
Robert Haas wrote:
 On Sun, Jun 26, 2011 at 10:33 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
  Excerpts from Robert Haas's message of vie jun 24 22:22:55 -0400 2011:
  On Fri, Jun 24, 2011 at 7:47 PM, Bruce Momjian br...@momjian.us wrote:
 
   You want the environment variable support removed?
 
  I don't. ?It's production usefulness is questionable, but it's quite
  handy for testing IMO.
 
  If that's what you want, I think being able to read a file (whose
  filename you pass with a switch to pg_upgrade) with a bunch of settings
  is even more convenient. ?Heck, maybe it's more convenient for the user
  too.
 
 If someone wants to do the work, I'm all in favor.  But I don't feel
 that we should insist that Bruce do it.

Is there agreement to remove all pg_upgrade-specific environment
variables?

-- 
  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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 1:39 PM, Bruce Momjian br...@momjian.us wrote:
 Robert Haas wrote:
 On Sun, Jun 26, 2011 at 10:33 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
  Excerpts from Robert Haas's message of vie jun 24 22:22:55 -0400 2011:
  On Fri, Jun 24, 2011 at 7:47 PM, Bruce Momjian br...@momjian.us wrote:
 
   You want the environment variable support removed?
 
  I don't. ?It's production usefulness is questionable, but it's quite
  handy for testing IMO.
 
  If that's what you want, I think being able to read a file (whose
  filename you pass with a switch to pg_upgrade) with a bunch of settings
  is even more convenient. ?Heck, maybe it's more convenient for the user
  too.

 If someone wants to do the work, I'm all in favor.  But I don't feel
 that we should insist that Bruce do it.

 Is there agreement to remove all pg_upgrade-specific environment
 variables?

I'm not in favor of that unless we have a workable replacement for 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


Re: [HACKERS] pg_upgrade defaulting to port 25432

2011-06-27 Thread Bruce Momjian
\Robert Haas wrote:
 On Mon, Jun 27, 2011 at 1:39 PM, Bruce Momjian br...@momjian.us wrote:
  Robert Haas wrote:
  On Sun, Jun 26, 2011 at 10:33 PM, Alvaro Herrera
  alvhe...@commandprompt.com wrote:
   Excerpts from Robert Haas's message of vie jun 24 22:22:55 -0400 2011:
   On Fri, Jun 24, 2011 at 7:47 PM, Bruce Momjian br...@momjian.us wrote:
  
You want the environment variable support removed?
  
   I don't. ?It's production usefulness is questionable, but it's quite
   handy for testing IMO.
  
   If that's what you want, I think being able to read a file (whose
   filename you pass with a switch to pg_upgrade) with a bunch of settings
   is even more convenient. ?Heck, maybe it's more convenient for the user
   too.
 
  If someone wants to do the work, I'm all in favor. ?But I don't feel
  that we should insist that Bruce do it.
 
  Is there agreement to remove all pg_upgrade-specific environment
  variables?
 
 I'm not in favor of that unless we have a workable replacement for them.

OK, fair enough.  Should I apply my ports patch to Postgres 9.2?

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


[HACKERS] generate_series() Interpretation

2011-06-27 Thread David E. Wheeler
Hackers,

I'm curious about behavior such as this:

bric=# select generate_series('2011-05-31'::timestamp , 
'2012-04-01'::timestamp, '1 month');
   generate_series   
-
 2011-05-31 00:00:00
 2011-06-30 00:00:00
 2011-07-30 00:00:00
 2011-08-30 00:00:00
 2011-09-30 00:00:00
 2011-10-30 00:00:00
 2011-11-30 00:00:00
 2011-12-30 00:00:00
 2012-01-30 00:00:00
 2012-02-29 00:00:00
 2012-03-29 00:00:00

It seems to me that this is subject to interpretation. If I was building a 
calendaring app, for example, I might rather that the results were:

   generate_series   
-
 2011-05-31 00:00:00
 2011-06-30 00:00:00
 2011-07-31 00:00:00
 2011-08-31 00:00:00
 2011-09-30 00:00:00
 2011-10-31 00:00:00
 2011-11-30 00:00:00
 2011-12-31 00:00:00
 2012-01-31 00:00:00
 2012-02-29 00:00:00
 2012-03-31 00:00:00

Is there some way to change the interpretation of interval calculation like 
this? Or would I just have to write my own function to do it the way I want?

Thanks,

David


-- 
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] beta3?

2011-06-27 Thread Josh Berkus
On 6/27/11 9:45 AM, Robert Haas wrote:
 We have a couple of open items outstanding right now, but I'm
 wondering if it's about time we should be thinking about a date for
 beta3.
 
 We tagged beta1 on April 27th, and beta2 on June 9th, so about six weeks 
 apart.
 
 But perhaps we shouldn't wait quite so long before putting out beta3?

I'd be up for July 11.  July 5 would be difficult, both because of the
American holiday, and Tom being on a trip.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

-- 
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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian br...@momjian.us wrote:
 OK, fair enough.  Should I apply my ports patch to Postgres 9.2?

I'm not sure which patch you are referring to.

-- 
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] beta3?

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 1:51 PM, Josh Berkus j...@agliodbs.com wrote:
 On 6/27/11 9:45 AM, Robert Haas wrote:
 We have a couple of open items outstanding right now, but I'm
 wondering if it's about time we should be thinking about a date for
 beta3.

 We tagged beta1 on April 27th, and beta2 on June 9th, so about six weeks 
 apart.

 But perhaps we shouldn't wait quite so long before putting out beta3?

 I'd be up for July 11.  July 5 would be difficult, both because of the
 American holiday, and Tom being on a trip.

That sounds reasonable to me.  I'll be on vacation then, but (1) I'm
not really involved in pushing the release out the door and (2) I
should have Internet access if push comes to shove.

-- 
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] generate_series() Interpretation

2011-06-27 Thread Steve Crawford

On 06/27/2011 10:49 AM, David E. Wheeler wrote:

Hackers,

I'm curious about behavior such as this:

bric=# select generate_series('2011-05-31'::timestamp , 
'2012-04-01'::timestamp, '1 month');
generate_series
-
  2011-05-31 00:00:00
  2011-06-30 00:00:00
  2011-07-30 00:00:00
  2011-08-30 00:00:00
  2011-09-30 00:00:00
  2011-10-30 00:00:00
  2011-11-30 00:00:00
  2011-12-30 00:00:00
  2012-01-30 00:00:00
  2012-02-29 00:00:00
  2012-03-29 00:00:00

It seems to me that this is subject to interpretation. If I was building a 
calendaring app, for example, I might rather that the results were:

generate_series
-
  2011-05-31 00:00:00
  2011-06-30 00:00:00
  2011-07-31 00:00:00
  2011-08-31 00:00:00
  2011-09-30 00:00:00
  2011-10-31 00:00:00
  2011-11-30 00:00:00
  2011-12-31 00:00:00
  2012-01-31 00:00:00
  2012-02-29 00:00:00
  2012-03-31 00:00:00

Is there some way to change the interpretation of interval calculation like 
this? Or would I just have to write my own function to do it the way I want?

Thanks,

David




That's just how intervals that represent varying periods of time work. 
You would need to write your own. But a series of end-of-month dates is 
pretty easy:
select generate_series('2011-06-01'::timestamp , 
'2012-04-01'::timestamp, '1 month') - '1 day'::interval;

  ?column?
-
 2011-05-31 00:00:00
 2011-06-30 00:00:00
 2011-07-31 00:00:00
 2011-08-31 00:00:00
 2011-09-30 00:00:00
 2011-10-31 00:00:00
 2011-11-30 00:00:00
 2011-12-31 00:00:00
 2012-01-31 00:00:00
 2012-02-29 00:00:00
 2012-03-31 00:00:00

Cheers,
Steve


--
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] generate_series() Interpretation

2011-06-27 Thread David E. Wheeler
On Jun 27, 2011, at 10:54 AM, Steve Crawford wrote:

 That's just how intervals that represent varying periods of time work. You 
 would need to write your own. But a series of end-of-month dates is pretty 
 easy:
 select generate_series('2011-06-01'::timestamp , '2012-04-01'::timestamp, '1 
 month') - '1 day'::interval;

Yeah, but it's trickier if you have a calendaring app and don't know that date 
a user has chosen for a monthly recurring event. They might have selected June 
30, in which case only February would ever need to be different than the 
default.

Best,

David


-- 
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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Bruce Momjian
Robert Haas wrote:
 On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian br...@momjian.us wrote:
  OK, fair enough. ?Should I apply my ports patch to Postgres 9.2?
 
 I'm not sure which patch you are referring to.

This one which makes 50432 the default port.

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

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 1ee2aca..5c5ce72
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*** output_check_banner(bool *live_check)
*** 29,34 
--- 29,37 
  	if (user_opts.check  is_server_running(old_cluster.pgdata))
  	{
  		*live_check = true;
+ 		if (old_cluster.port == DEF_PGUPORT)
+ 			pg_log(PG_FATAL, When checking a live old server, 
+    you must specify the old server's port number.\n);
  		if (old_cluster.port == new_cluster.port)
  			pg_log(PG_FATAL, When checking a live server, 
     the old and new port numbers must be different.\n);
diff --git a/contrib/pg_upgrade/option.c b/contrib/pg_upgrade/option.c
new file mode 100644
index 4401a81..d29aad0
*** a/contrib/pg_upgrade/option.c
--- b/contrib/pg_upgrade/option.c
*** parseCommandLine(int argc, char *argv[])
*** 58,65 
  	os_info.progname = get_progname(argv[0]);
  
  	/* Process libpq env. variables; load values here for usage() output */
! 	old_cluster.port = getenv(PGPORT) ? atoi(getenv(PGPORT)) : DEF_PGPORT;
! 	new_cluster.port = getenv(PGPORT) ? atoi(getenv(PGPORT)) : DEF_PGPORT;
  
  	os_user_effective_id = get_user_info(os_info.user);
  	/* we override just the database user name;  we got the OS id above */
--- 58,65 
  	os_info.progname = get_progname(argv[0]);
  
  	/* Process libpq env. variables; load values here for usage() output */
! 	old_cluster.port = getenv(PGPORTOLD) ? atoi(getenv(PGPORTOLD)) : DEF_PGUPORT;
! 	new_cluster.port = getenv(PGPORTNEW) ? atoi(getenv(PGPORTNEW)) : DEF_PGUPORT;
  
  	os_user_effective_id = get_user_info(os_info.user);
  	/* we override just the database user name;  we got the OS id above */
*** parseCommandLine(int argc, char *argv[])
*** 203,215 
  	}
  
  	/* Get values from env if not already set */
! 	check_required_directory(old_cluster.bindir, OLDBINDIR, -b,
  			old cluster binaries reside);
! 	check_required_directory(new_cluster.bindir, NEWBINDIR, -B,
  			new cluster binaries reside);
! 	check_required_directory(old_cluster.pgdata, OLDDATADIR, -d,
  			old cluster data resides);
! 	check_required_directory(new_cluster.pgdata, NEWDATADIR, -D,
  			new cluster data resides);
  }
  
--- 203,215 
  	}
  
  	/* Get values from env if not already set */
! 	check_required_directory(old_cluster.bindir, PGBINOLD, -b,
  			old cluster binaries reside);
! 	check_required_directory(new_cluster.bindir, PGBINNEW, -B,
  			new cluster binaries reside);
! 	check_required_directory(old_cluster.pgdata, PGDATAOLD, -d,
  			old cluster data resides);
! 	check_required_directory(new_cluster.pgdata, PGDATANEW, -D,
  			new cluster data resides);
  }
  
*** For example:\n\
*** 254,270 
  or\n), old_cluster.port, new_cluster.port, os_info.user);
  #ifndef WIN32
  	printf(_(\
!   $ export OLDDATADIR=oldCluster/data\n\
!   $ export NEWDATADIR=newCluster/data\n\
!   $ export OLDBINDIR=oldCluster/bin\n\
!   $ export NEWBINDIR=newCluster/bin\n\
$ pg_upgrade\n));
  #else
  	printf(_(\
!   C:\\ set OLDDATADIR=oldCluster/data\n\
!   C:\\ set NEWDATADIR=newCluster/data\n\
!   C:\\ set OLDBINDIR=oldCluster/bin\n\
!   C:\\ set NEWBINDIR=newCluster/bin\n\
C:\\ pg_upgrade\n));
  #endif
  	printf(_(\nReport bugs to pgsql-b...@postgresql.org.\n));
--- 254,270 
  or\n), old_cluster.port, new_cluster.port, os_info.user);
  #ifndef WIN32
  	printf(_(\
!   $ export PGDATAOLD=oldCluster/data\n\
!   $ export PGDATANEW=newCluster/data\n\
!   $ export PGBINOLD=oldCluster/bin\n\
!   $ export PGBINNEW=newCluster/bin\n\
$ pg_upgrade\n));
  #else
  	printf(_(\
!   C:\\ set PGDATAOLD=oldCluster/data\n\
!   C:\\ set PGDATANEW=newCluster/data\n\
!   C:\\ set PGBINOLD=oldCluster/bin\n\
!   C:\\ set PGBINNEW=newCluster/bin\n\
C:\\ pg_upgrade\n));
  #endif
  	printf(_(\nReport bugs to pgsql-b...@postgresql.org.\n));
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 613ddbd..4729ac3
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
***
*** 15,20 
--- 15,23 
  
  #include libpq-fe.h
  
+ /* Use port in the private/dynamic port number range */
+ #define DEF_PGUPORT			50432
+ 
  /* Allocate for null byte */
  #define USER_NAME_SIZE		128
  
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index b24c1e7..aa633e2
*** a/doc/src/sgml/pgupgrade.sgml
--- 

Re: [HACKERS] [COMMITTERS] pgsql: Make the visibility map crash-safe.

2011-06-27 Thread Robert Haas
On Thu, Jun 23, 2011 at 9:22 AM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Jun 22, 2011 at 10:23 PM, Robert Haas robertmh...@gmail.com wrote:
 Well, it seems I didn't put nearly enough thought into heap_update().
 The fix for the immediate problem looks simple enough - all the code
 has been refactored to use the new API, so the calls can be easily be
 moved into the critical section (see attached).  But looking at this a
 little more, I see that heap_update() is many bricks short of a load,
 because there are several places where the buffer can be unlocked and
 relocked, and we don't recheck whether the page is all-visible after
 reacquiring the lock.  So I've got some more work to do here.

 See what you think of the attached.  I *think* this covers all bases.
 It's a little more complicated than I would like, but I don't think
 fatally so.

For lack of comment, committed.  It's hopefully at least better than
what was there before, which was clearly several bricks short of a
load.

-- 
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] generate_series() Interpretation

2011-06-27 Thread Kevin Grittner
David E. Wheeler da...@kineticode.com wrote:
 
 generate_series   
 -
  2011-05-31 00:00:00
  2011-06-30 00:00:00
  2011-07-31 00:00:00
  2011-08-31 00:00:00
  2011-09-30 00:00:00
  2011-10-31 00:00:00
  2011-11-30 00:00:00
  2011-12-31 00:00:00
  2012-01-31 00:00:00
  2012-02-29 00:00:00
  2012-03-31 00:00:00
 
 Is there some way to change the interpretation of interval
 calculation like this? Or would I just have to write my own
 function to do it the way I want?
 
It is precisely to support such fancy things that some products
support a more abstract date type which allows 31 days in any month,
and then normalizes to real dates as needed.  The PostgreSQL
developer community has generally not been receptive to such use
cases.  I think you need to iterate through month intervals and add
those to the starting date for now.  If you want to start with the
last day of a month with less than 31 days, you may need to back up
a month or two to find a suitable month and offset your intervals by
the appropriate number of months.
 
I'd bet that if you encapsulate all that in a PostgreSQL function,
you're not the only one who would find it useful.
 
-Kevin

-- 
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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote:
 Robert Haas wrote:
 On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian br...@momjian.us wrote:
  OK, fair enough. ?Should I apply my ports patch to Postgres 9.2?

 I'm not sure which patch you are referring to.

 This one which makes 50432 the default port.

There appear to be some other changes mixed into this patch.

-- 
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] per-column generic option

2011-06-27 Thread Robert Haas
2011/6/27 Shigeru Hanada shigeru.han...@gmail.com:
 * It might be an option to extend attreloptions, instead of the new
 attfdwoptions.
 Although I didn't track the discussion when pg_foreign_table catalog
 that provides
 relation level fdw-options, was it impossible or unreasonable to extend 
 existing
 design of reloptions/attoptions?
 Right now, it accepts only hard-wired options listed at reloptions.c.
 But, it seems
 to me worthwhile, if it could accept options validated by loadable modules.

 IIRC someone has objected against storing FDW options in
 reloptions/attoptions, but I couldn't find such post.  I'll follow the
 discussion again.

I think they should definitely be separate.

-- 
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] generate_series() Interpretation

2011-06-27 Thread David E. Wheeler
On Jun 27, 2011, at 11:03 AM, Kevin Grittner wrote:

 It is precisely to support such fancy things that some products
 support a more abstract date type which allows 31 days in any month,
 and then normalizes to real dates as needed.  The PostgreSQL
 developer community has generally not been receptive to such use
 cases.  I think you need to iterate through month intervals and add
 those to the starting date for now.  If you want to start with the
 last day of a month with less than 31 days, you may need to back up
 a month or two to find a suitable month and offset your intervals by
 the appropriate number of months.
 
 I'd bet that if you encapsulate all that in a PostgreSQL function,
 you're not the only one who would find it useful.

Yeah, did that a while ago:

  
http://www.justatheory.com/computers/databases/postgresql/recurring_events.html

I think it could be simpler now, with generate_series() for some intervals.

Best,

David


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


[HACKERS] Commitfest 2001-06: 10 days in

2011-06-27 Thread Josh Berkus
All,

So we're supposedly 1/3 of the way through CF1.  Here's the good news:

- Almost all patches have reviewers assigned.
- 9 patches have been committed
- 8 more are ready for a committer
- 9 have been returned

This means that 1/4 of the patches have been dealt with and another 1/8
should be dealt with soon.

That sounds good until you realize that we're 1/3 of the way into the
commitfest -- at this point most of the patches should have been
reviewed, and half of them committed.  So we need to get cracking.  The
biggest overall holdup seems to be that very few reviews have been
turned in to -hackers.

I'll be emailing individual reviewers about their patches soon.

In the meantime, there's a couple of patch sets which need an advanced
pg hacker to review them.  I don't feel comfortable assigning them via
RRR to an intermediate-level contributor:

1) Robert Haas's vxid and less lwlocks patches:
https://commitfest.postgresql.org/action/patch_view?id=572
https://commitfest.postgresql.org/action/patch_view?id=585

2) Kaigai's security patches:
https://commitfest.postgresql.org/action/patch_view?id=550
https://commitfest.postgresql.org/action/patch_view?id=570
https://commitfest.postgresql.org/action/patch_view?id=571
https://commitfest.postgresql.org/action/patch_view?id=578

If you can help with any of these, please let me know!

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

-- 
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] Range Types, constructors, and the type system

2011-06-27 Thread Darren Duncan

Jeff Davis wrote:

On Sun, 2011-06-26 at 22:29 -0700, Darren Duncan wrote:

Tom Lane wrote:

Darren Duncan dar...@darrenduncan.net writes:
I believe that the best general solution here is for every ordered base type to 
just have a single total order, which is always used with that type in any 
generic order-sensitive operation, including any ranges defined over it, or any 
ORDER BY or any ,,etc.

We've spent years and blood on making sure that Postgres could support
multiple orderings for any datatype; and there are plenty of natural
examples for the usefulness of that.  So I'm not at all impressed by
any line of reasoning that starts out by baldly throwing that away.
I'm not saying that you can't use multiple orderings with a data type.  I'm just 
saying that the type only has *at most* one (possibly none) *native* ordering, 
which is what is used when you do something ordered-sensitive with the type, 
such as have a range.


So, are you saying that it would be impossible to have a range that uses
a different ordering? What about ORDER BY? What about BTrees?

And if those things can use different orders for the same type, then
what is the difference between what you are suggesting and a default
ordering for the type (which we already support)?

I suppose it's hard to tell what you mean by native.

Regards,
Jeff Davis


Maybe I'm just talking about default ordering then. -- Darren Duncan

--
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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Bruce Momjian
Robert Haas wrote:
 On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote:
  Robert Haas wrote:
  On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian br...@momjian.us wrote:
   OK, fair enough. ?Should I apply my ports patch to Postgres 9.2?
 
  I'm not sure which patch you are referring to.
 
  This one which makes 50432 the default port.
 
 There appear to be some other changes mixed into this patch.

The additional changes were to have the existing environment variables
begin with PG, as requested.

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


[HACKERS] SSI modularity questions

2011-06-27 Thread Kevin Grittner
There are two outstanding patches for SSI which involve questions
about modularity.  In particular, they involve calls to predicate
locking and conflict detection from executor source files rather
than AM source files (where most such calls exist).
 
(1)  Dan submitted this patch:
 
http://archives.postgresql.org/message-id/20110622045850.gn83...@csail.mit.edu
 
which is a very safe and very simple patch to improve performance on
sequential heap scans at the serializable transaction isolation
level.  The location of the code being modified raised questions
about modularity.  There is a reasonably clear place to which it
could be moved in the heap AM, but because it would acquire a
predicate lock during node setup, it would get a lock on the heap
even if the node was never used, which could be a performance
regression in some cases.
 
(2)  In reviewing the above, Heikki noticed that there was a second
place in the executor that SSI calls were needed but missing.  I
submitted a patch here:
 
http://archives.postgresql.org/message-id/4e07550f02250003e...@gw.wicourts.gov
 
I wonder, though, whether the section of code which I needed to
modify should be moved to a new function in heapam.c on modularity
grounds.
 
If these two places were moved, there would be no SSI calls from any
source file in the executor subdirectory.
 
Should these be moved before beta3?
 
-Kevin

-- 
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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian br...@momjian.us wrote:
 Robert Haas wrote:
 On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote:
  Robert Haas wrote:
  On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian br...@momjian.us wrote:
   OK, fair enough. ?Should I apply my ports patch to Postgres 9.2?
 
  I'm not sure which patch you are referring to.
 
  This one which makes 50432 the default port.

 There appear to be some other changes mixed into this patch.

 The additional changes were to have the existing environment variables
 begin with PG, as requested.

It's easier to read the patches if you do separate changes in separate
patches.  Anyway, I'm a bit nervous about this hunk:

+   if (old_cluster.port == DEF_PGUPORT)
+   pg_log(PG_FATAL, When checking a live old server, 
+  you must specify the old server's port 
number.\n);

Is the implication here that I'm now going to need to specify more
than 4 command-line options/environment variables for this to work?

-- 
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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Bruce Momjian
Robert Haas wrote:
 On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian br...@momjian.us wrote:
  Robert Haas wrote:
  On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote:
   Robert Haas wrote:
   On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian br...@momjian.us wrote:
OK, fair enough. ?Should I apply my ports patch to Postgres 9.2?
  
   I'm not sure which patch you are referring to.
  
   This one which makes 50432 the default port.
 
  There appear to be some other changes mixed into this patch.
 
  The additional changes were to have the existing environment variables
  begin with PG, as requested.
 
 It's easier to read the patches if you do separate changes in separate
 patches.  Anyway, I'm a bit nervous about this hunk:
 
 + if (old_cluster.port == DEF_PGUPORT)
 + pg_log(PG_FATAL, When checking a live old server, 
 +you must specify the old server's port 
 number.\n);
 
 Is the implication here that I'm now going to need to specify more
 than 4 command-line options/environment variables for this to work?

Yes, we don't inherit PGPORT anymore.  Doing anything else was too
complex to explain in the docs.

-- 
  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] WIP: Fast GiST index build

2011-06-27 Thread Alexander Korotkov
On Mon, Jun 27, 2011 at 6:34 PM, Heikki Linnakangas 
heikki.linnakan...@enterprisedb.com wrote:

 The penalty function is called whenever a tuple is routed to the next level
 down, and the final tree has the same depth with and without the patch, so I
 would expect the number of penalty calls to be roughly the same. But clearly
 there's something wrong with that logic; can you explain in layman's terms
 why the patch adds so many gist penalty calls? And how many calls does it
 actually add, can you gather some numbers on that? Any ides on how to
 mitigate that, or do we just have to live with it? Or maybe use some
 heuristic to use the existing insertion method when the patch is not
 expected to be helpful?

In short due to parralel routing of many index tuples routing can alter. In
fast build algorithm index tuples are accumulating into node buffers. When
corresponding node splits we have to repocate index tuples from it. In
original algorithm we are relocating node buffers into buffers of new nodes
produced by split. Even this requires additional penalty calls.
But for improvement of index quality I modified algorithm. With my
modification index tuple of splitted node buffer can be relocated also into
other node buffers of same parent. It produces more penalty calls.
I didn't have an estimate yet, but I'm working on it. Unfortunatelly, I
haven't any idea about mitigating it except turning off my modification.
Heuristic is possible, but I feel following problems. At first, we need to
somehow estimate length of varlena keys. I avoid this estimate in fast
algorithm itself just assumed worst case, but I believe we need some more
precise for good heuristic. At second, the right decision is strongly depend
on concurrent load. When there are no concurrent load (as in my experiments)
fraction of tree which fits to effective cache is reasonable for estimating
benefit of IO economy. But with high concurrent load part of cache occupied
by tree should be considerable smaller than whole effective cache.

--
With best regards,
Alexander Korotkov.


Re: [HACKERS] pg_upgrade defaulting to port 25432

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 2:27 PM, Bruce Momjian br...@momjian.us wrote:
 Robert Haas wrote:
 On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian br...@momjian.us wrote:
  Robert Haas wrote:
  On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote:
   Robert Haas wrote:
   On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian br...@momjian.us 
   wrote:
OK, fair enough. ?Should I apply my ports patch to Postgres 9.2?
  
   I'm not sure which patch you are referring to.
  
   This one which makes 50432 the default port.
 
  There appear to be some other changes mixed into this patch.
 
  The additional changes were to have the existing environment variables
  begin with PG, as requested.

 It's easier to read the patches if you do separate changes in separate
 patches.  Anyway, I'm a bit nervous about this hunk:

 +             if (old_cluster.port == DEF_PGUPORT)
 +                     pg_log(PG_FATAL, When checking a live old server, 
 +                                you must specify the old server's port 
 number.\n);

 Is the implication here that I'm now going to need to specify more
 than 4 command-line options/environment variables for this to work?

 Yes, we don't inherit PGPORT anymore.  Doing anything else was too
 complex to explain in the docs.

Seems like a usability regression.

-- 
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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Bruce Momjian
Bruce Momjian wrote:
 Robert Haas wrote:
  On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian br...@momjian.us wrote:
   Robert Haas wrote:
   On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote:
Robert Haas wrote:
On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian br...@momjian.us 
wrote:
 OK, fair enough. ?Should I apply my ports patch to Postgres 9.2?
   
I'm not sure which patch you are referring to.
   
This one which makes 50432 the default port.
  
   There appear to be some other changes mixed into this patch.
  
   The additional changes were to have the existing environment variables
   begin with PG, as requested.
  
  It's easier to read the patches if you do separate changes in separate
  patches.  Anyway, I'm a bit nervous about this hunk:
  
  +   if (old_cluster.port == DEF_PGUPORT)
  +   pg_log(PG_FATAL, When checking a live old server, 
  +  you must specify the old server's port 
  number.\n);
  
  Is the implication here that I'm now going to need to specify more
  than 4 command-line options/environment variables for this to work?
 
 Yes, we don't inherit PGPORT anymore.  Doing anything else was too
 complex to explain in the docs.

But only if you are running --check on a live server.  Otherwise, we
will just default to 50432 instead of 5432/PGPORT.

-- 
  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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 2:34 PM, Bruce Momjian br...@momjian.us wrote:
 Bruce Momjian wrote:
 Robert Haas wrote:
  On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian br...@momjian.us wrote:
   Robert Haas wrote:
   On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote:
Robert Haas wrote:
On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian br...@momjian.us 
wrote:
 OK, fair enough. ?Should I apply my ports patch to Postgres 9.2?
   
I'm not sure which patch you are referring to.
   
This one which makes 50432 the default port.
  
   There appear to be some other changes mixed into this patch.
  
   The additional changes were to have the existing environment variables
   begin with PG, as requested.
 
  It's easier to read the patches if you do separate changes in separate
  patches.  Anyway, I'm a bit nervous about this hunk:
 
  +           if (old_cluster.port == DEF_PGUPORT)
  +                   pg_log(PG_FATAL, When checking a live old server, 
  +                              you must specify the old server's port 
  number.\n);
 
  Is the implication here that I'm now going to need to specify more
  than 4 command-line options/environment variables for this to work?

 Yes, we don't inherit PGPORT anymore.  Doing anything else was too
 complex to explain in the docs.

 But only if you are running --check on a live server.  Otherwise, we
 will just default to 50432 instead of 5432/PGPORT.

Oh...

-- 
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] generate_series() Interpretation

2011-06-27 Thread Steve Crawford

On 06/27/2011 10:56 AM, David E. Wheeler wrote:

On Jun 27, 2011, at 10:54 AM, Steve Crawford wrote:


That's just how intervals that represent varying periods of time work. You 
would need to write your own. But a series of end-of-month dates is pretty easy:
select generate_series('2011-06-01'::timestamp , '2012-04-01'::timestamp, '1 
month') - '1 day'::interval;

Yeah, but it's trickier if you have a calendaring app and don't know that date 
a user has chosen for a monthly recurring event. They might have selected June 
30, in which case only February would ever need to be different than the 
default.

Best,

David



The query is marginally trickier. But the better calendaring apps give a 
variety of options when selecting repeat: A user who selects June 30, 
2011 and wants a monthly repeat might want:


30th of every month - skip months without a 30th
30th of every month - move to end-of-month if 30th doesn't exist
Last day of every month
Last Thursday of every month

Typical payday repeats are the 15th and last -day-of-month if a workday 
or the closest preceding workday if not, second and last Friday, 
every other Friday...


No matter how '1 month' is interpreted in generate_series, the 
application programmer will still need to write the queries required to 
handle whatever calendar-repeat features are deemed necessary.


Cheers,
Steve



--
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] generate_series() Interpretation

2011-06-27 Thread David E. Wheeler
On Jun 27, 2011, at 11:36 AM, Steve Crawford wrote:

 The query is marginally trickier. But the better calendaring apps give a 
 variety of options when selecting repeat: A user who selects June 30, 2011 
 and wants a monthly repeat might want:
 
 30th of every month - skip months without a 30th
 30th of every month - move to end-of-month if 30th doesn't exist
 Last day of every month
 Last Thursday of every month
 
 Typical payday repeats are the 15th and last -day-of-month if a workday or 
 the closest preceding workday if not, second and last Friday, every other 
 Friday...
 
 No matter how '1 month' is interpreted in generate_series, the application 
 programmer will still need to write the queries required to handle whatever 
 calendar-repeat features are deemed necessary.

Yeah, which is why I said it was subject to interpretation. Of course there's 
no way to tell generate_series() which to use, which is what I figured.

Thanks,

David



-- 
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] Range Types, constructors, and the type system

2011-06-27 Thread Robert Haas
On Sat, Jun 25, 2011 at 6:29 PM, Jeff Davis pg...@j-davis.com wrote:
 Different ranges over the same subtype make sense when using different
 total orders for the subtype. This is most apparent with text collation,
 but makes sense (at least mathematically, if not practically) for any
 subtype.

 For instance:
  [a, Z)
 is a valid range in en_US, but not in C, so it makes sense to have
 multiple ranges over the same subtype with different collations.

 But what if you have a function (like a constructor), of the form:
  (anyelement, anyelement) - anyrange
 ? To work with the type system, you need to be able to figure out the
 return type from the arguments; which means to support functions like
 this we need a mapping from the subtype to the range type.
 Unfortunately, that restricts us to one range type per subtype (this
 isn't a problem for ARRAYs, because there is only one useful array type
 for a given element type).

 This problem first came up a while ago:
 http://archives.postgresql.org/pgsql-hackers/2011-01/msg02788.php

 My workaround was to use domains, but that's not a very clean solution
 (you have to add a bunch of casts to make sure the right domain is
 chosen). It became entirely unworkable with collations, because people
 would be using different text collations a lot more frequently than,
 say, a different ordering for timestamptz. Tom mentioned that here:

 http://archives.postgresql.org/message-id/24831.1308579...@sss.pgh.pa.us

 I think Florian proposed the most promising line of attack here:

 http://archives.postgresql.org/message-id/ad4fc75d-db99-48ed-9082-52ee3a4d7...@phlo.org

 by suggesting that functions of the form:
  (anyelement, [other non-anyrange arguments]) - anyrange
 might be expendable. After all, they are only useful for constructors as
 far as we can tell. Other range functions will have an anyrange
 parameter, and we can use the actual type of the argument to know the
 range type (as well as the subtype).

 Although it's very nice to be able to say:
  range(1,10)
 and get an int4range out of it, it's not the only way, and it's not
 without its problems anyway. For instance, to get an int8range you have
 to do:
  range(1::int8, 10::int8)
 or similar.

 So, we could just make functions like:
  int4range(int4, int4)
  int8range(int8, int8)
  ...
 when creating the range type, and it would actually be a usability
 improvement.

Couldn't we also do neither of these things?  I mean, presumably
'[1,10]'::int8range had better work.

I'm not saying that's ideal from a usability perspective but I fear
this patch is going to be unmanageably large, and separating out the
things that you need for it to work at all from the things that you
need in order for it to be convenient might have some merit.

-- 
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] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address

2011-06-27 Thread Noah Misch
On Mon, Jun 27, 2011 at 01:28:30PM -0400, Robert Haas wrote:
 On Wed, Jun 22, 2011 at 1:36 PM, Robert Haas robertmh...@gmail.com wrote:
  I agree with you. ?If we had a whole pile of options it might be worth
  having heap_openrv() and heap_openrv_extended() so as not to
  complicate the simple case, but since there's no forseeable need to
  add anything other than missing_ok, my gut is to just add it and call
  it good.
 
 On further review, my gut is having second thoughts.  This patch is an
 awful lot smaller and easier to verify correctness if I just mess with
 the try calls and not the regular ones; and it avoids both
 back-patching hazards for us and hoops for third-party loadable
 modules that are using the non-try versions of those functions to jump
 through.

+1.  (Note that the function header comments need a few more updates.)

-- 
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] generate_series() Interpretation

2011-06-27 Thread Michael Nolan
On Mon, Jun 27, 2011 at 1:38 PM, David E. Wheeler da...@kineticode.comwrote:


 Yeah, which is why I said it was subject to interpretation. Of course
 there's no way to tell generate_series() which to use, which is what I
 figured.


generate_series() is doing exactly what it was designed to do, the
imprecision regarding adding '1 month' to something that may or may not have
been intended to be 'last day of the month' is a limitation in the interval
code.

One way to change this would be to implement another interval type such as
'full_month'  which would take a date that is know to be the last day of the
month and make it the last day of the appropriate month.  If the starting
date is NOT the last day of a month, the existing logic would suffice.

Or you can do as I have done and create your own last_day() function that
takes any date and makes it the last day of that month, and apply it to the
output of generate_series();
--
Mike Nolan
no...@tssi.com


Re: [HACKERS] generate_series() Interpretation

2011-06-27 Thread Steve Crawford



Yeah, which is why I said it was subject to interpretation. Of course there's 
no way to tell generate_series() which to use, which is what I figured.

Fortunately PostgreSQL uses the same interpretation for '1 month'  when 
used in generate_series that it does everywhere else - to do otherwise 
would be hella confusing. :)


Cheers,
Steve


--
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] generate_series() Interpretation

2011-06-27 Thread Christopher Browne
On Mon, Jun 27, 2011 at 1:49 PM, David E. Wheeler da...@kineticode.com wrote:
 Hackers,

 I'm curious about behavior such as this:

 bric=# select generate_series('2011-05-31'::timestamp , 
 '2012-04-01'::timestamp, '1 month');
   generate_series
 -
  2011-05-31 00:00:00
  2011-06-30 00:00:00
  2011-07-30 00:00:00
  2011-08-30 00:00:00
  2011-09-30 00:00:00
  2011-10-30 00:00:00
  2011-11-30 00:00:00
  2011-12-30 00:00:00
  2012-01-30 00:00:00
  2012-02-29 00:00:00
  2012-03-29 00:00:00

 It seems to me that this is subject to interpretation. If I was building a 
 calendaring app, for example, I might rather that the results were:

   generate_series
 -
  2011-05-31 00:00:00
  2011-06-30 00:00:00
  2011-07-31 00:00:00
  2011-08-31 00:00:00
  2011-09-30 00:00:00
  2011-10-31 00:00:00
  2011-11-30 00:00:00
  2011-12-31 00:00:00
  2012-01-31 00:00:00
  2012-02-29 00:00:00
  2012-03-31 00:00:00

 Is there some way to change the interpretation of interval calculation like 
 this? Or would I just have to write my own function to do it the way I want?

It's not hugely difficult to get something pretty appropriate:

emp@localhost-  select generate_series('2011-06-01'::timestamp ,
'2012-04-01'::timestamp, '1 month')- '1 day' ::interval;
  ?column?
-
 2011-05-31 00:00:00
 2011-06-30 00:00:00
 2011-07-31 00:00:00
 2011-08-31 00:00:00
 2011-09-30 00:00:00
 2011-10-31 00:00:00
 2011-11-30 00:00:00
 2011-12-31 00:00:00
 2012-01-31 00:00:00
 2012-02-29 00:00:00
 2012-03-31 00:00:00
(11 rows)

That's more or less a bit of cleverness.  But it's not so grossly
clever as to seem too terribly frightful.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, How would the Lone Ranger handle this?

-- 
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] generate_series() Interpretation

2011-06-27 Thread Christopher Browne
On Mon, Jun 27, 2011 at 2:36 PM, Steve Crawford
scrawf...@pinpointresearch.com wrote:
 On 06/27/2011 10:56 AM, David E. Wheeler wrote:

 On Jun 27, 2011, at 10:54 AM, Steve Crawford wrote:

 That's just how intervals that represent varying periods of time work.
 You would need to write your own. But a series of end-of-month dates is
 pretty easy:
 select generate_series('2011-06-01'::timestamp , '2012-04-01'::timestamp,
 '1 month') - '1 day'::interval;

 Yeah, but it's trickier if you have a calendaring app and don't know that
 date a user has chosen for a monthly recurring event. They might have
 selected June 30, in which case only February would ever need to be
 different than the default.

 Best,

 David



 The query is marginally trickier. But the better calendaring apps give a
 variety of options when selecting repeat: A user who selects June 30, 2011
 and wants a monthly repeat might want:

 30th of every month - skip months without a 30th
 30th of every month - move to end-of-month if 30th doesn't exist
 Last day of every month
 Last Thursday of every month

 Typical payday repeats are the 15th and last -day-of-month if a workday or
 the closest preceding workday if not, second and last Friday, every
 other Friday...

 No matter how '1 month' is interpreted in generate_series, the application
 programmer will still need to write the queries required to handle whatever
 calendar-repeat features are deemed necessary.

If you look up David Skoll's remind application
http://www.roaringpenguin.com/products/remind, you'll find something
that does this kind of pattern matching quite, quite well, at a rather
sophisticated level.

I find that I don't want to go through the struggle of figuring out
how to correctly describe those recurrences.

The other way of doing this sort of thing, which seems to be generally
more intuitive, is to treat these calendars as sets, which are a
structure that SQL is rather will designed to manipulate, and use
inclusions/exclusions/intersections to determine which days are of
interest.

I wrote something on this on pgsql-general about 5 years ago that
still seems pretty relevant.

http://archives.postgresql.org/pgsql-general/2006-02/msg00159.php
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, How would the Lone Ranger handle this?

-- 
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] Avoid index rebuilds for no-rewrite ALTER TABLE ALTER TYPE

2011-06-27 Thread Robert Haas
On Wed, Jun 15, 2011 at 1:03 AM, Noah Misch n...@leadboat.com wrote:
 [patch to avoid index rebuilds]

With respect to the documentation hunks, it seems to me that the first
hunk might be made clearer by leaving the paragraph of which it is a
part as-is, and adding another paragraph afterwards beginning with the
words In addition.  I am not sure whether the second hunk is
necessary at all.  Doesn't the existing language cover the same
territory as what you've added?

I think that the variables in ATPostAlterTypeCleanup() could be better
named.  They appear to be values, when in fact they are ListCells.
Honestly I'd probably just use l1 and l2, but if you want to insist on
some more mnemonic naming it should probably be something that sounds
vaguely list-ish.

As you no doubt expected, my eyes was immediately drawn to the
index-resurrection hack.  Reviewing the thread, I see that you asked
about that in January and never got feedback.  I have to say that what
you've done here looks like a pretty vile hack, but it's hard to say
for sure without knowing what to compare it against.  You made
reference to this being smaller and simpler than updating the index
definition in place - can you give a sketch of what would need to be
done if we went that route instead?

-- 
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] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 2:59 PM, Noah Misch n...@leadboat.com wrote:
 On Mon, Jun 27, 2011 at 01:28:30PM -0400, Robert Haas wrote:
 On Wed, Jun 22, 2011 at 1:36 PM, Robert Haas robertmh...@gmail.com wrote:
  I agree with you. ?If we had a whole pile of options it might be worth
  having heap_openrv() and heap_openrv_extended() so as not to
  complicate the simple case, but since there's no forseeable need to
  add anything other than missing_ok, my gut is to just add it and call
  it good.

 On further review, my gut is having second thoughts.  This patch is an
 awful lot smaller and easier to verify correctness if I just mess with
 the try calls and not the regular ones; and it avoids both
 back-patching hazards for us and hoops for third-party loadable
 modules that are using the non-try versions of those functions to jump
 through.

 +1.  (Note that the function header comments need a few more updates.)

Oh, good catch, thanks.  Committed with some further comment changes.

-- 
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] generate_series() Interpretation

2011-06-27 Thread David E. Wheeler
On Jun 27, 2011, at 12:36 PM, Christopher Browne wrote:

 I wrote something on this on pgsql-general about 5 years ago that
 still seems pretty relevant.
 
 http://archives.postgresql.org/pgsql-general/2006-02/msg00159.php

iwantsandy.com (now defunct) originally had a solution like this. However it 
supported a slew of recurrences:

* hours
* 2xday
* days
* weeks
* months
* quarters
* years
* decades

We had materializations of all of these going out 5 years or so. It took up an 
incredible amount of database space and was really slow. I replaced it with a 
variation on the code described in this blog post:

  
http://www.justatheory.com/computers/databases/postgresql/recurring_events.html

The database was a fraction of the original size and, because views were 
usually limited to a month at most, the number of rows generated for a query to 
show recurring events was quite limited (no one had an hourly reminder that 
when for more than a couple of days). Queries were a lot faster, too.

So I think the materialization of dates can work in certain limited cases such 
as your vacations 2005 example, and will be easier to use thanks to JOINs, I 
found that it performed poorly and was unnecessarily resource-intensive for our 
usage. And I suspect the same would be try for anyone building a calendar app 
with more than one simple kind of limited recurrence.

Best,

David


-- 
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] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address

2011-06-27 Thread Kohei KaiGai
The attached patch is rebased one towards the latest tree, using
relation_openrv_extended().

Although it is not a matter in this patch itself, I found a problem on
the upcoming patch
that consolidate routines associated with DropStmt.
Existing RemoveRelations() acquires a lock on the table owning an
index to be removed
in the case when OBJECT_INDEX is supplied.
However, the revised get_object_address() opens the supplied relation
(= index) in same
time with lookup of its name. So, we may break down the
relation_openrv_extended()
into a pair of RangeVarGetRelid() and relation_open().

Any good idea?

2011/6/27 Robert Haas robertmh...@gmail.com:
 On Mon, Jun 27, 2011 at 2:59 PM, Noah Misch n...@leadboat.com wrote:
 On Mon, Jun 27, 2011 at 01:28:30PM -0400, Robert Haas wrote:
 On Wed, Jun 22, 2011 at 1:36 PM, Robert Haas robertmh...@gmail.com wrote:
  I agree with you. ?If we had a whole pile of options it might be worth
  having heap_openrv() and heap_openrv_extended() so as not to
  complicate the simple case, but since there's no forseeable need to
  add anything other than missing_ok, my gut is to just add it and call
  it good.

 On further review, my gut is having second thoughts.  This patch is an
 awful lot smaller and easier to verify correctness if I just mess with
 the try calls and not the regular ones; and it avoids both
 back-patching hazards for us and hoops for third-party loadable
 modules that are using the non-try versions of those functions to jump
 through.

 +1.  (Note that the function header comments need a few more updates.)

 Oh, good catch, thanks.  Committed with some further comment changes.

 --
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company




-- 
KaiGai Kohei kai...@kaigai.gr.jp


pgsql-v9.2-drop-reworks-part-0.v5.patch
Description: Binary data

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


[HACKERS] Dry Run mode for pg_archivecleanup

2011-06-27 Thread Gabriele Bartolini

Hi guys,

   I have added the '-n' option to pg_archivecleanup which performs a 
dry-run and outputs the names of the files to be removed to stdout 
(making possible to pass the list via pipe to another process). Please 
find attached the small patch.


Thanks,
Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it

From 25fcf05ff787dae2b0d62de423a363f8597f1d42 Mon Sep 17 00:00:00 2001
From: Gabriele Bartolini gabriele.bartol...@2ndquadrant.it
Date: Mon, 27 Jun 2011 12:27:09 +0200
Subject: [PATCH] Add dryrun option to pg_archivecleanup

---
 contrib/pg_archivecleanup/pg_archivecleanup.c |   13 -
 doc/src/sgml/pgarchivecleanup.sgml|9 +
 2 files changed, 21 insertions(+), 1 deletions(-)

diff --git a/contrib/pg_archivecleanup/pg_archivecleanup.c 
b/contrib/pg_archivecleanup/pg_archivecleanup.c
index dd8a451..0bbec92 100644
--- a/contrib/pg_archivecleanup/pg_archivecleanup.c
+++ b/contrib/pg_archivecleanup/pg_archivecleanup.c
@@ -36,6 +36,7 @@ const char *progname;
 
 /* Options and defaults */
 bool   debug = false;  /* are we debugging? */
+bool   dryrun = false; /* are we performing a dry-run 
operation? */
 
 char  *archiveLocation;/* where to find the archive? */
 char  *restartWALFileName; /* the file from which we can restart restore */
@@ -123,6 +124,12 @@ CleanupPriorWALFiles(void)
fprintf(stderr, %s: removing file 
\%s\\n,
progname, WALFilePath);
 
+   if (dryrun) {
+   /* Prints the name of the file and
+* skips the actual removal of the file 
*/
+   fprintf(stdout, %s\n, WALFilePath);
+   continue;
+   }
rc = unlink(WALFilePath);
if (rc != 0)
{
@@ -205,6 +212,7 @@ usage(void)
printf(  %s [OPTION]... ARCHIVELOCATION OLDESTKEPTWALFILE\n, 
progname);
printf(\nOptions:\n);
printf(  -d generates debug output (verbose mode)\n);
+   printf(  -n shows the names of the files that would 
have been removed (dry-run)\n);
printf(  --help show this help, then exit\n);
printf(  --version  output version information, then exit\n);
printf(\n
@@ -241,13 +249,16 @@ main(int argc, char **argv)
}
}
 
-   while ((c = getopt(argc, argv, d)) != -1)
+   while ((c = getopt(argc, argv, dn)) != -1)
{
switch (c)
{
case 'd':   /* Debug mode */
debug = true;
break;
+   case 'n':   /* Dry-Run mode */
+   dryrun = true;
+   break;
default:
fprintf(stderr, Try \%s --help\ for more 
information.\n, progname);
exit(2);
diff --git a/doc/src/sgml/pgarchivecleanup.sgml 
b/doc/src/sgml/pgarchivecleanup.sgml
index ddffa32..8148c53 100644
--- a/doc/src/sgml/pgarchivecleanup.sgml
+++ b/doc/src/sgml/pgarchivecleanup.sgml
@@ -98,6 +98,15 @@ pg_archivecleanup:  removing file 
archive/00010037000E
   /listitem
  /varlistentry
 
+ varlistentry
+  termoption-n/option/term
+  listitem
+   para
+Print the names of the files that would have been removed on 
filenamestdout/ (performs a dry run).
+   /para
+  /listitem
+ /varlistentry
+
 /variablelist
/para
 
-- 
1.7.4.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] pg_upgrade defaulting to port 25432

2011-06-27 Thread Peter Eisentraut
On mån, 2011-06-27 at 14:34 -0400, Bruce Momjian wrote:
 Bruce Momjian wrote:
  Robert Haas wrote:
   It's easier to read the patches if you do separate changes in separate
   patches.  Anyway, I'm a bit nervous about this hunk:
   
   + if (old_cluster.port == DEF_PGUPORT)
   + pg_log(PG_FATAL, When checking a live old 
   server, 
   +you must specify the old server's 
   port number.\n);
   
   Is the implication here that I'm now going to need to specify more
   than 4 command-line options/environment variables for this to work?
  
  Yes, we don't inherit PGPORT anymore.  Doing anything else was too
  complex to explain in the docs.
 
 But only if you are running --check on a live server.  Otherwise, we
 will just default to 50432 instead of 5432/PGPORT.

When checking a live server, the built-in default port number or the
value of the environment variable PGPORT is used.  But when performing
an upgrade, a different port number is used by default, namely 50432,
which can be overridden XXX [how?]

Seems pretty clear to me, as long as that last bit is figured out.



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


[HACKERS] add support for logging current role (what to review?)

2011-06-27 Thread Alex Hunsaker
Ive been holding off because its marked as Waiting on Author, am now
thinking thats a mistake. =)

It links to this patch:
http://archives.postgresql.org/message-id/20110215135131.gx4...@tamriel.snowman.net

Which is older than the latest patch in that thread posted by Robert:
http://archives.postgresql.org/message-id/AANLkTikMadttguOWTkKLtgfe90kxR=u9njk9zebrw...@mail.gmail.com

(There are also various other patches and versions in that thread...)

The main difference between the first and the last patch is the first
one has support for changing what csv columns we output, while the
latter just tacks on an additional column.

The thread was very long and left me a bit confused as to what I
should actually be looking at. Or perhaps thats the point-- we need to
decide if a csvlog_fields GUC is worth it.

-- 
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] per-column generic option

2011-06-27 Thread David Fetter
On Fri, Jun 17, 2011 at 05:59:31AM -0700, David Fetter wrote:
 On Fri, Jun 17, 2011 at 07:19:39PM +0900, Shigeru Hanada wrote:

   Here's an example of a non-trivial mapping.
   
   Database type:
MySQL
   Foreign data type:
datetime
   PostgreSQL data type:
timestamptz
   Transformation direction:
Import
   Transformation:
CASE
WHEN DATA = '-00-00 00:00:00'
THEN NULL
ELSE DATA
END
   
   Here, I'm making the simplifying assumption that there is a bijective
   mapping between data types.

Any word on this?

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] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address

2011-06-27 Thread Robert Haas
On Mon, Jun 27, 2011 at 4:40 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote:
 The attached patch is rebased one towards the latest tree, using
 relation_openrv_extended().

Committed.

 Although it is not a matter in this patch itself, I found a problem on
 the upcoming patch
 that consolidate routines associated with DropStmt.
 Existing RemoveRelations() acquires a lock on the table owning an
 index to be removed
 in the case when OBJECT_INDEX is supplied.
 However, the revised get_object_address() opens the supplied relation
 (= index) in same
 time with lookup of its name. So, we may break down the
 relation_openrv_extended()
 into a pair of RangeVarGetRelid() and relation_open().

Not without looking at the patch.  I will respond on that thread when
I've read through it more thoroughly.

-- 
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] minor patch submission: CREATE CAST ... AS EXPLICIT

2011-06-27 Thread Brendan Jurd
On 18 June 2011 09:49, Brendan Jurd dire...@gmail.com wrote:
 Hi Fabien,

 I'm taking a look at this patch for the commitfest.  On first reading
 of the patch, it looked pretty sensible to me, but I had some trouble
 applying it to HEAD:

 error: patch failed: doc/src/sgml/ref/create_cast.sgml:20
 error: doc/src/sgml/ref/create_cast.sgml: patch does not apply
 error: patch failed: src/backend/parser/gram.y:499
 error: src/backend/parser/gram.y: patch does not apply
 error: patch failed: src/include/parser/kwlist.h:148
 error: src/include/parser/kwlist.h: patch does not apply
 error: patch failed: src/test/regress/expected/create_cast.out:27
 error: src/test/regress/expected/create_cast.out: patch does not apply
 error: patch failed: src/test/regress/sql/create_cast.sql:27
 error: src/test/regress/sql/create_cast.sql: patch does not apply

 Perhaps the patch could use a refresh?

The author has yet to reply to the above -- we are still lacking a
patch version that applies cleanly to HEAD.  I have marked this patch
'Waiting on Author'.

Cheers,
BJ

-- 
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] Avoid index rebuilds for no-rewrite ALTER TABLE ALTER TYPE

2011-06-27 Thread Noah Misch
On Mon, Jun 27, 2011 at 03:45:43PM -0400, Robert Haas wrote:
 On Wed, Jun 15, 2011 at 1:03 AM, Noah Misch n...@leadboat.com wrote:
  [patch to avoid index rebuilds]
 
 With respect to the documentation hunks, it seems to me that the first
 hunk might be made clearer by leaving the paragraph of which it is a
 part as-is, and adding another paragraph afterwards beginning with the
 words In addition.

The added restriction elaborates on the transitivity requirement, so I wanted to
keep the new language adjacent to that.

 I am not sure whether the second hunk is
 necessary at all.  Doesn't the existing language cover the same
 territory as what you've added?

The first hunk updates the contract for btree families, and the second updates
the contract for hash families.  I kept the second instance a bit terse since it
follows soon after the similar text for B-tree.

 I think that the variables in ATPostAlterTypeCleanup() could be better
 named.  They appear to be values, when in fact they are ListCells.
 Honestly I'd probably just use l1 and l2, but if you want to insist on
 some more mnemonic naming it should probably be something that sounds
 vaguely list-ish.

Okay; I'll do that in the next version.  Either l1/l2 or maybe oid_item/def_item
like we use in postgres.c.

 As you no doubt expected, my eyes was immediately drawn to the
 index-resurrection hack.  Reviewing the thread, I see that you asked
 about that in January and never got feedback.  I have to say that what
 you've done here looks like a pretty vile hack, but it's hard to say
 for sure without knowing what to compare it against.  You made
 reference to this being smaller and simpler than updating the index
 definition in place - can you give a sketch of what would need to be
 done if we went that route instead?

In at7-index-opfamily.patch attached to
http://archives.postgresql.org/message-id/20110113230124.ga18...@tornado.gateway.2wire.net
check out the code following the comment /* The old index is compatible.
Update catalogs. */ until the end of the function.  That code would need
updates for per-column collations, and it incorrectly reuses
values/nulls/replace arrays.  It probably does not belong in tablecmds.c,
either.  However, it gives the right general outline.

It would be valuable to avoid introducing a second chunk of code that knows
everything about the catalog entries behind an index.  That's what led me to the
put forward the most recent version as best.  What do you find vile about that
approach?  I wasn't comfortable with it at first, because I suspected the checks
in RelationPreserveStorage() might be important for correctness.  Having studied
it some more, though, I think they just reflect the narrower needs of its
current sole user.

Thanks,
nm

-- 
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] Range Types, constructors, and the type system

2011-06-27 Thread Jeff Davis
On Mon, 2011-06-27 at 14:50 -0400, Robert Haas wrote:
 Couldn't we also do neither of these things?  I mean, presumably
 '[1,10]'::int8range had better work.

I think that if we combine this idea with Florian's PAIR suggestion
here:
http://archives.postgresql.org/message-id/ad4fc75d-db99-48ed-9082-52ee3a4d7...@phlo.org

then I think we have a solution.

If we add a type RANGEINPUT that is not a pseudotype, we can use that as
an intermediate type that is returned by range constructors. Then, we
add casts from RANGEINPUT to each range type. That would allow
  range(1,2)::int8range
to work without changing the type system around, because range() would
have the signature:
  range(ANYELEMENT, ANYELEMENT) - RANGEINPUT
and then the cast would change it into an int8range. But we only need
the one cast per range type, and we can also support all of the other
kinds of constructors like:
  range_cc(ANYELEMENT, ANYELEMENT) - RANGEINPUT
  range_linf_c(ANYELEMENT) - RANGEINPUT
without additional hassle.

The RANGEINPUT type itself would hold similar information to actual
range types: the subtype OID (instead of the range type, because it's
not a range yet), optionally the two bounds (depending on the flags),
and the flags byte. The cast to a real range type would read the
subtype, and try to coerce the bounds to the subtype of the range you're
casting to, set the range type oid, leave the flags byte the same, and
it's done.

So, in effect, RANGEINPUT is a special type used only for range
constructors. If someone tried to output it, it would throw an
exception, and we'd even have enough information at that point to print
a nice error message with a hint.

Actually, this is pretty much exactly Florian's idea (thanks again,
Florian), but at the time I didn't like it because pair didn't capture
everything that I wanted to capture, like infinite bounds, etc. But
there's no reason that it can't, and your point made me realize that --
you are effectively just using TEXT as the intermediate type (which
works, but has some undesirable characteristics).

Do we think that this is a good way forward? The only thing I can think
of that's undesirable is that it's not normal to be required to cast the
result of a function, and might be slightly difficult to explain in the
documentation in a straightforward way.

Regards,
Jeff Davis


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


[HACKERS] [Hackers]Backend quey plan process

2011-06-27 Thread HuangQi
Hi,
 I've been tracing the data structure in the query plan process for a
while. But then I found the data structure manipulation is really so
confusing. Could some guy tell me where could I find any guide on how to
figure out the process and data structure usage? Is there any good resource
helping us read the code?
 BTW, which email system are you using to send to postgres mailing list?
As you can keep the top-answering and maintain the title of your email with
[hackers] in front, my gmail can not help on that. For this email, I just
add by hand.
Thank you a lot for your help.

-- 
Best Regards
Huang Qi Victor


Re: [HACKERS] [Hackers]Backend quey plan process

2011-06-27 Thread Jaime Casanova
HuangQi huangq...@gmail.com writes:

 Hi, 
      I've been tracing the data structure in the query plan process for a 
 while. But then I found the data structure manipulation is really so 
 confusing.
 Could some guy tell me where could I find any guide on how to figure out the 
 process and data structure usage? Is there any good resource helping us read
 the code? 

maybe you can read:
http://www.pgcon.org/2011/schedule/events/350.en.html, it's still a
mistery for me but this seems more clear than read the code... after
read this, read the README's in the code... read the code :)


      BTW, which email system are you using to send to postgres mailing list? 
 As you can keep the top-answering and maintain the title of your email with
 [hackers] in front, my gmail can not help on that. For this email, I just add 
 by hand.
     Thank you a lot for your help.


i used gmail until a couple of weeks ago and never had problems... (well
i'm still using it at least as a mail server, i just changed the
interface a access from)

-- 
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL 
Soporte 24x7, desarrollo, capacitación y servicios

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


[HACKERS] how to call the function--pqCatenateResultError()

2011-06-27 Thread _石头
Hello!~
  
   Now i encounter a function call problem in PostgreSQL's psql module!
   
   The situation is as follow:
In ./src/bin/psql/common.c, I want to call the function 
pqCatenateResultError().
Function pqCatenateResultError() is declared in 
./src/interfaces/libpq/libpq-init.h
extern void pqCatenateResultError(PGresult 
*res, const char *msg); 
and is defined in ./src/interfaces/libpq/fe-exec.c
   void
   pqCatenateResultError(PGresult *res, const char 
*msg)
  {
  PQExpBufferData errorBuf;
  if (!res || !msg)
 return;
  initPQExpBuffer(errorBuf);
  if (res-errMsg)
  appendPQExpBufferStr(errorBuf, res-errMsg);
  appendPQExpBufferStr(errorBuf, msg);
  pqSetResultError(res, errorBuf.data);
  termPQExpBuffer(errorBuf);
  }


  To call this function in ./common.c, I include 'libpq-init.h' in 
./src/bin/psql/common.h .
  As ./common.c include the header file 'common.h'.


 But when I use pqCatenateResultError() in ./common.c, It appears 
undefined reference to pqCatenateResultError() first.
 
 So I include 'extern void pqCatenateResultError(PGresult *res, const char 
*msg);' at the begining of './common.c' .
 But this change make no difference to the result.
   
  I do not know why this happened! Someone hlep me!  Thank you.


  There is another situation similar to the situation above:
  Function PQexec() is declared in ./src/interfaces/libpq/libpq-fe.h and 
defined in ./src/interfaces/libpq/fe-exec.c
 extern PGresult *PQexec(PGconn *conn, const char 
*query);
  I can call this function with no error happening!


  These two situation puzzled me!~



From:   Stone

Re: [HACKERS] Online base backup from the hot-standby

2011-06-27 Thread Jun Ishiduka

 Considering everything that has been discussed on this thread so far.
 
 Do you still think your patch is the best way to accomplish base backups
 from standby servers?
 If not what changes do you think should be made?

I reconsider the way to not use pg_stop_backup().

Process of online base backup on standby server:
 1. pg_start_backup('x');
 2. copy the data directory
 3. copy *pg_control*

Behavior while restore:
 * read Minimum recovery ending location of the copied pg_control.
 * use the value with the same purposes as the end-of-backup location.
   - When the value is equal to 0/0, this behavior do not do.
  This situation is to acquire backup from master server.



Jun Ishizuka
NTT Software Corporation
TEL:045-317-7018
E-Mail: ishizuka@po.ntts.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