Re: [HACKERS] [PATCH] Doc fix for VACUUM FREEZE

On Fri, Jan 3, 2014 at 9:02 PM, Peter Eisentraut wrote: > On 12/17/13, 8:16 PM, Maciek Sakrejda wrote: >> (now with patch--sorry about that) > > This patch doesn't apply. There are some recent changes around same place which broke this patch. Please find the modified patch attached with this mail

Re: [HACKERS] Proposal: variant of regclass

Hi, On Tue, Jan 14, 2014 at 4:28 PM, Yugo Nagata wrote: > Here is the patch to implement to_regclass, to_regproc, to_regoper, > and to_regtype. They are new functions similar to regclass, regproc, > regoper, and regtype except that if requested object is not found, > returns InvalidOid, rather th

Re: [HACKERS] [BUGS] surprising to_timestamp behavior

On Thu, Oct 31, 2013 at 10:50 AM, Jeevan Chalke < jeevan.cha...@enterprisedb.com> wrote: > > > > On Tue, Oct 29, 2013 at 11:05 PM, Robert Haas wrote: > >> On Tue, Oct 29, 2013 at 12:03 PM, Tom Lane wrote: >> > Robert Haas writes: >> >> It turns out that when you use the to_timestamp function, a

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Jan 14, 2014 at 2:00 PM, Florian Pflug wrote: > On Jan10, 2014, at 22:27 , David Rowley wrote: > > As the patch stands at the moment, I currently have a regression test > > which currently fails due to these extra zeros after the decimal point: > > > > -- This test currently fails due ex

Re: [HACKERS] [PATCH] Filter error log statements by sqlstate

On Mon, Jan 13, 2014 at 4:30 PM, Oskari Saarenmaa wrote: > Hi, > > > On 13/01/14 10:26, Jeevan Chalke wrote: > >> 1. Documentation is missing and thus becomes difficult to understand what >> exactly you are trying to do. Or in other words, user will be uncertain >> about using it more efficiently

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

On Tue, Jan 14, 2014 at 2:16 AM, Robert Haas wrote: > On Sat, Jan 11, 2014 at 1:08 AM, Amit Kapila wrote: >> Yes, currently this applies to update, what I have in mind is that >> in future if some one wants to use WAL compression for any other >> operation like 'full_page_writes', then it can be

Re: [HACKERS] Case sensitive mode in windows build option

On 01/14/2014 11:49 AM, Dilip kumar wrote: > > > As per current behavior if user want to build in debug mode in windows, > then he need to give debug in capital letters (DEBUG), > > I think many user will always make mistake in giving this option, in my > opinion we can make it case insensitive

[HACKERS] Case sensitive mode in windows build option

