Re: [HACKERS] \timing interval

2016-07-18 Thread Peter Eisentraut
On 7/15/16 11:23 AM, Tom Lane wrote: > Meh ... if we're using one-letter abbreviations for hour and second, > using three letters for minute seems just arbitrarily inconsistent. Well, it's the SI abbreviation. We also need to think through localization options. Using the 01:02:03.004 format

Re: [HACKERS] rethinking dense_alloc (HashJoin) as a memory context

2016-07-18 Thread Tom Lane
Greg Stark writes: > I wonder if we could go further. If we don't imagine having a very > large number of allocators then we could just ask each one in turn if > this block is one of theirs and which context it came from. That would > allow an allocator that just allocated

Re: [HACKERS] DO with a large amount of statements get stuck with high memory consumption

2016-07-18 Thread Merlin Moncure
On Sat, Jul 16, 2016 at 2:47 PM, Jan Wieck wrote: > On Tue, Jul 12, 2016 at 3:29 PM, Merlin Moncure wrote: >> >> I've noticed that pl/pgsql functions/do commands do not behave well >> when the statement resolves and frees memory. To be clear: >> >> FOR i in

Re: [HACKERS] rethinking dense_alloc (HashJoin) as a memory context

2016-07-18 Thread Greg Stark
On Sun, Jul 17, 2016 at 1:55 PM, Robert Haas wrote: > >On Wed, Jul 13, 2016 at 4:39 PM, Tom Lane wrote: >> >> I wonder whether we could compromise by reducing the minimum "standard >> chunk header" to be just a pointer to owning context, with the other

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread AMatveev
Hi > This https://github.com/davecramer/plj-new is a very old project > that did work at one time which attempted to do RPC calls to the jvm to > address exactly this problem. > However "cheaply" calling jvm from sql or vice-versa is not really possible. > I do like the idea of the background

Re: [HACKERS] DO with a large amount of statements get stuck with high memory consumption

2016-07-18 Thread Jan Wieck
On Mon, Jul 18, 2016 at 9:43 AM, Tom Lane wrote: > Merlin Moncure writes: > > BTW, while the fix does address the cleanup performance issue, it's > > still the case that anonymous code blocks burn up lots of resident > > memory (my 315k example I tested

Re: [HACKERS] DO with a large amount of statements get stuck with high memory consumption

2016-07-18 Thread Tom Lane
Jan Wieck writes: > In the meantime, would it be appropriate to backpatch the double linking > of memory context children at this time? I believe it has had plenty of > testing in the 9.6 cycle to be sure it didn't break anything. I'm concerned about the ABI breakage risk from

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread Greg Stark
On Mon, Jul 18, 2016 at 2:41 PM, wrote: > And I will be really happy when there are processors with infinite > performance and memory with infinite size. >:))) Well for what it's worth there's no theoretical difference between multi-process and multi-threaded. They're

Re: [HACKERS] DO with a large amount of statements get stuck with high memory consumption

2016-07-18 Thread Tom Lane
Merlin Moncure writes: > BTW, while the fix does address the cleanup performance issue, it's > still the case that anonymous code blocks burn up lots of resident > memory (my 315k example I tested with ate around 8gb IIRC) when run > like this. My question is, if the pl/pgsql

Re: [HACKERS] DO with a large amount of statements get stuck with high memory consumption

2016-07-18 Thread Merlin Moncure
On Mon, Jul 18, 2016 at 8:59 AM, Jan Wieck wrote: > > > On Mon, Jul 18, 2016 at 9:43 AM, Tom Lane wrote: >> >> Merlin Moncure writes: >> > BTW, while the fix does address the cleanup performance issue, it's >> > still the case that

Re: [HACKERS] DO with a large amount of statements get stuck with high memory consumption

