Re: [HACKERS] Point in Time Recovery
On Wed, 2004-07-14 at 03:31, Christopher Kings-Lynne wrote: Can you give us some suggestions of what kind of stuff to test? Is there a way we can artificially kill the backend in all sorts of nasty spots to see if recovery works? Does kill -9 simulate a 'power off'? I was hoping some fiendish plans would be presented to me... But please start with this feels like typical usage and we'll go from there...the important thing is to try the first one. I've not done power off tests, yet. They need to be done just to check...actually you don't need to do this to test PITR... We need to exhaustive tests of... - power off - scp and cross network copies - all the permuted recovery options - archive_mode = off (i.e. current behaviour) - deliberately incorrectly set options (idiot-proof testing) I'd love some help assembling a test document with numbered tests... Best regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Assisting developers
On Tue, Jul 13, 2004 at 06:29:51PM +0200, Peter Eisentraut wrote: Bruce Momjian wrote: I am not sure what can be done to solve this in the future. There are only a limited number of us who have the experience and time to review and comment on very complex patches. The issue as I see it is not reviewing patches, but defining features. You're right. The other problem is that about some features nobody wants to talk over, because a feature is out of main stream interest or almost nobody really understand a problem. In this case all start discussion if something is already done and they try use it. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is trust really a good default?
Magnus Hagander wrote: not to mention the more basic problem that the comments will now be wrong. That, however, it is correct :-( Sloppy. How about a text along the line of: CAUTION: Configuring the system for trust authentication allows any local user to connect using any PostgreSQL user name, including the superuser, over either Unix domain sockets or TCP/IP. If you are on a multiple-user machine, this is probably not good. Change it to use something other than trust authentication. Or something along that line? Since it would no longer actually be default. Or do we want something like On some installations, the default is...? Woh, I didn't think we agreed that the default would change from 'trust', only that we would now emit a warning and allow other authentication methods to be specified at initdb time. Certainly, I'm not saying it shuold change (I've given that up by now). But the difference would be that if you used -W with initdb, it would change the default *for that installation*. Initdb-with-no-parameters would stay the same to keep people who don't know about the switches happier. //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Release planning (was: Re: [HACKERS] Status report)
On 7/13/2004 10:23 PM, Christopher Kings-Lynne wrote: I was thinking of something much simpler where Jan would create an ARC patch against 7.4.X and have it either in /contrib for 7.4.X or on our ftp servers, or on a web site. I could create a mechanism so SELECT version() would display Jan's add-on. I think you guys just need to learn to become comfortable with being hard-nosed about this. Just Do Not Care about people who want ARC right this second. Do you see them calling up Oracle and saying please backport the new stuff from 11i-devel so we can use it now please? Hmmm ... I wonder what you think who those people are who want ARC right this second, and who you guys are on the other hand. To fill you in a little, I am the PostgreSQL CORE team member Jan Wieck, who burned Afilias payroll hours to implement the ARC buffer replacement strategy. The feature has been completed and fully contributed under the BSD license way ahead of any possible release schedule. I have had several requests for backports, but consider the feature too delicate to make such patch available. Don't worry, it's not one of my goals to get this into any release, the reason I am personally touched by this is not because it affects my checking account in any way. What touches me here is the fact that the PostgreSQL Open Source Project under the BSD license seems starting to care a lot more about some press releases and silly news splashes, than to care about real features contributed under the terms and conditions of the BSD license by serious members of the Open Source Community. Which part of the storage manager, that Futjitsu uses so that their customers don't need ARC, the background writer or vacuum at all, do you think will be contributed any time soon? Don't get me wrong here, I don't have a problem with someone making money out of my 8+ years of contributions to this project. But I do have a problem with those efforts getting in my way of contributing. Come again about hard-nosing, please. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Release planning
To fill you in a little, I am the PostgreSQL CORE team member Jan Wieck, who burned Afilias payroll hours to implement the ARC buffer replacement strategy. The feature has been completed and fully contributed under the BSD license way ahead of any possible release schedule. I have had several requests for backports, but consider the feature too delicate to make such patch available. Don't worry, it's not one of my goals to get this into any release, the reason I am personally touched by this is not because it affects my checking account in any way. Yes, but it has been committed, it will be released - the only thing is that people will have to wait a few more months for it. My point was exactly what you are saying. It's not worth backporting it because it is delicate, and it's not worth rushing to meet the demands of a vocal number of users if it will cost too much time in terms of developer and release engineering hours. What I was saying is that we don't need to release stuff right now when people demand it, if it is really inconvenient for us and we don't have the manpower. What touches me here is the fact that the PostgreSQL Open Source Project under the BSD license seems starting to care a lot more about some press releases and silly news splashes, than to care about real features contributed under the terms and conditions of the BSD license by serious members of the Open Source Community. There's a place for both. I do development for PostgreSQL because it's fun - however it would make me sad to see PostgreSQL as a whole wither and die because we get no press, no new users, no good reviews and everyone just uses MySQL... Also, it's a good way for people who cannot code to contribute to the project. Which part of the storage manager, that Futjitsu uses so that their customers don't need ARC, the background writer or vacuum at all, do you think will be contributed any time soon? Well, that's not possible to answer. However, they have already paid for tablespaces and nested transactions, so who am I to begrudge them their storage manager? Don't get me wrong here, I don't have a problem with someone making money out of my 8+ years of contributions to this project. But I do have a problem with those efforts getting in my way of contributing. I'm not quite sure how you've been inhibited from contributing? You've done a bunch of stuff for 7.5, that is committed and will be in release. How will press releases and news splashes hinder that? Come again about hard-nosing, please. I'm actually not sure you are disagreeing with me, Jan... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Release planning
On 7/14/2004 5:00 AM, Christopher Kings-Lynne wrote: Yes, but it has been committed, it will be released - the only thing is that people will have to wait a few more months for it. My point was Just a few more months? That is exactly what I was asking for, put some of the stuff into 7.6 so it will be released in a few more months instead of holding back the release now. Well, that's not possible to answer. However, they have already paid for tablespaces and nested transactions, so who am I to begrudge them their storage manager? Afilias has already payed for as well. Are they paying in some strange kind of different dollars or what? Don't get me wrong here, I don't have a problem with someone making money out of my 8+ years of contributions to this project. But I do have a problem with those efforts getting in my way of contributing. I'm not quite sure how you've been inhibited from contributing? You've done a bunch of stuff for 7.5, that is committed and will be in release. How will press releases and news splashes hinder that? There is again this will be released. Great, but what I don't get is why the stuff that wasn't ready at the original release schedule qualifies in your mind easily to push back, when my stuff that was ready is so unimportant that it can simply be jiggled around for a couple of months. Are nested transactions that more or an everyone needs feature than the benefits resulting from an improved cache algorithm? Come again about hard-nosing, please. I'm actually not sure you are disagreeing with me, Jan... Probably not. And as usual, I don't mean you in person, even if I would call you by name just to make my point ;-) Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Point in Time Recovery
The recovery mechanism doesn't rely upon you knowing 1 or 3. The recovery reads pg_control (from the backup) and then attempts to de-archive the appropriate xlog segment file and then starts rollforward Unfortunately this only works if pg_control was the first file to be backed up (or by chance no checkpoint happened after backup start and pg_control backup) Other db's have commands for: start/end external backup Maybe we should add those two commands that would initially only do the following: start external backup: - (checkpoint as an option) - make a copy of pg_control end external backup: - record WAL position (helps choose an allowed minimum PIT) Those commands would actually not be obligatory but recommended, and would only help with the restore process. Restore would then eighter take the existing pg_control backup, or ask the dba where rollforward should start and create a corresponding pg_control. A helper utility could list possible checkpoints in a given xlog. Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] Is trust really a good default?
On Wed, 2004-07-14 at 05:08, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: ... The point of this explanation is that as Debian maintainer I would have to disable any procedures that attempt to edit these conffiles, or at least ensure that their operation is under package control and produce only the effects that I desire. Uh, is this relevant at all? There has been no suggestion that initdb should try any harder or less hard than it does now to write $PGDATA/pg_hba.conf. All that's been discussed is what it should write there. If you are going to hack on it to enforce your opinion of what it should do, then you'll be making the same hack either way. It's just that if people are going to do things to initdb to accommodate the distributions, they need to understand the constraints. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA God is faithful, by whom ye were called unto the fellowship of his Son Jesus Christ our Lord. I Corinthians 1:9 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Point in Time Recovery
Simon Riggs [EMAIL PROTECTED] writes: I've not done power off tests, yet. They need to be done just to check...actually you don't need to do this to test PITR... I agree, power off is not really the point here. What we need to check into is (a) the mechanics of archiving WAL segments and (b) the process of restoring given a backup and a bunch of WAL segments. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Portals and nested transactions
My answers: Q1: Should Portals successfully created within the failed subxact be closed? Or should they remain open? no for protocol level I can understand a yes to this one for sql level, because it will be hard to clean up by hand :-( But I like the analogy to hold cursors, so I would also say no to sql level. Is the pro yes argument ACID allowed here ? I thought ACID is about data integrity and not flow control, and also deals with main transactions and not subtransactions. Q2: If the subxact changed the state of a pre-existing Portal, should that state change roll back? In particular, can a Close Portal operation roll back? NO for both SQL and protocol level. The analogy is imho that closing a 'hold cursor' is also never rolled back How to do it non-transactionally Sounds like a good plan, but also sounds like a lot of work. Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Release planning
Jan Wieck wrote: On 7/14/2004 5:00 AM, Christopher Kings-Lynne wrote: Yes, but it has been committed, it will be released - the only thing is that people will have to wait a few more months for it. My point was Just a few more months? That is exactly what I was asking for, put some of the stuff into 7.6 so it will be released in a few more months instead of holding back the release now. If we really released major versions every 4-6 months, what about the last stable version? If only the current and the last version are supported, this would mean that security/bugfixes are available only to versions no older than 8 months or so, or said the other way around if I need a bug fixed stable version I'll have to upgrade to the next major version after quite a short time. We'd have to support 2-3 versions older than the current release to cover one year major version stability, which appears not viable for the community. Seems we're stuck in the present way, being probably the best that can be made with limited resources. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Point in Time Recovery
On 14 Jul, Simon Riggs wrote: PITR Patch v5_1 just posted has Point in Time Recovery working Still some rough edgesbut we really need some testers now to give this a try and let me know what you think. Klaus Naumann and Mark Wong are the only [non-committers] to have tried to run the code (and let me know about it), so please have a look at [PATCHES] and try it out. Many thanks, Simon Riggs Simon, I just tried applying the v5_1 patch against the cvs tip today and got a couple of rejections. I'll copy the patch output here. Let me know if you want to see the reject files or anything else: $ patch -p0 ../../../pitr-v5_1.diff patching file backend/access/nbtree/nbtsort.c Hunk #2 FAILED at 221. 1 out of 2 hunks FAILED -- saving rejects to file backend/access/nbtree/nbtsort.c.rej patching file backend/access/transam/xlog.c Hunk #11 FAILED at 1802. Hunk #15 FAILED at 2152. Hunk #16 FAILED at 2202. Hunk #21 FAILED at 3450. Hunk #23 FAILED at 3539. Hunk #25 FAILED at 3582. Hunk #26 FAILED at 3833. Hunk #27 succeeded at 3883 with fuzz 2. Hunk #28 FAILED at 4446. Hunk #29 succeeded at 4470 with fuzz 2. 8 out of 29 hunks FAILED -- saving rejects to file backend/access/transam/xlog.c.rej patching file backend/postmaster/Makefile patching file backend/postmaster/postmaster.c Hunk #3 succeeded at 1218 with fuzz 2 (offset 70 lines). Hunk #4 succeeded at 1827 (offset 70 lines). Hunk #5 succeeded at 1874 (offset 70 lines). Hunk #6 succeeded at 1894 (offset 70 lines). Hunk #7 FAILED at 1985. Hunk #8 succeeded at 2039 (offset 70 lines). Hunk #9 succeeded at 2236 (offset 70 lines). Hunk #10 succeeded at 2996 with fuzz 2 (offset 70 lines). 1 out of 10 hunks FAILED -- saving rejects to file backend/postmaster/postmaster.c.rej patching file backend/storage/smgr/md.c Hunk #1 succeeded at 162 with fuzz 2. patching file backend/utils/misc/guc.c Hunk #1 succeeded at 342 (offset 9 lines). Hunk #2 succeeded at 1387 (offset 9 lines). patching file backend/utils/misc/postgresql.conf.sample Hunk #1 succeeded at 113 (offset 10 lines). patching file bin/initdb/initdb.c patching file include/access/xlog.h patching file include/storage/pmsignal.h ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Portals and nested transactions
On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote: I've been thinking about what to do with cursors in subtransactions. The problem really includes both cursors (created with DECLARE CURSOR) and portals (created with the V3-protocol Bind message) since they are the same kind of animal internally, namely a Portal. So within this proposal, a query executed by normal means will get its resources saved in the transaction ResourceOwner? How is the unnamed portal affected by it? Supposing that the unnamed portal is treated like any other portal (with its own ResourceOwner), we have to make sure to shut it down properly if something goes wrong. Not sure how this applies to portals created by SPI. Q1: Should Portals successfully created within the failed subxact be closed? Or should they remain open? Q2: If the subxact changed the state of a pre-existing Portal, should that state change roll back? In particular, can a Close Portal operation roll back? IMHO the transactional view is better; if we take the other approach, then users can't just use a simple retry loop around a subtransaction. The discussion sort of trailed off there because we had no ideas how to implement either. I will now sketch some implementation ideas about how to do the nontransactional way. Sounds excellent to me. We could support the transactional behavior as well, but not very efficiently (at least not in the first cut). I think we should decide what behavior is best now, and not change it in a later release. If it's going to be somewhat inefficient, try to minimise it. But just as I decided not to support the nested transaction syntax and instead change to the savepoint syntax, lets keep things consistent. IMHO anyway. On the other hand, some people supported the idea that v3 Bind portals should behave nontransactionally, while DECLARE portals should behave transactionally. Maybe we could make that a property of the portal, or even a user-selectable property (where we would define a reasonable default behavior). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) In a specialized industrial society, it would be a disaster to have kids running around loose. (Paul Graham) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Portals and nested transactions
Tom, As much as I can understand the arguments -- many of them performance-oriented -- for handling Portals non-transactionally, I simply don't see how we can do it and not create huge problems for anyone who uses both cursors and NTs together ... as those who use either are liable to do. What I think we could do, though, is record the Portal's high-level state as the number of rows fetched from it. On abort, rewind the Portal and then fetch that number of rows again (this is the same method used by MOVE ABSOLUTE). We could optimize things a little bit by not doing this repositioning until and unless the Portal is actually used again. Still, it wouldn't be cheap... From what you're describing, this seems like the wisest course. I can't endorse us getting into any situation where *some* operations are rolled back by an NT abort, and some are not.That seems like begging for 12-hour-long debugging sessions. The only cost of doing things transactionally seems to be the performance cost of re-fetching the Portal in the event of a subtransaction abort containing a Portal command. If it's a comparison between the performance loss of re-fetching a Portal, and the debugging nightmare of not knowing what state a Portal is in after an abort and rollback (consider NTs containing loops), I'll take the latter any day. Of course this only handles SELECT-query portals, not portals that contain data-modification commands. But the latter cannot be suspended partway through anyhow, so there is no scenario where we need to recover to a partly-executed state. (Recall what I said before about not allowing continuation of a portal that itself got an error.) Yes, and the possibility of updatable cursors makes the transactional argument even more compelling. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Release planning
Jan Wieck wrote: On 7/14/2004 5:00 AM, Christopher Kings-Lynne wrote: Yes, but it has been committed, it will be released - the only thing is that people will have to wait a few more months for it. My point was Just a few more months? That is exactly what I was asking for, put some of the stuff into 7.6 so it will be released in a few more months instead of holding back the release now. Well, that's not possible to answer. However, they have already paid for tablespaces and nested transactions, so who am I to begrudge them their storage manager? Afilias has already payed for as well. Are they paying in some strange kind of different dollars or what? Don't get me wrong here, I don't have a problem with someone making money out of my 8+ years of contributions to this project. But I do have a problem with those efforts getting in my way of contributing. I'm not quite sure how you've been inhibited from contributing? You've done a bunch of stuff for 7.5, that is committed and will be in release. How will press releases and news splashes hinder that? There is again this will be released. Great, but what I don't get is why the stuff that wasn't ready at the original release schedule qualifies in your mind easily to push back, when my stuff that was ready is so unimportant that it can simply be jiggled around for a couple of months. Are nested transactions that more or an everyone needs feature than the benefits resulting from an improved cache algorithm? The community decides when to stop development. Neither Afilias nor any other company has that control. If you want the development cycle cut shorter, make your case to the community --- if you win, great, if not, don't gripe about it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Release planning (was: Re: [HACKERS] Status report)
Jan Wieck wrote: On 7/13/2004 10:23 PM, Christopher Kings-Lynne wrote: I was thinking of something much simpler where Jan would create an ARC patch against 7.4.X and have it either in /contrib for 7.4.X or on our ftp servers, or on a web site. I could create a mechanism so SELECT version() would display Jan's add-on. I think you guys just need to learn to become comfortable with being hard-nosed about this. Just Do Not Care about people who want ARC right this second. Do you see them calling up Oracle and saying please backport the new stuff from 11i-devel so we can use it now please? Hmmm ... I wonder what you think who those people are who want ARC right this second, and who you guys are on the other hand. To fill you in a little, I am the PostgreSQL CORE team member Jan Wieck, who burned Afilias payroll hours to implement the ARC buffer replacement strategy. The feature has been completed and fully contributed under the BSD license way ahead of any possible release schedule. I have had several requests for backports, but consider the feature too delicate to make such patch available. Don't worry, it's not one of my goals to get this into any release, the reason I am personally touched by this is not because it affects my checking account in any way. What touches me here is the fact that the PostgreSQL Open Source Project under the BSD license seems starting to care a lot more about some press releases and silly news splashes, than to care about real features contributed under the terms and conditions of the BSD license by serious members of the Open Source Community. Which part of the storage manager, that Futjitsu uses so that their customers don't need ARC, the background writer or vacuum at all, do you think will be contributed any time soon? Don't get me wrong here, I don't have a problem with someone making money out of my 8+ years of contributions to this project. But I do have a problem with those efforts getting in my way of contributing. What are you talking about? Are you suggesting Fujitsu's features are getting more attendtion than ARC for some political reason? You think nested transactions and tablespaces are just press release features? All those features are being developed by the community under community direction and based on community feedback/needs. How is that different from Afilias funding ARC? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Release planning (was: Re: Status report)
Jan, What touches me here is the fact that the PostgreSQL Open Source Project under the BSD license seems starting to care a lot more about some press releases and silly news splashes, than to care about real features contributed under the terms and conditions of the BSD license by serious members of the Open Source Community. I don't get this. At what point did you seriously expect that 7.5 would be out less than a year after 7.4? The only circumstance I can recall discussing on -Core under which PG7.5 would be released early was if Win32 was ready and debugged by March. Otherwise, I was certainly expecting that things would go more or less as they did last year (though hopefully with more bugs caught during beta) and nothing we discussed here or on -Core led me to think otherwise. I do agree that the fact that, for example, NTs have gotten press coverage is *no* reason to decide whether to keep them for 7.5 or push them to 7.6. Press coverage exists to enhance the image of PostgreSQL and not to influence development. Nor is the fact that FJ sponsored the feature in question significant; if they want it ASAP, they can afford to backport. No, the questions which are important are the below. And keep in mind that the *only* patch we're talking about holding back is NTs; so far, nobody has raised any issues with PITR or Tablespaces that would prevent them from being part of 7.5.0. 1) How long is it going to take to resolve the remaining issues with NTs? It this a next week thing or is this like Win32, last year? 2) Are the unresolved issues with NTs the result of the complexity of the problem, or just the result of the community not paying attention to the patch until after feature-freeze? 3) Is there some majority in our community who would rather hold back 7.5 by several weeks in order to get NTs now instead of mid-2005? Based on (1) and (2), NTs are starting to feel like Win32 did last year to me. Please don't mistake me; I would very, very much like to have them as someone with half a million lines of PL/pgSQL in my past and half-a-dozen J2EE+PostgreSQL clients. It's a very hard problem and Alvaro has been working like a demon to resolve it. However, I'm beginning to think that it is a *very* hard problem and Alvaro has had only about 2 months to work on it. There are just too many things -- cursors, functions, exceptions, syntax -- that we have only begun to address. I'm very much afraid of a repeat of last year, where we waited an extra month for a Win32 version that was going to take an extra year. So I'm thinking we hold them back. That we try to get out 7.5 by October this year, and target 7.6, on a short release cycle, for March. We would already have 3 new features for 7.6, and I'm sure more will show up by January. This would also resolve the issue of shifting our release cycles. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Proposal for detecting encoding mismatch in initdb
I wrote: I've worked out a scheme that should adequately detect encoding mismatches in initdb. Done. Karel pointed me to some other projects that are trying to do the same thing, and they are no smarter than what we have now. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Release planning (was: Re: [HACKERS] Status report)
On Wed, 14 Jul 2004, Bruce Momjian wrote: What are you talking about? Are you suggesting Fujitsu's features are getting more attendtion than ARC for some political reason? You think nested transactions and tablespaces are just press release features? All those features are being developed by the community under community direction and based on community feedback/needs. How is that different from Afilias funding ARC? No, the beef that Jan has, and that I also have, is that we put off the release that was schedualed for June 1st in order to get Nested Xacts into the tree ... hindsight being 20-20, we shouldn't have done that, but should have delayed Nested Xacts for 7.6 ... there *were* enough features in the tree to warrant a release, and features that ppl needed / wanted. Do I believe there were political motivations for postponing the feature freeze? Personally ... no. And I don't believe that the Press Release (and a nice one at that) can really be counted as motivation for the postponement, since the PR was done *after* we decided to push things back a month ... I do believe that there was some pressure from Futjitsu involved, in postponing it, since they'd rather see it in sooner then later ... *but* ... I don't really believe that the pressure is any different then there quite possibly could have been had, say, Jan been almost finished ARC and Affilias wanted to see that sooner rather then later ... we all want to see the feature we've either been working on, or funded, released as soon as possible ... The big problem that I see with how this feature freeze/beta/release has gone down is that we have *alot* of big items that are/were being worked on (ARC, BGWriter, auto_vacuum, PITR, Nested Xacts), and only so much man power at the reviewer stage ... we *should* have frozen it all on June 1st, got the ready features out the door and released, and then concentrated on getting the almost ready, but not quite features into the next release as quickly as possible ... Hindsight is 20-20 ... maybe next time we'll learn from it? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: Release planning (was: Re: [HACKERS] Status report)
On 7/14/2004 1:13 PM, Bruce Momjian wrote: What are you talking about? Are you suggesting Fujitsu's features are getting more attendtion than ARC for some political reason? You think nested transactions and tablespaces are just press release features? All those features are being developed by the community under community direction and based on community feedback/needs. How is that different from Afilias funding ARC? I was getting a little upset about the suggestion of being hard-nosed against the people who want ARC now. Yes, I wanted those features out earlier and this was not because of Afilias or dictated by Afilias, but because there is demand for it from the community. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Release planning
On Wed, 14 Jul 2004, Bruce Momjian wrote: The community decides when to stop development. Neither Afilias nor any other company has that control. If you want the development cycle cut shorter, make your case to the community --- if you win, great, if not, don't gripe about it. Core decides when to stop development ... that is what cores role is ... to *steer* the development of the code ... the community didn't decide to extend the dev cycle this time, any more then it has in the past ... core decided to ... period. end of story. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Portals and nested transactions
Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote: I've been thinking about what to do with cursors in subtransactions. So within this proposal, a query executed by normal means will get its resources saved in the transaction ResourceOwner? No, *all* queries are executed within portals. The reason we need a transaction ResourceOwner is because query parsing/planning happens in advance of creating the portal, so we need someplace to keep track of resources acquired during that process. How is the unnamed portal affected by it? Same as the rest. I don't recall whether SPI creates actual portals, but we'd definitely want it to create a new ResourceOwner for queries it runs. On the other hand, some people supported the idea that v3 Bind portals should behave nontransactionally, while DECLARE portals should behave transactionally. Maybe we could make that a property of the portal, or even a user-selectable property (where we would define a reasonable default behavior). This is certainly possible. Whether it's a good idea needs further discussion... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Point in Time Recovery
On Wed, 2004-07-14 at 16:55, [EMAIL PROTECTED] wrote: On 14 Jul, Simon Riggs wrote: PITR Patch v5_1 just posted has Point in Time Recovery working Still some rough edgesbut we really need some testers now to give this a try and let me know what you think. Klaus Naumann and Mark Wong are the only [non-committers] to have tried to run the code (and let me know about it), so please have a look at [PATCHES] and try it out. I just tried applying the v5_1 patch against the cvs tip today and got a couple of rejections. I'll copy the patch output here. Let me know if you want to see the reject files or anything else: I'm on it. Sorry 'bout that all - midnight fingers. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Portals and nested transactions
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote: I've been thinking about what to do with cursors in subtransactions. So within this proposal, a query executed by normal means will get its resources saved in the transaction ResourceOwner? No, *all* queries are executed within portals. The reason we need a transaction ResourceOwner is because query parsing/planning happens in advance of creating the portal, so we need someplace to keep track of resources acquired during that process. How is the unnamed portal affected by it? Same as the rest. I don't recall whether SPI creates actual portals, but we'd definitely want it to create a new ResourceOwner for queries it runs. On the other hand, some people supported the idea that v3 Bind portals should behave nontransactionally, while DECLARE portals should behave transactionally. Maybe we could make that a property of the portal, or even a user-selectable property (where we would define a reasonable default behavior). This is certainly possible. Whether it's a good idea needs further discussion... I didn't want to be the first to speak up on this as I'm relatively new to the group (so thank you Alvaro), but I would definitely perfer the option of either trans or non-trans behavior. I can see using the non-trans behavior in a cursor based FOR loop with a savepoint/subtrans allowing me to fail on row x and continue on to row x+1 immediately. Then, after choosing trans-mode, I could implement a multi-strategy row processor. Of course, just to be difficult, my ideal default would be: Q1 -- Portals close Q2 -- Portals do NOT roll back to previous state. However, I do see the logical inconsistency in that. But then again, subtransactions/savepoints are not ACID, so it seems to be implementation dependent. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- --miker ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Release planning (was: Re: [HACKERS] Status report)
Marc G. Fournier wrote: The big problem that I see with how this feature freeze/beta/release has gone down is that we have *alot* of big items that are/were being worked on (ARC, BGWriter, auto_vacuum, PITR, Nested Xacts), and only so much man power at the reviewer stage ... we *should* have frozen it all on June 1st, got the ready features out the door and released, and then concentrated on getting the almost ready, but not quite features into the next release as quickly as possible ... Hindsight is 20-20 ... maybe next time we'll learn from it? True, 20-20. One thing is that you can't schedule assuming full knowledge of the future, of course. For example, on May 1, I thought by June 1 PITR and NT would be done, and that Win32 and tablespaces wouldn't. What actually happened is that tablespaces made the deadline (with June adjustments), NT is in but needs more work, and Win32 is better off now than I thought it would be. And we don't know if PITR is ready or not because we haven't studied it enough. One problem with pushing 7.5 out June 1 and then coming back to these features is losing momentum. Sure, ideally we could hit them all in 7.6, but realistically it is a lot harder to get things moving once you stop. Look at the PITR patch we had for 7.3 (?) and are only now getting it into the tree. I am not saying we did things right or wrong --- just pointing out the momentum issue. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Release planning
Marc G. Fournier wrote: On Wed, 14 Jul 2004, Bruce Momjian wrote: The community decides when to stop development. Neither Afilias nor any other company has that control. If you want the development cycle cut shorter, make your case to the community --- if you win, great, if not, don't gripe about it. Core decides when to stop development ... that is what cores role is ... to *steer* the development of the code ... the community didn't decide to extend the dev cycle this time, any more then it has in the past ... core decided to ... period. end of story. Not for me. I think core picks specific dates for release because it is too hard to get concensus quickly on dates, but the community is certainly involved in the setting of development cycles. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Portals and nested transactions
Mike Rylander wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote: I've been thinking about what to do with cursors in subtransactions. So within this proposal, a query executed by normal means will get its resources saved in the transaction ResourceOwner? No, *all* queries are executed within portals. The reason we need a transaction ResourceOwner is because query parsing/planning happens in advance of creating the portal, so we need someplace to keep track of resources acquired during that process. How is the unnamed portal affected by it? Same as the rest. I don't recall whether SPI creates actual portals, but we'd definitely want it to create a new ResourceOwner for queries it runs. On the other hand, some people supported the idea that v3 Bind portals should behave nontransactionally, while DECLARE portals should behave transactionally. Maybe we could make that a property of the portal, or even a user-selectable property (where we would define a reasonable default behavior). This is certainly possible. Whether it's a good idea needs further discussion... I didn't want to be the first to speak up on this as I'm relatively new to the group (so thank you Alvaro), but I would definitely perfer the option of either trans or non-trans behavior. I can see using the non-trans behavior in a cursor based FOR loop with a savepoint/subtrans allowing me to fail on row x and continue on to row x+1 immediately. Then, after choosing trans-mode, I could implement a multi-strategy row processor. Of course, just to be difficult, my ideal default would be: Q1 -- Portals close Q2 -- Portals do NOT roll back to previous state. However, I do see the logical inconsistency in that. But then again, subtransactions/savepoints are not ACID, so it seems to be implementation dependent. To make that a little more specific, something along the lines of: DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ] [ IN { LEXICAL | GLOBAL } SCOPE ^^^ ... or some such... I think in perl. :) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- --miker ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Point in Time Recovery
On Wed, 2004-07-14 at 10:57, Zeugswetter Andreas SB SD wrote: The recovery mechanism doesn't rely upon you knowing 1 or 3. The recovery reads pg_control (from the backup) and then attempts to de-archive the appropriate xlog segment file and then starts rollforward Unfortunately this only works if pg_control was the first file to be backed up (or by chance no checkpoint happened after backup start and pg_control backup) Other db's have commands for: start/end external backup OK...this idea has come up a few times. Here's my take: - OS and hardware facilities exist now to make instant copies of sets of files. Some of these are open source, others not. If you use these, you have no requirement for this functionalitybut these alone are no replacement for archive recovery I accept that some people may not wish to go to the expense or effort to use those options, but in my mind these are the people that will not be using archive_mode anyway. - all we would really need to do is to stop the bgwriter from doing anything during backup. pgcontrol is only updated at checkpoint. The current xlog is updated constantly, but this need not be copied because we are already archiving it as soon as its full. That leaves the bgwriter, which is now responsible for both lazy writing and checkpoints. So, put a switch into bgwriter to halt for a period, then turn it back on at the end. Could be a SIGHUP GUC...or... ...and with my greatest respects - please could somebody else code that?... my time is limited Best regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Portals and nested transactions
Josh Berkus wrote: Tom, As much as I can understand the arguments -- many of them performance-oriented -- for handling Portals non-transactionally, I simply don't see how we can do it and not create huge problems for anyone who uses both cursors and NTs together ... as those who use either are liable to do. I'd argue against rolling back portal state on subxact commit for three reasons that aren't performance-related: it makes (some?) client code harder, it's incompatible with other implementations of savepoints, and it's inconsistent with how WITH HOLD cursors already behave. ... The JDBC driver is going to be unhappy if this happens. It is not expecting the portal state of any cursors backing its ResultSets to change unexpectedly, as a ROLLBACK TO SAVEPOINT will do. To correctly handle this, at a minimum it needs notification of changes to the transaction nesting level as they happen (did anything get resolved here?); then it has to store the client-side state of each open portal whenever a new subxact (== SAVEPOINT) is opened, and restore the appropriate state on rollback. I'd expect any layer that uses portals/cursors to buffer results to have similar problems. There are two problems going on here: 1) The state of the portal is not necessarily directly visible to the application -- in the case of the JDBC driver they are used to buffer large resultsets -- so at that level the behaviour on rollback isn't visible or useful to the application anyway, and rolling back state actually makes life more difficult for the buffering code. 2) The application-visible result object semantics (the ResultSet in JDBC's case) may have its own semantics that don't correspond to the behaviour of portals, and it may not be possible to arbitarily change the result object's semantics (the only thing that the JDBC spec says about ResultSets vs. ROLLBACK is specifying the holdability of the resultset -- rolling back resultset state on rollback to savepoint is going to break most existing JDBC apps that use savepoints, IMO). So the driver ends up doing lots of extra work to fake nontransactional behaviour. ... Rolling back state is the opposite of what DB2 does according to the DB2 docs, as I mentioned in an earlier email: # The impact on cursors resulting from a ROLLBACK TO SAVEPOINT depends on the statements within the savepoint * If the savepoint contains DDL on which a cursor is dependent, the cursor is marked invalid. Attempts to use such a cursor results in an error (SQLSTATE 57007). * Otherwise: o If the cursor is referenced in the savepoint, the cursor remains open and is positioned before the next logical row of the result table. (A FETCH must be performed before a positioned UPDATE or DELETE statement is issued.) o Otherwise, the cursor is not affected by the ROLLBACK TO SAVEPOINT (it remains open and positioned). I don't know what Oracle does. The 2003 draft says that the behaviour of cursors established before the savepoint that was rolled back to is implementation-defined. Bah. ... Finally, we don't roll back WITH HOLD cursor state on top-level transaction rollback. Why are the semantics in a subxact rollback different? -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Portals and nested transactions
Alvaro Herrera wrote: On the other hand, some people supported the idea that v3 Bind portals should behave nontransactionally, while DECLARE portals should behave transactionally. Maybe we could make that a property of the portal, or even a user-selectable property (where we would define a reasonable default behavior). If this is going to happen, either the protocol-level portals need access to all the functionality of DECLARE, or it needs to be done as a user-selectable property of DECLARE. Currently the JDBC driver uses only protocol-level portals, but as soon as we want to support large scrollable or holdable ResultSets (effectively unsupported by the current driver) it will have to use DECLARE to get access to SCROLL / WITH HOLD. -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Another locale test program
Hi Peter, Compile the attached test program and then run It doesn't even compile in a OpenBSD box. The langinfo.h doesn't have 'CODESET' symbol. for x in `locale -a`; do LC_ALL=$x ./test; done | sort -u OpenBSD doesn't support locale at all (correct me if I'm wrong). If you don't have a locale command, maybe something like this will work: for x in `ls /usr/share/locale`; do LC_ALL=`basename $x` ./test; done | sort -u We do have nothing in /usr/share/locale -- Euler Taveira de Oliveira euler (at) ufgnet.ufg.br Desenvolvedor Web e Administrador de Sistemas UFGNet - Universidade Federal de Goiás ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Release planning (was: Re: [HACKERS] Status report)
On Tue, 2004-07-13 at 23:03, Marc G. Fournier wrote: As a community, I don't think we should be 'supporting' anything older then the last STABLE ... I agree, but that message isn't clearly stated anywhere. The lists are full of people running very old releases - and everybody keeps having to ask what release are you running and the answer is always shocking. Can we set up different lists for the various major versions, so people can see where to go for different types of support? Rather than General, have General7.4 and General7.3...making everybody's first question what is my release? where should I post this question? - 95% of people are newbies, whatever the list/project. That will also make it clear that on-list support is available for older releases, but they really should be thinking about upgrade. And different people can choose whether to hang out at the bleeding edge, or legacy support. We could also have different hints and tips by list then. One of the gems (hint 9, I think) is changing in r7.5, so we must either mis-advise people who use the new release or fail to advise those using an older release. The down-rev lists could contain advice about upgrading... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Release planning (was: Re: Status report)
Bruce Momjian wrote: I was thinking of something much simpler where Jan would create an ARC patch against 7.4.X and have it either in /contrib for 7.4.X or on our ftp servers, or on a web site. I could create a mechanism so SELECT version() would display Jan's add-on. :-( I was asking to add the vacuum delayed patch to 7.4 months ago and the response was: why introduce instability to a stable release ? I hope the global consensus is a no way to procede also for ARC. It's true the version 7.5 ( or 8.0 ) will be really a great release but IMHO introduce in one shot: 1) PITR 2) Nested Transaction 3) WIN32 porting 4) ARC 5) Table Space 6) I'm sure I'm forgetting something was really too much. I hope that all will be fine. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Release planning (was: Re: [HACKERS] Status report)
On Wed, 2004-07-14 at 21:02, Bruce Momjian wrote: Marc G. Fournier wrote: The big problem that I see with how this feature freeze/beta/release has gone down is that we have *alot* of big items that are/were being worked on (ARC, BGWriter, auto_vacuum, PITR, Nested Xacts), and only so much man power at the reviewer stage ... we *should* have frozen it all on June 1st, got the ready features out the door and released, and then concentrated on getting the almost ready, but not quite features into the next release as quickly as possible ... Hindsight is 20-20 ... maybe next time we'll learn from it? True, 20-20. One thing is that you can't schedule assuming full knowledge of the future, of course. For example, on May 1, I thought by June 1 PITR and NT would be done, and that Win32 and tablespaces wouldn't. What actually happened is that tablespaces made the deadline (with June adjustments), NT is in but needs more work, and Win32 is better off now than I thought it would be. And we don't know if PITR is ready or not because we haven't studied it enough. I see a couple of issues: - new releases of PostgreSQL require a full initdb. There is little upgrade support as there is with other products. (Not complaining..) - commercial products release around every 18 months...customers do NOT want this to be any quicker...more disruptive upgrades, plus marketing takes time to organise. I have customers that upgrade regularly every 3+ years (!), and take longer term strategy into consideration. - commercial issues often cause products to be delayedMS is said to be late delivering Yukonmarketing-led companies often delay waiting for the right feature setsaled-led companies never do. Delivering early as Oracle often does mean that the received wisdom is never upgrade to a x.0 version, always wait for the x.1 minor version where all the features actually work as advertised. Deadlines are great, but advertise them ahead of time, then stick to them. Every other project I see has a big page saying how to contribute and then details of deadlines etc.. We can have a major feature deadline, then a minor feature deadline. I particularly liked the idea of 1 July as the major feature deadline, then 14 July as major-feature-tweak deadline. That funnels things better to cater for the manpower available. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Point in Time Recovery
I noticed that compiling with 5_1 patch applied fails due to XLOG_archive_dir being removed from xlog.c , but src/backend/commands/tablecmds.c still uses it. I did the following to tablecmds.c : 5408c5408 extern char XLOG_archive_dir[]; --- extern char *XLogArchiveDest; 5410c5410 use_wal = XLOG_archive_dir[0] !rel-rd_istemp; --- use_wal = XLogArchiveDest[0] !rel-rd_istemp; Now I have to see if I have broken it with this change :-) regards Mark Simon Riggs wrote: On Wed, 2004-07-14 at 16:55, [EMAIL PROTECTED] wrote: On 14 Jul, Simon Riggs wrote: PITR Patch v5_1 just posted has Point in Time Recovery working Still some rough edgesbut we really need some testers now to give this a try and let me know what you think. Klaus Naumann and Mark Wong are the only [non-committers] to have tried to run the code (and let me know about it), so please have a look at [PATCHES] and try it out. I just tried applying the v5_1 patch against the cvs tip today and got a couple of rejections. I'll copy the patch output here. Let me know if you want to see the reject files or anything else: I'm on it. Sorry 'bout that all - midnight fingers. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Release planning (was: Re: [HACKERS] Status report)
We can have a major feature deadline, then a minor feature deadline. I particularly liked the idea of 1 July as the major feature deadline, then 14 July as major-feature-tweak deadline. That funnels things better to cater for the manpower available. That being said, your PITR patch still hasn't been comitted and it's well past 1st July. If I had to drop something from 7.5 right now to make a release, then it would have to be PITR! Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Point in Time Recovery
Hi, folks. My colleages and I are planning to test PITR after the 7.5 beta release. Now we are desinging test items, but some specification are enough clear (to us). For example, we are not clear which resouce manager order to store log records. - some access method (like B-tree) require to log its date or not. - create/drop action of table space to be stored to the log or not. We'll be pleased if someone informs them. The test set we'll proceed has following items; - PITR can recover ordinary commited transaction's data. - tuple data themselves - index data associated with them - PITR can recover commited some special transaction's data. - DDL; create database, table, index and so on - maintenance commands (handling large amount of data); truncate, vacuum, reindex and so on. Items above are 'data aspects' of the test. Other aspects are as follows - Place of the archival log's drive; PITR can recover a database from archived log data - stored in the same drive as xlog. - stored in a different drive on the same machine in which the PostgreSQL runs. - stored in a different drive on a different machine. - Duration between a checkpoint and recovery; PITR can recover a database enough long after a checkpoint. - Time to Recover; - to end of the log. - to some specified time. - Type of failures; - system down --- kill the PostgreSQL process (as a simulation). - media lost --- delete database files (as a simulation). - These two case will be tested by a simulated situation first, and we would try some 'real' failure after. (real power down of the test machine to the first case, and 'plug off' the disk drive to the second one. these action would damage test machine, this is because we plan them after 'ordinary' test items.) The test set is under construction and we'll test the 7.5 beta for some weeks, and report the result of the test here. Sincerely yours. Tetsuo SAKATA. -- sakata.tetsuo _at_ lab.ntt.co.jp SAKATA, Tetsuo. Yokosuka JAPAN. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Point in Time Recovery
I talked to Tom on the phone today and and I think we have a procedure for doing backup/restore in a fairly foolproof way. As outlined below, we need to record the start/stop and checkpoint WAL file names and offsets, and somehow pass those on to restore. I think any system that requires users to link those values together is going to cause confusion and be error-prone. My idea is to do much of this automatically. First, create a server-side function called pitr_backup_start() which creates a file in the /data directory which contains the WAL filename/offsets for last checkpoint and start. Then do the backup of the data directory. Then call pitr_backup_stop() which adds the stop filename/offsets to the file, and archive that file in the same place as the WAL files. To restore, you untar the backup of /data. Then the recover backend reads the file created by pitr_backup_start() to find the name of the backup parameter file. It then recovers that file from the archive location and uses the start/stop/checkpoint filename/offset information to the restore. The advantage of this is that the tar backup contains everything needed to find the proper parameter file for restore. Ideally we could get all the parameters into the tar backup, but that isn't possible because we can't push the stop counters into the backup after the backup has completed. I recommend the pitr_backup_start() file be named for the current WAL filename/offset, perhaps 032c.3da390.backup or something like that. The file would be a simple text file in pg_xlog/archive_status: # start 2004-07-14 21:35:22.324579-04 wal_checkpoint = 0319.021233 wal_start = 032c.92a9cb ...added after backup completes... wal_stop = 034a.3db030 # stop 2004-07-14 21:32:22.0923213-04 The timestamps are for documentation only. These files give admins looking in the archive directory information on backup times. (As an idea, there is no need for the user to specify a recovery mode. If the postmaster starts and sees the pitr_backup_start() file in /data, it can go into recovery mode automatically. If the archiver can't find the file in the archive location, it can assume that it is just being started from power failure mode. However if it finds the file in the archive location, it can assume it is to enter recovery mode. There is a race condition that a crash during copy of the file to the archive location would be a problem. The solution would be to create a special flag file before copying the file to archive, and then archive it and remove the flag file. If the postmaster starts up and sees the pitr_backup_start() file in /data and in the archive location, and it doesn't see the flag file, it then knows it is doing a restore because the flag file would never appear in a backup. Anyway, this is just an idea.) --- Simon Riggs wrote: On Wed, 2004-07-14 at 10:57, Zeugswetter Andreas SB SD wrote: The recovery mechanism doesn't rely upon you knowing 1 or 3. The recovery reads pg_control (from the backup) and then attempts to de-archive the appropriate xlog segment file and then starts rollforward Unfortunately this only works if pg_control was the first file to be backed up (or by chance no checkpoint happened after backup start and pg_control backup) Other db's have commands for: start/end external backup OK...this idea has come up a few times. Here's my take: - OS and hardware facilities exist now to make instant copies of sets of files. Some of these are open source, others not. If you use these, you have no requirement for this functionalitybut these alone are no replacement for archive recovery I accept that some people may not wish to go to the expense or effort to use those options, but in my mind these are the people that will not be using archive_mode anyway. - all we would really need to do is to stop the bgwriter from doing anything during backup. pgcontrol is only updated at checkpoint. The current xlog is updated constantly, but this need not be copied because we are already archiving it as soon as its full. That leaves the bgwriter, which is now responsible for both lazy writing and checkpoints. So, put a switch into bgwriter to halt for a period, then turn it back on at the end. Could be a SIGHUP GUC...or... ...and with my greatest respects - please could somebody else code that?... my time is limited Best regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your
Re: Release planning (was: Re: [HACKERS] Status report)
On Thu, 15 Jul 2004, Christopher Kings-Lynne wrote: We can have a major feature deadline, then a minor feature deadline. I particularly liked the idea of 1 July as the major feature deadline, then 14 July as major-feature-tweak deadline. That funnels things better to cater for the manpower available. That being said, your PITR patch still hasn't been comitted and it's well past 1st July. If I had to drop something from 7.5 right now to make a release, then it would have to be PITR! As Bruce would put it that would be unfair to Simon, since it isn't his fault that the patch hasn't been reviewed for commit yet ... if, once reviewed, it is found to need work, then it should be put off until 7.6 ... I'm in Josh's camp on this one, in that Nested Xacts should probably be pulled since it still needs work :( Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Release planning (was: Re: [HACKERS] Status report)
Christopher Kings-Lynne wrote: We can have a major feature deadline, then a minor feature deadline. I particularly liked the idea of 1 July as the major feature deadline, then 14 July as major-feature-tweak deadline. That funnels things better to cater for the manpower available. That being said, your PITR patch still hasn't been comitted and it's well past 1st July. If I had to drop something from 7.5 right now to make a release, then it would have to be PITR! Well, the problem is that Simon isn't the reason the patch hasn't been applied. The reason is that we have had other priorities in reviewing patches so it doesn't seem fair to single out that feature for exclusion. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Portals and nested transactions
On Wed, Jul 14, 2004 at 03:11:54PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote: I've been thinking about what to do with cursors in subtransactions. So within this proposal, a query executed by normal means will get its resources saved in the transaction ResourceOwner? No, *all* queries are executed within portals. The reason we need a transaction ResourceOwner is because query parsing/planning happens in advance of creating the portal, so we need someplace to keep track of resources acquired during that process. Ah-ha, got it (should have known better). Do you want me to do the legwork for this to happen, or was your initial plan to do it yourself? Either way is OK with me ... -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Granting software the freedom to evolve guarantees only different results, not better ones. (Zygo Blaxell) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] serverlog rotation/functions
OK, I talked to Tom about this patch and I understand the issues now. I think the best solution will be to have the postmaster start a child process that can read the guc variables and create a log file based on it contents. The child would be responsible to create a new log file every X seconds as specified in the postgresql.conf file. Backends wishing to read the log file would call a function to list the contents of a directory. We can do this by creating a generic backend super-user-only function that can list the contents of any directory. Then you would use an API to read a specific file, similar to the log reading functions you already have. In fact, you could set up the API so reading a directory would return a list of directory names so you don't need a separate directory listing function. (Does your API return file contents as one big text string or as lines. If you return it as one big text string, the directory idea will not work and you will need a separate function.) So, we have: o use pipe and dup to copy stdout and stderr to your postmaster child o new guc variables to specify log destination and rotation times o a server-side function to force a log rotate o API to list a directory and show file contents With this functionality, you can have clients listing the log directory and choosing the most recent log file or previous ones. You could even configure it to remove old log files. Both Tom and I believe this is a more generic and reliable solution. --- Andreas Pflug wrote: Bruce Momjian wrote: Also there are no documenttion changes. Here are the missing docs, freshly created against cvs. Regards, Andreas -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Portals and nested transactions
Alvaro Herrera [EMAIL PROTECTED] writes: Do you want me to do the legwork for this to happen, or was your initial plan to do it yourself? Either way is OK with me ... I'm working on it, should have it done in a day or so. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html