As per current behavior if user want to build in debug mode in windows, then he need to give debug in capital letters (DEBUG), I think many user will always make mistake in giving this option, in my opinion we can make it case insensitive. I have attached a small patch for the same ( just conve

[HACKERS] Soften pg_[start|stop]_backup to allow them on a standby?

Hi all, This is perhaps something that has already been discussed on hackers, I just could not find anything in the archives. Currently, pg_start_backup and pg_stop_backup cannot run on a standby because it is not possible to write a backup_label file to disk, because of the nature of a standby se

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On Jan14, 2014, at 02:10 , Kevin Grittner wrote: > The fact that some > day some new programmer might not be aware of all business rules, > or might choose to try to ignore them is the reason you add > constraints to columns and domains. Well, for columns and domains that seems easy. We could hav

Re: [HACKERS] Capturing EXPLAIN ANALYZE for a query without discarding the normal result set

Dave Cole writes: > It would be really cool if you could direct the EXPLAIN ANALYZE output to a > temporary table so that the query being analyzed could execute normally. What happens if the current transaction rolls back? If you want noninvasive explain data, contrib/auto_explain offers a solut

Re: [HACKERS] Capturing EXPLAIN ANALYZE for a query without discarding the normal result set

On Tue, Jan 14, 2014 at 5:06 AM, Dave Cole wrote: > It would be really cool if you could direct the EXPLAIN ANALYZE output to a > temporary table so that the query being analyzed could execute normally. You can use the auto_explain contrib module to log the query plans of slow(er) queries: http:/

[HACKERS] Capturing EXPLAIN ANALYZE for a query without discarding the normal result set

I apologise for dropping this out of nowhere. I had an idea about EXPLAIN ANALYZE that would be very useful for the system we are developing and supporting. It would be really cool if you could direct the EXPLAIN ANALYZE output to a temporary table so that the query being analyzed could execute n

[HACKERS] Comment typo in src/include/access/gin_private.h

I ran into a typo in src/include/access/gin_private.h. Patch attached. Thanks, Best regards, Etsuro Fujita gin_private.h-typo.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/m

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On Jan14, 2014, at 00:33 , Craig Ringer wrote: > So I guess the question is: Is it worth all that hassle to remove a > misfeature you have to go out of your way to use? Is support for non-1 > lower bounds stopping us from doing something useful and important? Or > is it just an irritation that it

Re: [HACKERS] Where do we stand on 9.3 bugs?

Marti Raudsepp writes: > On Mon, Jan 13, 2014 at 5:16 PM, Tom Lane wrote: >> What remaining issues are there blocking a 9.3.3 release? > Well hardly a blocker since this has missed 2 releases already, but > I'm still hopeful to get many PGXS-based extensions to build again > without the dreaded

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Mon, Jan 13, 2014 at 12:49 AM, Heikki Linnakangas wrote: >> In any case, my patch is bound to win decisively for the other >> extreme, the insert-only case, because the overhead of doing an index >> scan first is always wasted there with your approach, and the overhead >> of extended btree leaf

Re: [HACKERS] plpgsql.consistent_into

On 2014-01-14 02:54, Marti Raudsepp wrote: On Sun, Jan 12, 2014 at 7:51 AM, Marko Tiikkaja wrote: the behaviour of SELECT .. INTO when the query returns more than one row. Some of you might know that no exception is raised in this case Agreed. But I also agree with the rest of the thread abou

Re: [HACKERS] GIN improvements part 1: additional information

Peter Eisentraut wrote: > On 12/10/13, 2:44 PM, Alexander Korotkov wrote: > > However, patch didn't apply to head. Corrected version is attached. > Update the pgstattuple regression test results. The latest version of the patch still doesn't pass the test. I'll look at the patch in further detai

Re: [HACKERS] Where do we stand on 9.3 bugs?

Andres Freund writes: > On 2014-01-13 17:30:55 -0800, Josh Berkus wrote: >> Despite actually dating back to 9.0, something in the 9.3.2/9.2.6 >> updates is causing users to hit it now. And for those who do hit it, >> replication is impossible and there's no workaround. > There have been reports

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On 01/13/2014 05:48 PM, Andres Freund wrote: > On 2014-01-13 10:56:00 -0800, Josh Berkus wrote: >> Well, it was the lack of sysctl options which takes the 2Q change from >> "annoyance" to "potential disaster". We can't ever get away from the >> possibility that the Postgres use-case might be the m

Re: [HACKERS] plpgsql.consistent_into

On Sun, Jan 12, 2014 at 7:51 AM, Marko Tiikkaja wrote: > the behaviour of SELECT .. INTO when the query returns more than one row. > Some of you might know that no exception is raised in this case Agreed. But I also agree with the rest of the thread about changing current INTO behavior and introd

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On 2014-01-13 10:56:00 -0800, Josh Berkus wrote: > Well, it was the lack of sysctl options which takes the 2Q change from > "annoyance" to "potential disaster". We can't ever get away from the > possibility that the Postgres use-case might be the minority use-case, > and we might have to use non-d

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 14/01/14 14:29, Tom Lane wrote: [...] (2) the float and numeric variants should be implemented under nondefault names (I'm thinking FAST_SUM(), but bikeshed away). People who need extra speed and don't mind the slightly different results can alter their queries to use these variants. One rea

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 01/13/2014 05:30 PM, Dave Chinner wrote: > On Mon, Jan 13, 2014 at 03:24:38PM -0800, Josh Berkus wrote: > No matter what default NUMA allocation policy we set, there will be > an application for which that behaviour is wrong. As such, we've had > tools for setting application specific NUMA polic

Re: [HACKERS] Where do we stand on 9.3 bugs?

On 2014-01-13 17:30:55 -0800, Josh Berkus wrote: > On 01/13/2014 07:16 AM, Tom Lane wrote: > > What remaining issues are there blocking a 9.3.3 release? I know that > > there were unresolved multixact issues when we put out 9.3.2 --- are > > those all dealt with now? What else do people see as re

Re: [HACKERS] Where do we stand on 9.3 bugs?

On 01/13/2014 07:16 AM, Tom Lane wrote: > What remaining issues are there blocking a 9.3.3 release? I know that > there were unresolved multixact issues when we put out 9.3.2 --- are > those all dealt with now? What else do people see as release-blockers? I see this bug as a release-blocker. It

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Florian Pflug writes: > I think it'd be worthwile to get this into 9.4, if that's still an option, > even if we only support COUNT. My thought is (1) we can certainly implement inverse transitions for COUNT() and the integer variants of SUM(), and that alone would be a killer feature for some pe

Re: [HACKERS] plpgsql.consistent_into

On 01/13/2014 05:10 PM, Jim Nasby wrote: > On 1/13/14, 7:06 PM, Josh Berkus wrote: >> Regularly? No. But I've seen it, especially as part of a "does this >> query return any rows?" test. That's not the best way to test that, but >> that doesn't stop a lot of people doing it. > > Right, and I ce

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 2014-01-13 17:13:51 -0800, James Bottomley wrote: > a file into a user provided buffer, thus obtaining a page cache entry > and a copy in their userspace buffer, then insert the page of the user > buffer back into the page cache as the page cache page ... that's right, > isn't it postgress peopl

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On 1/13/14, 7:10 PM, Kevin Grittner wrote: Tom Lane wrote: >I think the argument really is that some people don't want to >make their application code work with such cases (which is fine) >so they'd like an inside-the-database guarantee that the app code >won't ever see such cases. Which is l

Re: [HACKERS] Disallow arrays with non-standard lower bounds

Tom Lane wrote: > I think the argument really is that some people don't want to > make their application code work with such cases (which is fine) > so they'd like an inside-the-database guarantee that the app code > won't ever see such cases.  Which is less fine, ISTM: if you fear > some part of

Re: [HACKERS] plpgsql.consistent_into

On 1/13/14, 7:06 PM, Josh Berkus wrote: On 01/13/2014 04:20 PM, Jim Nasby wrote: On 1/13/14, 5:57 PM, Josh Berkus wrote: I *really* don't want to go through all my old code to find places where I used SELECT ... INTO just to pop off the first row, and ignored the rest. I doubt anyone else does

Re: [HACKERS] [Lsf-pc] Linux kernel impact on PostgreSQL performance

On Mon, 2014-01-13 at 21:29 +, Greg Stark wrote: > On Mon, Jan 13, 2014 at 9:12 PM, Andres Freund wrote: > > For one, postgres doesn't use mmap for files (and can't without major > > new interfaces). Frequently mmap()/madvise()/munmap()ing 8kb chunks has > > horrible consequences for performan

Re: [HACKERS] Where do we stand on 9.3 bugs?

On Mon, Jan 13, 2014 at 5:16 PM, Tom Lane wrote: > What remaining issues are there blocking a 9.3.3 release? Well hardly a blocker since this has missed 2 releases already, but I'm still hopeful to get many PGXS-based extensions to build again without the dreaded "install: will not overwrite just

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Jan 13, 2014, at 19:03, Hannu Krosing wrote: > On 01/13/2014 09:53 PM, Trond Myklebust wrote: >> On Jan 13, 2014, at 15:40, Andres Freund wrote: >> >>> On 2014-01-13 15:15:16 -0500, Robert Haas wrote: On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner wrote: > I notice, Josh, that yo

Re: [HACKERS] plpgsql.consistent_into

On 1/14/14, 1:57 AM, Tom Lane wrote: Whatever your opinion of the default behavior, the fact that it's been that way for upwards of fifteen years without any mass protests should give you pause about changing it. For what it's worth, my patch does not change the default behaviour. I don't thi

Re: [HACKERS] plpgsql.consistent_into

On 1/13/14, 6:36 PM, Florian Pflug wrote: On Jan14, 2014, at 01:20 , Jim Nasby wrote: >On 1/13/14, 5:57 PM, Josh Berkus wrote: >>On 01/13/2014 03:41 PM, Florian Pflug wrote: >>>It therefor isn't an oversight that SELECT ... INTO allows multiple result rows >>>but INSERT/UPDATE/DELETE forbids

Re: [HACKERS] plpgsql.consistent_into

On 01/13/2014 04:20 PM, Jim Nasby wrote: > On 1/13/14, 5:57 PM, Josh Berkus wrote: >> I *really* don't want to go through all my old code to find places where >> I used SELECT ... INTO just to pop off the first row, and ignored the >> rest. I doubt anyone else does, either. > > Do you regularly h

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon 13-01-14 22:36:06, Mel Gorman wrote: > On Mon, Jan 13, 2014 at 06:27:03PM -0200, Claudio Freire wrote: > > On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby wrote: > > > On 1/13/14, 2:19 PM, Claudio Freire wrote: > > >> > > >> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas > > >> wrote: > > >>> > >

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

The issue with O_DIRECT is actually a much more general issue --- namely, database programmers that for various reasons decide they don't want to go down the O_DIRECT route, but then care about performance. PostgreSQL is not the only database which is had this issue. There are two papers at this

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Jan 13, 2014, at 16:03, Robert Haas wrote: > On Mon, Jan 13, 2014 at 3:53 PM, Trond Myklebust wrote: >> O_DIRECT was specifically designed to solve the problem of double buffering >> between applications and the kernel. Why are you not able to use that in >> these situations? > > O_DIRECT

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon 13-01-14 22:26:45, Mel Gorman wrote: > The flipside is also meant to hold true. If you know data will be needed > in the near future then posix_fadvise(POSIX_FADV_WILLNEED). Glancing at > the implementation it does a forced read-ahead on the range of pages of > interest. It doesn't look like

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, 2014-01-13 at 22:12 +0100, Andres Freund wrote: > On 2014-01-13 12:34:35 -0800, James Bottomley wrote: > > On Mon, 2014-01-13 at 14:32 -0600, Jim Nasby wrote: > > > Well, if we were to collaborate with the kernel community on this then > > > presumably we can do better than that for evictio

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan10, 2014, at 22:27 , David Rowley wrote: > As the patch stands at the moment, I currently have a regression test > which currently fails due to these extra zeros after the decimal point: > > -- This test currently fails due extra trailing 0 digits. > SELECT SUM(n::numeric) OVER (ORDER BY i

Re: [HACKERS] plpgsql.consistent_into

Florian Pflug writes: > On Jan14, 2014, at 01:20 , Jim Nasby wrote: >> And if we've always had it, why on earth didn't we make STRICT the default >> behavior? > Dunno, but AFAIK pl/pgsql mimics Oracle's PL/SQL, at least in some aspects, > so maybe this is one of the areas where we just do what o

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 1/13/14, 4:47 PM, Jan Kara wrote: Note to postgres guys: I think you should have a look at the proposed 'vrange' system call. The latest posting is here: http://www.spinics.net/lists/linux-mm/msg67328.html. It contains a rather detailed description of the feature. And if the feature looks good

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On 1/13/14, 3:04 PM, Jeff Janes wrote: I think the above is pretty simple for both interaction (allow us to inject a clean page into the file page cache) and policy (forget it after you hand it to us, then remember it again when we hand it back to you clean). And I think it would pretty like

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 1/13/14, 4:44 PM, Andres Freund wrote: > > One major usecase is transplanting a page comming from postgres' > >buffers into the kernel's buffercache because the latter has a much > >better chance of properly allocating system resources across independent > >applications running. > >If you wa

Re: [HACKERS] plpgsql.consistent_into

(Responding to both of your mails here) On Jan14, 2014, at 01:20 , Jim Nasby wrote: > On 1/13/14, 5:57 PM, Josh Berkus wrote: >> On 01/13/2014 03:41 PM, Florian Pflug wrote: >>> It therefor isn't an oversight that SELECT ... INTO allows multiple result >>> rows >>> but INSERT/UPDATE/DELETE forbi

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On 1/13/14, 5:33 PM, Craig Ringer wrote: So I guess the question is: Is it worth all that hassle to remove a misfeature you have to go out of your way to use? Is support for non-1 lower bounds stopping us from doing something useful and important? Or is it just an irritation that it exists? It'

Re: [HACKERS] Disallow arrays with non-standard lower bounds

Craig Ringer writes: > So I guess the question is: Is it worth all that hassle to remove a > misfeature you have to go out of your way to use? Is support for non-1 > lower bounds stopping us from doing something useful and important? Or > is it just an irritation that it exists? I think the argum

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 7:36 PM, Mel Gorman wrote: > That could be something we look at. There are cases buried deep in the > VM where pages get shuffled to the end of the LRU and get tagged for > reclaim as soon as possible. Maybe you need access to something like > that via posix_fadvise to say

Re: [HACKERS] plpgsql.consistent_into

On 1/13/14, 6:16 PM, Florian Pflug wrote: On Jan14, 2014, at 00:52 , Marko Tiikkaja wrote: When I've worked with PL/PgSQL, this has been a source of a few bugs that would have been noticed during testing if the behaviour of INTO wasn't as dangerous as it is right now. The question is, how man

Re: [HACKERS] plpgsql.consistent_into

On 1/13/14, 5:57 PM, Josh Berkus wrote: On 01/13/2014 03:41 PM, Florian Pflug wrote: It therefor isn't an oversight that SELECT ... INTO allows multiple result rows but INSERT/UPDATE/DELETE forbids them, it's been done that way on purpose and for a reason. We shouldn't be second-guessing ourselv

Re: [HACKERS] nested hstore patch

On Mon, January 13, 2014 18:30, Andrew Dunstan wrote: > > > On 01/13/2014 11:16 AM, Oleg Bartunov wrote: >> Andrew, >> >> did you run perl script ? Actually, I found, that operator table needs >> to be fixed. >> > > No. My build machine doesn't actually have DBD::Pg installed. Can you > send me a p

Re: [HACKERS] plpgsql.consistent_into

On Jan14, 2014, at 00:52 , Marko Tiikkaja wrote: > When I've worked with PL/PgSQL, this has been a source of a few bugs that > would have been noticed during testing if the behaviour of INTO wasn't as > dangerous as it is right now. The question is, how many bugs stemmed from wrong SQL queries, a

Re: [HACKERS] Syntax of INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On 1/13/14, 5:05 PM, Peter Geoghegan wrote: On Mon, Jan 13, 2014 at 2:20 PM, Jim Nasby wrote: Well, a common case for INSERT RETURNING is to get your set of surrogate keys back; so I think users would want the ability to RETURN what finally made it into the table. Your update can also have a

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On 01/14/2014 12:33 AM, Craig Ringer wrote: > On 01/14/2014 12:40 AM, Merlin Moncure wrote: >> On Sun, Jan 12, 2014 at 4:38 AM, Craig Ringer wrote: >>> Implicit casts to text, anybody? >> This backward compatibility break orphaned the company I work for on >> 8.1 until last year and very nearly ca

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 01/13/2014 09:53 PM, Trond Myklebust wrote: > On Jan 13, 2014, at 15:40, Andres Freund wrote: > >> On 2014-01-13 15:15:16 -0500, Robert Haas wrote: >>> On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner wrote: I notice, Josh, that you didn't mention the problems many people have run int

Re: [HACKERS] plpgsql.consistent_into

On 01/13/2014 03:41 PM, Florian Pflug wrote: > It therefor isn't an oversight that SELECT ... INTO allows multiple result > rows > but INSERT/UPDATE/DELETE forbids them, it's been done that way on purpose and > for a reason. We shouldn't be second-guessing ourselves by changing that > later - > n

Re: [HACKERS] plpgsql.consistent_into

On 1/14/14, 12:41 AM, Florian Pflug wrote: In fact, after reading the documentation on SELECT ... INTO, I'm convinced the the whole consistent_into thing is a bad idea. The documentation states clearly that For INSERT/UPDATE/DELETE with RETURNING, PL/pgSQL reports an error for more than o

Re: [HACKERS] plpgsql.consistent_into

On Jan13, 2014, at 22:49 , Jim Nasby wrote: > ISTM that in this case, it should be safe to make the new default behavior > STRICT; > if you forget to set the GUC to disable than you'll get an error that points > directly > at the problem, at which point you'll go "Oh, yeah... I forgot to set X..

Re: [HACKERS] GIN improvements part 1: additional information

On 13.1.2014 18:07, Alexander Korotkov wrote: > On Sat, Jan 11, 2014 at 6:15 AM, Tomas Vondra > wrote: > > On 8.1.2014 22:58, Alexander Korotkov wrote: > > Thanks for reporting. Fixed version is attached. > > I've tried to rerun the 'archie' benchmark with the c

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On 01/14/2014 12:40 AM, Merlin Moncure wrote: > On Sun, Jan 12, 2014 at 4:38 AM, Craig Ringer wrote: >> Implicit casts to text, anybody? > > This backward compatibility break orphaned the company I work for on > 8.1 until last year and very nearly caused postgres to be summarily > extirpated (onl

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On 01/13/2014 02:26 PM, Mel Gorman wrote: > Really? > > zone_reclaim_mode is often a complete disaster unless the workload is > partitioned to fit within NUMA nodes. On older kernels enabling it would > sometimes cause massive stalls. I'm actually very surprised to hear it > fixes anything and wou

Re: [HACKERS] Standalone synchronous master

On Jan13, 2014, at 22:30 , "Joshua D. Drake" wrote: > On 01/13/2014 01:14 PM, Jim Nasby wrote: >> >> On 1/13/14, 12:21 PM, Joshua D. Drake wrote: >>> >>> On 01/13/2014 10:12 AM, Hannu Krosing wrote: >> In other words, if we're going to have auto-degrade, the most >> intelligent place for

Re: [HACKERS] Syntax of INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Mon, Jan 13, 2014 at 2:20 PM, Jim Nasby wrote: > Well, a common case for INSERT RETURNING is to get your set of surrogate > keys back; so I think users would want the ability to RETURN what finally > made it into the table. Your update can also have a RETURNING clause. I'm not necessarily that

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 11:38:44PM +0100, Jan Kara wrote: > On Mon 13-01-14 22:26:45, Mel Gorman wrote: > > The flipside is also meant to hold true. If you know data will be needed > > in the near future then posix_fadvise(POSIX_FADV_WILLNEED). Glancing at > > the implementation it does a forced re

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 2014-01-13 14:19:56 -0800, James Bottomley wrote: > > Frequently mmap()/madvise()/munmap()ing 8kb chunks has > > horrible consequences for performance/scalability - very quickly you > > contend on locks in the kernel. > > Is this because of problems in the mmap_sem? It's been a while since I

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Mon, Jan 13, 2014 at 12:58 PM, Heikki Linnakangas wrote: > Well, even if you don't agree that locking all the conflicting rows for > update is sensible, it's still perfectly sensible to return the rejected > rows to the user. For example, you're inserting N rows, and if some of them > violate a

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 06:27:03PM -0200, Claudio Freire wrote: > On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby wrote: > > On 1/13/14, 2:19 PM, Claudio Freire wrote: > >> > >> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas > >> wrote: > >>> > >>> On a related note, there's also the problem of double-b

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 03:15:16PM -0500, Robert Haas wrote: > On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner wrote: > > I notice, Josh, that you didn't mention the problems many people > > have run into with Transparent Huge Page defrag and with NUMA > > access. > Ok, there are at least three

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On Mon, Jan 13, 2014 at 3:45 PM, David Fetter wrote: > On Mon, Jan 13, 2014 at 10:40:57AM -0600, Merlin Moncure wrote: >> This project has no deprecation policy, > > I believe it actually does, although it's not a formal, written > policy. Would you like to help draft one up? Lack of 'formal, wr

Re: [HACKERS] Syntax of INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On 1/12/14, 9:35 PM, Andreas Karlsson wrote: On 01/12/2014 11:20 PM, Peter Geoghegan wrote: On Sun, Jan 12, 2014 at 8:12 AM, Andreas Karlsson wrote: On 01/11/2014 11:42 PM, Peter Geoghegan wrote: I recently suggested that rather than RETURNING REJECTS, we could have a REJECTING clause, which

Re: [HACKERS] [Lsf-pc] Linux kernel impact on PostgreSQL performance

Everyone, I am looking for one or more hackers to go to Collab with me to discuss this. If you think that might be you, please let me know and I'll look for funding for your travel. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hac

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On Mon, Jan 13, 2014 at 4:45 PM, David Fetter wrote: > On Mon, Jan 13, 2014 at 10:40:57AM -0600, Merlin Moncure wrote: >> On Sun, Jan 12, 2014 at 4:38 AM, Craig Ringer wrote: >> > Implicit casts to text, anybody? >> >> This backward compatibility break orphaned the company I work for on >> 8.1 un

Re: [HACKERS] plpgsql.consistent_into

On 1/13/14, 1:44 AM, Pavel Stehule wrote: 2014/1/12 Florian Pflug mailto:f...@phlo.org>> On Jan12, 2014, at 22:37 , Pavel Stehule mailto:pavel.steh...@gmail.com>> wrote: > There is GUC for variable_conflict already too. In this case I would to > enable this functionality everyw

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On Mon, Jan 13, 2014 at 10:40:57AM -0600, Merlin Moncure wrote: > On Sun, Jan 12, 2014 at 4:38 AM, Craig Ringer wrote: > > Implicit casts to text, anybody? > > This backward compatibility break orphaned the company I work for on > 8.1 until last year and very nearly caused postgres to be summaril

Re: [HACKERS] Standalone synchronous master

On 01/13/2014 01:14 PM, Jim Nasby wrote: On 1/13/14, 12:21 PM, Joshua D. Drake wrote: On 01/13/2014 10:12 AM, Hannu Krosing wrote: In other words, if we're going to have auto-degrade, the most intelligent place for it is in RepMgr/HandyRep/OmniPITR/pgPoolII/whatever. It's also the *easiest*

Re: [HACKERS] [Lsf-pc] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 9:12 PM, Andres Freund wrote: > For one, postgres doesn't use mmap for files (and can't without major > new interfaces). Frequently mmap()/madvise()/munmap()ing 8kb chunks has > horrible consequences for performance/scalability - very quickly you > contend on locks in the k

Re: [HACKERS] Disallow arrays with non-standard lower bounds

On 1/13/14, 10:40 AM, Merlin Moncure wrote: On Sun, Jan 12, 2014 at 4:38 AM, Craig Ringer wrote: >Implicit casts to text, anybody? This backward compatibility break orphaned the company I work for on 8.1 until last year and very nearly caused postgres to be summarily extirpated (only rescued a

Re: [HACKERS] Standalone synchronous master

On 2014-01-13 15:14:21 -0600, Jim Nasby wrote: > On 1/13/14, 12:21 PM, Joshua D. Drake wrote: > > > >On 01/13/2014 10:12 AM, Hannu Krosing wrote: > In other words, if we're going to have auto-degrade, the most > intelligent place for it is in > RepMgr/HandyRep/OmniPITR/pgPoolII/whatever

Re: [HACKERS] Standalone synchronous master

On 1/13/14, 12:21 PM, Joshua D. Drake wrote: On 01/13/2014 10:12 AM, Hannu Krosing wrote: In other words, if we're going to have auto-degrade, the most intelligent place for it is in RepMgr/HandyRep/OmniPITR/pgPoolII/whatever. It's also the *easiest* place. Anything we do *inside* Postgres is

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 2014-01-13 12:34:35 -0800, James Bottomley wrote: > On Mon, 2014-01-13 at 14:32 -0600, Jim Nasby wrote: > > Well, if we were to collaborate with the kernel community on this then > > presumably we can do better than that for eviction... even to the > > extent of "here's some data from this range

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

Josh Berkus wrote: > Wanna go to Collab? I don't think that works out for me, but thanks for suggesting it. I'd be happy to brainstorm with anyone who does go about issues to discuss; although the ones I keep running into have already been mentioned. Regarding the problems others have mentione

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Jan 13, 2014, at 15:40, Andres Freund wrote: > On 2014-01-13 15:15:16 -0500, Robert Haas wrote: >> On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner wrote: >>> I notice, Josh, that you didn't mention the problems many people >>> have run into with Transparent Huge Page defrag and with NUMA >>>

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, 2014-01-13 at 14:32 -0600, Jim Nasby wrote: > On 1/13/14, 2:27 PM, Claudio Freire wrote: > > On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby wrote: > >> On 1/13/14, 2:19 PM, Claudio Freire wrote: > >>> > >>> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas > >>> wrote: > > On a related n

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 3:53 PM, Trond Myklebust wrote: > O_DIRECT was specifically designed to solve the problem of double buffering > between applications and the kernel. Why are you not able to use that in > these situations? O_DIRECT was apparently designed by a deranged monkey on some seri

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 12:32 PM, Jim Nasby wrote: > On 1/13/14, 2:27 PM, Claudio Freire wrote: > >> On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby wrote: >> >>> On 1/13/14, 2:19 PM, Claudio Freire wrote: >>> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas wrote: > > On a r

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 2014-01-13 15:53:36 -0500, Trond Myklebust wrote: > > I've wondered before if there wouldn't be a chance for postgres to say > > "my dear OS, that the file range 0-8192 of file x contains y, no need to > > reread" and do that when we evict a page from s_b but I never dared to > > actually propos

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On 01/13/2014 10:53 PM, Peter Geoghegan wrote: On Mon, Jan 13, 2014 at 12:17 PM, Robert Haas wrote: For what it's worth, I agree with Heikki. There's probably nothing sensible an upsert can do if it conflicts with more than one tuple, but if it conflicts with just exactly one, it oughta be OK.

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On 1/13/14, 2:37 PM, Claudio Freire wrote: On Mon, Jan 13, 2014 at 5:32 PM, Jim Nasby wrote: That's my point. In terms of kernel-postgres interaction, it's fairly simple. What's not so simple, is figuring out what policy to use. Remember, you cannot tell the kernel to put some page in its pag

Re: [HACKERS] PoC: Partial sort

Hi Alexander, First, thanks a lot for working on this feature. This PostgreSQL shortcoming crops up in all the time in web applications that implement paging by multiple sorted columns. I've been trying it out in a few situations. I implemented a new enable_partialsort GUC to make it easier to tu

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Mon, Jan 13, 2014 at 12:17 PM, Robert Haas wrote: > For what it's worth, I agree with Heikki. There's probably nothing > sensible an upsert can do if it conflicts with more than one tuple, > but if it conflicts with just exactly one, it oughta be OK. If there is exactly one, *and* the existin

Re: [HACKERS] Planning time in explain/explain analyze

On Mon, Jan 13, 2014 at 3:40 PM, Tom Lane wrote: > Robert Haas writes: >> On Mon, Jan 13, 2014 at 3:23 PM, Tom Lane wrote: >>> Meh. Why? This would only come into play for EXPLAIN EXECUTE stmtname. >>> I don't think users would be surprised to see a report of minimal planning >>> time for that

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

On Sat, Jan 11, 2014 at 1:08 AM, Amit Kapila wrote: > Yes, currently this applies to update, what I have in mind is that > in future if some one wants to use WAL compression for any other > operation like 'full_page_writes', then it can be easily extendible. > > To be honest, I have not evaluated

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On 2014-01-13 15:15:16 -0500, Robert Haas wrote: > On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner wrote: > > I notice, Josh, that you didn't mention the problems many people > > have run into with Transparent Huge Page defrag and with NUMA > > access. > > Amen to that. Actually, I think NUMA ca

Re: [HACKERS] Planning time in explain/explain analyze

Robert Haas writes: > On Mon, Jan 13, 2014 at 3:23 PM, Tom Lane wrote: >> Meh. Why? This would only come into play for EXPLAIN EXECUTE stmtname. >> I don't think users would be surprised to see a report of minimal planning >> time for that. In fact, it might be a good thing, as it would make i

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 5:32 PM, Jim Nasby wrote: >> >> That's my point. In terms of kernel-postgres interaction, it's fairly >> simple. >> >> What's not so simple, is figuring out what policy to use. Remember, >> you cannot tell the kernel to put some page in its page cache without >> reading it

  1   2   >