2016-07-18 Thread Tom Lane
Merlin Moncure writes: > Hm, maybe, instead of trying to figure out if in a loop, set a > 'called' flag with each statement and only cache when touched the > second time. (If that's easier, dunno). Well, then you just guarantee to lose once. I think Jan's sketch of marking

Re: [HACKERS] DO with a large amount of statements get stuck with high memory consumption

2016-07-18 Thread Jan Wieck
On Mon, Jul 18, 2016 at 10:05 AM, Tom Lane wrote: > Jan Wieck writes: > > In the meantime, would it be appropriate to backpatch the double linking > > of memory context children at this time? I believe it has had plenty of > > testing in the 9.6 cycle to be

Re: [HACKERS] rethinking dense_alloc (HashJoin) as a memory context

2016-07-18 Thread Robert Haas
On Mon, Jul 18, 2016 at 10:19 AM, Greg Stark wrote: > On Sun, Jul 17, 2016 at 1:55 PM, Robert Haas wrote: >>On Wed, Jul 13, 2016 at 4:39 PM, Tom Lane wrote: >>> I wonder whether we could compromise by reducing the minimum "standard >>>

Re: [HACKERS] Regression tests vs existing users in an installation

2016-07-18 Thread Tom Lane
Robert Haas writes: > On Sat, Jul 16, 2016 at 11:38 AM, Tom Lane wrote: >> I'm coming to the conclusion that the only thing that will make this >> materially better in the long run is automatic enforcement of a convention >> about what role names may be

Re: [HACKERS] [PROPOSAL] timestamp informations to pg_stat_statements

2016-07-18 Thread Jason Kim
Hi guys, Thank you for feedbacks. > I think that this is the job of a tool that aggregates things from > pg_stat_statements. It's unfortunate that there isn't a good > third-party tool that does that, but there is nothing that prevents > it. Right. We can do this if we aggregate it

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread AMatveev
Hi > In other words, there's no theoretical reason you couldn't have adapt > a JVM to create a large shared memory segment using mmap or SysV I think even if I was the leader in OS development, I could not correctly answer your question. So just let discuss. Ok, I agree with you that

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread AMatveev
Hi >So I think as long as process and thread have different function in OS, >use process like thread will have overhead in practice. But There are other negative things. I think parallel oriented library usually do not work with process. So Jvm integration is not exception. It is

Re: [HACKERS] Regression tests vs existing users in an installation

2016-07-18 Thread Peter Eisentraut
On 7/15/16 6:13 PM, Tom Lane wrote: > We've talked before about how the regression tests should be circumspect > about what role names they create/drop, so as to avoid possibly blowing > up an installation's existing users during "make installcheck". I'm not particularly sure that that is a

Re: [HACKERS] Typos in logical decoding

2016-07-18 Thread Magnus Hagander
On Mon, Jul 18, 2016 at 11:00 AM, Antonin Houska wrote: > While reading the logical decoding code I noticed a few supposedly mistyped > comments, see the diff below. > Applied, thanks. > Besides that, output_plugin_options argument of CreateInitDecodingContext > function is

Re: [HACKERS] Regression tests vs existing users in an installation

2016-07-18 Thread Tom Lane
Peter Eisentraut writes: > On 7/15/16 6:13 PM, Tom Lane wrote: >> We've talked before about how the regression tests should be circumspect >> about what role names they create/drop, so as to avoid possibly blowing >> up an installation's existing users during

Re: [HACKERS] rethinking dense_alloc (HashJoin) as a memory context

2016-07-18 Thread Peter Geoghegan
On Mon, Jul 18, 2016 at 7:56 AM, Robert Haas wrote: > The test case I used previously was an external sort, which does lots > of retail pfrees. Now that we've mostly abandoned replacement > selection, there will be many fewer pfrees while building runs, I > think, but

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread Robert Haas
On Mon, Jul 18, 2016 at 10:03 AM, Greg Stark wrote: > On Mon, Jul 18, 2016 at 2:41 PM, wrote: >> And I will be really happy when there are processors with infinite >> performance and memory with infinite size. >>:))) > > Well for what it's worth there's no

