Re: [HACKERS] Completing PL support for Event Triggers

2013-12-09 Thread Dimitri Fontaine
Peter Eisentraut writes: > I think this is wrong, and the reason it crashes if you remove it is > that you need to call increment_prodesc_refcount(prodesc), like in the > other handlers. Thanks, looks like this indeed. > Attached is my "final" patch. Let me know if it's OK for you. It looks li

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

2013-12-09 Thread Heikki Linnakangas
On 12/08/2013 09:56 PM, Alexander Korotkov wrote: On Fri, Nov 29, 2013 at 11:17 PM, Heikki Linnakangas < hlinnakan...@vmware.com> wrote: I'll continue reviewing next week.. Got dragged into other things and didn't make any progress on this last week. I'm trying again now. Good. Thanks for

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

2013-12-09 Thread Alexander Korotkov
On Mon, Dec 9, 2013 at 1:18 PM, Heikki Linnakangas wrote: > On 12/08/2013 09:56 PM, Alexander Korotkov wrote: > >> On Fri, Nov 29, 2013 at 11:17 PM, Heikki Linnakangas < >> hlinnakan...@vmware.com> wrote: >> >> I'll continue reviewing next week.. >>> >> > Got dragged into other things and didn't

Re: [HACKERS] Time-Delayed Standbys

2013-12-09 Thread KONDO Mitsumasa
Hi Fabrízio, I test your v4 patch, and send your review comments. * Fix typo > 49 -# commited transactions from the master, specify a recovery time delay. > 49 +# committed transactions from the master, specify a recovery time delay. * Fix white space > 177 - if (secs <= 0 && micr

Re: [HACKERS] Time-Delayed Standbys

2013-12-09 Thread Pavel Stehule
2013/12/9 KONDO Mitsumasa > Hi Fabrízio, > > I test your v4 patch, and send your review comments. > > * Fix typo > > 49 -# commited transactions from the master, specify a recovery time > delay. > > 49 +# committed transactions from the master, specify a recovery time > delay. > > * Fix white spa

Re: [HACKERS] Time-Delayed Standbys

2013-12-09 Thread KONDO Mitsumasa
(2013/12/09 19:36), KONDO Mitsumasa wrote: * Problem 1 I read your wittened document. There is "PITR has not affected". However, when I run PITR with min_standby_apply_delay=300, it cannot start server. The log is under following. [mitsu-ko@localhost postgresql]$ bin/pg_ctl -D data2 start se

Re: [HACKERS] Time-Delayed Standbys

2013-12-09 Thread KONDO Mitsumasa
(2013/12/09 19:35), Pavel Stehule wrote: 2013/12/9 KONDO Mitsumasa mailto:kondo.mitsum...@lab.ntt.co.jp>> Hi Fabrízio, I test your v4 patch, and send your review comments. * Fix typo > 49 -# commited transactions from the master, specify a recovery time delay. > 49 +#

Re: [HACKERS] Time-Delayed Standbys

2013-12-09 Thread Andres Freund
On 2013-12-09 19:51:01 +0900, KONDO Mitsumasa wrote: > Add my comment. We have to consider three situations. > > 1. PITR > 2. replication standby > 3. replication standby with restore_command > > I think this patch cannot delay in 1 situation. Why? Greetings, Andres Freund -- Andres Freund

Re: [HACKERS] Recovery to backup point

2013-12-09 Thread MauMau
From: "Michael Paquier" As far as I recall, I don't think so. The problem and the way to solve that are clear. The only trick is to be sure that recovery is done just until a consistent point is reached, and to implement that cleanly. May I implement this feature and submit a patch for the next

Re: [HACKERS] Time-Delayed Standbys

2013-12-09 Thread Craig Ringer
On 12/04/2013 02:46 AM, Robert Haas wrote: >> Thanks for your review Christian... > > So, I proposed this patch previously and I still think it's a good > idea, but it got voted down on the grounds that it didn't deal with > clock drift. I view that as insufficient reason to reject the > feature,

Re: [HACKERS] Recovery to backup point

2013-12-09 Thread Heikki Linnakangas
On 12/09/2013 02:03 PM, MauMau wrote: From: "Michael Paquier" As far as I recall, I don't think so. The problem and the way to solve that are clear. The only trick is to be sure that recovery is done just until a consistent point is reached, and to implement that cleanly. May I implement this

Re: [HACKERS] [bug fix] pg_ctl always uses the same event source

2013-12-09 Thread MauMau
From: "Amit Kapila" 1. isn't it better to handle as it is done in write_eventlog() which means if string is empty then use PostgreSQL. "evtHandle = RegisterEventSource(NULL, event_source ? event_source : "PostgreSQL");" Thank you for reviewing. Yes, I did so with the first revision of this

Re: [HACKERS] Recovery to backup point

2013-12-09 Thread MauMau
From: "Heikki Linnakangas" Thanks. Looks sane, although I don't much like the proposed interface to trigger this, setting recovery_target_time='backup_point'. What the code actually does is to stop recovery as soon as you reach consistency, which might not have anything to do with a backup. If

