### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

Your proposal makes me think of something similar which might be useful, INclusion constraints. As exclusion constraints might be thought of like a generalization of unique/key constraints, inclusion constraints are like a generalization of foreign key constraints. The inclusion constraints

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On 16.04.2012 08:40, Jeff Davis wrote: Does someone know of a spatial join algorithm (without IP claims) that would be as good as this one for ranges? I'd be happy with an algorithm that's specific to ranges, too, but my gut geeling is that there has to be a lot of research on spatial join

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

Jeff Davis pg...@j-davis.com writes: Proposed solution: a modified merge join that can handle ranges. 1. Order the ranges on both sides by the lower bound, then upper bound. Empty ranges can be excluded entirely. 2. Left := first range on left, Right := first range on right 3. If Left or

### Re: [HACKERS] how to create a non-inherited CHECK constraint in CREATE TABLE

Displace yes. It would error out if someone says ALTER TABLE ONLY... CHECK (); suggesting to use the ONLY with the CHECK. I'd say the behavior for that case can revert to the PostgreSQL 9.1 behavior. If the table has children, raise an error. Otherwise, add an inheritable CHECK

### Re: [HACKERS] Improving our clauseless-join heuristics

Another way to look at this is that if we have select ... from a,b,c,d where a.x = b.y + c.z we want to consider a cross-join of b and c, in the hopes that we can do something useful with the join clause at the next level where it can join to a. From b's perspective there is no percentage

### Re: [HACKERS] index-only scans vs. Hot Standby, round two

On Fri, Apr 13, 2012 at 12:33:06PM -0400, Robert Haas wrote: In the department of query cancellations, I believe Noah argued previously that this wasn't really going to cause a problem. And, indeed, if the master has a mix of inserts, updates, and deletes, then it seems likely that any

### Re: [HACKERS] not null validation option in contrib/file_fdw

Thank you for the review. -Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Andrew Dunstan Sent: Friday, April 13, 2012 9:16 PM To: Shigeru HANADA Cc: Etsuro Fujita; pgsql-hackers@postgresql.org Subject: Re:

### Re: [HACKERS] index-only scans vs. Hot Standby, round two

On Mon, Apr 16, 2012 at 8:02 AM, Noah Misch n...@leadboat.com wrote: On Fri, Apr 13, 2012 at 12:33:06PM -0400, Robert Haas wrote: In the department of query cancellations, I believe Noah argued previously that this wasn't really going to cause a problem.  And, indeed, if the master has a mix

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On Mon, Apr 16, 2012 at 7:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Dunno.  It might be easier to sell the idea of adding support for range joins in a couple of years, after we've seen how much use ranges get. Once we've started the journey towards range types we must complete it reasonably

### Re: [HACKERS] Last gasp

On Sun, Apr 15, 2012 at 11:31 PM, Greg Smith g...@2ndquadrant.com wrote: On 04/15/2012 05:46 AM, Simon Riggs wrote: Our problem is not lack of resource, it is ineffective delegation. As Hannu points out, he didn't know the patch would be rejected, so he didn't know help was needed to save

### Re: [HACKERS] Different gettext domain needed for error context

On 15.04.2012 00:54, Tom Lane wrote: I really think we need to change errcontext itself to pass the correct domain. If we are going to require a domain to be provided (and this does require that, for correct operation), then we need to break any code that doesn't provide it in a visible

### Re: [HACKERS] index-only scans vs. Hot Standby, round two

On 16.04.2012 10:38, Simon Riggs wrote: On Mon, Apr 16, 2012 at 8:02 AM, Noah Mischn...@leadboat.com wrote: On Fri, Apr 13, 2012 at 12:33:06PM -0400, Robert Haas wrote: In the department of query cancellations, I believe Noah argued previously that this wasn't really going to cause a problem.

### Re: [HACKERS] Last gasp

Alvaro Herrera alvhe...@commandprompt.com writes: I've used Redmine a lot, as you know, and I only keep using it because it's a requirement at work. It is certainly not close to usable for general pgsql stuff. (Trac, which we used to use prior to Redmine, was certainly much worse, though).

### Re: [HACKERS] man pages for contrib programs

Peter Eisentraut pete...@gmx.net writes: Good question. I guess we could keep the original name ... Modules for that chapter. Those are a kind of server application in my mind, I think we want to keep using “module” to mean the shared library file we load at runtime, be it a .so, a .dylib or a

### Re: [HACKERS] Why can't I use pgxs to build a plpgsql plugin?

