Re: [HACKERS] Aggregate Push Down - Performing aggregation on foreign server

On Fri, Oct 21, 2016 at 10:47 AM, Tom Lane wrote: > Robert Haas writes: >> I didn't find anything structurally wrong with this patch, so I've >> committed it with many cosmetic changes. Judging by what happened >> with join pushdown, there are probably

[HACKERS] Specifying the log file name of pgbench -l option

Hi all, The log file generated by pgbench -l option is fixed file name 'pgbench_log..'. And it's a little complicated for the script that runs pgbench repeatedly to identify the log file name. Attached patch make it possible to specify the log file name. I think it's useful for the use who want

Re: [HACKERS] Default setting for autovacuum_freeze_max_age

Bruce Momjian wrote: > On Fri, Oct 21, 2016 at 10:44:41AM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > Why is autovacuum_freeze_max_age's default set to 200 million, rather > > > than something like 2 billion? It seems 2 billion is half way to > > > wrap-around and

Re: [HACKERS] Improve output of BitmapAnd EXPLAIN ANALYZE

On 10/21/16 8:21 AM, Stephen Frost wrote: Counting each page as the relation's average number of tuples per page seems entirely reasonable to me, for what that is trying to report. My concern is that still leaves a lot of room for confusion when interpreting EXPLAIN ANALYZE. Every other node

Re: [HACKERS] Aggregate Push Down - Performing aggregation on foreign server

Robert Haas writes: > On Fri, Oct 21, 2016 at 10:47 AM, Tom Lane wrote: >> dromedary seems to have found one, or at least an unstable test result. > OK, looking at that now. Thanks. Looking at further failures, it looks like 32-bit machines in

Re: [HACKERS] Default setting for autovacuum_freeze_max_age

On Fri, Oct 21, 2016 at 10:44:41AM -0400, Tom Lane wrote: > Bruce Momjian writes: > > Why is autovacuum_freeze_max_age's default set to 200 million, rather > > than something like 2 billion? It seems 2 billion is half way to > > wrap-around and would be a better default. Right

Re: [HACKERS] emergency outage requiring database restart

Merlin Moncure wrote: > OK, I have some good (very- in the specific case of yours truly) news > to report. Doing a filesystem level copy to a test server I was able > to relfilenode swap one of the critical tables over the place of the > refilenode of the stored backup. Not being able know the

Re: [HACKERS] emergency outage requiring database restart

On Fri, Oct 21, 2016 at 1:37 PM, Merlin Moncure wrote: > On Fri, Oct 21, 2016 at 8:03 AM, Kevin Grittner wrote: >> On Tue, Oct 18, 2016 at 8:45 AM, Merlin Moncure wrote: >> >>> Most or all the damage seemed to be to the system catalogs

Re: [HACKERS] FSM corruption leading to errors

On 10/20/16 10:15 PM, Michael Paquier wrote: 2) If anything is found, stop the server and delete the files manually. 3) Re-start the server. OK, that's troublesome and costly for large relations, but we know that's the safest way to go for any versions, and there is no need to complicate the

Re: [HACKERS] Renaming of pg_xlog and pg_clog

David Steele wrote: > On 10/21/16 3:12 AM, David G. Johnston wrote: > > > I have no problem continuing keeping with historical precedent ​and > > allowing mnemonic abbreviations in our directory and file names at this > > point. > > I'm still in favor of pg_xact. A search of the 9.6 docs brings

Re: [HACKERS] Indirect indexes

On 2016-10-18 20:04:32, Claudio Freire wrote: > You don't need that limitation (and vacuum will be simpler) if you add the PK as another key, akin to: > > CREATE INDIRECT INDEX idx ON tab (a, b, c); > > turns into > > CREATE INDEX idx ON tab (a, b, c, pk); I know I am late to this point but I

Re: [HACKERS] PSA: Systemd will kill PostgreSQL

