Re: [HACKERS] 8.2 features status
If people are going to start listing features they want here's some things I think would be nice. I have no idea though if they would be useful to anyone else: 1) hierarchical / recursive queries. I realize it's just been discussed at length but since there was some question as to whether or not there's demand for it so I am just weighing in that I think there is. I have to deal with hierarchy tables all the time and I simply have several standard methods of dealing with them depending on the data set / format. But they all suck. I've just gotten use to using the workarounds since there is nothing else. If you are not hearing the screams it's just because I think it's just become a fact of life for most people (unless you're using oracle) that you've just got to work around it. And everyone already has some code to do this and they've already done it everywhere it needs to be done. And as long as you're a little bit clever you can always work around it without taking a big performance hit. But it would sure be nice to have next time I have to deal with a tree table. 2) PITR on a per database basis. I think this would be nice but I'm guessing that the work involved is big and that few people really care or need it, so it will probably never happen. 3) A further refinement of PITR where some sort of deamon ships small log segments as they are created so that the hot standby doesn't have to be updated in 16MB increments or have to wait for some timeout to occur. It could always be up to the minute data. 4) All the Greenplum Bizgress MPP goodness. In reality (and I don't know if bizgress mpp can actually do this) I'd like to have a cluster of cheap boxes. I'd like to install postgres on all of them and configure them in such a way that it automatically partitions and mirrors each table so that each piece of data is always on two boxes and large tables and indexes get divided up intelligently. Sort of like a raid10 on the database level. This way any one box could die and I would be fine. Enormous queries could be handled efficiently and I could scale up by just dropping in new hardware. Maybe greeenplum has done this. Maybe we will get their changes soon enough, maybe not. Maybe this sort of functionality will never happen. My guess is that all the little bit's a pieces of this will trickle in over the next several years and this sort of setup will be slowly converged on over time as lot's of little things come together. Table spaces and constraint exclusion come to mind here as things that could eventually evolve to contribute to a larger solution. 5) Somehow make it so I NEVER HAVE TO THINK ABOUT OR DEAL WITH VACUUM AGAIN. Once I get everything set up right everything works great but I'm sure if there's one thing I think everyone would love it would be getting postgres to the point where you don't even need to ship vacuumdb because there's no way the user could outsmart postgres's attempts to do garbage collection on it's own. 6) genuine updatable views. such that you just add an updatable keyword when you create the view and it's automagically updatable. I'm guessing that we'll get something like that, but its real magic will be throwing an error to tell you when you try to make a view updatable and it can't figure out how to make the rules properly. 7) allow some way to extract the data files from a single database and insert them into another database cluster. In many cases it would be a lot faster to copy the datafiles across the network than it is to dump, copy dump file, reload. 8) some sort of standard hooks to be used for replication. I guess when the replication people all get their heads together and tell the core developers what they all need something like this could evolve. Like I said, postgres more than satisfies my needs. I am especially happy when you factor in the cost of the software (free), and the quality of the community support (excellent). And you can definitely say that the missing list is shrinking. But I think of it like this. There are tiers of database functionality that different people need: A) Correct me if I'm wrong but as great as postgres is there are still people out there that MUST HAVE Oracle or DB2 to get done what they need to get done. They just do things that the others can't. They may be expensive. They may suck to use and administer but the simple fact is that they have features that people need that are not offered in less expensive databases. B) Very, very powerful databases but lack the biggest, most complicated enterprise features. C) Light weight db for taking care of the basic need to store data and query it with sql. (some would call these toy databases) D) databases which are experimental, unreliable or have other limits that make them not practical compared with the other options I
Re: [HACKERS] 8.2 features status
Joshua D. Drake wrote: Frankly, I don't care if we ever get a bug tracker or use trac. However a more formalized communication process is sorely needed IMHO. There's also supposed to be a wiki set up. There, people can try to make up tracking lists, project management, task lists, release goals or whatever on their own. If patterns emerge, we can formalize them, but I feel this would be a good way to try things out. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] interesting article: Leverage your PostgreSQL V8.1 skills to learn DB2, Version
Hello, I found maybe interesting article http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0603wasserman2/ good days Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] interesting article: Leverage your PostgreSQL V8.1 skills to learn DB2, Version
On Sat, Aug 05, 2006 at 10:01:30AM +0200, Pavel Stehule wrote: I found maybe interesting article http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0603wasserman2/ That for the article. I had been meaning to look at DB2, and it gave me a quick summary. What I get from the article, though, is that DB2 is more modularized than PostgreSQL, however, it has concurrency issues. Is this true? Anybody allowed to comment on the peformance of small-sized (~1 million rows) databases? I'm not seeing a compelling reason to switch. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(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] 8.2 features status
[EMAIL PROTECTED] writes: I don't object to someone informally polling people who have claimed a TODO item and not produced any visible progress for awhile. But I think anything like thou shalt report in once a week will merely drive people away from publicly claiming items, if not drive them away from doing anything at all. The former is much more what I had in mind than the latter. Let's do that. Like I said, no objection here. But who exactly is we --- ie, who's going to do the legwork? We surely don't want multiple people pestering the same developer ... Perl has its pumpking ... maybe we need a designated holder of the trunk. I see that as a Core function. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.2 features status
On Sat, Aug 05, 2006 at 12:19:54AM -0400, Matthew T. O'Connor wrote: Robert Treat wrote: So, the things I hear most non-postgresql people complain about wrt postgresql are: no full text indexing built in FTI is a biggie in my mind. I know it ain't happening for 8.2, but is the general plan to integrate TSearch2 directly into the backend? When the Tsearch developers say so I think. This will be the first major release with GIN which will form the basis of future releases of tsearch. IIRC they have a whole list of features they still want to add before it gets included... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] 8.2 features status
Tom Lane wrote: But a quick troll through the CVS logs shows ... multi-row VALUES, not only for INSERT but everywhere SELECT is allowed ... multi-argument aggregates, including SQL2003-standard statistical aggregates ... standard_conforming_strings can be turned on (HUGE deal for some people) ... support SQL-compliant row comparisons; they can be indexscan quals ISTM this could be spun as a standards-focused release as well (at least partial implementations of a number of optional(?) SQL2003 features). ---(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] 8.2 features status
On Fri, Aug 04, 2006 at 06:25:35PM -0700, Joshua D. Drake wrote: I have heard you make this argument before, and it is just is not true. Even Debian is moving toward a more formal structure as has FreeBSD. You seem stuck in this world where everything is still 1994 and all FOSS software is developed in academia. Debian moving towards a more formal structure? What I seeing is that they're trying to get away from the having one person responsible for things to working in groups. What it amounts to is simplifying the rules to doing someone elses work. People who don't like it leave and you hope you're left with a more efficient group. The links you provide are mostly about handling releases. To be honest, I think PostgreSQL's release handling is fine. But none of those projects tackles the issue of making sure certain things get done. If someone didn't do the work for getting GCC 4.1 working for Debian, then no matter how much of a release goal it was, it wouldn't happen... That means you let people know if you are not going to finish something, if you need help, if you can't help, or if you are going to bail on a project. You should also do so with (hopefully) the ability for someone to pick up where you left off. That I can agree with, but I don't think you can force it. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] 8.2 features status
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: I don't object to someone informally polling people who have claimed a TODO item and not produced any visible progress for awhile. But I think anything like thou shalt report in once a week will merely drive people away from publicly claiming items, if not drive them away from doing anything at all. The former is much more what I had in mind than the latter. Let's do that. Like I said, no objection here. But who exactly is we --- ie, who's going to do the legwork? We surely don't want multiple people pestering the same developer ... Perl has its pumpking ... maybe we need a designated holder of the trunk. I see that as a Core function. I can assure you that individual developers were contacted about completing their items for 8.2, to the extent that some developers got upset at me because of my insistence. If they were hired by PostgreSQL companies and I had a relationship with their manager, their managers were informed as well. Jonah, who said the community wasn't clear it wanted his items completed, was part of that group. I see no need to mention the other people I contacted. Many of them completed their items, and Jonah finished some of his items. The fact is, the existing system worked as it should, though it is often invisible. We didn't get all the features we wanted, but that isn't because the system isn't working. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] 8.2 features status
There's also supposed to be a wiki set up. There, people can try to make up tracking lists, project management, task lists, release goals or whatever on their own. If patterns emerge, we can formalize them, but I feel this would be a good way to try things out. Well I will re-extend my offer to put up a trac site for everyone which does contain a wiki. However, last time I offered I believe Marc was actually going to do it. 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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Hierarchical Queries--Status
All, In the spirit of our previous discussion, I am writing to inform you that Mark Cave-Ayland and I will be working on this TODO-item together. We are thinking through a new design (not based on the current patch) and will post it to -hackers for approval soon. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.2 features status
Bruce Momjian wrote: I can assure you that individual developers were contacted about completing their items for 8.2, to the extent that some developers got upset at me because of my insistence. If they were hired by PostgreSQL companies and I had a relationship with their manager, their managers were informed as well. Jonah, who said the community wasn't clear it wanted his items completed, was part of that group. I see no need to mention the other people I contacted. Many of them completed their items, and Jonah finished some of his items. The fact is, the existing system worked as it should, though it is often invisible. We didn't get all the features we wanted, but that isn't because the system isn't working. Thank you Bruce. That is good to know. Maybe the invisibility has led me astray. I'll shut up now and see if I can actually get Enums and some other good stuff done by this time next year. With any luck I won't be quite as derailed as I was last cycle. Also, I hope it's now clear at least that there are many people who want to see recursive queries. cheers andrew ---(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] 8.2 features status
The fact is, the existing system worked as it should, though it is often invisible. We didn't get all the features we wanted, but that isn't because the system isn't working. Well that kind of comes back to my point of better communication. Perhaps a lot of this discussion could have been avoided if the TODO had been more... proactive? For example: Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Confirmed for 8.2 | 07/20/06 I *think* it was peter that did that one, but you see my point. 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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 features status
Martijn van Oosterhout kleptog@svana.org writes: On Sat, Aug 05, 2006 at 12:19:54AM -0400, Matthew T. O'Connor wrote: FTI is a biggie in my mind. I know it ain't happening for 8.2, but is the general plan to integrate TSearch2 directly into the backend? When the Tsearch developers say so I think. Yeah, that's my take too. Oleg and Teodor obviously feel it's not done yet, and ISTM leaving it in contrib gives them more flexibility in a couple of ways: * they can make user-visible API changes without people getting as upset as if they were changing core features; * because it is a removable contrib module, they can (and do) offer back-ports of newer versions to existing PG release branches. I think some descendant of tsearch2 will eventually be in core, but we'll wait till we're pretty certain it's feature-stable. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ecpg test suite
On Fri, Aug 04, 2006 at 12:59:35PM -0400, Tom Lane wrote: *** expected/complex-test4.stdout Wed Aug 2 10:14:02 2006 --- results//complex-test4.stdout Fri Aug 4 12:56:13 2006 *** *** 1,4 ! Found f=14,07 text=0123456789 b=1 Found a[0] = 9 Found a[1] = 8 Found a[2] = 7 --- 1,4 ! Found f=14.07 text=0123456789 b=1 Found a[0] = 9 Found a[1] = 8 Found a[2] = 7 Locale problem. Fixed by setting locale to C. *** expected/pgtypeslib-dt_test.stderrThu Aug 3 09:24:58 2006 --- results//pgtypeslib-dt_test.stderrFri Aug 4 12:56:14 2006 *** *** 22,28 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 37: RESULT: 2000-07-12 17:34:29 offset: 8 array: Yes [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGget_data line 37: RESULT: 13556 days 12:34:00 offset: 12 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGtrans line 354 action = rollback connection = regress1 [NO_PID]: sqlca: code: 0, state: 0 --- 22,28 Some types have different internal sizes on different systems. I wonder what we do with these difference as a log file usually prints this info which is important for debugging sometimes. *** expected/pgtypeslib-dt_test.stdoutWed Aug 2 10:14:03 2006 --- results//pgtypeslib-dt_test.stdoutFri Aug 4 12:56:14 2006 *** *** 41,47 18 October %34 17:28:44 CEST 80, abc%n %C %B %%%d %H:%M:%S %Z %y) = 1880-10-31 15:28:44, error (should be error!): 1 timestamp_defmt_asc(abc 18 October %34 17:28:44 CEST 80, ) = 1880-10-31 15:28:44, error (should be error!): 1 ! timestamp_defmt_asc(1980-04-12 3:49:44 , (null)) = 1980-04-12 03:49:44, error: 0 timestamp_defmt_asc(July 14, 1988. Time: 9:15am, %B %d, %Y. Time: %I:%M%p) = 1988-07-14 09:15:00, error: 0 timestamp_defmt_asc(September 6 at 01:30 pm in the year 1983, %B %d at %I:%M %p in the year %Y) = 1983-09-06 13:30:00, error: 0 timestamp_defmt_asc( 1976, July 14. Time: 9:15am, %Y, %B %d. Time: %I:%M %p) = 1976-07-14 09:15:00, error: 0 --- 41,47 18 October %34 17:28:44 CEST 80, abc%n %C %B %%%d %H:%M:%S %Z %y) = 1880-10-31 15:28:44, error (should be error!): 1 timestamp_defmt_asc(abc 18 October %34 17:28:44 CEST 80, ) = 1880-10-31 15:28:44, error (should be error!): 1 ! timestamp_defmt_asc(1980-04-12 3:49:44 , ) = 1980-04-12 03:49:44, error: 0 timestamp_defmt_asc(July 14, 1988. Time: 9:15am, %B %d, %Y. Time: %I:%M%p) = 1988-07-14 09:15:00, error: 0 timestamp_defmt_asc(September 6 at 01:30 pm in the year 1983, %B %d at %I:%M %p in the year %Y) = 1983-09-06 13:30:00, error: 0 timestamp_defmt_asc( 1976, July 14. Time: 9:15am, %Y, %B %d. Time: %I:%M %p) = 1976-07-14 09:15:00, error: 0 Different compiler gets different output for NULL value. Fixed. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ecpg test suite
Michael Meskes [EMAIL PROTECTED] writes: Some types have different internal sizes on different systems. I wonder what we do with these difference as a log file usually prints this info which is important for debugging sometimes. If there's only a small number of possibilities, you could fix it by treating these as if they were locale differences --- that is, provide multiple expected files test.out, test_1.out, etc. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Updated INSERT/UPDATE RETURNING
Jonah H. Harris [EMAIL PROTECTED] writes: Here's the updated patch with DELETE RETURNING removed. This isn't really an issue because no one wanted DELETE RETURNING to begin with. Huh? Why'd you remove it? I can't imagine it makes things significantly simpler to omit that case, and even if you can't think of uses for it, I can (taking jobs from a to-do queue for instance). BTW, it occurs to me to wonder whether we've picked a good choice of syntax. I don't remember where the suggestion to use RETURNING came from (did we borrow it from another DBMS?). But AFAICS this syntax will require the introducing keyword to be a fully reserved word, and since RETURNING is not listed as a reserved word in the SQL spec, reserving it is arguably a spec violation. The simplest alternative that comes to mind is to use RETURNS instead of RETURNING; since RETURNS is listed as reserved, this doesn't violate spec. OTOH we currently treat RETURNS as an unreserved keyword, and we'd have to promote it to fully reserved. It could be argued that returns is a more likely name for a table column than returning; if so we'd actually be more likely to break existing apps this way. I don't have a strong feeling either way, but now is the time to decide. It is important to note that this patch is not yet ready to be committed. OK, but we need a final version soon. regards, tom lane ---(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] ecpg test suite
On Sat, Aug 05, 2006 at 01:14:25PM -0400, Tom Lane wrote: If there's only a small number of possibilities, you could fix it by treating these as if they were locale differences --- that is, provide multiple expected files test.out, test_1.out, etc. Frankly I have no idea. I was thinking about removing this bit of information from the log if it is a regression test run because it doesn't bring us more information in terms of regresseion testing. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Updated INSERT/UPDATE RETURNING
On 8/5/06, Tom Lane [EMAIL PROTECTED] wrote: Huh? Why'd you remove it? I can't imagine it makes things significantly simpler to omit that case, and even if you can't think of uses for it, I can (taking jobs from a to-do queue for instance). It can be added back. Dequeing is a good use-case idea though :) BTW, it occurs to me to wonder whether we've picked a good choice of syntax. I don't remember where the suggestion to use RETURNING came from (did we borrow it from another DBMS?). Oracle. DB2 uses something similar to SELECT * FROM (UPDATE tbl SET ... ); But AFAICS this syntax will require the introducing keyword to be a fully reserved word, and since RETURNING is not listed as a reserved word in the SQL spec, reserving it is arguably a spec violation. True. The simplest alternative that comes to mind is to use RETURNS instead I don't have a strong feeling either way, but now is the time to decide. I don't care either way, RETURNS is fine I guess. OK, but we need a final version soon. Sure thing. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO system WAS: 8.2 features status
Neil, all: If people are interested in the status of a patch, I think it's fine for them to email the person who's volunteered to work on it. The problem I would like to see resolved is that there is currently no accurate way to determine who is working on a patch except by comprehensive -hackers, -patches, and -performance archive reading. This is a little daunting for people who just joined the community, or who are users just wanting to know if someone is working on a feature they want. I doubt that any TODO system would have 100% participation, and I know that it would depend on having some non-hacker volunteers updating the information on behalf of developers who didn't want to use it. However, I think that getting those volunteers is entirely possible (for example, PWN is inculding a weekly patch list and it's not much more effort to check off those patches against a web-based TODO list). If the system reflected 70% of current development activity, then I think it would be a big improvement over the current read 100% of the mail archives for three mailing lists back one year to find out what's going on. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: pg_upgrade (was: [HACKERS] 8.2 features status)
Rick, The objective is to smoothly upgrade to the new version with minimal downtime. Thanks for jumping in. The different proposals as far as I can see are as follows: Proposal A - the big one time reformatting 1) shutdown the db 2) run a command that upgrades the data directory to the new format 3) start up the new postgres version with the new data dir Pros: only pg_upgrade (or whatever it's called) needs to know about the old and new formats, each version of postgres knows about it's format and that's it. The postgres code stays clean cons: your database is down while the upgrade takes place. This sucks because the people who need this are the same people who are trying to avoid downtime. It's faster than a dump/reload but it doesn't completely solve the problem, it just mitigates it. Yes, but it mitigates it *considerably.* The general idea is that doing an in-place binary upgrade should take 80% less time, and require only 10% (or so) extra disk space. This would be sufficient for most users with large databases; they can afford to be down from midnight to 3 am but not to be down for the whole weekend (as dump/reload requires for a 3tb database) nor do they have 100% + extra disk space and 20% extra CPU on the machine (as upgrade-by-replication requires). Plus, there are versions (like 8.2 looks to be) where there is *no* change in the file format. For these, pg_upgrade would need just to bootstrap the system, swap the system tables and header files, and restart. Seconds instead of hours. As PostgreSQL matures further, I predict that there will be more releases with no change in the file format, making this even more useful. Proposal B - the gradual upgrade 1) shutdown the db 2) start it back up with the new version of postgres 3) the new postgres version upgrades things in place as needed This is impractical, because the number of version-to-version compatibility libraries required will grow geometrically with time. We don't have to just accomodate the last version, but the last 4+ versions. Proposal C - PITR with in on the fly disk upgrades 1) setup PITR 2) run pg_upgrade on your latest backed up data directories 3) start up the new pg on that data directory in restartable recovery / read-only / hot-standby mode 4) update the recovery log importer so that it can update the log files on the fly as it applies them 5) failover to the hot standby as you normally would I don't think this is practical. Simon? -- Josh Berkus PostgreSQL @ Sun San Francisco ---(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] TODO system WAS: 8.2 features status
Josh Berkus wrote: I doubt that any TODO system would have 100% participation, and I know that it would depend on having some non-hacker volunteers updating the information on behalf of developers who didn't want to use it. However, I think that getting those volunteers is entirely possible (for example, PWN is inculding a weekly patch list and it's not much more effort to check off those patches against a web-based TODO list). If the system reflected 70% of current development activity, then I think it would be a big improvement over the current read 100% of the mail archives for three mailing lists back one year to find out what's going on. Yup, thats exactly what I experienced when I started a TODO list for the PHP project. A few developers wanted to get direct access, others use me as their proxy and then others do not care about the list at all. But it has been very helpful to the community to hear whats going on. It has let to some people joining the development or suggesting possible implementation options and more importantly it has reduced the work load of the release managers. This is not a task that requires a lot of technical know how and is therefore a great way for some non C hacker to contribute to the project. regards, Lukas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.2 features status
Tom Lane wrote: I tend to agree --- I don't see much value in trying to institute a formalized process. One more problem with the formalized process of claiming features in advance may stop what I suspect is a significant source of contributions -- people who add features/patches for internal work in their company and only after the fact find that they are something they'd contribute back. The small contribution I made (to help admins know when FSM settings were too low by monitoring log files instead of manual checks[1]) was done because we wanted it internally. Only after it proved useful to us, it was mentioned to the lists. Thanks in part to the BSD nature of postgresql, I suspect there are many internal-and-not-yet-released useful patches lurking around in industry. If I'm right, I'd wonder what the advocacy guys could do to get corporations to volunteer to contribute changes back that they've found useful internally. We have not had that many cases where lack of communication was a problem. One could say too much communication was the problem this time. I get the impression people implied they'd do something on a TODO and didn't. Arguably the project had been better off if noone had claimed the TODO, so if another company/team/whatever needed the feature badly, they could have worked on it themselves rather than waiting in hope of the feature. Of course they could have done this anyway - but if they see it on an implied roadmap document for the next release they're more likely to wait. Ron [1] http://archives.postgresql.org/pgsql-patches/2005-02/msg00171.php ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Updated INSERT/UPDATE RETURNING
Jonah H. Harris [EMAIL PROTECTED] writes: On 8/5/06, Tom Lane [EMAIL PROTECTED] wrote: BTW, it occurs to me to wonder whether we've picked a good choice of syntax. I don't remember where the suggestion to use RETURNING came from (did we borrow it from another DBMS?). Oracle. DB2 uses something similar to SELECT * FROM (UPDATE tbl SET ... ); Oh, okay. The simplest alternative that comes to mind is to use RETURNS instead I don't have a strong feeling either way, but now is the time to decide. I don't care either way, RETURNS is fine I guess. Well, if the syntax is compatible with Oracle as-is, that's probably a sufficient reason to stick with it. It's not like we haven't got any other non-spec reserved words ... regards, tom lane ---(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] 8.2 features status
Ron Mayer wrote: We have not had that many cases where lack of communication was a problem. One could say too much communication was the problem this time. I get the impression people implied they'd do something on a TODO and didn't. Arguably the project had been better off if noone had claimed the TODO, so if another company/team/whatever needed the feature badly, they could have worked on it themselves rather than waiting in hope of the feature. Of course they could have done this anyway - but if they see it on an implied roadmap document for the next release they're more likely to wait. This is just perverse. Surely you are not seriously suggesting that we should all develop in secret and then spring miracles fully grown on the community? We have bumped patches before because they have done things without discussing them, and were found not to be accepatble. The more complex features get, the more communication is needed. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.2 features status
[EMAIL PROTECTED] wrote: Ron Mayer wrote: We have not had that many cases where lack of communication was a problem. One could say too much communication was the problem this time. I get the impression people implied they'd do something on a TODO and didn't. Arguably the project had been better off if noone had claimed the TODO, so if another company/team/whatever needed the feature badly, they could have worked on it themselves rather than waiting in hope of the feature. This is just perverse. Surely you are not seriously suggesting that we should all develop in secret and then spring miracles fully grown on the community? Of course not. What I'm suggesting is two things. (1) That misleading information is worse than no information; and that speculative information next to TODOs can do as much harm discouraging others as it the good it does for communication. Perhaps a name/assignment/claim on a todo might be nice if someone wanted a private conversation with someone who knows about a feature; but even there wouldn't a public discussion on the lists likely be better? (2) That much corporate development on BSD projects is indeed developed in secret. Although may want to be contributed later either because the company no longer decides it's a trade-secret or gets tired of maintaining their own fork. Sure, such patches might need even more discussion and revision than if they were designed with core - but I think it's a reality that such work exists. We have bumped patches before because they have done things without discussing them, and were found not to be accepatble. The more complex features get, the more communication is needed. Agreed, of course. This makes me think that ongoing discussion on hackers patches is the only way to judge progress on a todo; and anything like assigned names estimated dates releases are less likely to be meaningful than what one could infer from discussions on the lists. ---(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
[HACKERS] Corner case in xlog stuff: what happens exactly at a seg boundary?
I'm noticing that if the current XLOG offset is exactly at a segment boundary (ie, the last wal record just filled the segment) then the various user-level functions return offsets that could be interpreted as the start of the next segment, eg regression=# select pg_switch_xlog(); pg_switch_xlog 0/1400 (1 row) regression=# select pg_xlogfile_from_wal_location(pg_switch_xlog()); pg_xlogfile_from_wal_location --- 00010014 (1 row) regression=# Actually, the last segment file that needs to be archived in this scenario is 0013; it's possible 0014 doesn't even exist yet. Rather than expecting user-level scripts to get this corner case right, I suggest that we ought to modify pg_stop_backup and friends so that what they return is the last used byte address of WAL, not the first unused byte address as now. Then, blindly extracting the filename will give the right answer about which file to archive, even in the boundary case. Comments? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 features status
Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: On Sat, Aug 05, 2006 at 12:19:54AM -0400, Matthew T. O'Connor wrote: FTI is a biggie in my mind. I know it ain't happening for 8.2, but is the general plan to integrate TSearch2 directly into the backend? When the Tsearch developers say so I think. Yeah, that's my take too. Oleg and Teodor obviously feel it's not done yet, and ISTM leaving it in contrib gives them more flexibility in a couple of ways: * they can make user-visible API changes without people getting as upset as if they were changing core features; * because it is a removable contrib module, they can (and do) offer back-ports of newer versions to existing PG release branches. I think some descendant of tsearch2 will eventually be in core, but we'll wait till we're pretty certain it's feature-stable. My impression from this post http://archives.postgresql.org/pgsql-hackers/2006-07/msg00556.php was that moving it into core should be doable for 8.3. I hope I didn't misunderstand. cheers andrew ---(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: pg_upgrade (was: [HACKERS] 8.2 features status)
Josh Berkus josh@agliodbs.com writes: Proposal C - PITR with in on the fly disk upgrades 1) setup PITR 2) run pg_upgrade on your latest backed up data directories 3) start up the new pg on that data directory in restartable recovery / read-only / hot-standby mode 4) update the recovery log importer so that it can update the log files on the fly as it applies them 5) failover to the hot standby as you normally would I don't think this is practical. Simon? I don't think there is very much hope of an in-place upgrade for scenarios involving changes in contents of user tables. In particular, what of a change that requires more space than before, such as adding a locale indicator to text fields? There's no guarantee that the data on an old page will still fit, and there's certainly no hope of something operating at the xlog level being able to move tuples across pages --- if nothing else, because it's not in a position to compute new index entries. I don't see this working for page-at-a-time updates even in a full backend environment; again, indexes are the killer consideration. I don't see how to get sane behavior from an index containing some old-style entries and some new-style ones for a changed datatype. As you mentioned, the scenarios that look practical for in-place upgrade are the ones where only system catalog contents need to change. We've already discussed this (many times) and agreed that we could live with restricting user-table changes to happen only once every few releases. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 features status
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Jim C. Nasby) transmitted: What say? It's a shame to have a person burn cycles on this, but anything would be an improvement over what we've got now. Anything includes some options that would probably *not* be improvements. I'm not sure that pestering everyone once a week would be a particularly good move. That's too likely to get silly unrealistic estimates as to how much is done. (Entirely typical in run-by-the-calendar projects project managed by Big Five consulting firms...) On the flip side, I don't think it is unreasonable to expect to hear *something* once a month or every two months on ToDo items that have been assigned. With the proviso that if no news is heard in several months, that surely suggests that the item isn't progressing, and might deserve others' attention... -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://linuxdatabases.info/info/linuxdistributions.html Rules of the Evil Overlord #79. If my doomsday device happens to come with a reverse switch, as soon as it has been employed it will be melteddown and made into limited-edition commemorative coins. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.2 features status
Quoth [EMAIL PROTECTED] (David Fetter): On Fri, Aug 04, 2006 at 02:37:56PM -0700, Neil Conway wrote: On Fri, 2006-08-04 at 12:40 -0700, David Fetter wrote: While I am not going to reopen the can of worms labeled 'bug tracker', I think it would be good to have a little more formality as far as claiming items goes. What say? I think this is a good plan for adding additional process overhead, and getting essentially nothing of value in return. I'm not convinced there's a problem in need of solving here... Perhaps you'd like to explain how big a burden on the developer it is to send an once a week, that being what I'm proposing here. As far as the problem in need of solving, it's what Andrew Dunstan referred to as splendid isolation, which is another way of saying, letting the thing you've taken on gather dust while people think you're working on it. It seems to me once a week is a bit too often to demand, particularly when trying to herd cats. A burden of once a month may seem more reasonable. -- let name=cbbrowne and tld=gmail.com in name ^ @ ^ tld;; http://linuxdatabases.info/info/nonrdbms.html If you pick up a starving dog and make him prosperous, he will not bite you; that is the principal difference between a dog and a man. -- Mark Twain ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Forcing current WAL file to be archived
Simon Riggs [EMAIL PROTECTED] writes: Patch included to implement xlog switching, using an xlog record processing instruction and forcibly moving xlog pointers. Applied with revisions. I didn't like the extra state you added to track whether an xlog switch had occurred --- the more bits of interdependent state the more chance for bugs, IMHO, and it seemed unnecessary since it's easy enough to test whether we are at a segment boundary. I also made the new user-level functions a bit more orthogonal, so that filenames could be extracted from the existing functions like pg_stop_backup. regards, tom lane ---(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] Corner case in xlog stuff: what happens exactly at a seg boundary?
I wrote: Rather than expecting user-level scripts to get this corner case right, I suggest that we ought to modify pg_stop_backup and friends so that what they return is the last used byte address of WAL, not the first unused byte address as now. Then, blindly extracting the filename will give the right answer about which file to archive, even in the boundary case. After further thought I desisted from that plan: changing the result convention of existing functions like pg_stop_backup() will break any existing archiving scripts that do get it right. Instead, we can put the boundary-case logic into the new functions that extract a filename from the WAL location string that the action functions return. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org