Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread David G. Johnston
On Sun, Apr 3, 2016 at 9:49 PM, Igal @ Lucee.org wrote: > On 4/3/2016 4:34 PM, Dave Cramer wrote: > > > On 4/3/2016 8:21 AM, Dave Cramer wrote: > >> >> I'd like to turn this question around. Are there good reasons to use -ng >> over pgjdbc ? >> >> As to your question, you may be

Re: [HACKERS] pgbench more operators & functions

2016-04-03 Thread Simon Riggs
On 4 April 2016 at 01:14, Michael Paquier wrote: > I'd say why not. > I'd say "why not wait?". Minor, non-urgent patches will definitely go nowhere for a long time, so it gains nobody to submit now. Submitting patches during freeze has been discouraged for many

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Igal @ Lucee.org
On 4/3/2016 4:34 PM, Dave Cramer wrote: On 4/3/2016 8:21 AM, Dave Cramer wrote: I'd like to turn this question around. Are there good reasons to use -ng over pgjdbc ? As to your question, you may be interested to know that pgjdbc is more performant than ng. That's good

Re: [HACKERS] Proposal: Generic WAL logical messages

2016-04-03 Thread Petr Jelinek
Hi, I rebased this patch on top of current master as the generic wal commit added some conflicting changes. Also fixed couple of typos in comments and added non ascii message to test. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support,

Re: [HACKERS] psql metaqueries with \gexec

2016-04-03 Thread Corey Huinker
On Sun, Apr 3, 2016 at 8:42 PM, Corey Huinker wrote: > On Sun, Apr 3, 2016 at 7:43 PM, Tom Lane wrote: > >> Corey Huinker writes: >> >>> + The secondary queries are executed in top-to-bottom, >> >>> left-to-right

Re: [HACKERS] Default Roles (was: Additional role attributes)

2016-04-03 Thread Stephen Frost
Fujii, * Fujii Masao (masao.fu...@gmail.com) wrote: > On Tue, Jul 14, 2015 at 3:46 AM, Stephen Frost wrote: > > Possibly, but I'd need to look at them more closely than I have time to > > right now. Can you provide a use-case? That would certainly help. > > I have seen the

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Dave Cramer
On 3 April 2016 at 22:20, Stephen Frost wrote: > * Craig Ringer (cr...@2ndquadrant.com) wrote: > > On 4 April 2016 at 10:13, Dave Cramer wrote: > > > Async notification is the easier part, I wasn't aware that the ssl > library > > > had this problem

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Stephen Frost
* Craig Ringer (cr...@2ndquadrant.com) wrote: > On 4 April 2016 at 10:13, Dave Cramer wrote: > > Async notification is the easier part, I wasn't aware that the ssl library > > had this problem though > > AFAIK the issue is that even if there are bytes available on the

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Stephen Frost
Dave, * Dave Cramer (p...@fastcrypt.com) wrote: > On 3 April 2016 at 21:56, Stephen Frost wrote: > > * Dave Cramer (p...@fastcrypt.com) wrote: > > > On 3 April 2016 at 15:35, Stephen Frost wrote: > > > > Not generally much of a JDBC user myself, but the

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Craig Ringer
On 4 April 2016 at 10:13, Dave Cramer wrote: > Async notification is the easier part, I wasn't aware that the ssl library > had this problem though > > AFAIK the issue is that even if there are bytes available on the underlying socket, the SSL lib doesn't know if that means

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Dave Cramer
On 3 April 2016 at 21:56, Stephen Frost wrote: > Dave, > > * Dave Cramer (p...@fastcrypt.com) wrote: > > On 3 April 2016 at 15:35, Stephen Frost wrote: > > > Not generally much of a JDBC user myself, but the inability to avoid > > > polling for LISTEN

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Stephen Frost
Dave, * Dave Cramer (p...@fastcrypt.com) wrote: > On 3 April 2016 at 15:35, Stephen Frost wrote: > > Not generally much of a JDBC user myself, but the inability to avoid > > polling for LISTEN notifications is a pretty big annoyance, which I just > > ran into with a client.

