Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread KONDO Mitsumasa
(2013/12/12 7:23), Fabrízio de Royes Mello wrote: On Wed, Dec 11, 2013 at 7:47 PM, Andres Freund and...@2ndquadrant.com * hot_standby=off: Makes delay useable with wal_level=archive (and thus a lower WAL volume) * standby_mode=off: Configurations that use tools like pg_standby and

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Simon Riggs
On 12 December 2013 08:19, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: (2013/12/12 7:23), Fabrízio de Royes Mello wrote: On Wed, Dec 11, 2013 at 7:47 PM, Andres Freund and...@2ndquadrant.com * hot_standby=off: Makes delay useable with wal_level=archive (and thus a lower WAL

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Simon Riggs
On 9 December 2013 10:54, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: (2013/12/09 19:35), Pavel Stehule wrote: 2013/12/9 KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp mailto:kondo.mitsum...@lab.ntt.co.jp Hi Fabrízio, I test your v4 patch, and send your review comments.

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-12 Thread Heikki Linnakangas
What's the status of this patch? I posted my version using a quite different approach than your original patch. You did some testing of that, and ran into unrelated bugs. Have they been fixed now? Where do we go from here? Are you planning to continue based on my proof-of-concept patch,

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-12 Thread Peter Geoghegan
On Thu, Dec 12, 2013 at 1:23 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: What's the status of this patch? I posted my version using a quite different approach than your original patch. You did some testing of that, and ran into unrelated bugs. Have they been fixed now? Sorry, I

Re: [HACKERS] In-Memory Columnar Store

2013-12-12 Thread knizhnik
On 12/12/2013 11:42 AM, Pavel Stehule wrote: it is interesting idea. For me, a significant information from comparation, so we do some significantly wrong. Memory engine should be faster naturally, but I don't tkink it can be 1000x. Sorry, but I didn't fabricate this results: Below is

Re: [HACKERS] TODO: Split out pg_resetxlog output into pre- and post-sections

2013-12-12 Thread Heikki Linnakangas
On 11/29/2013 08:41 AM, Rajeev rastogi wrote: On 29 November 2013, Amit Kapila Wrote: Further Review of this patch: I have done few more cosmetic changes in your patch, please find the updated patch attached with this mail. Kindly check once whether changes are okay. Changes are fine.

Re: [HACKERS] pgbench with large scale factor

2013-12-12 Thread Tatsuo Ishii
Fabien, Included is a proposed fix for this (also fixing weired remaining part). If there's no objection, I will commit it. Looks ok, but I would consider switching to double instead of int64. Assuming you are talking about remaining sec part, I agree. Here is the revised patch.

Re: [HACKERS] SSL: better default ciphersuite

2013-12-12 Thread Marko Kreen
On Wed, Dec 11, 2013 at 10:08:44PM -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Any other opinions on this out there? All instances of other SSL-enabled servers out there, except nginx, default to some variant of DEFAULT:!LOW:... or HIGH:MEDIUM: The proposal here is

Re: [HACKERS] TODO: Split out pg_resetxlog output into pre- and post-sections

2013-12-12 Thread Rajeev rastogi
On 12 December 2013, Heikki Linnakangas Wrote: Further Review of this patch: I have done few more cosmetic changes in your patch, please find the updated patch attached with this mail. Kindly check once whether changes are okay. Changes are fine. Thanks you. I have uploaded the

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

2013-12-12 Thread Greg Stark
Bonus points if you implement a (explicit) cast to and from timestamptz :) -- greg On 11 Dec 2013 12:41, Andres Freund and...@2ndquadrant.com wrote: Hi, There's already a couple of SQL function dealing with XLogRecPtrs and the logical replication work will add a couple of more. Currently

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread KONDO Mitsumasa
(2013/12/12 18:09), Simon Riggs wrote: On 9 December 2013 10:54, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: (2013/12/09 19:35), Pavel Stehule wrote: 2013/12/9 KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp mailto:kondo.mitsum...@lab.ntt.co.jp Hi Fabrízio, I test your

Re: [HACKERS] Reference to parent query from ANY sublink

