Re: [HACKERS] Re: [COMMITTERS] pgsql: Allocate all page images at once in generic wal interface

2016-05-18 Thread Mikael Kjellström
Could somebody explain me what's going on? That seems entirely unrelated to what you changed, and curculio's next run failed even more bizarrely: commands/explain.o: could not read symbols: File format not recognized collect2: ld returned 1 exit status As that is my animal I will take a

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Josh berkus
On 05/18/2016 03:51 PM, Peter Geoghegan wrote: > On Wed, May 18, 2016 at 8:52 AM, Jeff Janes wrote: >> How about going with something that says more about why we are doing >> it, rather than trying to describe in one or two words what it is >> doing? >> >> VACUUM (FORENSIC)

Re: [HACKERS] Parallel query and temp_file_limit

2016-05-18 Thread Peter Geoghegan
On Wed, May 18, 2016 at 3:40 AM, Robert Haas wrote: >> I'll write a patch to fix the issue, if there is a consensus on a solution. > > I think for 9.6 we just have to document this issue. In the next > release, we could (and might well want to) try to do something more >

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Peter Geoghegan
On Wed, May 18, 2016 at 8:52 AM, Jeff Janes wrote: > How about going with something that says more about why we are doing > it, rather than trying to describe in one or two words what it is > doing? > > VACUUM (FORENSIC) > > VACUUM (DEBUG) > > VACUUM (LINT) +1 -- Peter

[HACKERS] PgLogical 1.1 feedback

2016-05-18 Thread Joshua D. Drake
Hello, Continuing my testing with pglogical: It is very easy to end up in a broken state with pglogical. This isn't all that big of a deal because it is relatively easy to end up in a broken state with streaming replication as well. However, with streaming the replication the various

[HACKERS] Fix checkpoint skip logic on idle systems by tracking LSN progress

2016-05-18 Thread Michael Paquier
Hi all, A couple of months back is has been reported to pgsql-bugs that WAL segments were always switched with a low value of archive_timeout even if a system is completely idle: http://www.postgresql.org/message-id/20151016203031.3019.72...@wrigleys.postgresql.org In short, a closer look at the

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Andres Freund
On 2016-05-18 18:25:39 -0400, Tom Lane wrote: > Josh berkus writes: > > Maybe this is the wrong perspective. I mean, is there a reason we even > > need this option, other than a lack of any other way to do a full table > > scan to check for corruption, etc.? If we're only

Re: [HACKERS] PgLogical 1.1 feedback

2016-05-18 Thread Joshua D. Drake
On 05/18/2016 01:17 PM, Joshua D. Drake wrote: And then there are things like this: postgres=# \c logical You are now connected to database "logical" as user "postgres". logical=# select * from pglogical.node; -[ RECORD 1 ]-- node_id | 3276292710 node_name | replica2 -[ RECORD 2

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Tom Lane
Josh berkus writes: > Maybe this is the wrong perspective. I mean, is there a reason we even > need this option, other than a lack of any other way to do a full table > scan to check for corruption, etc.? If we're only doing this for > integrity checking, then maybe it's

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Andres Freund
On 2016-05-18 18:42:16 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2016-05-18 18:25:39 -0400, Tom Lane wrote: > >> Yes, I've been wondering that too. VACUUM is not meant as a corruption > >> checker, and should not be made into one, so what is the point of this > >>

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Tom Lane
Andres Freund writes: > On 2016-05-18 18:25:39 -0400, Tom Lane wrote: >> Yes, I've been wondering that too. VACUUM is not meant as a corruption >> checker, and should not be made into one, so what is the point of this >> flag exactly? > Well, so far a VACUUM FREEZE (or just

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Peter Geoghegan
On Wed, May 18, 2016 at 3:57 PM, Alvaro Herrera wrote: > Since we were considering a new VACUUM option, surely this is serious > enough to warrant more than just contrib. I would like to see us consider the long-term best place for amcheck's functionality at the same

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Alvaro Herrera
Andres Freund wrote: > > (AFAIK, "select count(*) from table" would offer a similar amount of > > sanity checking as a full-table VACUUM scan does, so it's not like > > we've removed functionality with no near-term replacement.) > > I don't think that'd do anything comparable to >