Re: [HACKERS] Recovery test failure for recovery_min_apply_delay on hamster

2016-04-03 Thread Michael Paquier
On Fri, Apr 1, 2016 at 10:02 AM, Michael Paquier wrote: > On Fri, Apr 1, 2016 at 4:14 AM, Alvaro Herrera > wrote: >> Noah Misch wrote: >> >>> The above-described topic is currently a PostgreSQL 9.6 open item. Alvaro, >>> since you committed

Re: [HACKERS] Using quicksort for every external sort run

2016-04-03 Thread Peter Geoghegan
On Sun, Apr 3, 2016 at 4:08 PM, Greg Stark wrote: >> SELECT * FROM (SELECT * FROM (SELECT a FROM int_test UNION SELECT a >> FROM int_test_padding) gg OFFSET 1e10) ff; > > ISTM OFFSET binds more loosely than UNION so these should be equivalent. Not exactly: postgres=# explain

Re: [HACKERS] psql metaqueries with \gexec

2016-04-03 Thread Corey Huinker
On Sun, Apr 3, 2016 at 7:43 PM, Tom Lane wrote: > Corey Huinker writes: > >>> + The secondary queries are executed in top-to-bottom, > >>> left-to-right order, so the command > > >> I took that as meaning what I said above. > > > Would using

Re: [HACKERS] pgbench more operators & functions

2016-04-03 Thread Michael Paquier
On Mon, Apr 4, 2016 at 1:15 AM, Fabien COELHO wrote: >>> Here is a simple patch... >> >> The patch deadline has passed and we are in the last CF of 9.6, as I'm >> sure you know. > > Yes I know, I'm ok with that, I was just putting stuff in the queue for > later, I was not

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-04-03 Thread Alex Shulgin
On Mon, Apr 4, 2016 at 1:06 AM, Tom Lane wrote: > Alex Shulgin writes: > > On Sun, Apr 3, 2016 at 10:53 PM, Tom Lane wrote: > >> The reason for checking toowide_cnt is that if it's greater than zero, > >> then in fact the track

Re: [HACKERS] psql metaqueries with \gexec

2016-04-03 Thread Tom Lane
Corey Huinker writes: >>> + The secondary queries are executed in top-to-bottom, >>> left-to-right order, so the command >> I took that as meaning what I said above. > Would using the term https://en.wikipedia.org/wiki/Row-major_order be more > clear? Meh, I

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Dave Cramer
On 3 April 2016 at 12:18, Igal @ Lucee.org wrote: > On 4/3/2016 8:21 AM, Dave Cramer wrote: > > > On 9 March 2016 at 20:49, Craig Ringer wrote: > >> On 3/8/2016 5:12 PM, Craig Ringer wrote: >> >>> >>> Are there good reasons to use pgjdbc over pgjdbc-ng

Re: [HACKERS] psql metaqueries with \gexec

2016-04-03 Thread Corey Huinker
> > + The secondary queries are executed in top-to-bottom, > left-to-right order, so the command > > I took that as meaning what I said above. > > Would using the term https://en.wikipedia.org/wiki/Row-major_order be more clear? The secondary queries are executed in row-major order,

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Dave Cramer
On 3 April 2016 at 15:35, Stephen Frost wrote: > * Dave Cramer (p...@fastcrypt.com) wrote: > > On 9 March 2016 at 20:49, Craig Ringer wrote: > > > > > On 10 March 2016 at 00:41, Igal @ Lucee.org wrote: > > > > > >> On 3/8/2016 5:12 PM,

Re: [HACKERS] psql metaqueries with \gexec

2016-04-03 Thread Tom Lane
Corey Huinker writes: > On Sun, Apr 3, 2016 at 4:26 PM, Tom Lane wrote: >> I'm not Jim, but I have a question: what's the motivation for the >> Fortran-order traversal of the result (down rows before across columns)? > If I am understanding you

