[HACKERS] non-blocking CREATE INDEX in 8.2??

2007-07-13 Thread Jim C. Nasby
According to http://developer.postgresql.org/index.php/Feature_Matrix, 8.2 has non-blocking CREATE INDEX, which is news to me. Is it correct? http://developer.postgresql.org/index.php?title=Feature_Matrixoldid=1734 is the version where that was added (very early in the history of the page). --

Re: [HACKERS] non-blocking CREATE INDEX in 8.2??

2007-07-13 Thread Jim C. Nasby
On Fri, Jul 13, 2007 at 01:45:18PM -0700, Joshua D. Drake wrote: Jim C. Nasby wrote: According to http://developer.postgresql.org/index.php/Feature_Matrix, 8.2 has non-blocking CREATE INDEX, which is news to me. Is it correct? CREATE INDEX CONCURRENTLY Well, I guess it's a good thing

Re: [HACKERS] Idea: Comments on system catalogs?

2007-07-09 Thread Jim C. Nasby
On Wed, Jul 04, 2007 at 01:03:20PM +0200, Dawid Kuroczko wrote: Hello. I think it could be a nice idea to put descriptions from http://www.postgresql.org/docs/8.2/static/catalogs.html into system catalogs itself. I.e., make a bunch of COMMENT ON COLUMN pg_class.relname IS 'Name of

Re: [HACKERS] psql/pg_dump vs. dollar signs in identifiers

2007-07-09 Thread Jim C. Nasby
On Mon, Jul 09, 2007 at 07:04:27PM +0100, Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Now, because we surround the pattern with ^...$ anyway, I can't offhand see a use-case for putting $ with its regexp meaning into the pattern. It's possible to still usefully use $ in the

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Jim C. Nasby
On Tue, Jul 03, 2007 at 11:31:08AM +0200, Michael Paesold wrote: Joshua D. Drake wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: Did we change the default autovac parameters for 8.3 (beyond turning it on?) because on any reasonably used database, they are way to conservative. We're

Re: [HACKERS] Still recommending daily vacuum...

2007-07-03 Thread Jim C. Nasby
On Mon, Jul 02, 2007 at 11:19:12PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : Well, with autovac defaulting to ON in 8.3, that's certainly obsolete text now. Is there a reason to say anything

Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL

2007-07-03 Thread Jim C. Nasby
On Tue, Jul 03, 2007 at 11:49:05AM -0400, Bruce Momjian wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane escribi?: I rather doubt that. The most likely implementation would involve cloning a template entry into pg_class. How about a new relkind which

[HACKERS] Still recommending daily vacuum...

2007-07-02 Thread Jim C. Nasby
From http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : Recommended practice for most sites is to schedule a database-wide VACUUM once a day at a low-usage time of day, supplemented by more frequent vacuuming of heavily-updated tables if necessary. (Some installations with

Re: [HACKERS] Controlling Load Distributed Checkpoints

2007-06-13 Thread Jim C. Nasby
On Sun, Jun 10, 2007 at 08:49:24PM +0100, Heikki Linnakangas wrote: Jim C. Nasby wrote: On Thu, Jun 07, 2007 at 10:16:25AM -0400, Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Thinking about this whole idea a bit more, it occured to me that the current approach to write all

Re: [HACKERS] Controlling Load Distributed Checkpoints

2007-06-09 Thread Jim C. Nasby
On Thu, Jun 07, 2007 at 10:16:25AM -0400, Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Thinking about this whole idea a bit more, it occured to me that the current approach to write all, then fsync all is really a historical artifact of the fact that we used to use the

Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately

2007-06-08 Thread Jim C. Nasby
On Fri, Jun 08, 2007 at 09:49:56AM -0400, Matthew O'Connor wrote: Michael Paesold wrote: Matthew T. O'Connor schrieb: Do we need a configurable autovacuum naptime at all? I know I put it in the original contrib autovacuum because I had no idea what knobs might be needed. I can't see a

Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately

2007-06-08 Thread Jim C. Nasby
On Thu, Jun 07, 2007 at 12:13:09PM -0700, Andrew Hammond wrote: On 6/7/07, Jim C. Nasby [EMAIL PROTECTED] wrote: On Mon, Jun 04, 2007 at 11:04:26AM -0400, Alvaro Herrera wrote: The launcher is set up to wake up in autovacuum_naptime seconds at most. So if the user configures a ridiculuos

Re: [HACKERS] Command tags in create/drop scripts

2007-06-08 Thread Jim C. Nasby
On Tue, Jun 05, 2007 at 05:52:39PM -, Andrew Hammond wrote: On Jun 5, 9:19 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: Zdenek Kotala wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? I don't think so; there is no demand from anybody but Zdenek to

[HACKERS] Issues with factorial operator

2007-06-08 Thread Jim C. Nasby
I'm working with a customer that recently discovered that some code had generated the following nice query... SELECT ... WHERE table_id = 92838278! AND ... So their production server now has several processes that are trying to compute some absurdly large factorial. There's two issues here: 1)

Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately

2007-06-07 Thread Jim C. Nasby
On Mon, Jun 04, 2007 at 11:04:26AM -0400, Alvaro Herrera wrote: The launcher is set up to wake up in autovacuum_naptime seconds at most. So if the user configures a ridiculuos time (for example 86400 seconds, which I've seen) then the launcher would not detect the postmaster death Yeah, I've

Re: [HACKERS] To all the pgsql developers..Have a look at the operators proposed by me in my researc

2007-06-06 Thread Jim C. Nasby
On Sat, Jun 02, 2007 at 01:37:19PM +, Tasneem Memon wrote: We can make the system ask the user as to what membership degree s/he wants to get the values, but we don?t want to make the system interactive, where a user gives a membership degree value of his/her choice. These operators are

Re: [HACKERS] TOAST usage setting

2007-06-04 Thread Jim C. Nasby
On Fri, Jun 01, 2007 at 01:50:12PM -0400, Bruce Momjian wrote: I think the long-term solution is to go to a 2k/8k fragment/block model, but that isn't going to happen for 8.3. There might well have been lessons learned since UFS (anyone know what ZFS does in this regard?), but I agree that we

[HACKERS] ecpg test runs out of disk space

2007-05-27 Thread Jim C. Nasby
Just had a rather disturbing event happen on platypus.. [EMAIL PROTECTED]:07]~/buildfarm/HEAD/pgsql.39276/src/interfaces/ecpg/test/results:271ll preproc-variable.* -rw-r--r-- 1 buildfarm decibel 6328 May 26 23:42 preproc-variable.c -rw-r--r-- 1 buildfarm decibel 76460670919 May 27

Re: [HACKERS] COPY into a view; help w. design patch

2007-05-21 Thread Jim C. Nasby
On Sat, May 19, 2007 at 01:41:47PM -0400, Tom Lane wrote: I _could_ make tables that correspond to the views and put BEFORE INSERT triggers on them and have the triggers insert into the views (or the equalivent), but then the users would have to use the views for most things and the

Re: [HACKERS] COPY into a view; help w. design patch

2007-05-21 Thread Jim C. Nasby
On Mon, May 21, 2007 at 05:02:29PM +, Karl O. Pinc wrote: On 05/21/2007 11:23:57 AM, Jim C. Nasby wrote: What about adding COPY support to rules? ISTM if you want to copy into a view you probably want to insert into it as well, so why not use the same mechanism? Presumably a COPY rule

Re: [HACKERS] Not ready for 8.3

2007-05-18 Thread Jim C. Nasby
On Thu, May 17, 2007 at 11:02:31PM -0400, Greg Smith wrote: On Thu, 17 May 2007, David Fetter wrote: Would you be interested in providing this meat? You're uniquely qualified because your shins still smart from all the things you barked them on :) Unfortunately I'm temporarily on the

Re: [HACKERS] Async commands (like drop index)

2007-05-18 Thread Jim C. Nasby
On Thu, May 17, 2007 at 12:30:45PM -0700, Joshua D. Drake wrote: It seems that it may be useful to allow something like: DROP INDEX NOWAIT. The idea being, that the terminal will come back, the index will be dropped in the background. If it doesn't drop, it rollback like normal and

Re: [HACKERS] Lack of urgency in 8.3 reviewing

2007-05-18 Thread Jim C. Nasby
On Fri, May 18, 2007 at 12:33:11AM +0800, Cui Shijun wrote: Thank you for your suggestions, I am thinking about Full page writes improvement. It seems not so complicated, just fit for a novice like me. I'll work on it. :-) Updated on

Re: [HACKERS] Lack of urgency in 8.3 reviewing

2007-05-18 Thread Jim C. Nasby
On Fri, May 18, 2007 at 03:21:00PM +0200, Zdenek Kotala wrote: What's about - full page writes improvement but I will have time after PGCon. Added you to the list for that at http://developer.postgresql.org/index.php/Todo:PatchStatus -- Jim Nasby

Re: [HACKERS] Async commands (like drop index)

2007-05-18 Thread Jim C. Nasby
On Fri, May 18, 2007 at 01:39:56PM -0400, Neil Conway wrote: On Fri, 2007-18-05 at 13:29 -0400, Alvaro Herrera wrote: I think what Joshua really wants is an equivalent of this That's not what his original email asked for, at any rate. start: BEGIN; LOCK TABLE foo IN ACCESS

Re: [HACKERS] Managing the community information stream

2007-05-16 Thread Jim C. Nasby
On Wed, May 16, 2007 at 10:46:50AM -0400, Alvaro Herrera wrote: I am not sure. We will have to investigate more the capabilities of the bug tracking system we intend to use. In the worst case one could add the URL for the archived message copy; second worst would be bouncing

Re: [HACKERS] Not ready for 8.3

2007-05-16 Thread Jim C. Nasby
On Wed, May 16, 2007 at 08:58:44AM +0100, Dave Page wrote: Jim C. Nasby wrote: On Tue, May 15, 2007 at 10:32:14PM +0200, Magnus Hagander wrote: Stefan Kaltenbrunner wrote: They are not stable. The items should point to the archives, which are supposedly more stable. (I had already fixed

Re: [HACKERS] 8.3 pending patch queue

2007-05-16 Thread Jim C. Nasby
On Wed, May 16, 2007 at 12:33:38AM -0300, Marc G. Fournier wrote: Someone (you, I think) advocated a '3 weeks and then dump the rest of the patches' (not quote as strong of wording, but similar) ... why not split the patches list up: submitted patches, not reviewed reviewed patches,

Re: [HACKERS] Testing concurrent psql

2007-05-16 Thread Jim C. Nasby
On Wed, May 16, 2007 at 09:43:36AM -0400, Gregory Stark wrote: I'm looking for corner cases where the concurrent psql patch doesn't handle things properly. I'm wondering about multiple result sets but I can't think of any cases where I can test them. If you submit multiple commands at the

Re: [HACKERS] Not ready for 8.3

2007-05-16 Thread Jim C. Nasby
On Wed, May 16, 2007 at 04:34:56PM +0100, Dave Page wrote: How much visibility do we have into the mhonarc database? We should be able to come up with a simple redirector that would point the old mhonarc URLs to URLs for the new system... coughdatabase?/cough And here I thought the reason

Re: [HACKERS] Not ready for 8.3

2007-05-16 Thread Jim C. Nasby
On Wed, May 16, 2007 at 07:48:10PM +0200, Magnus Hagander wrote: Dave Page wrote: I the current URLs represent the month, and the ID of the message as it comes out of the mbox I believe. We could probably write a script to dump a list of message IDs, directories and mbox positions I

Re: [HACKERS] Not ready for 8.3

2007-05-16 Thread Jim C. Nasby
On Wed, May 16, 2007 at 09:32:44PM +0100, Richard Huxton wrote: Dave Page wrote: Richard Huxton wrote: Magnus Hagander wrote: It's been on my list to rewrite the whole archive system for a while for various reasons. There is quite a bit of crossover with the patch tracker I proposed so I was

Re: [HACKERS] Managing the community information stream

2007-05-15 Thread Jim C. Nasby
On Tue, May 15, 2007 at 01:18:42PM -0400, Bruce Momjian wrote: In Debian's bug tracking system, when the bug is created (which is done by sending an email to a certain address) it gets a number, and the email is distributed to certain lists. People can then reply to that mail, and send

Re: [HACKERS] Seq scans roadmap

2007-05-15 Thread Jim C. Nasby
On Tue, May 15, 2007 at 10:25:35AM -0700, Jeff Davis wrote: On Tue, 2007-05-15 at 10:42 +0100, Heikki Linnakangas wrote: Luke Lonergan wrote: 32 buffers = 1MB with 32KB blocksize, which spoils the CPU L2 cache effect. How about using 256/blocksize? Sounds reasonable. We need

Re: [HACKERS] Not ready for 8.3

2007-05-15 Thread Jim C. Nasby
On Tue, May 15, 2007 at 12:42:28PM -0400, Bruce Momjian wrote: Joshua D. Drake wrote: Patch status: http://developer.postgresql.org/index.php/Todo:PatchStatus If... this is actually a problem (I leave to other committers and reviewers to comment) then I suggest we push all

Re: [HACKERS] Not ready for 8.3

2007-05-15 Thread Jim C. Nasby
On Tue, May 15, 2007 at 10:32:14PM +0200, Magnus Hagander wrote: Stefan Kaltenbrunner wrote: They are not stable. The items should point to the archives, which are supposedly more stable. (I had already fixed one item in PatchStatus this morning). Really it would be much nicer to have

Re: [HACKERS] Bulk inserts and usage_count

2007-05-15 Thread Jim C. Nasby
On Tue, May 15, 2007 at 04:37:28PM +0100, Heikki Linnakangas wrote: While testing the buffer ring patch, I noticed that bulk inserts with both INSERT and COPY pin and unpin the buffer they insert to for every tuple. That means that the usage_count of all those buffers are bumped snip A fix

Re: [HACKERS] Not ready for 8.3

2007-05-15 Thread Jim C. Nasby
On Tue, May 15, 2007 at 07:01:39PM -0400, Bruce Momjian wrote: Unless you're really in love with doing that sort of thing it's really good that someone else did it. You're one of a handful of folks that can actually review patches, while there's any number of us that can update a wiki.

Re: [HACKERS] Interaction of PITR backups and Bulkoperationsavoiding WAL

2007-05-15 Thread Jim C. Nasby
Simon intended to commit this per http://archives.postgresql.org/pgsql-hackers/2007-03/msg01761.php (actually, there was a change in what was being done). I suspect this item isn't valid any longer. On Tue, May 15, 2007 at 07:30:58PM -0400, Bruce Momjian wrote: This has been saved for the 8.4

Re: [HACKERS] Performance monitoring

2007-05-13 Thread Jim C. Nasby
On Sun, May 13, 2007 at 07:54:20AM +0100, Heikki Linnakangas wrote: Maybe we should improve the stats system so that we can collect events with timestamps and durations, but in my experience log files actually are the most reliable and universal way to collect real-time performance

[HACKERS] Performance monitoring (was: [PATCHES] Logging checkpoints and other slowdown causes)

2007-05-12 Thread Jim C. Nasby
Moving to -hackers. On Fri, May 11, 2007 at 04:37:44PM +0100, Heikki Linnakangas wrote: If you know when the checkpoint ended, and you know how long each of the pieces took, you can reconstruct the other times easily. The way you describe this it is true--that the summary is redundant given

Re: [HACKERS] utf8 COPY DELIMITER?

2007-04-18 Thread Jim C. Nasby
On Tue, Apr 17, 2007 at 02:28:18PM -0400, Tom Lane wrote: I doubt that supporting a single multibyte character would be an interesting extension --- if we wanted to do anything at all there, we'd just generalize the delimiter to be an arbitrary string. But it would certainly slow down COPY by

Re: [HACKERS] Background LRU Writer/free list

2007-04-18 Thread Jim C. Nasby
On Wed, Apr 18, 2007 at 09:09:11AM -0400, Greg Smith wrote: I'm mostly done with my review of the Automatic adjustment of bgwriter_lru_maxpages patch. In addition to issues already brought up with that code, there are some small things that need to be done to merge it with the recent

Re: [HACKERS] Hacking on PostgreSQL via GIT

2007-04-18 Thread Jim C. Nasby
On Wed, Apr 18, 2007 at 06:39:34PM +1200, Martin Langhoff wrote: Keyword expansions are generally bad because SCM tools should track _content_ - and keyword expansions _modify_ it to add metadata that is somewhat redundant, obtainable in other ways, and should just not be in the middle of the

Re: [HACKERS] Hacking on PostgreSQL via GIT

2007-04-18 Thread Jim C. Nasby
On Thu, Apr 19, 2007 at 10:07:08AM +1200, Martin Langhoff wrote: Jim C. Nasby wrote: Then how do you tell what version a file is if it's outside of a checkout? It's trivial for git to answer that - the file will either be pristine, and then we can just scan for the matching SHA1

Re: [HACKERS] Autovacuum vs statement_timeout

2007-04-17 Thread Jim C. Nasby
On Sun, Apr 01, 2007 at 12:36:01AM +0200, Peter Eisentraut wrote: Tom Lane wrote: I seem to remember that we'd agreed that autovacuum should ignore any globally set statement_timeout, on the grounds that a poorly chosen setting could indefinitely prevent large tables from being vacuumed.

Re: [HACKERS] where to write small reusable functions ?

2007-04-17 Thread Jim C. Nasby
On Fri, Apr 13, 2007 at 03:02:28PM +0200, Dany DeBontridder wrote: On 4/13/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Dany DeBontridder wrote: I'm working to implement a new feature to pg_dump: the ability to dump objects like function, indexes... pg_dump already dumps functions

Re: [HACKERS] SoC Students/Projects selected

2007-04-17 Thread Jim C. Nasby
On Sun, Apr 15, 2007 at 08:00:23PM -0300, Josh Berkus wrote: Now, while each of these students has an assigned mentor, that doesn't mean other people shouldn't help. If you're interested in their work, please pitch in. Note that we'll also be using the pgsql-students mailing list for

Re: [HACKERS] Autovacuum vs statement_timeout

2007-04-17 Thread Jim C. Nasby
On Tue, Apr 17, 2007 at 12:51:51PM -0700, Joshua D. Drake wrote: Jim C. Nasby wrote: On Sun, Apr 01, 2007 at 12:36:01AM +0200, Peter Eisentraut wrote: Tom Lane wrote: I seem to remember that we'd agreed that autovacuum should ignore any globally set statement_timeout, on the grounds

Re: CREATE INDEX and HOT (was [HACKERS] Question: pg_classattributes and race conditions ?)

2007-03-22 Thread Jim C. Nasby
On Mon, Mar 19, 2007 at 12:05:19PM +, Simon Riggs wrote: I was unwilling to compromise to have HOT if only one index existed, but IMHO allowing HOT with = 3 indexes is an acceptable compromise for this release. (We can always use vertical partitioning techniques to allow additional access

Re: [HACKERS] TOASTing smaller things

2007-03-21 Thread Jim C. Nasby
On Wed, Mar 21, 2007 at 12:37:36PM -0400, Chris Browne wrote: 4. A different mechanism would be to add a fifth storage column strategy (the present four are PLAIN, EXTENDED, EXTERNAL, MAIN), let's say, TOAST. At present, the 4 values are essentially advisory; columns get TOASTed if the

Re: [HACKERS] Interaction of PITR backups and Bulkoperationsavoiding WAL

2007-03-20 Thread Jim C. Nasby
On Fri, Mar 09, 2007 at 04:57:18PM +, Simon Riggs wrote: On Fri, 2007-03-09 at 11:47 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2007-03-09 at 11:15 -0500, Tom Lane wrote: It strikes me that allowing archive_command to be changed on the fly might not be

Re: [HACKERS] Stats for multi-column indexes

2007-03-20 Thread Jim C. Nasby
On Mon, Mar 19, 2007 at 06:55:56PM -0700, Jeff Davis wrote: On Mon, 2007-03-19 at 21:24 -0400, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: We can already keep stats for a functional index. Is there a reason we can't keep stats for a multi-column index? The questions that

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Jim C. Nasby
On Thu, Mar 08, 2007 at 01:52:27PM +0530, NikhilS wrote: I think it'd be great to make adding and removing partitions as simple as ALTER TABLE. I don't think that DELETE should be the mechanism to drop a partition, though. Again, DML statements shouldn't be performing DDL. Since partition

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Jim C. Nasby
On Thu, Mar 08, 2007 at 10:12:30AM +, Gregory Stark wrote: Jim Nasby [EMAIL PROTECTED] writes: One possibility would be to require 3 functions for a partitioned table: one accepts the partitioning key and tells you what partition it's in, one that tells you what the minimum

Re: [HACKERS] Auto creation of Partitions

2007-03-08 Thread Jim C. Nasby
One other thought... a lot of this discussion seems to parallel the debate from a while ago about whether SERIAL should act like a macro (ie: it just sets everything up and users are free to monkey under the hood afterwards), or whether it should be it's own 'closed-box' construct. Currently, we

Re: [HACKERS] Estimating seq_page_fetch and random_page_fetch

2007-03-08 Thread Jim C. Nasby
On Thu, Mar 08, 2007 at 05:35:03PM +, Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Umar Farooq Minhas [EMAIL PROTECTED] writes: How can we accrately estimate the seq_page_fetch and = random_page_fetch costs from outside the postgres using for example a = C routine.

Re: [HACKERS] Resumable vacuum proposal and design overview

2007-03-01 Thread Jim C. Nasby
On Wed, Feb 28, 2007 at 10:14:24PM +, Heikki Linnakangas wrote: cache instead. In the index scan phase, it's randomly accessed, but if the table is clustered, it's in fact not completely random access. In the 2nd vacuum pass, the array is scanned sequentially again. I'm not Only if

Re: [HACKERS] COMMIT NOWAIT Performance Option

2007-03-01 Thread Jim C. Nasby
On Wed, Feb 28, 2007 at 12:16:10PM -0500, Bruce Momjian wrote: background writer, and I think after a server crash, all pages would have to be read and checked. The good news is that both of these are Would they? If you're doing recovery you'd have to read all pages dirtied since the last

Re: [HACKERS] Dead Space Map version 2

2007-03-01 Thread Jim C. Nasby
On Wed, Feb 28, 2007 at 04:10:09PM +0900, ITAGAKI Takahiro wrote: Jim C. Nasby [EMAIL PROTECTED] wrote: At some point it might make sense to convert the FSM into a bitmap; that way everything just scales with database size. In the meantime, I'm not sure if it makes sense to tie the FSM

Re: [HACKERS] SOC user quotas

2007-03-01 Thread Jim C. Nasby
On Wed, Feb 28, 2007 at 02:29:52PM -0800, Joshua D. Drake wrote: I don't know, but in my opinion, I don't see anything bad in requiring dropping the data if the quota is full. That's what usually occurs in the case of normal filesystem quota... If you don't have a space there, you cannot

Re: [HACKERS] Revitalising VACUUM FULL for 8.3

2007-03-01 Thread Jim C. Nasby
On Thu, Mar 01, 2007 at 04:54:44PM +0200, Hannu Krosing wrote: I was not suggesting that we remove visible rows through truncation. Sure, unless you suggest to not truncate during this vacuum run ? But we are talking about vacuum full, so truncation is essential. It was suggested to

Re: [HACKERS] Revitalising VACUUM FULL for 8.3

2007-03-01 Thread Jim C. Nasby
On Thu, Mar 01, 2007 at 01:05:28PM +, Simon Riggs wrote: ISTM a radical approach is needed, so I'm very open to discussion about this and how we cope. If we break down the main thoughts into a few parts: 1. would like a way to CLUSTER/VACUUM FULL where we don't have to move all of the

Re: [HACKERS] autovacuum next steps, take 2

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 01:26:00AM -0500, Matthew T. O'Connor wrote: Tom Lane wrote: Matthew T. O'Connor matthew@zeut.net writes: I'm not sure what you are saying here, are you now saying that partial vacuum won't work for autovac? Or are you saying that saving state as Jim is describing

Re: [HACKERS] autovacuum next steps, take 2

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 12:54:28AM -0500, Matthew T. O'Connor wrote: Jim C. Nasby wrote: On Mon, Feb 26, 2007 at 10:18:36PM -0500, Matthew T. O'Connor wrote: Jim C. Nasby wrote: Here is a worst case example: A DB with 6 tables all of which are highly active and will need to be vacuumed

Re: [HACKERS] autovacuum next steps, take 2

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 12:00:41AM -0300, Alvaro Herrera wrote: Jim C. Nasby wrote: The advantage to keying this to autovac_naptime is that it means we don't need another GUC, but after I suggested that before I realized that's probably not the best idea. For example, I've seen clusters

Re: [HACKERS] Resumable vacuum proposal and design overview

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 11:44:28AM +0900, Galy Lee wrote: For example, there is one table: - The table is a hundreds GBs table. - It takes 4-8 hours to vacuum such a large table. - Enabling cost-based delay may make it last for 24 hours. - It can be vacuumed during night time for

Re: [HACKERS] Seeking Google SoC Mentors

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 12:47:14AM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Yes, but the list being discussed is SoC projects that the community would like to see done, which means most people would assume that #1 isn't an issue. We need to make sure that every

Re: [HACKERS] Dead Space Map version 2

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 12:55:21AM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Yes, DSM would make FSM recovery more important, but I thought it was recoverable now? Or is that only on a clean shutdown? Currently we throw away FSM during any non-clean restart

Re: [HACKERS] Dead Space Map version 2

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 05:38:39PM +0900, ITAGAKI Takahiro wrote: Jim C. Nasby [EMAIL PROTECTED] wrote: If we do UPDATE a tuple, the original page containing the tuple is marked as HIGH and the new page where the updated tuple is placed is marked as LOW. Don't you mean UNFROZEN

Re: [HACKERS] autovacuum next steps, take 2

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 12:12:22PM -0500, Matthew T. O'Connor wrote: Jim C. Nasby wrote: On Tue, Feb 27, 2007 at 12:00:41AM -0300, Alvaro Herrera wrote: Jim C. Nasby wrote: The advantage to keying this to autovac_naptime is that it means we don't need another GUC, but after I suggested

Re: [HACKERS] COMMIT NOWAIT Performance Option

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 10:49:32AM +, Simon Riggs wrote: I dislike introducing new nonstandard syntax (Oracle compatible is not standard). If we did this I'd vote for control via a GUC setting only; I think that is more useful anyway, as an application can be made to run with such a

Re: [HACKERS] No ~ operator for box, point

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 20, 2007 at 04:22:27PM -0500, Bruce Momjian wrote: Added to TODO: * Add missing operators for geometric data types Some geometric types do not have the full suite of geometric operators, e.g. box @ point I've started looking at this, and ISTM

Re: [HACKERS] COMMIT NOWAIT Performance Option

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 05:18:28PM -0500, Bruce Momjian wrote: Jim C. Nasby wrote: On Mon, Feb 26, 2007 at 10:56:58PM +, Simon Riggs wrote: 2. remove fsync parameter Why? Wouldn't fsync=off still speed up checkpoints? ISTM you'd still want this for things like database restores

Re: [HACKERS] COMMIT NOWAIT Performance Option

2007-02-27 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 07:17:37PM -0500, Bruce Momjian wrote: Actually, I don't know that combining both settings is a wise move. The delay should still provide crash protection, whereas with fsync=off you've got absolutely no protection from anything. That's a huge difference, and one

Re: [HACKERS] autovacuum next steps, take 2

2007-02-26 Thread Jim C. Nasby
On Mon, Feb 26, 2007 at 09:22:42PM -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Matthew T. O'Connor wrote: I'm not sure it's a good idea to tie this to the vacuum cost delay settings either, so let me as you this, how is this better than just allowing the admin to set

Re: [HACKERS] autovacuum next steps, take 2

2007-02-26 Thread Jim C. Nasby
On Mon, Feb 26, 2007 at 08:11:44PM -0300, Alvaro Herrera wrote: Matthew T. O'Connor wrote: Alvaro Herrera wrote: The second mode is the hot table worker mode, enabled when the worker detects that there's already a worker in the database. In this mode, the worker is limited to those

Re: [HACKERS] autovacuum next steps, take 2

2007-02-26 Thread Jim C. Nasby
On Mon, Feb 26, 2007 at 06:23:22PM -0500, Matthew T. O'Connor wrote: Alvaro Herrera wrote: Matthew T. O'Connor wrote: How can you determine what tables can be vacuumed within autovacuum_naptime? My assumption is that pg_class.relpages * vacuum_cost_page_miss * vacuum_cost_delay = time to

Re: [HACKERS] Seeking Google SoC Mentors

2007-02-26 Thread Jim C. Nasby
On Mon, Feb 26, 2007 at 09:10:38PM -0500, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Well, here's a question. Given the recent discussion re full disjunction, I'd like to know what sort of commitment we are going to give people who work on proposed projects. Um, if you

Re: [HACKERS] COMMIT NOWAIT Performance Option

2007-02-26 Thread Jim C. Nasby
On Mon, Feb 26, 2007 at 10:56:58PM +, Simon Riggs wrote: 2. remove fsync parameter Why? Wouldn't fsync=off still speed up checkpoints? ISTM you'd still want this for things like database restores. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB

Re: [HACKERS] Dead Space Map version 2

2007-02-26 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 12:05:57PM +0900, ITAGAKI Takahiro wrote: Each heap pages have 4 states for dead space map; HIGH, LOW, UNFROZEN and FROZEN. VACUUM uses the states to reduce the number of target pages. - HIGH : High priority to vacuum. Maybe many dead tuples in the page. - LOW

Re: [HACKERS] Expanding DELETE/UPDATE returning

2007-02-26 Thread Jim C. Nasby
On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote: Rusty Conover [EMAIL PROTECTED] writes: I didn't see this on the TODO list, but if it is my apologies. Is it in the cards to expand the functionality of DELETE/UPDATE returning to be able to sort the output of the rows returned?

Re: [HACKERS] autovacuum next steps, take 2

2007-02-26 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 12:00:41AM -0300, Alvaro Herrera wrote: Jim C. Nasby wrote: The advantage to keying this to autovac_naptime is that it means we don't need another GUC, but after I suggested that before I realized that's probably not the best idea. For example, I've seen clusters

Re: [HACKERS] autovacuum next steps, take 2

2007-02-26 Thread Jim C. Nasby
On Mon, Feb 26, 2007 at 10:48:49PM -0500, Tom Lane wrote: Matthew T. O'Connor matthew@zeut.net writes: That does sounds simpler. Is chunk-at-a-time a realistic option for 8.3? It seems fairly trivial to me to have a scheme where you do one fill-workmem-and-scan-indexes cycle per invocation,

Re: [HACKERS] COMMIT NOWAIT Performance Option

2007-02-26 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 11:05:45AM +0700, Jeroen T. Vermeulen wrote: On Tue, February 27, 2007 06:06, Joshua D. Drake wrote: Why do we want this?? Because some apps have *lots* of data and many really don't care whether they lose a few records. Honestly, I've met people that want this,

Re: [HACKERS] autovacuum next steps, take 2

2007-02-26 Thread Jim C. Nasby
On Mon, Feb 26, 2007 at 10:18:36PM -0500, Matthew T. O'Connor wrote: Jim C. Nasby wrote: On Mon, Feb 26, 2007 at 06:23:22PM -0500, Matthew T. O'Connor wrote: I'm not sure how pg_class.relpages is maintained but what happens to a bloated table? For example, a 100 row table that is constantly

Re: [HACKERS] autovacuum next steps, take 2

2007-02-26 Thread Jim C. Nasby
On Tue, Feb 27, 2007 at 12:37:42AM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: The proposal to save enough state to be able to resume a vacuum at pretty much any point in it's cycle might work; we'd have to benchmark it. With the default maintenance_work_mem of 128M

Re: [HACKERS] Simple Column reordering

2007-02-23 Thread Jim C. Nasby
On Fri, Feb 23, 2007 at 02:09:55PM +, Simon Riggs wrote: If you really want an interim solution, what about a builtin function that would explicitly mutate the definition and table contents (if any) along the lines you want? (assuming that's lots less work than just doing the whole

Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)

2007-02-23 Thread Jim C. Nasby
Yes, but if it was '2004-01-02 01:00:00'-'2004-01-01 00:00:00' it should return 25:00:00, not 1 day 1:00. I agree with Tom that this should be changed; I'm just arguing that we might well need a backwards-compatibility solution for a while. At the very least we'd need to make this change very

Re: [HACKERS] Status of Hierarchical Queries

2007-02-23 Thread Jim C. Nasby
On Thu, Feb 22, 2007 at 07:59:35AM +, Gregory Stark wrote: Gavin Sherry [EMAIL PROTECTED] writes: On Thu, 22 Feb 2007, Gregory Stark wrote: But in a simple recursive tree search you have a node which wants to do a join between the output of tree level n against some table to

Re: [HACKERS] Priorities for users or queries?

2007-02-23 Thread Jim C. Nasby
On Wed, Feb 21, 2007 at 04:14:35PM +, Jos?? Orlando Pereira wrote: Benjamin Arai wrote: Is there a way to give priorities to queries or users? Something similar to NICE in Linux. My goal is to give the updating (backend) application a very low priority and give the web application a

Re: [HACKERS] autovacuum next steps, take 2

2007-02-23 Thread Jim C. Nasby
On Fri, Feb 23, 2007 at 01:22:17PM -0300, Alvaro Herrera wrote: Jim C. Nasby wrote: On Thu, Feb 22, 2007 at 10:32:44PM -0500, Matthew T. O'Connor wrote: I'm not sure this is a great idea, but I don't see how this would result in large numbers of workers working in one database

Re: [HACKERS] Simple Column reordering

2007-02-23 Thread Jim C. Nasby
doing both physical and logical ordering code at the same time). On Fri, Feb 23, 2007 at 01:04:14PM -0300, Alvaro Herrera wrote: Jim C. Nasby wrote: On Fri, Feb 23, 2007 at 02:09:55PM +, Simon Riggs wrote: If you really want an interim solution, what about a builtin function that would

Re: [HACKERS] SCMS question

2007-02-23 Thread Jim C. Nasby
On Fri, Feb 23, 2007 at 08:32:34AM -0800, Joshua D. Drake wrote: I am happy to help with this any way I can, because I would love to see CVS take a big diving leap off the backend of mysql into the truncated data set of hell. That quote made the whole argument coming up again worthwhile. :) --

