Re: [HACKERS] Autovacuum in the backend
Josh Berkus wrote: Josh, Just my own two cents. First I am not knocking the work that has been on autovacuum. I am sure that it was a leap on its own to get it to work. However I will say that I just don't see the reason for it. I've personally seen at least a dozen user requests for autovacuum in the backend, and had this conversation about 1,100 times: NB: After a week, my database got really slow. Me: How often are you running VACUUM ANALYZE? NB: Running what? Can't argue that except... RTFM ;). I am not saying it doesn't have a validity. I am just saying that if you actually pay attention to PostgreSQL and maintain it, you don't need it ;) Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum in the backend
Matthew T. O'Connor wrote: Joshua D. Drake wrote: Just my own two cents. First I am not knocking the work that has been on autovacuum. I am sure that it was a leap on its own to get it to work. However I will say that I just don't see the reason for it. The major reasons for autovacuum as I see it are as follows: * Reduces administrative overhead having to keep track of what tables need to be vacuumed how often. Creates more overhead and thus reduces performance. * Reduces the total amount of time the system spends vacuuming since it only vacuums when needed. Can be easily done with cron. * Keeps stats up-to-date automatically Which can be done with cron * Eliminates newbie confusion RTFM * Eliminates one of the criticisms that the public has against PostgreSQL (justifed or not) Agreed. Just so everyone knows from the get go here. I am purposely playing a little devils advocate. Autovacuum has some drawbacks. I think we should be **publicly** aware of them before we pursue integration. Heaven knows it would make my life easier if it was integrated but anyway... Sincerely, Joshua D. Drake Also, as VACUUM improves, autovacuum will improve with it. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Autovacuum in the backend
* Reduces the total amount of time the system spends vacuuming since it only vacuums when needed. Can be easily done with cron. * Keeps stats up-to-date automatically Which can be done with cron * Eliminates newbie confusion RTFM * Eliminates one of the criticisms that the public has against PostgreSQL (justifed or not) Agreed. I few weeks ago I have set up a database with more than 1.800 tables (some complex business thing). inventing a clever cron-vacuum strategy is almost impossible (or at least very painful). there should be a mechanism (fortunately there is pg_autovacuum) to make this a bit more practical. in case of small databases this is not an issue. small is always simple. complex and large are the major challenges. best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Escape handling in strings
Note that issuing warnings due to normal DML SQL queries is much more severe than the typical DDL warnings. Many people have queries strewn throughout the application so updating them may be a *lot* of work. And for an app issuing hundreds or thousands of queries per minute (or even second) a warning could effectively be a showstopper. It could require disabling all warnings in their config to avoid filling their disk with Postgres logs in minutes. I would suggest this warning be disable-able with a GUC variable. Otherwise you're effectively giving no advance warning time to those users. If postgres keeps advancing at the pace it's advancing now I might suggest waiting two release cycles instead of just one. Judging by the mailing list it seems a lot of users aren't able to keep up with the Postgres development team and are often upgrading two versions at a time. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Escape handling in strings
On Jun 17, 2005, at 4:34 PM, Greg Stark wrote: And for an app issuing hundreds or thousands of queries per minute (or even second) a warning could effectively be a showstopper. It could require disabling all warnings in their config to avoid filling their disk with Postgres logs in minutes. Good point. I would suggest this warning be disable-able with a GUC variable. Otherwise you're effectively giving no advance warning time to those users. Perhaps NOTICE would be better, at least for the first step? People might be more comfortable with that, as using backslash escaping isn't really going to cause problems with this particular version, but rather for future versions. If postgres keeps advancing at the pace it's advancing now I might suggest waiting two release cycles instead of just one. How's this for an idea? Step 1 (8.1) NOTICE level (or some other level, lower than WARNING), E'' and \' are available Step 2 (8.2?) WARNING level, E'' and \' are available Step 3 (8.3? 8.4?) E'' available, plain '' interpreted literally. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
One related idea that I have been meaning to moot for a while now though, is that of a 'utility' database. One of the problems we've always had in pgAdmin (and presumably phpPgAdmin as well), is that the only database we know exists with any reasonable surety is template1, and consequently, this is the default database that pgAdmin connects to. There are obvious problems with this - in particular: - Newbies may not realise the significance of making their initial experiments in template1 - Administrators may not want users connecting to template1 - We don't want to create utility objects in template1 to offer enhanced functionality in the client. To overcome this, a alternative database created by initdb would be very useful. This would be roughly the equivalent of SQL Server's 'msdb' database and would allow: - A default non-template database for apps to connect to initially - A standard place for apps like pgAgent to store their cluster-specific configuration data - A standard place for apps like pgAdmin to store utility objects What are peoples thoughts on this? I think this is a very good idea. I've come up against this need once or twice before.. And the fact that stuff in template1 gets propagated out to all newly created databases can be a major pain when this happens. A shared database for this stuff would be great - then each tool could just create a schema for it's own stuff. How does pgAdmin deal with this today? //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
I also think it is useful and make things easier. A connection on template1 also prevent others to create new databases. connection1: template1#= connection2: foo=# create database bar; ERROR: source database template1 is being accessed by other users ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Autovacuum in the backend
Qingqing Zhou wrote: Tom Lane [EMAIL PROTECTED] writes: Yeah --- a libpq-based solution is not what I think of as integrated at all, because it cannot do anything that couldn't be done by the existing external autovacuum process. About all you can buy there is having the postmaster spawn the autovacuum process, which is slightly more convenient to use but doesn't buy any real new functionality. One reason of not using lib-pq is that this one has to wait for the completion of each vacuum (we don't has async execution in libpq right?), There *is* async execution in libpq, and it works. Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Magnus Hagander wrote: fer enhanced functionality in the client. To overcome this, a alternative database created by initdb would be very useful. This would be roughly the equivalent of SQL Server's 'msdb' database and would allow: - A default non-template database for apps to connect to initially - A standard place for apps like pgAgent to store their cluster-specific configuration data - A standard place for apps like pgAdmin to store utility objects What are peoples thoughts on this? I think this is a very good idea. I've come up against this need once or twice before.. And the fact that stuff in template1 gets propagated out to all newly created databases can be a major pain when this happens. A shared database for this stuff would be great - then each tool could just create a schema for it's own stuff. How does pgAdmin deal with this today? Not at all. pgAdmin II did store some information in the current db, pgAdmin III remembers everything locally. Extended feature functions are taken from the initial DB, by default template1 (most of them need to be in the db under investigation anyway). I'd be glad to see the utility database, this would unleash several ideas (e.g. a profiling agent I have in mind). Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
I think this is a very good idea. I've come up against this need once or twice before.. And the fact that stuff in template1 gets propagated out to all newly created databases can be a major pain when this happens. A shared database for this stuff would be great - then each tool could just create a schema for it's own stuff. How does pgAdmin deal with this today? In phpPgAdmin the default db to connect to can be specified per-server in the config file. It defaults to template1. It actually is not relevant at all which db it is, so long as they can connect to it. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Christopher Kings-Lynne wrote: In phpPgAdmin the default db to connect to can be specified per-server in the config file. It defaults to template1. It actually is not relevant at all which db it is, so long as they can connect to it. I wonder how many users actually change that value for php/pgadmin or simply leave it default. Observing myself, 10 % I'd guess. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Autovacuum in the backend
The major reasons for autovacuum as I see it are as follows: * Reduces administrative overhead having to keep track of what tables need to be vacuumed how often. Creates more overhead and thus reduces performance. Or reduces vacuum overhead because the vacuum strategy is much better than it was when you used cron. Especially as people get a chance to improve autovac. * Reduces the total amount of time the system spends vacuuming since it only vacuums when needed. Can be easily done with cron. Can you do partial table vacuums with CRON? You can work out the smartest time to vacuum with cron? I thought it just scheduled tasks at certain times. * Keeps stats up-to-date automatically Which can be done with cron An what is the management strategy for adjusting analyze when things change that you weren't aware of? (eg, big table changes that were unexpected) * Eliminates newbie confusion RTFM RTFM = MySQL in a lot of cases to be honest. * Eliminates one of the criticisms that the public has against PostgreSQL (justifed or not) Agreed. This is really the same as the previous RTFM question/response. People criticise because vacuum is foreign to them, and for newbie's that equals too hard, next db please. As much as it is a technical issue, it's an advocacy issue too. Plus we finally get XID wraparound protection. We finally decided that for 8.1 we needed some protection, which I think Tom committed. This again may be a newbie thing. But there are a lot of newbies out there then. We've see on the lists and on IRC this problem pop up a number of times. And people say Why didn't it tell me, RTFM it's exactly what they want to hear, or the fact they thought they read the manual, and missed understanding that bit. Just so everyone knows from the get go here. I am purposely playing a little devils advocate. Autovacuum has some drawbacks. I think we should be **publicly** aware of them before we pursue integration. It does have a number of issues. But I feel the integration issue is being addressed with a very short term view. Once it's integrated there are a lot of patches, tweaks and changes that just can't be made until it is integrated. The usefulness of some of the vacuum ideas that have been presented in the past will be able to become a reality. The dead space map is a perfect example. People have talked about it for most of the time I've been around. But until we have an integrated vacuum none of that can really happen. Heaven knows it would make my life easier if it was integrated but anyway... I understand these are not nessecarily Josh's view, but I thought I would offer comments on them. Sincerely, Joshua D. Drake Regards Russell Smith Also, as VACUUM improves, autovacuum will improve with it. Or because of autovacuum, vacuum and autovacuum will improve. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Autovacuum in the backend
Added to TODO: * Create a bitmap of pages that need vacuuming Instead of sequentially scanning the entire table, have the background writer or some other process record pages that have expired rows, then VACUUM can look at just those pages rather than the entire table. In the event of a system crash, the bitmap would probably be invalidated. Further to this, is there any use case for allowing FSM, or this DSM to spill to disk if the space fills up. It would allow the possibility of unusual changes to the db to not loose space. You could just load part of the overflow from the disk back int the FSM in memory and continue using free space. Regards Russell Smith ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
In phpPgAdmin the default db to connect to can be specified per-server in the config file. It defaults to template1. It actually is not relevant at all which db it is, so long as they can connect to it. I wonder how many users actually change that value for php/pgadmin or simply leave it default. Observing myself, 10 % I'd guess. Only people who ever change it are those whose dba's have disallowed connections to template1. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum in the backend
4) Related to this, I guess, is that a user's FSM settings might be completely inappropriate. The 'Just read the manual' or 'Just read the logs' argument doesn't cut it, because the main argument for autovacuum in the backend is that people do not and will not. Agreed, it doesn't solve all problems, and I'm not arguing that the integration of AV makes PostgreSQL newbie safe it just helps reduce the newbie problem. Again if the default FSM settings are inappropriate for a database then the user is probably doing something more complicated that a my cat minka database and will need to learn some tuning skills anyway. 5) It doesn't actually shrink tables -- ie, there's no VACUUM FULL. If we're telling users about VACUUM less often than we are now, there's bound to be bloating issues (see 4). But what's stopping the implementation of a Partial VACUUM FULL, where we lock the table, move enough blocks to shorten the relation so that there is say 10% bloat, or whatever is appropriate for that table. Or even just short the table a few block, and repeat the process when you have some time too. Not totally true, regular VACUUM can shrink tables a little (I think only if there is free space at the end of the table it can cutoff without moving data around). But if AV is on and the settings are reasonable, then a table shouldn't bloat much or at all. Also, I don't think we are telling people to VACUUM less, in fact tables that need it will usually get VACUUM'd more, we are just telling the users that if they turn AV on, they don't have to manage all the VACUUMing. Regards Russell Smith ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum in the backend
On Fri, 17 Jun 2005 06:26 pm, Andreas Pflug wrote: Qingqing Zhou wrote: Tom Lane [EMAIL PROTECTED] writes: Yeah --- a libpq-based solution is not what I think of as integrated at all, because it cannot do anything that couldn't be done by the existing external autovacuum process. About all you can buy there is having the postmaster spawn the autovacuum process, which is slightly more convenient to use but doesn't buy any real new functionality. One reason of not using lib-pq is that this one has to wait for the completion of each vacuum (we don't has async execution in libpq right?), There *is* async execution in libpq, and it works. I would have thought the main reasons for not using libpq means you are locked into only using commands that are available to all users via SQL. If you don't use libpq, you open up the ability to use functions that can make use of information available to the backend, and to also run functions in a way that it is not possible to do via SQL. Regards Russell Smith. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Autovacuum in the backend
On Fri, 17 Jun 2005, Russell Smith wrote: 4) Related to this, I guess, is that a user's FSM settings might be completely inappropriate. The 'Just read the manual' or 'Just read the logs' argument doesn't cut it, because the main argument for autovacuum in the backend is that people do not and will not. Agreed, it doesn't solve all problems, and I'm not arguing that the integration of AV makes PostgreSQL newbie safe it just helps reduce the newbie problem. Again if the default FSM settings are inappropriate for a database then the user is probably doing something more complicated that a my cat minka database and will need to learn some tuning skills anyway. 5) It doesn't actually shrink tables -- ie, there's no VACUUM FULL. If we're telling users about VACUUM less often than we are now, there's bound to be bloating issues (see 4). But what's stopping the implementation of a Partial VACUUM FULL, where we lock the table, move enough blocks to shorten the relation so that there is say 10% bloat, or whatever is appropriate for that table. Or even just short the table a few block, and repeat the process when you have some time too. Its a question of where you start off from again. You cannot just say 'I've vacuumed the first 100 pages' because it could well have changed underneath you. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum in the backend
On Fri, 17 Jun 2005, Russell Smith wrote: Added to TODO: * Create a bitmap of pages that need vacuuming Instead of sequentially scanning the entire table, have the background writer or some other process record pages that have expired rows, then VACUUM can look at just those pages rather than the entire table. In the event of a system crash, the bitmap would probably be invalidated. Further to this, is there any use case for allowing FSM, or this DSM to spill to disk if the space fills up. It would allow the possibility of unusual changes to the db to not loose space. You could just load part of the overflow from the disk back int the FSM in memory and continue using free space. FSM splilling to disk would be a problem. The reason is that when we need to allocate an empty page, we hit the FSM first. If that operation becomes disk bound, large updates and inserts are going to really suck from a performance point of view. The idea I discussed is disk backed, because its the first few pages of every heap segment. This map doesn't mean that pages are free. It means they've been modified. Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Christopher Kings-Lynne wrote: In phpPgAdmin the default db to connect to can be specified per-server in the config file. It defaults to template1. It actually is not relevant at all which db it is, so long as they can connect to it. I wonder how many users actually change that value for php/pgadmin or simply leave it default. Observing myself, 10 % I'd guess. Only people who ever change it are those whose dba's have disallowed connections to template1. Probably, though the create db issue is a good reason not to use template1. So may I propose to have a pg_system database created by initdb, as a copy from template1 in 8.1? Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 7.4.8 compilation failure on Fedora Core 4
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, RPM building for Fedora Core 4 and PostgreSQL 7.4.8 failed with the error below: == make[3]: Leaving directory `/usr/src/redhat/BUILD/postgresql-7.4.8/src/pl/tcl' make[3]: Entering directory `/usr/src/redhat/BUILD/postgresql-7.4.8/src/pl/plper l' gcc -O2 -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -m32 -march=i686 - -mtune=pe ntium4 -fasynchronous-unwind-tables -I/usr/include/et - -fno-strict-aliasing -Wmis sing-declarations, - -Wl,-rpath,/usr/lib/perl5/5.8.6/i386-linux-thread-multi/CORE -fpic -I. - -I/usr/lib/perl5/5.8.6/i386-linux-thread-multi/CORE -I../../../src/in clude -D_GNU_SOURCE -I/usr/include -I/usr/include/et -c -o plperl.o plperl.c cc1: error: unrecognized command line option -Wmissing-declarations, make[3]: *** [plperl.o] Error 1 make[3]: Leaving directory `/usr/src/redhat/BUILD/postgresql-7.4.8/src/pl/plperl ' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/redhat/BUILD/postgresql-7.4.8/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/src/redhat/BUILD/postgresql-7.4.8/src' make: *** [all] Error 2 error: Bad exit status from /var/tmp/rpm-tmp.91807 (%build) == Any comments on that? # gcc --version gcc (GCC) 4.0.0 20050519 (Red Hat 4.0.0-8) Regards, - -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com.tr http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFCsqDAtl86P3SPfQ4RAkQXAKDpfwVcgBzlonCfRnvvBMzFlDm/ggCfd7ob xDj58reyKtigN3Wm+4yUpRM= =Ebz3 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum in the backend
Joshua D. Drake wrote: Josh Berkus wrote: I've personally seen at least a dozen user requests for autovacuum in the backend, and had this conversation about 1,100 times: NB: After a week, my database got really slow. Me: How often are you running VACUUM ANALYZE? NB: Running what? Can't argue that except... RTFM ;). I am not saying it doesn't have a validity. I am just saying that if you actually pay attention to PostgreSQL and maintain it, you don't need it ;) I think everyone on this list would agree with you. The only reason I think the newbie protection is important (and I don't think it's the most important reason for autovacuum) is that perception is reality to some extent. Valid or not we still suffer from a reputation of being more complicated and slower than mysql. Steps towards reducing / eliminating that perception can only be good for us as I think lots of developers make their first database decision based solely on their perceptions and then just stick with what they know. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum in the backend
Joshua D. Drake wrote: Matthew T. O'Connor wrote: The major reasons for autovacuum as I see it are as follows: * Reduces administrative overhead having to keep track of what tables need to be vacuumed how often. Creates more overhead and thus reduces performance. In the general case, I disagree. Overall having your tables vacuumed and analyzed only when needed and never when not needed can only reduce system overhead. Granted there are limitations in the contrib version of autovacuum, some of which go away in the integrated case. * Reduces the total amount of time the system spends vacuuming since it only vacuums when needed. Can be easily done with cron. Really? What happens when your load / usage patterns change? When a table is added that gets heavily used? * Keeps stats up-to-date automatically Which can be done with cron Same response as above. * Eliminates newbie confusion RTFM ;-) * Eliminates one of the criticisms that the public has against PostgreSQL (justifed or not) Agreed. Just so everyone knows from the get go here. I am purposely playing a little devils advocate. Autovacuum has some drawbacks. I think we should be **publicly** aware of them before we pursue integration. Understood. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Autovacuum in the backend
Russell Smith wrote: * Reduces the total amount of time the system spends vacuuming since it only vacuums when needed. Can be easily done with cron. Can you do partial table vacuums with CRON? You can work out the smartest time to vacuum with cron? I thought it just scheduled tasks at certain times. To be fair, autovacuum can't do partial table vacuums either, in fact nothing can right now. Perhaps someday something like this will be feasible. * Eliminates one of the criticisms that the public has against PostgreSQL (justifed or not) Agreed. This is really the same as the previous RTFM question/response. People criticise because vacuum is foreign to them, and for newbie's that equals too hard, next db please. As much as it is a technical issue, it's an advocacy issue too. This bullet point is absolutely an advocacy issue. Every developer that says next db please will probably not come back to PostgreSQL for quite some time, thus bolstering the userbase of the competition and reducing the userbase of PostgreSQL. Plus we finally get XID wraparound protection. We finally decided that for 8.1 we needed some protection, which I think Tom committed. This again may be a newbie thing. But there are a lot of newbies out there then. We've see on the lists and on IRC this problem pop up a number of times. And people say Why didn't it tell me, RTFM it's exactly what they want to hear, or the fact they thought they read the manual, and missed understanding that bit. I think this point hasn't been stressed enough. With nested transactions these days (not to mention faster hardware) I can see XID wraparound becoming a much bigger issue. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Autovacuum in the backend
Russell Smith wrote: On Fri, 17 Jun 2005 06:26 pm, Andreas Pflug wrote: Qingqing Zhou wrote: One reason of not using lib-pq is that this one has to wait for the completion of each vacuum (we don't has async execution in libpq right?), There *is* async execution in libpq, and it works. I would have thought the main reasons for not using libpq means you are locked into only using commands that are available to all users via SQL. If you don't use libpq, you open up the ability to use functions that can make use of information available to the backend, and to also run functions in a way that it is not possible to do via SQL. Right now we don't really want to fire off more than one VACUUM at a time since it will create a more substantial IO storm issue than we already have with vacuum. Perhaps with the introduction of vacuum delay settings and table spaces we could / should rethink this, but for now it's the easiest way to go. As for the standard SQL issue, FSM data (or anything else we might want) could be exported via regular SQL via some type of super-user only system function. So that isn't really the issue. I don't remember all the details but you can look at the discussion when my patch was rejected (around July of 2004). People just didn't like including libpq into the backend for reasons I don't remember. I don't *think* this is up for discussion, I *think* autovacuum has to work without libpq if it is going to be accepted. Matthew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Probably, though the create db issue is a good reason not to use template1. Create db issue? So may I propose to have a pg_system database created by initdb, as a copy from template1 in 8.1? But then dbas will block off access to that db, or drop it and we're back to square one... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Escape handling in strings
Michael Glaesemann wrote: On Jun 17, 2005, at 4:34 PM, Greg Stark wrote: And for an app issuing hundreds or thousands of queries per minute (or even second) a warning could effectively be a showstopper. It could require disabling all warnings in their config to avoid filling their disk with Postgres logs in minutes. Good point. I would suggest this warning be disable-able with a GUC variable. Otherwise you're effectively giving no advance warning time to those users. Perhaps NOTICE would be better, at least for the first step? People might be more comfortable with that, as using backslash escaping isn't really going to cause problems with this particular version, but rather for future versions. I am thinking changing the level of the message isn't going to help people much because it still displays and fills up the server logs. If postgres keeps advancing at the pace it's advancing now I might suggest waiting two release cycles instead of just one. How's this for an idea? Step 1 (8.1) NOTICE level (or some other level, lower than WARNING), E'' and \' are available Step 2 (8.2?) WARNING level, E'' and \' are available Step 3 (8.3? 8.4?) E'' available, plain '' interpreted literally. Right now I am thinking we would have the warning available in 8.1, but not turn it on by default. Perhaps we can tell users to enable the warning at some time during 8.1 so they are ready for it in 8.2. If we get a significant must-upgrade 8.0.X release a few months before 8.1, we can tell them to change \' to '' and perhaps have the \' warning be enabled always in 8.1. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
-Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] Sent: Fri 6/17/2005 9:47 AM To: Magnus Hagander Cc: Dave Page; Josh Berkus; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend) In phpPgAdmin the default db to connect to can be specified per-server in the config file. It defaults to template1. It actually is not relevant at all which db it is, so long as they can connect to it. That's how pgAdmin does it (though you set the default on the server dialog), however it's not good having to default to a database that 99% of sysadmins probably don't want their users anywhere near. /D ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
-Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] Sent: Fri 6/17/2005 11:00 AM To: Andreas Pflug Cc: Magnus Hagander; Dave Page; Josh Berkus; pgsql-hackers@postgresql.org; Tom Lane Subject: Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend) Probably, though the create db issue is a good reason not to use template1. Create db issue? You can't create a db from template1 if other users are connected to it, which means the most simple form of create database will fail. So may I propose to have a pg_system database created by initdb, as a copy from template1 in 8.1? But then dbas will block off access to that db, or drop it and we're back to square one... That's their choice though, and it would then be up to them to provide an alternative for their users (there's nothing to stop them doing the same with template1 iirc). At least we would have a standard, non-template database for utilities to connect to, whose purpose could be documented. Regards Dave ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Christopher Kings-Lynne wrote: Probably, though the create db issue is a good reason not to use template1. Create db issue? CREATE TABLE (implicitely using TEMPLATE template1) often fails because template1 has connections exceeding the current one. So may I propose to have a pg_system database created by initdb, as a copy from template1 in 8.1? But then dbas will block off access to that db, or drop it and we're back to square one... Sure, some dbas also might like to drop INFORMATION_SCHEMA, or modify system catalogs or worse to bend the system as they like, effectively disabling common tools. But if we create this db with initdb, I'd expect to find it in the vast majority of installations. If not, we could fall back to template1 for admin tools. Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] DTrace Probes?
On 6/17/05, Josh Berkus josh@agliodbs.com wrote: Hey, Folks, I need to find someone who's really interesed in working with DTrace. Sun has offered to help put DTrace probes into PostgreSQL for advanced profiling, but need to know where to probe. Anyone? I'm afraid that I won't get around to this quickly enough. I played a little with DTrace probes when Solaris 10 just came out. DTrace is useful when you have no source code of application or when you are collecting statistics on a live system. Otherwise it is not much different from gprof apart, maybe, that it can collect statistics about kernel syscalls. Anyways, DTrace is a very powerful yet lightweight tool. Creating a strace program to attach to a running PostgreSQL instance and collect statistics will be a nice thing to do. We may even find some bottlenecks in our code. I can volunteer to do it but I do not have a through understanding of PostgreSQL internals. --Josh Regards, Nicolai ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Autovacuum in the backend
[EMAIL PROTECTED] (Gavin Sherry) wrote: I guess the main point is, if something major like this ships in the backend it says to users that the problem has gone away. pg_autovacuum is a good contrib style solution: it addresses a problem users have and attempts to solve it the way other users might try and solve it. When you consider it in the backend, it looks like a workaround. I think users are better served by solving the real problem. Hear, hear! It seems to me that the point in time at which it is *really* appropriate to put this into the backend is when the new GUC variable dead_tuple_map_size (akin to FSM) is introduced, and there is a new sort of 'VACUUM DEAD TUPLES' command which goes through the DTPM (Dead Tuple Page Map). In THAT case, there would be the ability to do a VACUUM on the dead bits of the table that consists of 50M rows without having to go through the 49M rows that haven't been touched in months. -- cbbrowne,@,gmail.com http://linuxfinances.info/info/languages.html I can't escape the sensation that I have already been thinking in Lisp all my programming career, but forcingthe ideas into the constraints of bad languages, which explode those ideas into a bewildering array of details, most of which are workarounds for the language. -- Kaz Kylheku ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum in the backend
Matthew T. O'Connor matthew@zeut.net writes: ... People just didn't like including libpq into the backend for reasons I don't remember. One reason I can think of is that there would be global-symbol conflicts --- libpq has copies of some backend routines, but they are not identical. In any case, the argument that's being made here is that an integrated autovac would be simple and newbie-friendly. I think it's impossible for a libpq-using autovac to ever be invisible to the DBA, if only because he has to configure it with an appropriate username/password, make sure pg_hba.conf will actually let it into every database, etc. There are way too many foot-guns in that scenario for my taste. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
But then dbas will block off access to that db, or drop it and we're back to square one... Don't see why they would. Let's review what we have here: Database Function(s) template0 guaranteed-virgin template for CREATE DATABASE template1 installation-default template for CREATE DATABASE default database to connect to for clients (I don't think I'm missing anything --- can anyone think of a purpose I have forgotten?) If we split template1's functions as template1 installation-default template for CREATE DATABASE default default database to connect to for clients then it becomes fairly reasonable for DBAs to block access to template1 after they've installed any installation-default stuff they want in it. There isn't any particular reason to block access to default, unless you don't want to have a shared database at all --- in which case you'd probably just drop it. It wouldn't just be default to connect to, it would also be location for tools to store cluster-wide information. Which makes pg_system a slightly more reasonable name in that context, but i certainly have no problem with default as a name. One argument against this is that it'd mean another copy of the system catalogs in a standard installation. That's been running three to five megabytes over the last few releases. Disk space is pretty cheap these days, but we do get occasional complaints from people who wish the footprint was smaller. As long as you can drop it without hosing your system completely, that can always be a solution for the ppl who are that space constrained. //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Christopher Kings-Lynne [EMAIL PROTECTED] writes: So may I propose to have a pg_system database created by initdb, as a copy from template1 in 8.1? Seems like a bizarre choice of name. Why not default? But then dbas will block off access to that db, or drop it and we're back to square one... Don't see why they would. Let's review what we have here: DatabaseFunction(s) template0 guaranteed-virgin template for CREATE DATABASE template1 installation-default template for CREATE DATABASE default database to connect to for clients (I don't think I'm missing anything --- can anyone think of a purpose I have forgotten?) If we split template1's functions as template1 installation-default template for CREATE DATABASE default default database to connect to for clients then it becomes fairly reasonable for DBAs to block access to template1 after they've installed any installation-default stuff they want in it. There isn't any particular reason to block access to default, unless you don't want to have a shared database at all --- in which case you'd probably just drop it. One argument against this is that it'd mean another copy of the system catalogs in a standard installation. That's been running three to five megabytes over the last few releases. Disk space is pretty cheap these days, but we do get occasional complaints from people who wish the footprint was smaller. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Magnus Hagander [EMAIL PROTECTED] writes: It wouldn't just be default to connect to, it would also be location for tools to store cluster-wide information. Which makes pg_system a slightly more reasonable name in that context, but i certainly have no problem with default as a name. Well, where a tool chooses to install stuff is the business of that tool; there isn't any particular reason to think that default would suddenly become a preferred choice, I think. I dislike the name pg_system because it implies that that DB is somehow special from the point of view of the system ... which is exactly what it would *not* be. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
It wouldn't just be default to connect to, it would also be location for tools to store cluster-wide information. Which makes pg_system a slightly more reasonable name in that context, but i certainly have no problem with default as a name. Well, where a tool chooses to install stuff is the business of that tool; there isn't any particular reason to think that default would suddenly become a preferred choice, I think. One of the two main reasons to do this was to have a place for tools to store persistant data in a standard way. At least it was in Daves mail ;-) Actually, two out of three points were data storage. It is, as you say, up to the tool where to put it. But we should provide a standard place for tools to do it, to make it easier for both tool makers and end users. I dislike the name pg_system because it implies that that DB is somehow special from the point of view of the system ... which is exactly what it would *not* be. That I can certainly agree with. //Magnus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Autovacuum in the backend
On Fri, 2005-06-17 at 00:03 -0700, Joshua D. Drake wrote: Matthew T. O'Connor wrote: Joshua D. Drake wrote: Just my own two cents. First I am not knocking the work that has been on autovacuum. I am sure that it was a leap on its own to get it to work. However I will say that I just don't see the reason for it. The major reasons for autovacuum as I see it are as follows: * Reduces administrative overhead having to keep track of what tables need to be vacuumed how often. Creates more overhead and thus reduces performance. Agreed, except I have a number of tables that are over scheduled with cron because about once a week (at a random time) they do go through heavy enough churn to require the more frequent vacuum, but the rest of the time they're fairly quiet. It's not ideal, but autovacuum was the difference between a 10 minute vacuum 24/7, or a 10 minute vacuum on the table for the heavy 2 or so hours which randomly appear. In the case of unexpected or uneven loads, cron isn't particularly useful. -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Magnus Hagander wrote: I dislike the name pg_system because it implies that that DB is somehow special from the point of view of the system ... which is exactly what it would *not* be. That I can certainly agree with. I suggested the name to indicate that it's a db used by system tools. So from a normal db user's point of view, it says don't fool with this db, you might break some tools you're using. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Tom Lane wrote: One argument against this is that it'd mean another copy of the system catalogs in a standard installation. That's been running three to five megabytes over the last few releases. Disk space is pretty cheap these days, but we do get occasional complaints from people who wish the footprint was smaller. In this case, a dba would drop anything not neccessary, including INFORMATION_SCHEMA. We also could provide an initdb switch to omit that pg_system db (and more non-vital stuff). I particularly dislike the name default for that database, because we'd have to expect users to place their user data there regularly (as in the public schema), which is just what should *not* happen. So the pg_ prefix should be used, the docs say clearly enough don't touch pg_% objects unless you know exactly what you do. Regards, Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Autovacuum in the backend
Josh, Just so everyone knows from the get go here. I am purposely playing a little devils advocate. Well, please stop it. We discussed AV over a year ago when we ran out of time to integrate it with 8.0. This disucussion now is hindering any discussion of what needs to be *done* to integrate it.This isn't a debating society. Folks, I'm sorry to be so grumpy about this, but so far 80% of the posts on this thread have been re-arguing a discussion we had in 2004. Which isn't helping Alvaro get anything done. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Autovacuum in the backend
Christopher Browne wrote: [EMAIL PROTECTED] (Gavin Sherry) wrote: I guess the main point is, if something major like this ships in the backend it says to users that the problem has gone away. pg_autovacuum is a good contrib style solution: it addresses a problem users have and attempts to solve it the way other users might try and solve it. When you consider it in the backend, it looks like a workaround. I think users are better served by solving the real problem. Hear, hear! It seems to me that the point in time at which it is *really* appropriate to put this into the backend is when the new GUC variable dead_tuple_map_size (akin to FSM) is introduced, and there is a new sort of 'VACUUM DEAD TUPLES' command which goes through the DTPM (Dead Tuple Page Map). In THAT case, there would be the ability to do a VACUUM on the dead bits of the table that consists of 50M rows without having to go through the 49M rows that haven't been touched in months. This will make VACUUM less painful, but it doesn't eliminate the need / desire for autovacuum. I agree this would be good, but I see it as a separate issue. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Andreas Pflug [EMAIL PROTECTED] writes: I particularly dislike the name default for that database, because we'd have to expect users to place their user data there regularly (as in the public schema), which is just what should *not* happen. Why not? Any tools using this database for their own purposes should surely be smart enough to put all their stuff in a tool-specific schema with a name chosen to be unlikely to collide with user names. So I see no reason at all that users couldn't use the database too. If your intent is to have a database reserved for tool use only, you can certainly have an agreement among tool authors to create pg_tools or some such if it's not there already. But there are no potential uses of such a database in the standard distribution, and so I see no reason to load down the standard distribution by creating a database that may go completely unused. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: I particularly dislike the name default for that database, because we'd have to expect users to place their user data there regularly (as in the public schema), which is just what should *not* happen. Why not? Any tools using this database for their own purposes should surely be smart enough to put all their stuff in a tool-specific schema with a name chosen to be unlikely to collide with user names. So I see no reason at all that users couldn't use the database too. If your intent is to have a database reserved for tool use only, you can certainly have an agreement among tool authors to create pg_tools or some such if it's not there already. But there are no potential uses of such a database in the standard distribution, and so I see no reason to load down the standard distribution by creating a database that may go completely unused. The whole point if it is to have a database that is nearly guaranteed to be there right from the start, i.e. right after initdb, not to need some decent script executed (or not) later. Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] LGPL
With libreadline, we are not taking their code or distributing it, but merely linking to it if it exists. But we are also requiring it. The rpms won't install unless readline is available. Now, some say that is enough to make us GPL, but many don't agree with that interpretation. -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] LGPL
On Fri, 17 Jun 2005, Joshua D. Drake wrote: With libreadline, we are not taking their code or distributing it, but merely linking to it if it exists. But we are also requiring it. The rpms won't install unless readline is available. that isn't a PostgreSQL requirement though, that is a packagers requirement ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] LGPL
Am Dienstag, den 14.06.2005, 22:59 -0300 schrieb Marc G. Fournier: We already do ... libreadline ... Hm. I remember in my source builds I used libedit which is the BSD replacement IIRC? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] LGPL
Joshua D. Drake wrote: With libreadline, we are not taking their code or distributing it, but merely linking to it if it exists. But we are also requiring it. The rpms won't install unless readline is available. Now, some say that is enough to make us GPL, but many don't agree with that interpretation. We have been down this road before. You can not be forced to GPL your code. You can be forced to stop using GPL code if you are in breach of the GPL. That's according to the FSF themselves (specifically Eblen Moglen). Some people have chosen to GPL their code rather than stop their reliance on GPL code. That would would be a no-brainer choice for us, as there is a simple BSD licensed replacement for libreadline. So relax ;-) All is well. cheers andrew ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] LGPL
Marc G. Fournier wrote: On Fri, 17 Jun 2005, Joshua D. Drake wrote: With libreadline, we are not taking their code or distributing it, but merely linking to it if it exists. But we are also requiring it. The rpms won't install unless readline is available. that isn't a PostgreSQL requirement though, that is a packagers requirement ... If we link to readline, postgresql won't start without it. Regardless of the package. That seems pretty much a postgresql requirement ;) Sincerely, Joshua D. Drake Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] LGPL
Huh ? ./configure --without-readliine works just fine, there is no requirement. Dave On 17-Jun-05, at 3:04 PM, Joshua D. Drake wrote: Marc G. Fournier wrote: On Fri, 17 Jun 2005, Joshua D. Drake wrote: With libreadline, we are not taking their code or distributing it, but merely linking to it if it exists. But we are also requiring it. The rpms won't install unless readline is available. that isn't a PostgreSQL requirement though, that is a packagers requirement ... If we link to readline, postgresql won't start without it. Regardless of the package. That seems pretty much a postgresql requirement ;) Sincerely, Joshua D. Drake Marc G. Fournier Hub.Org Networking Services (http:// www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] LGPL
Joshua D. Drake wrote: Dave Cramer wrote: Huh ? ./configure --without-readliine works just fine, there is no requirement. Again: If we **link** to readline, postgresql won't start without it. That is a postgresql requirement. Yes we can compile without it. That isn't what I was talking about. But as Andrew pointed out, it doesn't really matter. The point is the the source does not require it, but specific binaries might based on how they are built. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] LGPL
Joshua D. Drake wrote: If we link to readline, postgresql won't start without it. Regardless of the package. That seems pretty much a postgresql requirement ;) If you think you're in danger don't link to it. You don't have to at all. You can build without readline entirely (it's only needed for psql) or you can link to libedit instead. So it's a postgresql option, not a requirement. But they'll never worry anyway, it would be a complete waste of time and money to pursue you over it. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] LGPL
Dave Cramer wrote: Huh ? ./configure --without-readliine works just fine, there is no requirement. Again: If we **link** to readline, postgresql won't start without it. That is a postgresql requirement. Yes we can compile without it. That isn't what I was talking about. But as Andrew pointed out, it doesn't really matter. Sincerely, Joshua D. Drake Dave On 17-Jun-05, at 3:04 PM, Joshua D. Drake wrote: Marc G. Fournier wrote: On Fri, 17 Jun 2005, Joshua D. Drake wrote: With libreadline, we are not taking their code or distributing it, but merely linking to it if it exists. But we are also requiring it. The rpms won't install unless readline is available. that isn't a PostgreSQL requirement though, that is a packagers requirement ... If we link to readline, postgresql won't start without it. Regardless of the package. That seems pretty much a postgresql requirement ;) Sincerely, Joshua D. Drake Marc G. Fournier Hub.Org Networking Services (http:// www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 17 June 2005 15:09 To: Christopher Kings-Lynne Cc: Andreas Pflug; Magnus Hagander; Dave Page; Josh Berkus; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend) One argument against this is that it'd mean another copy of the system catalogs in a standard installation. That's been running three to five megabytes over the last few releases. Disk space is pretty cheap these days, but we do get occasional complaints from people who wish the footprint was smaller. Yeah, but those people could easily drop it to save that space. They'd have to offer an alternative default db for their users, but then I guess they probably have pretty unusual requirements anyway so I doubt that would add any pain. Regards, Dave. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Utility database
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Andreas Pflug) wrote: Magnus Hagander wrote: I dislike the name pg_system because it implies that that DB is somehow special from the point of view of the system ... which is exactly what it would *not* be. That I can certainly agree with. I suggested the name to indicate that it's a db used by system tools. So from a normal db user's point of view, it says don't fool with this db, you might break some tools you're using. I would tend to agree with the reasons not to use a pg_ prefix... Perhaps something like sys_ or def_ (short for system or default) would be better. It strikes me as a useful thing to make sure the name contains the word share or shared somewhere, as that would give even the most hapless user that accesses it some suggestion that this database is shared, and hence should be treated with some care and with some attempt to try to play well with others. Alternatively, the word commons, of the Tragedy of the Commons, might fit. Thus, sys_shared, def_share, user_commons are all sorts of names that suggest that this is some sort of default/shared area. -- output = (cbbrowne @ gmail.com) http://linuxdatabases.info/info/wp.html People who don't use computers are more sociable, reasonable, and ... less twisted -- Arthur Norman ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Utility database
In the last exciting episode, dpage@vale-housing.co.uk (Dave Page) wrote: But then dbas will block off access to that db, or drop it and we're back to square one... That's their choice though, and it would then be up to them to provide an alternative for their users (there's nothing to stop them doing the same with template1 iirc). At least we would have a standard, non-template database for utilities to connect to, whose purpose could be documented. At one time, it was uncommon to have computing sites that did not have some sort of priesthood of system operators that would be formally responsible for managing the local environment. They would do things like: - Install TeX and LaTeX, in some common area, and provide a Local Users' Guide To Using TeX and LaTeX that might even tell you which printers to use, and in what rooms you can find the TeX-compatible printers... - Install all sorts of *supported* system extensions, commonly under /usr/local In an environment with that sort of staffing, it surely is reasonable to anticipate that these (usually harassed) heroes might set up a PostgreSQL instance with a generic default database of this sort. Today, many users are in environments where there is no DBA, there is no system administrator, there is no one that really understands their computer system. What we provide as a default ought to try to be suited to both of those purposes, and it is unsafe to assume either the presence or absence of a DBA, as both are common conditions... -- let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];; http://cbbrowne.com/info/slony.html Editing is a rewording activity. -- Alan Perlis [And EMACS a rewording editor. Ed.] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
-Original Message- From: Andreas Pflug [mailto:[EMAIL PROTECTED] Sent: 17 June 2005 18:45 To: Tom Lane Cc: Christopher Kings-Lynne; Magnus Hagander; Dave Page; Josh Berkus; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend) The whole point if it is to have a database that is nearly guaranteed to be there right from the start, i.e. right after initdb, not to need some decent script executed (or not) later. OK, so it sounds like noone is really against this idea. Is anyone going to object to it being applied if I post a suitable patch? Assuming not, it seems like the only bone of contention is the name... So: pg_system - Implies it's a 'true' PostgreSQL system object, but also implies 'don't mess with me' default - Implies a standard 'default' database. pgdb - Blagged from the Microsoft equivalent, msdb. Others? Personally I prefer the first or last, as default implies to me that it's a kindof general use database - which, as Tom points out it could be, however I think it's better to encourage users to only use it as directed by tool providers, and not for general purpose. Regards, Dave. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] MemoryContextAlloc: invalid request size
Title: MemoryContextAlloc: invalid request size
I got the copy of the database and ran it
with truss.
From the trace log it looked like ${PGDATA/}base/last-number/pg_internal.init
was corrupted
I replaced this file with ${PGDATA/}base/prev-number/pg_internal.init
After that I was able to login and use
database. Still dont understand what Ive done.
Can somebody shed little light on what
actually happened?
Thanks,
Mike
From: Brusser, Michael
[mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 8:28
PM
To: 'Pgsql-Hackers
(pgsql-hackers@postgresql.org)'
Subject: [HACKERS]
MemoryContextAlloc: invalid request size
Our
customer is reporting a database problem after they ran into a
disk quota/filesystem full
situation.
This is
Postgres 7.2.x on Solaris.
The database server starts without
any obvious errors, but the app. Server
cannot establish connection. (It is
setup to use Unix Domain Socket)
I did not have a chance to see the
database-log, but we have the error-log and
the truss log from the App. Server.
In the
error-log I see this:
... ...
Connection to database failed
FATAL: MemoryContextAlloc:
invalid request size 0
And this
in the trace log:
... ...
10894:
open(/usr/lib/libresolv.so.2,
O_RDONLY) = 11
10894: fstat(11,
0xFFBE9004)
= 0
10894: mmap(0xFE02, 8192,
PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 11, 0) = 0xFE02
10894: mmap(0x,
303104, PROT_READ|PROT_EXEC, MAP_PRIVATE, 11, 0) = 0xFDD2
10894: mmap(0xFDD64000,
15564, PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED, 11, 212992) =
0xFDD64000
10894: mmap(0xFDD68000, 2728,
PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVATE|MAP_FIXED|MAP_ANON, -1, 0) =
0xFDD68000
10894: munmap(0xFDD54000,
65536)
= 0
10894: memcntl(0xFDD2,
33536, MC_ADVISE, MADV_WILLNEED, 0, 0) = 0
10894:
close(11)
= 0
10894: munmap(0xFE02,
8192)
= 0
10894: so_socket(1, 2, 0,
,
1)
= 11
10894: fstat64(11,
0xFFBE8A70)
= 0
10894: getsockopt(11, 65535,
8192, 0xFFBE8B70, 0xFFBE8B6C, 0) = 0
10894: setsockopt(11, 65535,
8192, 0xFFBE8B70, 4, 0) = 0
10894: fcntl(11, F_SETFL,
0x0080)
= 0
10894: connect(11,
0x0060EAA0, 77,
1)
= 0
10894: poll(0xFFBE89E8, 1,
-1)
= 1
10894: sigaction(SIGPIPE,
0xFFBE8788, 0xFFBE) = 0
10894: send(11, \0\001
(\002\0\0 s y n c.., 296, 0) = 296
10894: sigaction(SIGPIPE,
0xFFBE8788, 0xFFBE) = 0
10894: poll(0xFFBE89E8, 1,
-1)
= 1
10894: recv(11,
R\0\0\0\0 E F A T A L :.., 16384, 0) = 58
... ...
Could you
recommend the remedy?
Thanks in
advance,
Mike.
Re: [HACKERS] Utility database
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christopher Browne Sent: 17 June 2005 19:59 To: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Utility database Thus, sys_shared, def_share, user_commons are all sorts of names that suggest that this is some sort of default/shared area. I like the first. The second and third seem less obvious to me. 'default_shared' should definitely get the point across, though it's a little long. Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Utility database
Dave Page wrote: Thus, sys_shared, def_share, user_commons are all sorts of names that suggest that this is some sort of default/shared area. I like the first. The second and third seem less obvious to me. 'default_shared' should definitely get the point across, though it's a little long. It strikes me that these names just might have some significance to developers but will have none at all for users. I don't heve a better alternative ... maybe because the purpose has been expressed somewhat fuzzily. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Utility database
Andrew Dunstan wrote: It strikes me that these names just might have some significance to developers but will have none at all for users. I don't heve a better alternative ... maybe because the purpose has been expressed somewhat fuzzily. I'd define the purpose like this: - being a db that's existing reliably right after initdb, unless deleted by an ( evil-minded :-) admin. - contain data for cluster wide system services, e.g. pgAgent schedules, configuration for autovacuumV2, profiling data regards, Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum in the backend
On Fri, Jun 17, 2005 at 12:21:44PM -0400, Matthew T. O'Connor wrote: snip more stuff about how we need to track pages with dead tuples This will make VACUUM less painful, but it doesn't eliminate the need / desire for autovacuum. I agree this would be good, but I see it as a separate issue. Not only is it a seperate issue, but there's also no way it could possibly be done for 8.1, whereas autovacuum most likely will make it into 8.1. Additionally, there are noted improvements that come about by putting autovacuum in the backend instead of leaving it in contrib. And as others have mentioned numerous times, any improvements made to vacuum will help out vacuum as well. There simply isn't a downside to putting it in the backend that anyone's brought up. Autovacuum was originally scheduled for 8.0. There's been plans to put it in the backend for close to 2 years now. There's no reason at all to push it out any farther. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] LGPL
Joshua D. Drake [EMAIL PROTECTED] writes: With libreadline, we are not taking their code or distributing it, but merely linking to it if it exists. But we are also requiring it. The rpms won't install unless readline is available. The RPMs require it --- not our source code. Since the RPMs can only work atop a GPL OS (Linux), it hardly matters in that context. What is important is that it is possible, and useful, to build Postgres in a completely non-GPL environment. If that were not so then I think we'd have some license issues. But the fact that building PG in a GPL-ized environment creates a GPL-ized binary is not a problem from my point of view. You've already bought into the GPL if you're using that environment. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)
Dave Page dpage@vale-housing.co.uk writes: Personally I prefer the first or last, as default implies to me that it's a kindof general use database - which, as Tom points out it could be, however I think it's better to encourage users to only use it as directed by tool providers, and not for general purpose. If that is what you want then the database should surely not become the default connection target for clients. The proposal I thought was being made was that we separate the default-connection-target property from the default-CREATE-DATABASE-source property. This business about where tool authors can dump random junk of their own devising does not seem to me to fit at all with either of those properties. I think what you are really asking for is yet another standard database named something like TOOLS_ONLY_KEEP_OUT. But I do not see the argument for having that created by default, because any tool that is capable of creating random junk is surely capable of creating a database to put it in. Furthermore, if it's created by default and completely unused in the default installation, lots of DBAs will immediately drop it --- so I entirely fail to see the argument that tools could expect it to be there without any expenditure of their own effort. I still say the most that's needed here is some agreement among tool authors about a common choice of database name to create if their tool is installed. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Utility database
Dave Page dpage@vale-housing.co.uk writes: I like the first. The second and third seem less obvious to me. 'default_shared' should definitely get the point across, though it's a little long. I think shared would give the wrong impression to many people --- nowadays the connotation of that is something that you are exposing to at least your local network, maybe the entire internet (think Windows shares). I realize that the meaning you had in mind was shared among authorized users of this Postgres cluster, but I doubt that implication will come through to very many newbies. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] LGPL
On 6/18/05, Tom Lane [EMAIL PROTECTED] wrote: What is important is that it is possible, and useful, to build Postgres in a completely non-GPL environment. If that were not so then I think we'd have some license issues. But the fact that building PG in a GPL-ized environment creates a GPL-ized binary is not a problem from my point of view. You've already bought into the GPL if you're using that environment. Put another way: Linking to a GPLed library creates a gpled result, but being GPLed is completely and totally irrelevant to *users* because the GPL places no restrictions on use whatsoever. ... But is it really the case that PostgreSQL developers are being paid to code because PG is BSDed and proprietary forks are possible? ... There is no harm in being BSDed, but I question that the users of PostgreSQL are gaining enough advantage that there needs to be so much paranoia about making sure that the code is as easy as possible to make propritary forks of... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