Re: [HACKERS] Using quicksort for every external sort run

2016-04-03 Thread Greg Stark
On Sun, Apr 3, 2016 at 12:50 AM, Peter Geoghegan wrote: > 1459308434.753 2016-03-30 05:27:14 CEST STATEMENT: SELECT * FROM > (SELECT a FROM int_test UNION SELECT a FROM int_test_padding OFFSET > 1e10) ff; > > I think that this is invalid, because the query was intended as this:

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-04-03 Thread Tom Lane
Alex Shulgin writes: > On Sun, Apr 3, 2016 at 10:53 PM, Tom Lane wrote: >> The reason for checking toowide_cnt is that if it's greater than zero, >> then in fact the track list does NOT include all values seen, and it's >> flat-out wrong to claim that

Re: [HACKERS] psql metaqueries with \gexec

2016-04-03 Thread Corey Huinker
On Sun, Apr 3, 2016 at 4:26 PM, Tom Lane wrote: > Robert Haas writes: > > Jim, can you re-review this? > > I'm not Jim, but I have a question: what's the motivation for the > Fortran-order traversal of the result (down rows before across columns)? > It

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-04-03 Thread Alex Shulgin
On Sun, Apr 3, 2016 at 10:53 PM, Tom Lane wrote: > Alex Shulgin writes: > > This recalled observation can now also explain to me why in the > regression > > you've seen, the short path was not followed: my bet is that stadistinct > > appeared

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

2016-04-03 Thread Simon Riggs
On 3 April 2016 at 22:09, Tomas Vondra wrote: > On 04/03/2016 10:06 PM, Simon Riggs wrote: > >> On 14 March 2016 at 19:42, Tomas Vondra > > wrote: >> >> ... > >> >> >> I'd like to split this into 2

Re: [HACKERS] Re: BUG #13685: Archiving while idle every archive_timeout with wal_level hot_standby

2016-04-03 Thread Simon Riggs
On 3 April 2016 at 21:32, Simon Riggs wrote: > On 14 March 2016 at 17:46, David Steele wrote: > >> On 2/24/16 12:40 AM, Michael Paquier wrote: >> >> This has the merit to be clear, thanks for the input. Whatever the >>> approach taken at the end we

Re: [HACKERS] snapshot too old, configured by time

2016-04-03 Thread Jeff Janes
On Wed, Mar 30, 2016 at 12:34 PM, Kevin Grittner wrote: > On Sat, Mar 19, 2016 at 1:27 AM, Jeff Janes wrote: > >> I'm not sure if this is operating as expected. >> >> I set the value to 1min. >> >> I set up a test like this: >> >> pgbench -i >> >> pgbench

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

2016-04-03 Thread Tomas Vondra
On 04/03/2016 10:06 PM, Simon Riggs wrote: On 14 March 2016 at 19:42, Tomas Vondra > wrote: ... I'd like to split this into 2 patches 1) Add FK info to relcache 2) use FK info in planner Would the credit for this be 1)

Re: [HACKERS] Using quicksort for every external sort run

2016-04-03 Thread Peter Geoghegan
I just mean that, as you say, trace_sort is described in the documentation. I don't think we'll end up with any kind of cost model here, so where that would need to happen is only an academic matter. The create index parameter would only be an option for the DBA. That's about the only case I can

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-04-03 Thread Tom Lane
Alex Shulgin writes: > This recalled observation can now also explain to me why in the regression > you've seen, the short path was not followed: my bet is that stadistinct > appeared negative. Yes, I think that's right. The table under consideration had just a few live

Re: [HACKERS] Using quicksort for every external sort run

2016-04-03 Thread Tomas Vondra
On 04/03/2016 09:41 PM, Peter Geoghegan wrote: Hi Tomas, ... 3) replacement_sort_mem GUC I'm not quite sure what's the plan with this GUC. It was useful for development, but it seems to me it's pretty difficult to tune it in practice (especially if you don't know the internals, which users

Re: [HACKERS] Re: BUG #13685: Archiving while idle every archive_timeout with wal_level hot_standby