2013-12-12 Thread Dimitri Fontaine
Tom Lane t...@sss.pgh.pa.us writes: Yeah, I found myself wishing for an EXPLAIN option that would show that. It's not hard to do ... how about the attached? +1 I chose to print grouping keys for both Agg and Group nodes, and to show them unconditionally. There's some case maybe for only

Re: [HACKERS] Optimize kernel readahead using buffer access strategy

2013-12-12 Thread KONDO Mitsumasa
(2013/12/12 9:30), Claudio Freire wrote: On Wed, Dec 11, 2013 at 3:14 AM, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: enable_readahead=os|fadvise with os = on, fadvise = off Hmm. fadvise is method and is not a purpose. So I consider another idea of this GUC. Yeah, I was thinking

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Simon Riggs
On 12 December 2013 10:42, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: I agree with your request here, but I don't think negative values are the right way to implement that, at least it would not be very usable. I think that my proposal is the easiest and simplist way to solve this

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Andres Freund
On 2013-12-12 09:09:21 +, Simon Riggs wrote: * Add functionality (I propose) We can set negative number at min_standby_apply_delay. I think that this feature is for world wide replication situation. For example, master server is in Japan and slave server is in San

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

2013-12-12 Thread MauMau
Hi, Amit san, From: Amit Kapila amit.kapil...@gmail.com [elog.c] Writing the default value in this file was redundant, because event_source cannot be NULL. So change I think this change might not be safe as write_eventlog() gets called from write_stderr() which might get called before

Re: [HACKERS] autovacuum_work_mem

2013-12-12 Thread Simon Riggs
On 11 December 2013 22:23, Peter Geoghegan p...@heroku.com wrote: On Wed, Dec 11, 2013 at 1:28 PM, Simon Riggs si...@2ndquadrant.com wrote: But nobody has given a sensible answer to my questions, other than to roll out the same general points again. In practice, its a knob that does not do

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Simon Riggs
On 12 December 2013 11:05, Andres Freund and...@2ndquadrant.com wrote: My suggestion would be to add the TZ to the checkpoint record. This way all users of WAL can see the TZ of the master and act accordingly. I'll do a separate patch for that. Intuitively I'd say that might be useful - but

Re: [HACKERS] COPY table FROM STDIN doesn't show count tag

