Re: [HACKERS] Ooops ... seems we need a re-release pronto
On Feb 6, 12:27 pm, [EMAIL PROTECTED] (Tom Lane) wrote: As per numerous reports this morning, PG 8.2.2 and 8.1.7 both fail on fairly simple scenarios involving typmod-bearing columns (varchar, numeric, etc) with check constraints or functional indexes (and maybe other cases too, but those are the ones reported so far). I have not been able to reproduce the failures in 8.0 but I think it may have the same issue in a weaker form. We need a quick re-release I'm afraid. Should the existing source and binaries be pulled in the meantime? -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005
Re: [HACKERS] 8.1beta, SunOS and shmget
On Aug 29, 2005, at 12:41 PM, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Mon, Aug 29, 2005 at 11:30:46AM -0400, Tom Lane wrote: 20 buffers ... ugh. Obviously we are on the hairy edge of no longer functioning at all in 1MB shared memory. I'm not sure there is a whole lot we can do about this, but it's a tad irritating that clog, subtrans, and multixact are eating the equivalent of about 16 buffers (nonconfigurable) while the main buffer pool is so badly starved. 8 buffers each, I think, no? That's 32 buffers total. You're right; I was thinking that NUM_SLRU_BUFFERS was 4, but I see it's now 8. Did we bump that up on the basis of any solid evidence? There's 256K of shared memory going into those four dedicated buffer areas, which is kind of a lot when you're hoping to fit into 1MB. I just finished going through the initialization sequence to trace the calculation of shared memory size, and what I find in CVS tip is that it works out like this: shared_buffers * 8314 max_connections * (217.68 * max_locks_per_transaction + 356) max_prepared_transactions * (217.68 * max_locks_per_transaction + 576) wal_buffers * 8192 max_fsm_relations * 70 max_fsm_pages * 6 plus about 500K fixed space (These numbers are on a 32-bit machine, some of the multipliers would be a little higher on 64-bit.) The formula given in the docs doesn't seem to have been updated since 7.2: 250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections Most of the bloat since then seems to be accounted for by 2PC and the addition of subtrans and multixact buffers. Maybe we could make them allocate them automatically based on shared_buffers, with a ceiling of 8? Seems like it'd be reasonable to skinny down the number of dedicated buffers when shared_buffers is tiny, but I'm not sure about the particular equation to use. regards, tom lane Should the new formulation be sent to pgsql-docs? This looks like it could be worked into a patch pretty easily. Seems like it would make sense to update the docs for 8.1... -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SHMMAX seems entirely broken in OS X 10.4.2
In order to get postgres working in concert with an iSight on a PowerBook, I had to increase shmmax, and it seemed to work just fine by editing /etc/rc: sysctl -w kern.sysv.shmmax=134217728 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024 After restarting, I have: # sysctl -a | grep shm kern.sysv.shmmax: 134217728 kern.sysv.shmmin: 1 kern.sysv.shmmni: 32 kern.sysv.shmseg: 8 kern.sysv.shmall: 1024 Of course, this still doesn't seem to be enough to let postgres play nicely with the iSight... -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Aug 29, 2005, at 7:18 PM, Tom Lane wrote: Has anyone been able to set kern.sysv.shmmax above 4MB at all in latest OS X? I just spent a while trying what seemed every possible permutation of setting up /etc/sysctl.conf and editing /etc/rc directly, and it just fails (symptom: sysctl shows shmmax as -1, and Postgres cannot start). Grrr. Apple's been pretty lame about this from day one, but at least it's been possible to do it one way or another. (Curiously, it seems you can now change shmall after bootup in 10.4.2, but that's not much help when you can't change shmmax.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SHMMAX seems entirely broken in OS X 10.4.2
On Aug 29, 2005, at 10:37 PM, Tom Lane wrote: Thomas F. O'Connell [EMAIL PROTECTED] writes: After restarting, I have: # sysctl -a | grep shm kern.sysv.shmmax: 134217728 kern.sysv.shmmin: 1 kern.sysv.shmmni: 32 kern.sysv.shmseg: 8 kern.sysv.shmall: 1024 Of course, this still doesn't seem to be enough to let postgres play nicely with the iSight... Don't you need to increase shmall too? Also, which OS X release is this? 10.4 seems to have regressed compared to 10.3 :-( regards, tom lane This is 10.4.2. Everything worked as advertised. I didn't need to increase shmall for the settings to stick, but maybe I ought to for them to work. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Simplifying wal_sync_method
UFS was the filesystem on the Solaris 9 box. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Aug 11, 2005, at 4:18 PM, Andrew Sullivan wrote: On Wed, Aug 10, 2005 at 02:11:48AM -0500, Thomas F. O'Connell wrote: I was recently witness to a benchmark of 7.4.5 on Solaris 9 wherein it was apparently demonstrated that fsync was the fastest option among the 7.4.x wal_sync_method options. If there's a way to make this information more useful by providing more data, please let me know, and I'll see what I can do. What would be really interesting to me to know is what Sun did between 8 and 9 to make that so. We don't use Solaris for databases any more, but fsync was a lot slower than whatever we ended up using on 8. I wouldn't be surprised if they'd wired fsync directly to something else; but I can hardly believe it'd be faster than any other option. (Mind, we were using Veritas filesyste with this, as well, which was at least half the headache.) A ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Simplifying wal_sync_method
I was recently witness to a benchmark of 7.4.5 on Solaris 9 wherein it was apparently demonstrated that fsync was the fastest option among the 7.4.x wal_sync_method options. If there's a way to make this information more useful by providing more data, please let me know, and I'll see what I can do. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Aug 8, 2005, at 4:44 PM, Bruce Momjian wrote: In summary, we added all those wal_sync_method values in hopes of getting some data on which is best on which platform, but having gone several years with few reports, I am thinking we should just choose the best ones we can and move on, rather than expose a confusing API to the users. Does anyone show a platform where the *data* options are slower than the non-*data* ones? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] bgwriter, inherited temp tables TODO items?
Great! Is background writer clogging worthy? That's the one that put
postgres in a nearly unusable state after this bug was tripped.
Thanks!
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Jul 29, 2005, at 10:49 PM, Bruce Momjian wrote:
Added to TODO:
* Prevent inherited tables from expanding temporary subtables
of other
sessions
--
-
Thomas F. O'Connell wrote:
On Jul 21, 2005, at 1:22 PM, Bruce Momjian wrote:
Thomas F. O'Connell wrote:
I'm switching the aftermath of this thread -- http://
archives.postgresql.org/pgsql-general/2005-07/msg00501.php -- to -
hackers since it raised issues of potential concern to developers.
At various points in the thread, Tom Lane said the following:
I have an old note to myself that persistent write errors could
clog
the bgwriter, because I was worried that after an error it would
stupidly try to write the same buffer again instead of trying to
make
progress elsewhere. (CVS tip might be better about this, I'm not
sure.)
A dirty buffer for a file that doesn't exist anymore would
certainly
qualify as a persistent failure.
and
Hmm ... a SELECT from one of the actual tables would then
scan the
temp tables too, no?
Thinking about this, I seem to recall that we had agreed to make
the
planner ignore temp tables of other backends when expanding an
inheritance list --- but I don't see anything in the code
implementing
that, so it evidently didn't get done yet.
I don't immediately see TODO items correpsonding to these. Should
there be some? Or do these qualify as bugs and should they be
submitted to that queue?
Would you show a query that causes the problem so I can properly
word
the TODO item for inheritance and temp tables?
It's really more of a timing issue than a specific query issue.
Here's a scenario:
CREATE TABLE parent ( ... );
begin thread1:
CREATE TEMP TABLE child ( ... ) INHERITS FROM ( parent );
begin thread2:
while( 1 ) {
SELECT ... FROM parent WHERE ...;
}
end thread1 (thereby dropping the temp table at the end of session)
At this point, the file is gone, but, as I understand it, the planner
not ignoring temp tables of other backends means that thread2 is
inappropriately accessing the temp table child as it performs
SELECTS, thus causing potential dirty buffers in bgwriter, which at
some point during the heavy activity of the tight SELECT loop, will
have the file yanked out from under it and will throw a No such
file error.
So I guess the core issue is the failure of the planner to limit
access to temp tables.
Tom seems to come pretty close to a TODO item in his analysis in my
opinion. Something like:
Make the planner ignore temp tables of other backends when expanding
an inheritance list.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i?
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
--
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 5: don't forget to increase your free space map settings
Re: [HACKERS] bgwriter, inherited temp tables TODO items?
On Jul 21, 2005, at 1:22 PM, Bruce Momjian wrote:
Thomas F. O'Connell wrote:
I'm switching the aftermath of this thread -- http://
archives.postgresql.org/pgsql-general/2005-07/msg00501.php -- to -
hackers since it raised issues of potential concern to developers.
At various points in the thread, Tom Lane said the following:
I have an old note to myself that persistent write errors could
clog
the bgwriter, because I was worried that after an error it would
stupidly try to write the same buffer again instead of trying to make
progress elsewhere. (CVS tip might be better about this, I'm not
sure.)
A dirty buffer for a file that doesn't exist anymore would certainly
qualify as a persistent failure.
and
Hmm ... a SELECT from one of the actual tables would then scan the
temp tables too, no?
Thinking about this, I seem to recall that we had agreed to make the
planner ignore temp tables of other backends when expanding an
inheritance list --- but I don't see anything in the code
implementing
that, so it evidently didn't get done yet.
I don't immediately see TODO items correpsonding to these. Should
there be some? Or do these qualify as bugs and should they be
submitted to that queue?
Would you show a query that causes the problem so I can properly word
the TODO item for inheritance and temp tables?
It's really more of a timing issue than a specific query issue.
Here's a scenario:
CREATE TABLE parent ( ... );
begin thread1:
CREATE TEMP TABLE child ( ... ) INHERITS FROM ( parent );
begin thread2:
while( 1 ) {
SELECT ... FROM parent WHERE ...;
}
end thread1 (thereby dropping the temp table at the end of session)
At this point, the file is gone, but, as I understand it, the planner
not ignoring temp tables of other backends means that thread2 is
inappropriately accessing the temp table child as it performs
SELECTS, thus causing potential dirty buffers in bgwriter, which at
some point during the heavy activity of the tight SELECT loop, will
have the file yanked out from under it and will throw a No such
file error.
So I guess the core issue is the failure of the planner to limit
access to temp tables.
Tom seems to come pretty close to a TODO item in his analysis in my
opinion. Something like:
Make the planner ignore temp tables of other backends when expanding
an inheritance list.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] No user being created during initdb for OS X
Do you already have a postgres user on the system? And do you mean that initdb is not creating a postgres user in the database? Presumably, if run as the user that will own the server process, it should create that user in the database as well. http://www.postgresql.org/docs/8.0/static/app-initdb.html -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jul 20, 2005, at 8:35 PM, Jamie Deppeler wrote: Hi, Having an issue with 10.4.2 at the moment when i initialize the database no user is being created. I have setup Postgresql 8.0.3 on 10.3 without any issue, has anyone come accross this problem before? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] bgwriter, inherited temp tables TODO items?
I'm switching the aftermath of this thread -- http://archives.postgresql.org/pgsql-general/2005-07/msg00501.php -- to -hackers since it raised issues of potential concern to developers.At various points in the thread, Tom Lane said the following:"I have an old note to myself that persistent write errors could "clog"the bgwriter, because I was worried that after an error it wouldstupidly try to write the same buffer again instead of trying to makeprogress elsewhere. (CVS tip might be better about this, I'm not sure.)A dirty buffer for a file that doesn't exist anymore would certainlyqualify as a persistent failure."and"Hmm ... a SELECT from one of the "actual tables" would then scan thetemp tables too, no?Thinking about this, I seem to recall that we had agreed to make theplanner ignore temp tables of other backends when expanding aninheritance list --- but I don't see anything in the code implementingthat, so it evidently didn't get done yet."I don't immediately see TODO items correpsonding to these. Should there be some? Or do these qualify as bugs and should they be submitted to that queue? -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
[HACKERS] pg_dump schema search_path; selectSourceSchema()
As I mentioned in the last post to a thread in general detailing some of the hurdles of attempting to set up PostgreSQL virtual hosting, we've had to hack pg_dump to achieve the behavior that we want.The modifications we made call into question (for us, anyway) a few design decisions in PostgreSQL utilities, and we're wondering if our modification has unintentional ramifications or if, as an option, it's something that the hackers see value in.The main issue for us is that we are relying on search_path to cause phpPgAdmin and pg_dump to find restricted views into the system catalogs. selectSourceSchema as written breaks our expectations when it is called with pg_catalog as a single argument.Our first attempt to get things working was to remove all calls to selectSourceSchema. Clearly, this is playing with fire, and I'll admit to not understanding all the references to schemas other than the explicit pg_catalog references.What we're currently thinking of doing is adding a flag like --search-path-prefix that would let us prepend any path requirements we have to the built-in calls to selectSourceSchema(). So that if we're putting our user-restricted system catalog views in public (as we're currently doing) or a custom schema (which would be my ideal preference), we can prepend that to the search_path, and pg_dump will behave as expected when run by a user in a hardened environment as long as users haven't done anything foolish. I'm just wondering whether such a patch seems sane and adds value from the perspective of hackers. Are we overlooking any scenarios in which pg_dump specifically requires a more restrictive search_path in order to behave properly?From a bigger picture standpoint, this is mostly necessary because there's no way to configure PostgreSQL to restrict access to the system catalogs in any meaningful way. I'm not really sure how to search for discussions on this topic in the archives, and I'm also not sure whether the forthcoming roles features will go any way toward eliminating the need for such behavior. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
Re: [HACKERS] [PATCHES] default database creation with initdb
What about just calling the new database postgres by default? For true newbies, the first thing that happens if you try just running psql with no arguments is that you discover there's no database named postgres. For most first-time users, I suspect the postgres user is the super-user and the first user used to access any database. Just throwing out another suggestion. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum in the backend
Gavin, For the record, I don't consider myself a PostgreSQL newbie, nor do I manage any 2 TB databases (much less tables), but I do have an unusual production use case: thousands ( 10,000) of tables, many of them inherited, and many of them with hundreds of thousands (a few with millions) of rows. Honestly, creating crontab vacuum management for this scenario would be a nightmare, and pg_autovacuum has been a godsend. Considering the recent revelations of O(n^2) iterations over table lists in the current versions and the stated and apparent ease with which this problem could be solved by integrating the basic functionality of pg_autovacuum into the backend, I can personally attest to there being real-world use cases that would benefit tremendously from integrated autovacuum. A few months ago, I attempted to solve the wrong problem by converting a hardcoded threshold into another command-line option. If I had spotted the O(n^2) problem, I might've spent the time working on it then instead of the new command-line option. I suppose it's possible that I'll head down this road anyway if it looks like integrated pg_autovacuum is going to be put on hold indefinitely after this discussion. Anyway, just wanted to throw out some food for thought for the practicality of a tool like pg_autovacuum. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jun 16, 2005, at 5:22 PM, Gavin Sherry wrote: On Thu, 16 Jun 2005, Alvaro Herrera wrote: On Thu, Jun 16, 2005 at 04:20:34PM +1000, Gavin Sherry wrote: 2) By no fault of its own, autovacuum's level of granularity is the table level. For people dealing with non-trivial amounts of data (and we're not talking gigabytes or terabytes here), this is a serious drawback. Vacuum at peak times can cause very intense IO bursts -- even with the enhancements in 8.0. I don't think the solution to the problem is to give users the impression that it is solved and then vacuum their tables during peak periods. I cannot stress this enough. People running systems with petabyte-sized tables can disable autovacuum for those tables, and leave it running for the rest. Then they can schedule whatever maintenance they see fit on their gigantic tables. Trying to run a database with more than a dozen gigabytes of data without expert advice (or at least reading the manual) would be extremely stupid anyway. As I've said a few times, I'm not concerned about such users. I'm concerned about users with some busy tables of a few hundred megabytes. I still don't think VACUUM at arbitary times on such tables is suitable. Thanks, Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views: Summary of Arguments
I'm not thinking exclusively in terms of whether they would be useful to me, personally. In fact, I'm certain that they would be useful to me, personally. What I question is whether they need to be a part of the internal development of PostgreSQL. To me, CPAN is an integral part of being able to do Perl development effectively. Whether or not a VPAN setup could come to seem as natural and easy for new users to use, I don't know. Regardless, these new views are going to need to be documented similarly so that new users are aware of them. And it still isn't clear (to me) how the debate over how to shape them as included by default will resolve, so punting to an externally maintained repository is just a suggestion as an alternative. I think it's important to consider the perspective of both developers and users, and the internal views clearly creates issues for the developers. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On May 11, 2005, at 11:35 AM, Josh Berkus wrote: Thomas, All, I guess I'm having difficulty understanding why the system catalogs themselves and provision of support for information_schema are not sufficient for what exists in core. Because you can't answer the question: What tables does user phil have update permissions on? or How many overloaded versions of function df_new_company() do I have?, and similar, without doing advanced queries on the system tables. Queries which are prone to mistakes: earlier on this thread a *pgsql hacker* posted a sample system catalog query which contained a mistake. Asking our general users to navigate the complexity of the system catalogs is just not good application practice. I don't really think a VPAN is any kind of solution for this purpose (though I'd like it for other things). The purpose of these views is to make PostgreSQL more user-friendly, and telling people: Oh just go to http://name/of/obscure/site, lookup these 10 views, log in as superuser and load them and you're golden is not much of an improvement in user-friendliness. To reiterate my point previously: these system views are NOT aimed at the people on *this* list; they are for the people on the -NOVICE and - GENERAL lists and IRC and the people who don't yet use PostgreSQL. Please stop thinking exclusively in terms of whether they would be useful to you, personally. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Help me recovering data
Does auto_vacuum vacuum the system tables? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 16, 2005, at 5:42 PM, Matthew T. O'Connor wrote: Tom Lane wrote: [EMAIL PROTECTED] writes: Maybe I'm missing something, but shouldn't the prospect of data loss (even in the presense of admin ignorance) be something that should be unacceptable? Certainly within the realm normal PostgreSQL operation. Once autovacuum gets to the point where it's used by default, this particular failure mode should be a thing of the past, but in the meantime I'm not going to panic about it. Which I hope will be soon. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] VACUUM FULL FREEZE is unsafe
So why not have VACUUM FULL FREEZE just do what you propose: VACUUM FULL then VACUUM FREEZE. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 27, 2004, at 3:41 PM, Tom Lane wrote: The point of VACUUM FREEZE is to ensure that there are no tuples present in the database whose commit status depends on normal XIDs. Without this guarantee, cloning template0 might stop working once the relevant part of pg_clog has been pruned. If one combines freezing with moving tuples across pages (ie, VACUUM FULL FREEZE), then the commit status of moved tuples may depend on the vacuum's own XID (stored in XVAC). To maintain the freeze safety guarantee, we'd want to be sure that upon successful completion of the VACUUM, there are no moved tuples that haven't had their status hint bits updated to XMIN_COMMITTED or XMIN_INVALID. After some digging through vacuum.c, I have convinced myself that this does occur for all tuples moved down from the end of the table. update_hint_bits() takes care of all MOVED_IN rows; MOVED_OFF rows in the page that becomes the physically last page of the table are fixed near the bottom of repair_frag(); and MOVED_OFF rows in pages after that don't matter because we'll truncate those pages away entirely. Unfortunately this still leaves one case uncovered, which is a tuple that is moved because it is part of an update chain. If an original tuple in an update chain is in a page that is below the new end of the table, and was not a move target page (eg because it had no free space), then that tuple will never be visited to change its state from MOVED_OFF to XMIN_INVALID. This doesn't break initdb, because there will be no update-chain cases since no other transactions can be running. But it poses a nasty hazard for anyone who is updating and re-freezing a template database during normal operations (as for example in following the manual bug fix procedures we had to recommend for some of the 7.4 dot releases). Also, even though I don't see any failure cases for initdb, it seems awfully risky to assume that this is all going to work 100%; and if initdb did leave any improperly frozen tuples behind, it's quite likely we'd not notice the error until the code got into the field. ISTM that the safer way to handle this is VACUUM FULL (to compact) and then VACUUM FREEZE (to freeze). It's much clearer that lazy VACUUM can handle freezing reliably, because it never tries to move tuples around. Just doing this in initdb is a one-liner change, but I'm wondering if we ought to enforce that FULL and FREEZE not be specified at the same time, so that people couldn't risk such a problem in manual freezing of template databases. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] pg_autovacuum
You also need to use -L to specify a location for the log file. By default pg_autovacuum just logs to STDERR, so if you daemonize the process (via -D), you won't be able to recover the output easily unless you explicitly select a log file location. -tfo On Sep 22, 2004, at 2:29 AM, Iulia Pacurar wrote: Hi! I run pg_autovacuum: ./pg_autovacuum -D but then I cannot find pg_autovacuum.log file. Where shoud I look for it? Thank you. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_autovacuum and v8.0
pg_autovacuum will still be in contrib as of 8.0. It did not make integration with the core distribution. -tfo On Sep 9, 2004, at 11:09 AM, Hervé Piedvache wrote: Hi, I was thinking that new things will appear in v8.0 about pg_autovacuum ?? But I find nothing new in README and/or Version History Any help ? Regards, -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib/start-scripts/linux on Debian
So what is the thinking of HACKERS? Does it make sense to include nothing and rather allow each distribution to support its own? Or should each distribution known to support postgres be invited to submit the relevant script for inclusion? And, if so, should this be a HACKERS [or other appropriate group/individual]-led effort? I'm persisting just because no conclusive action was recommended, and I think this is a mildly important usability issue. I don't know whether altering this piece of contrib is something that would be relevant for 8.0... -tfo On Sep 5, 2004, at 8:01 AM, Ryan Kirkpatrick wrote: On Fri, 27 Aug 2004, Peter Eisentraut wrote: Thomas F.O'Connell wrote: I'd love to see a comment added to the linux start-script included in contrib that referenced update-rc.d. It's useful to note because Debian has different runlevels from Red Hat, and update-rc.d will intelligently do the same thing as chkconfig for Debian systems. Why not just include the Debian start script instead of asking to user to fix up the existing one? I agree. I wrote that start-script a long time ago when there was not a very good one at all for Linux. Since then, the package maintainers for the various distributions have filled that void, making my script obsolete. TTYL. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib/start-scripts/linux on Debian
Sure. I didn't know there was a Debian start script. Because we frequently keep up with the latest changes in postgres (which we regularly use), I always build postgres from source. But that would be a perfectly acceptable solution. -tfo On Aug 27, 2004, at 12:45 AM, Peter Eisentraut wrote: Thomas F.O'Connell wrote: I'd love to see a comment added to the linux start-script included in contrib that referenced update-rc.d. It's useful to note because Debian has different runlevels from Red Hat, and update-rc.d will intelligently do the same thing as chkconfig for Debian systems. Why not just include the Debian start script instead of asking to user to fix up the existing one? ---(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
[HACKERS] contrib/start-scripts/linux on Debian
I'd love to see a comment added to the linux start-script included in contrib that referenced update-rc.d. It's useful to note because Debian has different runlevels from Red Hat, and update-rc.d will intelligently do the same thing as chkconfig for Debian systems. If it's useful, I could post a patch, but since it's just a comment that would be sufficient (IMHO), it seemed like a post with request was also sufficient. :) Something like: # Debian users might want to consider using update-rc.d since Debian has its own set of runlevels. Thanks! -tfo ---(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] why is postgres-R not in standard dev Path.
See http://www.slony.org/ It's a master-multislave replication system that has a pretty robust development cycle. It just reached a 1.0 release. Whether any solution becomes a core part of the distribution remains, I think, to be seen. -tfo On Jul 27, 2004, at 4:03 AM, chinni wrote: Postgres-R is a multi server (write anywhere) replication tool which is possibly important for any enterprise if they want to shift to postgres. Did you guys debate on merging it. As of now They are working on postgres 7.2 and developing postgres-R. They plan to do it for 7.4 as well, why not merge these things. Is the postgres team planing to come up with a similar tool themselves ? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] psql listTables
In examining the output of psql -E to get some templates for some
queries I'm developing, I noticed in describe.c that there is logic to
inform the final IN clause that gets printed for relkind but no similar
logic for the CASE clause.
Here's what I get from a \d in 7.4.1:
SELECT n.nspname as Schema, c.relname as Name,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as Type,
u.usename as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Such that the IN clause for c.relkind seems to preclude two of the
options ('i' and 's') in the CASE.
I realize this is a trivial issue, but it seems like logic could be
added to the CASE statement to prevent irrelevant SELECT material from
being output.
Couldn't the SELECT clause include if(showTables), etc.?
-tfo
---(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] psql listTables
I know, but I don't get too many opportunities to contribute... :) Just figured I'd mention it. -tfo On Jul 23, 2004, at 4:06 PM, Tom Lane wrote: Thomas F.O'Connell [EMAIL PROTECTED] writes: I realize this is a trivial issue, but it seems like logic could be added to the CASE statement to prevent irrelevant SELECT material from being output. Hardly seems worth the trouble ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Anyone want to assist with the translation of the Advocacy site?
Um, doesn't world's mean world is ? In this situation, the 's denotes possession, as in the most advanced open source database of the world. worlds here is basically saying every world most advanced open source database and does not, in any case, connote possession. -tfo ---(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] Anyone want to assist with the translation of the Advocacy site?
Um, doesn't world's mean world is ? i forgot to provide a real-world example: http://www.amazon.com/ Earth's Biggest Selection -tfo ---(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
[HACKERS] the optimizer and exists
i think i might've stumbled across a tiny defect in the optimizer. unfortunately, i haven't the knowledge of the code to know where to begin looking at how to address this problem. anyway, consider the following: create table foo( id int2 ); create table bar( id int2 foo_id int2 references foo( id ) ); imagine that the tables are populated. now, consider the query select b.foo_id from bar b where b.id = some id and exists( select * from foo f where b.foo_id = f.id and b.id = some id, as above ); now consider the same query with select constant in place of select * in the EXISTS subquery. explain analyze indicates that the constant version always runs a little bit faster. shouldn't the optimizer be able to determine that it isn't necessary actually to read a row in the case of EXISTS? i'm assuming that's where the overhead is coming into play. i realize this is minutiae in comparison to other aspects of development, but it is another small performance boost that could be added since i imagine many people, myself included, find it more natural to throw in select * rather than select constant. i didn't see this on the current lists or TODO, but if it's a dupe, i apologize for the noise. i also apologize for not being able to patch it, myself! -tfo ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Time for 7.2.1?
is there any further word on 7.2.1, at this point? haven't seen mention of it on the list in a while? is it still waiting on something big? -tfo Bruce Momjian wrote: Applied to current and 7.2.X. Thanks. (No delay for /contrib commits from maintainers.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: 7.2 items
Bruce Momjian wrote: Here is a small list of big TODO items. I was wondering which ones people were thinking about for 7.2? * Allow inherited tables to inherit index, UNIQUE constraint, and primary key [inheritance] i was wondering if there was any thought still being given to Oliver Elphick's post from a while back that is still in TODO.detail [inheritance]: http://candle.pha.pa.us/mhonarc/todo.detail/inheritance/msg00010.html i kind of feel as though the inheritance semantics for postgres at the moment are not fully fleshed out, and including further features without having a full plan for the semantics doesn't seem to advance the effort of making postgres a true Object-Relational DBMS. for my part, as a user, i am excited that inheritance is available even in a limited fashion, but where i use it, i have basically had to invent my own semantics for referential integrity based on a suite of triggers. this issue is addressed in Oliver's post, but i was wondering if such issues were still a part of the development dialogue since Oliver's post was the last in TODO.detail [inheritance] and seemed to merit no response (or any that i could find in the mailing list archives). -tfo ---(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
[HACKERS] Re: Learning from other open source databases
Bruce Momjian wrote: Here is a general call for people to review other open-source database software and report back on things PostgreSQL can learn from them. i don't know how much there is to learn since it doesn't seem as though development has been active in a few years, but there's also GNU SQL, which i had always hoped would develop into a useable system. http://www.ispras.ru/~kml/gss/index.html -tfo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