2016-04-03 Thread Simon Riggs
On 14 March 2016 at 17:46, David Steele wrote: > On 2/24/16 12:40 AM, Michael Paquier wrote: > > This has the merit to be clear, thanks for the input. Whatever the >> approach taken at the end we have two candidates: >> - Extend XLogInsert() with an extra argument for flags

Re: [HACKERS] psql metaqueries with \gexec

2016-04-03 Thread Tom Lane
Robert Haas writes: > Jim, can you re-review this? I'm not Jim, but I have a question: what's the motivation for the Fortran-order traversal of the result (down rows before across columns)? It seems less than intuitive to do it that way. Perhaps there's a good reason, but

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-04-03 Thread Alex Shulgin
On Sun, Apr 3, 2016 at 8:24 AM, Alex Shulgin wrote: > > On Sun, Apr 3, 2016 at 7:49 AM, Tom Lane wrote: >> >> Alex Shulgin writes: >> > On Sun, Apr 3, 2016 at 7:18 AM, Tom Lane wrote: >> >> Well, we have to

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

2016-04-03 Thread Simon Riggs
On 14 March 2016 at 19:42, Tomas Vondra wrote: > Hi, > > On 03/14/2016 02:12 PM, David Steele wrote: > >> Hi Thomas, >> > ... > >> I don't think it would be clear to any reviewer which patch to apply >> even if they were working. I'm marking this "waiting for

Re: [HACKERS] Using quicksort for every external sort run

2016-04-03 Thread Peter Geoghegan
Hi Tomas, Overall, I agree with your summary. On Sun, Apr 3, 2016 at 5:24 AM, Tomas Vondra wrote: > So, let me sum this up, the way I understand the current status. > > > 1) overall, the patch seems to be a clear performance improvement I think that's clear. There

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Stephen Frost
* Dave Cramer (p...@fastcrypt.com) wrote: > On 9 March 2016 at 20:49, Craig Ringer wrote: > > > On 10 March 2016 at 00:41, Igal @ Lucee.org wrote: > > > >> On 3/8/2016 5:12 PM, Craig Ringer wrote: > >> > >>> One of the worst problems (IMO) is in the driver

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-04-03 Thread Alex Shulgin
On Sun, Apr 3, 2016, 18:40 Tom Lane wrote: > Alex Shulgin writes: > > > Well, if it's the only value it will be accepted simply because we are > > checking that special case already and don't even bother to loop through > > the track list. > > That

Re: [HACKERS] Add schema-qualified relnames in constraint error messages.

