Re: [HACKERS] like/ilike improvements
And Dennis said: It is only when you have a pattern like '%_' when this is a problem and we could detect this and do byte by byte when it's not. Now we check (*p == '\\') || (*p == '_') in each iteration when we scan over characters for '%', and we could do it once and have different loops for the two cases. That's pretty much what the patch does now - It never tries to match a single byte when it sees _, whether or not preceeded by %. My comment was about UTF-8 since I thought we were making a special version for UTF-8. I don't know what properties other multibyte encodings have. /Dennis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] msvc, build and install with cygwin in the PATH
With a small modification to src/tools/msvc/Install.pm (see attached
patch) it's possible for me to build with msvc and install postgres on a
Windows xp box and leave cygwin in the PATH. Since I use cygwin
frequently it's usfull for me to have it in the PATH.
This might not work on Win9x platforms. But is that platform supported
for development anyway?
Hannes.
Index: src/tools/msvc/Install.pm
===
--- src/tools/msvc/Install.pm (revision 44)
+++ src/tools/msvc/Install.pm (working copy)
@@ -110,7 +110,7 @@
my $subdirs = $norecurse?'':'/s';
print Copying $what unless ($silent);
-open($D, dir /b $subdirs $spec |) || croak Could not list $spec\n;
+open($D, cmd /c dir /b $subdirs $spec |) || croak Could not list
$spec\n;
while ($D)
{
chomp;
@@ -375,7 +375,7 @@
print Installing NLS files...;
EnsureDirectories($target, share/locale);
-open($D,dir /b /s nls.mk|) || croak Could not list nls.mk\n;
+open($D,cmd /c dir /b /s nls.mk|) || croak Could not list nls.mk\n;
while ($D)
{
chomp;
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [GSOC] - I ntegrity check algorithm for data files
Robert Mach wrote: I see different ways of delivering this functionality to Postgresql. The best of course would be to become part of Postgresql release either as a PostgreSQL command (like UPDATE) or as an postgresql server application like vacuumdb. Other possibility is to create a freestanding program that would be called with location of datafiles as arguments. Last possibility is to create an administrative console access (single user mode) to the database in which this integrity check could be fired. I discussed It with Niel and for logical test It should be PostgreSQL function. However, for physical layer test database cluster should be in inconsistent state and PostgreSQL startup process fails. It means that there are only limited possibilities. 1) standalone binary, which will be linked mostly from postgreSQL object files and replace main and add new testing functionality. This is recommended by Neil. 2) Add new postmaster context/mod - repair and recovery, when postgres is run as postmaster --recovery. 3) My personal favorite is create management console - which allows to perform check without physically access to local machine. Management console should be use for another purpose - for example disable/enable databases from cluster, perform upgrade of datalayer to the new version, kill sessions, update postgresql.conf and so on... However this solution has significant impact on current postgres behavior, but I think it should be big deal for postgres. Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GSOC] - I ntegrity check algorithm for data files
Zdenek Kotala wrote: 3) My personal favorite is create management console - which allows to perform check without physically access to local machine. Management console should be use for another purpose - for example disable/enable databases from cluster, perform upgrade of datalayer to the new version, kill sessions, update postgresql.conf and so on... However this solution has significant impact on current postgres behavior, but I think it should be big deal for postgres. I believe pgadmin lets you update postgresql.conf remotely, and I know you can kill sessions from within PG ( pg_cancel_backend() ). For the rest, that's what ssh is for imho. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] GIT patch review
Hello, I'd like to help reviewing patches, in particular the group index tupes (GIT) patch by Heikki Linnakangas (http://archives.postgresql.org/pgsql-hackers/2007-02/msg01264.php). I've spoken with Alvaro about it, he gave me several links to the threads on hackers related to the GIT patch and I have some questions: What about proposition for indexam changes, I can't find any patches there ? (http://momjian.us/mhonarc/patches/msg00125.html) Is the patch for changing amgetmulti to amgetbitmap relevant to the GIT patch ? This original patch is here: http://archives.postgresql.org/pgsql-patches/2007-03/msg00163.php It doesn't apply cleanly to the cvs head, I can provide necessary changes (actually I've sent them and figured there is a nowhere mentioned limit on -hackers which is why I'm resending the message without that patch), Any other suggestions on reviewing the GIT patch ? Regards, -- Alexey Klyukin http://www.commandprompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] msvc, build and install with cygwin in the PATH
Hannes Eder wrote: -open($D, dir /b $subdirs $spec |) || croak Could not list $spec\n; +open($D, cmd /c dir /b $subdirs $spec |) || croak Could not list $spec\n; What the heck are we doing here anyway? We should be doing this a la Perl - calling out to dir /b is surely not the best way to do this. If we need to recurse we should use File::Find. cheers andrew ---(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] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Shachar Shemesh [EMAIL PROTECTED] writes: Before you explode at me (again :), I'm not arguing that you can do binary based calculations of decimal numbers without having rounding errors that come to bite you. I know you can't. What I'm saying is that we have two cases to consider. In one of them the above is irrelevant, and in the other I'm not so sure it's true. You're setting up a straw-man argument, though. The real-world problem cases here are not decimal, they are non-IEEE binary floating arithmetic. The typical difference from IEEE is slightly different tradeoffs in number of mantissa bits vs number of exponent bits within a 32- or 64-bit value. I seem to recall also that there are machines that treat the exponent as power-of-16 not power-of-2. So depending on which way the tradeoffs went, the other format will have either more precision or more range than IEEE. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] like/ilike improvements
Tom Lane wrote: 3. UTF8: % can advance bytewise. _ must check it is on a first byte (else return match failure) and if so do NextChar. So primitives are NextChar, NextByte, ByteEq, IsFirstByte. We should only be able to get out of step from the %_ case, I believe, so we should only need to do the first-byte test in that case (which is in a different code path from the normal _ case. Does that seem right? cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] like/ilike improvements
Andrew Dunstan [EMAIL PROTECTED] writes: We should only be able to get out of step from the %_ case, I believe, so we should only need to do the first-byte test in that case (which is in a different code path from the normal _ case. Does that seem right? At least put Assert(IsFirstByte()) in the main path. I'm a bit suspicious of the separate-path business anyway. Will it do the right thing with say %%%_ ? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] like/ilike improvements
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: We should only be able to get out of step from the %_ case, I believe, so we should only need to do the first-byte test in that case (which is in a different code path from the normal _ case. Does that seem right? At least put Assert(IsFirstByte()) in the main path. I'm a bit suspicious of the separate-path business anyway. Will it do the right thing with say %%%_ ? Yes: /* %% is the same as % according to the SQL standard */ /* Advance past all %'s */ while ((plen 0) (*p == '%')) NextByte(p, plen); cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] like/ilike improvements
Andrew Dunstan wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: We should only be able to get out of step from the %_ case, I believe, so we should only need to do the first-byte test in that case (which is in a different code path from the normal _ case. Does that seem right? At least put Assert(IsFirstByte()) in the main path. I'm a bit suspicious of the separate-path business anyway. Will it do the right thing with say %%%_ ? Yes: /* %% is the same as % according to the SQL standard */ /* Advance past all %'s */ while ((plen 0) (*p == '%')) NextByte(p, plen); I am also wondering if it might be sensible to make this choice once at backend startup and store a function pointer, instead of doing it for every string processed by like/ilike: if (pg_database_encoding_max_length() == 1) return SB_MatchText(s, slen, p, plen); else if (GetDatabaseEncoding() == PG_UTF8) return UTF8_MatchText(s, slen, p, plen); else return MB_MatchText(s, slen, p, plen); I guess that might make matters harder if we ever got per-column encodings. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] like/ilike improvements
Andrew Dunstan [EMAIL PROTECTED] writes: I am also wondering if it might be sensible to make this choice once at backend startup and store a function pointer, instead of doing it for every string processed by like/ilike: if (pg_database_encoding_max_length() == 1) return SB_MatchText(s, slen, p, plen); else if (GetDatabaseEncoding() == PG_UTF8) return UTF8_MatchText(s, slen, p, plen); else return MB_MatchText(s, slen, p, plen); I guess that might make matters harder if we ever got per-column encodings. Yeah. It's not saving much anyway ... I wouldn't bother. 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] [GSOC] - I ntegrity check algorithm for data files
Richard Huxton wrote: Zdenek Kotala wrote: 3) My personal favorite is create management console - which allows to perform check without physically access to local machine. Management console should be use for another purpose - for example disable/enable databases from cluster, perform upgrade of datalayer to the new version, kill sessions, update postgresql.conf and so on... However this solution has significant impact on current postgres behavior, but I think it should be big deal for postgres. I believe pgadmin lets you update postgresql.conf remotely, IIRC, it is possible only when you have some additional module/function installed on server and it works only for pgadmin, not for psql. and I know you can kill sessions from within PG ( pg_cancel_backend() ). If you look how kill session is implemented it does not look much safe. For the rest, that's what ssh is for imho. And what you will do when you don't have ssh access on this machine and 5432 is only one way how to administrate a server? (Windows is another story.) Zdenek ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GSOC] - I ntegrity check algorithm for data files
Zdenek Kotala wrote: I believe pgadmin lets you update postgresql.conf remotely, IIRC, it is possible only when you have some additional module/function installed on server and it works only for pgadmin, not for psql. It needs the adminpack contrib module installed in the maintenance DB (normally postgres). In theory it could work for psql, but it's completely impractical - adminpack just gives us some file IO functions which we just use to read and write the files. All the editing logic is handled by pgAdmin. Regards, Dave ---(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] [GSOC] - I ntegrity check algorithm for data files
Zdenek Kotala wrote: Richard Huxton wrote: For the rest, that's what ssh is for imho. And what you will do when you don't have ssh access on this machine and 5432 is only one way how to administrate a server? (Windows is another story.) If I've not got ssh access to the machine, then I'm not the one responsible for editing configuration files. If it's a Windows machine, I'd probably use remote-desktop and pgadmin (not that anyone has ever asked me to). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GSOC] - I ntegrity check algorithm for data files
Richard Huxton wrote: Zdenek Kotala wrote: Richard Huxton wrote: For the rest, that's what ssh is for imho. And what you will do when you don't have ssh access on this machine and 5432 is only one way how to administrate a server? (Windows is another story.) If I've not got ssh access to the machine, then I'm not the one responsible for editing configuration files. I'm not sure if ssh access is in correlation with db administration. However in some Unix system you can get right to user start stop postgres (RBAC, sudo ...), but there is not easy way how to allow him to access configuration file only in way to share postgres user password. Zdenek ---(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] [GENERAL] query log corrupted-looking entries
Hoping to resurrect this thread. I am seeing more and more of this as the database gets more usage and it really messes up query log analysis. A quick summary: When I posted this was getting corrupted query log entries. I still am. They look like this (the problem line + one line before and after): 2007-05-15 14:24:52 PDT [3859]: [968-1] LOG: duration: 0.071 ms 2007-05-15 14:24:52 PDT [3859]: [969-1] LOG: statement2007-05-15 14:24:52 PDT [3885]: [208-1] LOG: duration: 11.800 ms : PREPARE unnamed AS SELECT P.IS_DISPLAY, P.IS_ACTIVE, P.CITY_POSTAL_CODE_ID,P.property_name,P.address_1,P.address_2, This seems to happen with statements of all lengths (some are long, but many are not). This used to happen on 8.1.3. I am now on: PostgreSQL 8.1.8 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3) I think what Tom suggested last (see below) is that I try something on my machine, but it was not entirely to me what exactly and I failed to follow up. I'd be happy to do any tests to help diagnose this better, although I don't have a reliable way to reproduce it (put a lot of stress on the DB is all I can do and hope it happens). Thanks, George -Original Message- From: George Pavlov Sent: Wednesday, October 18, 2006 1:23 PM To: Tom Lane Cc: pgsql-hackers@postgreSQL.org Subject: RE: [GENERAL] query log corrupted-looking entries the behavior. It'd be interesting to verify whether it's the same on George's machine though. Let me know how to test this. (Please do a for dummies version -- I am not sure I can figure it out from the thread even though someone else might be able to.) -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 1:17 PM To: George Pavlov Cc: pgsql-hackers@postgreSQL.org Subject: Re: [GENERAL] query log corrupted-looking entries I wrote: I checked around with some kernel/glibc gurus in Red Hat, and the consensus seemed to be that we'd be better off to bypass fprintf() and just send message strings to stderr using write() --- ie, instead of elog.c doing fprintf(stderr, %s, buf.data); do write(fileno(stderr), buf.data, strlen(buf.data)); I did some strace'ing of the backend, and observed that even for very long messages (upwards of 100K) the fprintf results in a single write() call. This was true on both Fedora Core 5 and a pretty old HPUX version. So it'd seem that most versions of libc already know about optimizing fprintf-%s into a direct write(), and changing our code wouldn't change the behavior. It'd be interesting to verify whether it's the same on George's machine though. 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] GIT patch review
Alexey Klyukin wrote: What about proposition for indexam changes, I can't find any patches there ? (http://momjian.us/mhonarc/patches/msg00125.html) That mail is just discussion that lead to the patch below: Is the patch for changing amgetmulti to amgetbitmap relevant to the GIT patch ? This original patch is here: http://archives.postgresql.org/pgsql-patches/2007-03/msg00163.php The fundamental change to the indexam API that GIT requires is support for returning candidate matches. There's two parts to that: the bitmap index scan API (amgetmulti) and the regular amgettuple API. Another issue that needs to be dealt with is that as the GIT patch stands, it doesn't retain the ordering of tuples within a heap page, which means that they need to be sorted on a page-by-page basis when returning them to the executor. That's ugly, the way it's implemented now. To make it less ugly, we'd need support in the amgettuple API to return tuples in partial ordering. Since there was discussion on changing the bitmap index API to make it more efficient for on-disk bitmap indexes, I created a combined patch that both works nicely with on-disk bitmap indexes, and supports candidate matches. That's what the amgetmulti-amgetbitmap patch does. The other part, supporting candidate matches in the amgettuple API hasn't been done. I posted a design that's in the patch queue (Indexam interface proposal), hoping to have a consensus on that. There was some discussion on using the candidate matches support for GIN and GiST as well. IIRC there was no objections to the candidate matches support, but I haven't written a patch to do that. A more controversial and invasive change is the support for returning tuples in partial ordering. If we don't want to do that, we can modify the main GIT/clustered indexes patch so that it does retain the full ordering of tuples. It'll degrade much more quickly to a normal B-tree if tuples are not perfectly ordered on the heap, if tuples are updated for examply, but it'll be less invasive. It doesn't apply cleanly to the cvs head, I can provide necessary changes (actually I've sent them and figured there is a nowhere mentioned limit on -hackers which is why I'm resending the message without that patch), Ok, thanks. Any other suggestions on reviewing the GIT patch ? You might want to start by reading the readme: http://community.enterprisedb.com/git/git-readme.txt Thanks for looking into this. If you have any questions, just ask. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Possible to inline setof SQL UDFs?
I'm curious if there is a reason that SQL setof UDFs couldn't be inlined? For example, given a sable setof SQL UDF like CREATE TYPE uids AS (uid integer); CREATE FUNCTION needs_secure(integer, integer) RETURNS SETOF uids AS $_$ SELECT uid FROM needs nsec WHERE nsec.foo = $1 AND nsec.bar = $2 $_$ LANGUAGE SQL STABLE; Couldn't any call to this function SELECT * FROM needs n JOIN needs_secure( 1, 5000 ) ns ON n.uid = ns.uid; Become SELECT * FROM needs n JOIN ( SELECT uid FROM needs nsec WHERE nsec.foo = 1 AND nsec.bar = 5000 ) ns ON n.uid = ns.uid; ? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Possible to inline setof SQL UDFs?
[EMAIL PROTECTED] writes: I'm curious if there is a reason that SQL setof UDFs couldn't be inlined? Lack of round tuits, I think. I seem to recall having looked at it awhile ago and concluded that it wasn't quite trivial, but I forget why. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] like/ilike improvements
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
We should only be able to get out of step from the %_ case, I believe,
so we should only need to do the first-byte test in that case (which is
in a different code path from the normal _ case. Does that seem right?
At least put Assert(IsFirstByte()) in the main path.
I'm a bit suspicious of the separate-path business anyway. Will it do
the right thing with say %%%_ ?
OK, Here is a patch that I am fairly confident does what's been
discussed, as summarised by Tom.
To answer Guillaume's question - it probably won't apply cleanly to 8.2
sources.
cheers
andrew
Index: src/backend/utils/adt/like.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/like.c,v
retrieving revision 1.68
diff -c -r1.68 like.c
*** src/backend/utils/adt/like.c 27 Feb 2007 23:48:08 - 1.68
--- src/backend/utils/adt/like.c 24 May 2007 00:26:49 -
***
*** 28,48
#define LIKE_ABORT (-1)
! static int MatchText(char *t, int tlen, char *p, int plen);
! static int MatchTextIC(char *t, int tlen, char *p, int plen);
! static int MatchBytea(char *t, int tlen, char *p, int plen);
! static text *do_like_escape(text *, text *);
! static int MBMatchText(char *t, int tlen, char *p, int plen);
! static int MBMatchTextIC(char *t, int tlen, char *p, int plen);
static text *MB_do_like_escape(text *, text *);
/*
* Support routine for MatchText. Compares given multibyte streams
* as wide characters. If they match, returns 1 otherwise returns 0.
*
*/
! static int
wchareq(char *p1, char *p2)
{
int p1_len;
--- 28,50
#define LIKE_ABORT (-1)
! static int SB_MatchText(char *t, int tlen, char *p, int plen);
! static text *SB_do_like_escape(text *, text *);
! static int MB_MatchText(char *t, int tlen, char *p, int plen);
static text *MB_do_like_escape(text *, text *);
+ static int UTF8_MatchText(char *t, int tlen, char *p, int plen);
+
+ static int GenericMatchText(char *s, int slen, char* p, int plen);
+ static int Generic_Text_IC_like(text *str, text *pat);
+
/*
* Support routine for MatchText. Compares given multibyte streams
* as wide characters. If they match, returns 1 otherwise returns 0.
*
*/
! static inline int
wchareq(char *p1, char *p2)
{
int p1_len;
***
*** 72,86
* of getting a single character transformed to the system's wchar_t format.
* So now, we just downcase the strings using lower() and apply regular LIKE
* comparison. This should be revisited when we install better locale support.
- *
- * Note that MBMatchText and MBMatchTextIC do exactly the same thing now.
- * Is it worth refactoring to avoid duplicated code? They might become
- * different again in the future.
*/
/* Set up to compile like_match.c for multibyte characters */
#define CHAREQ(p1, p2) wchareq(p1, p2)
- #define ICHAREQ(p1, p2) wchareq(p1, p2)
#define NextChar(p, plen) \
do { int __l = pg_mblen(p); (p) +=__l; (plen) -=__l; } while (0)
#define CopyAdvChar(dst, src, srclen) \
--- 74,85
* of getting a single character transformed to the system's wchar_t format.
* So now, we just downcase the strings using lower() and apply regular LIKE
* comparison. This should be revisited when we install better locale support.
*/
+ #define NextByte(p, plen) ((p)++, (plen)--)
+
/* Set up to compile like_match.c for multibyte characters */
#define CHAREQ(p1, p2) wchareq(p1, p2)
#define NextChar(p, plen) \
do { int __l = pg_mblen(p); (p) +=__l; (plen) -=__l; } while (0)
#define CopyAdvChar(dst, src, srclen) \
***
*** 90,122
*(dst)++ = *(src)++; \
} while (0)
! #define MatchText MBMatchText
! #define MatchTextIC MBMatchTextIC
#define do_like_escape MB_do_like_escape
#include like_match.c
- #undef CHAREQ
- #undef ICHAREQ
- #undef NextChar
- #undef CopyAdvChar
- #undef MatchText
- #undef MatchTextIC
- #undef do_like_escape
-
/* Set up to compile like_match.c for single-byte characters */
! #define CHAREQ(p1, p2) (*(p1) == *(p2))
! #define ICHAREQ(p1, p2) (tolower((unsigned char) *(p1)) == tolower((unsigned char) *(p2)))
! #define NextChar(p, plen) ((p)++, (plen)--)
#define CopyAdvChar(dst, src, srclen) (*(dst)++ = *(src)++, (srclen)--)
#include like_match.c
- /* And some support for BYTEA */
- #define BYTEA_CHAREQ(p1, p2) (*(p1) == *(p2))
- #define BYTEA_NextChar(p, plen) ((p)++, (plen)--)
- #define BYTEA_CopyAdvChar(dst, src, srclen) (*(dst)++ = *(src)++, (srclen)--)
/*
* interface routines called by the function manager
--- 89,148
*(dst)++ = *(src)++; \
} while (0)
! #define MatchText MB_MatchText
#define do_like_escape MB_do_like_escape
#include like_match.c
/* Set up to compile
[HACKERS] Article on 8.3 release on LWN.net
Hi, LWN.net has published another article on our development process. This times it focus on our stated lack of (or slowness of) progress in the review of the 8.3 patch queue. It is an interesting read. The article is at http://lwn.net/SubscriberLink/235230/de029adb1cab0ab2/ -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 The problem with the future is that it keeps turning into the present (Hobbes) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: Before you explode at me (again :), I'm not arguing that you can do binary based calculations of decimal numbers without having rounding errors that come to bite you. I know you can't. What I'm saying is that we have two cases to consider. In one of them the above is irrelevant, and in the other I'm not so sure it's true. You're setting up a straw-man argument, though. I was answering your claim that it's impossible to convert decimal to binary based floats without rounding errors. The real-world problem cases here are not decimal, they are non-IEEE binary floating arithmetic. The typical difference from IEEE is slightly different tradeoffs in number of mantissa bits vs number of exponent bits within a 32- or 64-bit value. I answered that elsewhere while suggesting a different format that would address that. These numbers do not appear to be a concern in our situation, however. I seem to recall also that there are machines that treat the exponent as power-of-16 not power-of-2. I'm pretty sure I don't understand this. Maybe I misunderstood the format, but wouldn't that actually lose you precision with, at most, marginal gain in range? As far as I can see, the moment you no longer work in base 2 you lose the implicit bit, which means you have a one bit less starting point than base 2 notations (all number are denormalized). So depending on which way the tradeoffs went, the other format will have either more precision or more range than IEEE. Again, should that be a real concern, see my message at http://archives.postgresql.org/pgsql-hackers/2007-05/msg00892.php for details about what the suggestion actually is. Just be sure to read IEEE there as meaning IEEE like. I allowed different sizes for the fields. regards, tom lane Shachar ---(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