On 13.04.2012 19:17, Guillaume Lelarge wrote: On Thu, 2012-04-12 at 12:28 +0300, Heikki Linnakangas wrote: On 08.04.2012 11:59, Guillaume Lelarge wrote: There could be a good reason which would explain why we can't (or don't want to) do this, but I don't see it right now. Me neither, except

### [HACKERS] nodes/*funcs.c inconsistencies

I observed these inconsistencies in node support functions: - _copyReassignOwnedStmt() uses COPY_SCALAR_FIELD() on the string field newrole, and _equalReassignOwnedStmt() uses COMPARE_NODE_FIELD(). - _outCreateForeignTableStmt() calls _outCreateStmt() directly. This produces the label

### [HACKERS] [BUG] Checkpointer on hot standby runs without looking checkpoint_segments

Hello, this is bug report and a patch for it. The first patch in the attachments is for 9.2dev and next one is for 9.1.3. On the current 9.2dev, IsCheckpointOnSchedule@checkpointer.c does not check against WAL segments written. This makes checkpointer always run at the speed according to

### Re: [HACKERS] [BUG] Checkpointer on hot standby runs without looking checkpoint_segments

On Mon, Apr 16, 2012 at 1:05 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, this is bug report and a patch for it. The first patch in the attachments is for 9.2dev and next one is for 9.1.3. On the current 9.2dev, IsCheckpointOnSchedule@checkpointer.c does not check

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On Mon, Apr 16, 2012 at 10:29 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 16.04.2012 08:40, Jeff Davis wrote: Does someone know of a spatial join algorithm (without IP claims) that would be as good as this one for ranges? I'd be happy with an algorithm that's

### [HACKERS] A typo fix in a comment in xlog.c

Hello, I found a duplicate words in the comment of StartupXLOG@xlog.c and the attached patch fixes it. Essentially the fix is in one line as follows, - * We're in recovery, so unlogged relations relations may be trashed + * We're in recovery, so unlogged relations may be trashed But I did

### Re: [HACKERS] A typo fix in a comment in xlog.c

On mån, 2012-04-16 at 21:28 +0900, Kyotaro HORIGUCHI wrote: Hello, I found a duplicate words in the comment of StartupXLOG@xlog.c and the attached patch fixes it. Essentially the fix is in one line as follows, - * We're in recovery, so unlogged relations relations may be trashed + *

### Re: [HACKERS] JSON for PG 9.2

On Tue, 2012-01-31 at 12:58 -0500, Andrew Dunstan wrote: On 01/30/2012 10:37 AM, Andrew Dunstan wrote: Aside: is query_to_json really necessary? It seems rather ugly and easily avoidable using row_to_json. I started with this, again by analogy with query_to_xml(). But I agree

### Re: [HACKERS] JSON for PG 9.2

On 04/16/2012 09:34 AM, Hannu Krosing wrote: based on Abhijit's feeling and some discussion offline, the consensus seems to be to remove query_to_json. The only comment I have here is that query_to_json could have been replaced with json_agg, so thet you don't need to do double-buffering for

### Re: [HACKERS] index-only scans vs. Hot Standby, round two

Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Can we have a soft hot standby conflict that doesn't kill the query, but disables index-only-scans? Well, there wouldn't be any way for the planner to know whether an index-only scan would be safe or not. I think this would have

### Re: [HACKERS] JSON for PG 9.2

On Mon, Apr 16, 2012 at 9:10 AM, Andrew Dunstan and...@dunslane.net wrote: On 04/16/2012 09:34 AM, Hannu Krosing wrote: based on Abhijit's feeling and some discussion offline, the consensus seems to be to remove query_to_json. The only comment I have here is that query_to_json could have

On Mon, Apr 16, 2012 at 06:24:57AM +0200, Boszormenyi Zoltan wrote: Yes, just like when the readahead window set to 256, FETCH 1024 will iterate through 4 windows or FETCH 64 iterates through the same window 4 times. This is the idea behind the readahead window. Really? It's definitely not the

### Re: [HACKERS] JSON for PG 9.2

On Mon, 2012-04-16 at 10:10 -0400, Andrew Dunstan wrote: On 04/16/2012 09:34 AM, Hannu Krosing wrote: based on Abhijit's feeling and some discussion offline, the consensus seems to be to remove query_to_json. The only comment I have here is that query_to_json could have been replaced

### Re: [HACKERS] [COMMITTERS] pgsql: Add new replication mode synchronous_commit = 'write'.

On Sun, Apr 15, 2012 at 12:13 AM, Thom Brown t...@linux.com wrote: On 14 April 2012 15:58, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Apr 14, 2012 at 4:16 AM, Thom Brown t...@linux.com wrote: I have a question though.  What happens when this is set to write (or remote_write as proposed)