On Fri, Oct 21, 2016 at 8:29 AM, Peter Eisentraut wrote: > On 8/16/16 1:05 PM, Tom Lane wrote: >> Oh, interesting. It had occurred to me that we might be able to dodge >> this issue if we started to recommend using unnamed POSIX semaphores >> instead of SysV.

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

On Sun, Oct 2, 2016 at 8:52 AM, Michael Paquier wrote: >> So, if I understand correctly, then we can mark the version posted by >> you upthread [1] which includes a test along with Kyotaro's fix can be >> marked as Ready for committer. If so, then please change the

Re: [HACKERS] Parallel Index Scans

On Fri, Oct 21, 2016 at 9:27 AM, Amit Kapila wrote: >> I think the parallel_workers reloption should override the degree of >> parallelism for any sort of parallel scan on that table. Had I >> intended it to apply only to sequential scans, I would have named it >>

Re: [HACKERS] Default setting for autovacuum_freeze_max_age

On Fri, Oct 21, 2016 at 1:17 PM, Josh Berkus wrote: > Particularly, with 9.6's freeze map, point (2) is even stronger reason > to *lower* autovacuum_max_freeze_age. Since there's little duplicate > work in a freeze scan, a lot of users will find that frequent freezing >

Re: [HACKERS] Renaming of pg_xlog and pg_clog

On Fri, Oct 21, 2016 at 9:47 AM, Stephen Frost wrote: > * Robert Haas (robertmh...@gmail.com) wrote: >> On Thu, Oct 20, 2016 at 12:12 PM, Stephen Frost wrote: >> > That said, I'd also like to see a --force or similar option or mechanism >> > put in place

Re: [HACKERS] Renaming of pg_xlog and pg_clog

* Robert Haas (robertmh...@gmail.com) wrote: > I don't think that the problem is that people are accidentally typing > "pg_resetxlog $PGDATA" and pressing return. They're typing that on > purpose, and if you change the sequence of characters required to get > that effect, they'll just type the

Re: [HACKERS] emergency outage requiring database restart

On Fri, Oct 21, 2016 at 8:03 AM, Kevin Grittner wrote: > On Tue, Oct 18, 2016 at 8:45 AM, Merlin Moncure wrote: > >> Most or all the damage seemed to be to the system catalogs with >> at least two critical tables dropped or inaccessible in some >> fashion.

Re: [HACKERS] Improve output of BitmapAnd EXPLAIN ANALYZE

* Jim Nasby (jim.na...@bluetreble.com) wrote: > On 10/21/16 8:21 AM, Stephen Frost wrote: > >Counting each page as the relation's average number of tuples per page > >seems entirely reasonable to me, for what that is trying to report. > > My concern is that still leaves a lot of room for

Re: [HACKERS] Default setting for autovacuum_freeze_max_age

On 10/21/2016 07:44 AM, Tom Lane wrote: > Bruce Momjian writes: >> Why is autovacuum_freeze_max_age's default set to 200 million, rather >> than something like 2 billion? It seems 2 billion is half way to >> wrap-around and would be a better default. Right now, the default

Re: [HACKERS] Renaming of pg_xlog and pg_clog

On Sat, Oct 22, 2016 at 4:29 AM, Alvaro Herrera wrote: > David Steele wrote: >> On 10/21/16 3:12 AM, David G. Johnston wrote: >> >> > I have no problem continuing keeping with historical precedent and >> > allowing mnemonic abbreviations in our directory and file names

Re: [HACKERS] emergency outage requiring database restart

Jim Nasby wrote: > On 10/21/16 2:02 PM, Alvaro Herrera wrote: > > Merlin Moncure wrote: > > > > > OK, I have some good (very- in the specific case of yours truly) news > > > to report. Doing a filesystem level copy to a test server I was able > > > to relfilenode swap one of the critical tables

Re: [HACKERS] Indirect indexes