2013-12-12 Thread Rajeev rastogi
On 12th December 2013, Rajeev Rastogi Wrote: On 9th December, Amit Khandelkar wrote: 1. slashcopyissuev1.patch :- This patch fixes the \COPY issue. You have removed the if condition in this statement, mentioning that it is always true now: - if (copystream ==

Re: [HACKERS] Changeset Extraction Interfaces

2013-12-12 Thread Andres Freund
Hello Robert, On 2013-12-11 22:29:46 -0500, Robert Haas wrote: On Wed, Dec 4, 2013 at 7:15 PM, Andres Freund and...@2ndquadrant.com wrote: There's basically three major 'verbs' that can be performed on a stream, currently named (walsender names): * INIT_LOGICAL_REPLICATION name

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

2013-12-12 Thread Álvaro Hernández Tortosa
On 09/12/13 18:26, Greg Stark wrote: On Sat, Dec 7, 2013 at 3:28 AM, Álvaro Hernández Tortosa a...@nosys.es 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

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

2013-12-12 Thread Andres Freund
On 2013-12-11 08:13:18 -0500, Robert Haas wrote: On Wed, Dec 11, 2013 at 7:41 AM, Andres Freund and...@2ndquadrant.com wrote: There's already a couple of SQL function dealing with XLogRecPtrs and the logical replication work will add a couple of more. Currently each of those funtions

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

2013-12-12 Thread Álvaro Hernández Tortosa
On 09/12/13 18:00, Robert Haas wrote: On Fri, Dec 6, 2013 at 10:28 PM, Álvaro Hernández Tortosa a...@nosys.es 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

Re: [HACKERS] SSL: better default ciphersuite

2013-12-12 Thread Magnus Hagander
On Thu, Dec 12, 2013 at 11:30 AM, Marko Kreen mark...@gmail.com wrote: On Wed, Dec 11, 2013 at 10:08:44PM -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Any other opinions on this out there? All instances of other SSL-enabled servers out there, except nginx, default to

Re: [HACKERS] Optimize kernel readahead using buffer access strategy

2013-12-12 Thread Simon Riggs
On 14 November 2013 12:09, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: For your information of effect of this patch, I got results of pgbench which are in-memory-size database and out-memory-size database, and postgresql.conf settings are always used by us. It seems to improve

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

2013-12-12 Thread Simon Riggs
On 12 December 2013 12:27, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-11 08:13:18 -0500, Robert Haas wrote: On Wed, Dec 11, 2013 at 7:41 AM, Andres Freund and...@2ndquadrant.com wrote: There's already a couple of SQL function dealing with XLogRecPtrs and the logical replication

Re: [HACKERS] Optimize kernel readahead using buffer access strategy

2013-12-12 Thread Mitsumasa KONDO
2013/12/12 Simon Riggs si...@2ndquadrant.com On 14 November 2013 12:09, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: For your information of effect of this patch, I got results of pgbench which are in-memory-size database and out-memory-size database, and postgresql.conf

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Mitsumasa KONDO
2013/12/12 Simon Riggs si...@2ndquadrant.com On 12 December 2013 10:42, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: I agree with your request here, but I don't think negative values are the right way to implement that, at least it would not be very usable. I think that my

Re: [HACKERS] Optimize kernel readahead using buffer access strategy

2013-12-12 Thread Simon Riggs
On 12 December 2013 13:43, Mitsumasa KONDO kondo.mitsum...@gmail.com wrote: Your tests seem to relate to pgbench. Do we have tests on more BI related tasks? Yes, off-course! We will need another benchmark test before conclusion of this patch. What kind of benchmark do you have? I suggest

[HACKERS] pg_stat_statements shows too short COMMIT time

2013-12-12 Thread MauMau
Hello, I've found a problem with pg_stat_statements while doing some performance test. If the fix is desired and not difficult, I'm willing to address it. Could you give me any information and/or your opinions? [Problem] The time of COMMIT statements is unreasonably short.

Re: [HACKERS] SSL: better default ciphersuite

2013-12-12 Thread Marko Kreen
On Thu, Dec 12, 2013 at 01:33:57PM +0100, Magnus Hagander wrote: On Thu, Dec 12, 2013 at 11:30 AM, Marko Kreen mark...@gmail.com wrote: On Wed, Dec 11, 2013 at 10:08:44PM -0500, Tom Lane wrote: I know that SChannel SSL library in Windows XP (and earlier) is such RC4+3DES only

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Florian Pflug
Here's an analysis of Jeff Janes' simple example of a table where our n_distinct estimate is way off. On Dec11, 2013, at 00:03 , Jeff Janes jeff.ja...@gmail.com wrote: create table baz as select floor(random()*1000), md5(random()::text) from generate_series(1,1); create table baz2

Re: [HACKERS] Reference to parent query from ANY sublink

2013-12-12 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Hm, that means there's only one grouping column (and it's the second tlist entry of the child plan node).  So that seems conclusive that the unique-ification is being done wrong. Further confirmation using the EXPLAIN patch with Antonin's v2 patch against

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: On 12 December 2013 11:05, Andres Freund and...@2ndquadrant.com wrote: My suggestion would be to add the TZ to the checkpoint record. This way all users of WAL can see the TZ of the master and act accordingly. I'll do a separate patch for that.

Re: [HACKERS] Changeset Extraction Interfaces

2013-12-12 Thread Robert Haas
On Thu, Dec 12, 2013 at 7:04 AM, Andres Freund and...@2ndquadrant.com wrote: I think there'll always be a bit of a difference between slots for physical and logical data, even if 90% of the implementation is the same. We can signal that difference by specifying logical/physical as an option or

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Robert Haas
On Thu, Dec 12, 2013 at 9:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On 12 December 2013 11:05, Andres Freund and...@2ndquadrant.com wrote: My suggestion would be to add the TZ to the checkpoint record. This way all users of WAL can see the TZ of the

Re: [HACKERS] In-Memory Columnar Store

