Re: [HACKERS] Bug about column references within subqueries used in selects
I don't think so...the columns of update_test are visible to the scalar subquery...that way you can use fields from 'a' to filter the subquery... select a, (select y from supdate_test where x = a) from update_test; Yes this is fine, but in select columnname from tablename using column references of the other involved table is what I am objecting to. There's nothing here to object to, the system is acting correctly. Your column name b is ambiguous, and the system takes the column b that exists, rather than returning an error on a column that doesn't exist. If you were explicit in your column name, you would get an error: =# select a, (select supdate_test.b from supdate_test) from update_test; ERROR: No such attribute supdate_test.b Regards, Paul ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
Tom Lane wrote: I think that the easiest fix might be to not remove no-longer-used segment files during a truncate, but simply reduce them to zero size rather than delete them. Then any open file pointers aren't invalidated. The only alternative I can see is to invent some new signaling mechanism to force closure of open files, but that seems ugly, complex, and perhaps subject to race conditions. Setting the files to zero size seems to make sense all around, as when the bgwriter wants to use one of those segments it can 'figure out' that there's nothing in the file and it should start at offset zero. Regards, Paul Bort ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Reducing data type space usage
Gregory Stark writes: Tom Lane [EMAIL PROTECTED] writes: There isn't if you want the type to also handle long strings. But what if we restrict it to short strings? See my message just now. Then it seems like it imposes a pretty hefty burden on the user. But there are a lot of places where it wins: - single byte for a multi-state flag - hex representation of a hash (like SHA-1) - part numbers - lots of fields imported from legacy systems - ZIP/Postal codes And for all of those you can decisively say at design time that 127 characters is an OK limit. +1 for Bruce/Tom's idea. Regards, Paul Bort ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [Pgsqlrpms-hackers] [HACKERS] Safer auto-initdb for RPM init
Am Freitag, 25. August 2006 16:31 schrieb Reinhard Max: But shouldn't mountpoints always have 000 permissions to prevent writing into the directory as long as nothing is mounted to it? That's an interesting point, but in practice nobody does that. And we're trying to defend exactly against the case where someone has set up a mount point manually. It had never occurred to me, but I'm definitely going to start doing it now. So it will be in practice, at least around here. Regards, Paul Bort ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] BugTracker (Was: Re: 8.2 features status)
Kenneth Marshall wrote: RT is easy to setup/configure/use and works well with PostgreSQL as the backend. RT works with Postgres, but I wouldn't say well. All queries in RT are generated by a query generator due to a naive obsession with database independance. They've achieved database independance at the cost of all the queries being brain-dead. Fixing the query generator would be a pretty big job. We use RT with PostgreSQL for all internal IT requests and development/support/doc tasks on a couple products, and there's never been a problem. Are the queries optimal? no. The alternative might have been MySQL-only, and that would be worse. I can't really give a fair estimate on performance, because I'm running it on a PIII at 800MHz with several other things as well. But it's fast enough that I'm not screaming for a hardware upgrade. Regards, Paul Bort ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] GUC with units, details
Peter Eisentraut wrote: I have committed it using the 1024 multiplier, but if you want to propose changing all uses of kB, MB, and GB in PostgreSQL to the other system, now would be the time to do it. I think it would be a good idea. I know I don't have time to do it for 8.2. I get the feeling that there isn't enough interest on -hackers that anyone else is likely to do this, so I guess it waits until later. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GUC with units, details
Peter Eisentraut wrote: This consideration would become much more interesting if *any* software product actually made use of this newer proposed convention, but so far I haven't seen one yet. So we'll look at it when Oracle does it? I think we should be leading this charge, rather than following. Regards, Paul Bort ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GUC with units, details
Peter Eisentraut wrote: I didn't say Oracle, I said anyone. It could be Microsoft or Samba or Red Hat or NetBSD or my VoIP phone. OK, I did some further digging, and (http://members.optus.net/alexey/prefBin.xhtml) has a list at the end of the page of software that the author claims use the standard. From that list: Azureus - Has this (http://www.azureuswiki.com/index.php/Data_units) entry in the FAQ. All of the screen shots show 'kB/s'. but since they're referring to bandwidth, it would be reasonable to conclude that they intend decimal units. Lynx - The documentation (http://lynx.isc.org/current/lynx2-8-6/lynx_help/Lynx_users_guide.html) consistently uses KiB instead of KB. FreeDOS-32 - Their standards page (http://freedos-32.sourceforge.net/showdoc.php?page=standards) states that they comply with this standard. Regards, Paul Bort ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Better name/syntax for online index creation
Gregory Start wrote: Fwiw a few data points: MSSQL uses (WITH ONLINE=ON) much like we and Oracle use ONLINE tacked on to the end of the create index command. Where did you find this? I thought my MSDN-foo was pretty good, and I didn't find this when searched a couple days ago. Regards, Paul Bort ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GUC with units, details
Peter Eisentraut wrote: I'd imagine that one of the first things someone will want to try is something like SET work_mem TO '10MB', which will fail or misbehave because 1000 bytes do not divide up into chunks of 1024 bytes. Who wants to explain to users that they have to write '10MiB'? How about this: INFO: Your setting was converted to IEC standard binary units. Use KiB, MiB, and GiB to avoid this warning. Since about forever, PostgreSQL has used kB, MB, GB to describe memory allocation. If we want to change that, we ought to do it across the board. But that's a big board. The standard hasn't been around forever; some incarnation of PostgreSQL certainly pre-dates it. But it was created to reduce confusion between binary and decimal units. The Linux kernel changed to the standard years ago. And that's just a few more lines of code than PostgreSQL. ( http://kerneltrap.org/node/340 and others ) Regards, Paul Bort ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] GUC with units, details
Peter Eisentraut politely corrected: For your entertainment, here are the usage numbers from the linux-2.6.17 kernel: kilobyte (-i) 82 kibibyte (-i) 2 megabyte (-i) 98 mebibyte (-i) 0 gigabyte (-i) 32 gibibyte (-i) 0 KB1151 kB407 KiB 181 MB3830 MiB 298 GB815 GiB 17 Thanks for the info. I had seen several articles on it, and shot my mouth off without double-checking. My apologies. I still think it would be a good idea to use the standard, and that this is an opportunity to do so. Regards, Paul Bort ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] GUC with units, details
Martijn van Oosterhout wrote: How about this: INFO: Your setting was converted to IEC standard binary units. Use KiB, MiB, and GiB to avoid this warning. That's silly. If you're going to treat KB as 1024 bytes anyway, complaining about it is just being pedantic. But after a version or two with warnings, we have grounds to make it an error. I'd rather just go with the standard from day 1 and reject decimal units where they don't make sense, but that seems unlikely. The thing is, most memory sizes in postgres need to be some multiple of a page size. You can't have a shared buffers of exactly 10 bytes, while 102400 bytes is possible. When someone has a GB of memory, they really mean a GiB, but no-one bothers to correct them. And hard drives are just the opposite: a 250GB drive does not have 268,435,456,000 bytes of unformatted space. Is there anywhere in postgres where using K=1000 would be significantly clearer than K=1024? If the unit for a setting is pages, then a value of '1K' could cause some confusion as to whether that's 1,000 or 1,024 pages. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Better name/syntax for online index creation
Tom Lane wrote: psql could actually tell these apart if it worked just a bit harder. CLUSTER with no arguments is the one case, CLUSTER with anything after it is the other. Not sure why we couldn't be bothered to get that right in psql the first time :-(. Should this go on the to-do list? Regards, Paul Bort ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Adding a pgbench run to buildfarm
Jim Nasby wrote: Why is it being hard-coded? I think it makes a lot more sense to allow pg_bench options to be specified in the buildfarm config. Even better yet would be specifying them on the command line, which would allow members to run a more rigorous test once a day/week (I'm thinking one that might take 30 minutes, which could well ferret out some issues that a simple 5 minute test won't). They absolutely won't be hard-coded. I'm asking for values to use as defaults in the config file. Also allowing command-line parameters is interesting, but I think we should wait on it until the initial version is in place. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] GUC with units, details
Peter Eisentraut wrote: Memory units are kB, MB, GB. The factor is 1024. Then shouldn't the factor be 1000? If the factor is 1024, then the units should be KiB, MiB, GiB per IEEE 1541 (http://en.wikipedia.org/wiki/IEEE_1541) and others. I'm not trying to be pedantic, but the general approach with -hackers seems to be towards compliance where practical. Regards, Paul Bort ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Adding a pgbench run to buildfarm
Andrew Dunstan wrote: We are really not going to go in this direction. If you want ideal performance tests then a heterogenous distributed collection of autonomous systems like buildfarm is not what you want. You are going to have to live with the fatc that there will be occasional, possibly even frequent, blips in the data due to other activity on the machine. If you want tightly controlled or very heavy load testing this is the wrong vehicle. You might think that what that leaves us is not worth having - the consensus in Toronto seemed to be that it is worth having, which is why it is being pursued. I wasn't at the conference, but the impression I'm under is that the point of this isn't to catch a change that causes a 1% slowdown; the point is to catch much larger problems, probably 20% slowdown or more. Given the concerns about running this on machines that don't have a lot of CPU and disk to spare, should it ship disabled? Andrew, what do you think of pgbench reports shipping separately? I have no idea how the server end is set up, so I don't know how much of a pain that would be. Regards, Paul Bort P.S. My current thought for settings is scaling factor 10, users 5, transactions 1000. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Better name/syntax for online index creation
Greg Stark asked: I know Oracle calls this online index builds. In fact it works similarly with a single keyword online tacked on near the end of the create index statement. Anyone know what MSSQL or DB2 call it? I have to live with MSSQL at work, and I didn't remember anything like this, so I looked up the syntax for CREATE INDEX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlre f/ts_tsqlcon_6lyk.asp), and I can't find anything that gives the user control over this. The closest is this note in the remarks: Backup and CREATE INDEX operations do not block each other. If a backup is in progress, index is created in a fully logged mode, which may require extra log space. It sounds to me like they fall back to 'online' index creation if a backup is in progress, but give the user no control over it. I also looked in the settings and didn't see anything relevant. Regards, Paul Bort ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Adding a pgbench run to buildfarm
-hackers, With help from Andrew Dunstan, I'm adding the ability to do a pgbench run after all of the other tests during a buildfarm run. Andrew said I should solicit opinions as to what parameters to use. A cursory search through the archives led me to pick a scaling factor of 10, 5 users, and 100 transactions. All of these will be adjustable using the build-farm.conf mechanism already in place. Comments? Suggestions? Regards, Paul Bort ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Adding a pgbench run to buildfarm
100 transactions seems barely enough to get through startup transients. Maybe 1000 would be good. OK. I think the hard part of this is the reporting process. How do we track how performance varies over time? It doesn't seem very useful to compare different buildfarm members, but a longitudinal display of performance on a single buildfarm machine over time would be cool. (I'm still missing Mark Wong's daily OSDL performance reports :-() I was thinking that the output from pgbench would be sent back to the server and stored somewhere for later analysis. Actually the $64 question here is whether we trust pgbench as the standard performance test ... I think that it's what we've got today, and if tomorrow it gets better, then the data we get from the buildfarm will improve similarly. Regards, Paul Bort ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_regress breaks on msys
Andrew Dunstan [EMAIL PROTECTED] writes: Well, we have a result, courtesy of a special run from Stefan: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=seahorsedt= 2006-07-19%2017:52:41 has: Command was: C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test /regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbu ild/HEAD/inst/bin/initdb -D C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/ regress/./tmp_check/data -L C:/msys/1.0/home/pgbuild/pgfarmbuild/HEAD/pgsql.804/src/test/ regress/./tmp_check/install/C:/msys/1.0/home/pgbuild/pgfarmbui ld/HEAD/inst/share/postgresql --noclean --no-locale ./log/initdb.log 21 The second C:/msys/1.0/ should not be in the path to initdb. Andrew's on to something, I think. Colons are verboten anywhere in a filename except position 2, right after a drive letter. The path to postgresql later in the command will also have problems. Regards, Paul Bort ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] automatic system info tool?
On UNIX systems uname may work pretty well. But I guess each system may have slightly different options. What'll probably happen is that you end up with a big if() statement testing $Config{osname} wtih each case having specific code to determine the specifics. But for that you need information. How do you get the currently running release of windows for example? If you can open a command shell you can get the OS version with the 'ver' command under Windows: C:\ver Microsoft Windows XP [Version 5.1.2600] C:\ This should work on 2000 or later. (Windows 2000 is 5.0.something.) Regards, Paul ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] automatic system info tool?
How do you do this from a program though. Under UNIX uname() is a function call as well as a program. It returns the os name, version, hostname and system type. Multiple methods (TIMTOWTDI) depending on what you want: my $verstring = `cmd.exe /c ver`; # or use Win32; my ($string, $major, $minor, $build, $id ) = Win32::GetOSVersion; The environment variables PROCESSOR_ARCHITECTURE and PROCESSOR_IDENTIFIER should provide the basic hardware information. Mind you, maybe perl provides emulation for uname? Not that I know of. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Three weeks left until feature freeze
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Peter Eisentraut Taking a step back here, I see two points in favor of including PL/Java or something like it into the main CVS: 1. Build farm support It seems that eventually one would like to have build farm support for many things. I can see build farm support being useful for the ODBC driver or Postgis, for instance. We need a better, more general solution for that. Does PL/Java really have to be in core to be tested in the build farm? Could the build farm code be enhanced to test non-core stuff? (I like the idea of a separate status 'light' for non-core.) 2. Help with PL API changes On the one hand, that seems great, but on the other hand, I see a lot of people disqualifying themselves from touching PL/Java code in any significant way because they don't know Java well enough. So I don't see this working in practice. Or at least, it's quite doubtful that the convenience gained in this area will outweigh any inconveniences coming from this move. I think that if the buildfarm could alert us that there's a problem with a PL when it happens, rather than discovering it way later, having the code in the core repository is less critical. Regarding the packagers who don't include non-core components that their users might like, would a README.distros help? It could suggest good things to include, where to find them, and tips for building. This could also distinguish the mature packages on pgFoundry from the ones that are not quite ready for prime time: when a package's maintainer(s) think it's ready for production, they could submit a patch to the README.distros that adds the package. (I'm not attached to the filename, it just seemed less confusing than README.packagers.) Regards, Paul ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Truncated tuples for tuple hash tables
Tom Lane said: To make use of a TruncatedTuple, we'd set up a temporary HeapTupleData struct with its t_data field pointing 16 bytes before the start of the TruncatedTuple. As long as the code using it never tries to access any of the missing fields (t_xmin through t_ctid), this would work exactly like a normal HeapTuple. This sounds like a security risk. What's the worst thing that could be in those 16 bytes, and could that be used to bite (sorry) us? If those 16 bytes could be user data in another tuple, there might be an attack there. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [CORE] GPL Source and Copyright Questions
so presumably this is only needed for old Cygwin versions. Can anyone say how old 1001 is and whether we still ought to care about it? IIRC, I've been on 1.5.x for at least three years. 1.0/1.1 seems to be around 2000/2001, based on a quick Google. So it's definitely older than PG 7.3. Regards, Paul Bort ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Rethinking stats communication mechanisms
* reader's read starts before and ends after writer's update: reader will certainly note a change in update counter. * reader's read starts before and ends within writer's update: reader will note a change in update counter. * reader's read starts within and ends after writer's update: reader will note a change in update counter. * reader's read starts within and ends within writer's update: reader will see update counter as odd. Am I missing anything? The only remaining concern would be the possibility of the reader thrashing because the writer is updating so often that the reader never gets the same counter twice. IIRC, the reader was only sampling, not trying to catch every entry, so that will help. But is it enough? Regards, Paul Bort ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Rethinking stats communication mechanisms
BTW, I think the writer would actually need to bump the counter twice, once before and once after it modifies its stats area. Else there's no way to detect that you've copied a partially-updated stats entry. Actually, neither of these ideas works: it's possible that the reader copies the entry between the two increments of the counter. Then, it won't see any reason to re-read, but nonetheless it has copied an inconsistent partially-modified entry. Anyone know a variant of this that really works? Here's a theory: If the counter is bumped to an odd number before modification, and an even number after it's done, then the reader will know it needs to re-read if the counter is an odd number. This might be assuming too much about what the writer knows about the current contents of the counter, but since it's per-back end, I think it would work. Regards, Paul Bort ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Compression and on-disk sorting
Compressed-filesystem extension (like e2compr, and I think either Fat or NTFS) can do that. Windows (NT/2000/XP) can compress individual directories and files under NTFS; new files in a compressed directory are compressed by default. So if the 'spill-to-disk' all happened in its own specific directory, it would be trivial to mark that directory for compression. I don't know enough Linux/Unix to know if it has similar capabilities. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Google SoC--Idea Request
Personally I would much rather see a tuning advisor tool in more general use than just provide small/medium/large config setting files. True dat. Maybe the SoC project here is just such a tuning advisor tool? Something that can run pgbench repeatedly, try different settings, and compare results. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Unresolved Win32 bug reports
Some of the SysInternals tools might be a start. ProcessExplorer provides information about processes: http://www.sysinternals.com/Utilities/ProcessExplorer.html DebugView shows Debugging output (not sure if PG uses this): http://www.sysinternals.com/Utilities/DebugView.html Also, I haven't used it, but this looks like the Windows equivalent of gdb: http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jim C. Nasby Sent: Thursday, April 20, 2006 4:14 PM To: Larry Rosenman Cc: Magnus Hagander; Martijn van Oosterhout; Bruce Momjian; PostgreSQL-development Subject: Re: [HACKERS] Unresolved Win32 bug reports On Thu, Apr 20, 2006 at 02:17:35PM -0500, Larry Rosenman wrote: It seems to hang up just fine on my XPSP2, PG 8.1.2 HTT box. :( LER I may have spoken too soon :( I took a look and in fact the machine was just disk bound, so it appears that either HT doesn't exhibit this behavior, or XP doesn't exhibit it (all the machines I produced the error on are running w2k3 server). I'll try and pin down better exactly what hardware/software will reproduce this. In the meantime, if anyone has any good info for getting a dump of one of these processes... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql by default
I wonder if Oracle ever recommended disabling PL/SQL (not to mention MS Transact-SQL)... Don't know abiout Oracle, but you can't disable Transact-SQL in SQL Server 7.0 or 2000 (don't know about 2003^h5) because Enterprise Manager and sp_help* require it. And +1 for not installing plpgsql by default. I just had to CREATELANG on 20-odd servers scattered across the US, and it was no big thing. Just rolled it out with the upgrade that needed it. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] Looking for a tool to * pg tables as ERDs
I'll second autodoc. Been using it with Docbook and Dia for over a year with good results. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Dunstan Sent: Thursday, February 23, 2006 12:00 PM To: Markus Schaber Cc: Ron Peacetree; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PERFORM] Looking for a tool to * pg tables as ERDs Markus Schaber wrote: Hi, Ron, Ron Peacetree wrote: Where * == {print | save to PDF | save to mumble format | display on screen} Anyone know of one? psql with fancy output formatting comes to my mind, or COPY table TO file SQL command. How on earth can either of these have to do with producing an ERD? postgresql_autodoc might help: http://pgfoundry.org/projects/autodoc/ cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] RFC: built-in historical query time profiling
Title: RE: [HACKERS] RFC: built-in historical query time profiling I see your point. The ugliness of log-parsing beckons. Maybe it would make sense to use a separate log server machine, where they could be written to a database without impacting production?
Re: [HACKERS] We are not following the spec for HAVING without GR
Title: RE: [HACKERS] We are not following the spec for HAVING without GROUP BY Would those of you with access to other DBMSes try this: snip Results for Microsoft SQL Server 2000 - 8.00.944 (Intel X86): --- (0 row(s) affected) --- 1 (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) --- (0 row(s) affected) --- 1 (1 row(s) affected) So it looks like MS is following the standard.
Re: [HACKERS] Raw size
Title: RE: [HACKERS] Raw size 990 * 2072 = 2,051,280 Bytes BUT after clustering triples according to an index on att1: snip 142 * 8 * 1024 = 1,163,264 Bytes Is there any compression or what? varchar means 'character varying'. What varies is the length. So a varchar(1000) with 'foo' in it only takes a few bytes ('foo' plus length info) instead of 1000 bytes. If you really want a fixed-length field, nchar or char should do what you want.
Re: [HACKERS] New form of index persistent reference
Title: RE: [HACKERS] New form of index persistent reference If that ID is the only thing you use to access that data, why not just store it in a flat file with fixed-length records? seek() (or your language's equivalent) is usually fast. If you need to drive that from within PostgreSQL, you would need an untrusted language to read the file, but you could also generate it from a table using a trigger. Or maybe use a serial column, an index on that column, and cluster the table on that index. It's more than one lookup, but not much with a Btree index. (Not sure if this is better than just using a serial and an index. http://www.postgresql.org/docs/8.0/interactive/sql-cluster.html says it isn't, if I read it correctly.) Then anytime there is a batch of updates to the table, re-cluster it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 10, 2005 11:22 AM To: pgsql-hackers@postgresql.org Subject: [HACKERS] New form of index persistent reference For about 5 years now, I have been using a text search engine that I wrote and maintain. In the beginning, I hacked up function mechanisms to return multiple value sets and columns. Then PostgreSQL aded setof and it is was cool. Then it was able to return a set of rows, which was even better. Lately, I have been thinking that a cool form of index would be some sort of persistent reference index. Like the old ISAM days of yore, a fixed number could point you right to the row that you want. I'm not sure if the persistent reference is a specific auto numbering column type or separate index structure or both. I asked the question how do you get a record without going through an index, the answer was CTID, which unfortunately changes when the row is updated. Now, what I want to brainstorm is some sort of persistent reference where the value is not algorithmically stored, maybe just an offset into a table. The number of operations should be about 1 per lookup. Imagine a dynamically growing array that has one slot per row. Every row is considered unique. Rows which are updated, their CTID is updated in the reference. (with vacuum?) Imagine something like this: create table foobar(id reference, name varchar, value varchar); select * from foobar where id = 100; The reference type has an implicit index that is basically a lookup table. On unique references where the reference value is fairly arbitrary, this would be a HUGE gain for direct lookups. There is no need for the NlogN of a tree. On the surface level, this would be a huge win for websites that use semi-fixed tables of data. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Escaping the ARC patent
Title: RE: [HACKERS] Escaping the ARC patent Just an idle thought, but each connection to the DB could add a fixed amount to some queueing parameter. The amount added to be set per backend, and the client could use a SET variable to adjust the standard amount for it's own backend. When the client dies/disconnects, the queueing parameter (whatever it is) would be reduced appropriately. Wouldn't that require a SIGHUP on the postmaster with every connection? (Because all of the backends need to know about the new buffer count) Wouldn't that be bad?
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
Title: RE: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED a) start from the user: Search for useroid in pg_auth_members.member For each returned role, search for that role in member column Repeat until all roles the useroid is in have been found [Note: This could possibly be done and stored per-user on connection, but it would mean we'd have to have a mechanism to update it when necessary, possibly instigated by the user, or just force them to reconnect ala unix group membership] Look through ACL list to see if the useroid has permission or if any of the roles found do. b) start from the ACL list: Search for each roleoid in pg_auth_members.role For each returned member, search for that member in role column Upon member == useroid match is found check for permission, if granted then stop, otherwise continue processing Has the advantage that the search stops once it's been determined that permission is there and doesn't require updating. If I may humbly suggest another option: c) Use tables for users, roles, and user x role as you already have (Or was a user's roles in an array? I forget) Add a fourth table (access?) with the PK (user, priv, role). Whenever a privilege is granted or revoked, for a user or a role, insert or delete the appropriate rows in the access table. This pre-loads all of the cost of maintaining the ACL and should reduce the effort of checking a particular privilege to an index seek. With this method, a user can be granted a privilege by more than one role, and if they are removed from one of those roles, the other still grants the privilege. The access table can also store the privileges that each role has by storing the role ID in the user ID column. I know that it makes for a potentially huge table, but it makes the model straightforward and reliable. Examples: Grant role 'foo' privilege 'bar': INSERT INTO access (user, priv, role ) VALUES ( 'foo', 'bar', 'foo' ); Grant user 'baz' role 'foo': INSERT INTO access ( user, priv, role ) SELECT 'baz', priv, role FROM access WHERE user = 'foo'; Remove user 'baz' from role 'foo': DELETE FROM access WHERE user = 'baz' AND role = 'foo'; Remove privilege 'bar' from role 'foo': DELETE FROM access WHERE priv = 'bar' AND role = 'foo'; -- Note that this automatically cleaned up all of the users, too. Grant privilege 'bar' to user 'baz' without a role involved: INSERT INTO access ( user, priv, role ) VALUES ( 'baz', 'bar', 'baz' ); Grant user 'postgres' privilege 'su' in a hard-to-revoke way: INSERT INTO access ( user, priv, role ) VALUES ( 'postgres', 'su', '' ); Check to see if user 'baz' has privilege 'bar': SELECT user, priv, role FROM access WHERE user = 'baz' AND priv = 'bar'; -- This even tells you the role(s) that grant the privilege. Inheritance from role to role can even be handled by repeating the inserts or deletes with appropriate roles. (This would even allow a role to inherit a privilege from multiple parent roles, and work correctly if it is revoked by one.)
Re: [HACKERS] Call for port reports
Title: RE: [HACKERS] Call for port reports Port report for Gentoo (www.gentoo.org) Linux: No errors. uname -a: Linux imgvmhost 2.4.26-gentoo-r3 #1 Tue Sep 7 14:20:02 EDT 2004 i686 Intel(R) Pentium(R) 4 CPU 2.40GHz GenuineIntel GNU/Linux gcc -v: gcc version 3.3.4 20040623 (Gentoo Linux 3.3.4-r1, ssp-3.3.2-2, pie-8.7.6) configure: ./configure --prefix=$mypg --enable-thread-safety --with-tcl --with-perl --with-python --with-krb5 --with-pam -with-openssl make check: == All 96 tests passed. == version: template1=# select version(); version PostgreSQL 8.0.0rc1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 20040623 (Gentoo Linux 3.3.4-r1, ssp-3.3.2-2, pie-8.7.6) (If there are other combinations of options anyone would like me to try, just let me know. I don't know which combinations are interesting, since the defaults work for my application.)
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Title: RE: [Testperf-general] Re: [HACKERS] ExclusiveLock From: Kenneth Marshall [mailto:[EMAIL PROTECTED]] [snip] The simplest idea I had was to pre-layout the WAL logs in a contiguous fashion on the disk. Solaris has this ability given appropriate FS parameters and we should be able to get close on most other OSes. Once that has happened, use something like the FSM map to show the allocated blocks. The CPU can keep track of its current disk rotational position (approx. is okay) then when we need to write a WAL block start writing at the next area that the disk head will be sweeping. Give it a little leaway for latency in the system and we should be able to get very low latency for the writes. Obviously, there would be wasted space but you could intersperse writes to the granularity of space overhead that you would like to see. As far as implementation, I was reading an interesting article that used a simple theoretical model to estimate disk head position to avoid latency. Ken, That's a neat idea, but I'm not sure how much good it will do. As bad as rotational latency is, seek time is worse. Pre-allocation isn't going to do much for rotational latency if the heads also have to seek back to the WAL. OTOH, pre-allocation could help two other performance aspects of the WAL: First, if the WAL was pre-allocated, steps could be taken (by the operator, based on their OS) to make the space allocated to the WAL contiguous. Statistics on how much WAL is needed in 24 hours would help with that sizing. This would reduce seeks involved in writing the WAL data. The other thing it would do is reduce seeks and metadata writes involved in extending WAL files. All of this is moot if the WAL doesn't have its own spindle(s). This almost leads back to the old-fashioned idea of using a raw partition, to avoid the overhead of the OS and file structure. Or I could be thoroughly demonstrating my complete lack of understanding of PostgreSQL internals. :-) Maybe I'll get a chance to try the flash drive WAL idea in the next couple of weeks. Need to see if the hardware guys have a spare flash drive I can abuse. Paul
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Title: RE: [Testperf-general] Re: [HACKERS] ExclusiveLock The impression I had was that disk drives no longer pay the slightest attention to interleave specs, because the logical model implied by the concept is too far removed from modern reality (on-disk buffering, variable numbers of sectors per track, transparently remapped bad sectors, yadda yadda). Entirely true. Interleave was an issue back when the controller wasn't fast enough to keep up with 3600 RPM disks, and is now completely obscured from the bus. I don't know if the ATA spec includes interleave control; I suspect it does not. And that's just at the hardware level ... who knows where the filesystem is putting your data, or what the kernel I/O scheduler is doing with your requests :-( Basically I see the TODO item as a blue-sky research topic, not something we have any idea how to implement. That doesn't mean it can't be on the TODO list ... I think that if we also take into consideration various hardware and software RAID configurations, this is just too far removed from the database level to be at all practical to throw code at. Perhaps this should be rewritten as a documentation change: recommendations about performance hardware? What we recommend for our highest volume customers (alas, on a proprietary RDBMS, and only x86) is something like this: - Because drive capacity is so huge now, choose faster drives over larger drives. 15K RPM isn't three times faster than 5400, but there is a noticable difference. - More spindles reduce delays even further. Mirroring allows reads to happen faster because they can come from either side of the mirror, and spanning reduces problems with rotational delays. - The ideal disk configuration that we recommend is a 14 drive chassis with a split backplane. Run each backplane to a separate channel on the controller, and mirror the channels. Use the first drive on each channel for the OS and swap, the second drive for transaction logs, and the remaining drives spanned (and already mirrored) for data. With a reasonable write cache on the controller, this has proven to be a pretty fast configuration despite a less than ideal engine. One other thought: How does static RAM compare to disk speed nowadays? A 1Gb flash drive might be reasonable for the WAL if it can keep up.
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Title: RE: [Testperf-general] Re: [HACKERS] ExclusiveLock From: Doug McNaught [mailto:[EMAIL PROTECTED]] Bort, Paul [EMAIL PROTECTED] writes: One other thought: How does static RAM compare to disk speed nowadays? A 1Gb flash drive might be reasonable for the WAL if it can keep up. Flash RAM wears out; it's not suitable for a continuously-updated application like WAL. -Doug But if it's even 2x faster than a disk, that might be worth wearing them out. Given that they have published write count limits, one could reasonably plan to replace the memory after half of that time and be comfortable with the lifecycle. I saw somewhere that even with continuous writes on USB 2.0, it would take about twelve years to exhaust the write life of a typical flash drive. Even an order-of-magnitude increase in throughput beyond that only calls for a new drive every year. (Or every six months if you're paranoid. If you're that paranoid, you can mirror them, too.) Whether USB 2.0 is fast enought for the WAL is a separate discussion.
Re: [HACKERS] psql \e broken again
Title: RE: [HACKERS] psql \e broken again From: Zeugswetter Andreas DAZ SD [mailto:[EMAIL PROTECTED]] I am not sure the test is valid, since %EDITOR% was used on the command line, which does it's own magic on quotes. Is there a command that would use the envvar EDITOR without putting it on the commandline ? We are talking about directly using the envvar from inside the program, no? I wasn't sure either, but a quick trudge through my Windows-installed software that has been ported from *nix didn't show any programs that attempted to preserve that aspect of their behavior. CVSNT at least attempted to invoke an external editor, so I tested with that. I like Kevin Brown's suggestion of writing out a temporary .txt file and 'executing' it. It will follow the principle of least suprise for Windows users. And it is suprisingly easy to wait for a child process with the NT API. WaitForSingleObjectEx() ( http://tinyurl.com/5d4tj ) will wait with a timeout, so the caller can handle other messages or update counters as needed. ( We usually timeout every 1000ms and repeat the call until it returns a non-timeout status. )
Re: [HACKERS] psql \e broken again
Title: RE: [HACKERS] psql \e broken again From: Peter Eisentraut [mailto:[EMAIL PROTECTED]] Is there a Windows port of the command-line cvs tool? That would be a good thing to compare with. The one that I see most often ( and use here ) is CVSGUI ( http://www.wincvs.org/ ), which includes a CVS command-line client despite the name. ( The command-line CVS might be from CVSNT, http://www.cvsnt.com/cvspro/ ). So I thought I'd try a few scenarios, since I have it installed: U:\sandbox\docbook REM DOUBLE-QUOTES IN COMMAND LINE: U:\sandbox\docbook set EDITOR=D:\Program Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe U:\sandbox\docbook cvs -d :sspi:[EMAIL PROTECTED]:/repo -e %EDITOR% commit -D xml_reindent.pl 'D:\Program' is not recognized as an internal or external command, operable program or batch file. cvs commit: warning: editor session failed U:\sandbox\docbook REM DOUBLE-QUOTES IN ENVIRONMENT VARIABLE: U:\sandbox\docbook set EDITOR=D:\Program Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe U:\sandbox\docbook cvs -d :sspi:[EMAIL PROTECTED]:/repo -e %EDITOR% commit -D xml_reindent.pl 'D:\Program' is not recognized as an internal or external command, operable program or batch file. cvs commit: warning: editor session failed U:\sandbox\docbook REM DOUBLE-QUOTES IN BOTH: U:\sandbox\docbook cvs -d :sspi:[EMAIL PROTECTED]:/repo -e %EDITOR% commit -D xml_reindent.pl Unknown command: `Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe' U:\sandbox\docbook REM SINGLE-QUOTES IN ENVIRONMENT VARIABLE: U:\sandbox\docbook set EDITOR='D:\Program Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe' U:\sandbox\docbook cvs -d :sspi:[EMAIL PROTECTED]:/repo -e %EDITOR% commit -D xml_reindent.pl Unknown command: `Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe'' U:\sandbox\docbook REM SINGLE-QUOTES IN COMMAND LINE: U:\sandbox\docbook set EDITOR=D:\Program Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe U:\sandbox\docbook cvs -d :sspi:[EMAIL PROTECTED]:/repo -e '%EDITOR%' commit -D xml_reindent.pl Unknown command: `Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe'' U:\sandbox\docbook REM SINGLE-QUOTES IN BOTH: U:\sandbox\docbook set EDITOR='D:\Program Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe' U:\sandbox\docbook cvs -d :sspi:[EMAIL PROTECTED]:/repo -e '%EDITOR%' commit -D xml_reindent.pl Unknown command: `Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe''' U:\sandbox\docbook REM BACKSLASH-ESCAPE SPACES: U:\sandbox\docbook set EDITOR=D:\Program\ Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe U:\sandbox\docbook cvs -d :sspi:[EMAIL PROTECTED]:/repo -e %EDITOR% commit -D xml_reindent.pl Unknown command: `Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe' U:\sandbox\docbook REM BACKSLASH-ESCAPE SPACES AND DOUBLE-QUOTE ON COMMAND LINE: U:\sandbox\docbook set EDITOR=D:\Program\ Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe U:\sandbox\docbook cvs -d :sspi:[EMAIL PROTECTED]:/repo -e %EDITOR% commit -D xml_reindent.pl 'D:\Program\' is not recognized as an internal or external command, operable program or batch file. cvs commit: warning: editor session failed U:\sandbox\docbook REM BACKSLASH-ESCAPE SPACES AND SINGLE-QUOTE ON COMMAND LINE: U:\sandbox\docbook set EDITOR=D:\Program\ Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe U:\sandbox\docbook cvs -d :sspi:[EMAIL PROTECTED]:/repo -e %EDITOR% commit -D xml_reindent.pl Unknown command: `Files\XEmacs\XEmacs-21.4.13\i586-pc-win32\XEmacs.exe'' -- The message is not recognized as an internal or external command... is from the operating system, so CVS is at least trying to launch an editor in those cases. So to answer your question, at least in part, there is no current good behavior to emulate. At least on this version of CVS: Concurrent Versions System (CVSNT) 2.0.51d (client/server) Copyright (c) 1989-2001 Brian Berliner, david d `zoo' zuhn, Jeff Polk, and other authors CVSNT version (Aug 19 2004) Copyright (c) 1999-2004 Tony Hoyle and others see http://www.cvsnt.org If you have any other combinations that might make sense, let me know, and I'll give it a try.
Re: [HACKERS] Nested xacts: looking for testers and review
Tom Lane wisely wrote: While we clearly want this functionality, I tend to agree with Barry that COMMIT IGNORE ABORT (and the other variants that have been floated) is a horrid, confusing name for it. I would suggest using END with some modifier, instead. Perhaps END [ WORK | TRANSACTION ] [ IGNORE ERRORS ] END doesn't so directly imply that you are trying to commit a failed transaction. Would it make more sense to specify at the time the optional subtransaction is committed that it is not critical to the completion of the outer transaction? BEGIN; BEGIN; DROP TABLE foo; COMMIT NON_CRITICAL; CREATE TABLE foo (i int); COMMIT; I don't 'get' the nested transaction code, so I don't know how horrible this would be to write. It just seemed more useful, because you could specify which sub-transactions are show stoppers, and which ones aren't. Or if I'm completely off base, please forgive my intrusion. Paul ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] serverlog function
Andreas wrote: AFAICS, we have some alternatives: - try to grab the currently created files/syslog/eventlog. Seems hard to do, because we'd depend on additional external tools. - redirect stderr to a postgresql.conf known file. Disadvantage: breaks piping. - maintain a sharedMem for the latest messages. Disadvantage: limited space, no access to older entries after postmaster restart. - additional log_destination file. Disadvantage: Yet Another File besides the redirected stderr, but this seems a minor problem. Another alternative would be to add code to the admin tool to get the log via scp or a similar method. IMHO PostgreSQL is doing the right thing here by using the OS logging, and breaking that isn't a good idea when there are other ways to solve the problem. ---(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] Email data type
From: Gaetano Mendola [mailto:[EMAIL PROTECTED] I think I have to discard also the addresses with last octet equal to 256. Any comments ? Any octet that contains a number less than 0 or greater than 255 should be suspect. Assuming you really meant 255: It would be perfectly legal for an entity on the internet to have a block of addresses with a subnet mask of less than 24 bits, which leads to legal addresses that end in 255. For example, if your company/university/black helicopter squad needed about 500 servers with direct presence, you might be assigned a block like 123.45.6.0/23. So the network address would be 123.45.6.0, the broadcast address would be 123.45.7.255, and everything in between, including 123.45.6.255 and 123.45.7.0, would be available for your servers. HTH, Paul ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Collaboration Tool Proposal
Janos, So far, all of the solutions that are being seriously considered seem to be free, open-source software. I can't find any indication on your site that this is software the PostgreSQL community can hack to bits as needed over the years. Even if it's free now, there's the possibility that it will later turn out to be a free straitjacket. Regards, Paul -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 1:19 PM To: [EMAIL PROTECTED] Subject: Re: [HACKERS] Collaboration Tool Proposal Hi, please look at CodeBeamer (www.intland.com) it has all featured you described and for selected open source projects is free now. It is a web based collaborative software development platform with -project tracking (dashboard) -tracker -document manager (sharing + versioning) -forum -cvs, Subversion and other SCM integration, GUI -code browsing, xref for C/C++ and Java -automated build Thanks, Janos ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ADMIN] Schema comparisons
Ordering the pg_dump output by name within classes instead of OID sounds good to me, too. Also, something that might be easier for comparing schemata between databases: rather than dumping the database, have you tried using PostgreSQL Autodoc (http://www.rbt.ca/autodoc/) which just outputs the schema in a variety of formats (including XML for dia and DocBook). It just seems that if you're only concerned with structure, and not content of user tables, this could at least shorten the amount of data to be compared, if not get you into a space where there's already a tool to do all the work (I don't know if there's an XML-smart diff.) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-www] Collaboration Tool Proposal
-Original Message- From: Greg Stark [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 12:17 AM To: [EMAIL PROTECTED] Subject: Re: [HACKERS] [pgsql-www] Collaboration Tool Proposal [...snip...] I might suggest again RT. It's open source and has serious commercial traction. The postgres port needs a lot of work for it to really catch up to the original MySQL implementation so most of the users are using it with MySQL. A second for considering RT. I've been using RT 3.0.6 for about five months now for our internal support and (closed-source) bug tracking, and can report that it works very smoothly with PostgreSQL. I had more problems with getting all the Perl dependencies lined up than anything else, but that was mostly my ignorance regarding big Perl apps and Apache. It also can accept tickets via web or e-mail, so using it would not require reducing the available methods for submitting bugs. ---(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] [pgsql-www] Collaboration Tool Proposal
My apologies, then! I was operating off of the statements of others, and the fact that the only RT impelementations I've used were running on MySQL. So, questions: 1) can you compare/contrast RT vs. BZ vs. Simplified bug-tracking, like GForge? I've used Bugzilla for searching for FOP issues, and a couple other places, and I find the RT search much more obvious. I can get what I want out of Bugzilla, but usually by creating a really broad search and sifting entries one at a time for likely candidates. The fact that an RT search is iterative is much more obvious, because the bottom of the search page lists all of the current criteria, and the box for adding new ones. Add or remove, and re-run the search. I like the trick that it does with mutually exclusive conditions: It assumes an 'or' between them. (eg, all tickets that are in state 'open' or 'closed'.) OTOH, Bugzilla tracks a whole pile more fields by default. I've taken to putting version numbers in the ticket subject in RT because for the small project here, it's easier than learning how to add a version field. (I haven't tried adding my own fields.) Both handle attachments and comments sanely. I don't know if BZ has an e-mail interface, but the one in RT has filled the basic needs here. (We haven't pushed the limits of the e-mail part.) I have never tried to install BZ. RT's install (RedHat 8.0, PostgreSQL 7.2.4 from RPMs) was straightforward once all the Perl modules were up to date. (All of the needed modules were available from CPAN.) I don't recall using any simplified bug tracking on-line, except maybe at ImageMagick.com, which seems to be more a forum or mailing list search, with no real tracking fields. 2) What help, if any, would we be able to get in supporting RT from the RT community? I'm afraid I have no idea what or where the larger RT community is. I know there's commercial support available from the author (whom I have no contact with), and I found the answers to my (self-created) problems during setup using Google. I found RT because of a (don't ban me, please ;-) discussion on SlashDot. (http://slashdot.org/article.pl?sid=03/10/06/1854211) There were a large number of proponents of RT there; their posts claimed years of use at many sites. I would be happy to lend my meager talents to setting it up for a trial, if that's where the group decides to go. But Josh made a good point off-list: are we trying to solve the problem of ticket/bug tracking, or community/collaboration in general? My $0.02: CVS handles the code, mailing lists handle the dialog, and a ticket/bug tracker keeps people from losing things. About all that leaves for the web site to do is advocate PostgreSQL (which I think it does nicely) and related projects, and provide some glue (like how to find the name of the other lists or projects to see what they're doing.) New tools or old, every day with PostgreSQL is a good day. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Allow backend to output result sets in XML
Please forgive me if this is silly, but if you wanted XML from the server, couldn't you just write a PL/Perl untrusted function that takes a SELECT statement as its parameter, and returns a single scalar containing the XML? - The XML:: modules in Perl help with the XML formatting - DBD::PgSPI could be handed the query as-is - No change to BE/FE or wire protocols - No impact on people who don't want it - Probably works across versions with minimal fuss Returning a simple XML structure with column names and rows should only take a few lines. (I'd write an example if I knew XML:: better.) I'll go back to lurking now. Thanks all for the great database! ---(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