Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Daniel Farina
On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake j...@commandprompt.com wrote: On 6/5/2013 10:07 PM, Daniel Farina wrote: If I told you there were some of us who would prefer to attenuate the rate that things get written rather than cancel or delay archiving for a long period of time, would

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Joshua D. Drake
On 6/5/2013 10:54 PM, Peter Geoghegan wrote: On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake j...@commandprompt.com wrote: I just wonder if we are looking in the right place (outside of some obvious badness like the PANIC running out of disk space). So you don't think we should PANIC on

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Daniel Farina
On Wed, Jun 5, 2013 at 11:05 PM, Joshua D. Drake j...@commandprompt.com wrote: On 6/5/2013 10:54 PM, Peter Geoghegan wrote: On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake j...@commandprompt.com wrote: I just wonder if we are looking in the right place (outside of some obvious badness

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Harold Giménez
Hi, On Wed, Jun 5, 2013 at 11:05 PM, Joshua D. Drake j...@commandprompt.comwrote: On 6/5/2013 10:54 PM, Peter Geoghegan wrote: On Wed, Jun 5, 2013 at 10:27 PM, Joshua D. Drake j...@commandprompt.com wrote: Instead of running out of disk space PANIC we should just write to an emergency

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Joshua D. Drake
On 6/5/2013 11:09 PM, Daniel Farina wrote: Instead of running out of disk space PANIC we should just write to an emergency location within PGDATA and log very loudly that the SA isn't paying attention. Perhaps if that area starts to get to an unhappy place we immediately bounce into read-only

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Peter Geoghegan
On Wed, Jun 5, 2013 at 11:28 PM, Joshua D. Drake j...@commandprompt.com wrote: I have zero doubt that in your case it is true and desirable. I just don't know that it is a positive solution to the problem as a whole. Your case is rather limited to your environment, which is rather limited to

Re: [HACKERS] Move unused buffers to freelist

2013-06-06 Thread Amit Kapila
On Tuesday, May 28, 2013 6:54 PM Robert Haas wrote: Instead, I suggest modifying BgBufferSync, specifically this part right here: else if (buffer_state BUF_REUSABLE) reusable_buffers++; What I would suggest is that if the BUF_REUSABLE flag is set here, use

Re: [HACKERS] how to find out whether a view is updatable

2013-06-06 Thread Dean Rasheed
On 5 June 2013 08:59, Dean Rasheed dean.a.rash...@gmail.com wrote: I'm still not happy with pg_view_is_updatable() et al. and the information_schema views. I accept that the information_schema views have to be the way they are because that's what's defined in the standard, but as it stands,

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Joshua D. Drake
On 6/5/2013 11:25 PM, Harold Giménez wrote: Instead of running out of disk space PANIC we should just write to an emergency location within PGDATA This merely buys you some time, but with aggressive and sustained write throughput you are left on the same spot. Practically speaking

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Joshua D. Drake
On 6/5/2013 11:31 PM, Peter Geoghegan wrote: On Wed, Jun 5, 2013 at 11:28 PM, Joshua D. Drake j...@commandprompt.com wrote: I have zero doubt that in your case it is true and desirable. I just don't know that it is a positive solution to the problem as a whole. Your case is rather limited to

Re: [HACKERS] Make targets of doc links used by phpPgAdmin static

2013-06-06 Thread Magnus Hagander
On Jun 6, 2013 4:14 AM, Peter Eisentraut pete...@gmx.net wrote: On Tue, 2013-06-04 at 22:27 -0500, Karl O. Pinc wrote: On 06/04/2013 10:16:20 PM, Peter Eisentraut wrote: On Tue, 2013-05-07 at 23:18 -0400, Alvaro Herrera wrote: Peter Eisentraut wrote: On Tue, 2013-05-07 at 00:32

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Heikki Linnakangas
On 06.06.2013 06:20, Joshua D. Drake wrote: 3. The spread checkpoints have always confused me. If anything we want a checkpoint to be fast and short because: (I'm sure you know this, but:) If you perform a checkpoint as fast and short as possible, the sudden burst of writes and fsyncs will

Re: [HACKERS] MVCC catalog access

2013-06-06 Thread Andres Freund
On 2013-06-05 18:56:28 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: Now, I did find a couple that I thought should probably stick with SnapshotNow, specifically pgrowlocks and pgstattuple. Those are just gathering statistical information, so there's no harm in having the

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Heikki Linnakangas
On 05.06.2013 23:16, Josh Berkus wrote: For limiting the time required to recover after crash, checkpoint_segments is awkward because it's difficult to calculate how long recovery will take, given checkpoint_segments=X. A bulk load can use up segments really fast, and recovery will be fast,

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Joshua D. Drake
On 6/6/2013 1:11 AM, Heikki Linnakangas wrote: (I'm sure you know this, but:) If you perform a checkpoint as fast and short as possible, the sudden burst of writes and fsyncs will overwhelm the I/O subsystem, and slow down queries. That's what we saw before spread checkpoints: when a

Re: [HACKERS] MVCC catalog access

2013-06-06 Thread Andres Freund
Hi Robert, Took a quick look through the patch to understand what your current revision is actually doing and to facilitate thinking about possible pain points. Here are the notes I made during my reading: On 2013-06-03 14:57:12 -0400, Robert Haas wrote: +++ b/src/backend/catalog/catalog.c @@

Re: [HACKERS] About large objects asynchronous and non-blocking support

2013-06-06 Thread Dmitriy Igrishin
2013/6/6 Tatsuo Ishii is...@postgresql.org Hi. At the moment libpq doesn't seem to support asynchronous and non-blocking support for large objects, in the style of PQsendQuery/PQgetResult. This makes large objects hardly suited for single-threaded programs based on some variant of

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Heikki Linnakangas
On 06.06.2013 11:42, Joshua D. Drake wrote: On 6/6/2013 1:11 AM, Heikki Linnakangas wrote: Yes checkpoint_segments is awkward. We shouldn't have to set it at all. It should be gone. The point of having checkpoint_segments or max_wal_size is to put a limit (albeit a soft one) on the amount of

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Heikki Linnakangas
On 05.06.2013 22:18, Kevin Grittner wrote: Heikki Linnakangashlinnakan...@vmware.com wrote: I was not thinking of making it a hard limit. It would be just like checkpoint_segments from that point of view - if a checkpoint takes a long time, max_wal_size might still be exceeded. Then I

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Heikki Linnakangas
On 05.06.2013 22:24, Fujii Masao wrote: On Thu, Jun 6, 2013 at 3:35 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: The checkpoint spreading code already tracks if the checkpoint is on schedule, and it takes into account both checkpoint_timeout and checkpoint_segments. Ie. if you consume

Re: [HACKERS] Freezing without write I/O

2013-06-06 Thread Greg Stark
On Fri, May 31, 2013 at 3:04 AM, Robert Haas robertmh...@gmail.com wrote: Even at a more modest 10,000 tps, with default settings, you'll do anti-wraparound vacuums of the entire cluster about every 8 hours. That's not fun. I've forgotten now. What happens if you have a long-lived transaction

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Kevin Grittner
Heikki Linnakangas hlinnakan...@vmware.com wrote: On 05.06.2013 22:18, Kevin Grittner wrote: Heikki Linnakangashlinnakan...@vmware.com  wrote: I was not thinking of making it a hard limit. It would be just like checkpoint_segments from that point of view - if a checkpoint takes a long time,

Re: [HACKERS] Freezing without write I/O

2013-06-06 Thread Heikki Linnakangas
On 06.06.2013 15:16, Greg Stark wrote: On Fri, May 31, 2013 at 3:04 AM, Robert Haasrobertmh...@gmail.com wrote: Even at a more modest 10,000 tps, with default settings, you'll do anti-wraparound vacuums of the entire cluster about every 8 hours. That's not fun. I've forgotten now. What

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Heikki Linnakangas
On 06.06.2013 15:31, Kevin Grittner wrote: Heikki Linnakangashlinnakan...@vmware.com wrote: On 05.06.2013 22:18, Kevin Grittner wrote: Heikki Linnakangashlinnakan...@vmware.com wrote: I was not thinking of making it a hard limit. It would be just like checkpoint_segments from that point

Re: [HACKERS] Make targets of doc links used by phpPgAdmin static

2013-06-06 Thread Karl O. Pinc
On 06/05/2013 09:13:45 PM, Peter Eisentraut wrote: On Tue, 2013-06-04 at 22:27 -0500, Karl O. Pinc wrote: On 06/04/2013 10:16:20 PM, Peter Eisentraut wrote: On Tue, 2013-05-07 at 23:18 -0400, Alvaro Herrera wrote: Peter Eisentraut wrote: On Tue, 2013-05-07 at 00:32 -0500, Karl O.

Re: [HACKERS] Partitioning performance: cache stringToNode() of pg_constraint.ccbin

2013-06-06 Thread Amit Kapila
On Tuesday, June 04, 2013 12:37 AM Noah Misch wrote: A colleague, Korry Douglas, observed a table partitioning scenario where deserializing pg_constraint.ccbin is a hot spot. The following test case, a simplification of a typical partitioning setup, spends 28% of its time in stringToNode()

[HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Heikki Linnakangas
In the Redesigning checkpoint_segments thread, many people opined that there should be a hard limit on the amount of disk space used for WAL: http://www.postgresql.org/message-id/CA+TgmoaOkgZb5YsmQeMg8ZVqWMtR=6s4-ppd+6jiy4oq78i...@mail.gmail.com. I'm starting a new thread on that, because

[HACKERS] pg_ugprade use of --check and --link

2013-06-06 Thread Bruce Momjian
In a private bug report, I have realized that if you are eventually going to be using link mode with pg_upgrade, and you run --check mode, you should use --link with --check to check that both clusters are on the same file system. I have documented this with the attached, applied patch, and

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Andres Freund
On 2013-06-06 17:00:30 +0300, Heikki Linnakangas wrote: A more workable idea is to sprinkle checks in higher-level code, before you hold any critical locks, to check that there is enough preallocated WAL. Like, at the beginning of heap_insert, heap_update, etc., and all similar indexam entry

Re: [HACKERS] all_visible replay aborting due to uninitialized pages

2013-06-06 Thread Robert Haas
On Thu, May 30, 2013 at 2:29 AM, Andres Freund and...@2ndquadrant.com wrote: Yeah, I think it's fine. The patch also looks fine, although I think the comments could use a bit of tidying. I guess we need to back-patch this all the way back to 8.4? It will require some adjustments for the

Re: [HACKERS] pg_ugprade use of --check and --link

2013-06-06 Thread Alvaro Herrera
Bruce Momjian wrote: In a private bug report, I have realized that if you are eventually going to be using link mode with pg_upgrade, and you run --check mode, you should use --link with --check to check that both clusters are on the same file system. Would it make sense to run the filesystem

Re: [HACKERS] all_visible replay aborting due to uninitialized pages

2013-06-06 Thread Andres Freund
On 2013-06-06 10:22:14 -0400, Robert Haas wrote: On Thu, May 30, 2013 at 2:29 AM, Andres Freund and...@2ndquadrant.com wrote: Yeah, I think it's fine. The patch also looks fine, although I think the comments could use a bit of tidying. I guess we need to back-patch this all the way back

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Heikki Linnakangas
On 06.06.2013 17:17, Andres Freund wrote: On 2013-06-06 17:00:30 +0300, Heikki Linnakangas wrote: A more workable idea is to sprinkle checks in higher-level code, before you hold any critical locks, to check that there is enough preallocated WAL. Like, at the beginning of heap_insert,

[HACKERS] Statement timeout logging

2013-06-06 Thread Thom Brown
Hi, When a statement is cancelled due to it running for long enough for statement_timeout to take effect, it logs a message: ERROR: canceling statement due to statement timeout However, it doesn't log what the timeout was at the time of the cancellation. This may be set in postgresql.conf,

Re: [HACKERS] Processing long AND/OR lists

2013-06-06 Thread Gurjeet Singh
On Mon, May 27, 2013 at 10:32 AM, Christopher Browne cbbro...@gmail.comwrote: On Mon, May 27, 2013 at 1:42 AM, Gurjeet Singh gurj...@singh.im wrote: Joking about 640K aside, it doesn't seem reasonable to expect a truly enormous query as is generated by the broken forms of this logic to turn

Re: [HACKERS] RFC: ExecNodeExtender

2013-06-06 Thread Robert Haas
On Tue, Jun 4, 2013 at 2:50 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Also, I don't think ExecNodeExtender is not a good naming, because it is a bit long and abbreviation (ENE?) is hard to imagine the feature. Please give this feature a cool and well understandable name. I agree that

Re: [HACKERS] Statement timeout logging

2013-06-06 Thread Pavel Stehule
2013/6/6 Thom Brown t...@linux.com: Hi, When a statement is cancelled due to it running for long enough for statement_timeout to take effect, it logs a message: ERROR: canceling statement due to statement timeout However, it doesn't log what the timeout was at the time of the

Re: [HACKERS] MVCC catalog access

2013-06-06 Thread Robert Haas
On Thu, Jun 6, 2013 at 5:30 AM, Andres Freund and...@2ndquadrant.com wrote: + * XXX: Now that we have MVCC catalog access, the reasoning above is no longer + * true. Are there other good reasons to hard-code this, or should we revisit + * that decision? We could just the function by

Re: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review]

2013-06-06 Thread Robert Haas
On Wed, Jun 5, 2013 at 7:24 AM, Amit Kapila amit.kap...@huawei.com wrote: On Monday, May 27, 2013 4:17 PM Amit Kapila wrote: On Wednesday, April 03, 2013 11:55 AM Amit Kapila wote: On Tuesday, April 02, 2013 9:49 PM Peter Eisentraut wrote: There are 2 options to proceed for this patch for

Re: [HACKERS] JSON and unicode surrogate pairs

2013-06-06 Thread Robert Haas
On Wed, Jun 5, 2013 at 10:46 AM, Andrew Dunstan and...@dunslane.net wrote: In 9.2, the JSON parser didn't check the validity of the use of unicode escapes other than that it required 4 hex digits to follow '\u'. In 9.3, that is still the case. However, the JSON accessor functions and operators

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Josh Berkus
Daniel, So your suggestion is that if archiving is falling behind, we should introduce delays on COMMIT in order to slow down the rate of WAL writing? Just so I'm clear. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list

[HACKERS] Multiple error reports.

2013-06-06 Thread Dmitriy Igrishin
According to http://www.postgresql.org/docs/9.2/static/libpq-async.html Even when PQresultStatus indicates a fatal error, PQgetResult should be called until it returns a null pointer, to allow libpq to process the error information completely. In libpq/fe-exec.c:PQexecFinish() error messages

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Josh Berkus
Then I suggest we not use exactly that name. I feel quite sure we would get complaints from people if something labeled as max was exceeded -- especially if they set that to the actual size of a filesystem dedicated to WAL files. You're probably right. Any suggestions for a better name?

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Jeff Janes
On Wed, Jun 5, 2013 at 8:20 PM, Joshua D. Drake j...@commandprompt.comwrote: On 06/05/2013 05:37 PM, Robert Haas wrote: - If it looks like we're going to exceed limit #3 before the checkpoint completes, we start exerting back-pressure on writers by making them wait every time they write

Re: [HACKERS] MVCC catalog access

2013-06-06 Thread Jim Nasby
On 6/5/13 3:49 PM, Robert Haas wrote: Now, I did find a couple that I thought should probably stick with SnapshotNow, specifically pgrowlocks and pgstattuple. FWIW, I've often wished for a way to make all stat access transactional, across all the stats views. Perhaps that couldn't be done by

Re: [HACKERS] [PATCH]Tablesample Submission

2013-06-06 Thread Simon Riggs
On 18 September 2012 10:32, Hitoshi Harada umi.tan...@gmail.com wrote: As wiki says, BERNOULLI relies on the statistics of the table, which doesn't sound good to me. Of course we could say this is our restriction and say good-bye to users who hadn't run ANALYZE first, but it is too hard for

Re: [HACKERS] Cost limited statements RFC

2013-06-06 Thread Jeff Janes
On Fri, May 24, 2013 at 11:51 AM, Greg Smith g...@2ndquadrant.com wrote: On 5/24/13 9:21 AM, Robert Haas wrote: But I wonder if we wouldn't be better off coming up with a little more user-friendly API. Instead of exposing a cost delay, a cost limit, and various charges, perhaps we should

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Jeff Janes
On Thu, Jun 6, 2013 at 1:42 AM, Joshua D. Drake j...@commandprompt.comwrote: I may be confused but it is my understanding that bgwriter writes out the data from the shared buffer cache that is dirty based on an interval and a max pages written. It primarily writes out based on how many

Re: [HACKERS] Cost limited statements RFC

2013-06-06 Thread Robert Haas
On Thu, Jun 6, 2013 at 3:34 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Fri, May 24, 2013 at 11:51 AM, Greg Smith g...@2ndquadrant.com wrote: On 5/24/13 9:21 AM, Robert Haas wrote: But I wonder if we wouldn't be better off coming up with a little more user-friendly API. Instead of

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Heikki Linnakangas
On 06.06.2013 20:24, Josh Berkus wrote: Yeah, something like that :-). I was thinking of letting the estimate decrease like a moving average, but react to any increases immediately. Same thing we do in bgwriter to track buffer allocations: Seems reasonable. Here's a patch implementing that.

Re: [HACKERS] Vacuum, Freeze and Analyze: the big picture

2013-06-06 Thread Jeff Janes
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote: On 06/02/2013 05:56 AM, Robert Haas wrote: (b) users making ridiculous settings changes to avoid the problems caused by anti-wraparound vacuums kicking in at inconvenient times and eating up too many resources.

Re: [HACKERS] Partitioning performance: cache stringToNode() of pg_constraint.ccbin

2013-06-06 Thread Noah Misch
On Thu, Jun 06, 2013 at 07:02:27PM +0530, Amit Kapila wrote: On Tuesday, June 04, 2013 12:37 AM Noah Misch wrote: This patch can give good performance gain in the scenario described by you. Infact I had taken the readings with patch, it shows similar gain. Thanks for testing. This patch

Re: [HACKERS] Vacuum, Freeze and Analyze: the big picture

2013-06-06 Thread Jeff Janes
On Mon, Jun 3, 2013 at 6:34 AM, Kevin Grittner kgri...@ymail.com wrote: Where I hit a nightmare scenario with an anti-wraparound autovacuum, personally, was after an upgrade using pg_dump piped to psql. At a high OLTP transaction load time (obviously the most likely time for it to kick in,

Re: [HACKERS] Cost limited statements RFC

2013-06-06 Thread Andres Freund
On 2013-06-06 12:34:01 -0700, Jeff Janes wrote: On Fri, May 24, 2013 at 11:51 AM, Greg Smith g...@2ndquadrant.com wrote: On 5/24/13 9:21 AM, Robert Haas wrote: But I wonder if we wouldn't be better off coming up with a little more user-friendly API. Instead of exposing a cost delay, a

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Christian Ullrich
* Heikki Linnakangas wrote: The current situation is that if you run out of disk space while writing WAL, you get a PANIC, and the server shuts down. That's awful. We can So we need to somehow stop new WAL insertions from happening, before it's too late. A naive idea is to check if

Re: [HACKERS] Vacuum, Freeze and Analyze: the big picture

2013-06-06 Thread Jeff Janes
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote: - I'll whack in some manual VACUUM cron jobs during low load maintenance hours and hope that keeps the worst of the problem away, that's what random forum posts on the Internet say to do. - oh my, why did my DB just do

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Andres Freund
On 2013-06-06 23:28:19 +0200, Christian Ullrich wrote: * Heikki Linnakangas wrote: The current situation is that if you run out of disk space while writing WAL, you get a PANIC, and the server shuts down. That's awful. We can So we need to somehow stop new WAL insertions from happening,

[HACKERS] SPGist triple parity concept doesn't work

2013-06-06 Thread Tom Lane
I've been looking into the problem reported at http://www.postgresql.org/message-id/519a5917.40...@qunar.com and what I find is that we have spgist insertion operations deadlocking against each other because one is descending from page A to page B while the other descends from page B to page A.

Re: [HACKERS] Freezing without write I/O

2013-06-06 Thread Greg Stark
On Thu, Jun 6, 2013 at 1:39 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: That will keep OldestXmin from advancing. Which will keep vacuum from advancing relfrozenxid/datfrozenxid. Which will first trigger the warnings about wrap-around, then stops new XIDs from being generated, and

Re: [HACKERS] SPGist triple parity concept doesn't work

2013-06-06 Thread Greg Stark
On Thu, Jun 6, 2013 at 10:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: To prevent : deadlocks we introduce a concept of triple parity of pages: if inner tuple : is on page with BlockNumber N, then its child tuples should be placed on the : same page, or else on a page with BlockNumber M where

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Greg Stark
On Thu, Jun 6, 2013 at 10:38 PM, Andres Freund and...@2ndquadrant.com wrote: That's not a bad technique. I wonder how reliable it would be in postgres. Do all filesystems allow a rename() to succeed if there isn't actually any space left? E.g. on btrfs I wouldn't be sure. We need to rename

[HACKERS] Proposal for CSN based snapshots

2013-06-06 Thread Ants Aasma
Given the recent ideas being thrown about changing how freezing and clog is handled and MVCC catalog access I thought I would write out the ideas that I have had about speeding up snapshots in case there is an interesting tie in with the current discussions. To refresh your memory the basic idea

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Josh Berkus
Given the behavior of xlog, I'd want to adjust the algo so that peak usage on a 24-hour basis would affect current preallocation. That is, if a site regularly has a peak from 2-3pm where they're using 180 segments/cycle, then they should still be somewhat higher at 2am than a database which

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Josh Berkus
Let's talk failure cases. There's actually three potential failure cases here: - One Volume: WAL is on the same volume as PGDATA, and that volume is completely out of space. - XLog Partition: WAL is on its own partition/volume, and fills it up. - Archiving: archiving is failing or too slow,

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Jaime Casanova
On Thu, Jun 6, 2013 at 4:28 PM, Christian Ullrich ch...@chrullrich.net wrote: * Heikki Linnakangas wrote: The current situation is that if you run out of disk space while writing WAL, you get a PANIC, and the server shuts down. That's awful. We can So we need to somehow stop new WAL

Re: [HACKERS] create a git symbolic-ref for REL9_3_STABLE

2013-06-06 Thread Peter Eisentraut
On Wed, 2013-06-05 at 08:43 +0800, amul sul wrote: Just want to ask, what exactly you want, is like this 1. you want to create symbolic -ref as _git symbolic-ref refs/heads/REL9_3_STABLE refs/heads/master_ 2. which will show in _git branch_ as REL9_3_STABLE - master * master 3. Then you

Re: [HACKERS] SPGist triple parity concept doesn't work

2013-06-06 Thread Tom Lane
Greg Stark st...@mit.edu writes: On Thu, Jun 6, 2013 at 10:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: : This rule guarantees that tuples on page M will have no children on page N, : since (M+1) mod 3 != N mod 3. Even if the invariant was maintained why doesn't that just mean you need three

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Greg Smith
On 6/6/13 4:42 AM, Joshua D. Drake wrote: On 6/6/2013 1:11 AM, Heikki Linnakangas wrote: (I'm sure you know this, but:) If you perform a checkpoint as fast and short as possible, the sudden burst of writes and fsyncs will overwhelm the I/O subsystem, and slow down queries. That's what we saw

Re: [HACKERS] Redesigning checkpoint_segments

2013-06-06 Thread Greg Smith
On 6/6/13 4:41 AM, Heikki Linnakangas wrote: I was thinking of letting the estimate decrease like a moving average, but react to any increases immediately. Same thing we do in bgwriter to track buffer allocations: Combine what your submitted patch does and this idea, and you'll have

Re: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review]

2013-06-06 Thread Amit Kapila
On Thursday, June 06, 2013 10:22 PM Robert Haas wrote: On Wed, Jun 5, 2013 at 7:24 AM, Amit Kapila amit.kap...@huawei.com wrote: On Monday, May 27, 2013 4:17 PM Amit Kapila wrote: On Wednesday, April 03, 2013 11:55 AM Amit Kapila wote: On Tuesday, April 02, 2013 9:49 PM Peter Eisentraut

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Jeff Janes
On Thursday, June 6, 2013, Josh Berkus wrote: Let's talk failure cases. There's actually three potential failure cases here: - One Volume: WAL is on the same volume as PGDATA, and that volume is completely out of space. - XLog Partition: WAL is on its own partition/volume, and fills it

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Joshua D. Drake
On 06/06/2013 09:30 PM, Jeff Janes wrote: Archiving - In some ways, this is the simplest case. Really, we just need a way to know when the available WAL space has become 90% full, and abort archiving at that stage. Once we stop attempting to archive, we can

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

2013-06-06 Thread Daniel Farina
On Thu, Jun 6, 2013 at 9:30 PM, Jeff Janes jeff.ja...@gmail.com wrote: I would oppose that as the solution, either an unconditional one, or configurable with is it as the default. Those segments are not unneeded. I need them. That is why I set up archiving in the first place. If you need