### [HACKERS] Bug tracker tool we need (was: Last gasp)

Dimitri Fontaine dimi...@2ndquadrant.fr writes: Alvaro Herrera alvhe...@commandprompt.com writes: I've used Redmine a lot, as you know, and I only keep using it because it's a requirement at work. It is certainly not close to usable for general pgsql stuff. (Trac, which we used to use

### Re: [HACKERS] Bug tracker tool we need (was: Last gasp)

On Mon, Apr 16, 2012 at 18:24, Alex a...@commandprompt.com wrote: Dimitri Fontaine dimi...@2ndquadrant.fr writes: Alvaro Herrera alvhe...@commandprompt.com writes: I've used Redmine a lot, as you know, and I only keep using it because it's a requirement at work.  It is certainly not close to

### Re: [HACKERS] JSON for PG 9.2

On Mon, Apr 16, 2012 at 11:19 AM, Hannu Krosing ha...@2ndquadrant.com wrote: If doing something in 9.3 then what I would like is some way to express multiple queries. Basically a variant of query_to_json(query text[]) where queries would be evaluated in order and then all the results

### Re: [HACKERS] how to create a non-inherited CHECK constraint in CREATE TABLE

Excerpts from Nikhil Sontakke's message of lun abr 16 03:56:06 -0300 2012: Displace yes. It would error out if someone says ALTER TABLE ONLY... CHECK (); suggesting to use the ONLY with the CHECK. I'd say the behavior for that case can revert to the PostgreSQL 9.1 behavior.

### Re: [HACKERS] Bug tracker tool we need

Magnus Hagander mag...@hagander.net writes: One thing to note is that the referenced wiki page is over a year old. And that many more things have been said on email lists than are actually in that page. Yeah, I went through it briefly and rather important concern seem to have been raised by

2012-04-16 18:04 keltezéssel, Michael Meskes írta: On Mon, Apr 16, 2012 at 06:24:57AM +0200, Boszormenyi Zoltan wrote: Yes, just like when the readahead window set to 256, FETCH 1024 will iterate through 4 windows or FETCH 64 iterates through the same window 4 times. This is the idea behind the

### Re: [HACKERS] [BUG] Checkpointer on hot standby runs without looking checkpoint_segments

On Mon, Apr 16, 2012 at 9:05 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: In the backported version to 9.1.3, bgwriter.c is modified instead of checkpointer.c in 9.2. And GetWalRcvWriteRecPtr() is used as the equivalent of GetStandbyFlushRecPtr() in 9.2. In 9,2,

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On Mon, 2012-04-16 at 02:52 -0400, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: 1. Order the ranges on both sides by the lower bound, then upper bound. Empty ranges can be excluded entirely. 2. Left := first range on left, Right := first range on right 3. If Left or Right is

### Re: [HACKERS] index-only scans vs. Hot Standby, round two

On Mon, Apr 16, 2012 at 3:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Can we have a soft hot standby conflict that doesn't kill the query, but disables index-only-scans? Well, there wouldn't be any way for the planner to know

### Re: [HACKERS] Why can't I use pgxs to build a plpgsql plugin?

On Mon, 2012-04-16 at 13:09 +0300, Heikki Linnakangas wrote: On 13.04.2012 19:17, Guillaume Lelarge wrote: On Thu, 2012-04-12 at 12:28 +0300, Heikki Linnakangas wrote: On 08.04.2012 11:59, Guillaume Lelarge wrote: There could be a good reason which would explain why we can't (or don't

### Re: [HACKERS] index-only scans vs. Hot Standby, round two

On Mon, Apr 16, 2012 at 4:26 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Can we have a soft hot standby conflict that doesn't kill the query, but disables index-only-scans? Yeah, something like that seems possible. For example, suppose the master includes, in each

### Re: [HACKERS] index-only scans vs. Hot Standby, round two

On Mon, Apr 16, 2012 at 3:02 AM, Noah Misch n...@leadboat.com wrote: Do you refer to PD_ALL_VISIBLE as not merely a hint due to the requirement to prevent a page from simultaneously having a negative PD_ALL_VISIBLE and a positive visibility map bit?  That is to say, PD_ALL_VISIBLE is fully a

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On Mon, 2012-04-16 at 16:22 +0400, Alexander Korotkov wrote: There is a good overview article about spatial joins. http://www.cs.umd.edu/users/hjs/pubs/jacoxtods07.pdf Thank you, that's exactly the kind of overview I was looking for. It shows that there is a lot of methods based on building

### Re: [HACKERS] index-only scans vs. Hot Standby, round two

On Mon, Apr 16, 2012 at 1:58 PM, Simon Riggs si...@2ndquadrant.com wrote: If we do need to do something, then introduce concept of a visibility conflict. On replay: If feedback not set, set LSN of visibility conflict on PROCs that conflict, if not already set. On query: If feedback not

### Re: [HACKERS] Last gasp

On 04/15/2012 12:01 PM, Tom Lane wrote: Where I think we have been fooling ourselves is in failing to tell the difference between a patch that is committable in the current fest, versus one that is still WIP and is going to need more development time. I wonder if this bit of state might be

### Re: [HACKERS] Improving our clauseless-join heuristics

Amit Kapila amit.kap...@huawei.com writes: For this kind of query, currently (referring 9.0.3 code) also it considers join of b,c and b,d. As there is no join clause between b,c,d so it will go in path of make_rels_by_clauseless_joins() where it considers join of b,c and b,d. In this kind

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On Sun, 2012-04-15 at 23:18 -0700, Darren Duncan wrote: Your proposal makes me think of something similar which might be useful, INclusion constraints. As exclusion constraints might be thought of like a generalization of unique/key constraints, inclusion constraints are like a

### Re: [HACKERS] Last gasp

Alex wrote: Jay Levittjay.lev...@gmail.com writes: Alex wrote: I didn't follow this whole thread, but have we considered Redmine[1]? As the resident Ruby is shiny, let's do everything in Rails on my MacBook guy, I'd like to make a statement against interest: I've tried Redmine a few times

### [HACKERS] Aggressive memory consumption in {ts,array}_typanalyze

Both \$SUBJECT functions pass to hash_create() an expected hash table size of 1 * attstattarget. Based on header comments, this represents a near-worst case. These typanalyze functions scan the hash tables sequentially, thereby visiting the entire allocation. Per the recommendation in

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On Tue, Apr 17, 2012 at 12:12 AM, Jeff Davis pg...@j-davis.com wrote: On Mon, 2012-04-16 at 16:22 +0400, Alexander Korotkov wrote: There is a good overview article about spatial joins. http://www.cs.umd.edu/users/hjs/pubs/jacoxtods07.pdf Thank you, that's exactly the kind of overview I

### Re: [HACKERS] Memory usage during sorting

On 14 April 2012 14:34, Peter Geoghegan pe...@2ndquadrant.com wrote: FWIW, I started playing with adding timsort to Postgres last night: https://github.com/Peter2ndQuadrant/postgres/tree/timsort I've fixed this feature-branch so that every qsort_arg call site (including the tuplesort

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On Mon, Apr 16, 2012 at 9:12 PM, Jeff Davis pg...@j-davis.com wrote: That had occurred to me, but I was hesitant to only use temp indexes. It still doesn't really offer a good solution when both sides of the join are relatively large (because of random I/O). Also the build speed of the index

### Re: [HACKERS] Bug tracker tool we need

Alex wrote: I still fail to see how Redmine doesn't fit into requirements summarized at that wiki page[1], so that must be something other than formal requirement of being free/open software and running postgres behind (some sort of feeling maybe?) Well, if those requirements are in fact

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

Simon Riggs wrote: I'd like to see something along the lines of demand-created optional indexes, that we reclaim space/maintenance overhead on according to some cache management scheme. More space you have, the more of the important ones hang around. The rough same idea applies to materialised

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On Apr 16, 2012, at 1:40 AM, Jeff Davis pg...@j-davis.com wrote: See attached SQL for example. The Problem statement: slow. Nested loops are the only option, although they can benefit from an inner GiST index if available. But if the join is happening up in the plan tree somewhere, then it's

### Re: [HACKERS] Bug tracker tool we need

On 04/16/2012 09:24 AM, Alex wrote: Jay, Alvaro, Dimitri (and whoever else wants to speak up) could you please describe your ideal tool for the task? Given that every other existing tool likely have pissed off someone already, I guess our best bet is writing one from scratch. Or maybe there

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

Robert Haas robertmh...@gmail.com writes: On Apr 16, 2012, at 1:40 AM, Jeff Davis pg...@j-davis.com wrote: See attached SQL for example. The Problem statement: slow. Nested loops are the only option, although they can benefit from an inner GiST index if available. But if the join is

### Re: [HACKERS] Bug tracker tool we need (was: Last gasp)

On Mon, Apr 16, 2012 at 06:29:47PM +0200, Magnus Hagander wrote: FWIW, I think the closest thing we've found so far would be debbugs - which IIRC doesn't have any kind of reasonable database backend, which would be a strange choice for a project like ours :) And makes many things harder...