Alvaro Herrera writes: > Robert Haas wrote: >> So, I think that this is a really promising direction, but also that >> you should try very hard to try to get out from under this 6-byte PK >> limitation. That seems really ugly, and in practice it probably means >> your

[HACKERS] Fast Default WIP patch for discussion

As promised and requested find attached a work in progress patch for fast defaults.This is my first patch, I hope I used the right format…..The premise of the feature is to avoid a table rewrite when adding a column with a default.This is done by remembering the default value in pg_attribute

Re: [HACKERS] FSM corruption leading to errors

On Sat, Oct 22, 2016 at 5:17 AM, Jim Nasby wrote: > On 10/20/16 10:15 PM, Michael Paquier wrote: >> >> 2) If anything is found, stop the server and delete the files manually. >> 3) Re-start the server. >> OK, that's troublesome and costly for large relations, but we know

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

On Sat, Oct 22, 2016 at 12:15 AM, Jim Nasby wrote: > On 10/21/16 8:47 AM, Tom Lane wrote: >>> >>> It seems to me that you'd even want to make the drop of orphaned >>> > tables mandatory once it is detected even it is not a wraparound >>> > autovacuum. >> >> If we are

Re: [HACKERS] Indirect indexes

Robert Haas wrote: > So, I think that this is a really promising direction, but also that > you should try very hard to try to get out from under this 6-byte PK > limitation. That seems really ugly, and in practice it probably means > your PK is probably going to be limited to int4, which is

Re: [HACKERS] Remove autovacuum GUC?

On 10/20/16 11:50 PM, Craig Ringer wrote: Personally what I think is needed here is to make monitoring and bloat visibility not completely suck. So we can warn users if tables haven't been vac'd in ages and have recent churn. And so they can easily SELECT a view to get bloat estimates with an

Re: [HACKERS] Indirect indexes

On 10/21/16 2:48 PM, Sven R. Kunze wrote: You don't need that limitation (and vacuum will be simpler) if you add the PK as another key, akin to: CREATE INDIRECT INDEX idx ON tab (a, b, c); turns into CREATE INDEX idx ON tab (a, b, c, pk); I know I am late to this point but I wanted to

Re: [HACKERS] Indirect indexes

Just throwing an anecdote out there, but my company uses UUID for primary keys on every table in the DB. While int4 is for sure more popular, it would be nice if there weren't even more reasons to "force" people in that direction. I know I started regretting the decision to go with UUID primary

Re: [HACKERS] Indirect indexes

On 10/19/16 7:52 AM, Robert Haas wrote: So, I think that this is a really promising direction, but also that you should try very hard to try to get out from under this 6-byte PK limitation. That seems really ugly, and in practice it probably means your PK is probably going to be limited to

Re: [HACKERS] Proposal for changes to recovery.conf API

On 09/28/2016 10:13 AM, Robert Haas wrote: > On Tue, Sep 6, 2016 at 10:11 AM, David Steele wrote: >> On 9/6/16 8:07 AM, Robert Haas wrote: >>> On Wed, Aug 31, 2016 at 9:45 PM, Simon Riggs >>> wrote: Related cleanup * Promotion signal file is

Re: [HACKERS] emergency outage requiring database restart

On 10/21/16 2:02 PM, Alvaro Herrera wrote: Merlin Moncure wrote: OK, I have some good (very- in the specific case of yours truly) news to report. Doing a filesystem level copy to a test server I was able to relfilenode swap one of the critical tables over the place of the refilenode of the

Re: [HACKERS] Improve output of BitmapAnd EXPLAIN ANALYZE

On 10/21/16 12:30 PM, Stephen Frost wrote: I don't see why we would want to stick 'N/A' in for the header, even if we are reporting the details, when we can provide a pretty reasonable number. Because then it's absolutely clear that we don't have a valid rowcount, only a guess (and a guess

Re: [HACKERS] Indirect indexes

