Re: [HACKERS] [pgsql-hackers-win32] Time to close pgsql-cygwin?
Magnus Hagander schrieb: It occurs to me that there is no longer any great need to have a separate hackers list for win32 development. Perhaps we should close it down now and keep all development on -hackers? I also think this is a good idea. The number of win32 only issues of -hacker level is significantly smaller now, and having to bounce people between the lists can be kind of annoying... I believe we should close pgsql-cygwin also. The cygwin users should ask at the official cygwin list as described in the README and CYGWIN announcements, not at the pgsql-cygwin list. Most problems are cygwin specific, others are carried in the FAQ_README and the seperate /usr/share/doc/Cygwin/postgresql-x.x.x.README If so, I'll write a documentation patch. -- Reini Urban http://phpwiki.org/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 64-bit API for large objects
My company has written a 64-bit large object API, extending the postgresql server to be able to read/write/seek/tell/open/close objects larger than 2GB. If the hackers community considers this valuable, we will submit the changes back for the rest of the community to share. From one of my programmers, Jeremy Drake: I tested this out on my box with a 4gb dvd iso image, and it appears to work correctly. The test code I found for large object things does not really seem to exercise the api very well though. And the regression tests do not seem to even touch large objects (they all still pass after this change). Mark, can you take a look at this and make sure I haven't broken anything too obviously? I wrote it into the same file as the large object code, since that file has some static stuff for caching things which I would like to share. I opted to add new functions tell64 and seek64 rather than changing the existing ones for backwards compatibility. I plugged them into the pg_proc catalog, but everything in that file has an explicit OID, and I do not feel comfortable (yet) grabbing up OIDs for stuff. So I set them to an OID of zero, which means the scripts will assign it one which is not used (in the range 1-something). Since the convention is that such functions have explicit assigned OIDs, it would probably be required to get real ones if this were ever to be submitted back. Also, in the libpq stuff, at the moment I have it fail if it cannot find the seek64 or tell64 functions. It may be best to have it work as long as you don't try to call them, in order to preserve backwards compatibility with other server versions. If you think this is a reasonable patch, it might be nice to send it to them, be a good neighbor and all that... ---(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] Spinlocks, yet again: analysis and proposed patches
On Sat, Sep 17, 2005 at 01:40:28AM -0400, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: On Sat, 17 Sep 2005, Tom Lane wrote: It'd be real interesting to see comparable numbers from some non-Linux kernels, particularly commercial systems like Solaris. Did you see the Solaris results I posted? Are you speaking of http://archives.postgresql.org/pgsql-hackers/2005-09/msg00715.php ? That doesn't seem directly relevant to the point, because it's for a 2-CPU machine; so there's no way to run a test case that uses more than one but less than all the processors. In either the one or all cases, performance ought to be pretty stable regardless of whether the kernel understands about any processor asymmetries that may exist in the hardware. Not to mention that I don't know of any asymmetries in a dual SPARC anyway. We really need to test this on comparable hardware, which I guess means we need Solaris/x86 on something with hyperthreading or known NUMA asymmetry. I have access to a 4-way Opteron 852 running Solaris 10. What patches would you like me to test? -- 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 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] statement_timeout logging
On Fri, 2005-09-16 at 20:48 -0400, Bruce Momjian wrote: We can go three ways. We can add a boolean GUC to control printing of the query during a timeout, but that seems like overkill. We can add a new level for log_min_error_statement that is just above error, but that seems confusing. I think the right solution would be to allow log_min_duration_statement to work for canceled queries. Right now, log_min_duration_statement doesn't work for canceled queries because the query never completes to give a final duration and hit the test code. Should that be fixed now or added to the TODO list? The last one seems the right way to go. So, reformat the message at statement_timeout, so that the log looks exactly like log_min_duration_statement: e.g. LOG: statement_timeout has been activated to cancel statement LOG: duration 1625652ms statement SELECT * from bigOne LOG: query has been cancelled by user action Perhaps we should change the message from kill() to be statement rather than query also... I'd vote fix now, but I guess that seems to be becoming a regular viewpoint from me. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Does anybody use ORDER BY x USING y?
Hi, PostgreSQL's grammer allows you to specify the operator to sort with in the ORDER BY clause. Various bits of the backend support this feature, yet it appears to partially undocumented. I can't find it in the ORDER BY [1] section but there is a paragraph on it under the SELECT documentation [2]. I'm asking because SQL COLLATE support is really doing something similar. I was wondering if instead of adding something in parallel just replace sortop with collateid. This means all the code relating to pathkeys won't need to change since we still use OIDs for the pathkeys, they're just not operator oids anymore. We can continue to support USING [op] as long as [op] is one of the GT or LT operators in the OPERATOR CLASS. This restriction may exist already, I can't tell. All we lose is the ability to say USING [arbitrary op]. Does anybody use this. Would people object to requiring the operator after USING to be part of an operator class? Have a nice day, [1] http://www.postgresql.org/docs/8.0/interactive/queries-order.html [2] http://www.postgresql.org/docs/8.0/interactive/sql-select.html -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp7TMawPFCRG.pgp Description: PGP signature
Re: [HACKERS] Does anybody use ORDER BY x USING y?
Martjin, We can continue to support USING [op] as long as [op] is one of the GT or LT operators in the OPERATOR CLASS. This restriction may exist already, I can't tell. All we lose is the ability to say USING [arbitrary op]. Does anybody use this. Would people object to requiring the operator after USING to be part of an operator class? Hmmm ... would this prevent the hackish workaround for case-insensitive sort? -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Does anybody use ORDER BY x USING y?
On Sun, Sep 18, 2005 at 12:34:10PM -0700, Josh Berkus wrote: All we lose is the ability to say USING [arbitrary op]. Does anybody use this. Would people object to requiring the operator after USING to be part of an operator class? Hmmm ... would this prevent the hackish workaround for case-insensitive sort? Err, which hackish workaround would that be? The right solution is citext which creates it's own operator class. This doesn't have anything to do with functional indexes either. I've been using Google to find any interesting use of the USING clause but havn't found any yet. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpQyeqL55uBv.pgp Description: PGP signature
Re: [HACKERS] Does anybody use ORDER BY x USING y?
Martijn van Oosterhout wrote: On Sun, Sep 18, 2005 at 12:34:10PM -0700, Josh Berkus wrote: All we lose is the ability to say USING [arbitrary op]. Does anybody use this. Would people object to requiring the operator after USING to be part of an operator class? Hmmm ... would this prevent the hackish workaround for case-insensitive sort? Err, which hackish workaround would that be? The right solution is citext which creates it's own operator class. This doesn't have anything to do with functional indexes either. Last time I looked it appeared to have significant limitations, and some considerable inefficiencies (e.g, copying the strings and folding them to canonical case on every comparison). I would certainly be extremely wary of just saying that's the solution. 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
Re: [HACKERS] Does anybody use ORDER BY x USING y?
Martijn van Oosterhout Wrote: All we lose is the ability to say USING [arbitrary op]. Does anybody use this. Would people object to requiring the operator after USING to be part of an operator class? Hmmm ... would this prevent the hackish workaround for case-insensitive sort? Err, which hackish workaround would that be? The right solution is citext which creates it's own operator class. This doesn't have anything to do with functional indexes either. I've been using Google to find any interesting use of the USING clause but havn't found any yet. I was actually of the impression that that was exacty what it was for: specifying what op(class) to use for the sort in case you wanted to use a non-default opclass for the type, and/or if the less-than operator wasn't called ''. ... John ---(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] Does anybody use ORDER BY x USING y?
On Sun, Sep 18, 2005 at 04:19:06PM -0400, Andrew Dunstan wrote: Err, which hackish workaround would that be? The right solution is citext which creates it's own operator class. This doesn't have anything to do with functional indexes either. Last time I looked it appeared to have significant limitations, and some considerable inefficiencies (e.g, copying the strings and folding them to canonical case on every comparison). I would certainly be extremely wary of just saying that's the solution. Ok, so citext has its limitations. Case-insensetive sort is hard [1]. My real question was, what was the solution he was referring to using the USING clause? [1] http://lafstern.org/matt/col2_new.pdf -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp8mAO26x7IC.pgp Description: PGP signature
Re: [HACKERS] Does anybody use ORDER BY x USING y?
On Mon, Sep 19, 2005 at 06:26:10AM +1000, John Hansen wrote: I was actually of the impression that that was exacty what it was for: specifying what op(class) to use for the sort in case you wanted to use a non-default opclass for the type, and/or if the less-than operator wasn't called ''. That's my thought. However, the code doesn't seem to restrict you to that so I was wondering if there was any other use out there that we should consider supporting... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpZSXRjLfg74.pgp Description: PGP signature
Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches
On Sun, 18 Sep 2005, Jim C. Nasby wrote: On Sat, Sep 17, 2005 at 01:40:28AM -0400, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: On Sat, 17 Sep 2005, Tom Lane wrote: It'd be real interesting to see comparable numbers from some non-Linux kernels, particularly commercial systems like Solaris. Did you see the Solaris results I posted? Are you speaking of http://archives.postgresql.org/pgsql-hackers/2005-09/msg00715.php ? That doesn't seem directly relevant to the point, because it's for a 2-CPU machine; so there's no way to run a test case that uses more than one but less than all the processors. In either the one or all cases, performance ought to be pretty stable regardless of whether the kernel understands about any processor asymmetries that may exist in the hardware. Not to mention that I don't know of any asymmetries in a dual SPARC anyway. We really need to test this on comparable hardware, which I guess means we need Solaris/x86 on something with hyperthreading or known NUMA asymmetry. I have access to a 4-way Opteron 852 running Solaris 10. What patches would you like me to test? These ones here: http://archives.postgresql.org/pgsql-hackers/2005-09/msg00566.php Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Does anybody use ORDER BY x USING y?
Martijn van Oosterhout kleptog@svana.org writes: On Mon, Sep 19, 2005 at 06:26:10AM +1000, John Hansen wrote: I was actually of the impression that that was exacty what it was for: specifying what op(class) to use for the sort in case you wanted to use a non-default opclass for the type, and/or if the less-than operator wasn't called ''. That's my thought. However, the code doesn't seem to restrict you to that so I was wondering if there was any other use out there that we should consider supporting... One of the half-baked ideas about operator classes that I mentioned a few days ago was to either redesign or reinterpret USING in a way that would make it easier to associate a btree opclass with a requested ordering. I'm not sure that we want to *require* there to be a btree opclass matching any ORDER BY request, but it's something to consider. (There are some examples in the regression tests of ORDER BY using operators that aren't in any btree opclass, but I'm not sure any of them represent useful real-world cases. In principle, if the operator represents a self-consistent ordering at all, then a btree opclass could be built with it. So it could be argued that we're just supporting programmer laziness to not require one.) Right now we use some heuristics to try to identify an opclass containing the mentioned operator, but this is pretty unreliable and would become more so if reverse-sort opclasses became standard equipment. Another thing that's flaky in the current treatment is the question of whether NULLs sort before or after ordinary values. We've essentially tried to force NULLs to sort high (as if they compare greater than all ordinary values), so that ASC and DESC orderings can be obtained from forward and backwards scans of an ordinary btree index. This is going to break entirely in the presence of reverse-sort opclasses --- given the current btree code, such an opclass would cause NULLs to appear to sort low. I suspect we have to bring out the NULL sort behavior as an explicit property of opclasses, but I'm not sure just how to do that. A related point is that we not infrequently get requests for a way to make ORDER BY sort nulls low; it'd be nice if we could actually support that, rather than going in the direction of making sure it can't happen. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Does anybody use ORDER BY x USING y?
On P, 2005-09-18 at 18:04 -0400, Tom Lane wrote: Another thing that's flaky in the current treatment is the question of whether NULLs sort before or after ordinary values. We've essentially tried to force NULLs to sort high (as if they compare greater than all ordinary values), so that ASC and DESC orderings can be obtained from forward and backwards scans of an ordinary btree index. This is going to break entirely in the presence of reverse-sort opclasses --- given the current btree code, such an opclass would cause NULLs to appear to sort low. I suspect we have to bring out the NULL sort behavior as an explicit property of opclasses, but I'm not sure just how to do that. A related point is that we not infrequently get requests for a way to make ORDER BY sort nulls low; it'd be nice if we could actually support that, rather than going in the direction of making sure it can't happen. I think that placement of NULL's should be a property of ORDER BY and separated from opclass. From: http://opensource2.atlassian.com/projects/hibernate/browse/HHH-465 support of nulls first / last in order clause NULLS LAST is part of the SQL 99 standard. The syntax is as follows: ORDER BY [COLUMN NAME] [ASC | DESC] [NULLS FIRST | NULLS LAST] In different DBs, the sorting of nulls relative to other values is handled differently. PostgreSQL - Nulls are considered HIGHER than non-nulls. DB2 - Higher MSSQL - Lower MySQL - Lower Oracle - Higher The following DBs have supported this functionality: DB2 V7 Oracle 9i PostgreSQL, MySQL, SQLServer do not appear to support this from what I can gather. see http://forum.hibernate.org/viewtopic.php? t=942176start=0postdays=0postorder=aschighlight= -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Does anybody use ORDER BY x USING y?
Hannu Krosing [EMAIL PROTECTED] writes: I think that placement of NULL's should be a property of ORDER BY and separated from opclass. That would be an extremely bad idea, because it would immediately remove index scans as one way to meet an ORDER BY. I'm thinking in terms of NULL high/low as becoming a property of btree opclasses so that indexes know what to do with nulls, and so that the planner can tell whether a given index meets the required sort ordering or not. Alternatively we could define an index's ordering as being specified by both an opclass and a NULL direction, but that doesn't seem better to me; especially since the null-direction concept doesn't seem meaningful for non-btree indexes at all, but a structure like that would require us to associate a null-direction with all indexes. regards, tom lane ---(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] Does anybody use ORDER BY x USING y?
Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: I think that placement of NULL's should be a property of ORDER BY and separated from opclass. That would be an extremely bad idea, because it would immediately remove index scans as one way to meet an ORDER BY. I'm thinking in terms of NULL high/low as becoming a property of btree opclasses so that indexes know what to do with nulls, and so that the planner can tell whether a given index meets the required sort ordering or not. Alternatively we could define an index's ordering as being specified by both an opclass and a NULL direction, but that doesn't seem better to me; especially since the null-direction concept doesn't seem meaningful for non-btree indexes at all, but a structure like that would require us to associate a null-direction with all indexes. Not sure I understand ... in fact I am sure I don't :-) Are you envisioning that the null direction will be able to be selected at the time of the select statement? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Does anybody use ORDER BY x USING y?
Tom Lane [EMAIL PROTECTED] writes: Hannu Krosing [EMAIL PROTECTED] writes: I think that placement of NULL's should be a property of ORDER BY and separated from opclass. That would be an extremely bad idea, because it would immediately remove index scans as one way to meet an ORDER BY. Well couldn't the index scan be taught to go fetch the NULLs in a separate traversal? -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Does anybody use ORDER BY x USING y?
Andrew Dunstan [EMAIL PROTECTED] writes: Not sure I understand ... in fact I am sure I don't :-) Are you envisioning that the null direction will be able to be selected at the time of the select statement? Yes, of course. My point is that we need to define operator class as all you need to know about the behavior of a particular index column. Moving away from that equivalence is just going to mess things up with no redeeming social benefit. This looks bad, because the first conclusion is that for any particular comparison function (eg, int4cmp) you'd want four separate operator classes, to cover the combinations of ASC-sort and DESC-sort versus NULLs-high and NULLs-low. But you'd be paying for that complication somewhere, and ISTM the operator class abstraction is exactly the right level to pay it at. We could ease the pain for creators of user-defined types by inventing some mechanism that automatically creates the whole set of operator classes --- this is another idea that's barely half baked yet, but I think it ties in nicely with the idea of operator class families to relate opclasses for different datatypes. Basically I'd like to solve most of these issues by constructing a new layer atop opclasses, not by deciding that an opclass doesn't convey the full story about the behavior of an index column. regards, tom lane ---(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] Does anybody use ORDER BY x USING y?
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: That would be an extremely bad idea, because it would immediately remove index scans as one way to meet an ORDER BY. Well couldn't the index scan be taught to go fetch the NULLs in a separate traversal? (1) IS NULL is not an indexable operation, so no, not without significant overhaul of the index AM API. (2) This propagates a problem that is specific to orderable indexes (ie btree) into code that is generic to all indexes, and thus creates the problem of how do you deal with specifying NULL ordering without any definition of ordering for non-NULLs. (3) You still have to invent a mechanism to define whether you want nulls first or last ... and make sure that that mechanism works for plans that use explicit SORT steps as well as those that use index scans. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Beta2 Wrap Up ...
On Sun, 18 Sep 2005, Magnus Hagander wrote: Having spent days, no, weeks deciding on that name on list I do not want to see it change this late, especially as we'll now need to go and update pgAdmin again! Fortunately, pgAdmin doesn't use that function, but only the basic pg_relation_size(). Phew! Good for you :-) Also, the change to pg_cancel_backend breaks backwards compatibility with 8.0, which is a whole lot worse than breaking it with 8.1-beta1. Unfortunately, core doesn't see this as backward compatibility break, instead it's regarded as adjustment of a new function. Anything that's not in core isn't worth a single thought This function has been in core since 8.0. The other functions were new (to core that is, imported from external module), but this one was in the main backend already. Is there a reason the old/new can't be aliaseed to each other, instead of the old just being removed? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Start translating
I think beta 2 is a good time to start fixing up the translations again. As I had previously announced, long-time translators are invited to register themselves at http://pgfoundry.org/projects/pgtranslation and commit their work there. Those who do not want to do that can continue to submit their work at via [EMAIL PROTECTED] The NLS status tables at http://developer.postgresql.org/~petere/nlsstatus/ are now up to date regarding both the PostgreSQL source code and the PgFoundry repository, so you can download the up to date PO files from there to start your work. I eventually plan to move the status tables and other web pages to the PgFoundry site. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