2013-12-12 Thread Merlin Moncure
On Thu, Dec 12, 2013 at 4:02 AM, knizhnik knizh...@garret.ru wrote: On 12/12/2013 11:42 AM, Pavel Stehule wrote: it is interesting idea. For me, a significant information from comparation, so we do some significantly wrong. Memory engine should be faster naturally, but I don't tkink it can be

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

2013-12-12 Thread Robert Haas
On Thu, Dec 12, 2013 at 8:20 AM, Simon Riggs si...@2ndquadrant.com wrote: On 12 December 2013 12:27, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-11 08:13:18 -0500, Robert Haas wrote: On Wed, Dec 11, 2013 at 7:41 AM, Andres Freund and...@2ndquadrant.com wrote: There's already a

Re: [HACKERS] Reference to parent query from ANY sublink

2013-12-12 Thread Tom Lane
Kevin Grittner kgri...@ymail.com writes: Further confirmation using the EXPLAIN patch with Antonin's v2 patch against the table before any EXPLAIN or ANALYZE:  Hash Join  (cost=37.12..80.40 rows=442 width=12)    Hash Cond: (((upper.f2)::double precision = lower.f3) AND (upper.f1 =

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Simon Riggs
On 12 December 2013 15:03, Robert Haas robertmh...@gmail.com wrote: On Thu, Dec 12, 2013 at 9:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On 12 December 2013 11:05, Andres Freund and...@2ndquadrant.com wrote: My suggestion would be to add the TZ to the

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

2013-12-12 Thread Robert Haas
On Thu, Dec 12, 2013 at 12:27 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, Dec 12, 2013 at 3:43 AM, Peter Eisentraut pete...@gmx.net wrote: This patch fails the regression tests; see attachment. Thanks for reporting the diffs. The reason for failures is that still decoding for

Re: [HACKERS] Reference to parent query from ANY sublink

2013-12-12 Thread Tom Lane
I wrote: That's about what I thought: it's unique-ifying according to the original semijoin qual, without realizing that the pulled-up clause from the lower WHERE would need to be treated as part of the semijoin qual. This isn't a bug in the existing code, because the case can never arise,

[HACKERS] typo: XIDs are actually compared using modulo-2^32 arithmetic

2013-12-12 Thread Gianni Ciolli
Hi, It seems there is a typo here: http://www.postgresql.org/docs/devel/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND where we say that we compare XIDs using arithmetic modulo 2^31, which should instead be 2^32 (as it is with uint32, e.g. xid_age). Best wishes, Dr. Gianni Ciolli -

Re: [HACKERS] Changeset Extraction Interfaces

2013-12-12 Thread Andres Freund
On 2013-12-12 10:01:21 -0500, Robert Haas wrote: On Thu, Dec 12, 2013 at 7:04 AM, Andres Freund and...@2ndquadrant.com wrote: I think there'll always be a bit of a difference between slots for physical and logical data, even if 90% of the implementation is the same. We can signal that

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

2013-12-12 Thread Alvaro Herrera
Andres Freund wrote: On 2013-12-11 22:08:41 -0300, Alvaro Herrera wrote: Andres Freund wrote: I worry that all these multixact accesses will create huge performance problems due to the inefficiency of the multixactid cache. If you scan a huge table there very well might be millions of

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

2013-12-12 Thread Alvaro Herrera
Robert Haas escribió: I am happy to have my old patch resurrected - could become a trend. But someone should probably go back and check who objected and for what reasons. Here it is FWIW:

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

2013-12-12 Thread Alexander Korotkov
On Tue, Dec 10, 2013 at 12:26 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 12/09/2013 11:34 AM, Alexander Korotkov wrote: On Mon, Dec 9, 2013 at 1:18 PM, Heikki Linnakangas hlinnakan...@vmware.comwrote: Even if we use varbyte encoding, I wonder if it would be better to treat

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

2013-12-12 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes: Robert Haas escribió: I am happy to have my old patch resurrected - could become a trend. But someone should probably go back and check who objected and for what reasons. Here it is FWIW:

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Jeff Janes
On Wed, Dec 11, 2013 at 2:33 PM, Greg Stark st...@mit.edu wrote: I think we're all wet here. I don't see any bias towards larger or smaller rows. Larger tied will be on a larger number of pages but there will be fewer of them on any one page. The average effect should be the same. Smaller

