Re: [HACKERS] GIN data corruption bug(s) in 9.6devel

2015-12-21 Thread Tomas Vondra
On 12/21/2015 07:41 PM, Jeff Janes wrote: On Sat, Dec 19, 2015 at 3:19 PM, Tomas Vondra wrote: ... So both patches seem to do the trick, but (2) is faster. Not sure if this is expected. (BTW all the results are without asserts enabled). Do you know what the

Re: [HACKERS] tracking owner of extension-managed objects

2015-12-21 Thread Chapman Flack
On 12/21/2015 12:46 PM, Tom Lane wrote: > BTW, any such ownership relationship really needs to be reflected into > pg_shdepend, else someone might drop a role that still owns objects. > (I guess there are problems with extensions trying to do such things at > all, since we don't provide a way for

Re: [HACKERS] Avoiding pin scan during btree vacuum

2015-12-21 Thread Alvaro Herrera
Simon Riggs wrote: > During VACUUM of btrees, we need to pin all pages, even those where tuples > are not removed, which I am calling here the "pin scan". This is especially > a problem during redo, where removing one tuple from a 100GB btree can take > a minute or more. That causes replication

Re: [HACKERS] Re: Reusing abbreviated keys during second pass of ordered [set] aggregates

2015-12-21 Thread Peter Geoghegan
On Mon, Dec 21, 2015 at 10:53 AM, Robert Haas wrote: > PFA my proposal for comment changes for 9.5 and master. This is based > on your 0001, but I edited somewhat. Please let me know your > thoughts. I am not willing to go further and rearrange actual code in > 9.5 at

Re: [HACKERS] tracking owner of extension-managed objects

2015-12-21 Thread Chapman Flack
On 12/21/2015 02:30 PM, Chapman Flack wrote: > On 12/21/2015 12:46 PM, Tom Lane wrote: >> all, since we don't provide a way for extensions to hook into the DROP >> mechanisms. Perhaps that should be fixed.) > > That is literally *the very next* e-mail I was going to compose. > > I was looking

Re: [HACKERS] GIN data corruption bug(s) in 9.6devel

2015-12-21 Thread Jeff Janes
On Sat, Dec 19, 2015 at 3:19 PM, Tomas Vondra wrote: > Hi, > > On 11/06/2015 02:09 AM, Tomas Vondra wrote: >> >> Hi, >> >> On 11/06/2015 01:05 AM, Jeff Janes wrote: >>> >>> On Thu, Nov 5, 2015 at 3:50 PM, Tomas Vondra >>> wrote: >> >>

Re: [HACKERS] Patch: Implement failover on libpq connect level.

2015-12-21 Thread Alvaro Herrera
Victor Wagner wrote: > On Mon, 21 Dec 2015 17:18:37 +0300 > Teodor Sigaev wrote: > > > Sorry, but there is something wrong with your patch: > > % patch -p1 -C < ~/Downloads/libpq-failover-5.patch > > Really, somehow broken version of the patch got into message. It would be

Re: [HACKERS] Avoiding pin scan during btree vacuum

2015-12-21 Thread Tom Lane
Alvaro Herrera writes: > I think the new comment that talks about Toast Index should explain > *why* we can skip the pinning in all cases except that one, instead of > just saying we can do it. I've not looked at that code in a long while, but my recollection is that

[HACKERS] Coding note: truncating with strlcpy() is not such a hot idea

2015-12-21 Thread Tom Lane
There's been some effort to replace uses of strncpy() and our homegrown StrNCpy() macro with strlcpy(). I had my nose rubbed today in the fact that that isn't always a good transformation. The reason why not is that strlcpy() is defined to return strlen(src), which means that even after it's

Re: [HACKERS] Re: Reusing abbreviated keys during second pass of ordered [set] aggregates

2015-12-21 Thread Robert Haas
On Fri, Dec 18, 2015 at 2:22 PM, Peter Geoghegan wrote: > On Fri, Dec 18, 2015 at 9:35 AM, Robert Haas wrote: >>> Attached revision updates both the main commit (the optimization), and >>> the backpatch commit (updated the contract). >> >> - /*

Re: [HACKERS] Combining Aggregates

2015-12-21 Thread David Rowley
On 22 December 2015 at 01:30, Robert Haas wrote: > Can we use Tom's expanded-object stuff instead of introducing > aggserialfn and aggdeserialfn? In other words, if you have a > aggtranstype = INTERNAL, then what we do is: > > 1. Create a new data type that represents the

Re: [HACKERS] Parallel Aggregate

2015-12-21 Thread David Rowley
On 22 December 2015 at 04:16, Paul Ramsey wrote: > Shouldn’t parallel aggregate come into play regardless of scan selectivity? > I'd say that the costing should take into account the estimated number of groups. The more tuples that make it into each group, the more

Re: [HACKERS] Patch to improve a few appendStringInfo* calls

2015-12-21 Thread David Rowley
On 22 December 2015 at 01:58, Robert Haas wrote: > On Thu, Jul 2, 2015 at 6:08 AM, David Rowley > wrote: > >> I left out the changes like > >> > >>> - appendStringInfoString(, buf.data); > >>> +

Re: [HACKERS] Parallel Aggregate

2015-12-21 Thread Haribabu Kommi
On Tue, Dec 22, 2015 at 2:16 AM, Paul Ramsey wrote: > Shouldn’t parallel aggregate come into play regardless of scan selectivity? > I know in PostGIS land there’s a lot of stuff like: > > SELECT ST_Union(geom) FROM t GROUP BY areacode; > > Basically, in the BI case,

Re: [HACKERS] plpgsql - DECLARE - cannot to use %TYPE or %ROWTYPE for composite types

2015-12-21 Thread Pavel Stehule
Hi 2015-12-21 16:21 GMT+01:00 Artur Zakirov : > Hi. > I have tried to do some review of this patch. Below are my comments. > > Introduction: > > This patch fixes and adds the following functionality: > - %TYPE - now can be used for composite types. > - %ARRAYTYPE -

Re: [HACKERS] Patch to improve a few appendStringInfo* calls

2015-12-21 Thread Pavel Stehule
2015-12-21 13:58 GMT+01:00 Robert Haas : > On Thu, Jul 2, 2015 at 6:08 AM, David Rowley > wrote: > >> I left out the changes like > >> > >>> - appendStringInfoString(, buf.data); > >>> + appendBinaryStringInfo(,

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

2015-12-21 Thread Tom Lane
Viktor Leis writes: > I think it would be a good start to distinguish between nested loop > joins with and without a index. We do. > In my opinion, the latter should simply NEVER be chosen. So, if you're given a query with a non-equality join condition that doesn't match any

Re: [HACKERS] Combining Aggregates

2015-12-21 Thread David Rowley
On 18 December 2015 at 01:28, David Rowley wrote: > # select sum(x::numeric) from generate_series(1,3) x(x); > ERROR: invalid memory alloc request size 18446744072025250716 > > The reason that this happens is down to the executor always thinking that > Aggref

Re: [HACKERS] Optimizing away second VACUUM heap scan when only BRIN indexes on table

2015-12-21 Thread Peter Geoghegan
On Mon, Dec 21, 2015 at 1:24 AM, Simon Riggs wrote: > Given BRIN's characteristics, such a table design is compelling when the > table is very large, yet possible only for certain use cases. You can say the same thing about BRIN itself, of course. -- Peter Geoghegan

Re: [HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-12-21 Thread Heikki Linnakangas
On 17/12/15 19:07, Robert Haas wrote: On Mon, Dec 14, 2015 at 6:08 PM, Tomas Vondra wrote: So we know that we should expect about (prev_wal_bytes - wal_bytes) + (prev_wal_fpw_bytes - wal_fpw_bytes) ( regular WAL) + ( FPW WAL

Re: [HACKERS] Costing foreign joins in postgres_fdw

2015-12-21 Thread Ashutosh Bapat
On Fri, Dec 18, 2015 at 6:39 PM, Albe Laurenz wrote: > Ashutosh Bapat wrote: > > Costs for foreign queries are either obtained from the foreign server > using EXPLAIN (if > > use_remote_estimate is ON) otherwise they are cooked up locally based on > the statistics

Re: [HACKERS] Patch: Implement failover on libpq connect level.

2015-12-21 Thread Teodor Sigaev
Sorry, but there is something wrong with your patch: % patch -p1 -C < ~/Downloads/libpq-failover-5.patch -- |diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c ... Hunk #18 succeeded at 2804. patch: malformed patch at line 666:

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

2015-12-21 Thread Tom Lane
Albe Laurenz writes: > - I also can corroborate your finding that nested loop joins are often > harmful, particularly when the inner loop is a sequential scan. > One of the first things I do when investigating bad performance of a query > whose plan has a nestend

Re: [HACKERS] SET SESSION AUTHORIZATION superuser limitation.

2015-12-21 Thread Robert Haas
On Sun, Dec 20, 2015 at 1:47 PM, Tom Lane wrote: > The syntax you propose exposes the user's password in cleartext in > the command, where it is likely to get captured in logs for example. > That's not going to do. Of course, right now, the ALTER USER ... PASSWORD command has

Re: [HACKERS] Patch: Implement failover on libpq connect level.

2015-12-21 Thread Victor Wagner
On Mon, 21 Dec 2015 17:18:37 +0300 Teodor Sigaev wrote: > Sorry, but there is something wrong with your patch: > % patch -p1 -C < ~/Downloads/libpq-failover-5.patch Really, somehow broken version of the patch got into message. Here is correct one. diff --git

Re: [HACKERS] ToDo list update for BRIN indexes

2015-12-21 Thread Robert Haas
On Mon, Dec 21, 2015 at 8:06 AM, Simon Riggs wrote: > On 21 December 2015 at 12:54, Robert Haas wrote: >> >> On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes wrote: >> > Is there anything in the below section which has been been

Re: [HACKERS] ToDo list update for BRIN indexes

2015-12-21 Thread Simon Riggs
On 21 December 2015 at 14:38, Robert Haas wrote: > On Mon, Dec 21, 2015 at 8:06 AM, Simon Riggs > wrote: > > On 21 December 2015 at 12:54, Robert Haas wrote: > >> > >> On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes

Re: [HACKERS] A question regarding LWLock in ProcSleep

2015-12-21 Thread Kenan Yao
Hi Amit, Thanks for the reply! Yes, in LockAcquireExtended, after exiting WaitOnLock, there would be a read access to proclock, however, since the lock has either been granted or rejected by deadlock check, I can think of no possible concurrent write access to the proclock, so is it really

Re: [HACKERS] A question regarding LWLock in ProcSleep

2015-12-21 Thread Kenan Yao
+Heikki, Hi Heikki, Could you please help provide a case to elaborate why we need the LWLock here? or could you please tell me to whom should I ask this question? Thanks, Kenan On Tue, Dec 22, 2015 at 11:41 AM, Kenan Yao wrote: > Hi Amit, > > Thanks for the reply! > > Yes,

Re: [HACKERS] Additional LWLOCK_STATS statistics

2015-12-21 Thread Michael Paquier
On Mon, Dec 21, 2015 at 4:50 PM, Jesper Pedersen wrote: > On 12/18/2015 01:16 PM, Robert Haas wrote: >> >> Is this just for informational purposes, or is this something you are >> looking to have committed? I originally thought the former, but now >> I'm wondering if

Re: [HACKERS] Additional role attributes && superuser review

2015-12-21 Thread Michael Paquier
On Tue, Dec 22, 2015 at 1:41 AM, Stephen Frost wrote: > Updated and rebased patch attached which takes the 'pg_switch_xlog' > default role back out, leaving us with: > > pg_monitor - View privileged info > pg_backup - start/stop backups, switch xlog, create restore points >

Re: [HACKERS] custom function for converting human readable sizes to bytes

2015-12-21 Thread Michael Paquier
On Tue, Dec 22, 2015 at 12:11 AM, Robert Haas wrote: > On Sun, Dec 20, 2015 at 4:54 AM, Pavel Stehule > wrote: >> new update: >> >> 1. unit searching is case insensitive >> >> 2. initial support for binary byte prefixes - KiB, MiB, .. (IEC

Re: [HACKERS] Commit fest status for 2015-11

2015-12-21 Thread Tom Lane
Michael Paquier writes: > To committers: there are 4 patches waiting for input. Those are: : SQL function to report log message AFAICT, there is no committer who likes this idea enough to commit it. It's questionable whether we need such a feature at all, and it's

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

2015-12-21 Thread Craig Ringer
On 21 December 2015 at 23:57, Viktor Leis wrote: > > Please have a look at Figure 6 (page 6) in > http://www.vldb.org/pvldb/vol9/p204-leis.pdf Disabling nested loop > joins without index scan (going from (a) to (b)) results in great > improvements across the board. And even more

Re: [HACKERS] Additional role attributes && superuser review

2015-12-21 Thread Michael Paquier
On Tue, Dec 22, 2015 at 2:54 PM, Amit Langote wrote: > On 2015/12/22 14:05, Michael Paquier wrote: >> On Tue, Dec 22, 2015 at 1:41 AM, Stephen Frost wrote: >>> Updated and rebased patch attached which takes the 'pg_switch_xlog' >>> default role

Re: [HACKERS] Patch: Implement failover on libpq connect level.

2015-12-21 Thread Michael Paquier
On Mon, Dec 21, 2015 at 11:50 PM, Victor Wagner wrote: > On Mon, 21 Dec 2015 17:18:37 +0300 > Teodor Sigaev wrote: > >> Sorry, but there is something wrong with your patch: >> % patch -p1 -C < ~/Downloads/libpq-failover-5.patch > > Really, somehow broken

Re: [HACKERS] custom function for converting human readable sizes to bytes

2015-12-21 Thread Pavel Stehule
2015-12-22 6:57 GMT+01:00 Michael Paquier : > On Tue, Dec 22, 2015 at 2:33 PM, Pavel Stehule > wrote: > > > > > > 2015-12-22 6:22 GMT+01:00 Michael Paquier : > >> > >> On Tue, Dec 22, 2015 at 12:11 AM, Robert Haas

Re: [HACKERS] Declarative partitioning

2015-12-21 Thread Amit Langote
On 2015/12/18 3:56, Robert Haas wrote: > On Mon, Dec 14, 2015 at 2:14 AM, Amit Langote > wrote: >> Syntax to create a partitioned table (up to 2 levels of partitioning): >> >> CREATE TABLE foo ( >> ... >> ) >> PARTITION BY R/L ON (key0) >> SUBPARTITION BY R/L ON

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

2015-12-21 Thread Craig Ringer
On 21 December 2015 at 20:53, Viktor Leis wrote: > I think your suggestion amounts to caching the cardinalities of all > two-way joins. One major issue is that for a query like > > select * from r1, r2 where r1.x = r2.y and r1.a = ? and r2.b; > > it depends on the specific

Re: [HACKERS] custom function for converting human readable sizes to bytes

2015-12-21 Thread Pavel Stehule
2015-12-22 6:22 GMT+01:00 Michael Paquier : > On Tue, Dec 22, 2015 at 12:11 AM, Robert Haas > wrote: > > On Sun, Dec 20, 2015 at 4:54 AM, Pavel Stehule > wrote: > >> new update: > >> > >> 1. unit searching is case

Re: [HACKERS] custom function for converting human readable sizes to bytes

2015-12-21 Thread Michael Paquier
On Tue, Dec 22, 2015 at 2:33 PM, Pavel Stehule wrote: > > > 2015-12-22 6:22 GMT+01:00 Michael Paquier : >> >> On Tue, Dec 22, 2015 at 12:11 AM, Robert Haas >> wrote: >> > On Sun, Dec 20, 2015 at 4:54 AM, Pavel Stehule

Re: [HACKERS] SET SESSION AUTHORIZATION superuser limitation.

2015-12-21 Thread Craig Ringer
On 21 December 2015 at 22:57, Tom Lane wrote: > Robert Haas writes: > > On Sun, Dec 20, 2015 at 1:47 PM, Tom Lane wrote: > >> The syntax you propose exposes the user's password in cleartext in > >> the command, where it is likely

[HACKERS] Commit fest status for 2015-11

2015-12-21 Thread Michael Paquier
Hi all, As of today, at the moment I am writing this message, here is the commit fest app status: Needs review: 20. Waiting on Author: 24. Ready for Committer: 4. Committed: 29. Moved to next CF: 11. Rejected: 8. Returned with Feedback: 7. Total: 103. This means in short that 48 out of 103

Re: [HACKERS] Foreign join pushdown vs EvalPlanQual

2015-12-21 Thread Amit Langote
On 2015/12/22 15:24, Michael Paquier wrote: > On Thu, Dec 10, 2015 at 1:32 AM, Robert Haas wrote: >> If we get the feature - join pushdown for postgres_fdw - >> working, then we might get some feedback from users about what they >> like about it or don't, and certainly if

Re: [HACKERS] Coding note: truncating with strlcpy() is not such a hot idea

2015-12-21 Thread Noah Misch
On Mon, Dec 21, 2015 at 03:47:56PM -0500, Tom Lane wrote: > 1. Using strlcpy to copy from sources that are not known null-terminated > is outright unsafe: someday, you'll fall off the end of memory and > SIGSEGV. > > 2. Even with a known null-terminated string, using strlcpy to chop small >

Re: [HACKERS] Some questions about the array.

2015-12-21 Thread Michael Paquier
On Tue, Dec 22, 2015 at 2:28 AM, Tom Lane wrote: > Uriy Zhuravlev writes: >>> I'm dubious that the parsetree representation is well-chosen. >>> Probably a single is_slice flag would have been better. > >> What do you mean? This flag is for what?

Re: [HACKERS] Freeze avoidance of very large table.

2015-12-21 Thread Kyotaro HORIGUCHI
Hello, At Fri, 18 Dec 2015 12:09:43 -0500, Robert Haas wrote in > On Thu, Dec 17, 2015 at 1:17 AM, Michael Paquier > wrote: > > I am not really getting the meaning of this

Re: [HACKERS] Optimizing away second VACUUM heap scan when only BRIN indexes on table

2015-12-21 Thread Simon Riggs
On 21 December 2015 at 02:14, Alvaro Herrera wrote: > Jim Nasby wrote: > > On 11/23/15 5:06 PM, Peter Geoghegan wrote: > > >I realize that the second scan performed by lazy_vacuum_heap() only > > >visits those pages known to contain dead tuples. However, the > >

Re: [HACKERS] Parallel Aggregate

2015-12-21 Thread Haribabu Kommi
On Mon, Dec 21, 2015 at 6:48 PM, David Rowley wrote: > On 21 December 2015 at 17:23, Haribabu Kommi > wrote: >> >> >> Attached latest performance report. Parallel aggregate is having some >> overhead >> in case of low selectivity.This can

Re: [HACKERS] Optimizing away second VACUUM heap scan when only BRIN indexes on table

2015-12-21 Thread Simon Riggs
On 21 December 2015 at 09:35, Peter Geoghegan wrote: > On Mon, Dec 21, 2015 at 1:24 AM, Simon Riggs > wrote: > > Given BRIN's characteristics, such a table design is compelling when the > > table is very large, yet possible only for certain use cases. > >

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

2015-12-21 Thread Albe Laurenz
Viktor Leis wrote: > We have recently performed an experimental evaluation of PostgreSQL's > query optimizer. For example, we measured the contributions of > cardinality estimation and the cost model on the overall query > performance. You can download the resulting paper here: >

Re: [HACKERS] Commit fest status for 2015-11

2015-12-21 Thread Michael Paquier
On Tue, Dec 22, 2015 at 3:35 PM, Tom Lane wrote: > Michael Paquier writes: >> To committers: there are 4 patches waiting for input. > > Those are: > > : SQL function to report log message > > AFAICT, there is no committer who likes this idea enough

Re: [HACKERS] pgbench - allow backslash-continuations in custom scripts

2015-12-21 Thread Michael Paquier
On Wed, Oct 14, 2015 at 5:49 PM, Fabien COELHO wrote: > > Hello, > > Here is a review, sorry for the delay... > >> This is done as the additional fourth patch, not merged into >> previous ones, to show what's changed in the manner of command >> storing. >> [...] >>> >>> -

Re: [HACKERS] Additional role attributes && superuser review

2015-12-21 Thread Amit Langote
On 2015/12/22 14:05, Michael Paquier wrote: > On Tue, Dec 22, 2015 at 1:41 AM, Stephen Frost wrote: >> Updated and rebased patch attached which takes the 'pg_switch_xlog' >> default role back out, leaving us with: >> >> pg_monitor - View privileged info >> pg_backup -

Re: [HACKERS] Foreign join pushdown vs EvalPlanQual

2015-12-21 Thread Michael Paquier
On Thu, Dec 10, 2015 at 1:32 AM, Robert Haas wrote: > On Wed, Dec 9, 2015 at 3:22 AM, Etsuro Fujita > wrote: >> Sorry, my explanation might be not enough, but I'm not saying to hide the >> subplan. I think it would be better to show the

Re: [HACKERS] WIP: Fix parallel workers connection bug in pg_dump (Bug #13727)

2015-12-21 Thread Michael Paquier
On Fri, Nov 6, 2015 at 12:23 AM, Marko Tiikkaja wrote: > On 11/5/15 4:11 PM, Zeus Kronion wrote: >> >> On Nov 1, 2015 5:04 PM, "Marko Tiikkaja" wrote: >>> >>> However, I don't quite like the way the password cache is kept up to date >> >> in the old *or* the new code.

Re: [HACKERS] Foreign join pushdown vs EvalPlanQual

2015-12-21 Thread Michael Paquier
On Tue, Dec 22, 2015 at 3:52 PM, Amit Langote wrote: > On 2015/12/22 15:24, Michael Paquier wrote: >> As this debate continues, I think that moving this patch to the next >> CF would make the most sense then.. So done this way. > > Perhaps, this ended (?) with the

Re: [HACKERS] pglogical_output - a general purpose logical decoding output plugin

2015-12-21 Thread Michael Paquier
On Mon, Dec 7, 2015 at 12:10 PM, Craig Ringer wrote: > Removed, change pushed. > > Also pushed a change to expose the decoded row data to row filter hooks. > > I won't cut a v4 for this, as I'm working on merging the SGML-ified docs and > will do a v4 with that and the

Re: [HACKERS] Getting sorted data from foreign server for merge join

2015-12-21 Thread Ashutosh Bapat
> > I went over this patch in some detail today and did a lot of cosmetic > cleanup. The results are attached. I'm fairly happy with this > version, but let me know what you think. Of course, feedback from > others is more than welcome also. > > Attached patch with some cosmetic changes (listed

Re: [HACKERS] Combining Aggregates

2015-12-21 Thread Robert Haas
On Mon, Dec 21, 2015 at 4:02 AM, David Rowley wrote: > Ok, so it seems that my mindset was not in parallel process space when I > was thinking about this. I think having the pointer in the Tuple is > probably going to be fine for this multiple stage aggregation

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

2015-12-21 Thread Viktor Leis
Am 21.12.2015 um 09:22 schrieb Albe Laurenz: > Viktor Leis wrote: >> We have recently performed an experimental evaluation of PostgreSQL's >> query optimizer. For example, we measured the contributions of >> cardinality estimation and the cost model on the overall query >> performance. You can

Re: [HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-12-21 Thread Fabien COELHO
Hello Heikki, The reason I didn't commit this back then was lack of performance testing. I'm fairly confident that this would be a significant improvement for some workloads, and shouldn't hurt much even in the worst case. But I did only a little testing on my laptop. I think Simon was in

Re: [HACKERS] A typo in syncrep.c

2015-12-21 Thread Robert Haas
On Mon, Dec 21, 2015 at 12:23 AM, Kyotaro HORIGUCHI wrote: > At Fri, 18 Dec 2015 12:44:34 -0500, Robert Haas wrote > in >> On Wed, Dec 16, 2015 at 3:33 AM, Kyotaro

Re: [HACKERS] Patch to improve a few appendStringInfo* calls

2015-12-21 Thread Robert Haas
On Thu, Jul 2, 2015 at 6:08 AM, David Rowley wrote: >> I left out the changes like >> >>> - appendStringInfoString(, buf.data); >>> + appendBinaryStringInfo(, buf.data, buf.len); >> >> >> because they're not an improvement in readablity,

Re: [HACKERS] Optimization for updating foreign tables in Postgres FDW

2015-12-21 Thread Etsuro Fujita
On 2015/11/26 18:00, Etsuro Fujita wrote: On 2015/11/25 20:36, Thom Brown wrote: On 13 May 2015 at 04:10, Etsuro Fujita wrote: On 2015/05/13 0:55, Stephen Frost wrote: While the EXPLAIN output changed, the structure hasn't really changed from what was discussed

Re: [HACKERS] ToDo list update for BRIN indexes

2015-12-21 Thread Simon Riggs
On 21 December 2015 at 12:54, Robert Haas wrote: > On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes wrote: > > Is there anything in the below section which has been been implemented or > > rendered irrelevant by BRIN indexes? > > > >

Re: [HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-12-21 Thread Tomas Vondra
Hi, On 12/21/2015 12:03 PM, Heikki Linnakangas wrote: On 17/12/15 19:07, Robert Haas wrote: On Mon, Dec 14, 2015 at 6:08 PM, Tomas Vondra wrote: So we know that we should expect about (prev_wal_bytes - wal_bytes) + (prev_wal_fpw_bytes - wal_fpw_bytes) (

Re: [HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

2015-12-21 Thread Heikki Linnakangas
On 21/12/15 13:53, Tomas Vondra wrote: On 12/21/2015 12:03 PM, Heikki Linnakangas wrote: On 17/12/15 19:07, Robert Haas wrote: If it works well empirically, does it really matter that it's arbitrary? I mean, the entire planner is full of fairly arbitrary assumptions about which things to

Re: [HACKERS] ToDo list update for BRIN indexes

2015-12-21 Thread Robert Haas
On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes wrote: > Is there anything in the below section which has been been implemented or > rendered irrelevant by BRIN indexes? > > https://wiki.postgresql.org/wiki/Todo#Indexes > > "Consider smaller indexes that record a range of values

Re: [HACKERS] Odd behaviour of SELECT ... ORDER BY ... FOR UPDATE

2015-12-21 Thread Robert Haas
On Thu, Jul 2, 2015 at 3:59 AM, Etsuro Fujita wrote: > Hi Marko, > > On 2015/07/02 16:27, Marko Tiikkaja wrote: >> On 7/2/15 9:15 AM, Etsuro Fujita wrote: >>> While working on the foreign-join-pushdown issue, I noticed that in READ >>> COMMITTED isolation level it's

Re: [HACKERS] Additional role attributes && superuser review

2015-12-21 Thread Stephen Frost
Michael, all, * Michael Paquier (michael.paqu...@gmail.com) wrote: > OK, let's do so then by having this one fall under pg_backup. Let's > not be my grunting concerns be an obstacle for this patch, and we > could still change it afterwards in this release beta cycle anyway > based on user

Re: [HACKERS] Some questions about the array.

2015-12-21 Thread Uriy Zhuravlev
In the continuation of thread: http://www.postgresql.org/message-id/19144.1450457...@sss.pgh.pa.us I'm dubious that the parsetree representation is well-chosen. Probably a single is_slice flag would have been better. What do you mean? This flag is for what? You are about the A_Indices

Re: [HACKERS] Parallel Aggregate

2015-12-21 Thread Paul Ramsey
On December 21, 2015 at 2:33:56 AM, Haribabu Kommi (kommi.harib...@gmail.com) wrote: Yes the query is producing more groups according to the selectivity.  For example - scan selectivity - 40, the number of groups - 400  Following is the query:  SELECT tenpoCord,  SUM(yokinZandaka) AS

Re: [HACKERS] extend pgbench expressions with functions

2015-12-21 Thread Fabien COELHO
Hello Michael, I'm not sure whether we are talking about the same thing: - there a "double" type managed within expressions, but not variables - there is a double() function, which takes an int and casts to double I understood that you were suggesting to remove all "double" expressions, but

Re: [HACKERS] Freeze avoidance of very large table.

2015-12-21 Thread Robert Haas
On Mon, Dec 21, 2015 at 3:27 AM, Kyotaro HORIGUCHI wrote: > Hello, > > At Fri, 18 Dec 2015 12:09:43 -0500, Robert Haas wrote > in >> On Thu, Dec 17, 2015 at 1:17 AM,

[HACKERS] Avoiding pin scan during btree vacuum

2015-12-21 Thread Simon Riggs
During VACUUM of btrees, we need to pin all pages, even those where tuples are not removed, which I am calling here the "pin scan". This is especially a problem during redo, where removing one tuple from a 100GB btree can take a minute or more. That causes replication lags. Bad Thing. Previously,

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

2015-12-21 Thread Viktor Leis
Am 21.12.2015 um 15:42 schrieb Tom Lane: > Viktor Leis writes: >> I think it would be a good start to distinguish between nested loop >> joins with and without a index. > > We do. > >> In my opinion, the latter should simply NEVER be chosen. > > So, if you're given a query with

Re: [HACKERS] SET SESSION AUTHORIZATION superuser limitation.

2015-12-21 Thread Dmitry Igrishin
2015-12-21 17:57 GMT+03:00 Tom Lane : > Robert Haas writes: > > On Sun, Dec 20, 2015 at 1:47 PM, Tom Lane wrote: > >> The syntax you propose exposes the user's password in cleartext in > >> the command, where it is likely to get

Re: [HACKERS] SET SESSION AUTHORIZATION superuser limitation.

2015-12-21 Thread Tom Lane
Robert Haas writes: > On Sun, Dec 20, 2015 at 1:47 PM, Tom Lane wrote: >> The syntax you propose exposes the user's password in cleartext in >> the command, where it is likely to get captured in logs for example. >> That's not going to do. > Of course,

Re: [HACKERS] custom function for converting human readable sizes to bytes

2015-12-21 Thread Robert Haas
On Sun, Dec 20, 2015 at 4:54 AM, Pavel Stehule wrote: > new update: > > 1. unit searching is case insensitive > > 2. initial support for binary byte prefixes - KiB, MiB, .. (IEC standard), > change behave for SI units > > Second point is much more complex then it is

Re: [HACKERS] plpgsql - DECLARE - cannot to use %TYPE or %ROWTYPE for composite types

2015-12-21 Thread Artur Zakirov
Hi. I have tried to do some review of this patch. Below are my comments. Introduction: This patch fixes and adds the following functionality: - %TYPE - now can be used for composite types. - %ARRAYTYPE - new functionality, provides the array type from a variable or table column. - %ELEMENTTYPE

[HACKERS] tracking owner of extension-managed objects

2015-12-21 Thread Chapman Flack
I'm looking for best-practice advice. PL/Java is an extension that manages some objects (jar files, which users can tell PL/Java to load, drop, or replace). The objects have owners (have had since PL/Java 1.1.0 anyway). When the owner tracking was added for 1.1.0 it recorded the owner oid. In

Re: [HACKERS] Some questions about the array.

2015-12-21 Thread Tom Lane
Uriy Zhuravlev writes: >> I'm dubious that the parsetree representation is well-chosen. >> Probably a single is_slice flag would have been better. > What do you mean? This flag is for what? You are about the A_Indices > node(lidx_default/uidx_default)? Yes. Those

Re: [HACKERS] tracking owner of extension-managed objects

2015-12-21 Thread Tom Lane
Chapman Flack writes: > PL/Java is an extension that manages some objects (jar files, which > users can tell PL/Java to load, drop, or replace). The objects have > owners (have had since PL/Java 1.1.0 anyway). > When the owner tracking was added for 1.1.0 it recorded the