Re: [HACKERS] Release Notes: Major Changes in 8.2
Simon Riggs napsal(a): Improved monitoring and performance tuning (Tom, Bruce, Greg, Larry) Overhead of statistics collection has been considerably reduced and new statistics and system information is available. Better query logging improves diagnostics and especially performance tuning. Server now includes DTrace support. Indexes can now also be created CONCURRENTLY, allowing application tuning without effecting server availability. You forgot to Robert Lor - author of DTrace support. Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 'configure --disable-shared' and 'make check'
Peter Eisentraut wrote: I notice that when I run 'make check' on a statically linked HEAD, it fails during 'createlang' with Because createlang relies on *dynamic* loading. So that is working as designed. I interpret that as 'static builds for the database server are not supported'. Ok by me. Yours, Laurenz Albe ---(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] TODO: Fix CREATE CAST on DOMAINs
On Wed, Sep 20, 2006 at 10:56:08AM -0700, Mark Dilger wrote: If the system chooses cast chains based on a breadth-first search, then the existing int2 - int8 cast would be chosen over an int2 - int4 - int8 chain, or an int2 - int3 - int4 - int8 chain, or in fact any chain at all, because the int2 - int8 cast is the shortest. But we're not talking about a search here, we don't always know where the endpoint is. Imagine you have the following three functions: abs(int8) abs(float4) abs(numeric) And you have an int2. Which is the best cast to use? What's the answer if you have a float8? What if it's an unknown type text string? Now, consider that functions can have up to 32 arguments and that this resolution might have to be applied to each argument and you find that searching is going to get very expensive very quickly. The current system of requiring only a single step is at least predictable. If you have the choice between: - first argument matches, second needs three safe conversions, and - first argument need one unsafe conversion, second matches exactly Which is cheaper? To make this manageable you have to keep the number of types you can cast to small, or you'll get lost in the possibilites. Adding just a single step domain to base type conversion seems pretty safe, but anything more is going to be hard. 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] Release Notes: Major Changes in 8.2
On Wed, 2006-09-20 at 23:22 -0400, Bruce Momjian wrote: Usually the major items just jump out of the release list. In this case, nothing really jumped out, and I felt if I listed sereral, it was going to look weak because they were not big things, so I figured I would just go with the broad list. Look back at the 7.4 release notes as a comparison. I think 8.0 was such a milestone release we tend to judge ourselves by that and maybe feel like the pace has slackened. IMHO, it has accelerated. We hit the lower hanging fruit first, so early features were major items; later items seem smaller and less important by comparison, especially when completed by a team rather than a few individuals. I don't think it matters whether the new features originated as a single patch or as a stream of smaller patches. The end result is a major improvement in a specific area. Picking one area I'm more familiar with, sort performance was increased over many patches by many people, but the original objective of making a step-change in that area *has* been achieved (even if there are some additional gains still to be had for certain narrower use-cases). The role of the Major changes section is to provide a summary for administrators who need to understand what a new release will give them and make a cost/benefit judgement. We want people to understand the good work that has been done and that does involve some filtering and summarization, and its possibly true that it is harder in this release than others. We need a Major changes section: People don't read the detail: sysadmins are too busy these days. If there are no major features listed, people will assume there are none and say oh its just a bug fix release. If we aren't encouraging people to upgrade, why release at all? Maybe people only upgrade every other release - if so, we'll get all of the 8.0 upgraders. Improving scalability in 8.1 was great. Improving it again in 8.2 is amazing and we should tell people, even if it sounds somewhat boring because we did it last time as well. I think: again, wow, this software is going places. Personally, I'll be ecstatic if we can do that again for 8.3... Or perhaps we can do more broad-stroke list items, like monitoring or performance, as listed below. Whether we like my list or not, I think such a grouped list should exist. I'm mainly seeking to persuade you on that point and would be comfortable even if you came up with a different grouped list. Seeing a list of names after a topic emphasises the community development process. In some cases, there was a stated objective and that has been achieved. In other cases there was a community-driven move in directions maybe we didn't predict. In the latter case, surely it is the strength of open source that evolution works so well and really does produce noticeably major changes. The changes in monitoring and tuning tools is an excellent example: many smaller changes making a significant improvement. Please vote in favour of a Major Changes section. -- Simon Riggs EnterpriseDB 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: [PATCHES] [HACKERS] Incrementally Updated Backup
True, but running several dozen instances on a single machine will require a lot more memory (or, conversely, each individual database gets a lot less memory to use). Of course, this is all hand-waving right now... it'd be interesting to see which approach was actually better. I'm running 4 WAL logging standby clusters on a single machine. While the load on the master servers occasionally goes up to 60, the load on the standby machine have never climbed above 5. Of course when the master servers are all loaded, the standby gets behind with the recovery... but eventually it gets up to date again. I would be very surprised if it would get less behind if I would use it in the 1 by 1 scenario. Cheers, Csaba. ---(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] Phantom Command ID
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: A big question is, do we need to implement spilling to disk? My thought is no, at least not in the first cut ... this is something that can be added later if it proves critical, and right at the moment my guess is that it never will. The data structure design sounds fine. I thought so too. We could also limit the size of the hash table, which takes up most of the memory, and only keep the latest phantom cids there. Presumably, if current command id is 1000, you're not likely to set cmax to 500 on any tuple in that transaction anymore. -- Heikki Linnakangas EnterpriseDB 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] Phantom Command ID
Another question is, what should cmin and cmax system columns return? If we overlay cmin and cmax, cmin and cmax on-disk will always be the same value. And with phantom cids, it wouldn't be meaningful outside the inserting/deleting transaction. The options that I can think of are: 1. Only return cmin and cmax when they mean something, that is within the inserting / deleting transaction. This is not good if you want to use them for debugging (and what other use do they have?) 2. Cmin and cmax return the value that's stored on disk, whether or not they make sense. 3. Remove cmin and cmax system columns to avoid confusion, and replace them with cminmax, that returns what's on disk. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Add documentation for new in-core advisory
On Wed, 2006-09-20 at 20:43 -0300, Tom Lane wrote: Log Message: --- Add documentation for new in-core advisory lock functions. Merlin Moncure Modified Files: -- pgsql/doc/src/sgml: func.sgml (r1.338 - r1.339) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/func.sgml.diff?r1=1.338r2=1.339) mvcc.sgml (r2.62 - r2.63) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/mvcc.sgml.diff?r1=2.62r2=2.63) Could we/should we make mention that this feature was provided as user locks in previous versions of PostgreSQL, even if that specific module has now been moved. The release notes don't mention that the advisory locks feature has been added either. -- Simon Riggs EnterpriseDB 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] Phantom Command ID
Heikki Linnakangas [EMAIL PROTECTED] writes: We could also limit the size of the hash table, which takes up most of the memory, and only keep the latest phantom cids there. Presumably, if current command id is 1000, you're not likely to set cmax to 500 on any tuple in that transaction anymore. The downside of that though is that if you did generate any such, you'd assign a fresh (duplicate) phantom cid --- so you're bloating the array in exchange for reducing the hash size. It is quite easy to have current command counter much greater than the CID of a still-live command: consider for example an UPDATE that is firing triggers as it goes, and each trigger executes some queries. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 'configure --disable-shared' and 'make check'
Albe Laurenz [EMAIL PROTECTED] writes: Peter Eisentraut wrote: Because createlang relies on *dynamic* loading. So that is working as designed. I interpret that as 'static builds for the database server are not supported'. Ok by me. Well, we're not supporting dynamically linked objects in a static build ;-) It's at least theoretically possible that you could link selected PL objects into a static backend build, but no one is particularly interested in expending effort on it. There don't seem to be any platforms anymore on which --disable-shared is actually important. (Should we just get rid of it?) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Index bloat problem in 7.4
I am aware that more recent versions 8.x have fixed this problem, I checked the 7.4 release notes but can't see if any of the fixes made it into 7.4. Did they ? Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] advisory locks and permissions
Bruce Momjian [EMAIL PROTECTED] writes: Doesn't creating many temp tables in a transaction do the same thing? True, but it's a tad harder/less likely that you'd accidentally cause a problem that way. I'm not sure if I'm crying wolf or whether there's a serious issue. Certainly, if you have SQL-command access there are plenty of ways to cause DoS situations of varying levels of severity. An admin who is concerned about this can revoke public access on the functions for himself ... but should that be the default out-of-the-box configuration? I feel more comfortable with saying you have to turn on this potentially-dangerous feature than with saying you have to turn it off. Another reason for restricting access to the advisory-lock functions is that an uninformed application might take the wrong locks, and bollix up your intended usage accidentally. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 'configure --disable-shared' and 'make check'
Tom Lane wrote: It's at least theoretically possible that you could link selected PL objects into a static backend build, but no one is particularly interested in expending effort on it. There don't seem to be any platforms anymore on which --disable-shared is actually important. (Should we just get rid of it?) There is just one case where I think static linking might make some sense: pg_dump for use during an upgrade. Unfortunately, at least on Linux I found it to be close to impossible, as it uses some things that are apparently only available dynamically - I forget the details. So basically, I would say yes, let's just drop --disable-shared. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] advisory locks and permissions
Le jeudi 21 septembre 2006 01:52, Tom Lane a écrit : Or we could try to do something about limiting the number of such locks that can be granted, but that seems nontrivial to tackle at such a late stage of the devel cycle. Thoughts? What about reserving some amount of shared_buffers out of those locks? (For example ext2 preserve some disk space for root in case of emergency) Don't know anything about how easily (error prone) this can be done, though. Le jeudi 21 septembre 2006 16:22, Tom Lane a écrit : Another reason for restricting access to the advisory-lock functions is that an uninformed application might take the wrong locks, and bollix up your intended usage accidentally. This sounds like one more attempt to protect against idiots, which universe tend to produce on a pretty quick rate :) My 2¢, -- Dimitri Fontaine Directeur Technique Tel: 06 74 15 56 53 pgpnt2Cy8pGIV.pgp Description: PGP signature
Re: [HACKERS] Phantom Command ID
Heikki Linnakangas [EMAIL PROTECTED] writes: Another question is, what should cmin and cmax system columns return? If we're going to fool with these, I'd like to renew the suggestion I made awhile back that none of the system columns should have explicit entries in pg_attribute, but rather their lookup should be special-cased in the parser. And whatever we do with cmin/cmax, the infomask should become exposed as well. 2. Cmin and cmax return the value that's stored on disk, whether or not they make sense. This is pretty much the approach we've been taking with the past overlay hacks --- what is returned is not always what you might expect from the column header. I think this is tolerable as long as the infomask can be examined to determine what's really being shown, but it's probably not the cleanest way. 3. Remove cmin and cmax system columns to avoid confusion, and replace them with cminmax, that returns what's on disk. Don't forget it could be xvac or cphantom too ;-). I think I agree with this approach but not that particular name exactly. I'm inclined to suggest that we just continue to use cmin for the field --- cmax could be dropped or become an alias for cmin. A fourth possibility is to abandon the rule that these columns never read as null, and to have them show their contents when meaningful (as determined by infomask) and null otherwise. However, then we'd have to support all of cmin, cmax, cphantom, and xvac in order to ensure that we always have a column that can show the on-disk value. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 'configure --disable-shared' and 'make check'
Am Donnerstag, 21. September 2006 16:02 schrieb Tom Lane: There don't seem to be any platforms anymore on which --disable-shared is actually important. (Should we just get rid of it?) IIRC, I added it in the past to test for static-only platforms like QNX. I don't know if we plan to support such platforms in the future. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] advisory locks and permissions
* Tom Lane ([EMAIL PROTECTED]) wrote: An admin who is concerned about this can revoke public access on the functions for himself ... but should that be the default out-of-the-box configuration? I feel more comfortable with saying you have to turn on this potentially-dangerous feature than with saying you have to turn it off. I agree with having it turned off by default, at least in 8.2. Once it's out there and in use we can review that decision for 8.3 and possibly implement appropriate safeguards based on the usage. Another reason for restricting access to the advisory-lock functions is that an uninformed application might take the wrong locks, and bollix up your intended usage accidentally. This begs for a mechanism to define who can take what locks, etc.. Which seems to be an 8.3 issue. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Release Notes: Major Changes in 8.2
Josh Berkus wrote: Bruce, What happened to PL/pgSQL debugging? Did it die? The debuggers is going to be on pgfoundry, if it isn't there already. The idea is that it would be loadable for 8.2, work out all the bugs, and perhaps included in 8.3. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Release Notes: Major Changes in 8.2
OK, I will work it. --- Simon Riggs wrote: On Wed, 2006-09-20 at 23:22 -0400, Bruce Momjian wrote: Usually the major items just jump out of the release list. In this case, nothing really jumped out, and I felt if I listed sereral, it was going to look weak because they were not big things, so I figured I would just go with the broad list. Look back at the 7.4 release notes as a comparison. I think 8.0 was such a milestone release we tend to judge ourselves by that and maybe feel like the pace has slackened. IMHO, it has accelerated. We hit the lower hanging fruit first, so early features were major items; later items seem smaller and less important by comparison, especially when completed by a team rather than a few individuals. I don't think it matters whether the new features originated as a single patch or as a stream of smaller patches. The end result is a major improvement in a specific area. Picking one area I'm more familiar with, sort performance was increased over many patches by many people, but the original objective of making a step-change in that area *has* been achieved (even if there are some additional gains still to be had for certain narrower use-cases). The role of the Major changes section is to provide a summary for administrators who need to understand what a new release will give them and make a cost/benefit judgement. We want people to understand the good work that has been done and that does involve some filtering and summarization, and its possibly true that it is harder in this release than others. We need a Major changes section: People don't read the detail: sysadmins are too busy these days. If there are no major features listed, people will assume there are none and say oh its just a bug fix release. If we aren't encouraging people to upgrade, why release at all? Maybe people only upgrade every other release - if so, we'll get all of the 8.0 upgraders. Improving scalability in 8.1 was great. Improving it again in 8.2 is amazing and we should tell people, even if it sounds somewhat boring because we did it last time as well. I think: again, wow, this software is going places. Personally, I'll be ecstatic if we can do that again for 8.3... Or perhaps we can do more broad-stroke list items, like monitoring or performance, as listed below. Whether we like my list or not, I think such a grouped list should exist. I'm mainly seeking to persuade you on that point and would be comfortable even if you came up with a different grouped list. Seeing a list of names after a topic emphasises the community development process. In some cases, there was a stated objective and that has been achieved. In other cases there was a community-driven move in directions maybe we didn't predict. In the latter case, surely it is the strength of open source that evolution works so well and really does produce noticeably major changes. The changes in monitoring and tuning tools is an excellent example: many smaller changes making a significant improvement. Please vote in favour of a Major Changes section. -- Simon Riggs EnterpriseDB 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 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Release Notes: Major Changes in 8.2
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: 21 September 2006 16:25 To: Josh Berkus Cc: pgsql-hackers@postgresql.org; Simon Riggs Subject: Re: [HACKERS] Release Notes: Major Changes in 8.2 Josh Berkus wrote: Bruce, What happened to PL/pgSQL debugging? Did it die? The debuggers is going to be on pgfoundry, if it isn't there already. The idea is that it would be loadable for 8.2, work out all the bugs, and perhaps included in 8.3. We've also discussed bundling the GUI with pgAdmin for 1.8 (which will be released with 8.3) so that idea could work out nicely. Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Add documentation for new in-core advisory
Simon Riggs [EMAIL PROTECTED] writes: The release notes don't mention that the advisory locks feature has been added either. I think Bruce only has the notes synced up to about a week ago ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Add documentation for new
Simon Riggs wrote: On Wed, 2006-09-20 at 20:43 -0300, Tom Lane wrote: Log Message: --- Add documentation for new in-core advisory lock functions. Merlin Moncure Modified Files: -- pgsql/doc/src/sgml: func.sgml (r1.338 - r1.339) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/func.sgml.diff?r1=1.338r2=1.339) mvcc.sgml (r2.62 - r2.63) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/mvcc.sgml.diff?r1=2.62r2=2.63) Could we/should we make mention that this feature was provided as user locks in previous versions of PostgreSQL, even if that specific module has now been moved. The release notes don't mention that the advisory locks feature has been added either. Release date: CURRENT AS OF 2006-09-18 They need to be updated. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] large object regression tests
Jeremy Drake [EMAIL PROTECTED] writes: I put together a patch which adds a regression test for large objects, hopefully attached to this message. I would like some critique of it, to see if I have gone about it the right way. Also I would be happy to hear any additional tests which should be added to it. I'd prefer it if we could arrange not to need any absolute paths embedded into the test, because maintaining tests that require such is a real PITA --- instead of just committing the actual test output, one has to reverse-convert it to a .source file. I suggest that instead of testing the server-side lo_import/lo_export functions, perhaps you could test the psql equivalents and write and read a file in psql's working directory. I think we could do without the Moby Dick extract too ... 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] TODO: Fix CREATE CAST on DOMAINs
Martijn van Oosterhout wrote: On Wed, Sep 20, 2006 at 10:56:08AM -0700, Mark Dilger wrote: If the system chooses cast chains based on a breadth-first search, then the existing int2 - int8 cast would be chosen over an int2 - int4 - int8 chain, or an int2 - int3 - int4 - int8 chain, or in fact any chain at all, because the int2 - int8 cast is the shortest. But we're not talking about a search here, we don't always know where the endpoint is. Imagine you have the following three functions: abs(int8) abs(float4) abs(numeric) And you have an int2. Which is the best cast to use? What's the answer if you have a float8? What if it's an unknown type text string? Now, consider that functions can have up to 32 arguments and that this resolution might have to be applied to each argument and you find that searching is going to get very expensive very quickly. The current system of requiring only a single step is at least predictable. If you have the choice between: - first argument matches, second needs three safe conversions, and - first argument need one unsafe conversion, second matches exactly Which is cheaper? To make this manageable you have to keep the number of types you can cast to small, or you'll get lost in the possibilites. Adding just a single step domain to base type conversion seems pretty safe, but anything more is going to be hard. Have a nice day, The searching never needs to be done at runtime. It should be computable at cast creation time. A new cast creates a potential bridge between any two types in the system. Using a shortest path algorithm, the best chain (if any exists) from one type to another can be computed and pre-compiled, right? So, assume the following already exists: Types A,B,C, fully connected with casts A-B, B-A, A-C, C-A, B-C, C-B, with some marked IMPLICIT, some marked EXPLICIT, and some marked SAFE. Types X,Y,Z, also fully connected with casts, as above. Then assume someone comes along and creates a new type M with conversions A-M, M-A, X-M, and M-X. At the time that type and those casts are added to the system, the system could calculate any additional casts to/from B, C, Y, and Z. A simple implementation (but maybe not optimal) would be for the system to autogenerate code like: CREATE FUNCTION cast_M_Y (arg M) RETURNS Y AS $$ SELECT arg::X::Y; $$ LANGUAGE SQL; CREATE CAST (M AS Y) WITH FUNCTION cast_M_Y(M) [ AS ASSIGNMENT | AS IMPLICIT ] And then load that function and cast. The only real trick seems to be determining the rules for which cast chain gets used within that autogenerated function, and whether the generated cast is IMPLICIT, EXPLICIT, or ASSIGNMENT. Looking over what I have just written, another idea pops up. To avoid having the system decide which casts are reasonable, you could extend the syntax and allow an easy shorthand for the user. Something like: CREATE CAST (M AS A) WITH FUNCTION cast_M_A AS ASSIGNMENT PROPOGATES TO B AS ASSIGNMENT, PROPOGATES TO C AS ASSIGNMENT; CREATE CAST (A AS M) WITH FUNCTION cast_A_M AS ASSIGNMENT PROPOGATES FROM B, PROPOGATES FROM C; And then the casts from M-B, M-C, B-M, and C-M would all be added to the system. Thoughts? mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Cause of moving-target FSM space-needed reports
We've seen reports occasionally of how the system reports that max_fsm_pages needs to be increased to at least X, and then when the DBA does so, it complains that some larger amount is needed --- one recent report is here: http://archives.postgresql.org/pgsql-admin/2006-06/msg00176.php I finally got around to looking at this, and I see what is going on. The reported number is based on the aggregate of request page counts passed by VACUUM to the FSM module. However, vacuumlazy.c is prefiltering its requests to at most MaxFSMPages, because it knows that no more than that many pages will be accepted anyway. So if you have a single table containing more than max_fsm_pages pages with interesting amounts of free space, the reported total is being artificially constrained, and then when you relax the constraint, the reported total jumps up. In the case Jeff describes where it kept saying max_fsm_pages plus 2608 were needed, I suppose he had boatloads of free space in one table (probably pg_largeobject) and exactly 2608 interesting pages in all other tables. I think it's reasonable for vacuumlazy.c to track at most MaxFSMPages pages as it's doing now --- but it should keep a separate count of the total number of pages with at least threshold amount of free space, and pass that as a separate argument to RecordRelationFreeSpace. This will not take any more space in shared memory than we already use, but it will allow us to report a truthful value for number of pages needed, which we clearly are failing to do now. It might also be a good idea if vacuum verbose reported this page count, since when you've got a single table bloated like this, VACUUM FULL or CLUSTER might be a more appropriate solution than increasing the FSM size --- but there's no way to know which rel is the problem from the FSM total. In fact, maybe vacuum should just throw a WARNING when it finds a single rel with more than MaxFSMPages pages with useful free space? Comments? I'd like to put in a fix for beta1, which means today ... 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] Release Notes: Major Changes in 8.2
Bruce, All: The debuggers is going to be on pgfoundry, if it isn't there already. The idea is that it would be loadable for 8.2, work out all the bugs, and perhaps included in 8.3. So, should I take this off the press list for 8.2 and save it for 8.3, when the feature will be actually useful? Second question: are the Advisory Locks actually a unique PostgreSQL feature, or are these something other databases already have? --Josh Berkus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Index bloat problem in 7.4
Hi, Dave, Dave Cramer wrote: I am aware that more recent versions 8.x have fixed this problem, I checked the 7.4 release notes but can't see if any of the fixes made it into 7.4. Usually, only critical data loss and security fixes are put into the minor updates (e. G. 7.4.0 to 7.4.13). So, if it's not in the release notes, it is unlikely that it dit make it into the 7.4 releases. You may try backporting it yourself, or use a cronjob or such issuing regular REINDEX commands during the night (or whenever you have idle hours). HTH; Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Cause of moving-target FSM space-needed reports
Hi, Tom, Tom Lane wrote: I think it's reasonable for vacuumlazy.c to track at most MaxFSMPages pages as it's doing now --- but it should keep a separate count of the total number of pages with at least threshold amount of free space, and pass that as a separate argument to RecordRelationFreeSpace. This will not take any more space in shared memory than we already use, but it will allow us to report a truthful value for number of pages needed, which we clearly are failing to do now. It might also be a good idea if vacuum verbose reported this page count, since when you've got a single table bloated like this, VACUUM FULL or CLUSTER might be a more appropriate solution than increasing the FSM size --- but there's no way to know which rel is the problem from the FSM total. In fact, maybe vacuum should just throw a WARNING when it finds a single rel with more than MaxFSMPages pages with useful free space? +1 for both from my side, it has bitten me and our admins several times now. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Cause of moving-target FSM space-needed reports
Tom Lane wrote: I think it's reasonable for vacuumlazy.c to track at most MaxFSMPages pages as it's doing now --- but it should keep a separate count of the total number of pages with at least threshold amount of free space, and pass that as a separate argument to RecordRelationFreeSpace. This will not take any more space in shared memory than we already use, but it will allow us to report a truthful value for number of pages needed, which we clearly are failing to do now. It might also be a good idea if vacuum verbose reported this page count, since when you've got a single table bloated like this, VACUUM FULL or CLUSTER might be a more appropriate solution than increasing the FSM size --- but there's no way to know which rel is the problem from the FSM total. In fact, maybe vacuum should just throw a WARNING when it finds a single rel with more than MaxFSMPages pages with useful free space? Comments? I'd like to put in a fix for beta1, which means today ... Sounds reasonable - it's arguably a bug, albeit relatively benign. I guess it might be less likely in 8.2 anyway given that we will have more generous default max_fsm_pages settings in most cases. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release Notes: Major Changes in 8.2
On Thu, Sep 21, 2006 at 11:24:53AM -0400, Bruce Momjian wrote: Josh Berkus wrote: Bruce, What happened to PL/pgSQL debugging? Did it die? The debuggers is going to be on pgfoundry, if it isn't there already. The idea is that it would be loadable for 8.2, work out all the bugs, and perhaps included in 8.3. But didn't we end up putting some hooks in the backend to make this possible? Regardless, I think we should include a section of major new projects/developments from pgFoundry, because they ultimately make PostgreSQL a more useful database. Maybe this list should only be in the PR (and not the release notes)... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Cause of moving-target FSM space-needed reports
Tom Lane wrote: In fact, maybe vacuum should just throw a WARNING when it finds a single rel with more than MaxFSMPages pages with useful free space? I fully agree with this. This (in particular, how many useful free pages a rel has) is information a good DBA can make very good use of, and is needed in the case where it exceeds MaxFSMPages. I would also suggest having vacuum verbose print an INFO message with the rel's number of free pages for rels that don't exceed MaxFSMPages (that number should be printed in the WARNING when the number exceeds MaxFSMPages). Are there other ways of getting this information from the system? If not, then I'd consider this proposed change important. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_upgrade: downgradebility
On 9/20/06, Josh Berkus josh@agliodbs.com wrote: Supposedly someone from EnterpriseDB is working on this as well; where are you? Josh, We're currently working on the design docs and a beta. Denis, Faiz, and/or Ahsan will post here when it's posted. You should expect some activity in this area in the next two weeks or so. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_upgrade: downgradebility
Jonah, We're currently working on the design docs and a beta. Denis, Faiz, and/or Ahsan will post here when it's posted. You should expect some activity in this area in the next two weeks or so. So, are you saying that you already have code? If not, care to open up the process? We have others interested in contributing. -- --Josh 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] pg_upgrade: downgradebility
On 9/21/06, Josh Berkus josh@agliodbs.com wrote: So, are you saying that you already have code? Yes. If not, care to open up the process? We have others interested in contributing. We'll be opening it in the next few weeks. A large majority of it is working but we want to solidify the design and current issues before releasing it for comment and suggestion. -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] 'configure --disable-shared' and 'make check'
Tom Lane wrote: Well, we're not supporting dynamically linked objects in a static build ;-) It's at least theoretically possible that you could link selected PL objects into a static backend build, but no one is particularly interested in expending effort on it. There don't seem to be any platforms anymore on which --disable-shared is actually important. (Should we just get rid of it?) ISTR having to use --disable-shared to get a working profile-able build on some platforms (might have been Solaris 8 with sun complier, but I'm not really sure as it was a while ago). Cheers Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Release Notes: Major Changes in 8.2
Josh Berkus wrote: Bruce, All: The debuggers is going to be on pgfoundry, if it isn't there already. The idea is that it would be loadable for 8.2, work out all the bugs, and perhaps included in 8.3. So, should I take this off the press list for 8.2 and save it for 8.3, when the feature will be actually useful? Yes, I think so. Second question: are the Advisory Locks actually a unique PostgreSQL feature, or are these something other databases already have? Probably not unique. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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] Release Notes: Major Changes in 8.2
[EMAIL PROTECTED] (Bruce Momjian) writes: Josh Berkus wrote: Bruce, What happened to PL/pgSQL debugging? Did it die? The debuggers is going to be on pgfoundry, if it isn't there already. The idea is that it would be loadable for 8.2, work out all the bugs, and perhaps included in 8.3. If we now have the hooks in place, then it is surely worth saying so. To then point people to pgFoundry for an add-on debugger application seems pretty fair. -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://linuxdatabases.info/info/finances.html Rules of the Evil Overlord #133. If I find my beautiful consort with access to my fortress has been associating with the hero, I'll have her executed. It's regrettable, but new consorts are easier to get than new fortresses and maybe the next one will pay attention at the orientation meeting. http://www.eviloverlord.com/ ---(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] Release Notes: Major Changes in 8.2
Simon Riggs wrote: SQL:2003 Analytical functions (Sergey, Tom, Neil) All statistical aggregate functions defined by SQL:2003 are now supported and user-defined aggregates now can take multiple columns as inputs. Could this be a good starting point for SQL:2003 Window functions as now the work on SQL:2003 statistical functions are done? As experienced postgres developers what would be your roadmap to implement window functions? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Index bloat problem in 7.4
Markus Schaber wrote: Hi, Dave, Dave Cramer wrote: I am aware that more recent versions 8.x have fixed this problem, I checked the 7.4 release notes but can't see if any of the fixes made it into 7.4. Usually, only critical data loss and security fixes are put into the minor updates (e. G. 7.4.0 to 7.4.13). So, if it's not in the release notes, it is unlikely that it dit make it into the 7.4 releases. Except it was solved in 7.4: Allow B-tree index compaction and empty page reuse (Tom) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_upgrade: downgradebility
Jonah H. Harris [EMAIL PROTECTED] writes: On 9/21/06, Josh Berkus josh@agliodbs.com wrote: If not, care to open up the process? We have others interested in contributing. We'll be opening it in the next few weeks. A large majority of it is working but we want to solidify the design and current issues before releasing it for comment and suggestion. I see more and more people falling into the trap of doing a lot of work *before* showing their ideas to the community. This is an excellent plan if what you are hoping to accomplish is to waste a lot of work. Otherwise, you'll do a lot better to get the community involved sooner. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_upgrade: downgradebility
Jonah H. Harris wrote: On 9/21/06, Josh Berkus josh@agliodbs.com wrote: So, are you saying that you already have code? Yes. If not, care to open up the process? We have others interested in contributing. We'll be opening it in the next few weeks. A large majority of it is working but we want to solidify the design and current issues before releasing it for comment and suggestion. I don't understand why anybody thinks this makes any sense. You know, if I could post the Mammoth Replicator code for comment, I'd do it as soon as possible, design flaws and coding bugs included. It would help me get early feedback on dumb or bright decisions, so that I don't waste time pursuing fixes that are not invasive enough, or that are too invasive. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] advisory locks and permissions
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Doesn't creating many temp tables in a transaction do the same thing? True, but it's a tad harder/less likely that you'd accidentally cause a problem that way. Then why not use a GUC (that only an administrator can set) to control the maximum number of advisory locks a given backend can take at any one time? Then it becomes the DBA's problem (and solution) if someone manages to run the database out of shared memory through this mechanism. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Cause of moving-target FSM space-needed reports
On Thu, 21 Sep 2006, Tom Lane wrote: We've seen reports occasionally of how the system reports that max_fsm_pages needs to be increased to at least X, and then when the DBA does so, it complains that some larger amount is needed --- one recent report is here: http://archives.postgresql.org/pgsql-admin/2006-06/msg00176.php I finally got around to looking at this, and I see what is going on. The reported number is based on the aggregate of request page counts passed by VACUUM to the FSM module. However, vacuumlazy.c is prefiltering its requests to at most MaxFSMPages, because it knows that no more than that many pages will be accepted anyway. So if you have a single table containing more than max_fsm_pages pages with interesting amounts of free space, the reported total is being artificially constrained, and then when you relax the constraint, the reported total jumps up. In the case Jeff describes where it kept saying max_fsm_pages plus 2608 were needed, I suppose he had boatloads of free space in one table (probably pg_largeobject) and exactly 2608 interesting pages in all other tables. It was indeed pg_largeobject that caused all the grief. I have since put these settings in pg_autovacuum: vsl_cs=# select * from pg_autovacuum ; vacrelid | enabled | vac_base_thresh | vac_scale_factor | anl_base_thresh | anl_scale_factor | vac_cost_delay | vac_cost_limit --+-+-+--+-+--++ 2613 | t | 150 | 0.1 | 75 | 0.05 | -1 | -1 (1 row) And it seems much happier these days: INFO: free space map contains 299025 pages in 144 relations DETAIL: A total of 296160 page slots are in use (including overhead). 296160 page slots are required to track all free space. Current limits are: 5366499 page slots, 2000 relations, using 31572 KB. Before the more aggressive autovacuum settings, we would have the problem crop up again when a researcher would delete several large objects at once. Apparently, each large object is around 80MB in size, so I suspect it's not difficult to overwhelm autovacuum if you remove quite a few of these at one time. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release Notes: Major Changes in 8.2
I created a major features list for 8.2 and put it into CVS. Instead of going into detail (meaning the item would not appear in the Changes section below, I just highlighted some of the big stuff, and was purposely vague about the details, so people just have an overview of what is below. Let me know how it looks. Simon's list below looks good, but it really has a lot of details, particuarly it goes into use-cases for many of the features, and in fact goes into more detail that we even have in the release notes now. Is that what people want? My concern is that if we push too much information, it is hard to see the actual features, i.e. if we say, we have feature X, and it is good for Y, Z, and Q do people remember Y and Z and forget X? Again, I don't want to be the person writing these release notes, so I am looking for feedback, good or bad. --- Simon Riggs wrote: I'd like to include a section on Major changes in this release at the top of the release notes, as has been done for at least the last 6 major releases. The notes below are one stab at that, for **discussion**. I've tried to arrange specific changes into groups... Major changes in this release: Improved scalability and performance on multi-processor systems (Tom, Alvaro, Itagaki, Qingqing, Heikki) A variety of changes improves the performance of both sequential scans and index scans, as well as enhancing multi-processor scalability. The advanced query optimizer has also been further enhanced, allowing indexes and partitioning to be useful in more cases. Improved utility and large query performance (Tom, Simon, Alon, Andreas) Large sorts will have typical performance increases of 100-300%, improving complex queries and creating new indexes. Loading times have also been reduced. Large queries, data loads, upgrades and restores will be considerably improved. Improved monitoring and performance tuning (Tom, Bruce, Greg, Larry) Overhead of statistics collection has been considerably reduced and new statistics and system information is available. Better query logging improves diagnostics and especially performance tuning. Server now includes DTrace support. Indexes can now also be created CONCURRENTLY, allowing application tuning without effecting server availability. Zero administration overhead now possible (Alvaro) With autovacuum enabled, all required vacuuming will now take place without administrator intervention enabling wider distribution of embedded databases. Improved defaults and configuration (Peter, Andrew) Installation defaults are now improved for many tunable memory parameters and these can now be specified in kB, MB and GB. Warm Standby Servers for High Availability (Simon, Tom) Warm Standby servers can now be more easily configured and are appropriate in a wider range of circumstances than previously. Improved scalability and performance of text search: GIN and Tsearch2 (Teodor, Oleg) New GIN indexes allow much larger text search indexes than were previously possible. TSearch2 has been enhanced and performance has also been greatly improved. Enhanced DML Functionality (Jonah, Joe, Tom, Susanne, Atsushi) INSERT/UPDATE/DELETE RETURNING and INSERT .. VALUES (), VALUES (), VALUES () allow more efficient application designs. Enhancements to UPDATE and DELETE allow additional constructs for clarity and ease of use. SQL:2003 Analytical functions (Sergey, Tom, Neil) All statistical aggregate functions defined by SQL:2003 are now supported and user-defined aggregates now can take multiple columns as inputs. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_upgrade: downgradebility
On 9/21/06, Tom Lane [EMAIL PROTECTED] wrote: I see more and more people falling into the trap of doing a lot of work *before* showing their ideas to the community. This is an excellent plan if what you are hoping to accomplish is to waste a lot of work. Otherwise, you'll do a lot better to get the community involved sooner. Thanks for your opinion, but I disagree completely. -- 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] pg_upgrade: downgradebility
On 9/21/06, Alvaro Herrera [EMAIL PROTECTED] wrote: You know, if I could post the Mammoth Replicator code for comment, I'd do it as soon as possible, design flaws and coding bugs included. It's easy to say something on the contrary when you know you can't back it up to begin with. Before everyone grabs their pitchforks, I would like to comment that this was going to be a strictly commercial product and as such, had already been through some design. We decided to open source it for everyone and get community involvement, so excuse us if we're going to spend just a little more time on it. I don't think I've seen anyone actually propose a design and state they were going to work on it (at least until 8.3)... so it's funny that I'm trying to follow the new, keep the community informed policy and yet everyone is still not happy enough. In short, our design is based on many of the pg_upgrade concepts but rewritten in C... if you have any blatant faults in pg_upgrade, that's the best place to start discussion. -- 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 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] pg_upgrade: downgradebility
Jonah H. Harris wrote: On 9/21/06, Tom Lane [EMAIL PROTECTED] wrote: I see more and more people falling into the trap of doing a lot of work *before* showing their ideas to the community. This is an excellent plan if what you are hoping to accomplish is to waste a lot of work. Otherwise, you'll do a lot better to get the community involved sooner. Thanks for your opinion, but I disagree completely. History in this community has basically shown that although you disagree you are completely wrong. Good luck with your project. 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 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] pg_upgrade: downgradebility
Jonah, I don't think I've seen anyone actually propose a design and state they were going to work on it (at least until 8.3)... so it's funny that I'm trying to follow the new, keep the community informed policy and yet everyone is still not happy enough. Ooops, didn't mean to start a which-hunt. To be fair, I e-mailed Denis about the project before it came up here, and he didn't respond (still hasn't). The current pgFoundry project is fine if this is going to be some proprietary code which EDB is going to drop on the community. However, that runs a significant risk of major design issues which will cause the project to never be used, and to be replaced with something else. Even the name of the pgFoundry project, as proposed, pretty much prohibits serious contributions from anyone else: it's EnterpriseDB Migrator, not pg_upgrade. So I think the thing to decide is whether this is EDB freeware or a real community OSS effort. I think that we'll be happy to have either, but right now it's in some grey area. --Josh Berkus ---(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] pg_upgrade: downgradebility
On 9/21/06, Josh Berkus josh@agliodbs.com wrote: Ooops, didn't mean to start a which-hunt. Not you're fault :) So I think the thing to decide is whether this is EDB freeware or a real community OSS effort. I think that we'll be happy to have either, but right now it's in some grey area. We certainly want it to be a community effort, we're just cleaning up the design right now. The code itself will be posted as-is for anyone to break, patch, and enhance. Even if the design is the only thing the community latches onto, improves, and drives into a new working pg_upgrade, that would be a good thing. We're just asking for a little patience. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_upgrade: downgradebility
We would help if we do it in an open pgfoundry project. Bizgres? - Luke Msg is shrt cuz m on ma treo -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Thursday, September 21, 2006 11:08 PM Eastern Standard Time To: Jonah H. Harris Cc: pgsql-hackers@postgresql.org; Andrew Sullivan Subject:Re: [HACKERS] pg_upgrade: downgradebility Jonah, I don't think I've seen anyone actually propose a design and state they were going to work on it (at least until 8.3)... so it's funny that I'm trying to follow the new, keep the community informed policy and yet everyone is still not happy enough. Ooops, didn't mean to start a which-hunt. To be fair, I e-mailed Denis about the project before it came up here, and he didn't respond (still hasn't). The current pgFoundry project is fine if this is going to be some proprietary code which EDB is going to drop on the community. However, that runs a significant risk of major design issues which will cause the project to never be used, and to be replaced with something else. Even the name of the pgFoundry project, as proposed, pretty much prohibits serious contributions from anyone else: it's EnterpriseDB Migrator, not pg_upgrade. So I think the thing to decide is whether this is EDB freeware or a real community OSS effort. I think that we'll be happy to have either, but right now it's in some grey area. --Josh Berkus ---(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 ---(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] pg_upgrade: downgradebility
Jonah, We certainly want it to be a community effort, we're just cleaning up the design right now. The code itself will be posted as-is for anyone to break, patch, and enhance. Even if the design is the only thing the community latches onto, improves, and drives into a new working pg_upgrade, that would be a good thing. We're just asking for a little patience. OK ... can we re-name the project, please, though? It's going to be hard for employees of other companies to contribute to the EnterpriseDB Migrator. Maybe PGMigrator with EnterpriseDB leading off the discription? --Josh Berkus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_upgrade: downgradebility
On 9/21/06, Josh Berkus josh@agliodbs.com wrote: OK ... can we re-name the project, please, though? It's going to be hard for employees of other companies to contribute to the EnterpriseDB Migrator. Maybe PGMigrator with EnterpriseDB leading off the discription? I can't personally make that call but I'll discuss it with everyone tomorrow and get back to you. -- 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 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] pg_upgrade: downgradebility
Jonah H. Harris [EMAIL PROTECTED] writes: In short, our design is based on many of the pg_upgrade concepts but rewritten in C... if you have any blatant faults in pg_upgrade, that's the best place to start discussion. Well, actually, there's a reason why pg_upgrade isn't in the CVS tree anymore ... it never quite worked and for many releases has been in absolutely no danger of working. So you've not exactly allayed my concerns by stating the above. I suggest again that you ought to be running your basic design past the list sooner rather than later. There are a lot of smart people on this list --- they can help you not make mistakes. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Feature request (was psql: absolutes and toggles)
Dropping -general. On Sep 14, 2006, at 5:33 PM, Steve Crawford wrote: I would like the ability to absolutely set parameters/settings in psql so that our psql scripts could generate predictable output absent a known or controllable initial state. Original discussion at bottom of message. One alternate and easier approach I've thought of is to simply add something akin to a \factory-reset meta-command which would return all settings to the state they would be in immediately after starting psql with the --no-psqlrc option. This would at least provide one solution to the problem and might be a handy meta-command even if absolute settings were added. If a factory reset meta-command were added I think that \o should be exempted as it is already an absolute setting that can be predictably used in scripts and, where output redirection isn't specified in the script, we shouldn't interfere with the ability to save the output of a script or scripts as the user desires. I remember some discussion about a connection-level reset, but I don't think it would apply to psql. Another way to deal with this would be to add a command that allows you to definitively set any setting, ie: \set timing = on I can see value in both options. BTW, it probably wouldn't be terribly difficult to figure out how to do the \set option. You'd have to see how commands 'plug in' to the interface (just look at any other command as an example, preferably one that takes an option), and see how options are actually set (ie: look at \timing). Coming up with a partial patch and asking for help is likely to get this done a lot sooner than just sticking it on the TODO. Peter Eisentraut wrote: Steve Crawford wrote: We create psql scripts that can be used at various times by various users. I have been unable to find how to absolutely set various options (timing, expanded, etc.) rather than toggle them. The --no-psqlrc option provides a partial workaround - as long as the user remembers to include it and as long as they are only running the one script. But if they forget or if they are already running a session there is no telling what settings have been toggled by previously run scripts or the users themselves. So...have I overlooked an interactive psql option that will let me reset all options to factory-defaults or a method of specifying an absolute setting to the various options? Probably not. If not, do psql users out there feel this is worth a feature request? I think so. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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