[HACKERS] Updating our timezone code in the back branches

2016-07-18 Thread Tom Lane
When I updated our copy of the IANA timezone library back in March (commit 1c1a7cbd6), I noted that we ought to consider back-patching those changes once they'd settled out in HEAD. Now that the code has survived a couple of months of beta testing, I think it is time to do that. I went through

Re: [HACKERS] More parallel-query fun

2016-07-18 Thread Piotr Stefaniak
On 2016-06-16 20:14, Tom Lane wrote: As of HEAD you can exercise quite a lot of parallel query behavior by running the regression tests with these settings applied: force_parallel_mode = regress max_parallel_workers_per_gather = 2-- this is default at the moment min_parallel_relation_size =

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread Robert Haas
On Sun, Jul 17, 2016 at 10:00 PM, Jan Wieck wrote: >> I admit that it is risky, but I think there are things that could be >> done to limit the risk. I don't believe we can indefinitely continue >> to ignore the potential performance benefits of making a switch like >> this.

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread Andres Freund
On 2016-07-18 15:47:58 -0400, Robert Haas wrote: > I think the risk profile is exactly the opposite of what you are > suggesting here. If we provide an option to compile the server with > all global variables converted to thread-local variables, there's > really not a whole lot that can break,

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-18 Thread Jim Nasby
On 7/17/16 2:22 PM, Petr Jelinek wrote: I generally agree, but I think the more important question is "Why?". Is it becouse DDL looks more like a sentence? Is it because arrays are a PITA? Is it too hard to call functions? For me it's many small reasons. I want to store it in catalogs and some

Re: [HACKERS] application_name in process name?

2016-07-18 Thread Mike Blackwell
On Sun, Jul 17, 2016 at 10:34 AM, Tom Lane wrote: > It occurs to me that we could also remove the update_process_title GUC: > what you would do is configure a process_title pattern that doesn't > include the %-escape for current command tag, and the infrastructure > could

Re: [HACKERS] application_name in process name?

2016-07-18 Thread Tom Lane
Mike Blackwell writes: > On Sun, Jul 17, 2016 at 10:34 AM, Tom Lane wrote: >> It occurs to me that we could also remove the update_process_title GUC: >> what you would do is configure a process_title pattern that doesn't >> include the %-escape for

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-18 Thread Joshua D. Drake
On 07/18/2016 03:17 PM, Jim Nasby wrote: On 7/17/16 2:22 PM, Petr Jelinek wrote: I do agree that DDL "feels better" (which I think is what JD was alluding too). Yes and no. It reads better and is more clear to those who are not developers or have a developer background which is, many in

[HACKERS] Re: Document that vacuum can't truncate if old_snapshot_threshold >= 0

