Re: [HACKERS] 8.0beta5 results w/ dbt2
Mark Wong wrote: I have some initial results using 8.0beta5 with our OLTP workload. Off the bat I see about a 23% improvement in overall throughput. The most significant thing I've noticed was in the oprofile report where FunctionCall2 and hash_seq_search have moved down the profile a bit. Also, I have libc with symbols now so we can see what it's doing with in the oprofile output. 8.0beta5 results: http://www.osdl.org/projects/dbt2dev/results/dev4-010/199/ throughput: 4076.97 8.0beta4 results: http://www.osdl.org/projects/dbt2dev/results/dev4-010/191/ throughput: 3323.07 Is it possible that there are some other differences effecting the result? At "Table Blocks Read" there is a "history" table in #191, but it does not show up in #199. Just a thought,... but can you explain, Mark? Best Regards, Michael ---(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] [GENERAL] Column n.nsptablespace does not exist error
On Tue, 2004-11-30 at 17:54 +1100, Johan Wehtje wrote: > I am getting the error "Column n.nsptablespace does not exist" in my > application when I connect using my Administrative tool. This only > happens with Version 8, but it does crash my application, does anyone > have any ideas ? You need to upgrade your admin tool -- that column was removed from the system catalogs in beta5. See: http://archives.postgresql.org/pgsql-hackers/2004-11/msg00987.php -Neil ---(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: [Testperf-general] Re: [HACKERS] 8.0beta5 results w/ dbt2
On Tue, 2004-11-30 at 04:35, Tom Lane wrote: > Mark Wong <[EMAIL PROTECTED]> writes: > > I have some initial results using 8.0beta5 with our OLTP workload. > > Off the bat I see about a 23% improvement in overall throughput. > > Between beta4 and beta5? That's astonishing. We didn't really do very > much that was performance-focused. Digging in the CVS logs, I see only > some changes intended to speed up subtransaction commit, which I suppose > is not relevant to your benchmark, plus these two changes: > > 2004-11-16 22:13 neilc > > * src/backend/access/: hash/hash.c, nbtree/nbtree.c: > Micro-optimization of markpos() and restrpos() in btree and hash > indexes. Rather than using ReadBuffer() to increment the reference > count on an already-pinned buffer, we should use > IncrBufferRefCount() as it is faster and does not require acquiring > the BufMgrLock. > > 2004-11-09 16:42 tgl > > * src/backend/optimizer/util/clauses.c: Allow planner to fold > "stable" functions to constants when forming selectivity estimates, > per recent discussion. > > Given the right sort of queries I suppose the second change might create > a significant improvement, but I wasn't expecting 23% on a > general-purpose benchmark... Hmm... well it is a GP benchmark, but the results are based upon the performance of one transaction while in the presence of the other workloads. Speed up New Order and the whole thing improves. If you look at the graph of New Order response time distribution, the higher result gives much more frequent sub-second response for 8.0beta5 and the hump at around 23secs has moved down to 14secs. Notably, the payment transaction and stock level transaction have almost identical response time peaks in both cases. Perhaps some interaction between them has been slowing us down? Now its gone... The results seem to be significantly different, so I believe the results. Well done Mark - great new graphs. Any chance we could see the graphs showing 0.5 sec bins on the x axis, with all data < 0.5 sec removed from the graph so we can show the tail? Or point me at the data? Very pleased This shows me one additional thing: we aren't using sufficiently good instrumentation to understand where the problems lie. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.0beta5 results w/ dbt2
Mark Wong <[EMAIL PROTECTED]> writes: > I have some initial results using 8.0beta5 with our OLTP workload. > http://www.osdl.org/projects/dbt2dev/results/dev4-010/199/ > throughput: 4076.97 Do people really only look at the "throughput" numbers? Looking at those graphs it seems that while most of the OLTP transactions are fulfilled in subpar response times, there are still significant numbers that take as much as 30s to fulfil. Is this just a consequence of the type of queries being tested and the data distribution? Or is Postgres handling queries that could run consistently fast but for some reason generating large latencies sometimes? I'm concerned because in my experience with web sites, once the database responds slowly for even a small fraction of the requests, the web server falls behind in handling http requests and a catastrophic failure builds. It seems to me that reporting maximum, or at least the 95% confidence interval (95% of queries executed between 50ms-20s) would be more useful than an overall average. Personally I would be happier with an average of 200ms but an interval of 100-300ms than an average of 100ms but an interval of 50ms-20s. Consistency can be more important than sheer speed. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] multiline CSV fields
Andrew Dunstan <[EMAIL PROTECTED]> writes: > The advantage of having it in COPY is that it can be done serverside direct > from the file system. For massive bulk loads that might be a plus, although I > don't know what the protocol+socket overhead is. Actually even if you use client-side COPY it's *still* more efficient than any more general client-side alternative. As Tom pointed out to me a while back, neither the protocol nor libpq allow for having multiple queries in flight simultaneously. That makes it impossible to stream large quantities of data to the server efficiently. Each record requires a round-trip and context switch overhead. In an ideal world the client should be able to queue up enough records to fill the socket buffers and allow the kernel to switch to a more batch oriented context switch mode where the server can process large numbers of records before switching back to the client. Ideally this would apply to any kind of query execution. But as a kind of short cut towards this for bulk loading I'm curious whether it would be possible to adopt a sort of batch execution mode where a statement is prepared, then parameters to the statement are streamed to the server in a kind of COPY mode. It would have to be some format that allowed for embedded newlines though; there's just no point in an interface that can't handle arbitrary data. Personally I find the current CSV support inadequate. It seems pointless to support CSV if it can't load data exported from Excel, which seems like the main use case. But I always thought bulk loading should be from some external application anyways. The problem is that there isn't any interface suitable for an external application to use. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Auto Vacuum
Oops! [EMAIL PROTECTED] (Bruce Momjian) was seen spray-painting on a wall: > That could be part of auto-vacuum. Vacuum itself would still > sequential scan, I think. The idea is to easily grab expire tuples > when they are most cheaply found. The nifty handling of this would be to introduce "VACUUM CACHE", which would simply walk through the shared memory cache to look for expiries there. That could have a most interesting interaction with ARC... On the "unfortunate" side, marking tuples as dead would, I believe draw in some index pages. (Right?) Those pages drawn in would remain at the "cheapest" end of the cache; an ARC 'win.' And it should be the case that this ultimately shrinks cache usage, as dead tuples get thrown out. Running VACUUM CACHE periodically on a system that is "killing" tuples at a pretty steady clip ought to clear out many of those tuples without needing to browse the tables. This ought to be particularly helpful with large tables that have small "contentious" portions that generate dead tuples. -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/rdbms.html "Heuristics (from the French heure, "hour") limit the amount of time spent executing something. [When using heuristics] it shouldn't take longer than an hour to do something." ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Error: column "nsptablespace" does not exist
Robert Treat <[EMAIL PROTECTED]> writes: > don't we normally announce if initdb is required on new beta releases? We > should. It was sloppy that we didn't do that for beta5, and I apologize for it. One problem is that we don't have a defined place for per-beta-version release notes. The current structure of release.sgml doesn't cater for it --- and I doubt we want to permanently memorialize beta-version issues anyway. Any thoughts? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 8.0beta5 results w/ dbt2
Mark Wong <[EMAIL PROTECTED]> writes: > I have some initial results using 8.0beta5 with our OLTP workload. > Off the bat I see about a 23% improvement in overall throughput. Between beta4 and beta5? That's astonishing. We didn't really do very much that was performance-focused. Digging in the CVS logs, I see only some changes intended to speed up subtransaction commit, which I suppose is not relevant to your benchmark, plus these two changes: 2004-11-16 22:13 neilc * src/backend/access/: hash/hash.c, nbtree/nbtree.c: Micro-optimization of markpos() and restrpos() in btree and hash indexes. Rather than using ReadBuffer() to increment the reference count on an already-pinned buffer, we should use IncrBufferRefCount() as it is faster and does not require acquiring the BufMgrLock. 2004-11-09 16:42 tgl * src/backend/optimizer/util/clauses.c: Allow planner to fold "stable" functions to constants when forming selectivity estimates, per recent discussion. Given the right sort of queries I suppose the second change might create a significant improvement, but I wasn't expecting 23% on a general-purpose benchmark... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] multiline CSV fields
Tom Lane wrote: Kris Jurka <[EMAIL PROTECTED]> writes: Endlessly extending the COPY command doesn't seem like a winning proposition to me and I think if we aren't comfortable telling every user to write a script to pre/post-process the data we should instead provide a bulk loader/unloader that transforms things to our limited COPY functionality. There are all kinds of feature requests I've seen along these lines that would make COPY a million option mess if we try to support all of it directly. I agree completely --- personally I'd not have put CSV into the backend either. IIRC we already have a TODO item for a separate bulk loader, but no one's stepped up to the plate yet :-( IIRC, the way it happened was that a proposal was made to do CSV import/export in a fairly radical way, I countered with a much more modest approach, which was generally accepted and which Bruce and I then implemented, not without some angst (as well as a little sturm und drang). The advantage of having it in COPY is that it can be done serverside direct from the file system. For massive bulk loads that might be a plus, although I don't know what the protocol+socket overhead is. Maybe it would just be lost in the noise. Certainly I can see some sense in having COPY deal with straightforward cases and a bulk-load-unload program in bin to handle the hairier cases. Multiline fields would come into that category. The bulk-load-unload facility could possibly handle things other than CSV format too (XML anyone?). The nice thing about an external program is that it would not have to handle data embedded in an SQL stream, so the dangers from shifts in newline style, missing quotes, and the like would be far lower. We do need to keep things in perspective a bit. The small wrinkle that has spawned this whole thread will not affect most users of the facility - and many many users will thanks us for having provided it. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Aubit 4GL for postgresql
On Monday 29 November 2004 16:48, Dave Cramer wrote: > Hi, > > Mike Aubury is considering porting Aubit 4GL > http://www.aubit.com/index.php?page=Products/Aubit4gl to native > postgresql. > > If this is of interest to you please respond. He is trying to measure > the level of interest. An unnamed company I am aquainted with uses Progress and it's 4GL hooks it's a piece of crap. I'd love to see it replaced with a postgresql based solution, but first we need native windows support (speak of the devil) and then a mature 4GL interface. Now I don't expect said company to change any time soon, however there are some more open minded companies out there looking to dump Progress, and this would certainly be a bonus for them. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(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] Error handling in plperl and pltcl
On 11/29/2004 10:43 PM, Tom Lane wrote: Jan Wieck <[EMAIL PROTECTED]> writes: I don't agree that the right cure is to execute each and every statement itself as a subtransaction. What we ought to do is to define a wrapper for the catch Tcl command, that creates a subtransaction and executes the code within during that. What I would like to do is provide a catch-like Tcl command that defines a subtransaction, and then optimize the SPI commands so that they don't create their own sub-subtransaction if they can see they are directly within the subtransaction command. But when they aren't, they need to define their own subtransactions so that the error semantics are reasonable. I think what you're saying is that a catch command should be exactly equivalent to a subtransaction, but I'm unconvinced --- a catch might be used around some Tcl operations that don't touch the database, in which case the subtransaction overhead would be a serious waste. That is right. What the catch replacement command should do is to establish some sort of "catch-level", run the script inside the catch block. The first spi operation inside of that block causes a subtransaction to be created and remembered in that catch-level. At the end - i.e. when that block of commands finishes, the subtransaction is committed or rolled back and nothing done if the command block didn't hit any spi statement. The real point here is that omitting the per-command subtransaction ought to be a hidden optimization, not something that intrudes to the point of having unclean semantics when we can't do it. We could treat the entire function call as one subtransaction in the first place. Then create more sub-subtransactions as catch blocks appear. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] multiline CSV fields
Tom Lane wrote: > Kris Jurka <[EMAIL PROTECTED]> writes: > > Endlessly extending the COPY command doesn't seem like a winning > > proposition to me and I think if we aren't comfortable telling every user > > to write a script to pre/post-process the data we should instead provide a > > bulk loader/unloader that transforms things to our limited COPY > > functionality. There are all kinds of feature requests I've seen > > along these lines that would make COPY a million option mess if we try to > > support all of it directly. > > I agree completely --- personally I'd not have put CSV into the backend > either. What pushed us was the large number of request for it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] bug fix request
On Monday 29 November 2004 02:58, Christopher Kings-Lynne wrote: > > Hmm. This error is not coming from "a line of the copy", it is occurring > > because the COPY command itself fails, and so the server never tells > > psql to shift into COPY mode. I'm not sure that a reasonable fix for > > this is possible. As a counterexample, if you misspelled COPY as COPZ, > > would you expect the software to decide that following lines up to > > \. should be ignored? If you manually misentered a COPY command and got > > an error, would you be surprised to have psql ignore everything you > > typed until you typed \. ? (I can bet we'd get bug reports about that.) > > Hmmm...doesn't stop it being annoying, however. > > I presumed I was replicating the same problem I get when running SQL > scripts that insert a few million rows. Basically I start it running, > then maybe some command before the COPY fails, then it gets to the COPY > anyway and start barfing millions of lines. Then I have to change my > terminal settings to record heaps of lines and then try to ctrl-C the > query before it scrolls too far off, just to find out the line that > caused the error. > Chris, does the problem manifest itself if one of your COPY'd data lines violates a primary key with existing data in the table ? -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(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] Error handling in plperl and pltcl
Jan Wieck <[EMAIL PROTECTED]> writes: > I don't agree that the right cure is to execute each and every statement > itself as a subtransaction. What we ought to do is to define a wrapper > for the catch Tcl command, that creates a subtransaction and executes > the code within during that. What I would like to do is provide a catch-like Tcl command that defines a subtransaction, and then optimize the SPI commands so that they don't create their own sub-subtransaction if they can see they are directly within the subtransaction command. But when they aren't, they need to define their own subtransactions so that the error semantics are reasonable. I think what you're saying is that a catch command should be exactly equivalent to a subtransaction, but I'm unconvinced --- a catch might be used around some Tcl operations that don't touch the database, in which case the subtransaction overhead would be a serious waste. The real point here is that omitting the per-command subtransaction ought to be a hidden optimization, not something that intrudes to the point of having unclean semantics when we can't do it. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] multiline CSV fields
Kris Jurka <[EMAIL PROTECTED]> writes: > Endlessly extending the COPY command doesn't seem like a winning > proposition to me and I think if we aren't comfortable telling every user > to write a script to pre/post-process the data we should instead provide a > bulk loader/unloader that transforms things to our limited COPY > functionality. There are all kinds of feature requests I've seen > along these lines that would make COPY a million option mess if we try to > support all of it directly. I agree completely --- personally I'd not have put CSV into the backend either. IIRC we already have a TODO item for a separate bulk loader, but no one's stepped up to the plate yet :-( regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] multiline CSV fields
Kris Jurka wrote: > > > On Mon, 29 Nov 2004, Andrew Dunstan wrote: > > > Longer term I'd like to be able to have a command parameter that > > specifies certain fields as multiline and for those relax the line end > > matching restriction (and for others forbid multiline altogether). That > > would be a TODO for 8.1 though, along with optional special handling for > > first line column headings. > > > > Endlessly extending the COPY command doesn't seem like a winning > proposition to me and I think if we aren't comfortable telling every user > to write a script to pre/post-process the data we should instead provide a > bulk loader/unloader that transforms things to our limited COPY > functionality. There are all kinds of feature requests I've seen > along these lines that would make COPY a million option mess if we try to > support all of it directly. > > - skipping header rows > - skipping certain data file columns > - specifying date formats > - ignoring duplicates > - outputting an arbitrary SELECT statement Agreed. There are lots of wishes for COPY and it will become bloated if we do them all. I am concerned someone will say, "Oh, I know the CSV format and I might load the data into another database someday so I will always use CVS" not knowing it isn't a 100% consistent format. I think we need to issues a warning if a \r or \n is output by COPY CSV just so people understand the limitation. We can then reevaluate where we need to go for 8.1. Open item updated: * warn on COPY TO ... CSV with \r,\n in data -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Auto Vacuum
Matthew T. O'Connor wrote: > Bruce Momjian wrote: > > >Matthew T. O'Connor wrote: > > > > > >>Bruce Momjian wrote: > >> > >> > >>>I have added an auto-vacuum TODO item: > >>> > >>>* Auto-vacuum > >>> o Move into the backend code > >>> o Scan the buffer cache to find free space or use background writer > >>> o Use free-space map information to guide refilling > >>> > >>> > >>I'm not sure what you mean exactly by "Scan the buffer cache to find > >>free space or use background writer", the other two are definitely high > >>priority todo items (at least as far as autovacuum in concerned). > >> > >> > > > >I am thinking we could look for expired tuples when while they are in > >the buffer cache or before they are written to disk so we don't have to > >a sequential scan to find them. > > > > > > Is that related to autovacuum? Or is that a potential feature inside the > actual vacuum command? That could be part of auto-vacuum. Vacuum itself would still sequential scan, I think. The idea is to easily grab expire tuples when they are most cheaply found. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Auto Vacuum
Bruce Momjian wrote: Matthew T. O'Connor wrote: Bruce Momjian wrote: I have added an auto-vacuum TODO item: * Auto-vacuum o Move into the backend code o Scan the buffer cache to find free space or use background writer o Use free-space map information to guide refilling I'm not sure what you mean exactly by "Scan the buffer cache to find free space or use background writer", the other two are definitely high priority todo items (at least as far as autovacuum in concerned). I am thinking we could look for expired tuples when while they are in the buffer cache or before they are written to disk so we don't have to a sequential scan to find them. Is that related to autovacuum? Or is that a potential feature inside the actual vacuum command? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Opinions on Usenet ...
Bruce Momjian <[EMAIL PROTECTED]> wrote: > > Gavin Sherry wrote: > > On Mon, 29 Nov 2004, Marc G. Fournier wrote: > > > > > > > > If there were a comp.databases.postgresql.hackers newsgroup created and > > > carried by all the news servers ... would you move to using it vs using > > > the mailing lists? > > > > > > The USENET community seems to think that there would be a mass exodus from > > > the lists to usenet ... based on past discussions concerning moving some > > > stuff out of email to stuff like bug trackers, I don't believe this to be > > > the case, but am curious what the opinion of other developers happens to > > > be ... would a USENET group actually be preferrable? > > > > No. > > Yes ... well, actually ... no. I just wanted to be different. :-) Personally, I'd frequent whichever venue had the most utility. That being said: I much prefer the Usenet mechanism over either mailing lists or web-based bulletin-boards for non-real-time group discussions. Jim ---(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] Error: column "nsptablespace" does not exist
On Monday 29 November 2004 11:03, Andreas Pflug wrote: > Christopher Kings-Lynne wrote: > >>> Sorry Chris - obviously the pgAdmin team are just a bit crazier than > >>> your lot :-) > >> > >> And a little faster fixing it :-) > > > > I didn't even see it go through. Which is weird because I normally > > notice that kind of thing... > > Same with us. It's probably the result of the 100+msg thread about > restoring issues with tablespaces. I didn't follow it completely, so I > missed the msg #101 which probably noticed this minor change... > > It would have been A Good Thing (tm) if this change had been announced > more clearly, considering the fact that admin tools developers wouldn't > expect such a late change. > Yeah it's the double edged sword that postgresql is maturing to the point that there are now several admin tools that are 8.0 ready before 8.0 release will be made, which is normally a good thing. One thing I was thinking about is don't we normally announce if initdb is required on new beta releases? We should. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Opinions on Usenet ...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > If there were a comp.databases.postgresql.hackers newsgroup created and > carried by all the news servers ... would you move to using it vs using > the mailing lists? No, but I might use it from time to time. Although I abandoned newsgroups a long time ago, they still serve a purpose. > ... > the case, but am curious what the opinion of other developers happens to > be ... would a USENET group actually be preferrable? Not personally preferable, but I feel that we should either be mailing list only, or do things the right way, which means being underneath comp.* as an official (moderated) newsgroup, and following the accepted standards. (something we have always strived for in other areas). Thus, the true question should be: do we support newsgrouping these lists or keep them mailing list only? I'd rather do the latter than a broken implementation of the former. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200411290939 -BEGIN PGP SIGNATURE- iD8DBQFBqzU+vJuQZxSWSsgRAnWBAKCxND6C6HYplw+DO/FO3F0JIbMbeQCg4JHM Sp/jKz0wodd4layMgdjLfbk= =c/1R -END PGP SIGNATURE- ---(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] Opinions on Usenet ...
On Mon, 2004-11-29 at 15:03 -0400, Marc G. Fournier wrote: > If there were a comp.databases.postgresql.hackers newsgroup created and > carried by all the news servers ... would you move to using it vs using > the mailing lists? Is there a reliable, fast, public news server out there which would carry it at reasonable speed (my ISP updates their groups nightly -- completely useless). -- Rod Taylor <[EMAIL PROTECTED]> ---(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] 8.0beta5 results w/ dbt2
Mark Wong wrote: > I have some initial results using 8.0beta5 with our OLTP workload. > Off the bat I see about a 23% improvement in overall throughput. The > most significant thing I've noticed was in the oprofile report where > FunctionCall2 and hash_seq_search have moved down the profile a bit. > > Also, I have libc with symbols now so we can see what it's doing with > in the oprofile output. > > 8.0beta5 results: > http://www.osdl.org/projects/dbt2dev/results/dev4-010/199/ > throughput: 4076.97 > > 8.0beta4 results: > http://www.osdl.org/projects/dbt2dev/results/dev4-010/191/ > throughput: 3323.07 You saw an improvement of 23% from beta4 to beta5? I didn't think we did any major performance changes between those releases. Tom? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Opinions on Usenet ...
On Mon, 29 Nov 2004, Bruce Momjian wrote: Gavin Sherry wrote: On Mon, 29 Nov 2004, Marc G. Fournier wrote: If there were a comp.databases.postgresql.hackers newsgroup created and carried by all the news servers ... would you move to using it vs using the mailing lists? The USENET community seems to think that there would be a mass exodus from the lists to usenet ... based on past discussions concerning moving some stuff out of email to stuff like bug trackers, I don't believe this to be the case, but am curious what the opinion of other developers happens to be ... would a USENET group actually be preferrable? No. Yes ... well, actually ... no. I just wanted to be different. :-) You watched "Night at the Roxbury" last night, didn't you? :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Solution proposal for TODO item "Clients: sequences"
That would be something good for 8.1 so I will keep your email. This has been saved for the 8.1 release: http:/momjian.postgresql.org/cgi-bin/pgpatches2 --- Gevik Babakhani wrote: > Dear People, > > Hereby a proposal for the TODO item "Clients: Have psql show current values > for a sequences". > I have added a new slash command to psql client "\sq" for showing the last > values of the > existing sequences in the public schema. The code is only tested on rh9. > > The new files are sequence_info.c and sequence_info.h I would like > to hear your comments. (Be gentle this is my first) > > The sources can be downloaded from http://www.truesoftware.net/psql/ > > Regards, > Gevik > > > > SCREEN OUTPUT > > [EMAIL PROTECTED] psql]$ ./psql > Welcome to psql 8.0.0beta5, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms >\h for help with SQL commands >\? for help with psql commands >\g or terminate with semicolon to execute query >\q to quit > > gevik=# \sq > Current sequence values > Sequence | Last value > ---+ > mytableid | 5 > seq1 | 1 > (2 rows) > > gevik=# > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 8: explain analyze is your friend
Re: [HACKERS] Auto Vacuum
Matthew T. O'Connor wrote: > Bruce Momjian wrote: > > >I have added an auto-vacuum TODO item: > > > >* Auto-vacuum > >o Move into the backend code > >o Scan the buffer cache to find free space or use background writer > >o Use free-space map information to guide refilling > > > > I'm not sure what you mean exactly by "Scan the buffer cache to find > free space or use background writer", the other two are definitely high > priority todo items (at least as far as autovacuum in concerned). > I am thinking we could look for expired tuples when while they are in the buffer cache or before they are written to disk so we don't have to a sequential scan to find them. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 8: explain analyze is your friend
Re: [HACKERS] Opinions on Usenet ...
Gavin Sherry wrote: > On Mon, 29 Nov 2004, Marc G. Fournier wrote: > > > > > If there were a comp.databases.postgresql.hackers newsgroup created and > > carried by all the news servers ... would you move to using it vs using > > the mailing lists? > > > > The USENET community seems to think that there would be a mass exodus from > > the lists to usenet ... based on past discussions concerning moving some > > stuff out of email to stuff like bug trackers, I don't believe this to be > > the case, but am curious what the opinion of other developers happens to > > be ... would a USENET group actually be preferrable? > > No. Yes ... well, actually ... no. I just wanted to be different. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Opinions on Usenet ...
[EMAIL PROTECTED] ("Gevik Babakhani") writes: >Maybe it is me but, I am trying to send a posting regarding a solution >proposal for a TODO item. >My posting has a .tgz attachment but it seems that it never arives at >hackers list! >This is very frustrating. I even ask Bruce for help. how big is the message? *raised eyebrow* depending on size, it might get caught up in the queue to be approved ... the only other possibility is that the anti-virus or anti-spam checkers are picking it up ... what i'd recommend is putting it up on a URL and posting the URL so that ppl can download it ... >-Original Message- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier >Sent: Monday, November 29, 2004 9:24 PM >To: Gevik Babakhani >Cc: [EMAIL PROTECTED] >Subject: Re: [HACKERS] Opinions on Usenet ... >On Mon, 29 Nov 2004, Gevik Babakhani wrote: >> I was wondering if there is a better solution than mailing lists. >> My experience is that mailing lists are somewat combersome to use. >> Especially when your postings do not arrive! >When they don't arrive? *raised eyebrow* You having a problem? :( >I don't know about everyone else, but my personal preference for mailing >lists is due to the lack of spam that gets to them, something that you can't >really do easily on Usenet ... > >Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) >Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 >---(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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Error handling in plperl and pltcl
On 11/19/2004 7:54 PM, Tom Lane wrote: Thomas Hallgren <[EMAIL PROTECTED]> writes: My approach with PL/Java is a bit different. While each SPI call is using a try/catch they are not using a subtransaction. The catch will however set a flag that will ensure two things: 1. No more calls can be made from PL/Java to the postgres backend. 2. Once PL/Java returns, the error will be re-thrown. That's what pltcl has always done, and IMHO it pretty well sucks :-( it's neither intuitive nor useful. At the time that code was written it simply acted as a stopgap to prevent subsequent SPI calls after elog while still unwinding the Tcl call stack properly to avoid resource leaking inside of Tcl. I don't agree that the right cure is to execute each and every statement itself as a subtransaction. What we ought to do is to define a wrapper for the catch Tcl command, that creates a subtransaction and executes the code within during that. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] multiline CSV fields
On Mon, 29 Nov 2004, Andrew Dunstan wrote: > Longer term I'd like to be able to have a command parameter that > specifies certain fields as multiline and for those relax the line end > matching restriction (and for others forbid multiline altogether). That > would be a TODO for 8.1 though, along with optional special handling for > first line column headings. > Endlessly extending the COPY command doesn't seem like a winning proposition to me and I think if we aren't comfortable telling every user to write a script to pre/post-process the data we should instead provide a bulk loader/unloader that transforms things to our limited COPY functionality. There are all kinds of feature requests I've seen along these lines that would make COPY a million option mess if we try to support all of it directly. - skipping header rows - skipping certain data file columns - specifying date formats - ignoring duplicates - outputting an arbitrary SELECT statement Kris Jurka ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] 8.0beta5 results w/ dbt2
I have some initial results using 8.0beta5 with our OLTP workload. Off the bat I see about a 23% improvement in overall throughput. The most significant thing I've noticed was in the oprofile report where FunctionCall2 and hash_seq_search have moved down the profile a bit. Also, I have libc with symbols now so we can see what it's doing with in the oprofile output. 8.0beta5 results: http://www.osdl.org/projects/dbt2dev/results/dev4-010/199/ throughput: 4076.97 8.0beta4 results: http://www.osdl.org/projects/dbt2dev/results/dev4-010/191/ throughput: 3323.07 Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Aubit 4GL for postgresql
I just finished doing a brief evaluation of informix-4gl. I was going to do some non-4gl work for a company that uses 4gl extensively, so I just wanted to get an idea where they were coming from. I am interested in that PostgreSQL port, but right now it's just academic. Regards, Jeff Davis On Mon, 2004-11-29 at 16:48 -0500, Dave Cramer wrote: > Hi, > > Mike Aubury is considering porting Aubit 4GL > http://www.aubit.com/index.php?page=Products/Aubit4gl to native > postgresql. > > If this is of interest to you please respond. He is trying to measure > the level of interest. > ---(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] Opinions on Usenet ...
On Mon, 29 Nov 2004, Marc G. Fournier wrote: > > If there were a comp.databases.postgresql.hackers newsgroup created and > carried by all the news servers ... would you move to using it vs using > the mailing lists? > > The USENET community seems to think that there would be a mass exodus from > the lists to usenet ... based on past discussions concerning moving some > stuff out of email to stuff like bug trackers, I don't believe this to be > the case, but am curious what the opinion of other developers happens to > be ... would a USENET group actually be preferrable? No. Gavin ---(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] Opinions on Usenet ...
On Mon, 2004-11-29 at 15:03 -0400, Marc G. Fournier wrote: > If there were a comp.databases.postgresql.hackers newsgroup created and > carried by all the news servers ... would you move to using it vs using > the mailing lists? No. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stopgap solution for table-size-estimate
On Mon, 2004-11-29 at 15:37, Tom Lane wrote: > "Zeugswetter Andreas DAZ SD" <[EMAIL PROTECTED]> writes: > > Tom wrote: > >>> But I am used to applications > >>> that prepare a query and hold the plan for days or weeks. If you happen > >>> to > >>> create the plan when the table is by chance empty you lost. > >> > >> You lose in either case, since this proposal doesn't change when > >> planning occurs or doesn't occur. > > > This is not true in my case, since I only "update statistics"/analyze > > when the tables have representative content (i.e. not empty). > > I'm unsure why you feel you need a knob to defeat this. The only time > when the plan would change from what you think of as the hand-tuned > case is when the physical table size is greatly different from what it > was when you analyzed. The entire point of wanting to make this change > is exactly that in that situation the plan *does* need to change. Well, the cutover between plans is supposed to happen at exactly the right place, so in theory you should want this. The margin for error on the various estimates means that the actual cutover is often some way away from the smooth transition point. If you're unlucky enough to have a plan that fluctuates on either side of the planner's transition point AND where the transition point is misplaced then you can get a large discontinuity in execution times. That's when a careful man such as Andreas can take extra benefit from manual control. You're both right. We should help both the careful tuner and the short-of-time-developer. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Aubit 4GL for postgresql
Hi, Mike Aubury is considering porting Aubit 4GL http://www.aubit.com/index.php?page=Products/Aubit4gl to native postgresql. If this is of interest to you please respond. He is trying to measure the level of interest. -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Solution proposal for TODO item "Clients: sequences"
Dear People, Hereby a proposal for the TODO item "Clients: Have psql show current values for a sequences". I have added a new slash command to psql client "\sq" for showing the last values of the existing sequences in the public schema. The code is only tested on rh9. The new files are sequence_info.c and sequence_info.h I would like to hear your comments. (Be gentle this is my first) The sources can be downloaded from http://www.truesoftware.net/psql/ Regards, Gevik SCREEN OUTPUT [EMAIL PROTECTED] psql]$ ./psql Welcome to psql 8.0.0beta5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit gevik=# \sq Current sequence values Sequence | Last value ---+ mytableid | 5 seq1 | 1 (2 rows) gevik=# ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Opinions on Usenet ...
Marc G. Fournier wrote: > If there were a comp.databases.postgresql.hackers newsgroup created > and carried by all the news servers ... would you move to using it vs > using the mailing lists? No. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Opinions on Usenet ...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Mon, 29 Nov 2004, Marc G. Fournier wrote: If there were a comp.databases.postgresql.hackers newsgroup created and carried by all the news servers ... would you move to using it vs using the mailing lists? No. Using mailing lists is what I prefer since about '95. Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBq460tl86P3SPfQ4RAheLAKDKTBaYshtXtQ4aM6caTyTqMZTvVgCfW+ec FpBEKlasn5HW+S4aCfjNkw0= =WspG -END PGP SIGNATURE- ---(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
[HACKERS] USENET vs Mailing Lists Poll ...
The WWW folks just put up a survey asking: "If there was an official newsgroup for postgresql, would you switch to using Usenet from using the mailing lists?" The Poll can be found at http://www.postgresql.org ... we're curious as to what sort of interest there is by the 'General Users' ... As a side note, for those that do vote 'yes', please note that there is an official pgsql.* hierarchy gated from the mailing lists, that is available at news.postgresql.org, if you do wish to use a news reader vs a mail reader ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Opinions on Usenet ...
Marc G. Fournier wrote: The USENET community seems to think that there would be a mass exodus from the lists to usenet ... based on past discussions concerning moving some stuff out of email to stuff like bug trackers, I don't believe this to be the case, but am curious what the opinion of other developers happens to be ... would a USENET group actually be preferrable? No, not for me. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Opinions on Usenet ...
Maybe it is me but, I am trying to send a posting regarding a solution proposal for a TODO item. My posting has a .tgz attachment but it seems that it never arives at hackers list! This is very frustrating. I even ask Bruce for help. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier Sent: Monday, November 29, 2004 9:24 PM To: Gevik Babakhani Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] Opinions on Usenet ... On Mon, 29 Nov 2004, Gevik Babakhani wrote: > I was wondering if there is a better solution than mailing lists. > My experience is that mailing lists are somewat combersome to use. > Especially when your postings do not arrive! When they don't arrive? *raised eyebrow* You having a problem? :( I don't know about everyone else, but my personal preference for mailing lists is due to the lack of spam that gets to them, something that you can't really do easily on Usenet ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Opinions on Usenet ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > If there were a comp.databases.postgresql.hackers newsgroup created and > carried by all the news servers ... would you move to using it vs using the > mailing lists? > > The USENET community seems to think that there would be a mass exodus from the > lists to usenet ... They're nuts. The only "exodus" would be in the form of people posting one-off questions not bothering to subscribe before posting. It seems to me that the news interface is useful if it's a list you don't read regularly. You just check periodically to see if there's anything on a particular topic (like to see how a new release is faring before upgrading) or to post the occasional support question without wanting to be subscribed all the time. On that basis it seems to me the only list that makes sense to gateway to USENET is pgsql-general. That's the outward-facing list for people to ask support one-off questions on. The rest of the lists should really be mailing lists for ongoing internal discussion. The news.postgresql.org interface should satisfy people who want the news-style user interface but with the serious-subscribers-only type of environment. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Opinions on Usenet ...
On Mon, 29 Nov 2004, Gevik Babakhani wrote: I was wondering if there is a better solution than mailing lists. My experience is that mailing lists are somewat combersome to use. Especially when your postings do not arrive! When they don't arrive? *raised eyebrow* You having a problem? :( I don't know about everyone else, but my personal preference for mailing lists is due to the lack of spam that gets to them, something that you can't really do easily on Usenet ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Opinions on Usenet ...
Didn't know that existed... Ack ... its never been 'hidden', but its also never been fully advertised either ... To be fair, I didn't know it existed until the whole usenet thing popped up either. Sincerely, Joshua D. Drake I'm tempted to write a 'monthly FAQ' that gets posted that talks about the usenet gateway, as well as how to do such seemingly simple things like "how to unsubscribe to the lists" ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(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] Opinions on Usenet ...
I was wondering if there is a better solution than mailing lists. My experience is that mailing lists are somewat combersome to use. Especially when your postings do not arrive! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier Sent: Monday, November 29, 2004 8:04 PM To: [EMAIL PROTECTED] Subject: [HACKERS] Opinions on Usenet ... If there were a comp.databases.postgresql.hackers newsgroup created and carried by all the news servers ... would you move to using it vs using the mailing lists? The USENET community seems to think that there would be a mass exodus from the lists to usenet ... based on past discussions concerning moving some stuff out of email to stuff like bug trackers, I don't believe this to be the case, but am curious what the opinion of other developers happens to be ... would a USENET group actually be preferrable? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Opinions on Usenet ...
On Mon, 29 Nov 2004, Rod Taylor wrote: Is there a reliable, fast, public news server out there which would carry it at reasonable speed (my ISP updates their groups nightly -- completely useless). news.postgresql.org? Didn't know that existed... Ack ... its never been 'hidden', but its also never been fully advertised either ... I'm tempted to write a 'monthly FAQ' that gets posted that talks about the usenet gateway, as well as how to do such seemingly simple things like "how to unsubscribe to the lists" ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Auto Vacuum
Bruce Momjian wrote: I have added an auto-vacuum TODO item: * Auto-vacuum o Move into the backend code o Scan the buffer cache to find free space or use background writer o Use free-space map information to guide refilling I'm not sure what you mean exactly by "Scan the buffer cache to find free space or use background writer", the other two are definitely high priority todo items (at least as far as autovacuum in concerned). Matthew ---(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] Opinions on Usenet ...
> > Is there a reliable, fast, public news server out there which would > > carry it at reasonable speed (my ISP updates their groups nightly -- > > completely useless). > > news.postgresql.org? Didn't know that existed... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Auto Vacuum
Hello Russell, Russell Smith wrote: I am doing serious thinking about the implementation of Auto Vacuum as part of the backend, Not using libpq, but classing internal functions directly. It appears to me that calling internal functions directly is a better implementation than using the external library to do the job. We are planning to move it into the backend (no longer an external libpq based contrib module) I tried to do this for 8.0, but it didn't make the cut, so I expect this work will be done for 8.1. I know I might be stepping on Matthew's toes, but I don't really want to. I am a complete newbie to the postgresql code, however I am trying. Vacuum appears to be one of the bigger saw points with administrator having to configure it via scheduled tasks. Agreed, that is one of the reasons I took on Autovacuum, I think it is something a lot of admins would like PG to do for itself. The major autovacuum issues 1. Transaction Wraparound 2. Vacuum of relations 3. Tracking of when to do vacuums 4. Where to store information needed by auto vacuum 1. Transaction Wraparound This is handled by the current autovacuum using the process outlined in: http://www.postgresql.org/docs/7.4/static/maintenance.html 2. Vacuuming of relations Currently, the entire heap must be vacuumed at one time. I would possible be desireable to have only part of the relation vacuumed at a time. If you can find out which parts of the relation have the most slack space. There is a todo item regarding tracking recent deletions so they can be resused. Some form of this would be helpful to work out what to vacuum. Performance issues for this type of activity may be a concern. But I have no experience to be able to make comment on them. So I welcome yours. This is not really an autovacuum related topic, if at some point someone adds the ability to VACUUM to do partials then autovacuum will make use of it. BTW, this has been suggested several times so please search the archives for details. 3. Tracking of when to vacuum Current autovacuum relies the stats collector to be running. I would like to only use the stats if they are available, and have an option to be able to vacuum accurately without having to have stats running. I think it is universally agreed upon that using data from the FSM is a better solution since it would not require you to have the stats system running and actually gives you a very accurate picture of what table have slack space to recover (assuming that the FSM is large enough). This is a topic that I need help on from some more enlightened core hackers. 4. Where to store information required by auto vacuum. The backend integration patch that I submitted a few months ago added a new pg_autovacuum table to the system catalogues. This table stored data that pg_autovacuum needed to persist across backend restarts, and also allowed the user to set per table settings for thresholds etc. I never heard anyone complain about this design, so from the silence I assume this is an acceptable way of maintaining pg_autovacuum related data. Matthew ---(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] Opinions on Usenet ...
On Mon, 29 Nov 2004, Rod Taylor wrote: On Mon, 2004-11-29 at 15:03 -0400, Marc G. Fournier wrote: If there were a comp.databases.postgresql.hackers newsgroup created and carried by all the news servers ... would you move to using it vs using the mailing lists? Is there a reliable, fast, public news server out there which would carry it at reasonable speed (my ISP updates their groups nightly -- completely useless). news.postgresql.org? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Adding Reply-To: to Lists configuration ...
On Mon, Nov 29, 2004 at 07:35:41AM -0500, Jim Seymour wrote: > > Chris Green <[EMAIL PROTECTED]> wrote: > > > > On Sun, Nov 28, 2004 at 07:34:28PM -0400, Marc G. Fournier wrote: > > > > > > What is the general opinion of this? I'd like to implement it, but not > > > so > > > much so that I'm going to beat my head against a brick wall on it ... > > > > > Personally I'm against it because it means that I'll often get two > > replies when people reply to my postings. However it's not a big > > issue for me. > > Actually, it would result in just the opposite. > It depends on the mailing list software, you could be right. However on another mailing list where I'm a member I get two copies of messages when people do 'Reply to all' simply because I have a Reply-To: of my own set. (I have Reply-To: set so that if people want to send me a personal reply it gets to a mailbox I will read. If you reply to my From: address the message will end up in a catch-all, low-priority, probably junk mailbox). This is a perpetual problem, if people all used the same MUA and (assuming it has the capability) all used the 'reply to list' command to reply to the list everything would be wonderful! :-) -- Chris Green ([EMAIL PROTECTED]) "Never ascribe to malice, that which can be explained by incompetence." ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Adding Reply-To: to Lists configuration ...
On Sun, Nov 28, 2004 at 07:34:28PM -0400, Marc G. Fournier wrote: > > What is the general opinion of this? I'd like to implement it, but not so > much so that I'm going to beat my head against a brick wall on it ... > Personally I'm against it because it means that I'll often get two replies when people reply to my postings. However it's not a big issue for me. -- Chris Green ([EMAIL PROTECTED]) "Never ascribe to malice, that which can be explained by incompetence." ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-www] pg_autovacuum is nice ... but ...
Bruce Momjian wrote: Should I add a TODO to warn if FSM values are too small? Is that doable? It sounds like it should be, and it would be a valuable pointer to people, so yep. Any idea who'd be interested in claiming it? Regards and best wishes, Justin Clift ---(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] Opinions on Usenet ...
Marc G. Fournier wrote: If there were a comp.databases.postgresql.hackers newsgroup created and carried by all the news servers ... would you move to using it vs using the mailing lists? No. Regards Gaetano Mendola ---(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] Opinions on Usenet ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > If there were a comp.databases.postgresql.hackers newsgroup created and > carried by all the news servers ... would you move to using it vs using > the mailing lists? No. I abandoned Usenet years ago. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Opinions on Usenet ...
Marc G. Fournier wrote: If there were a comp.databases.postgresql.hackers newsgroup created and carried by all the news servers ... would you move to using it vs using the mailing lists? Heck no. I have no desire to use USENET. Sincerely, Joshua D. Drake The USENET community seems to think that there would be a mass exodus from the lists to usenet ... based on past discussions concerning moving some stuff out of email to stuff like bug trackers, I don't believe this to be the case, but am curious what the opinion of other developers happens to be ... would a USENET group actually be preferrable? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS]
---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Opinions on Usenet ...
If there were a comp.databases.postgresql.hackers newsgroup created and carried by all the news servers ... would you move to using it vs using the mailing lists? The USENET community seems to think that there would be a mass exodus from the lists to usenet ... based on past discussions concerning moving some stuff out of email to stuff like bug trackers, I don't believe this to be the case, but am curious what the opinion of other developers happens to be ... would a USENET group actually be preferrable? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Stopgap solution for table-size-estimate updatingproblem
Tom Lane <[EMAIL PROTECTED]> writes: > Nonsense. You're assuming incremental changes (ie, only a small > fractional change in table size), but we are getting killed by > non-incremental cases. If the plan cost estimates are such that a small > fractional change in table size will cause the planner to switch to a > hugely worse plan, then you're living on the edge of disaster anyway. > Or are you telling me that every time you VACUUM or ANALYZE, you > immediately hand-inspect the plans for every query you use? Well with the current situation the best I can hope for is to run analyze at times when we can withstand minor outages and I can respond. Probably I would run it during off-peak hours. So basically while I don't hand-inspect plans, I'm using the site to test them. If the site's still running 5 minutes after the analyze then they're probably ok. I have actually written up a script that I intend to experiment with that explains every query in the system then runs analyze within a transaction and then reruns explain on every query to check for any changed plans. It only commits if there are no unchanged plans. This is all just an experiment though. I'm not sure how effective it'll be. > A further point is that only VACUUM can decrease the table size, and > VACUUM already updates these stats anyway. The only "loss of control" > involved here is prevention of a plan change in response to a > significant increase in table size. Overestimates of result size > usually don't produce as horrible plans as underestimates, so the > downside doesn't seem as large as you make it out to be. That's true. I don't think the proposed change makes the situation with respect to plan stability any worse than the status quo. But it does seem to lock us into the idea that plans could change at any time whatsoever. I'm not sure why VACUUM without ANALYZE updates the statistics at all though. Isn't that what ANALYZE is for? > This is pure fantasy. It certainly has nothing to do with the current > state of nor future directions for the planner, and you haven't even > convinced me that it's a desirable goal. What you are describing is a > brittle, inflexible system that is much more likely to break under > unforeseen circumstances than it is to perform well reliably. Huh. That's how I see the current setup. I find the current thinking too fragile precisely because there's no way to test it and guarantee it will perform consistently. I want something that won't suddenly change behaviour in ways I can't predict. I want something that will consistently run the same code path every time except at well defined points in time according to well defined processes. I'll point out other databases end up treading the same ground. Oracle started with a well defined rules-based system that was too inflexible to handle complex queries. So they went to a cost-based optimizer much like Postgres's current optimizer. But DBAs resisted for a long time precisely because they couldn't control it or predict its behaviour as well. Now they have a plan stability system where you can plan queries using the cost based optimizer but then store the plans for future use. You can even take the plans and store them and load them on development systems for testing. Their system is awfully kludgy though. Postgres can probably do much better. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Documentation on PITR still scarce
Simon Riggs <[EMAIL PROTECTED]> writes: > Greg's additional request might be worded: > > * Allow a warm standby system to also allow read-only queries Others have also asked in the past for a mode where a database could be run off read-only media like a CD-ROM. I would phrase it more like: * Allow truly read-only operation, could be useful for read-only media as well as for querying a warm-standby database or for inspecting a database without disturbing PITR recovery. -- greg ---(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] unnest
Joe Conway <[EMAIL PROTECTED]> writes: > Problem is that a polymorphic SRF cannot (currently at least) both > accept and return type anyarray. Beyond that, would the proposed function really be SQL-compliant other than this one point? I had the idea that UNNEST required some fundamental changes (but I might be confusing it with something else). regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Status of server side Large Object support?
Tom, Here's an attempt to do some major rethinking and solve all open issues (and of course creating some new ones). The idea is based on use of normal tables with a bytea column that stores one LO-page per row (henceforth referred to as LO-page tables). Several such tables can be used in order to overcome the table size limit and to enable distribution of LO's over different tablespaces. Also, the use of normal tables will resolve the issues concerning protection, user-accessible locking, and the current lack of MVCC. I feel that a more simplistic approach using already present mechanisms would make the design easier to maintain. There's no reason why LO's should require special attention from a vacuum or dump/restore perspective, nor why it should be excluded from the MVCC. This is what I think is needed: A new composite datatype must be used in place of todays oid to identify a large object. The type will look something like this: CREATE TYPE lo_locator AS ( lo_page_table int, lo_id int, lo_xact_id int ); The lo_page_table will hold the Oid of the associated LO-page table. The lo_id is the Oid used by the data pages within that table. The lo_xact_id is set to the current transaction id each time a data page is changed. Its purpose is to resolve the concurrency issue that arise when several transactions simultaniously change the same LO but on different data pages. I suggest that the system have a way to set a default LO-page table on a per schema basis. This table could be used unless the user (schema owner) explicitly declares another table. If no table has been declared for a schema the default should be the table declared for 'public'. If no table is declared there either, some global default can be used. Among other things, a default LO-page table will make it possible to retain backward compatibility. Protection can be obtained using normal grant/revoke permissions on the LO-page tables. I.e. they will serve as permission groups. LO's requiering specific permissions must be stored in a separate LO-page table. The LargeObjectDesc is changed as follows: - It must have an additional Oid that appoints the table it makes use of. - The uint32 used for the offset can be changed to an int64 at the same time. - The current SubTransactionId will become obsolete since all changes made to the LO-page tables are under sub-transaction control anyway. - Something to quickly find our way back to the row containing the lo_locator must be added so that it's easy to update the lo_xact_id that resides there. I'm not sure how to do that in the most efficient manner so its represented by a comment here. Please fill in :-) Thus we'd get: typedef struct LargeObjectDesc { Oid pageTableId; /* Page-table in use for this LO */ Oid id; /* LO's identifier within LO-page table */ int64 offset; /* current seek pointer */ int flags;/* locking info, etc */ /* + something that enables us to find our way * back so that the lo_xact_id can be updated * effiently */ } LargeObjectDesc; Tables hosting LO pages must be created using the following declaration: CREATE TABLE ( lo_id oid NOT NULL, lo_pageno int NOT NULL, lo_data bytea, PRIMARY KEY (lo_id, lo_pageno) ); Two restricions concerning a LO-pages table: 1. Each row (page) must be considered fixed in size. 2. Normal (I mean through SQL) access to the LO-page tables must be discuraged somehow. The lo_ protocoll needs to change so that the lo_seek and lo_tell uses 64 bit quantities. The lo_creat, lo_open, and lo_drop will all act on the default LO-page table. A new set of functions that allow the LO-page table to be explicitly stated for the create, open, and drop operations will be needed. Finally, three new functions, lo_size(int lod), lo_truncate(int lod, int64 new_size), and lo_get_page_table_id(int lod) should be added. Comments, suggestions? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Stopgap solution for table-size-estimate updatingproblem
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> I'm unsure why you feel you need a knob to defeat this. > Simply put because the optimizer isn't infallible. And one of the main reasons that it's fallible is because it sometimes uses grossly obsolete statistics. We can fix the first-order problems in this line with the proposed changes. (Obsolete pg_statistic contents are an issue too, but they usually have only second-order effects on plan choices.) > And some mistakes are more > costly than others. Continuing to use a plan that worked fine after an > incremental change to the table is unlikely to cause pain We're not talking about "incremental" changes; those would be unlikely to result in a plan change in any case. The cases that are causing pain are where the table size has changed by an order of magnitude and the planner failed to notice. > You're going to say the opposite is also possible but it's not really true. A > DML change that doesn't trigger an execution plan change isn't going to cause > a disproportionate change in the execution time of queries. Nonsense. You're assuming incremental changes (ie, only a small fractional change in table size), but we are getting killed by non-incremental cases. If the plan cost estimates are such that a small fractional change in table size will cause the planner to switch to a hugely worse plan, then you're living on the edge of disaster anyway. Or are you telling me that every time you VACUUM or ANALYZE, you immediately hand-inspect the plans for every query you use? A further point is that only VACUUM can decrease the table size, and VACUUM already updates these stats anyway. The only "loss of control" involved here is prevention of a plan change in response to a significant increase in table size. Overestimates of result size usually don't produce as horrible plans as underestimates, so the downside doesn't seem as large as you make it out to be. > For a production OLTP system I would want to be able to control when > the plans change. In an ideal world I would even want to inspect and > test them before they go live. This is pure fantasy. It certainly has nothing to do with the current state of nor future directions for the planner, and you haven't even convinced me that it's a desirable goal. What you are describing is a brittle, inflexible system that is much more likely to break under unforeseen circumstances than it is to perform well reliably. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] unnest
Bruce Momjian wrote: I assume this is not something for our PostgreSQL CVS, even the later SRF implementation. I agree with that assessment, at least in its present state. For example: regression=# select * from unnest(array[[1,2,3],[4,5,6]]); unnest 1 2 3 4 5 6 (6 rows) Per SQL99 I think that ought to return something like: -- output faked regression=# select * from unnest(array[[1,2,3],[4,5,6]]); unnest {1,2,3} {4,5,6} (2 rows) Problem is that a polymorphic SRF cannot (currently at least) both accept and return type anyarray. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Stopgap solution for table-size-estimate updatingproblem
Tom Lane <[EMAIL PROTECTED]> writes: > I'm unsure why you feel you need a knob to defeat this. The only time > when the plan would change from what you think of as the hand-tuned > case is when the physical table size is greatly different from what it > was when you analyzed. The entire point of wanting to make this change > is exactly that in that situation the plan *does* need to change. Simply put because the optimizer isn't infallible. And some mistakes are more costly than others. Continuing to use a plan that worked fine after an incremental change to the table is unlikely to cause pain whereas changing plans opens a pandora's box of potential catastrophic failures. Imagine a scenario where the system was running fine using nested loops and index scans but the user deletes a few records (at 9am just as the site is hitting peak usage and before I'm awake) and suddenly the planner decides to use sequential scans and hash joins. The resulting plan may be far too slow and crash the application. This is especially likely if the original plan estimates were off. You're going to say the opposite is also possible but it's not really true. A DML change that doesn't trigger an execution plan change isn't going to cause a disproportionate change in the execution time of queries. It's going to cause a change in execution time proportionate to the change in the data. If the user doubles the number of records in the table (something I can predict the likelihood of) it probably means the query will take twice as long. Now there may be a faster plan out there but failing to find it just means the query will take twice as long. If the user halves the number of records and the planner tries to be clever and switches plans, then it might be right, but it might be wrong. And the potential damage if it's wrong is unbounded. It could just take twice as long, but it could take 1,000 times as long or worse. For a production OLTP system I would want to be able to control when the plans change. In an ideal world I would even want to inspect and test them before they go live. The last thing I want is for them to change spontaneously when I'm not expecting it. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Basic Requirements for SQL Window Functions
Simon Riggs <[EMAIL PROTECTED]> writes: > The SQL window functions seem to require an ordering for most of their > operations. AFAICS, the entire concept of a "window" implies the input is ordered in some way; what operations would they provide that don't require this? > It is possible that that could be provided by a sort node in > the execution plan. Either sort or indexscan, but you'd certainly need one or the other. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Documentation on PITR still scarce
Simon Riggs wrote: > On Mon, 2004-11-29 at 13:10, Bruce Momjian wrote: > >>Or TODO maybe worded as: >> >>* Allow the PITR process to be debugged and data examined >> > > > Yes, thats good for me... > > Greg's additional request might be worded: > >* Allow a warm standby system to also allow read-only queries Yes, this will shift postgresql in Sybase direction. Did you solved also all your concerns on my two bash scripts ? Are that scripts eligibles to be putted in contrib ? Regards Gaetano Mendola ---(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] bug fix request
Tom Lane <[EMAIL PROTECTED]> writes: > Disable tab completion, or don't paste tabs. I don't think psql can be > expected to recognize that a tab is coming from pasted input. Hm, this also bother me all the time. It doesn't sound like it would be very hard to detect pasted tabs actually. Two options come to mind: . If there's any input available it's probably not a typed tab since typists usually can't type fast enough to out type the terminal emulator, and even if they could typing tab for command completion and then going ahead and typing the next character immediately would reasonably cancel the tab completion. . Pasted tabs are normally at the start of a line for indentation. Simply ignoring tab completion after white-space, ie, unbounded tab completion on an empty token, would eliminate 99.9% of the problem. -- greg ---(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] Stopgap solution for table-size-estimate updatingproblem
"Zeugswetter Andreas DAZ SD" <[EMAIL PROTECTED]> writes: > I think I recall that lseek may have a negative effect on some OS's > readahead calculations (probably only systems that cannot handle an > lseek to the next page eighter) ? Do you think we should cache the > last value to avoid the syscall ? We really can't, since the point of doing it is to find out whether any other backends have extended the file since we last looked. Also, IIRC seqscan startup does a similar lseek() anyhow, so having the planner do one will make no difference to the readahead or lack of it in a subsequent seqscan. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Stopgap solution for table-size-estimate updatingproblem
>> This is not true in my case, since I only "update statistics"/analyze >> when the tables have representative content (i.e. not empty). > > I'm unsure why you feel you need a knob to defeat this. The only time > when the plan would change from what you think of as the hand-tuned > case is when the physical table size is greatly different from what it > was when you analyzed. Ok, understood. I just need to make sure I don't "vacuum full" in that case, which is good anyway if I expect the table to soon grow to this size again. I think that is good. I think I recall that lseek may have a negative effect on some OS's readahead calculations (probably only systems that cannot handle an lseek to the next page eighter) ? Do you think we should cache the last value to avoid the syscall ? Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Error: column "nsptablespace" does not exist
Christopher Kings-Lynne wrote: Sorry Chris - obviously the pgAdmin team are just a bit crazier than your lot :-) And a little faster fixing it :-) I didn't even see it go through. Which is weird because I normally notice that kind of thing... Same with us. It's probably the result of the 100+msg thread about restoring issues with tablespaces. I didn't follow it completely, so I missed the msg #101 which probably noticed this minor change... It would have been A Good Thing (tm) if this change had been announced more clearly, considering the fact that admin tools developers wouldn't expect such a late change. Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] set variable for TOAST_TUPLE_THRESHOLD
Hi I would like to test, how storing one biggish (~450 bytes bytea) column into toast table would affect my applications performance. is there a way to set the values used for this using some SET variable or some column in system tables. or is my only option to build a custom version of server with changed TOAST_TUPLE_THRESHOLD/TOAST_TUPLE_TARGET (in src/include/access/tuptoaster.h) as advised by Tom Lane in http://listcrawler.com/message2.jsp?id=53577 -- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] multiline CSV fields
Bruce Momjian <[EMAIL PROTECTED]> writes: > Also, can you explain why we can't read across a newline to the next > quote? Is it a problem with the way our code is structured or is it a > logical problem? It's a structural issue in the sense that we separate the act of dividing the input into rows from the act of dividing it into columns. I do not think that separation is wrong however. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Stopgap solution for table-size-estimate updatingproblem
"Zeugswetter Andreas DAZ SD" <[EMAIL PROTECTED]> writes: > Tom wrote: >>> But I am used to applications >>> that prepare a query and hold the plan for days or weeks. If you happen to >>> create the plan when the table is by chance empty you lost. >> >> You lose in either case, since this proposal doesn't change when >> planning occurs or doesn't occur. > This is not true in my case, since I only "update statistics"/analyze > when the tables have representative content (i.e. not empty). I'm unsure why you feel you need a knob to defeat this. The only time when the plan would change from what you think of as the hand-tuned case is when the physical table size is greatly different from what it was when you analyzed. The entire point of wanting to make this change is exactly that in that situation the plan *does* need to change. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Basic Requirements for SQL Window Functions
An example of a window function is RANK or a moving AVG, though also include ROW_NUMBER or CUME_DIST. They are a different kind of aggregate introduced by/included in SQL:2003, which require a "sliding window" of rows. The SQL window functions seem to require an ordering for most of their operations. It is possible that that could be provided by a sort node in the execution plan. It also seems that this might have some effect on the MAX/MIN handling issue - I raise this now in case there is some inter-relationship. I've started another thread to avoid opening Pandora's box again, but.. Earlier discussions around MAX/MIN handling mention this On Thu, 2004-11-11 at 15:24, Tom Lane wrote: > "Zeugswetter Andreas DAZ SD" <[EMAIL PROTECTED]> writes: > >> How are you planning to represent the association between MIN/MAX and > >> particular index orderings in the system catalogs? > > > Don't we already have that info to decide whether an index handles > > an "ORDER BY" without a sort node ? > > We know how to determine that an index matches an ORDER BY clause. > But what has an aggregate called MAX() got to do with ORDER BY? Magic > assumptions about operators named "<" are not acceptable answers; there > has to be a traceable connection in the catalogs. > > As a real-world example of why I won't hold still for hard-wiring this: > a complex-number data type might have btree opclasses allowing it to be > sorted either by real part or by absolute value. One might then define > max_real() and max_abs() aggregates on the type. It should be possible > to optimize such aggregates the same way as any other max() aggregate. Are we OK to say that window functions will always need a sort node? Is there an optimization that anyone can see that might lead us away from that requirement, and if so do we need to solve the problem described above? -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Error: column "nsptablespace" does not exist
Sorry Chris - obviously the pgAdmin team are just a bit crazier than your lot :-) And a little faster fixing it :-) I didn't even see it go through. Which is weird because I normally notice that kind of thing... Chris ---(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] multiline CSV fields
Bruce Momjian wrote: Also, can you explain why we can't read across a newline to the next quote? Is it a problem with the way our code is structured or is it a logical problem? Someone mentioned multibyte encodings but I don't understand how that applies here. In a CSV file, each line is a record. Reading across a newline for the next quote (assuming the next field is quoted) would mean stealing fields from the next record. I did see one complaint about missing or extra fields at the end of a record - I think it is reasonable for us to expect the data to be rectangular, and not ragged. (I hope this answers your question - I am not 100% certain I understaood it). cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Documentation on PITR still scarce
On Mon, 2004-11-29 at 13:10, Bruce Momjian wrote: > Or TODO maybe worded as: > > * Allow the PITR process to be debugged and data examined > Yes, thats good for me... Greg's additional request might be worded: * Allow a warm standby system to also allow read-only queries Thanks. > --- > > Simon Riggs wrote: > > On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: > > > > > Is this a TODO? > > > > Yes, but don't hold your breath on that feature. > > > > Gavin and I were discussing briefly a design that would allow something > > similar to this. The design would allow the user to stop/start recovery > > and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to > > implement than the proposal below, which I agree is desirable. We > > haven't hardly started that discussion yet though. > > I called this "recovery console" functionality. > > > > I'm not sure I like the Suspended Animation phrase, I thought maybe > > TARDIS or Langston Field sums it up better (kidding...) > > > > > Greg Stark wrote: > > > > > > > > Tom Lane <[EMAIL PROTECTED]> writes: > > > > > > > > > I suppose it might be useful to have some kind of "suspended > > > > > animation" > > > > > behavior where you could bring up a backend and look at the database > > > > > in > > > > > a strict read-only fashion, not really executing transactions at all, > > > > > just to see what you had. Then you could end the recovery and go to > > > > > normal operations, or allow the recovery to proceed further if you > > > > > decided this wasn't where you wanted to be yet. However that would > > > > > require a great deal of mechanism we haven't got (yet). In particular > > > > > there is no such thing as strict read-only examination of the > > > > > database. > > > > > > > > That would be a great thing to have one day for other reasons aside > > > > from the > > > > ability to test out a recovered database. It makes warm standby > > > > databases much > > > > more useful. > > > > > > > > A warm standby is when you keep a second machine constantly up to date > > > > by > > > > applying the archived PITR logs as soon as they come off your server. > > > > You're > > > > ready to switch over at the drop of a hat and don't have to go through > > > > the > > > > whole recovery process, you just switch the database from recovery mode > > > > to > > > > active mode and make it your primary database. But in the until then the > > > > backup hardware languishes, completely useless. > > > > > > > > Oracle has had a feature for a long time that you can actually open the > > > > standby database in a strict read-only mode and run queries. This is > > > > great for > > > > a data warehouse situation where you want to run long batch jobs against > > > > recent data. > > > > > > > > -- Best Regards, Simon Riggs ---(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] multiline CSV fields
Bruce Momjian wrote: Andrew Dunstan wrote: OK, then should we disallow dumping out data in CVS format that we can't load? Seems like the least we should do for 8.0. As Tom rightly points out, having data make the round trip was not the goal of the exercise. Excel, for example, has no trouble reading such data (or at least my installation of it). Personally I consider CSVs with line end chars embedded in fields to be broken anyway, but this was something that was specifically mentioned when we were discussing requirements, which is why I coded for it. OK, I am pretty uncomforable with this but you know this usage better than I do. Should we issue a warning message stating it will not be able to be reloaded? If it bothers you that much. I'd make a flag, cleared at the start of each COPY, and then where we test for CR or LF in CopyAttributeOutCSV, if the flag is not set then set it and issue the warning. Longer term I'd like to be able to have a command parameter that specifies certain fields as multiline and for those relax the line end matching restriction (and for others forbid multiline altogether). That would be a TODO for 8.1 though, along with optional special handling for first line column headings. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] multiline CSV fields
Bruce Momjian wrote: > Andrew Dunstan wrote: > > >OK, then should we disallow dumping out data in CVS format that we can't > > >load? Seems like the least we should do for 8.0. > > > > > > > > > > > > > As Tom rightly points out, having data make the round trip was not the > > goal of the exercise. Excel, for example, has no trouble reading such > > data (or at least my installation of it). > > > > Personally I consider CSVs with line end chars embedded in fields to be > > broken anyway, but this was something that was specifically mentioned > > when we were discussing requirements, which is why I coded for it. > > OK, I am pretty uncomforable with this but you know this usage better > than I do. Should we issue a warning message stating it will not be > able to be reloaded? Also, can you explain why we can't read across a newline to the next quote? Is it a problem with the way our code is structured or is it a logical problem? Someone mentioned multibyte encodings but I don't understand how that applies here. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] multiline CSV fields
Andrew Dunstan wrote: > >OK, then should we disallow dumping out data in CVS format that we can't > >load? Seems like the least we should do for 8.0. > > > > > > > > As Tom rightly points out, having data make the round trip was not the > goal of the exercise. Excel, for example, has no trouble reading such > data (or at least my installation of it). > > Personally I consider CSVs with line end chars embedded in fields to be > broken anyway, but this was something that was specifically mentioned > when we were discussing requirements, which is why I coded for it. OK, I am pretty uncomforable with this but you know this usage better than I do. Should we issue a warning message stating it will not be able to be reloaded? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] multiline CSV fields
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: Tom Lane wrote: Which we do not have, because pg_dump doesn't use CSV. I do not think this is a must-fix, especially not if the proposed fix introduces inconsistencies elsewhere. Sure, pg_dump doesn't use it but COPY should be able to load anything it output. I'd buy into that proposition if CSV showed any evidence of being a sanely defined format, but it shows every indication of being neither well-defined, nor self-consistent, nor even particularly portable. I suggest adjusting your expectations. All I expect from that code is being able to load the majority of data from the more popular Microsloth applications. Trying to achieve 100% consistency for corner cases is just going to interfere with the real use-case for the feature, which is coping with output from applications that aren't very consistent in the first place. OK, then should we disallow dumping out data in CVS format that we can't load? Seems like the least we should do for 8.0. As Tom rightly points out, having data make the round trip was not the goal of the exercise. Excel, for example, has no trouble reading such data (or at least my installation of it). Personally I consider CSVs with line end chars embedded in fields to be broken anyway, but this was something that was specifically mentioned when we were discussing requirements, which is why I coded for it. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [pgsql-www] pg_autovacuum is nice ... but ...
Justin Clift wrote: > Bruce Momjian wrote: > > Should I add a TODO to warn if FSM values are too small? Is that doable? > > It sounds like it should be, and it would be a valuable pointer to > people, so yep. > > Any idea who'd be interested in claiming it? Turns out it was already on the TODO list: * Allow free space map to be auto-sized or warn when it is too small The free space map is in shared memory so resizing is difficult. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Documentation on PITR still scarce
Or TODO maybe worded as: * Allow the PITR process to be debugged and data examined --- Simon Riggs wrote: > On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: > > > Is this a TODO? > > Yes, but don't hold your breath on that feature. > > Gavin and I were discussing briefly a design that would allow something > similar to this. The design would allow the user to stop/start recovery > and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to > implement than the proposal below, which I agree is desirable. We > haven't hardly started that discussion yet though. > I called this "recovery console" functionality. > > I'm not sure I like the Suspended Animation phrase, I thought maybe > TARDIS or Langston Field sums it up better (kidding...) > > > Greg Stark wrote: > > > > > > Tom Lane <[EMAIL PROTECTED]> writes: > > > > > > > I suppose it might be useful to have some kind of "suspended animation" > > > > behavior where you could bring up a backend and look at the database in > > > > a strict read-only fashion, not really executing transactions at all, > > > > just to see what you had. Then you could end the recovery and go to > > > > normal operations, or allow the recovery to proceed further if you > > > > decided this wasn't where you wanted to be yet. However that would > > > > require a great deal of mechanism we haven't got (yet). In particular > > > > there is no such thing as strict read-only examination of the database. > > > > > > That would be a great thing to have one day for other reasons aside from > > > the > > > ability to test out a recovered database. It makes warm standby databases > > > much > > > more useful. > > > > > > A warm standby is when you keep a second machine constantly up to date by > > > applying the archived PITR logs as soon as they come off your server. > > > You're > > > ready to switch over at the drop of a hat and don't have to go through the > > > whole recovery process, you just switch the database from recovery mode to > > > active mode and make it your primary database. But in the until then the > > > backup hardware languishes, completely useless. > > > > > > Oracle has had a feature for a long time that you can actually open the > > > standby database in a strict read-only mode and run queries. This is > > > great for > > > a data warehouse situation where you want to run long batch jobs against > > > recent data. > > > > > > -- > > > greg > > > > -- > Best Regards, Simon Riggs > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 8: explain analyze is your friend
Re: [HACKERS] Documentation on PITR still scarce
OK, how would it be worded? * Allow PITR recovery to a read-only server --- Simon Riggs wrote: > On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: > > > Is this a TODO? > > Yes, but don't hold your breath on that feature. > > Gavin and I were discussing briefly a design that would allow something > similar to this. The design would allow the user to stop/start recovery > and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to > implement than the proposal below, which I agree is desirable. We > haven't hardly started that discussion yet though. > I called this "recovery console" functionality. > > I'm not sure I like the Suspended Animation phrase, I thought maybe > TARDIS or Langston Field sums it up better (kidding...) > > > Greg Stark wrote: > > > > > > Tom Lane <[EMAIL PROTECTED]> writes: > > > > > > > I suppose it might be useful to have some kind of "suspended animation" > > > > behavior where you could bring up a backend and look at the database in > > > > a strict read-only fashion, not really executing transactions at all, > > > > just to see what you had. Then you could end the recovery and go to > > > > normal operations, or allow the recovery to proceed further if you > > > > decided this wasn't where you wanted to be yet. However that would > > > > require a great deal of mechanism we haven't got (yet). In particular > > > > there is no such thing as strict read-only examination of the database. > > > > > > That would be a great thing to have one day for other reasons aside from > > > the > > > ability to test out a recovered database. It makes warm standby databases > > > much > > > more useful. > > > > > > A warm standby is when you keep a second machine constantly up to date by > > > applying the archived PITR logs as soon as they come off your server. > > > You're > > > ready to switch over at the drop of a hat and don't have to go through the > > > whole recovery process, you just switch the database from recovery mode to > > > active mode and make it your primary database. But in the until then the > > > backup hardware languishes, completely useless. > > > > > > Oracle has had a feature for a long time that you can actually open the > > > standby database in a strict read-only mode and run queries. This is > > > great for > > > a data warehouse situation where you want to run long batch jobs against > > > recent data. > > > > > > -- > > > greg > > > > -- > Best Regards, Simon Riggs > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 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] multiline CSV fields
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Which we do not have, because pg_dump doesn't use CSV. I do not think > >> this is a must-fix, especially not if the proposed fix introduces > >> inconsistencies elsewhere. > > > Sure, pg_dump doesn't use it but COPY should be able to load anything it > > output. > > I'd buy into that proposition if CSV showed any evidence of being a > sanely defined format, but it shows every indication of being neither > well-defined, nor self-consistent, nor even particularly portable. > I suggest adjusting your expectations. All I expect from that code is > being able to load the majority of data from the more popular Microsloth > applications. Trying to achieve 100% consistency for corner cases is > just going to interfere with the real use-case for the feature, which is > coping with output from applications that aren't very consistent in the > first place. OK, then should we disallow dumping out data in CVS format that we can't load? Seems like the least we should do for 8.0. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Adding Reply-To: to Lists configuration ...
Chris Green <[EMAIL PROTECTED]> wrote: > > On Sun, Nov 28, 2004 at 07:34:28PM -0400, Marc G. Fournier wrote: > > > > What is the general opinion of this? I'd like to implement it, but not so > > much so that I'm going to beat my head against a brick wall on it ... > > > Personally I'm against it because it means that I'll often get two > replies when people reply to my postings. However it's not a big > issue for me. Actually, it would result in just the opposite. Jim ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Stopgap solution for table-size-estimate updatingproblem
>> One possibility: vacuum already knows how many tuples it removed. We >> could set reltuples equal to, say, the mean of the number-of-tuples- >> after-vacuuming and the number-of-tuples-before. In a steady state >> situation this would represent a fairly reasonable choice. In cases >> where the table size has actually decreased permanently, it'd take a few >> cycles of vacuuming before reltuples converges to the new value, but that >> doesn't seem too bad. > > That sounds good to me. Covers all cases I can see from here. Yes, sounds good for me also. I think that would be a good thing even if viewed isolated from the rest of the proposal. I am sorry if I made the impression that I don't like a change in this direction in general, I think there is need for both. I am only worried about core OLTP applications where every query is highly tuned (and a different plan is more often than not counter productive, especially if it comes and goes without intervention). >> A standalone ANALYZE should still do what it does now, though, I think; >> namely set reltuples to its best estimate of the current value. good, imho :-) > A GUC-free solution...but yet manual control is possible. Sounds good to > me - and for you Andreas, also? It is the GUC to keep the optimizer from using the dynamic page count, that I would still like to have. I especially liked Simon's name for it: enable_dynamic_statistics=true Tom wrote: >> But I am used to applications >> that prepare a query and hold the plan for days or weeks. If you happen to >> create the plan when the table is by chance empty you lost. > > You lose in either case, since this proposal doesn't change when > planning occurs or doesn't occur. This is not true in my case, since I only "update statistics"/analyze when the tables have representative content (i.e. not empty). Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Error: column "nsptablespace" does not exist
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] on behalf of Christopher Kings-Lynne Sent: Sun 11/28/2004 2:57 PM To: Roland Volkmann Cc: PostgreSQL Developers Subject: Re: [HACKERS] Error: column "nsptablespace" does not exist No other applications will be broken because no other application is crazy enough to worry about displaying the tablespace on a schema just yet. Sorry Chris - obviously the pgAdmin team are just a bit crazier than your lot :-) And a little faster fixing it :-) Regards, Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Documentation on PITR still scarce
On Mon, 2004-11-29 at 02:20, Bruce Momjian wrote: > Is this a TODO? Yes, but don't hold your breath on that feature. Gavin and I were discussing briefly a design that would allow something similar to this. The design would allow the user to stop/start recovery and turn a debug trace on/off, in a gdb-like mode. Thats a lot easier to implement than the proposal below, which I agree is desirable. We haven't hardly started that discussion yet though. I called this "recovery console" functionality. I'm not sure I like the Suspended Animation phrase, I thought maybe TARDIS or Langston Field sums it up better (kidding...) > Greg Stark wrote: > > > > Tom Lane <[EMAIL PROTECTED]> writes: > > > > > I suppose it might be useful to have some kind of "suspended animation" > > > behavior where you could bring up a backend and look at the database in > > > a strict read-only fashion, not really executing transactions at all, > > > just to see what you had. Then you could end the recovery and go to > > > normal operations, or allow the recovery to proceed further if you > > > decided this wasn't where you wanted to be yet. However that would > > > require a great deal of mechanism we haven't got (yet). In particular > > > there is no such thing as strict read-only examination of the database. > > > > That would be a great thing to have one day for other reasons aside from the > > ability to test out a recovered database. It makes warm standby databases > > much > > more useful. > > > > A warm standby is when you keep a second machine constantly up to date by > > applying the archived PITR logs as soon as they come off your server. You're > > ready to switch over at the drop of a hat and don't have to go through the > > whole recovery process, you just switch the database from recovery mode to > > active mode and make it your primary database. But in the until then the > > backup hardware languishes, completely useless. > > > > Oracle has had a feature for a long time that you can actually open the > > standby database in a strict read-only mode and run queries. This is great > > for > > a data warehouse situation where you want to run long batch jobs against > > recent data. > > > > -- > > greg > > -- Best Regards, Simon Riggs ---(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] multiline CSV fields
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Which we do not have, because pg_dump doesn't use CSV. I do not think >> this is a must-fix, especially not if the proposed fix introduces >> inconsistencies elsewhere. > Sure, pg_dump doesn't use it but COPY should be able to load anything it > output. I'd buy into that proposition if CSV showed any evidence of being a sanely defined format, but it shows every indication of being neither well-defined, nor self-consistent, nor even particularly portable. I suggest adjusting your expectations. All I expect from that code is being able to load the majority of data from the more popular Microsloth applications. Trying to achieve 100% consistency for corner cases is just going to interfere with the real use-case for the feature, which is coping with output from applications that aren't very consistent in the first place. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings