Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Joshua D. Drake
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Joshua D. Drake
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Hans-Jürgen Schönig
* 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

Re: [HACKERS] [PATCHES] Escape handling in strings

2005-06-17 Thread Greg Stark
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

Re: [HACKERS] [PATCHES] Escape handling in strings

2005-06-17 Thread Michael Glaesemann
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Magnus Hagander
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread William ZHANG
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Andreas Pflug
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Andreas Pflug
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Christopher Kings-Lynne
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Andreas Pflug
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Russell Smith
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Russell Smith
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Christopher Kings-Lynne
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Russell Smith
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Russell Smith
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Gavin Sherry
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Gavin Sherry
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Andreas Pflug
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

[HACKERS] 7.4.8 compilation failure on Fedora Core 4

2005-06-17 Thread Devrim GUNDUZ
-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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Matthew T. O'Connor
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Matthew T. O'Connor
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,

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Matthew T. O'Connor
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Matthew T. O'Connor
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Christopher Kings-Lynne
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

Re: [HACKERS] [PATCHES] Escape handling in strings

2005-06-17 Thread Bruce Momjian
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Dave Page
-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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Dave Page
-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)

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Andreas Pflug
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

Re: [HACKERS] DTrace Probes?

2005-06-17 Thread Nicolai Tufar
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Christopher Browne
[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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Tom Lane
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Magnus Hagander
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Tom Lane
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Tom Lane
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,

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Magnus Hagander
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Rod Taylor
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Andreas Pflug
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Andreas Pflug
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Josh Berkus
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Matthew T. O'Connor
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Tom Lane
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Andreas Pflug
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

Re: [HACKERS] LGPL

2005-06-17 Thread Joshua D. Drake
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.

Re: [HACKERS] LGPL

2005-06-17 Thread Marc G. Fournier
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

Re: [HACKERS] LGPL

2005-06-17 Thread Tino Wildenhain
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

Re: [HACKERS] LGPL

2005-06-17 Thread Andrew Dunstan
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

Re: [HACKERS] LGPL

2005-06-17 Thread Joshua D. Drake
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

Re: [HACKERS] LGPL

2005-06-17 Thread Dave Cramer
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

Re: [HACKERS] LGPL

2005-06-17 Thread Bruce Momjian
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

Re: [HACKERS] LGPL

2005-06-17 Thread Andrew Dunstan
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

Re: [HACKERS] LGPL

2005-06-17 Thread Joshua D. Drake
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Dave Page
-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

Re: [HACKERS] Utility database

2005-06-17 Thread Christopher Browne
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

Re: [HACKERS] Utility database

2005-06-17 Thread Christopher Browne
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Dave Page
-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

Re: [HACKERS] MemoryContextAlloc: invalid request size

2005-06-17 Thread Brusser, Michael
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

Re: [HACKERS] Utility database

2005-06-17 Thread Dave Page
-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

Re: [HACKERS] Utility database

2005-06-17 Thread Andrew Dunstan
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

Re: [HACKERS] Utility database

2005-06-17 Thread Andreas Pflug
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

Re: [HACKERS] Autovacuum in the backend

2005-06-17 Thread Jim C. Nasby
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

Re: [HACKERS] LGPL

2005-06-17 Thread Tom Lane
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

Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

2005-06-17 Thread Tom Lane
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

Re: [HACKERS] Utility database

2005-06-17 Thread Tom Lane
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

Re: [HACKERS] LGPL

2005-06-17 Thread Gregory Maxwell
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