Re: [HACKERS] A couple of cosmetic changes around shared memory code

2016-05-18 Thread Piotr Stefaniak
On 2016-05-17 19:05, Tom Lane wrote: Michael Paquier writes: On Tue, May 17, 2016 at 4:40 AM, Piotr Stefaniak wrote: -toc_bytes = offsetof(shm_toc, toc_entry) +nentry * sizeof(shm_toc_entry) +toc_bytes = offsetof(shm_toc,

Re: [HACKERS] foreign table batch inserts

2016-05-18 Thread Craig Ringer
On 19 May 2016 at 01:39, Michael Paquier wrote: > On Wed, May 18, 2016 at 12:27 PM, Craig Ringer > wrote: > > On 18 May 2016 at 06:08, Michael Paquier > wrote: > >> > Wouldn’t it make sense to do the insert batch wise

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread David Steele
On 5/18/16 6:37 AM, Robert Haas wrote: On Tue, May 17, 2016 at 5:47 PM, Gavin Flower wrote: On 18/05/16 09:34, Vik Fearing wrote: On 17/05/16 21:32, Alvaro Herrera wrote: Is SCAN_ALL really the best we can do here? The business of having an underscore in an

[HACKERS] memory layouts for binary search in nbtree

2016-05-18 Thread Andres Freund
Hi, currently we IIRC use linearly sorted datums for the search in individual btree nodes. Not surprisingly that's often one of the dominant entries in profiles. We could probably improve upon that by using an order more optimized for efficient binary search. See e.g.

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Robert Haas
On Wed, May 18, 2016 at 8:41 AM, David Steele wrote: >> I think we should give this a name that hints more strongly at this >> being an exceptional thing, like vacuum (even_frozen_pages). > > How about just FROZEN? Perhaps it's too confusing to have that and FREEZE, > but I

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Robert Haas
On Wed, May 18, 2016 at 9:42 AM, Joshua D. Drake wrote: >> It's not a bad thought, but I do think it might be a bit confusing. >> My main priority for this new option is that people aren't tempted to >> use it very often, and I think a name like "even_frozen_pages" is more

Re: [HACKERS] Parallel query and temp_file_limit

2016-05-18 Thread David Rowley
On 18 May 2016 at 22:40, Robert Haas wrote: > On Tue, May 17, 2016 at 6:40 PM, Peter Geoghegan wrote: > > On Tue, May 17, 2016 at 3:33 PM, Peter Geoghegan wrote: > >> Fundamentally, since temporary_files_size enforcement simply > >>

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Joshua D. Drake
On 05/18/2016 05:51 AM, Robert Haas wrote: On Wed, May 18, 2016 at 8:41 AM, David Steele wrote: I think we should give this a name that hints more strongly at this being an exceptional thing, like vacuum (even_frozen_pages). How about just FROZEN? Perhaps it's too

Re: [HACKERS] Parallel query and temp_file_limit

2016-05-18 Thread Robert Haas
On Tue, May 17, 2016 at 6:40 PM, Peter Geoghegan wrote: > On Tue, May 17, 2016 at 3:33 PM, Peter Geoghegan wrote: >> Fundamentally, since temporary_files_size enforcement simply >> piggy-backs on low-level fd.c file management, without any >> consideration of

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Robert Haas
On Tue, May 17, 2016 at 5:47 PM, Gavin Flower wrote: > On 18/05/16 09:34, Vik Fearing wrote: >> On 17/05/16 21:32, Alvaro Herrera wrote: >>> >>> Is SCAN_ALL really the best we can do here? The business of having an >>> underscore in an option name has no precedent

Re: [HACKERS] memory layouts for binary search in nbtree

2016-05-18 Thread Simon Riggs
On 18 May 2016 at 14:25, Andres Freund wrote: > Hi, > > currently we IIRC use linearly sorted datums for the search in > individual btree nodes. Not surprisingly that's often one of the > dominant entries in profiles. We could probably improve upon that by > using an order

[HACKERS] Improve BEGIN tab completion

2016-05-18 Thread Andreas Karlsson
Hi, I noticed that the tab completion was not aware of that TRANSACTION/WORK is optional in BEGIN, and that we do not complete [NOT] DEFERRABLE. While fixing it I also improved the completion support for SET SESSION slightly. Andreas diff --git a/src/bin/psql/tab-complete.c

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Victor Yegorov
2016-05-18 16:45 GMT+03:00 Robert Haas : > No, that's what the existing FREEZE option does. This new option is > about unnecessarily vacuuming pages that don't need it. The > expectation is that vacuuming all-frozen pages will be a no-op. > VACUUM (INCLUDING ALL) ? --

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Joe Conway
On 05/18/2016 09:55 AM, Victor Yegorov wrote: > 2016-05-18 16:45 GMT+03:00 Robert Haas >: > > No, that's what the existing FREEZE option does. This new option is > about unnecessarily vacuuming pages that don't need it. The >

[HACKERS] explain analyze does not report actual rows correctly?

2016-05-18 Thread chang chao
Hi all The test data and sql is in the following mail. http://www.postgresql.org/message-id/sg2pr06mb114954351fa5dae7566854a984...@sg2pr06mb1149.apcprd06.prod.outlook.com After I disabled hash join and nested-loop join(set enable_hashjoin = off; set enable_nestloop = off;) and execute the

Re: [HACKERS] The rewritting of join conditions caused a very slow query plan.

2016-05-18 Thread chang chao
Hi,all This is my ddl and test data. CREATE TABLE level1_table ( level1_no serial NOT NULL , level1_node_name varchar(255), PRIMARY KEY (level1_no) ) WITHOUT OIDS; CREATE TABLE level2_table ( level2_no serial NOT NULL , parent_no int NOT NULL,

Re: [HACKERS] Declarative partitioning

2016-05-18 Thread Ildar Musin
Hi Amit and all, On 18.05.2016 04:26, Amit Langote wrote: On 2016/05/18 2:22, Tom Lane wrote: The two ways that we've dealt with this type of hazard are to copy data out of the relcache before using it; or to give the relcache the responsibility of not moving a particular portion of data if it

Re: [HACKERS] Reviewing freeze map code

2016-05-18 Thread Jeff Janes
On Wed, May 18, 2016 at 7:09 AM, Joe Conway wrote: > On 05/18/2016 09:55 AM, Victor Yegorov wrote: >> 2016-05-18 16:45 GMT+03:00 Robert Haas > >: >> >> No, that's what the existing FREEZE option does. This new option

Re: [HACKERS] foreign table batch inserts

2016-05-18 Thread Craig Ringer
On 18 May 2016 at 06:08, Michael Paquier wrote: > > Wouldn’t it make sense to do the insert batch wise e.g. 100 rows ? > > Using a single query string with multiple values, perhaps, but after > that comes into consideration query string limit particularly for > large

Re: [HACKERS] foreign table batch inserts

2016-05-18 Thread Michael Paquier
On Wed, May 18, 2016 at 12:27 PM, Craig Ringer wrote: > On 18 May 2016 at 06:08, Michael Paquier wrote: >> > Wouldn’t it make sense to do the insert batch wise e.g. 100 rows ? >> >> Using a single query string with multiple values, perhaps, but

Re: [HACKERS] explain analyze does not report actual rows correctly?

2016-05-18 Thread Tom Lane
chang chao writes: > The actual rows(rows=9950) part in the following line contained in the above > query plan seems strange. > " -> Sort (cost=10.64..11.14 rows=200 width=520) (actual time=0.045..0.561 > rows=9950 loops=1)" > Shouldn't it be 200? No, that's probably

Re: [HACKERS] Postgres_fdw join pushdown - getting server crash in left outer join of three table

2016-05-18 Thread Ashutosh Bapat
On Fri, May 13, 2016 at 10:14 AM, Robert Haas wrote: > On Wed, Mar 23, 2016 at 12:53 PM, Robert Haas > wrote: > > On Wed, Mar 23, 2016 at 5:24 AM, Rajkumar Raghuwanshi > > wrote: > >> Thanks Ashutosh for the

Re: [HACKERS] Declarative partitioning

2016-05-18 Thread Tom Lane
Amit Langote writes: > On 2016/05/18 2:22, Tom Lane wrote: >> The two ways that we've dealt with this type of hazard are to copy data >> out of the relcache before using it; or to give the relcache the >> responsibility of not moving a particular portion of data if