2016-04-03 Thread Alex Shulgin
On Sun, Apr 3, 2016, 18:32 Tom Lane wrote: > Peter Geoghegan writes: > > On Thu, Feb 11, 2016 at 9:50 AM, Robert Haas > wrote: > >> Wow, that's a fabulous idea. I see Oleksandr has tried to implement > >> it, although I haven't

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-04-03 Thread Tom Lane
Alex Shulgin writes: > On Sun, Apr 3, 2016 at 7:49 AM, Tom Lane wrote: >> If there is only one value, it will have 100% of the samples, so it would >> get included under just about any decision rule (other than "more than >> 100% of this value plus

Re: [HACKERS] Add schema-qualified relnames in constraint error messages.

2016-04-03 Thread Tom Lane
Peter Geoghegan writes: > On Thu, Feb 11, 2016 at 9:50 AM, Robert Haas wrote: >> Wow, that's a fabulous idea. I see Oleksandr has tried to implement >> it, although I haven't looked at the patch. But I think this would be >> REALLY helpful. > +1

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Igal @ Lucee.org
On 4/3/2016 8:21 AM, Dave Cramer wrote: On 9 March 2016 at 20:49, Craig Ringer > wrote: On 3/8/2016 5:12 PM, Craig Ringer wrote: Are there good reasons to use pgjdbc over pgjdbc-ng then? Maturity, support for older

Re: [HACKERS] pgbench more operators & functions

2016-04-03 Thread Fabien COELHO
Hello Simon, Here is a simple patch... The patch deadline has passed and we are in the last CF of 9.6, as I'm sure you know. Yes I know, I'm ok with that, I was just putting stuff in the queue for later, I was not asking for the patch to be considered right now. Another minor patch on

Re: [HACKERS] Proposal: RETURNING primary_key()

2016-04-03 Thread Dave Cramer
On 9 March 2016 at 20:49, Craig Ringer wrote: > On 10 March 2016 at 00:41, Igal @ Lucee.org wrote: > >> On 3/8/2016 5:12 PM, Craig Ringer wrote: >> >>> One of the worst problems (IMO) is in the driver architecture its self. >>> It attempts to prevent

Re: [HACKERS] remove wal_level archive

2016-04-03 Thread Michael Paquier
On Mon, Mar 14, 2016 at 11:46 PM, Michael Paquier wrote: > On Mon, Mar 14, 2016 at 12:50 PM, David Steele wrote: >> On 3/11/16 1:29 PM, David Steele wrote: >> >>> Unless anyone has objections I would like to mark this 'ready for >>> committer'. >>

Re: [HACKERS] Using quicksort for every external sort run

2016-04-03 Thread Tomas Vondra
Hi, So, let me sum this up, the way I understand the current status. 1) overall, the patch seems to be a clear performance improvement There's far more "green" cells than "red" ones in the spreadsheets, and the patch often shaves off 30-75% of the sort duration. Improvements are pretty much

Re: [HACKERS] Move PinBuffer and UnpinBuffer to atomics

2016-04-03 Thread Dilip Kumar
On Sun, Apr 3, 2016 at 2:28 PM, Amit Kapila wrote: > > What is the conclusion of this test? As far as I see, with the patch > (0001-WIP-Avoid-the-use-of-a-separate-spinlock-to-protect), the performance > degradation is not fixed, but with pin-unpin patch, the

Re: [HACKERS] Choosing parallel_degree

2016-04-03 Thread Julien Rouhaud
On 22/03/2016 07:58, Julien Rouhaud wrote: > On 21/03/2016 20:38, Julien Rouhaud wrote: >> On 21/03/2016 05:18, James Sewell wrote: >>> OK cool, thanks. >>> >>> Can we remove the minimum size limit when the per table degree setting >>> is applied? >>> >>> This would help for tables with 2 - 1000

Re: [HACKERS] Move PinBuffer and UnpinBuffer to atomics

2016-04-03 Thread Amit Kapila
On Sun, Apr 3, 2016 at 9:55 AM, Dilip Kumar wrote: > > On Fri, Apr 1, 2016 at 2:09 PM, Andres Freund wrote: > >> One interesting thing to do would be to use -P1 during the test and see >> how much the performance varies over time. >> > > I have run

Re: [HACKERS] pgbench more operators & functions

2016-04-03 Thread Simon Riggs
On 3 April 2016 at 06:54, Fabien COELHO wrote: > Here is a simple patch... The patch deadline has passed and we are in the last CF of 9.6, as I'm sure you know. Another minor patch on pgbench probably isn't going to help stabilise this release, so these changes won't be

[HACKERS] Small fix: avoid passing null pointers to memcpy()

2016-04-03 Thread Piotr Stefaniak
Hello, from running the regression test suite (including TAP tests) and also sqlsmith, I've got a couple of places where UBSan reported calls to memcpy() with null pointer passed as either source or destination. Patch attached. diff --git a/contrib/pgcrypto/px.c b/contrib/pgcrypto/px.c index

Re: [HACKERS] More stable query plans via more predictable column statistics

2016-04-03 Thread Alex Shulgin
On Sun, Apr 3, 2016 at 7:49 AM, Tom Lane wrote: > Alex Shulgin writes: > > On Sun, Apr 3, 2016 at 7:18 AM, Tom Lane wrote: > >> Well, we have to do *something* with the last (possibly only) value. > >> Neither "include always" nor