[HACKERS] How to do fast performance timing

2013-12-09 Thread Benedikt Grundmann
At Jane Street we have recently spend a lot of time trying to get a fast gettimeofday. I saw lots of references in various postgres hacker threads related to a lack of such a facility so The culmination of those efforts can be read here: https://github.com/janestreet/core/blob/master/lib/ti

Re: [HACKERS] Time-Delayed Standbys

2013-12-09 Thread Greg Stark
On 9 Dec 2013 12:16, "Craig Ringer" wrote: > The only way to "deal with" clock drift that isn't fragile in the face > of variable latency, etc, is to basically re-implement (S)NTP in order > to find out what the clock difference with the remote is. There's actually an entirely different way to "

Re: [HACKERS] WITHIN GROUP patch

2013-12-09 Thread Peter Eisentraut
On 11/21/13, 5:04 AM, Atri Sharma wrote: > Please find attached the latest patch for WITHIN GROUP. This patch is > after fixing the merge conflicts. I would like to see more explanations and examples in the documentation. You introduce this feature with "Ordered set functions compute a single res

Re: [HACKERS] Backup throttling

2013-12-09 Thread Fujii Masao
On Fri, Dec 6, 2013 at 6:43 PM, Boszormenyi Zoltan wrote: > Hi, > > 2013-12-05 15:36 keltezéssel, Antonin Houska írta: > >> On 12/02/2013 02:23 PM, Boszormenyi Zoltan wrote: >>> >>> Hi, >>> >>> I am reviewing your patch. >> >> Thanks. New version attached. > > > I have reviewed and tested it and m

Re: [HACKERS] JSON decoding plugin

2013-12-09 Thread Merlin Moncure
On Mon, Dec 9, 2013 at 7:03 AM, Euler Taveira wrote: > Hi, > > A few months ago, it was proposed [1] that would be interested to have a > json output plugin for logical decoding. Here it is. > > Each transaction is a JSON object that can contain xid (optional), > timestamp (optional), and change a

Re: [HACKERS] pg_archivecleanup bug

2013-12-09 Thread Robert Haas
On Fri, Dec 6, 2013 at 11:10 AM, Tom Lane wrote: > Robert Haas writes: >> On Thu, Dec 5, 2013 at 6:15 PM, Tom Lane wrote: >>> In general, I think there is no excuse for code in the backend to use >>> readdir() directly; it should be using ReadDir(), which takes care of this >>> as well as error

Re: [HACKERS] pg_archivecleanup bug

2013-12-09 Thread Robert Haas
On Thu, Dec 5, 2013 at 6:15 PM, Tom Lane wrote: > But the other usages seem to be in assorted utilities, which > will need to do it right for themselves. initdb.c's walkdir() seems to > have it right and might be a reasonable model to follow. Or maybe we > should invent a frontend-friendly versi

Re: [HACKERS] JSON decoding plugin

2013-12-09 Thread Euler Taveira
On 09-12-2013 13:12, Merlin Moncure wrote: > This is pretty neat. Couple minor questions: > *) Aren't you *en*coding data into json, not the other way around (decoding?) > Yes. The 'decoding' came from the functionality (logical decoding) and because the POC plugin is named 'test_decoding'. I als

Re: [HACKERS] RFC: programmable file format for postgresql.conf

2013-12-09 Thread Robert Haas
On Fri, Dec 6, 2013 at 10:28 PM, Álvaro Hernández Tortosa wrote: > I think both could be used a lot, editing directly a rich configuration > file or using a GUI tool. I'm trying to suggest supporting both. I don't really understand how changing the file format fixes anything. You could make

[HACKERS] What are multixactids?

2013-12-09 Thread hubert depesz lubaczewski
Hi, when working on fixing the bug related to vacuum freeze, I found out that there is something called "MultiXactId". Searching docs showed that it is mentioned only once, in release notes to 9.3.2: http://www.postgresql.org/search/?u=%2Fdocs%2F9.3%2F&q=multixactid What's more - I found that Pet

Re: [HACKERS] Extension Templates S03E11

2013-12-09 Thread Robert Haas
On Sat, Dec 7, 2013 at 3:12 AM, Jeff Davis wrote: > So if we do it this way, then we should pick a new name, like "package". That was my first reaction as well, when I looked at this a few years ago, but I've since backed away from that position. You're certainly correct that it's awkward to hav

Re: [HACKERS] RFC: programmable file format for postgresql.conf

2013-12-09 Thread Greg Stark
On Sat, Dec 7, 2013 at 3:28 AM, Álvaro Hernández Tortosa wrote: >>> "Right now, writing such a tool in a generic way gets so bogged down >>> just in parsing/manipulating the postgresql.conf file that it's hard to >>> focus on actually doing the tuning part." >> >> That was in 2008. I don't think

Re: [HACKERS] Extension Templates S03E11