On 10/21/16 4:05 PM, Adam Brusselback wrote: My main point is that maybe the reason why most users use int4 pkeys (besides conventional wisdom) is because it gets the most support from features like this, and it may not be quite as skewed if that same support were given to other types. I think

Re: [HACKERS] emergency outage requiring database restart

Alvaro Herrera writes: > Jim Nasby wrote: >> It occurs to me that it might be worth embedding the relation name in the >> free space of the first block. Most people would never notice the missing 64 >> bytes, but it would be incredibly helpful in cases like this... >

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

Michael Paquier writes: > On Sat, Oct 22, 2016 at 12:15 AM, Jim Nasby wrote: >> On 10/21/16 8:47 AM, Tom Lane wrote: >>> If we are willing to do that then we don't really have to solve the >>> problem on the backend side. One could expect

Re: [HACKERS] make check-world failing for me

On 22 October 2016 at 01:52, Tom Lane wrote: > Thom Brown writes: > > I'm using the latest version of Linux Mint Debian Edition, having > recently > > upgraded from an older version, and now I can't get make check-world to > > finish successfully. > > > The

[HACKERS] tuplesort_gettuple_common() and *should_free argument

I noticed that the routine tuplesort_gettuple_common() fails to set *should_free in all paths in master branch (no bug in 9.6). Consider the TSS_SORTEDONTAPE case, where we can return false without also setting "*should_free = false" to instruct caller not to pfree() tuple memory that tuplesort

[HACKERS] Draft release notes for next week's releases

I have committed draft release notes at https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=eacaf6e29fd2a3047aff9738a35a8e9b05e55375 They should be up on the website at https://www.postgresql.org/docs/devel/static/release-9-6-1.html as soon as guaibasaurus does its next run (about

Re: [HACKERS] Draft release notes for next week's releases

On Fri, Oct 21, 2016 at 4:51 PM, Tom Lane wrote: > Please review ... Is somebody going to look at the bugfix for the issue where ON CONFLICT DO NOTHING is used at higher isolation levels [1]? I think that it's still possible to get it in. [1]

Re: [HACKERS] make check-world failing for me

Thom Brown writes: > I'm using the latest version of Linux Mint Debian Edition, having recently > upgraded from an older version, and now I can't get make check-world to > finish successfully. > The initial theory was that the version of Bison I'm using (3.0.2) is to > blame, but

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

On 10/21/16 8:47 AM, Tom Lane wrote: It seems to me that you'd even want to make the drop of orphaned > tables mandatory once it is detected even it is not a wraparound > autovacuum. If we are willing to do that then we don't really have to solve the problem on the backend side. One could

Re: [HACKERS] Aggregate Push Down - Performing aggregation on foreign server

On Fri, Oct 21, 2016 at 11:20 AM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Oct 21, 2016 at 10:47 AM, Tom Lane wrote: >>> dromedary seems to have found one, or at least an unstable test result. > >> OK, looking at that now.

Re: [HACKERS] Parallel Index Scans

On Fri, Oct 21, 2016 at 10:55 PM, Robert Haas wrote: > On Fri, Oct 21, 2016 at 9:27 AM, Amit Kapila wrote: >>> I think the parallel_workers reloption should override the degree of >>> parallelism for any sort of parallel scan on that table. Had I

Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

On Fri, Oct 21, 2016 at 6:31 AM, Robert Haas wrote: > On Thu, Oct 20, 2016 at 4:04 PM, Tomas Vondra > wrote: >>> I then started a run at 96 clients which I accidentally killed shortly >>> before it was scheduled to finish, but the results are

Re: [HACKERS] Question about behavior of snapshot too old feature

On Mon, Oct 17, 2016 at 10:04 PM, Kevin Grittner wrote: > On Sun, Oct 16, 2016 at 9:26 PM, Masahiko Sawada > wrote: > >> When I set old_snapshot_threshold = 0 I got error at step #3, which >> means that the error is occurred without table pruning. > >

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