[HACKERS] patch: make_timestamp function

2013-12-12 Thread Pavel Stehule
Hello this patch try to complete a set of functions make_date and make_timestamp. Regards Pavel commit a1344a0624f87438e2a12c0a7263a0e6dc9a1a30 Author: Pavel Stehule pavel.steh...@gooddata.com Date: Thu Dec 12 18:08:47 2013 +0100 initial implementation make_timestamp diff --git

Re: [HACKERS] Changeset Extraction Interfaces

2013-12-12 Thread Robert Haas
On Thu, Dec 12, 2013 at 10:49 AM, Andres Freund and...@2ndquadrant.com wrote: I hadn't realized that the options were going to be different for logical vs. physical. I don't see how we could avoid that, there just are some differences between both. Right, I'm not complaining, just observing

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

2013-12-12 Thread Andres Freund
Hi, On 2013-12-12 11:55:51 -0500, Tom Lane wrote: I'm not, however, terribly thrilled with the suggestions to add implicit casts associated with this type. Implicit casts are generally dangerous. It's a tradeof. Currently we have the following functions returning LSNs as text: *

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Simon Riggs
On 12 December 2013 15:19, Simon Riggs si...@2ndquadrant.com wrote: Don't panic guys! I meant UTC offset only. And yes, it may not be needed, will check. Checked, all non-UTC TZ offsets work without further effort here. -- Simon Riggs http://www.2ndQuadrant.com/

Re: [HACKERS] typo: XIDs are actually compared using modulo-2^32 arithmetic

2013-12-12 Thread Tom Lane
Gianni Ciolli gianni.cio...@2ndquadrant.it writes: It seems there is a typo here: http://www.postgresql.org/docs/devel/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND where we say that we compare XIDs using arithmetic modulo 2^31, which should instead be 2^32 (as it is with uint32,

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Fabrízio de Royes Mello
On Thu, Dec 12, 2013 at 3:39 PM, Simon Riggs si...@2ndquadrant.com wrote: On 12 December 2013 15:19, Simon Riggs si...@2ndquadrant.com wrote: Don't panic guys! I meant UTC offset only. And yes, it may not be needed, will check. Checked, all non-UTC TZ offsets work without further effort

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Jeff Janes
On Thu, Dec 12, 2013 at 6:39 AM, Florian Pflug f...@phlo.org wrote: Here's an analysis of Jeff Janes' simple example of a table where our n_distinct estimate is way off. On Dec11, 2013, at 00:03 , Jeff Janes jeff.ja...@gmail.com wrote: create table baz as select floor(random()*1000),

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

2013-12-12 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2013-12-12 11:55:51 -0500, Tom Lane wrote: I'm not, however, terribly thrilled with the suggestions to add implicit casts associated with this type. Implicit casts are generally dangerous. It's a tradeof. Currently we have the following

Re: [HACKERS] In-Memory Columnar Store

2013-12-12 Thread knizhnik
On 12/12/2013 07:03 PM, Merlin Moncure wrote: On Thu, Dec 12, 2013 at 4:02 AM, knizhnik knizh...@garret.ru wrote: Yeah. It's not fair to compare vs an implementation that is constrained to use only 1mb. For analytics work huge work mem is pretty typical setting. 10x improvement is believable

Re: [HACKERS] In-Memory Columnar Store

2013-12-12 Thread Merlin Moncure
On Thu, Dec 12, 2013 at 12:18 PM, knizhnik knizh...@garret.ru wrote: IMHO it is strange to see such small default values in postgresql configuration. This (low default work mem) is because of three things: 1) Most queries do not really need a lot of work mem 2) Work mem stacks with each query

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: It would be relatively easy to fix this if we trusted the number of visible rows in each block to be fairly constant. But without that assumption, I don't see a way to fix the sample selection process without reading the entire table. Yeah, varying

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Claudio Freire
On Thu, Dec 12, 2013 at 3:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Janes jeff.ja...@gmail.com writes: It would be relatively easy to fix this if we trusted the number of visible rows in each block to be fairly constant. But without that assumption, I don't see a way to fix the sample

