date:20110627

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

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

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)

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

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

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

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

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

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?

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

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

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

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

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

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

\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

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

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?

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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