Re: [HACKERS] On file locking
Giles Lean [EMAIL PROTECTED] writes: Boring reference material follows. Couldn't help noticing that you omitted HPUX ;-) On HPUX 10.20, flock doesn't seem to exist (hasn't got a man page nor any mention in /usr/include). lockf says All locks for a process are released upon the first close of the file, even if the process still has the file opened, and all locks held by a process are released when the process terminates. and When a file descriptor is closed, all locks on the file from the calling process are deleted, even if other file descriptors for that file (obtained through dup() or open(), for example) still exist. which seems to imply (but doesn't actually say) that HPUX keeps track of exactly which process took out the lock, even if the file is held open by multiple processes. This all doesn't look good for using file locks in the way I had in mind :-( ... but considering that all these man pages seem pretty vague, maybe some direct experimentation is called for. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] COUNT and Performance ...
This patch adds a note to the documentation describing why the performance of min() and max() is slow when applied to the entire table, and suggesting the simple workaround most experienced Pg users eventually learn about (SELECT xyz ... ORDER BY xyz LIMIT 1). Any suggestions on improving the wording of this section would be welcome. Cheers, -- ORDER and LIMIT work pretty fast (no seq scan). In special cases there can be another way to avoid seq scans: action=# select tuple_count from pgstattuple('t_text'); tuple_count - 14203 (1 row) action=# BEGIN; BEGIN action=# insert into t_text (suchid) VALUES ('10'); INSERT 578606 1 action=# select tuple_count from pgstattuple('t_text'); tuple_count - 14204 (1 row) action=# ROLLBACK; ROLLBACK action=# select tuple_count from pgstattuple('t_text'); tuple_count - 14203 (1 row) If people want to count ALL rows of a table. The contrib stuff is pretty useful. It seems to be transaction safe. The performance boost is great (PostgreSQL 7.3, RedHat, 166Mhz): root@actionscouts:~# time psql action -c select tuple_count from pgstattuple('t_text'); tuple_count - 14203 (1 row) real0m0.266s user0m0.030s sys 0m0.020s root@actionscouts:~# time psql action -c select count(*) from t_text count --- 14203 (1 row) real0m0.701s user0m0.040s sys 0m0.010s I think that this could be a good workaround for huge counts (maybe millions of records) with no where clause and no joins. Hans http://kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [mail] Re: [HACKERS] Windows Build System
Andrew Dunstan wrote: I think I have sorted through the confusion. Looks like the only thing cygwin might be used for is a client. Here's what the manual that comes with the 4.0.9gamma source says: There are two versions of the MySQL command-line tool: Binary Description mysql Compiled on native Windows, which offers very limited text editing capabilities. mysqlc Compiled with the Cygnus GNU compiler and libraries, which offers readline editing. If you want to use mysqlc.exe, you must copy `C:\mysql\lib\cygwinb19.dll' to your Windows system directory (`\windows\system' or similar place). I am using SRA's Win32 port here on XP, and it doesn't use readline. It does have arrow handling for psql, but does not do Control-A/E handling, nor keep the history between psql invocations. I assume this is what the limited command-line handling they are talking about. -- 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] Linux.conf.au 2003 Report
On Sat, Feb 01, 2003 at 02:35:15PM +0900, Curt Sampson wrote: Sure. But you still want to be able to say (and can say, in some [many?] socket API implementations) that you want to accept only IPv4 or only IPv6 connections. I also want to be able to say the same thing in my database. You just create either an ipv4 or ipv6 socket. And then you can bind to an address of that type if you want. Either all addresses or a specific one. Depending on the OS, binding to all addresses on IPv6 will also bind to all the ipv4 addresses, which can be both handy an annoying. On others you need 2 sockets if you want to listen on both ipv4 and ipv6, which makes more sense. Kurt ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Windows Build System - My final thoughts
Lamar Owen wrote: On Friday 31 January 2003 03:21, Bruce Momjian wrote: Man, I go away for one day, and look what you guys get into. :-) No duh. Whew. Lastly, SRA just released _today_ their first Win32 port of PostgreSQL, and it is _threaded_: http://osb.sra.co.jp/PowerGres/ Is there an English translation of the site so one who doesn't speak or write Japanese can try it out? No, sorry. Tatsuo mentioned that. However, Babelfish will do the translation: http://world.altavista.com/ Put in the URL, and choose translate Japanese to English. -- 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] Windows Build System - My final thoughts
Jeff Davis wrote: As for build environment, we have two audiences --- those using binaries, and those compiling from source. Clearly we are going to have more binary users vs. source users on Win32 than on any other platform, so at this stage I think making thing easier for the majority of our Unix developers is the priority, meaning we should use our existing Makefiles and cygwin to compile. Later, if things warrant it, we can do VC++ project files somehow. I'm ignorant when it comes to build environments on windows, but I was under the impression that DJGPP was mostly a complete environment. Are there any plans to support it, or is it even possible? I don't think we want to throw our Unix folks into culture shock. Let's pick one build environment and go from there, either cygwin or something else. Once the patches are in, folks can test the various build options. -- 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] Windows Build System - My final thoughts
Justin Clift wrote: + Aside from all this, it might be nice to have a few Win32 specific gui pieces in place at the time that PostgreSQL 7.4 Win32 is released. Am sure they'll develop over time, but was thinking we should at least make a good impression with the first release. Hey, if we make a really bad impression with the first release, then there might not be the quadruple-zillion Windows PG users after all. If that sounds like a good idea, maybe adding the GUC variables random_query_delay (minutes), crash_how_often (seconds), and reboot_plus_corrupt_please (true/false)? What we need is for the backend to query postgresql.org to set those parameters, so we can control how many Win32 users adopt PostgreSQL. :-) -- 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: [mail] Re: [HACKERS] Windows Build System
From: Bruce Momjian [EMAIL PROTECTED] I am using SRA's Win32 port here on XP, and it doesn't use readline. It does have arrow handling for psql, but does not do Control-A/E handling, nor keep the history between psql invocations. I assume this is what the limited command-line handling they are talking about. Probably. But readline is GPL'd (not LGPL'd), so my company can't bundle it or anything that uses it with any non-GPL software we distribute. Similar arguments probably apply to a cygwin based port (not one built using cygwin, but requiring it to run) - IANAL but the company has to err on the side of caution here. andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] COUNT and Performance ...
On Sun, 2003-02-02 at 03:55, Hans-Jürgen Schönig wrote: If people want to count ALL rows of a table. The contrib stuff is pretty useful. It seems to be transaction safe. Interesting -- I didn't know about the contrib stuff. I'll update the docs patch. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Linux.conf.au 2003 Report
Kurt Roeckx wrote: On Sat, Feb 01, 2003 at 02:35:15PM +0900, Curt Sampson wrote: Sure. But you still want to be able to say (and can say, in some [many?] socket API implementations) that you want to accept only IPv4 or only IPv6 connections. I also want to be able to say the same thing in my database. You just create either an ipv4 or ipv6 socket. And then you can bind to an address of that type if you want. Either all addresses or a specific one. Depending on the OS, binding to all addresses on IPv6 will also bind to all the ipv4 addresses, which can be both handy an annoying. On others you need 2 sockets if you want to listen on both ipv4 and ipv6, which makes more sense. Well, that's interesting. Current CVS only binds to IPv6, and assumes IPv4 will work too. If some OS's require a separate Ipv4 binding, we are going to hear about it before 7.4: --- #ifdef HAVE_IPV6 /* Try INET6 first. May fail if kernel doesn't support IP6 */ status = StreamServerPort(AF_INET6, VirtualHost, (unsigned short) PostPortNumber, UnixSocketDir, ServerSock_INET); if (status != STATUS_OK) { elog(LOG, IPv6 support disabled --- perhaps the kernel does not support IPv6); #endif status = StreamServerPort(AF_INET, VirtualHost, (unsigned short) PostPortNumber, UnixSocketDir, ServerSock_INET); if (status != STATUS_OK) { postmaster_error(cannot create INET stream port); ExitPostmaster(1); } #ifdef HAVE_IPV6 else elog(LOG, IPv4 socket created); } #endif } -- 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/users-lounge/docs/faq.html
[HACKERS] Case Studio II
Has anyone seriously tried out this package? It looks like a cheaper variant on ERWin, with the merit of having some PostgreSQL support. It only runs on WinTel, which is somewhat unfortunate, but I haven't gotten the sort of diagramming I have been looking for out of AutoDoc, so I'd be game to look at something pricey, assuming it is useful. -- http://cbbrowne.com/info/linux.html Rules of the Evil Overlord #50. My main computers will have their own special operating system that will be completely incompatible with standard IBM and Macintosh powerbooks. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Linux.conf.au 2003 Report
On Sun, 2 Feb 2003, Kurt Roeckx wrote: On Sat, Feb 01, 2003 at 02:35:15PM +0900, Curt Sampson wrote: Sure. But you still want to be able to say (and can say, in some [many?] socket API implementations) that you want to accept only IPv4 or only IPv6 connections. I also want to be able to say the same thing in my database. You just create either an ipv4 or ipv6 socket. Um...I'm talking about inserting data into a column in postgres. How do I declare that a column can accept IPv4 addresses only? And why will *nobody* address the question of whether this type should include ISO/OSI addresses or not, and why? cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(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] PostgreSQL, NetBSD and NFS
On Saturday 01 February 2003 15:48, Tom Lane wrote: More and more bizarre. What is the hardware platform --- does it have TAS? NetBSD on a Pentium (i386 port) so yes, it does have TAS. I assume you were thinking about the spinlock emulation. I have been looking through backend/storage/lmgr/lwlock.c and backend/storage/lmgr/spin.c myself and can't find any place that it can get into an infinite loop without making a system call within the loop. It's very odd. Also odd, why would running over NFS have any bearing on it if we could find such a place? -- D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] pg_hba.conf hostmask.
Currently in pg_hba.conf you specify the ip addresses that can connect with 2 fields: the ip address and the mask. What do you think about changing it to ip address/mask? Where mask can be both the current mask, or the prefix length. It's so much handier to use, especially for ipv6. Kurt ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Last call for 7.3.2
Neil Conway [EMAIL PROTECTED] writes: I posted a patch fixing PQcmdTuples() for some additional commands -- should that go into 7.3.2? I was thinking of that as a feature addition, but you still have time to convince me it's a bug fix ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Last call for 7.3.2
On Sun, 2003-02-02 at 15:15, Tom Lane wrote: I was thinking of that as a feature addition, but you still have time to convince me it's a bug fix ... IMHO, it's a bugfix, or at least fixes a notable omission: the documented function of PQcmdTuples is that it Returns the number of rows affected by the SQL command. My only reservation is that I rewrote the existing function, rather than modifying it to support the new command tags (which is better long-term, IMHO), but perhaps too invasive for a stable branch. I'm happy to let you make the call on this one... Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] COUNT and Performance ...
On Sun, 2003-02-02 at 13:04, Tom Lane wrote: I think your test case is small enough that the whole table is resident in memory, so this measurement only accounts for CPU time per tuple and not any I/O. Given the small size of pgstattuple's per-tuple loop, the speed differential is not too surprising --- but it won't scale up to larger tables. Good observation. When the entire table is in cache, pgstattuple about 4 times faster than count(*) on my machine. When the table is too large to fit into cache, the performance difference drops to 8% in favour of pgstattuple: nconway=# select count(*) from big_table; count - 8388612 (1 row) Time: 26769.99 ms nconway=# SELECT tuple_count FROM pgstattuple('big_table'); tuple_count - 8388612 (1 row) Time: 24658.87 ms Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Last call for 7.3.2
Neil Conway [EMAIL PROTECTED] writes: On Sun, 2003-02-02 at 15:15, Tom Lane wrote: I was thinking of that as a feature addition, but you still have time to convince me it's a bug fix ... IMHO, it's a bugfix, or at least fixes a notable omission: the documented function of PQcmdTuples is that it Returns the number of rows affected by the SQL command. Yeah, but the next sentence makes it perfectly clear which commands the function works for. Extending it to FETCH and MOVE is clearly a useful extension ... but it's an extension. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Linux.conf.au 2003 Report
On Sun, 2 Feb 2003, Bruce Momjian wrote: Kurt Roeckx wrote: On Sat, Feb 01, 2003 at 02:35:15PM +0900, Curt Sampson wrote: Sure. But you still want to be able to say (and can say, in some [many?] socket API implementations) that you want to accept only IPv4 or only IPv6 connections. I also want to be able to say the same thing in my database. You just create either an ipv4 or ipv6 socket. And then you can bind to an address of that type if you want. Either all addresses or a specific one. Depending on the OS, binding to all addresses on IPv6 will also bind to all the ipv4 addresses, which can be both handy an annoying. On others you need 2 sockets if you want to listen on both ipv4 and ipv6, which makes more sense. Well, that's interesting. Current CVS only binds to IPv6, and assumes IPv4 will work too. If some OS's require a separate Ipv4 binding, we are going to hear about it before 7.4: I don't think we should listen on IPv6 just because it is supported. It should be a configuration variable: tcpip_socket = true ipv6 = true Gavin ---(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] Linux.conf.au 2003 Report
Gavin Sherry wrote: On Sun, 2 Feb 2003, Bruce Momjian wrote: Kurt Roeckx wrote: On Sat, Feb 01, 2003 at 02:35:15PM +0900, Curt Sampson wrote: Sure. But you still want to be able to say (and can say, in some [many?] socket API implementations) that you want to accept only IPv4 or only IPv6 connections. I also want to be able to say the same thing in my database. You just create either an ipv4 or ipv6 socket. And then you can bind to an address of that type if you want. Either all addresses or a specific one. Depending on the OS, binding to all addresses on IPv6 will also bind to all the ipv4 addresses, which can be both handy an annoying. On others you need 2 sockets if you want to listen on both ipv4 and ipv6, which makes more sense. Well, that's interesting. Current CVS only binds to IPv6, and assumes IPv4 will work too. If some OS's require a separate Ipv4 binding, we are going to hear about it before 7.4: I don't think we should listen on IPv6 just because it is supported. It should be a configuration variable: tcpip_socket = true ipv6 = true We had a huge discussion on this. I think you were away for it. You can control what you listen on by modifying pg_hba.conf. -- 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] Case Studio II
Hi, I use it and can recommend it. It is actively developed/maintained (the updates are free). There is very basic support of 7.3 schemas in the new version for instance... And the development team is very responsive, if you don't like or missing something write them... kuba On Sun, 2 Feb 2003 [EMAIL PROTECTED] wrote: Has anyone seriously tried out this package? It looks like a cheaper variant on ERWin, with the merit of having some PostgreSQL support. It only runs on WinTel, which is somewhat unfortunate, but I haven't gotten the sort of diagramming I have been looking for out of AutoDoc, so I'd be game to look at something pricey, assuming it is useful. -- http://cbbrowne.com/info/linux.html Rules of the Evil Overlord #50. My main computers will have their own special operating system that will be completely incompatible with standard IBM and Macintosh powerbooks. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] Windows Build System - My final thoughts
Bruce Momjian wrote: Justin Clift wrote: + Aside from all this, it might be nice to have a few Win32 specific gui pieces in place at the time that PostgreSQL 7.4 Win32 is released. Am sure they'll develop over time, but was thinking we should at least make a good impression with the first release. Hey, if we make a really bad impression with the first release, then there might not be the quadruple-zillion Windows PG users after all. If that sounds like a good idea, maybe adding the GUC variables random_query_delay (minutes), crash_how_often (seconds), and reboot_plus_corrupt_please (true/false)? What we need is for the backend to query postgresql.org to set those parameters, so we can control how many Win32 users adopt PostgreSQL. :-) All your [data] base belong to us ? ;-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL, NetBSD and NFS
D'Arcy J.M. Cain [EMAIL PROTECTED] writes: Also odd, why would running over NFS have any bearing on it if we could find such a place? Yup, 'tis the question. The only theory I have been able to come up with is that there's something flaky about your network hardware, such that Postgres sometimes reads bad data from the NFS server. But the glaring problem with that theory is that bad data coming from a regular disk drive generally results in error messages or core dumps. Silent hangs would be a new behavior AFAIR. At this point I think you need to rebuild with --enable-debug and --enable-cassert (if you didn't already) and then capture some stack traces from the stuck backend. We have to find out what the backend thinks it's doing. BTW: *are* we certain it's associated with NFS, and not a hardware problem on your NetBSD box? Can you perform the same tests running the database off a local disk? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Linux.conf.au 2003 Report
Bruce Momjian [EMAIL PROTECTED] writes: Gavin Sherry wrote: I don't think we should listen on IPv6 just because it is supported. It should be a configuration variable: tcpip_socket = true ipv6 = true We had a huge discussion on this. I think you were away for it. You can control what you listen on by modifying pg_hba.conf. Can you actually control whether the postmaster is listening by modifying pg_hba.conf? I don't think so. I think I was the one who talked us into assuming that ipv4 and ipv6 should be treated as a single protocol. But some people have since made pretty good cases that it's better to regard them as separate protocols. Perhaps we should rethink that decision. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Linux.conf.au 2003 Report
On Sun, 2 Feb 2003, Tom Lane wrote: I think I was the one who talked us into assuming that ipv4 and ipv6 should be treated as a single protocol. But some people have since made pretty good cases that it's better to regard them as separate protocols. From a security standpoint, I think it's definitely better to regard them as separate protocols. They are certainly separately filtered on firewalls, and they are often routed differently, too. That said, I see no reason not to have some sort of easy way of saying, listen on all the interfaces you can find using all the protocols you know. So long as you have the ability to distinguish where you listen by both protocol and address, it's easy to be as secure as you need to be. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Linux.conf.au 2003 Report
On Sun, Feb 02, 2003 at 12:49:34PM -0500, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Gavin Sherry wrote: I don't think we should listen on IPv6 just because it is supported. It should be a configuration variable: tcpip_socket = true ipv6 = true We had a huge discussion on this. I think you were away for it. You can control what you listen on by modifying pg_hba.conf. Can you actually control whether the postmaster is listening by modifying pg_hba.conf? I don't think so. Why isn't virtual_host used for deciding to what addresses it should listen? It currently only seems to support 1 address, and I don't really know why. Is there a reason you can't make this a list of hostnames/ip addresses? It really is where it belongs. Kurt ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PostgreSQL, NetBSD and NFS
On Sunday 02 February 2003 12:26, Tom Lane wrote: D'Arcy J.M. Cain [EMAIL PROTECTED] writes: Also odd, why would running over NFS have any bearing on it if we could find such a place? Yup, 'tis the question. The only theory I have been able to come up with is that there's something flaky about your network hardware, Possible but two separate networks? At this point I think you need to rebuild with --enable-debug and --enable-cassert (if you didn't already) and then capture some stack traces from the stuck backend. We have to find out what the backend thinks it's doing. That was going to be my next step. BTW: *are* we certain it's associated with NFS, and not a hardware problem on your NetBSD box? Can you perform the same tests running the database off a local disk? That box is running 5 production database engines on 5 different ports. This is the 6th one and the only difference is that it is running from the NFS mounted drive. -- D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PERFORM] not using index for select min(...)
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: For example, the following query is not possible to workaround in PostgreSQL: select teams_desc.team_id, team_name, team_code, notes, min(teams_tree.treeno) as lnode, max(teams_tree.treeno) as rnode, parent.team_id as parent_id, count(*)/2 as tlevel from teams_desc JOIN teams_tree USING (team_id) join teams_tree parent ON parent.treeno teams_tree.treeno join teams_tree parents on parents.treeno teams_tree.treeno WHERE parent.treeno = (SELECT max(p1.treeno) from teams_tree p1 where p1.treeno teams_tree.treeno and exists (select treeno from teams_tree p2 where p2.treeno teams_tree.treeno and p2.team_id = p1.team_id)) AND EXISTS (select parents2.team_id from teams_tree parents2 where parents2.treeno teams_tree.treeno AND parents2.team_id = parents.team_id) group by teams_desc.team_id, team_name, team_code, notes, parent.team_id; While one would hardly expect the above query to be fast, it is dissapointing that it takes about 8-10 times as long to execute on PostgreSQL as on MSSQL, since MSSQL seems to be able to use indexes to evaluate all three MIN() and MAX() expressions. I think you are leaping to conclusions about why there's a speed difference. Or maybe I'm too dumb to see how an index could be used to speed these min/max operations --- but I don't see that one would be useful. Certainly not an index on treeno alone. Would you care to explain exactly how it's done? Intuitively, it seems that an index on treeno is exactly what would make the difference -- but min() and max() have to be smart enough to use them when necessary. I have a strong suspicion that min() and max() in MSSQL and other databases are integrated into the parser, planner, and executor directly. It's the only way I can think of that would make it possible for those functions to make use of indexes and other advantages to the fullest extent possible. For instance, in the above query, the max() operation in the subselect would tell the planner and executor to use the index on p1.treeno for two comparisons simultaneously: p1.treeno teams_tree.treeno and max(p1.treeno). That means that the executor would descend the tree of the (btree) index and instead of just comparing whether a branch is less than teams_tree.treeno and following *all* of the branches that qualify, it would follow the *largest* branch that qualified and nothing else. That's a very significant optimization of the search, because instead of eliminating an average of 50% of the branches to follow at each node, it eliminates all but one. But it's not something that a naive aggregate function would be able to do: the min() and max() aggregates would (I expect) have to become first class objects in the parser, planner, and executor just as the WHERE clause and its conditions are. There may be other aggregate functions that can make use of indexes to the same extent that min() and max() should be able to, but I don't know what they are offhand, and I certainly doubt that they would be used nearly as often as min() and max(). Even with our type system, I'd think that min() and max() would be relatively straightforward as first class objects (well, as straightforward as any first class object that gets implemented in all three stages, at any rate!): they work efficiently (that is, can use an index scan) when the column in question has a btree index on it, and fall back to sequential scans (and use the appropriate operator, or ) when the column in question doesn't. It might even be reasonable to allow a type to overload these functions so that the planner and executor use the type-provided functions when available (with the limitation that such type-provided functions would always require a sequential scan as they do now) and fall back to the builtin ones when the type doesn't provide them. I imagine this might complicate the parser, planner, and executor quite a bit, however. So the interesting question that arises from the above is: are there any types that define a min() and max() but which *do not* define and ? I can't think of such types myself but can imagine that some esoteric data types might qualify. For the purposes of optimizing the common case, however, such esoteric types could easily be ignored, but it's for their sake that it would be useful to be able to use a type-defined function in place of min() or max(). -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] PGP signing releases
Folks, I think we should PGP sign all the official packages that are provided for download from the various mirror sites. IMHO, this is important because: - ensuring that end users can trust PostgreSQL is an important part to getting the product used in mission-critical applications, as I'm sure you all know. Part of that is producing good software; another part is ensuring that users can trust that the software we put out hasn't been tampered with. - people embedding trojan horses in open source software is not unheard of. In fact, it's probably becoming more common: OpenSSH, sendmail, libpcap/tcpdump and bitchx have all been the victim of trojan horse attacks fairly recently. - PGP signing binaries is relatively easy, and doesn't need to be done frequently. Comments? I'd volunteer to do the work myself, except that it's pretty closely intertwined with the release process itself... Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(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] PGP signing releases
Neil Conway [EMAIL PROTECTED] writes: I think we should PGP sign all the official packages that are provided for download from the various mirror sites. This is probably a good idea. I'd volunteer to do the work myself, except that it's pretty closely intertwined with the release process itself... Marc would have to be the guy who actually generates the tarball signatures. But you could possibly help him get the procedure set up, if he's not familiar with it already... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PGP signing releases
On Sun, 2003-02-02 at 18:39, Neil Conway wrote: Folks, I think we should PGP sign all the official packages that are provided for download from the various mirror sites. IMHO, this is important because: - ensuring that end users can trust PostgreSQL is an important part to getting the product used in mission-critical applications, as I'm sure you all know. Part of that is producing good software; another part is ensuring that users can trust that the software we put out hasn't been tampered with. - people embedding trojan horses in open source software is not unheard of. In fact, it's probably becoming more common: OpenSSH, sendmail, libpcap/tcpdump and bitchx have all been the victim of trojan horse attacks fairly recently. - PGP signing binaries is relatively easy, and doesn't need to be done frequently. Comments? I'd volunteer to do the work myself, except that it's pretty closely intertwined with the release process itself... Cheers, Neil Actually, if you just had everyone sign the official key and submit it back to the party that's signing, that would probably be good enough. Basically, as long as people can verify the package has been signed and can reasonably verify that the signing key is safe and/or can be verified, confidence should be high in the signed package. I certainly have no problem with people signing my key nor with signing others as long as we can verify/authenticate each others keys prior. Regards, -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PGP signing releases
On Sun, 2 Feb 2003, Neil Conway wrote: Folks, I think we should PGP sign all the official packages that are provided for download from the various mirror sites. IMHO, this is important because: - ensuring that end users can trust PostgreSQL is an important part to getting the product used in mission-critical applications, as I'm sure you all know. Part of that is producing good software; another part is ensuring that users can trust that the software we put out hasn't been tampered with. right, that is why we started to provide md5 checksums ... I'd volunteer to do the work myself, except that it's pretty closely intertwined with the release process itself... well, if you want to tell me the steps, I'll consider it ... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PGP signing releases
On Sunday 02 February 2003 21:23, Marc G. Fournier wrote: On Sun, 2 Feb 2003, Neil Conway wrote: I think we should PGP sign all the official packages that are provided for download from the various mirror sites. IMHO, this is important because: right, that is why we started to provide md5 checksums ... Actually this impacts RPMs more than the tarball, although the tarball's md5 sums are important. I have been intending to do this for some time; maybe it's time to bite the bullet. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] 7.2 - 7.3 incompatibility
I found an example of a casting problem in our source code now that we're running 7.3: SELECT CURRENT_DATE - EXTRACT(DOW FROM CURRENT_DATE); (Get's the start of the week); This worked in 7.2, but fails in 7.3 with: ERROR: Unable to identify an operator '-' for types 'date' and 'double precision' You will have to retype this query using an explicit cast I'm not sure really why DOW needs to be double precision, but hey... I guess this isn't so important, as there are workarounds (which are complicated...) Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Linux.conf.au 2003 Report
Kurt Roeckx [EMAIL PROTECTED] writes: [virtual_host] currently only seems to support 1 address, and I don't really know why. Is there a reason you can't make this a list of hostnames/ip addresses? That was what the boys at uu.net needed, so that's what they implemented. If you need more, I think a patch would be accepted ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] On file locking
On Sun, 2 Feb 2003, Tom Lane wrote: This all doesn't look good for using file locks in the way I had in mind :-( ... but considering that all these man pages seem pretty vague, maybe some direct experimentation is called for. Definitely. I wonder about the NetBSD manpage quotes in the post you followed up to, given that last time I checked flock() was implmented, in the kernel, using fcntl(). Either that's changed, or the manpages are unclear or lying. This has been my experience in the past; locking semantics are subtle and unclear enough that you really need to test for exactly what you want at build time on every system, and you've got to do this testing on the filesystem you intend to put the locks on. (So you don't, e.g., test a local filesystem but end up with data on an NFS filesystem with different locking semantics.) That's what procmail does. Given this, I'm not even sure the whole idea is worth persuing. (Though I guess I should find out what NetBSD is really doing, and fix the manual pages correspond to reality.) cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(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] Windows Build System - My final thoughts
-Original Message- From: Justin Clift [mailto:[EMAIL PROTECTED]] Sent: 02 February 2003 15:01 To: Bruce Momjian Cc: Lamar Owen; PostgreSQL-development Subject: Re: [HACKERS] Windows Build System - My final thoughts Hey, if we make a really bad impression with the first release, then there might not be the quadruple-zillion Windows PG users after all. If that sounds like a good idea, maybe adding the GUC variables random_query_delay (minutes), crash_how_often (seconds), and reboot_plus_corrupt_please (true/false)? What we need is for the backend to query postgresql.org to set those parameters, so we can control how many Win32 users adopt PostgreSQL. :-) All your [data] base belong to us ? I'll get working on some php code if we're happy to use http for the comms ;-) Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Last call for 7.3.2
The plan for 7.3.2 release is for Marc to wrap the tarball tomorrow and announce on Tuesday. I have already stamped the version number and updated the release history in CVS, but is there anyone out there with last-minute fixes? In particular, is there anything that needs to be done to update the pre-built documentation that will go into the tarball? I'm still quite unclear on what our build process for that is ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Last call for 7.3.2
On Sun, 2003-02-02 at 14:59, Tom Lane wrote: The plan for 7.3.2 release is for Marc to wrap the tarball tomorrow and announce on Tuesday. I have already stamped the version number and updated the release history in CVS, but is there anyone out there with last-minute fixes? I posted a patch fixing PQcmdTuples() for some additional commands -- should that go into 7.3.2? http://archives.postgresql.org/pgsql-patches/2003-01/msg00187.php Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Interactive Documentation - how do you want it to work?
As you may have noticed we have recently revamped the Interactive Documentation on the website (http://www.postgresql.org/docs). This has raised a couple of questions about how the idocs should work, so I'd like to get some votes on the following 2 issues: 1) How should comments be linked to document sets?: - Each comment attaches only to the page name version of the page to which it was submitted. - Each comment attaches only to the page name, version of the page to which it was submitted *and* subsequent versions (this is the current behaviour). - Each comment should attach to the page name to which it was submitted regardless of the version. 2) Bearing in mind your answer to the previous question, should all the comments be deleted when useful examples have been merged into the main documents (remember that the definition of 'useful' may vary), or should we only remove the 'junk' ones? - Yes - No Thanks, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Irix 6.2, Postgres 7.3.1, some brokenness
I've got a 6-way SGI Challenge L with R4400/200 cpu's in it, and a gig of interleaved ram. I am running postgres 7.3.1, which I compiled 64-bit with MIPSPro. The database is living on an ultrawide scsi raid. I am also running postgres on a 2-cpu (85mhz) Sparc 20 with 384mb of ram, and the database lives on a 10krpm single narrow disk. I'm finding that the Sparc 20 is about twice as fast as the Challenge. Postgres on the '20 was compiled 32-bit (of course) with gcc 3.2. When I am testing the machines, I have run tests with 1 db handle, with 2 db handles, and with 2 vs 6 handles. Consistently, the 20 is faster than the Challenge. I suspect something is going wrong here on mips. I have tried, without any luck, to get postgres compiled with gcc 3.2 (from freeware.sgi.com). It keeps adding '-r' when its linking, even when LDFLAGS is unset, LD is 'gcc' and CC is 'gcc'. I could do the linking manually, but this is not really a viable option for a release. Additionally, postgres does *not* trap divide-by-zero errors, as Solaris and Linux do. 'select 1/0' causes the backend to crash. elvis=# select 1/0; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: WARNING: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. Failed. !# Justin tells me there is a release imminent, and that I should mention this ASAP. Neil suggested I try with gcc, but as I said, I've been unable to get it to compile with gcc. Also, I understand (but can confirm on monday) that Oracle has dropped support for Irix entirely. This would make postgres the *only* database for SGI MIPS, and I'd really like to see it better supported. SGI machines are awesome for database servers. Anyone needing a shell on the machine can feel free to ask. Alex -- alex avriette $^X is my programming language of choice. [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] Irix 6.2, Postgres 7.3.1, some brokenness
I have tried, without any luck, to get postgres compiled with gcc 3.2 (from freeware.sgi.com). It keeps adding '-r' when its linking, even when LDFLAGS is unset, LD is 'gcc' and CC is 'gcc'. I could do the linking manually, but this is not really a viable option for a release. Looking at this more, I see this: [goro:~/postgresql-7.3.1/src] alex% grep LD Makefile* | grep -- '-r' Makefile.global:LDREL = -r Can we just not append LDREL to LD if we're on Irix? alex -- alex avriette $^X is my programming language of choice. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGP signing releases
Marc G. Fournier [EMAIL PROTECTED] writes: On Sun, 2 Feb 2003, Neil Conway wrote: - ensuring that end users can trust PostgreSQL is an important part to getting the product used in mission-critical applications, as I'm sure you all know. Part of that is producing good software; another part is ensuring that users can trust that the software we put out hasn't been tampered with. right, that is why we started to provide md5 checksums ... The md5 checksum is useful as a cross-check that you've got a clean copy, but it doesn't prove that the copy on the FTP site hasn't been tampered with. Someone who's managed to break into the FTP server could replace the tarball with a trojaned version *and* alter the md5 file to match. The point of a PGP signature is that only someone who has the corresponding secret key could make a signature file that matches the tarball and the public key. regards, tom lane ---(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] Irix 6.2, Postgres 7.3.1, some brokenness
alex avriette [EMAIL PROTECTED] writes: I've got a 6-way SGI Challenge L with R4400/200 cpu's in it, and a gig of interleaved ram. I am running postgres 7.3.1, which I compiled 64-bit with MIPSPro. I'm finding that the Sparc 20 is about twice as fast as the Challenge. I'm betting that Postgres doesn't think there's any TAS support on the MIPS machine. This may actually be true depending on what model of MIPS it is --- IIRC, there were no userland synchronization instructions in MIPS-1. If it's MIPS-2 architecture, then the problem is we don't know what to test for. Additionally, postgres does *not* trap divide-by-zero errors, as Solaris and Linux do. 'select 1/0' causes the backend to crash. What's the signal you're getting? regards, tom lane ---(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] Irix 6.2, Postgres 7.3.1, some brokenness
When compiled with gcc, it does trap divide by zero. Alex ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] not using index for select min(...)
Tom, In the end, the only reasonable way to handle this kind of thing is to teach the query planner about it. Considering the small number of cases that are usefully optimizable (basically only MIN and MAX on a single table without any WHERE or GROUP clauses), and the ready availability of a SQL-level workaround, it strikes me as a very low-priority TODO item. Low priority for you, Tom. For some of us, it's one of the three most high-priority bugs in PostgreSQL. I constantly try to sell my clients, and potential clients, on PostgreSQL. And the two things that trip me up the most frequently are lack of replication and our dog-slow aggregates. I can usually sell Postgres on our strong points, but the aggregate issue is *always* a problem. And the slow aggregate problem comes up about twice a week on Performance and three times a week on SQL. Regardless of the technical reason, among MSSQL, Oracle, MySQL and PostgreSQL, we have the slowest performing simple aggregates. It's very well to explain this is due to our system of extensible aggregates, but if a potential Postgres developer doesn't want to create custom aggregates, but does want to use MIN() in a correlated subquery, then they will go to a different RDBMS. As I said before, I'm absolutely thrilled that you came up with a solution for COUNT(*) ... GROUP BY queries through Hash Aggregates. That's half the picture, now we need a way to speed up MIN() and MAX() for simple one-column expressions. While there is a workaround using ORDER BY LIMIT, this doesn't work for correlated subqueries or if one wants to evaluate the result of MAX() in the query. For example, the following query is not possible to workaround in PostgreSQL: select teams_desc.team_id, team_name, team_code, notes, min(teams_tree.treeno) as lnode, max(teams_tree.treeno) as rnode, parent.team_id as parent_id, count(*)/2 as tlevel from teams_desc JOIN teams_tree USING (team_id) join teams_tree parent ON parent.treeno teams_tree.treeno join teams_tree parents on parents.treeno teams_tree.treeno WHERE parent.treeno = (SELECT max(p1.treeno) from teams_tree p1 where p1.treeno teams_tree.treeno and exists (select treeno from teams_tree p2 where p2.treeno teams_tree.treeno and p2.team_id = p1.team_id)) AND EXISTS (select parents2.team_id from teams_tree parents2 where parents2.treeno teams_tree.treeno AND parents2.team_id = parents.team_id) group by teams_desc.team_id, team_name, team_code, notes, parent.team_id; While one would hardly expect the above query to be fast, it is dissapointing that it takes about 8-10 times as long to execute on PostgreSQL as on MSSQL, since MSSQL seems to be able to use indexes to evaluate all three MIN() and MAX() expressions. Further, assigning such a common query function to a Postgres-specific workaround hardly upholds our project's dedication to standards. The fact that we are telling new users to use non-SQL-compliant code to do a query type present in 90% of databases bothers me every single time I give a newbie that advice. It still seems to me that if a query's WHERE expression can be evaluated using an index, then any related MIN() or MAX() expression should be evaluable using an index. That is, if you are selecting: SELECT MAX(team_id) FROM teams WHERE team_id BETWEEN 100 and 200; ... with an index on team_id then this entire query should be able to return trough an index scan. We've discussed the particular planner problems this presents for PostgreSQL, but I still believe that these are solvable ... and moreover, that we *need* to solve them if we're going to be competitive with other SQL RDBMSes. I do realize that it's my job to find something to do about this issue since I'm the one so worked up about it. What I'm concerned about is the possibility of having any idea or fix I come up with dismissed out of hand because it's a low-priority todo. Please add up the questions and complaints of the users on SQL, NOVICE, and PERFORMANCE ... I know you read them. Thanks for reading, Tom. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] not using index for select min(...)
Josh Berkus [EMAIL PROTECTED] writes: For example, the following query is not possible to workaround in PostgreSQL: select teams_desc.team_id, team_name, team_code, notes, min(teams_tree.treeno) as lnode, max(teams_tree.treeno) as rnode, parent.team_id as parent_id, count(*)/2 as tlevel from teams_desc JOIN teams_tree USING (team_id) join teams_tree parent ON parent.treeno teams_tree.treeno join teams_tree parents on parents.treeno teams_tree.treeno WHERE parent.treeno = (SELECT max(p1.treeno) from teams_tree p1 where p1.treeno teams_tree.treeno and exists (select treeno from teams_tree p2 where p2.treeno teams_tree.treeno and p2.team_id = p1.team_id)) AND EXISTS (select parents2.team_id from teams_tree parents2 where parents2.treeno teams_tree.treeno AND parents2.team_id = parents.team_id) group by teams_desc.team_id, team_name, team_code, notes, parent.team_id; While one would hardly expect the above query to be fast, it is dissapointing that it takes about 8-10 times as long to execute on PostgreSQL as on MSSQL, since MSSQL seems to be able to use indexes to evaluate all three MIN() and MAX() expressions. I think you are leaping to conclusions about why there's a speed difference. Or maybe I'm too dumb to see how an index could be used to speed these min/max operations --- but I don't see that one would be useful. Certainly not an index on treeno alone. Would you care to explain exactly how it's done? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Interactive Documentation - how do you want it to
On Sun, 2003-02-02 at 15:22, Dave Page wrote: - Each comment attaches only to the page name, version of the page to which it was submitted *and* subsequent versions (this is the current behaviour). - Each comment should attach to the page name to which it was submitted regardless of the version. IMHO either one of these, considering below... 2) Bearing in mind your answer to the previous question, should all the comments be deleted when useful examples have been merged into the main documents (remember that the definition of 'useful' may vary), or should we only remove the 'junk' ones? Once the comment's suggestion has been incorporated and the docs updated, I think it should be removed. Just like in the rest of the documentation, there's no point presenting duplicate content to the user, so we should only keep the idocs comments that are still relevant. The same goes for comments that have no value (e.g. support requests). Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [PERFORM] not using index for select min(...)
Tom, I think you are leaping to conclusions about why there's a speed difference. Or maybe I'm too dumb to see how an index could be used to speed these min/max operations --- but I don't see that one would be useful. Certainly not an index on treeno alone. Would you care to explain exactly how it's done? If I knew that, I'd have proposed a patch already, yes? I'm working on it. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Interactive Documentation - how do you want it towork?
-Original Message- From: Neil Conway [mailto:[EMAIL PROTECTED]] Sent: 02 February 2003 20:52 To: Dave Page Cc: PostgreSQL Hackers Subject: Re: [HACKERS] Interactive Documentation - how do you want it towork? 2) Bearing in mind your answer to the previous question, should all the comments be deleted when useful examples have been merged into the main documents (remember that the definition of 'useful' may vary), or should we only remove the 'junk' ones? Once the comment's suggestion has been incorporated and the docs updated, I think it should be removed. Just like in the rest of the documentation, there's no point presenting duplicate content to the user, so we should only keep the idocs comments that are still relevant. The same goes for comments that have no value (e.g. support requests). My concern here is that what (for example) Bruce decides is not a useful addition to the docs themselves, maybe something that would have helped me with some bizarre problem. If we dump *all* the docs after they have been merged then I might lose that helpful tip. Also, and perhaps more importantly, the comments will be merged into a *future* version. If I am running 7.2, I'm going to look at the 7.2 docs, not 7.3. Regards, Dave. ---(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] On file locking
Curt Sampson [EMAIL PROTECTED] wrote: On Sun, 2 Feb 2003, Tom Lane wrote: This all doesn't look good for using file locks in the way I had in mind :-( ... but considering that all these man pages seem pretty vague, maybe some direct experimentation is called for. Definitely. I wonder about the NetBSD manpage quotes in the post you followed up to, given that last time I checked flock() was implmented, in the kernel, using fcntl(). Either that's changed, or the manpages are unclear or lying. Using the same kernel code != same semantics. I think the NetBSD manual pages are trying to say that it's safe to have lockf(), fcntl(), and flock() locking playing together. That needn't be the case on all operating systems and the standards don't require it. This has been my experience in the past; locking semantics are subtle and unclear enough that you really need to test for exactly what you want at build time on every system, and you've got to do this testing on the filesystem you intend to put the locks on. What he said ... Giles ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Interactive Documentation - how do you want it to
On 2 Feb 2003, Neil Conway wrote: On Sun, 2003-02-02 at 15:22, Dave Page wrote: - Each comment attaches only to the page name, version of the page to which it was submitted *and* subsequent versions (this is the current behaviour). - Each comment should attach to the page name to which it was submitted regardless of the version. IMHO either one of these, considering below... Agreed. 2) Bearing in mind your answer to the previous question, should all the comments be deleted when useful examples have been merged into the main documents (remember that the definition of 'useful' may vary), or should we only remove the 'junk' ones? Once the comment's suggestion has been incorporated and the docs updated, I think it should be removed. Just like in the rest of the documentation, there's no point presenting duplicate content to the user, so we should only keep the idocs comments that are still relevant. The same goes for comments that have no value (e.g. support requests). I do not think that useful comments should be deleted, even when the suggestions there in are incorporated into documentation. Instead, they (the user comments) should be marked as being incorporated into documentation of release X.Y.Z. I think this is more useful because 1) it provides some credit to user who provided the example; and 2) it gives readers a user perspective on the problem which they can compare to the documentation -- the language of documentation can sometimes be too brief. While we're talking about modifications to idocs, why not have a rating system for the usefulness of a comment. Also, how about some obfuscation of the email addresses included with user comments? Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Interactive Documentation - how do you want it to
While we're talking about modifications to idocs, why not have a rating system for the usefulness of a comment. Comment ratings could be useful if the rating is tied to a doc version. A very useful 7.1 comment may be a little antiquated for 7.3. This would solve almost all of the issues if comments are ordered by rating. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Linux.conf.au 2003 Report
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Gavin Sherry wrote: I don't think we should listen on IPv6 just because it is supported. It should be a configuration variable: tcpip_socket = true ipv6 = true We had a huge discussion on this. I think you were away for it. You can control what you listen on by modifying pg_hba.conf. Can you actually control whether the postmaster is listening by modifying pg_hba.conf? I don't think so. Good question. I assumed if you removed the IPv6 local address that a local IPv6 wouldn't work, and that you need to specify an IPv6 remote host to have it be accepted. Yes, it is still listening on Ipv6, but no one can get in except localhost by default, so I don't see the issue. I think I was the one who talked us into assuming that ipv4 and ipv6 should be treated as a single protocol. But some people have since made pretty good cases that it's better to regard them as separate protocols. Perhaps we should rethink that decision. Sure. -- 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] pg_hba.conf hostmask.
Kurt Roeckx wrote: Currently in pg_hba.conf you specify the ip addresses that can connect with 2 fields: the ip address and the mask. What do you think about changing it to ip address/mask? Where mask can be both the current mask, or the prefix length. It's so much handier to use, especially for ipv6. Yes, some have asked about this. My understanding was that CIDR (host/len) was mostly for networks, while hostname/mask was for hosts. Now, you can specify hosts using /32, but is is unusual? Maybe not. We basically have columns in pg_hba.conf that can specify either hosts or networks, so I suppose either should work. One neat trick would be to allow both, and I think I can easily code that up. If you specify a '/' and value after the host address, you don't use a netmask value. How is that? -- 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] Interactive Documentation - how do you want it to work?
Yes, please delete the old comments. We want to merge as many in as we can, and remove the rest. --- Dave Page wrote: As you may have noticed we have recently revamped the Interactive Documentation on the website (http://www.postgresql.org/docs). This has raised a couple of questions about how the idocs should work, so I'd like to get some votes on the following 2 issues: 1) How should comments be linked to document sets?: - Each comment attaches only to the page name version of the page to which it was submitted. - Each comment attaches only to the page name, version of the page to which it was submitted *and* subsequent versions (this is the current behaviour). - Each comment should attach to the page name to which it was submitted regardless of the version. 2) Bearing in mind your answer to the previous question, should all the comments be deleted when useful examples have been merged into the main documents (remember that the definition of 'useful' may vary), or should we only remove the 'junk' ones? - Yes - No Thanks, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Interactive Documentation - how do you want it to
Folks, we want organized documentation, not cudos to commentors or something that is so large that people have to wade through the comments to see if something is interesting. The focus is the docs, and the comments are only there to improve the docs. They are there for no other reason. --- Gavin Sherry wrote: On 2 Feb 2003, Neil Conway wrote: On Sun, 2003-02-02 at 15:22, Dave Page wrote: - Each comment attaches only to the page name, version of the page to which it was submitted *and* subsequent versions (this is the current behaviour). - Each comment should attach to the page name to which it was submitted regardless of the version. IMHO either one of these, considering below... Agreed. 2) Bearing in mind your answer to the previous question, should all the comments be deleted when useful examples have been merged into the main documents (remember that the definition of 'useful' may vary), or should we only remove the 'junk' ones? Once the comment's suggestion has been incorporated and the docs updated, I think it should be removed. Just like in the rest of the documentation, there's no point presenting duplicate content to the user, so we should only keep the idocs comments that are still relevant. The same goes for comments that have no value (e.g. support requests). I do not think that useful comments should be deleted, even when the suggestions there in are incorporated into documentation. Instead, they (the user comments) should be marked as being incorporated into documentation of release X.Y.Z. I think this is more useful because 1) it provides some credit to user who provided the example; and 2) it gives readers a user perspective on the problem which they can compare to the documentation -- the language of documentation can sometimes be too brief. While we're talking about modifications to idocs, why not have a rating system for the usefulness of a comment. Also, how about some obfuscation of the email addresses included with user comments? Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go 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 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] Interactive Documentation - how do you want it towork?
Dave Page wrote: My concern here is that what (for example) Bruce decides is not a useful addition to the docs themselves, maybe something that would have helped me with some bizarre problem. If we dump *all* the docs after they have been merged then I might lose that helpful tip. Also, and perhaps more importantly, the comments will be merged into a *future* version. If I am running 7.2, I'm going to look at the 7.2 docs, not 7.3. We have already had several eyes look at the comment, so I am fairly certain that there isn't anything useful. If there is, someone else will make the same comment, and we will catch it. Ideally, we would have no comments because it would all be in the docs. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Interactive Documentation - how do you want it towork?
I don't think I was clear before. When someone is looking at the interactive docs, I would like them to say, Oh, there's a comment. I better read that in case it will help me. If we have old comments, their special value becomes diminished. That's why I think they should be removed as they are reviewed. --- Dave Page wrote: -Original Message- From: Neil Conway [mailto:[EMAIL PROTECTED]] Sent: 02 February 2003 20:52 To: Dave Page Cc: PostgreSQL Hackers Subject: Re: [HACKERS] Interactive Documentation - how do you want it towork? 2) Bearing in mind your answer to the previous question, should all the comments be deleted when useful examples have been merged into the main documents (remember that the definition of 'useful' may vary), or should we only remove the 'junk' ones? Once the comment's suggestion has been incorporated and the docs updated, I think it should be removed. Just like in the rest of the documentation, there's no point presenting duplicate content to the user, so we should only keep the idocs comments that are still relevant. The same goes for comments that have no value (e.g. support requests). My concern here is that what (for example) Bruce decides is not a useful addition to the docs themselves, maybe something that would have helped me with some bizarre problem. If we dump *all* the docs after they have been merged then I might lose that helpful tip. Also, and perhaps more importantly, the comments will be merged into a *future* version. If I am running 7.2, I'm going to look at the 7.2 docs, not 7.3. Regards, Dave. ---(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 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] pg_hba.conf hostmask.
On Sun, 2 Feb 2003, Bruce Momjian wrote: Yes, some have asked about this. My understanding was that CIDR (host/len) was mostly for networks, while hostname/mask was for hosts. Now, you can specify hosts using /32, but is is unusual? Maybe not. Typically, if you have something like an access list where you're specifying hosts or networks, you default the netmask to /32 if it's not supplied. However, if we're going to maintain backward compatability with the old format (i.e., using a separately specified netmask in the next column if no slash is present in the address column) we can't do that. Personally, I'm all for breaking backwards compatability (as I usually am :-)) but could quite easily live with specifying all most hosts as n.n.n.n/32 forever into the future, too. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Last call for 7.3.2
Tom Lane wrote: The plan for 7.3.2 release is for Marc to wrap the tarball tomorrow and announce on Tuesday. I have already stamped the version number and updated the release history in CVS, but is there anyone out there with last-minute fixes? In particular, is there anything that needs to be done to update the pre-built documentation that will go into the tarball? I'm still quite unclear on what our build process for that is ... Alex Avriette (CC'd) mentioned yesterday that he's generated patches to make sure 7.3.x works on IRIX, as presently it won't compile with gcc. Have prodded him to submit them _now_ for review if possible. If not, then heck, we tried. :) Regards and best wishes, Justin Clift regards, tom lane -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(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] COPY as non super user
You can't because the backend opens that file as the super-user. You can use \copy in psql as an normal user, though. --- Jaume Teixi wrote: how should I use COPY arti FROM 'ARTI.txt' USING DELIMITERS '|' as normal user ? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- 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/users-lounge/docs/faq.html