Re: [HACKERS] Changeset Extraction Interfaces

2013-12-12 Thread Andres Freund
On 2013-12-12 12:13:24 -0500, Robert Haas wrote: On Thu, Dec 12, 2013 at 10:49 AM, Andres Freund and...@2ndquadrant.com wrote: If we were to start out streaming changes before the last running transaction has finished, they would be visible in that exported snapshot and you couldn't use

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Josh Berkus
On 12/12/2013 10:33 AM, Claudio Freire wrote: Well, why not take a supersample containing all visible tuples from N selected blocks, and do bootstrapping over it, with subsamples of M independent rows each? Well, we still need to look at each individual block to determine grouping correlation.

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Claudio Freire
On Thu, Dec 12, 2013 at 3:56 PM, Josh Berkus j...@agliodbs.com wrote: Estimated grouping should, however, affect MCVs. In cases where we estimate that grouping levels are high, the expected % of observed values should be discounted somehow. That is, with total random distribution you have a

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Jeff Janes
On Thu, Dec 12, 2013 at 10:33 AM, Claudio Freire klaussfre...@gmail.comwrote: On Thu, Dec 12, 2013 at 3:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Janes jeff.ja...@gmail.com writes: It would be relatively easy to fix this if we trusted the number of visible rows in each block to be

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Claudio Freire
On Thu, Dec 12, 2013 at 4:13 PM, Jeff Janes jeff.ja...@gmail.com wrote: Well, why not take a supersample containing all visible tuples from N selected blocks, and do bootstrapping over it, with subsamples of M independent rows each? Bootstrapping methods generally do not work well when ties

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Jeff Janes
On Tue, Dec 3, 2013 at 3:30 PM, Greg Stark st...@mit.edu wrote: At multiple conferences I've heard about people trying all sorts of gymnastics to avoid ANALYZE which they expect to take too long and consume too much I/O. This is especially a big complain after upgrades when their new database

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

2013-12-12 Thread Alvaro Herrera
Andres Freund wrote: On 2013-12-11 22:08:41 -0300, Alvaro Herrera wrote: Andres Freund wrote: I worry that this MultiXactIdSetOldestMember() will be problematic in longrunning vacuums like a anti-wraparound vacuum of a huge table. There's no real need to set

Re: [HACKERS] Extra functionality to createuser

2013-12-12 Thread Christopher Browne
On Wed, Dec 11, 2013 at 7:53 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 10, 2013 at 9:55 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Dec 10, 2013 at 12:20 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Dec 7, 2013 at 11:39 PM, Amit Kapila amit.kapil...@gmail.com

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

2013-12-12 Thread Alvaro Herrera
Alvaro Herrera wrote: One last thing (I hope). It's not real easy to disable this check, because it actually lives in GetNewMultiXactId. It would uglify the API a lot if we were to pass a flag down two layers of routines; and moving it to higher-level routines doesn't seem all that

[HACKERS] stuck spinlock

2013-12-12 Thread Christophe Pettus
Greetings, Immediately after an upgrade from 9.3.1 to 9.3.2, we have a client getting frequent (hourly) errors of the form: /var/lib/postgresql/9.3/main/pg_log/postgresql-2013-12-12_211710.csv:2013-12-12 21:40:10.328 UTC,n,n,32376,10.2.1.142:52451,52aa24eb.7e78,5,SELECT,2013-12-12 21:04:43

Re: [HACKERS] Time-Delayed Standbys

2013-12-12 Thread Fabrízio de Royes Mello
On Thu, Dec 12, 2013 at 3:42 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Thu, Dec 12, 2013 at 3:39 PM, Simon Riggs si...@2ndquadrant.com wrote: On 12 December 2013 15:19, Simon Riggs si...@2ndquadrant.com wrote: Don't panic guys! I meant UTC offset only. And yes, it

Re: [HACKERS] ANALYZE sampling is too good