### [HACKERS] Slow temporary tables when using sync rep

Hi, I've noticed that when using synchronous replication (on 9.2devel at least), temporary tables become really slow: thom@test=# create temporary table temp_test (a text, b text); CREATE TABLE Time: 16.812 ms thom@test=# SET synchronous_commit = 'local'; SET Time: 2.739 ms thom@test=# insert

### Re: [HACKERS] Slow temporary tables when using sync rep

On Mon, Apr 16, 2012 at 6:27 PM, Thom Brown t...@linux.com wrote: Hi, I've noticed that when using synchronous replication (on 9.2devel at least), temporary tables become really slow: Since temporary tables are only present until the session ends (or possibly only until a commit), why are

### Re: [HACKERS] Improving our clauseless-join heuristics

I might still be misunderstanding, but I think what you are suggesting is that in the loop in make_rels_by_clause_joins, if we find that the old_rel doesn't have a join clause/restriction with the current other_rel, we check to see whether other_rel has any join clauses at all, and force the join

### Re: [HACKERS] A typo fix in a comment in xlog.c

But I did fill-paragraph for the fixed comment so the patch replaces a little bit more. You might want to adjust your fill-column setting to 79, so pgindent doesn't reformat that again. Compare to what I just committed. Thank you for sugestion. I could't decide fill-column fit to every