On Fri, 21 Oct 2016 14:29:24 +0900 Michael Paquier wrote: > That's invasive. I am wondering if a cleaner approach here would be a > flag in deleteOneObject() that performs the lock cleanup, as that's > what you are trying to solve here. The problem occurs earlier, at

Re: [HACKERS] [RFC] Transaction management overhaul is necessary?

On 21 October 2016 at 18:57, Pavel Stehule wrote: > 2016-10-21 10:24 GMT+02:00 Tsunakawa, Takayuki > : >> >> Hello, >> >> From our experience in handling customers' problems, I feel it's necessary >> to evolve PostgreSQL's transaction

[HACKERS] Default setting for autovacuum_freeze_max_age

Why is autovacuum_freeze_max_age's default set to 200 million, rather than something like 2 billion? It seems 2 billion is half way to wrap-around and would be a better default. Right now, the default seems to freeze 10x more often than it has to. Does it default to 200 million so clog can be

Re: [HACKERS] Improve output of BitmapAnd EXPLAIN ANALYZE

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > Changing it in a new major release seems entirely reasonable. > > It's still a crock though. I wonder whether it wouldn't be better to > change the nodeBitmap code so that when EXPLAIN ANALYZE is active, > it

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

Michael Paquier writes: > On Thu, Oct 20, 2016 at 9:30 PM, Constantin S. Pan wrote: >> I tried to fix the problem with a new backend not being >> able to reuse a temporary namespace when it contains >> thousands of temporary tables. I disabled locking

Re: [HACKERS] Parallel Index Scans

On Thu, Oct 20, 2016 at 10:33 PM, Robert Haas wrote: > On Wed, Oct 19, 2016 at 11:07 PM, Amit Kapila wrote: >>> Ideally, the parallel_workers storage parameter will rarely be >>> necessary because the optimizer will generally do the right thing in

Re: [HACKERS] Renaming of pg_xlog and pg_clog

* Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Oct 20, 2016 at 12:12 PM, Stephen Frost wrote: > > That said, I'd also like to see a --force or similar option or mechanism > > put in place to reduce the risk of users trashing their system because > > they think

Re: [HACKERS] pg_basebackup stream xlog to tar