2013-12-12 Thread Florian Pflug
On Dec12, 2013, at 19:29 , Tom Lane t...@sss.pgh.pa.us wrote: However ... where this thread started was not about trying to reduce the remaining statistical imperfections in our existing sampling method. It was about whether we could reduce the number of pages read for an acceptable cost in

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

2013-12-12 Thread Andres Freund
Hi, On 2013-12-12 18:39:43 -0300, Alvaro Herrera wrote: Alvaro Herrera wrote: One last thing (I hope). It's not real easy to disable this check, because it actually lives in GetNewMultiXactId. It would uglify the API a lot if we were to pass a flag down two layers of routines; and moving

Re: [HACKERS] patch: make_timestamp function

2013-12-12 Thread Fabrízio de Royes Mello
On Thu, Dec 12, 2013 at 3:11 PM, Pavel Stehule pavel.steh...@gmail.comwrote: Hello this patch try to complete a set of functions make_date and make_timestamp. Could we have the 'make_timestamptz' function too? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira:

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

2013-12-12 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: Unfortunately I find that too ugly. Adding a flag in the procarray because of an Assert() in a lowlevel routine? That's overkill. If this flag doesn't need to be visible to other backends, it absolutely does not belong in the procarray.

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

2013-12-12 Thread Andres Freund
On 2013-12-12 18:24:34 -0300, Alvaro Herrera wrote: + /* + * It's an update; should we keep it? If the transaction is known + * aborted then it's okay to ignore it, otherwise not. (Note this + * is just an

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Tom Lane
Christophe Pettus x...@thebuild.com writes: Immediately after an upgrade from 9.3.1 to 9.3.2, we have a client getting frequent (hourly) errors of the form: /var/lib/postgresql/9.3/main/pg_log/postgresql-2013-12-12_211710.csv:2013-12-12 21:40:10.328

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Andres Freund
On 2013-12-12 13:50:06 -0800, Christophe Pettus wrote: Immediately after an upgrade from 9.3.1 to 9.3.2, we have a client getting frequent (hourly) errors of the form: /var/lib/postgresql/9.3/main/pg_log/postgresql-2013-12-12_211710.csv:2013-12-12 21:40:10.328

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Peter Geoghegan
On Thu, Dec 12, 2013 at 3:33 PM, Andres Freund and...@2ndquadrant.com wrote: Any other changes but the upgrade? Maybe a different compiler version? Show pg_config output. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Christophe Pettus
On Dec 12, 2013, at 3:37 PM, Peter Geoghegan p...@heroku.com wrote: Show pg_config output. Below; it's the Ubuntu package. BINDIR = /usr/lib/postgresql/9.3/bin DOCDIR = /usr/share/doc/postgresql-doc-9.3 HTMLDIR = /usr/share/doc/postgresql-doc-9.3 INCLUDEDIR = /usr/include/postgresql

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Christophe Pettus
On Dec 12, 2013, at 3:33 PM, Andres Freund and...@2ndquadrant.com wrote: Any other changes but the upgrade? Maybe a different compiler version? Just the upgrade; they're using the Ubuntu packages from apt.postgresql.org. Also, could you share some details about the workload? Highly

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Christophe Pettus
On Dec 12, 2013, at 3:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Hm, a PANIC really ought to result in a core file. You sure you don't have that disabled (perhaps via a ulimit setting)? Since it's using the Ubuntu packaging, we have pg_ctl_options = '-c' in

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Tom Lane
Christophe Pettus x...@thebuild.com writes: On Dec 12, 2013, at 3:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Hm, a PANIC really ought to result in a core file. You sure you don't have that disabled (perhaps via a ulimit setting)? Since it's using the Ubuntu packaging, we have pg_ctl_options =

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Christophe Pettus
On Dec 12, 2013, at 4:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: If you aren't getting a core file for a PANIC, then core files are disabled. And just like that, we get one. Stack trace: #0 0x7f699a4fa425 in raise () from /lib/x86_64-linux-gnu/libc.so.6 (gdb) bt #0 0x7f699a4fa425 in

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Andres Freund
On 2013-12-12 16:22:28 -0800, Christophe Pettus wrote: On Dec 12, 2013, at 4:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: If you aren't getting a core file for a PANIC, then core files are disabled. And just like that, we get one. Stack trace: #0 0x7f699a4fa425 in raise () from

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Christophe Pettus
On Dec 12, 2013, at 4:23 PM, Andres Freund and...@2ndquadrant.com wrote: Could you install the -dbg package and regenerate? Of course! #0 0x7f699a4fa425 in raise () from /lib/x86_64-linux-gnu/libc.so.6 #1 0x7f699a4fdb8b in abort () from /lib/x86_64-linux-gnu/libc.so.6 #2

Re: [HACKERS] ruleutils vs. empty targetlists

2013-12-12 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane escribió: What I'm thinking about this today is that really the *right* solution is to allow syntactically-empty SELECT lists; once we've bought into the notion of zero-column tables, the notion that you can't have an empty select list

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Christophe Pettus
On Dec 12, 2013, at 4:23 PM, Andres Freund and...@2ndquadrant.com wrote: Could you install the -dbg package and regenerate? Here's another, same system, different crash: #0 0x7fa03faf5425 in raise () from /lib/x86_64-linux-gnu/libc.so.6 #1 0x7fa03faf8b8b in abort () from

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

2013-12-12 Thread Alvaro Herrera
Andres Freund wrote: On 2013-12-12 18:24:34 -0300, Alvaro Herrera wrote: + /* +* It's an update; should we keep it? If the transaction is known +* aborted then it's okay to ignore it, otherwise not. (Note this +

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Tom Lane
Christophe Pettus x...@thebuild.com writes: On Dec 12, 2013, at 4:23 PM, Andres Freund and...@2ndquadrant.com wrote: Could you install the -dbg package and regenerate? Here's another, same system, different crash: Both of these look like absolutely run-of-the-mill buffer access attempts.

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Christophe Pettus
On Dec 12, 2013, at 5:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Presumably, we are seeing the victim rather than the perpetrator of whatever is going wrong. This is probing about a bit blindly, but the only thing I can see about this system that is in some way unique (and this is happening

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Tom Lane
Christophe Pettus x...@thebuild.com writes: On Dec 12, 2013, at 5:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Presumably, we are seeing the victim rather than the perpetrator of whatever is going wrong. This is probing about a bit blindly, but the only thing I can see about this system that

Re: [HACKERS] SSL: better default ciphersuite

2013-12-12 Thread Peter Eisentraut
On Thu, 2013-12-12 at 12:30 +0200, Marko Kreen wrote: First, if there is explicit wish to keep RC4/SEED in play, I'm fine with HIGH:MEDIUM:!aNULL as new default. Clarity-wise, it's still much better than current value. And this value will result *exactly* same list in same order as current

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Christophe Pettus
On Dec 12, 2013, at 6:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Are you possibly using any nonstandard extensions? No, totally stock PostgreSQL. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Andres Freund
Hi, On 2013-12-12 13:50:06 -0800, Christophe Pettus wrote: Immediately after an upgrade from 9.3.1 to 9.3.2, we have a client getting frequent (hourly) errors of the form: Is it really a regular pattern like hourly? What's your checkpoint_segments? Could you, arround the time of a crash,

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Peter Geoghegan
On Thu, Dec 12, 2013 at 5:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Memo to hackers: I think the SIGSTOP stuff is rather obsolete now that most systems dump core files with process IDs embedded in the names. What would be more useful today is an option to send SIGABRT, or some other signal

Re: [HACKERS] stuck spinlock

2013-12-12 Thread Andres Freund
On 2013-12-12 21:15:29 -0500, Tom Lane wrote: Christophe Pettus x...@thebuild.com writes: On Dec 12, 2013, at 5:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Presumably, we are seeing the victim rather than the perpetrator of whatever is going wrong. This is probing about a bit blindly, but

Re: [HACKERS] Why standby.max_connections must be higher than primary.max_connections?

2013-12-12 Thread satoshi yamada
Because the KnownAssignedXIDs and lock tables on the standby need to be large enough to contain the largest snapshot and greatest number of AccessExclusiveLocks that could exist on the master at any given time. Right. Initially during the development of Hot Standby, it looked like the

  1   2   >