### Re: [HACKERS] 9.3 Pre-proposal: Range Merge Join

On Mon, 2012-04-16 at 22:20 +0100, Simon Riggs wrote: On Mon, Apr 16, 2012 at 9:12 PM, Jeff Davis pg...@j-davis.com wrote: That had occurred to me, but I was hesitant to only use temp indexes. It still doesn't really offer a good solution when both sides of the join are relatively large

### Re: [HACKERS] Bug tracker tool we need

I believe the biggest hurdle for many hackers is that in redmine, email is not a first class citizen. The majority of hackers are never going to want to go into a web interface to get something done, they live in VI/Emacs and the command line. One thing that redmine definitely breaks is

### Re: [HACKERS] [BUG] Checkpointer on hot standby runs without looking checkpoint_segments

Hello, thank you for comment. In the backported version to 9.1.3, bgwriter.c is modified instead of checkpointer.c in 9.2. And GetWalRcvWriteRecPtr() is used as the equivalent of GetStandbyFlushRecPtr() in 9.2. In 9,2, GetXLogReplayRecPtr() should be used instead of

On Mon, Apr 16, 2012 at 07:18:07PM +0200, Boszormenyi Zoltan wrote: OK. I would like to stretch your agreement a little. :-) ... Yeah, you got a point here. By the new FETCH request. Instead of the above, I imagined this: - the runtime notices that the new request is larger than the current

2012-04-17 05:52 keltezéssel, Michael Meskes írta: On Mon, Apr 16, 2012 at 07:18:07PM +0200, Boszormenyi Zoltan wrote: OK. I would like to stretch your agreement a little. :-) ... Yeah, you got a point here. By the new FETCH request. Instead of the above, I imagined this: - the runtime

On Tue, Apr 17, 2012 at 06:02:34AM +0200, Boszormenyi Zoltan wrote: I listed two scenarios. 1. occasional bump of the readahead window for large requests, for smaller requests it uses the originally set size 2. permanent bump of the readahead window for large requests (larger than

### Re: [HACKERS] Why can't I use pgxs to build a plpgsql plugin?

2012/4/16 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: On 13.04.2012 19:17, Guillaume Lelarge wrote: On Thu, 2012-04-12 at 12:28 +0300, Heikki Linnakangas wrote: On 08.04.2012 11:59, Guillaume Lelarge wrote: There could be a good reason which would explain why we can't (or don't

### Re: [HACKERS] not null validation option in contrib/file_fdw

I updated the patch. Attached is an updated version of the patch. Changes: * fix a bug in fileGetOptions() * rename the validation option and its code to validate_data_file * clean up Best regards, Etsuro Fujita -Original Message- From: pgsql-hackers-ow...@postgresql.org

### Re: [HACKERS] Bug tracker tool we need

On Mon, Apr 16, 2012 at 23:48, Jay Levitt jay.lev...@gmail.com wrote: Alex wrote: I still fail to see how Redmine doesn't fit into requirements summarized at that wiki page[1], so that must be something other than formal requirement of being free/open software and running postgres behind