2013-12-09 Thread Jeff Davis
On Mon, 2013-12-09 at 12:17 -0500, Robert Haas wrote: > On Sat, Dec 7, 2013 at 3:12 AM, Jeff Davis wrote: > > So if we do it this way, then we should pick a new name, like "package". > > That was my first reaction as well, when I looked at this a few years > ago, but I've since backed away from t

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Josh Berkus
Greg, > I really don't believe the 5% thing. It's not enough for n_distinct > and it's *far* too high a value for linear properties like histograms > or nullfrac etc. Actually, it is enough for n_distinct, or more properly, 5% is as good as you can get for n_distinct unless you're going to jump

Re: [HACKERS] Performance optimization of btree binary search

2013-12-09 Thread Peter Geoghegan
On Fri, Dec 6, 2013 at 4:53 PM, Peter Geoghegan wrote: > I had considered that something like Intel Speedstep technology had a > role here, but I'm pretty sure it steps up very aggressively when > things are CPU bound - I tested that against a Core 2 Duo desktop a > couple of years back, where it

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Tom Lane
Josh Berkus writes: > Reading 5% of a 200GB table is going to be considerably faster than > reading the whole thing, if that 5% is being scanned in a way that the > FS understands. Really? See the upthread point that reading one sector from each track has just as much seek overhead as reading th

Re: [HACKERS] Extra functionality to createuser

2013-12-09 Thread Robert Haas
On Sat, Dec 7, 2013 at 11:39 PM, Amit Kapila wrote: > On Fri, Dec 6, 2013 at 10:31 AM, Peter Eisentraut wrote: >> On Wed, 2013-11-20 at 11:23 -0500, Christopher Browne wrote: >>> I note that similar (with not quite identical behaviour) issues apply >>> to the user name. Perhaps the >>> resolutio

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Robert Haas
On Mon, Dec 9, 2013 at 1:03 PM, Josh Berkus wrote: >> I really don't believe the 5% thing. It's not enough for n_distinct >> and it's *far* too high a value for linear properties like histograms >> or nullfrac etc. > > Actually, it is enough for n_distinct, or more properly, 5% is as good > as you

Re: [HACKERS] plpgsql_check_function - rebase for 9.3

2013-12-09 Thread Jim Nasby
On 12/8/13 11:24 PM, Pavel Stehule wrote: > #option check_on_first_start > #option check_on_create > #option check_newer what exactly check_newer means, does it mean whenever a function is replaced (changed)? no, it means, so request for check will be ignored ever - some

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Greg Stark
On Mon, Dec 9, 2013 at 6:03 PM, Josh Berkus wrote: > > It's also applicable for the other stats; histogram buckets constructed > from a 5% sample are more likely to be accurate than those constructed > from a 0.1% sample. Same with nullfrac. The degree of improved > accuracy, would, of course,

Re: [HACKERS] About shared cache invalidation mechanism

2013-12-09 Thread Robert Haas
On Sun, Dec 8, 2013 at 1:55 AM, huaicheng Li wrote: > I know that all invalid cache messages are stored in the > shmInvalidationBuffer ring buffer and that they should be consumed by all > other backends to keep their own cache fresh. Since there may be some > "stragglers" which process the SI mes

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Greg Stark
On Mon, Dec 9, 2013 at 6:54 PM, Greg Stark wrote: > > This "some math" is straightforward basic statistics. The 95th > percentile confidence interval for a sample consisting of 300 samples > from a population of a 1 million would be 5.66%. A sample consisting > of 1000 samples would have a 95th p

Re: [HACKERS] What are multixactids?

2013-12-09 Thread Andreas Karlsson
On 12/09/2013 06:04 PM, hubert depesz lubaczewski wrote: Hi, when working on fixing the bug related to vacuum freeze, I found out that there is something called "MultiXactId". Searching docs showed that it is mentioned only once, in release notes to 9.3.2: http://www.postgresql.org/search/?u=%2F

Re: [HACKERS] How to do fast performance timing

2013-12-09 Thread Jim Nasby
On 12/9/13 7:33 AM, Benedikt Grundmann wrote: At Jane Street we have recently spend a lot of time trying to get a fast gettimeofday. I saw lots of references in various postgres hacker threads related to a lack of such a facility so The culmination of those efforts can be read here: htt

Re: [HACKERS] What are multixactids?

2013-12-09 Thread hubert depesz lubaczewski
On Mon, Dec 09, 2013 at 07:59:10PM +0100, Andreas Karlsson wrote: > As you can see from Peter's message it is explained in > README.tuplock[1]. Basically it is used whenever more than one lock > is acquired on the same tuples as a reference to where the locks are > stored. It can store updated/dele

Re: [HACKERS] plpgsql_check_function - rebase for 9.3

2013-12-09 Thread Pavel Stehule
2013/12/9 Jim Nasby > On 12/8/13 11:24 PM, Pavel Stehule wrote: > >> > #option check_on_first_start >> > #option check_on_create >> > #option check_newer >> >> what exactly check_newer means, does it mean whenever a function is >> replaced (changed)? >> >> >> no, it means,

Re: [HACKERS] What are multixactids?

2013-12-09 Thread Andreas Karlsson
On 12/09/2013 08:05 PM, hubert depesz lubaczewski wrote: Thanks. Read that. Still, it would be good to have some information in normal docs, but I guess this has to do for now. It is mentioned several times in the documentation but I do not think it is explained anywhere. -- Andreas Karlsson

Re: [HACKERS] What are multixactids?

2013-12-09 Thread Jim Nasby
On 12/9/13 1:05 PM, hubert depesz lubaczewski wrote: On Mon, Dec 09, 2013 at 07:59:10PM +0100, Andreas Karlsson wrote: I recommend you read the section in README.tuplock. 1. https://github.com/postgres/postgres/blob/d9250da032e723d80bb0150b9276cc544df6a087/src/backend/access/heap/README.tuplock

Re: [HACKERS] shared memory message queues

2013-12-09 Thread Robert Haas
On Sun, Dec 8, 2013 at 5:52 AM, Kohei KaiGai wrote: > 2013/12/6 Kohei KaiGai : >> What will happen if sender tries to send a large chunk that needs to >> be split into multiple sub-chunks and receiver concurrently detaches >> itself from the queue during the writes by sender? >> It seems to me the

[HACKERS] In-Memory Columnar Store

2013-12-09 Thread knizhnik
Hello! I want to annouce my implementation of In-Memory Columnar Store extension for PostgreSQL: Documentation: http://www.garret.ru/imcs/user_guide.html Sources: http://www.garret.ru/imcs-1.01.tar.gz Any feedbacks, bug reports and suggestions are welcome. Vertical representation o

Re: [HACKERS] UNNEST with multiple args, and TABLE with multiple funcs

2013-12-09 Thread Noah Misch
On Thu, Dec 05, 2013 at 10:34:08PM -0500, Stephen Frost wrote: > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > Noah Misch writes: > > > Two naming proposals, "ROWS FROM" and "TABLE FROM", got an ACK from more > > > than > > > one person apiece. I move that we settle on "ROWS FROM". > > > > I'm not

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

2013-12-09 Thread Heikki Linnakangas
On 12/09/2013 11:34 AM, Alexander Korotkov wrote: On Mon, Dec 9, 2013 at 1:18 PM, Heikki Linnakangas wrote: Even if we use varbyte encoding, I wonder if it would be better to treat block + offset number as a single 48-bit integer, rather than encode them separately. That would allow the delta o

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Jeff Janes
On Sat, Dec 7, 2013 at 11:46 AM, Robert Haas wrote: > On Tue, Dec 3, 2013 at 6:30 PM, Greg Stark wrote: > > I always gave the party line that ANALYZE only takes a small > > constant-sized sample so even very large tables should be very quick. > > But after hearing the same story again in Heroku

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Jim Nasby
On 12/6/13 3:21 AM, Andres Freund wrote: On 2013-12-05 17:52:34 -0800, Peter Geoghegan wrote: Has anyone ever thought about opportunistic ANALYZE piggy-backing on other full-table scans? That doesn't really help Greg, because his complaint is mostly that a fresh ANALYZE is too expensive, but it

Re: [HACKERS] plpgsql_check_function - rebase for 9.3

2013-12-09 Thread Jim Nasby
On 12/9/13 1:08 PM, Pavel Stehule wrote: So presumably it would be check_never, not check_newer... :) BTW, it's not terribly hard to work around the temp table issue; you just need to create the expected table in the session when you create the function. But even in this case, I think it w

Re: [HACKERS] Bug in VACUUM reporting of "removed %d row versions" in 9.2+

