Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Thanks Josh, I'll look for the earlier one and try to add it there... -Paul -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 12:09 AM To: Paul Silveira Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007) Paul Silveira wrote: Hello, I just wanted to voice my opinion for this feature... I've implemented a few Production applicaitons with PostgreSQL now and would die for that feature. Right now, I am constantly trying to find way's to make my data more available. Paul unfortunately you have responded to a hijacked thread. Jonah was speaking about a project that he wishes would have been accepted which was called Full Disjunctions. I have not read the read-only queries during wal replay thread but I can assure you that Jonah's response had nothing to do with it. Joshua D. Drake I've even resulted to using pg_dump to create read only copies of the database and placed them behind load balancers to make the data more available. Something like this would allow me to quickly leverage a read only node to scale out the applicaiton... If it can at all be built, it would get my first, second and third vote. :) Regards, Paul Silveira Jonah H. Harris-2 wrote: On 2/26/07, Bruce Momjian [EMAIL PROTECTED] wrote: Jonah, I have no idea what fault you are trying to blame on the community in the above statement. The author didn't discuss the idea with the community before spending months on it so we have no obligation to accept it in the core. You're missing the point entirely. The majority of the (vocal) community didn't even want the feature and as such, failed to provide viable suggestions for him to move forward. As the majority of the community didn't want the feature, it wouldn't have made a difference when he proposed it; which would have remained negative nonetheless. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Merlin Moncure wrote: getting back on topic (ahem), florian: are you comfortable going ahead with this? is there anything you need help with? I'm currently updating my proposal, trying to incorporate the points people brought up in this thread. I realized that trying to use the same kind of read only mode for both a standalone postgres (with the datadir on read-only storage), and a PITR-slave was missguided - http://archives.postgresql.org/pgsql-hackers/2005-11/msg01043.php was really enlightning ;-) Tom's objects regarding performance are the hardest one to deal with - I'm currently thinking about ways that the locking requirements could be relaxed - though I still plan to do a basic implementation first, and then try to improve it. I'll post an updated proposal during the next few days. greetings, Florian Pflug ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Hello, I just wanted to voice my opinion for this feature... I've implemented a few Production applicaitons with PostgreSQL now and would die for that feature. Right now, I am constantly trying to find way's to make my data more available. I've even resulted to using pg_dump to create read only copies of the database and placed them behind load balancers to make the data more available. Something like this would allow me to quickly leverage a read only node to scale out the applicaiton... If it can at all be built, it would get my first, second and third vote. :) Regards, Paul Silveira Jonah H. Harris-2 wrote: On 2/26/07, Bruce Momjian [EMAIL PROTECTED] wrote: Jonah, I have no idea what fault you are trying to blame on the community in the above statement. The author didn't discuss the idea with the community before spending months on it so we have no obligation to accept it in the core. You're missing the point entirely. The majority of the (vocal) community didn't even want the feature and as such, failed to provide viable suggestions for him to move forward. As the majority of the community didn't want the feature, it wouldn't have made a difference when he proposed it; which would have remained negative nonetheless. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- View this message in context: http://www.nabble.com/Proposal-for-Implenting-read-only-queries-during-wal-replay-%28SoC-2007%29-tf3279821.html#a9197770 Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Paul Silveira wrote: Hello, I just wanted to voice my opinion for this feature... I've implemented a few Production applicaitons with PostgreSQL now and would die for that feature. Right now, I am constantly trying to find way's to make my data more available. Paul unfortunately you have responded to a hijacked thread. Jonah was speaking about a project that he wishes would have been accepted which was called Full Disjunctions. I have not read the read-only queries during wal replay thread but I can assure you that Jonah's response had nothing to do with it. Joshua D. Drake I've even resulted to using pg_dump to create read only copies of the database and placed them behind load balancers to make the data more available. Something like this would allow me to quickly leverage a read only node to scale out the applicaiton... If it can at all be built, it would get my first, second and third vote. :) Regards, Paul Silveira Jonah H. Harris-2 wrote: On 2/26/07, Bruce Momjian [EMAIL PROTECTED] wrote: Jonah, I have no idea what fault you are trying to blame on the community in the above statement. The author didn't discuss the idea with the community before spending months on it so we have no obligation to accept it in the core. You're missing the point entirely. The majority of the (vocal) community didn't even want the feature and as such, failed to provide viable suggestions for him to move forward. As the majority of the community didn't want the feature, it wouldn't have made a difference when he proposed it; which would have remained negative nonetheless. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Jonah H. Harris wrote: On 2/23/07, Joshua D. Drake [EMAIL PROTECTED] wrote: A good example of the wrong way to do it is the Full Disjunctions project. Great idea, Great project, not bitrot and hard space because it hasn't been touched or maintained sense release. Don't get me started there. The decision was split between PostgreSQL core 50/50 for inclusion in contrib, yet it was not included. As I said at the time, people will move on and the project would go to pgfoundry (which I called the graveyard) and die; which is exactly what happened. And, in the Full Disjunctions project's defense, the community was wholly at fault. He posted several times for - suggestions and most of the community responses were, why would we care about or want that. The community can't rely on contributors, especially students, to spend months of their time pushing ideas through a gauntlet of negativity. Jonah, I have no idea what fault you are trying to blame on the community in the above statement. The author didn't discuss the idea with the community before spending months on it so we have no obligation to accept it in the core. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
On 2/26/07, Bruce Momjian [EMAIL PROTECTED] wrote: Jonah, I have no idea what fault you are trying to blame on the community in the above statement. The author didn't discuss the idea with the community before spending months on it so we have no obligation to accept it in the core. You're missing the point entirely. The majority of the (vocal) community didn't even want the feature and as such, failed to provide viable suggestions for him to move forward. As the majority of the community didn't want the feature, it wouldn't have made a difference when he proposed it; which would have remained negative nonetheless. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Jonah H. Harris wrote: On 2/26/07, Bruce Momjian [EMAIL PROTECTED] wrote: Jonah, I have no idea what fault you are trying to blame on the community in the above statement. The author didn't discuss the idea with the community before spending months on it so we have no obligation to accept it in the core. You're missing the point entirely. The majority of the (vocal) community didn't even want the feature and as such, failed to provide viable suggestions for him to move forward. As the majority of the community didn't want the feature, it wouldn't have made a difference when he proposed it; which would have remained negative nonetheless. There are so many things wrong with the above paragraph. Vocal hackers? Bruce, wanted real world example Dave, wanted it in contrib Berkus, wanted it in contrib Drake, (not really a hacker) pushed for exposure even though pushed to pgfoundry Tgl, wanted real world example, backed it on pgfoundry Dunstan, liked the idea but wanted it pushed to pgfoundry From what I can tell Jonah, you are all bark and no bite. Everything you mention is bogus in this thread. I read the responses (just now) about full disjunctions and they were not negative. They were more, Show me the beef which is perfectly acceptable when reviewing the possibility of accepting code. From what I can tell, unless the hacker said, You joy! let's rock and make it part of core NOW! it would have been considered negative by you. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
getting back on topic (ahem), florian: are you comfortable going ahead with this? is there anything you need help with? merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: My line of reasoning is that stopping wal replay at a arbitrary point, and then starting a read-only transaction with an empty snapshot (meaning that all exactly those transactions marked as comitted in the clog are assumed to be visible to the transaction) is exactly the same as sending the backend a SIGKILL when it just wrote the wal record in question, and then restarting postgres, and starting a transaction. The hole in that reasoning is that no one would be satisfied with the behavior of a Postgres database that was being forcibly restarted every few seconds. Yeah, we won't lose transactions that have been promised committed, but losing a large fraction of transactions-in-progress won't please anyone. Nor will queries on a slave that's behaving like that provide an accurate model of what the same queries would produce if issued on the master. Sorry, I don't get your point. What do you mean by loosing a large fraction of transactions in progress. You wouldn't loose them - the master would be running as usual. And after the slave replayed the wal past their commit record, they would be visible on the slave too. The point of my argument was just to convice (myself) that wal-logging of locking requests is not necessary, as long as you don't want to playback archived wal _and_ run read-only queries on the slave at the same time. I also don't understand what you mean by Nor will queries on the slave that's behaving like that provide an accurate model of what the same queries would produce if issued on the master. Sureley they won't - after all, this is a kind of asynchronous replication. The most you can hope fore is to eventually catch up with the master. And I don't propose that my serialization of wal-replay and running queries is how PITR master-slave replication should ultimatly look like. But's it's something that can be done _now_, without changing what kind of operations are wal-logged. And something that I believe I can finish in the timeframe of a SoC project. After I'm done with implementing this limited read-only mode, I'd be very interested in trying to extend it, by allow wal-replay and queries to run concurrently. greetings, Florian Pflug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
On 2/23/07, Joshua D. Drake [EMAIL PROTECTED] wrote: A good example of the wrong way to do it is the Full Disjunctions project. Great idea, Great project, not bitrot and hard space because it hasn't been touched or maintained sense release. Don't get me started there. The decision was split between PostgreSQL core 50/50 for inclusion in contrib, yet it was not included. As I said at the time, people will move on and the project would go to pgfoundry (which I called the graveyard) and die; which is exactly what happened. And, in the Full Disjunctions project's defense, the community was wholly at fault. He posted several times for suggestions and most of the community responses were, why would we care about or want that. The community can't rely on contributors, especially students, to spend months of their time pushing ideas through a gauntlet of negativity. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Jonah H. Harris wrote: On 2/23/07, Joshua D. Drake [EMAIL PROTECTED] wrote: A good example of the wrong way to do it is the Full Disjunctions project. Great idea, Great project, not bitrot and hard space because it hasn't been touched or maintained sense release. Don't get me started there. The decision was split between PostgreSQL core 50/50 for inclusion in contrib, yet it was not included. The argument for not including it was valid, it didn't adhere on several levels including code style and grammatical changes. The point is, if the author would have done the project in public, the problem wouldn't have happen. As I said at the time, people will move on and the project would go to pgfoundry (which I called the graveyard) and die; I would say that is the author's fault. There are plenty of extremely vibrant and lively developed projects on pgfoundry. which is exactly what happened. And, in the Full Disjunctions project's defense, the community was wholly at fault. He posted several times for suggestions and most of the community responses were, why would we care about or want that. Yes *some* of the community didn't understand it but there were others in the community who made a specific effort to explain why it was good, including Josh Berkus. The community can't rely on contributors, especially students, to spend months of their time pushing ideas through a gauntlet of negativity. Someone who wants to provide a feature to the community can't expect the community just to open their arms without explanation and full discussion of a feature. You don't honestly expect a mature open source project to just accept any code do you? For the record, I like the idea of full disjunctions but they must past quality muster to be included in the community. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
On 2/24/07, Joshua D. Drake [EMAIL PROTECTED] wrote: The argument for not including it was valid, it didn't adhere on several levels including code style and grammatical changes. IIRC, the only exception to code style was cleaning up some mixed code/declaration warnings. The point is, if the author would have done the project in public, the problem wouldn't have happen. It was public, very few offered suggestions. I would say that is the author's fault. There are plenty of extremely vibrant and lively developed projects on pgfoundry. He already did over a year and half research on the subject, wrote the code for it, published a paper on it, and offered it to the community. Why would he choose to spend more time getting beaten up for something that's already behind him? Yes *some* of the community didn't understand it but there were others in the community who made a specific effort to explain why it was good, including Josh Berkus. Yes, there were a few. Someone who wants to provide a feature to the community can't expect the community just to open their arms without explanation and full discussion of a feature. Perhaps you don't recall.. but his design and research was far more than almost all other PostgreSQL features. The only one longer was perhaps the HOT design. You don't honestly expect a mature open source project to just accept any code do you? Obviously not. That's just a stupid question to ask. For the record, I like the idea of full disjunctions but they must past quality muster to be included in the community. Again, he offered to fix anything anyone had issues with... but people were too busy whining about the feature itself than to provide sound advice for moving forward. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
He already did over a year and half research on the subject, wrote the code for it, published a paper on it, and offered it to the community. Why would he choose to spend more time getting beaten up for something that's already behind him? If he is not willing to proceed with public debate, I fail to see the purpose in doing the work at all. Perhaps you don't recall.. but his design and research was far more than almost all other PostgreSQL features. The only one longer was perhaps the HOT design. Hmmm and look at HOT. That would be an example of how it *should* be done. HOT has been a constant influx of WIP patches and discussion *to the community* and thusly to rounding out nicely to be a great feature. Full Disjunctions did not do that. For the record, I like the idea of full disjunctions but they must past quality muster to be included in the community. Again, he offered to fix anything anyone had issues with... but people Then why didn't he fix them? There were specific problems people had. were too busy whining about the feature itself than to provide sound advice for moving forward. Guess we will have to agree to disagree. That is not my recollection of the events on any level. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
On 2/24/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Guess we will have to agree to disagree. That is not my recollection of the events on any level. Guess so. This topic ended in August and I'm no longer going to argue about it. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Hi, Here's some feedback, this is a feature that would be very useful to a project I am currently working on. Doug On Fri, 2007-02-23 at 17:34 +0100, Florian G. Pflug wrote: Hi I plan to submit a proposal for implementing support for read-only queries during wal replay as a Google Summer of Code 2007 project. I've been browsing the postgres source-code for the last few days, and came up with the following plan for a implementation. I'd be very interested in any feedback on the propsoal - especially of the you overlooked this an that, it can never work that way kind ;-) greetings, Florian Pflug Implementing read-only quries during wal archive replay --- Submitter: Florian Pflug [EMAIL PROTECTED] Abstract: Implementing full support for read-only queries during wal archive replay is splitted into multiple parts, where each part offeres additional functionality over what postgres provides now. This makes tackling this as a Google Summer of Code 2007 project feasable, and guarantees that at least some progress is made, even if solving the whole problem turns out to be harder then previously thought. Parts/Milestones of the implementation: A) Allow postgres to be started in read-only mode. After initial wal recovery, postgres doesn't perform writes anymore. All transactions started are implicitly in readonly mode. All transactions will be assigned dummy transaction ids, which never make it into the clog. B) Split StartupXLOG into two steps. The first (Recovery) will process only enough wal to bring the system into a consistent state, while the second one (Replay) replays the archive until it finds no more wal segments. This replay happens in chunks, such that after a chunk all *_safe_restartpoint functions return true. C) Combine A) and B), in the simplest possible way. Introduce a global R/W lock, which is taken by the Replay part of B) in write mode before replaying a chunk, then released, and immediatly reaquired before replaying the next chunk. The startup sequence is modified to do only the Recovery part where is is doing StartupXLOG now, and to lauch an extra process (similar to bgwriter) to do the second (Replay) part in the background. The system is then started up in read-only mode, with the addition that the global R/W lock is taken in read mode before starting any transaction. Thus, while a transaction is running, no archive replay happens. Benefits: *) Part A) alone might be of value for some people in the embedded world, or people who want to distribute software the use postgres. You could e.g. distribute a CD with a large, read-only database, and your application would just need to start postmaster to be able to query it directly from the CD. *) Read-only hot standby is a rather simple way to do load-balancing, if your application doesn't depend on the data being absolutely up-to-date. *) Even if this isn't used for load-balancing, it gives the DBA an easy way to check how far a PITR slave is lagging behind, therefore making PITR replication more user-friendly. Open Questions/Problems *) How do read-only transactions obtain a snapshot? Is it sufficient to just create an empty snapshot for them, meaning that they'll always look at the clog to obtain a transaction's state? *) How many places to attempt to issue writes? How hard is it to silence them all while in read-only mode. *) How does the user interface look like? I'm currently leaning towards a postgresql.conf setting read_only=yes. This would put postgres into read-only mode, and if a recovery.conf is present, archive replay would run as a background process. Limitations: *) The replaying process might be starved, letting the slave fall further and further behind the master. Only true if the slave executes a lot of queries, though. *) Postgres would continue to run in read-only mode, even after finishing archive recovery. A restart would be needed to switch it into read-write mode again. (I probably wouldn't be too hard to do that switch without a restart, but it seems better to tackle this after the basic features are working) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Florian G. Pflug [EMAIL PROTECTED] writes: I plan to submit a proposal for implementing support for read-only queries during wal replay as a Google Summer of Code 2007 project. You are discussing this on the wrong list. B) Split StartupXLOG into two steps. The first (Recovery) will process only enough wal to bring the system into a consistent state, How will you know what that is? C) Combine A) and B), in the simplest possible way. Introduce a global R/W lock, which is taken by the Replay part of B) in write mode before replaying a chunk, then released, and immediatly reaquired before replaying the next chunk. That seems certain to result in intolerable performance, for both the queries and the replay process. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: I plan to submit a proposal for implementing support for read-only queries during wal replay as a Google Summer of Code 2007 project. You are discussing this on the wrong list. So what list would be more appropriate? B) Split StartupXLOG into two steps. The first (Recovery) will process only enough wal to bring the system into a consistent state, How will you know what that is? With the same logic that postgres uses now to bring an file-system backup into a consistent state when doing PITR. C) Combine A) and B), in the simplest possible way. Introduce a global R/W lock, which is taken by the Replay part of B) in write mode before replaying a chunk, then released, and immediatly reaquired before replaying the next chunk. That seems certain to result in intolerable performance, for both the queries and the replay process. That depends entirely on the usecase. And besides, this limitation could and probably would be adressed in the future. I think a step-by-step approach is more likely to be successfull then attempting to solve all problems at once. greetings, Florian Pflug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Florian G. Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: You are discussing this on the wrong list. So what list would be more appropriate? My mistake, I read the message header and saw Postgresql-General ... did not look at the actual address ... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Florian G. Pflug wrote: I plan to submit a proposal for implementing support for read-only queries during wal replay as a Google Summer of Code 2007 project. I've been browsing the postgres source-code for the last few days, and came up with the following plan for a implementation. I'd be very interested in any feedback on the propsoal - especially of the you overlooked this an that, it can never work that way kind ;-) I had the same thought roughly two years ago: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01043.php People weren't very interested in having a read-only mode. I think it would be a nice feature if it's not too complicated. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
I'll throw in my vote, I would find this quite useful. -Glen Florian G. Pflug wrote: I plan to submit a proposal for implementing support for read-only queries during wal replay as a Google Summer of Code 2007 project. I've been browsing the postgres source-code for the last few days, and came up with the following plan for a implementation. I'd be very interested in any feedback on the propsoal - especially of the you overlooked this an that, it can never work that way kind ;-) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
People weren't very interested in having a read-only mode. I think it would be a nice feature if it's not too complicated. Actually, I think there's high demand for it off this list. Effectively it would allow our warm backup mode to become a hot backup mode. As SoC admin, I'd vote for such a proposal unless someone explains to me why it's impossible. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 1: 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] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Josh Berkus wrote: People weren't very interested in having a read-only mode. I think it would be a nice feature if it's not too complicated. Actually, I think there's high demand for it off this list. Effectively it would allow our warm backup mode to become a hot backup mode. As SoC admin, I'd vote for such a proposal unless someone explains to me why it's impossible. One thing I would like noted, is whoever does SoC work for PostgreSQL this year, needs to work *with* the community. Otherwise there is no point. A good example of the wrong way to do it is the Full Disjunctions project. Great idea, Great project, not bitrot and hard space because it hasn't been touched or maintained sense release. In order to get it into core, it would have needed a lot of work. Let's make sure we don't duplicate the issue. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
On Fri, Feb 23, 2007 at 10:57:24PM +, Heikki Linnakangas wrote: Florian G. Pflug wrote: I plan to submit a proposal for implementing support for read-only queries during wal replay as a Google Summer of Code 2007 project. I've been browsing the postgres source-code for the last few days, and came up with the following plan for a implementation. I'd be very interested in any feedback on the propsoal - especially of the you overlooked this an that, it can never work that way kind ;-) I had the same thought roughly two years ago: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01043.php People weren't very interested in having a read-only mode. I think it would be a nice feature if it's not too complicated. Every customer I've ever talked to about HA has either asked about it or thought it was a great idea. We should definitely do it if it's not a load of difficult.. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Heikki Linnakangas wrote: Florian G. Pflug wrote: I plan to submit a proposal for implementing support for read-only queries during wal replay as a Google Summer of Code 2007 project. I've been browsing the postgres source-code for the last few days, and came up with the following plan for a implementation. I'd be very interested in any feedback on the propsoal - especially of the you overlooked this an that, it can never work that way kind ;-) I had the same thought roughly two years ago: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01043.php People weren't very interested in having a read-only mode. I think it would be a nice feature if it's not too complicated. I think main feature would be supporting read-only queries on PITR slaves. But creating a read-only mode seemed to me (and to you too, it seems ;-) ) like a good first step towards that goal. After reading tom's reply to your original proposal, I agree that supporting a write-protected datadir is not a true subset of supporting read-only queries on PITR slaves. But I still think that tackling the read-only datadir support is a good first step - not the least because it'll help me to get familiar with the relevent parts of the backend. I've been thinking about your trick of writing readonly into the postmaster.pid file to switch postgres into read-only mode. On the one hand, it's really neat - if solves the problem of not being able to create a pid file in the datadir in ro mode, while on the other hand making sure that there *is* a pid file. But if I went that way, it would mean there would be *three* configfiles you have to get right for a working PITR slave with read-only query support - postgresql.conf, recovery.conf, and postmaster.pid. So I think I'll rather go with a postgresql.conf setting. I'd allow three values hard, soft and off. hard would prevent all writes to the datadir, while soft would be the setting of choice for a PITR slave. In the soft case, postgres would still write a postmaster.pid, and so be protected against other running postmasters. In the hard case, there would be no such protection - but since there would be no writes anyway, you don't risk data corruption in case another postmaster was running - the worst the would happen is that the read-only postmaster crashes. greetings, Florian Pflug ---(end of broadcast)--- TIP 1: 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] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Josh Berkus wrote: People weren't very interested in having a read-only mode. I think it would be a nice feature if it's not too complicated. Actually, I think there's high demand for it off this list. Effectively it would allow our warm backup mode to become a hot backup mode. As SoC admin, I'd vote for such a proposal unless someone explains to me why it's impossible. From my point of view, the most subtle point of the whole proposal is if not doing wal-replay _and_ quries at the same time, but rather one after the other, really solves all the locking issues. My line of reasoning is that stopping wal replay at a arbitrary point, and then starting a read-only transaction with an empty snapshot (meaning that all exactly those transactions marked as comitted in the clog are assumed to be visible to the transaction) is exactly the same as sending the backend a SIGKILL when it just wrote the wal record in question, and then restarting postgres, and starting a transaction. Since later won't lead to problems today, doing the formed for hot standby should not lead to problems too. There reason that I put Process wal in chucks such that all *_safe_restartpoint functions return true at the end of a chunk was the be on the safe side, not because I could find any actual problem if that requirement was dropped. Can anyone find a flaw in those arguments? greetings, Florian Pflug ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)
Florian G. Pflug [EMAIL PROTECTED] writes: My line of reasoning is that stopping wal replay at a arbitrary point, and then starting a read-only transaction with an empty snapshot (meaning that all exactly those transactions marked as comitted in the clog are assumed to be visible to the transaction) is exactly the same as sending the backend a SIGKILL when it just wrote the wal record in question, and then restarting postgres, and starting a transaction. The hole in that reasoning is that no one would be satisfied with the behavior of a Postgres database that was being forcibly restarted every few seconds. Yeah, we won't lose transactions that have been promised committed, but losing a large fraction of transactions-in-progress won't please anyone. Nor will queries on a slave that's behaving like that provide an accurate model of what the same queries would produce if issued on the master. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