2016-07-18 Thread Noah Misch
On Fri, Jul 15, 2016 at 02:38:54AM -0400, Noah Misch wrote: > On Wed, Jul 13, 2016 at 02:14:06PM -0700, Andres Freund wrote: > > That appears to not be mentioned in a comment, the commit message or the > > the docs. I think this definitely needs to be prominently documented. > > [Action required

Re: [HACKERS] [BUG] pg_basebackup from disconnected standby fails

2016-07-18 Thread Michael Paquier
On Sat, Jul 16, 2016 at 9:20 PM, Amit Kapila wrote: > On Wed, Jul 13, 2016 at 8:56 AM, Michael Paquier > wrote: >> If we want to tackle the case I mentioned above, one way is to just >> update minRecoveryPoint when an exclusive or a

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread Andres Freund
On 2016-07-19 08:33:20 +0800, Craig Ringer wrote: > On 19 July 2016 at 03:53, Andres Freund wrote: > > > On 2016-07-18 15:47:58 -0400, Robert Haas wrote: > > > I think the risk profile is exactly the opposite of what you are > > > suggesting here. If we provide an option to

Re: [HACKERS] dumping database privileges broken in 9.6

2016-07-18 Thread Noah Misch
On Fri, Jul 15, 2016 at 03:46:17PM -0400, Stephen Frost wrote: > * Noah Misch (n...@leadboat.com) wrote: > > On Sat, Jul 09, 2016 at 12:55:33AM -0400, Stephen Frost wrote: > > > * Noah Misch (n...@leadboat.com) wrote: > > > > This PostgreSQL 9.6 open item is past due for your status update. > >

Re: [HACKERS] heap_update() VM retry could break HOT?

2016-07-18 Thread Pavan Deolasee
On Mon, Jul 18, 2016 at 12:47 PM, Andres Freund wrote: > > as far as I can see that could mean that we perform hot updates when not > permitted, because the tuple has been replaced since, including the > pkey. Similarly, the wrong tuple lock mode could end up being used. > >

Re: [HACKERS] Reviewing freeze map code

2016-07-18 Thread Michael Paquier
On Sat, Jul 16, 2016 at 10:08 AM, Andres Freund wrote: > On 2016-07-14 20:53:07 -0700, Andres Freund wrote: >> On 2016-07-13 23:06:07 -0700, Andres Freund wrote: >> > won't enter the branch, because HEAP_XMAX_LOCK_ONLY won't be set. Which >> > will leave t_ctid and

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread Craig Ringer
On 19 July 2016 at 03:53, Andres Freund wrote: > On 2016-07-18 15:47:58 -0400, Robert Haas wrote: > > I think the risk profile is exactly the opposite of what you are > > suggesting here. If we provide an option to compile the server with > > all global variables converted

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread Craig Ringer
On 18 July 2016 at 02:27, Robert Haas wrote: > On Fri, Jul 15, 2016 at 4:28 AM, Craig Ringer > wrote: > > I don't think anyone's considering moving from multi-processing to > > multi-threading in PostgreSQL. I really, really like the protection that

Re: [HACKERS] dumping database privileges broken in 9.6

2016-07-18 Thread Stephen Frost
Noah, On Monday, July 18, 2016, Noah Misch wrote: > On Fri, Jul 15, 2016 at 03:46:17PM -0400, Stephen Frost wrote: > > * Noah Misch (n...@leadboat.com ) wrote: > > > On Sat, Jul 09, 2016 at 12:55:33AM -0400, Stephen Frost wrote: > > > > * Noah Misch

Re: [HACKERS] Improving executor performance

2016-07-18 Thread Peter Geoghegan
On Wed, Jul 13, 2016 at 6:18 PM, Andres Freund wrote: > While, as in 6) above, removing linked lists from the relevant > structures helps, it's not that much. Staring at this for a long while > made me realize that, somewhat surprisingly to me, is that one of the > major

Re: [HACKERS] dumping database privileges broken in 9.6

2016-07-18 Thread Michael Paquier
On Sat, Jul 16, 2016 at 4:46 AM, Stephen Frost wrote: > Going through and doing testing now. Unfortunately, it doesn't look > like adding in testing of tablespaces into the TAP tests would be very > easy (the only TAP test that deals with tablespaces that I found was >

[HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

2016-07-18 Thread Noah Misch
On Sat, Jul 16, 2016 at 06:48:08PM -0400, Noah Misch wrote: > On Wed, Jul 13, 2016 at 03:57:02PM -0500, Kevin Grittner wrote: > > On Wed, Jul 13, 2016 at 12:48 PM, Andres Freund wrote: > > > On 2016-07-13 10:06:52 -0500, Kevin Grittner wrote: > > >> On Wed, Jul 13, 2016 at

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread AMatveev
Hi > Such a host delegate process could be explicitly built with > multithread support and not 'infect' the rest of the code with its > requirements. > > Using granular RPC is nice for isolation but I am concerned that the > latencies might be high. I agree with you. Moreover I think that

Re: [HACKERS] Reviewing freeze map code

2016-07-18 Thread Andres Freund
On 2016-07-18 01:33:10 -0700, Andres Freund wrote: > On 2016-07-18 10:02:52 +0530, Amit Kapila wrote: > > On Mon, Jul 18, 2016 at 9:13 AM, Andres Freund wrote: > > > On 2016-07-18 09:07:19 +0530, Amit Kapila wrote: > > >> + /* > > >> + * Before locking the buffer, pin the

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread AMatveev
Hi > I admit that it is risky, but I think there are things that could be > done to limit the risk. I don't believe we can indefinitely continue > to ignore the potential performance benefits of making a switch like > this. Breaking a thirty-year old code base irretrievably would be > sad, but

[HACKERS] Typos in logical decoding

2016-07-18 Thread Antonin Houska
While reading the logical decoding code I noticed a few supposedly mistyped comments, see the diff below. Besides that, output_plugin_options argument of CreateInitDecodingContext function is unused, and the function passes NIL to the corresponding argument of StartupDecodingContext. This looks

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread AMatveev
Hi > The issue here is an architectural mismatch between PostgreSQL and > the JVM, made worse by the user's very stored-proc-heavy code. Some > other runtime that's designed to co-operate with a multiprocessing > environment could well be fine, but the JVM isn't. At least, the >

Re: [HACKERS] Changing the result set to contain the cost of the optimizer's chosen plan

2016-07-18 Thread Srinivas Karthik V
Hi Tom, Yes, we indeed get the cost of the plan at the first line itself. Somehow, I missed this point. We just in fact implemented this functionality and its working. Thanks again. Regards, Srinivas Karthik On Tue, Jul 12, 2016 at 6:43 AM, Craig Ringer wrote: > On 11

Re: [HACKERS] Reviewing freeze map code

2016-07-18 Thread Andres Freund
On 2016-07-18 10:02:52 +0530, Amit Kapila wrote: > On Mon, Jul 18, 2016 at 9:13 AM, Andres Freund wrote: > > On 2016-07-18 09:07:19 +0530, Amit Kapila wrote: > >> + /* > >> + * Before locking the buffer, pin the visibility map page if it may be > >> + * necessary. > >> + */ >

Re: [HACKERS] RecoveryTargetTLI dead variable in XLogCtlData

2016-07-18 Thread Michael Paquier
On Wed, Jul 13, 2016 at 12:29 PM, Michael Paquier wrote: > I just bumped into $subject, a variable that is never set and never used: > --- a/src/backend/access/transam/xlog.c > +++ b/src/backend/access/transam/xlog.c > @@ -631,8 +631,6 @@ typedef struct XLogCtlData >

[HACKERS] heap_update() VM retry could break HOT?

2016-07-18 Thread Andres Freund
Hi, heap_update() retries pinning the vm pinning, as explained in the following comment: /* * Before locking the buffer, pin the visibility map page if it appears to * be necessary. Since we haven't got the lock yet, someone else might be * in the middle of

Re: [HACKERS] pg_xlogdump follow into the future

2016-07-18 Thread Magnus Hagander
On Thu, Jul 14, 2016 at 8:27 PM, Andres Freund wrote: > On 2016-07-14 13:46:23 +0200, Magnus Hagander wrote: > > Currently, if you run pg_xlogdump with -f, you have to specify an end > > position in an existing file, or if you don't it will only follow until > the > > end of

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread AMatveev
Hi > There's https://github.com/jnr/jnr-ffi that enables to call C > functions without resorting to writing JNI wrappers. I have not said that you are wrong. It's the dark side of "like seprate process" They can cheaply call sql from jvm. And they can't cheaply call jvm from sql. Jvm in oracle

Re: [HACKERS] One process per session lack of sharing

2016-07-18 Thread Dave Cramer
On 18 July 2016 at 06:04, wrote: > Hi > > > There's https://github.com/jnr/jnr-ffi that enables to call C > > functions without resorting to writing JNI wrappers. > I have not said that you are wrong. > It's the dark side of "like seprate process" > They can cheaply call sql