2013-12-09 Thread Bruce Momjian
Where are we on this? --- On Fri, May 10, 2013 at 04:37:58PM +0100, Simon Riggs wrote: > Commit d0dcb315db0043f10073a9a244cea138e9e60edd and previous > introduced a bug into the reporting of removed row versions. ('Twas > my

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Jim Nasby
On 12/8/13 1:49 PM, Heikki Linnakangas wrote: On 12/08/2013 08:14 PM, Greg Stark wrote: The whole accounts table is 1.2GB and contains 10 million rows. As expected with rows_per_block set to 1 it reads 240MB of that containing nearly 2 million rows (and takes nearly 20s -- doing a full table sca

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Peter Geoghegan
On Mon, Dec 9, 2013 at 1:18 PM, Jeff Janes wrote: > I don't recall ever tracing a bad plan down to a bad n_distinct. It does happen. I've seen it several times. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: htt

Re: [HACKERS] [RFC] Shouldn't we remove annoying FATAL messages from server log?

2013-12-09 Thread Kevin Grittner
MauMau wrote: > From: "Greg Stark" >> On the client end the FATAL is pretty logical but in the logs it >> makes it sound like the entire server died. I agree that is easily misunderstood, especially since a FATAL problem is less severe than a PANIC; while in common English usage panic is what m

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Heikki Linnakangas
On 12/09/2013 11:35 PM, Jim Nasby wrote: On 12/8/13 1:49 PM, Heikki Linnakangas wrote: On 12/08/2013 08:14 PM, Greg Stark wrote: The whole accounts table is 1.2GB and contains 10 million rows. As expected with rows_per_block set to 1 it reads 240MB of that containing nearly 2 million rows (and

Re: [HACKERS] stats for network traffic WIP

2013-12-09 Thread Nigel Heron
On Sat, Dec 7, 2013 at 1:17 PM, Fujii Masao wrote: > > Could you share the performance numbers? I'm really concerned about > the performance overhead caused by this patch. > I've tried pgbench in select mode with small data sets to avoid disk io and didn't see any difference. That was on my old c

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Claudio Freire
On Mon, Dec 9, 2013 at 6:47 PM, Heikki Linnakangas wrote: > On 12/09/2013 11:35 PM, Jim Nasby wrote: >> >> On 12/8/13 1:49 PM, Heikki Linnakangas wrote: >>> >>> On 12/08/2013 08:14 PM, Greg Stark wrote: The whole accounts table is 1.2GB and contains 10 million rows. As expected with

Re: [HACKERS] plpgsql_check_function - rebase for 9.3

2013-12-09 Thread Peter Eisentraut
On 12/8/13, 12:01 PM, Pavel Stehule wrote: > But still I have no idea, how to push check without possible slowdown > execution with code duplication Create a GUC parameter plpgsql.slow_checks or whatever (perhaps more specific), which people can turn on when they run their test suites. This doesn

Re: [HACKERS] pgsql: Fix a couple of bugs in MultiXactId freezing

2013-12-09 Thread Alvaro Herrera
Here's a revamped version of this patch. One thing I didn't do here is revert the exporting of CreateMultiXactId, but I don't see any way to avoid that. Andres mentioned the idea of sharing some code between heap_prepare_freeze_tuple and heap_tuple_needs_freeze, but I haven't explored that. --

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Robert Haas
On Mon, Dec 9, 2013 at 4:18 PM, Jeff Janes wrote: > My reading of the code is that if it is not in the MCV, then it is assumed > to have the average selectivity (about 1/n_distinct, but deflating top and > bottom for the MCV list). There is also a check that it is less than the > least common of

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Heikki Linnakangas
On 12/09/2013 11:56 PM, Claudio Freire wrote: On Mon, Dec 9, 2013 at 6:47 PM, Heikki Linnakangas wrote: On 12/09/2013 11:35 PM, Jim Nasby wrote: On 12/8/13 1:49 PM, Heikki Linnakangas wrote: On 12/08/2013 08:14 PM, Greg Stark wrote: The whole accounts table is 1.2GB and contains 10 millio

Re: [HACKERS] Re: [RFC] Shouldn't we remove annoying FATAL messages from server log?

2013-12-09 Thread Jim Nasby
On 12/8/13 3:08 PM, MauMau wrote: #5 is output when the DBA shuts down the replication standby server. #6 is output when the DBA shuts down the server if he is using any custom background worker. These messages are always output. What I'm seeing as a problem is that FATAL messages are output in

Re: [HACKERS] Problem with displaying "wide" tables in psql

2013-12-09 Thread Jeff Janes
On Mon, Dec 2, 2013 at 10:45 PM, Sergey Muraviov < sergey.k.murav...@gmail.com> wrote: > Hi. > > Psql definitely have a problem with displaying "wide" tables. > Even in expanded mode, they look horrible. > So I tried to solve this problem. > I get compiler warnings: print.c: In function 'print_a

Re: [HACKERS] [RFC] Shouldn't we remove annoying FATAL messages from server log?

2013-12-09 Thread Jim Nasby
On 12/6/13 7:38 AM, Andres Freund wrote: On 2013-12-06 22:35:21 +0900, MauMau wrote: From: "Tom Lane" No. They are FATAL so far as the individual session is concerned. Possibly some documentation effort is needed here, but I don't think any change in the code behavior would be an improvement.

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Claudio Freire
On Mon, Dec 9, 2013 at 8:14 PM, Heikki Linnakangas wrote: > On 12/09/2013 11:56 PM, Claudio Freire wrote: >> Without patches to the kernel, it is much better. >> >> posix_fadvise interferes with read-ahead, so posix_fadvise on, say, >> bitmap heap scans (or similarly sorted analyze block samples)

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Heikki Linnakangas
Claudio Freire wrote: >On Mon, Dec 9, 2013 at 8:14 PM, Heikki Linnakangas > wrote: >> I took a stab at using posix_fadvise() in ANALYZE. It turned out to >be very >> easy, patch attached. Your mileage may vary, but I'm seeing a nice >gain from >> this on my laptop. Taking a 3 page sample of a

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Claudio Freire
On Mon, Dec 9, 2013 at 8:45 PM, Heikki Linnakangas wrote: > Claudio Freire wrote: >>On Mon, Dec 9, 2013 at 8:14 PM, Heikki Linnakangas >> wrote: >>> I took a stab at using posix_fadvise() in ANALYZE. It turned out to >>be very >>> easy, patch attached. Your mileage may vary, but I'm seeing a nice

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Tom Lane
Heikki Linnakangas writes: > Maybe. Or maybe the heuristic read ahead isn't significant/helpful, when > you're prefetching with posix_fadvise anyway. Yeah. If we're not reading consecutive blocks, readahead is unlikely to do anything anyhow. Claudio's comments do suggest that it might be a bad

Re: [HACKERS] [RFC] Shouldn't we remove annoying FATAL messages from server log?

2013-12-09 Thread Tom Lane
Jim Nasby writes: > Arguably 1-3 are inaccurate since they're not really about a backend dying... > they occur during the startup phase; you never even get a functioning > backend. That's a marked difference from other uses of FATAL. How so? "FATAL" means "an error that terminates your session

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Mark Kirkwood
On 10/12/13 12:14, Heikki Linnakangas wrote: I took a stab at using posix_fadvise() in ANALYZE. It turned out to be very easy, patch attached. Your mileage may vary, but I'm seeing a nice gain from this on my laptop. Taking a 3 page sample of a table with 717717 pages (ie. slightly large

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Tom Lane
Mark Kirkwood writes: > I did a test run: > pgbench scale 2000 (pgbench_accounts approx 25GB). > postgres 9.4 > i7 3.5Ghz Cpu > 16GB Ram > 500 GB Velociraptor 10K > (cold os and pg cache both runs) > Without patch: ANALYZE pgbench_accounts90s > With patch: ANALYZE pgbench_accounts 91s >

Re: [HACKERS] [RFC] Shouldn't we remove annoying FATAL messages from server log?

2013-12-09 Thread Jim Nasby
On 12/9/13 5:56 PM, Tom Lane wrote: Jim Nasby writes: Arguably 1-3 are inaccurate since they're not really about a backend dying... they occur during the startup phase; you never even get a functioning backend. That's a marked difference from other uses of FATAL. How so? "FATAL" means "an

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Mark Kirkwood
On 10/12/13 13:14, Mark Kirkwood wrote: On 10/12/13 12:14, Heikki Linnakangas wrote: I took a stab at using posix_fadvise() in ANALYZE. It turned out to be very easy, patch attached. Your mileage may vary, but I'm seeing a nice gain from this on my laptop. Taking a 3 page sample of a ta

Re: [HACKERS] [RFC] Shouldn't we remove annoying FATAL messages from server log?

2013-12-09 Thread Tom Lane
Jim Nasby writes: > On 12/9/13 5:56 PM, Tom Lane wrote: >> How so? "FATAL" means "an error that terminates your session", which >> is exactly what these are. > Except in these cases the user never actually got a working session; their > request was denied. > To be clear, from the client standp

Re: [HACKERS] [RFC] Shouldn't we remove annoying FATAL messages from server log?

2013-12-09 Thread Craig Ringer
On 12/05/2013 11:25 PM, MauMau wrote: > Hello, > > My customers and colleagues sometimes (or often?) ask about the > following message: > > FATAL: the database system is starting up I would LOVE that message to do away, forever. It's a huge PITA for automated log monitoring, analysis, and aler

Re: [HACKERS] Re: [RFC] Shouldn't we remove annoying FATAL messages from server log?

2013-12-09 Thread Craig Ringer
On 12/06/2013 03:02 AM, Josh Berkus wrote: > Heck, I'd be happy just to have a class of messages which specifically > means "OMG, there's something wrong with the server", that is, a flag > for messages which only occur when PostgreSQL encounters a bug, data > corrpution, or platform error. Right

Re: [HACKERS] pgsql: Fix a couple of bugs in MultiXactId freezing

2013-12-09 Thread Andres Freund
On 2013-12-09 19:14:58 -0300, Alvaro Herrera wrote: > Here's a revamped version of this patch. One thing I didn't do here is > revert the exporting of CreateMultiXactId, but I don't see any way to > avoid that. I don't so much have a problem with exporting CreateMultiXactId(), just with exporting

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Mark Kirkwood
On 10/12/13 13:20, Mark Kirkwood wrote: On 10/12/13 13:14, Mark Kirkwood wrote: On 10/12/13 12:14, Heikki Linnakangas wrote: I took a stab at using posix_fadvise() in ANALYZE. It turned out to be very easy, patch attached. Your mileage may vary, but I'm seeing a nice gain from this on my la

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Craig Ringer
On 12/06/2013 09:52 AM, Peter Geoghegan wrote: > Has anyone ever thought about opportunistic ANALYZE piggy-backing on > other full-table scans? That doesn't really help Greg, because his > complaint is mostly that a fresh ANALYZE is too expensive, but it > could be an interesting, albeit risky appr

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Craig Ringer
On 12/10/2013 05:20 AM, Jim Nasby wrote: >> > > FWIW, if synchronize_seqscans is on I'd think it'd be pretty easy to > fire up a 2nd backend to do the ANALYZE portion (or perhaps use Robert's > fancy new shared memory stuff). Apologies for posting the same as a new idea before I saw your post. I

Re: [HACKERS] dblink performance regression

2013-12-09 Thread Jim Nasby
On 12/7/13 7:50 PM, Joe Conway wrote: On 12/07/2013 05:41 PM, Fabrízio de Royes Mello wrote: > >On Sat, Dec 7, 2013 at 11:20 PM, Michael Paquier >mailto:michael.paqu...@gmail.com>> >wrote: >>> >>>IMHO is more elegant create a procedure to encapsulate the code >>>to avoid redundancy. >>Yep, per

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Mark Kirkwood
On 10/12/13 13:53, Mark Kirkwood wrote: On 10/12/13 13:20, Mark Kirkwood wrote: On 10/12/13 13:14, Mark Kirkwood wrote: On 10/12/13 12:14, Heikki Linnakangas wrote: I took a stab at using posix_fadvise() in ANALYZE. It turned out to be very easy, patch attached. Your mileage may vary, but I

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Mark Kirkwood
On 10/12/13 15:04, Mark Kirkwood wrote: On 10/12/13 13:53, Mark Kirkwood wrote: On 10/12/13 13:20, Mark Kirkwood wrote: On 10/12/13 13:14, Mark Kirkwood wrote: On 10/12/13 12:14, Heikki Linnakangas wrote: I took a stab at using posix_fadvise() in ANALYZE. It turned out to be very easy, pat

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Mark Kirkwood
On 10/12/13 15:11, Mark Kirkwood wrote: On 10/12/13 15:04, Mark Kirkwood wrote: On 10/12/13 13:53, Mark Kirkwood wrote: On 10/12/13 13:20, Mark Kirkwood wrote: On 10/12/13 13:14, Mark Kirkwood wrote: On 10/12/13 12:14, Heikki Linnakangas wrote: I took a stab at using posix_fadvise() in ANA

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Josh Berkus
On 12/09/2013 02:37 PM, Robert Haas wrote: > I've never seen an n_distinct value of more than 5 digits, regardless > of reality. Typically I've seen 20-50k, even if the real number is > much higher. But the n_distinct value is only for non-MCVs, so if we > estimate the selectivity of column = 'ra

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Mark Kirkwood
On 10/12/13 15:17, Mark Kirkwood wrote: On 10/12/13 15:11, Mark Kirkwood wrote: On 10/12/13 15:04, Mark Kirkwood wrote: On 10/12/13 13:53, Mark Kirkwood wrote: On 10/12/13 13:20, Mark Kirkwood wrote: On 10/12/13 13:14, Mark Kirkwood wrote: On 10/12/13 12:14, Heikki Linnakangas wrote: I to

Re: [HACKERS] [patch] Adding EXTRA_REGRESS_OPTS to all pg_regress invocations

2013-12-09 Thread Bruce Momjian
On Thu, Dec 5, 2013 at 09:52:27AM +0100, Christoph Berg wrote: > > The change is sane in itself. It won't affect anyone who doesn't use > > EXTRA_REGRESS_OPTS. Why would we want to make packagers do MORE > > work? > > The patch has been in the Debian/Ubuntu/apt.pg.o packages for some > time, for

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Mark Kirkwood
On 10/12/13 15:32, Mark Kirkwood wrote: On 10/12/13 15:17, Mark Kirkwood wrote: On 10/12/13 15:11, Mark Kirkwood wrote: On 10/12/13 15:04, Mark Kirkwood wrote: On 10/12/13 13:53, Mark Kirkwood wrote: On 10/12/13 13:20, Mark Kirkwood wrote: On 10/12/13 13:14, Mark Kirkwood wrote: On 10/12/13

Re: [HACKERS] [bug fix] pg_ctl always uses the same event source

2013-12-09 Thread Amit Kapila
On Mon, Dec 9, 2013 at 5:52 PM, MauMau wrote: > From: "Amit Kapila" > >> 1. isn't it better to handle as it is done in write_eventlog() which >> means if string is empty then >>use PostgreSQL. >> "evtHandle = RegisterEventSource(NULL, event_source ? event_source : >> "PostgreSQL");" > > > Tha

Re: [HACKERS] Storing pg_stat_statements query texts externally, pg_stat_statements in core

2013-12-09 Thread Peter Geoghegan
On Sat, Dec 7, 2013 at 9:26 AM, Fujii Masao wrote: > The patch doesn't apply cleanly against the master due to recent change of > pg_stat_statements. Could you update the patch? Revision is attached, including changes recently discussed on other thread [1] to allow avoiding sending query text whe

Re: [HACKERS] Storing pg_stat_statements query texts externally, pg_stat_statements in core

2013-12-09 Thread Peter Geoghegan
On Mon, Dec 9, 2013 at 7:31 PM, Peter Geoghegan wrote: > I go to some lengths to to avoid doing this with only a shared lock. I should have said: I go to great lengths to do this with only a shared lock, and not an exclusive (see gc_count stuff). -- Peter Geoghegan -- Sent via pgsql-hackers

Re: [HACKERS] Proof of concept: standalone backend with full FE/BE protocol

2013-12-09 Thread Bruce Momjian
On Fri, Dec 6, 2013 at 04:04:36PM +0100, Andres Freund wrote: > On 2013-12-05 23:01:28 +0200, Heikki Linnakangas wrote: > > On 12/05/2013 10:37 PM, Robert Haas wrote: > > >On Thu, Dec 5, 2013 at 3:05 PM, Tom Lane wrote: > > >>It might be unpleasant to use in some cases, though. > > > > > >Why wou

Re: [HACKERS] [patch] Adding EXTRA_REGRESS_OPTS to all pg_regress invocations

2013-12-09 Thread Michael Paquier
On Tue, Dec 10, 2013 at 12:08 PM, Bruce Momjian wrote: > On Thu, Dec 5, 2013 at 09:52:27AM +0100, Christoph Berg wrote: >> > The change is sane in itself. It won't affect anyone who doesn't use >> > EXTRA_REGRESS_OPTS. Why would we want to make packagers do MORE >> > work? >> >> The patch has bee

Re: [HACKERS] plpgsql_check_function - rebase for 9.3

2013-12-09 Thread Amit Kapila
On Mon, Dec 9, 2013 at 10:54 AM, Pavel Stehule wrote: > 2013/12/9 Amit Kapila >> >> > >> > There are two points, that should be solved >> > >> > a) introduction a dependency to other object in schema - now CREATE >> > FUNCTION >> > is fully independent on others >> > >> > b) slow start - if we ch

Re: [HACKERS] [RFC] Shouldn't we remove annoying FATAL messages from server log?

2013-12-09 Thread Bruce Momjian
On Tue, Dec 10, 2013 at 08:47:22AM +0800, Craig Ringer wrote: > On 12/05/2013 11:25 PM, MauMau wrote: > > Hello, > > > > My customers and colleagues sometimes (or often?) ask about the > > following message: > > > > FATAL: the database system is starting up > > I would LOVE that message to do a

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Claudio Freire
On Tue, Dec 10, 2013 at 12:13 AM, Mark Kirkwood wrote: > Just one more... > > The Intel 520 with ext4: > > > Without patch: ANALYZE pgbench_accounts 5s > With patch: ANALYZE pgbench_accounts 1s > > And double checking - > With patch, but effective_io_concurrency = 1: ANALYZE pgbench_accounts 5s

Re: [HACKERS] Time-Delayed Standbys

2013-12-09 Thread KONDO Mitsumasa
(2013/12/09 20:29), Andres Freund wrote: On 2013-12-09 19:51:01 +0900, KONDO Mitsumasa wrote: Add my comment. We have to consider three situations. 1. PITR 2. replication standby 3. replication standby with restore_command I think this patch cannot delay in 1 situation. Why? I have three r

Re: [HACKERS] ANALYZE sampling is too good

2013-12-09 Thread Mark Kirkwood
On 10/12/13 17:18, Claudio Freire wrote: On Tue, Dec 10, 2013 at 12:13 AM, Mark Kirkwood wrote: Just one more... The Intel 520 with ext4: Without patch: ANALYZE pgbench_accounts 5s With patch: ANALYZE pgbench_accounts 1s And double checking - With patch, but effective_io_concurrency = 1:

Re: [HACKERS] Get more from indices.

2013-12-09 Thread Kyotaro HORIGUCHI
Thank you, > > One is, you put the added code for getrelation_info() out of the block for > > the condition (info->relam == BTREE_AM_OID) (though amcanorder would be .. > By checking the following equation in build_index_paths(), the updated > version of the patch guarantees that the result of an

Re: [HACKERS] stats for network traffic WIP

2013-12-09 Thread Fujii Masao
On Tue, Dec 10, 2013 at 6:56 AM, Nigel Heron wrote: > On Sat, Dec 7, 2013 at 1:17 PM, Fujii Masao wrote: >> >> Could you share the performance numbers? I'm really concerned about >> the performance overhead caused by this patch. >> > > I've tried pgbench in select mode with small data sets to avo

Re: [HACKERS] stats for network traffic WIP

2013-12-09 Thread Atri Sharma
On Tue, Dec 10, 2013 at 10:59 AM, Fujii Masao wrote: > On Tue, Dec 10, 2013 at 6:56 AM, Nigel Heron wrote: >> On Sat, Dec 7, 2013 at 1:17 PM, Fujii Masao wrote: >>> >>> Could you share the performance numbers? I'm really concerned about >>> the performance overhead caused by this patch. >>> >> >

Re: [HACKERS] [patch] Adding EXTRA_REGRESS_OPTS to all pg_regress invocations

2013-12-09 Thread Tom Lane
Bruce Momjian writes: > OK, Christoph has provided a full set of tested patches back to 8.4. > Should I backpatch these? Peter says no, but two others say yes. It's hard to paint that as a bug fix, so I'd vote for HEAD only. regards, tom lane -- Sent via pgsql-hacker

  1   2   >