Re: [HACKERS] Proposal for Implenting read-only queries during wal replay (SoC 2007)

2007-02-23 Thread Jim C. Nasby
On Fri, Feb 23, 2007 at 10:57:24PM +, Heikki Linnakangas wrote: Florian G. Pflug wrote: I plan to submit a proposal for implementing support for read-only queries during wal replay as a Google Summer of Code 2007 project. I've been browsing the postgres source-code for the last few

Re: [HACKERS] SCMS question

2007-02-23 Thread Jim C. Nasby
On Fri, Feb 23, 2007 at 04:24:29PM -0700, Warren Turkal wrote: On Friday 23 February 2007 15:50, you wrote: How to people get a branch? Do they have their own logins? If monotone is something like Git, you just create it in your local working copy and push is somewhere public when you are

Re: [HACKERS] [GENERAL] pg_autovacuum should allow NULL values

2007-02-23 Thread Jim C. Nasby
On Fri, Feb 23, 2007 at 06:47:52PM -0500, Tom Lane wrote: I wrote: I don't find this particularly important, because we have never intended direct update of catalog entries to be a primary way of interacting with the system. The current pg_autovacuum setup is a stopgap until the dust has

Re: [HACKERS] autovacuum next steps, take 2

2007-02-22 Thread Jim C. Nasby
On Wed, Feb 21, 2007 at 05:40:53PM -0500, Matthew T. O'Connor wrote: My Proposal: If we require admins to identify hot tables tables, then: 1) Launcher fires-off a worker1 into database X. 2) worker1 deals with hot tables first, then regular tables. 3) Launcher continues to launch workers to

Re: [HACKERS] autovacuum next steps, take 2

2007-02-22 Thread Jim C. Nasby
On Thu, Feb 22, 2007 at 09:32:57AM -0500, Matthew T. O'Connor wrote: Jim C. Nasby wrote: On Wed, Feb 21, 2007 at 05:40:53PM -0500, Matthew T. O'Connor wrote: My Proposal: If we require admins to identify hot tables tables, then: 1) Launcher fires-off a worker1 into database X. 2

Re: [HACKERS] autovacuum next steps, take 2

2007-02-22 Thread Jim C. Nasby
On Thu, Feb 22, 2007 at 09:35:45AM +0100, Zeugswetter Andreas ADI SD wrote: vacuum should be a process with the least amount of voodoo. If we can just have vacuum_delay and vacuum_threshold, where threshold allows an arbitrary setting of how much bandwidth we will allot to the

  1   2   3   4   5   6   7   8   9   10   >