On Mon, Oct 17, 2016 at 2:37 PM, Michael Paquier wrote: > Except that it looks in pretty good to me, so I am switching that as > ready for committer. + /* +* Create pg_xlog/archive_status (and thus pg_xlog) so we can write to +* basedir/pg_xlog as

Re: [HACKERS] PSA: Systemd will kill PostgreSQL

On 8/16/16 1:05 PM, Tom Lane wrote: > Oh, interesting. It had occurred to me that we might be able to dodge > this issue if we started to recommend using unnamed POSIX semaphores > instead of SysV. (Obviously we'd want to check performance, but it's > at least a plausible alternative.) I had

Re: [HACKERS] emergency outage requiring database restart

On Tue, Oct 18, 2016 at 8:45 AM, Merlin Moncure wrote: > Most or all the damage seemed to be to the system catalogs with > at least two critical tables dropped or inaccessible in some > fashion. A lot of the OIDs seemed to be pointing at the wrong > thing. While the oid in

Re: [HACKERS] Aggregate Push Down - Performing aggregation on foreign server

On Thu, Oct 20, 2016 at 5:38 AM, Jeevan Chalke wrote: > Changes look good to me. > Thanks for the detailed review. I didn't find anything structurally wrong with this patch, so I've committed it with many cosmetic changes. Judging by what happened with join

Re: [HACKERS] Default setting for autovacuum_freeze_max_age

Bruce Momjian writes: > Why is autovacuum_freeze_max_age's default set to 200 million, rather > than something like 2 billion? It seems 2 billion is half way to > wrap-around and would be a better default. Right now, the default seems > to freeze 10x more often than it has to.

Re: [HACKERS] Aggregate Push Down - Performing aggregation on foreign server

Robert Haas writes: > I didn't find anything structurally wrong with this patch, so I've > committed it with many cosmetic changes. Judging by what happened > with join pushdown, there are probably some residual bugs, but > hopefully not too many. dromedary seems to have

Re: [HACKERS] Renaming of pg_xlog and pg_clog

On 10/21/16 3:12 AM, David G. Johnston wrote: I have no problem continuing keeping with historical precedent ​and allowing mnemonic abbreviations in our directory and file names at this point. I'm still in favor of pg_xact. A search of the 9.6 docs brings up a number of hits for "xact":

Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

On 10/21/2016 08:13 AM, Amit Kapila wrote: On Fri, Oct 21, 2016 at 6:31 AM, Robert Haas wrote: On Thu, Oct 20, 2016 at 4:04 PM, Tomas Vondra wrote: I then started a run at 96 clients which I accidentally killed shortly before it was

Re: [HACKERS] Indirect indexes

On Thu, Oct 20, 2016 at 4:24 PM, Bruce Momjian wrote: > On Thu, Oct 20, 2016 at 05:14:51PM +0200, Petr Jelinek wrote: > > > Also, it seems indirect indexes would be useful for indexing columns > > > that are not updated frequently on tables that are updated frequently, > > >

[HACKERS] [RFC] Transaction management overhaul is necessary?

Hello, >From our experience in handling customers' problems, I feel it's necessary to >evolve PostgreSQL's transaction management. The concrete problems are: 1. PostgreSQL cannot end and begin transactions in PL/pgSQL and PL/Java stored functions. This is often the reason people could not

Re: [HACKERS] Parallel tuplesort (for parallel B-Tree index creation)

On Tue, Oct 18, 2016 at 3:48 AM, Peter Geoghegan wrote: > On Mon, Oct 17, 2016 at 5:36 AM, Amit Kapila wrote: > > I read the following paragraph from the Volcano paper just now: > > """ > During implementation and benchmarking of parallel sorting, we

Re: [HACKERS] [RFC] Transaction management overhaul is necessary?

2016-10-21 10:24 GMT+02:00 Tsunakawa, Takayuki < tsunakawa.ta...@jp.fujitsu.com>: > Hello, > > From our experience in handling customers' problems, I feel it's necessary > to evolve PostgreSQL's transaction management. The concrete problems are: > > 1. PostgreSQL cannot end and begin

Re: [HACKERS] Parallel tuplesort (for parallel B-Tree index creation)

On Fri, Oct 21, 2016 at 4:25 PM, Amit Kapila wrote: > On Tue, Oct 18, 2016 at 3:48 AM, Peter Geoghegan wrote: >> On Mon, Oct 17, 2016 at 5:36 AM, Amit Kapila wrote: >> >> I read the following paragraph from the Volcano paper

Re: [HACKERS] Parallel tuplesort (for parallel B-Tree index creation)

On Thu, Oct 20, 2016 at 12:03 AM, Peter Geoghegan wrote: > On Wed, Oct 19, 2016 at 7:39 AM, Robert Haas wrote: >> Gather Merge can't emit a tuple unless it has buffered at least one >> tuple from every producer; otherwise, the next tuple it receives from

Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

On Fri, Oct 21, 2016 at 1:07 PM, Tomas Vondra wrote: > On 10/21/2016 08:13 AM, Amit Kapila wrote: >> >> On Fri, Oct 21, 2016 at 6:31 AM, Robert Haas >> wrote: >>> >>> On Thu, Oct 20, 2016 at 4:04 PM, Tomas Vondra >>>

Re: [HACKERS] Stopping logical replication protocol

Craig, Andres what do you thinks about previous message? 2016-10-06 0:35 GMT+03:00 Vladimir Gordiychuk : > > Vladimir? I'm running out of time to spend on this at least until the next > CF. Think you can make these changes? > > Yes, I can. But I thinks It should be discuss