Re: [HACKERS] Transaction is read-only in auto commit mode
Chattopadhyay asked: My application based on Java servlets was running fine with version PostgreSQL 7.x, but started giving error: transaction is read-only, in version 8.0 and 8.1. I am using Suse Linux 9.3/PostgreSQL 8.0 or Suse Linux 10.1/PostgreSQL 8.1. I am using JDBC 3 drivers and all connections are in auto-commit mode. Could you please tell me what's going wrong. [...] I'd really appreciate if anyone could suggest a pointer for further investigation, if not an outright solution. Via JDBC, ask the database to SHOW default_transaction_read_only and look at the output. If that is 'true', look at SELECT source FROM pg_settings WHERE name='default_transaction_read_only' If it is false, check java.sql.Connection.isReadOnly(). It is 'false' by default, but maybe is changed somewhere in your code. Yours, Laurenz Albe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Can i see server SQL commands ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, Sep 28, 2006 at 04:27:36AM +0300, Adnan DURSUN wrote: Hi all I wanna know what is going on while a DML command works. For example ; Which commands are executed by the core when we send an UPDATE tab SET col = val1... Adnan, this mailing list is not the right one for such questions. More appropriate would be [EMAIL PROTECTED] or maybe pgsql-general@postgresql.org. Having said that, you may set the log level of the server in the configuration file (whose location depends on your OS and PostgreSQL version. Look there for a line log_statements = XXX and set XXX to 'all'. Don't forget to restart your server afterwards. HTH - -- tomas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFFG3QyBcgs9XrR2kYRAgdqAJ0VnUw5+Q79HiIwHocHIw4TWHePaQCffBBK ASn3Z6XpKG91NTrmEaBtz08= =Ibh3 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [OT][HACKERS] DROP FUNCTION IF EXISTS
Backspace deletes character-wise, as long as you have LANG set correctly. Check LANG and the LC_* environment variables. OK, you're right: $ echo $LANG [EMAIL PROTECTED] # show client_encoding ; client_encoding - UTF8 (1 row) But then I wonder why the client encoding is set to UTF-8 ? I did not fiddle at all with this AFAIK, and I guess psql is the one setting this... OTOH, I'm accessing the DB box via ssh, and my local box has: cnagy echo $LANG en_US.UTF-8 So it might be some strange interaction between my local locale, the server's locale and ssh... BTW, I tried if the same problem happens on the server command line (via ssh), and it does (while, for completeness, it works fine on my local box): [EMAIL PROTECTED]:~$ date -sh: date: command not found ^ this here is the result of 'ü + backspace' So I guess this is not postgres related, and therefore off topic for this list... Cheers, Csaba. ---(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
[HACKERS] casting to domain problem.
Folks, Continuing on previous discussion: http://archives.postgresql.org/pgsql-hackers/2006-09/msg01681.php I was wondering why we are stripping the domains in find_coercion_pathway(). Is it because we do not care for domains at that point? And why don't we have extra logic there in order to find the appropriate cast record (for the domain). I searched the list for a possible answer but I couldn't find one. Could someone please clarify this? -- Regards, Gevik Babakhani http://www.postgresql.nl http://www.truesoftware.nl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Block B-Tree concept
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: AFAICS, we could disable the optimization and use a full-blown transaction when vacuuming a table with a functional block index. No, we couldn't, or at least it's not merely a matter of reversing a recent optimization. The fundamental issue with all these proposals is the assumption that you can re-compute the index entries at all. VACUUM has never, ever, depended on the assumption that it can re-evaluate index entries and get the same answers as the original insertion did. Now obviously it should theoretically be able to do that, in a theoretical bug-free world, but given that we allow user-defined functions in index expressions that is a very hazardous assumption: you might get a different answer. Or an error. The current VACUUM procedure is able to clean up index entries correctly without any recalculation of the index values, just matching of TIDs. I think we'd be taking a serious robustness hit if we abandon that property. I'm not worried about getting different results. If a used-defined function behaves badly, you're queries are screwed anyway. But throwing an error would be bad, because that would abort the whole vacuum. If we want to keep the property that VACUUM doesn't re-evaluate index entries, any proposal that doesn't keep track of every heap tuple isn't going to work. I'll try to modify the design I had in mind so that it does keep track of every heap tuple in some form. This is basically the same objection that I have to the occasional proposals for retail VACUUM. Yeah. :-( BTW, it's not merely a problem for functional indexes: the datatype-specific functions invoked while searching an index are also hazards. Good point. I didn't realize that before. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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
[HACKERS] Install on Windows XP
I cannot get gmake to work for the postgresql-8.1.4 source code on Windows XP. I created MinGW with the MinGW 5.0.3.exe (both current latest) and MySyS 1.0.10. Gmake was not available so I used the included mysys make – GNU version 79. After succeeding with “./configure –without-zlib” it then failed on “make” as follows: rt/win32 -c -o fseeko.o fseeko.c gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -I../../src/port -DFRONTEND -I../../src/include -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/port/win32 -c -o getrusage.o getrusage.c In file included from ../../src/include/rusagestub.h:17, from getrusage.c:18: c:/MinGW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/sys/time.h:27: error: redefinition of `struct timezone' c:/MinGW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/sys/time.h:40: error: conflicting types for 'gettimeofday' ../../src/include/port.h:266: error: previous declaration of 'gettimeofday' was here c:/MinGW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/sys/time.h:40: error: conflicting types for 'gettimeofday' ../../src/include/port.h:266: error: previous declaration of 'gettimeofday' was here make[2]: *** [getrusage.o] Error 1 make[2]: Leaving directory `/c/postgresql-8.1.4/src/port' make[1]: *** [install] Error 2 make[1]: Leaving directory `/c/postgresql-8.1.4/src' make: *** [install] Error 2. I tracked down zlib but never did figure out where to place it. I retried using the guide in libpqxx which said: extract, compile install zlib by ./configure –prefix=c:/mingw/local make make make install – this failed. I then tried ./configure –prefix=c:/mingw/local –with-includes=c:/mingw/local/include –with-libs=c:/mingw/local/lib make make install but it had an issue with make. Mike Bassett ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Row IS NULL question
I'm playing around NULL. Docs says that A row value is considered not null if it has at least one field that is not null. and SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows So, I try: wow=# \d tst Table public.tst Column | Type | Modifiers +--+--- a | double precision | b | double precision | % echo 'SELECT count(*) FROM tst WHERE a IS NULL AND b IS NULL;' | psql wow SET count --- 6 (1 row) But ROW() IS NULL doesn't find anything: % echo 'SELECT ROW(tst.*) IS NULL FROM tst;' | psql wow | grep 't' % echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow SET count --- 0 (1 row) What do I do wrong? Version of postgres - today's HEAD. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Backup and restore through JDBC
Hello List I am trying to develop, a API to carry through backup and restore through JDBC. I think that the best form is to use JNI. Some Suggestion?
Re: [HACKERS] Faster StrNCpy
Mark, In the specific case of the namestrcpy () function, it will copy a maximum of 64 bytes, but the length of the source string is unknown. I would have to think that memcpy () would certainly win if you knew the source and destination sizes etc. Perhaps there are some places like that in the code that don't use memcpy () currently? David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 27, 2006 4:27 PM To: Strong, David Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Faster StrNCpy On Wed, Sep 27, 2006 at 07:08:05AM -0700, Strong, David wrote: We sometimes see TupleDescInitEntry () taking high CPU times via OProfile. This does include, amongst a lot of other code, a call to namestrcpy () which in turn calls StrNCpy (). Perhaps this is not a good candidate right now as a name string is only 64 bytes. Just wondering - are any of these cases where a memcpy() would work just as well? Or are you not sure that the source string is at least 64 bytes in length? memcpy(target, source, sizeof(target)); target[sizeof(target)-1] = '\0'; I imagine any extra checking causes processor stalls, or at least for the branch prediction to fill up? Straight copies might allow for maximum parallelism? If it's only 64 bytes, on processors such as Pentium or Athlon, that's 2 or 4 cache lines, and writes are always performed as cache lines. I haven't seen the code that you and Tom are looking at to tell whether it is safe to do this or not. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(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] New version of money type
On Thu, 14 Sep 2006 10:35:03 -0400 D'Arcy J.M. Cain darcy@druid.net wrote: For years I have been promising that a 64 bit version of the money type was on the way. Here it is. So far it compiles and I have done some basic testing on it and it seems to work fine. Note that the currency symbol is also dropped on output as well but it is accepted on input. darcy=# select '$92,233,720,368,547,758.07'::money; money 92,233,720,368,547,758.07 There has been plenty of discussion back and forth but still no ruling from core. Is money out in the next release in which case I can convert this to a contrib module or will this improvement be accepted for the next release. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 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] New version of money type
Though this may be the kiss of death, I favor a 64 bit float version of money. It's more terse than numeric and a *lot* faster when performing numeric operations because it would use a cpu intrinsic operand. - Luke Msg is shrt cuz m on ma treo -Original Message- From: D'Arcy J.M. Cain [mailto:[EMAIL PROTECTED] Sent: Thursday, September 28, 2006 11:02 AM Eastern Standard Time To: D'Arcy J.M. Cain Cc: pgsql-hackers@postgreSQL.org Subject:Re: [HACKERS] New version of money type On Thu, 14 Sep 2006 10:35:03 -0400 D'Arcy J.M. Cain darcy@druid.net wrote: For years I have been promising that a 64 bit version of the money type was on the way. Here it is. So far it compiles and I have done some basic testing on it and it seems to work fine. Note that the currency symbol is also dropped on output as well but it is accepted on input. darcy=# select '$92,233,720,368,547,758.07'::money; money 92,233,720,368,547,758.07 There has been plenty of discussion back and forth but still no ruling from core. Is money out in the next release in which case I can convert this to a contrib module or will this improvement be accepted for the next release. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] New version of money type
On Thu, 28 Sep 2006 11:09:17 -0400 Luke Lonergan [EMAIL PROTECTED] wrote: Though this may be the kiss of death, I favor a 64 bit float version of money. It's more terse than numeric and a I assume you mean ...64 bit INT version... -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] New version of money type
Oic - so it's a floating point in an 8 byte int. That probably limits the speed benefits, no? - Luke Msg is shrt cuz m on ma treo -Original Message- From: D'Arcy J.M. Cain [mailto:[EMAIL PROTECTED] Sent: Thursday, September 28, 2006 11:14 AM Eastern Standard Time To: Luke Lonergan Cc: pgsql-hackers@postgreSQL.org Subject:Re: [HACKERS] New version of money type On Thu, 28 Sep 2006 11:09:17 -0400 Luke Lonergan [EMAIL PROTECTED] wrote: Though this may be the kiss of death, I favor a 64 bit float version of money. It's more terse than numeric and a I assume you mean ...64 bit INT version... -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New version of money type
On Thu, 28 Sep 2006 11:25:45 -0400 Luke Lonergan [EMAIL PROTECTED] wrote: Oic - so it's a floating point in an 8 byte int. That probably limits the speed benefits, no? No, it's an int type. Floating point has nothing to do with the money type, either in the old 32 bit version or the proposed 64 bit version. It does display in a DECIMAL format but just because there is a decimal point in the output does not make it floating point. All internal calculations are done as integer arithmetic. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 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] New version of money type
On Thu, Sep 28, 2006 at 11:32:37AM -0400, D'Arcy J.M. Cain wrote: On Thu, 28 Sep 2006 11:25:45 -0400 Luke Lonergan [EMAIL PROTECTED] wrote: Oic - so it's a floating point in an 8 byte int. That probably limits the speed benefits, no? No, it's an int type. Floating point has nothing to do with the money type, either in the old 32 bit version or the proposed 64 bit version. It does display in a DECIMAL format but just because there is a decimal point in the output does not make it floating point. All internal calculations are done as integer arithmetic. Floating point math and hard-earned money are two things that don't mix well. :) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] New version of money type
On Thu, 28 Sep 2006 10:35:01 -0500 Jim C. Nasby [EMAIL PROTECTED] wrote: Floating point math and hard-earned money are two things that don't mix well. :) Using FP to track money is a good way to stop making any. :-) -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Constant changes (Re-Build)
Well Thanks you both for your answers, I really appreciate your opinions on this subject. And Heikki I would really like to take a look to that book. If I have the chance to read it I will tell you how the things go Ok... Greets and Thanks one more time..2006/9/27, Heikki Linnakangas [EMAIL PROTECTED]: luis garcia wrote: Hi I'm a student from Valencia-Venezuela and I'm working with some other friends to make PostgreSQL allows the definition of Temporal Databases and their respective Selection, Insertion and some other functions needed to treat this paradigm(all based in TSQL2 Query Language).That's interesting. May I suggest that you take a look at a book calledTemporal Data the Relational Model by C.J. Date, Hugh Darwin andNikos Lorentzos(http://www.amazon.com/Temporal-Relational-Kaufmann-Management-Systems/dp/1558608559 ).It describes the best approach I've seen this far to dealing withtemporal data. Right now we are working directly on the source code and making different changes during the day, so I'd like to ask you which is the better choice for re-building (I'm not sure if that is the right term) only the code files that I just have changed. I'm working on a Slow PC with not to many recourse, so every time I make (-configure/-make/-make-install/) i lose like 30 minutes of work, and I have been thinking in some other way to only re-configure the files I've recently changed.Well, you don't need to run configure every time you want to build. If you just run make, it will compile just the changes. I'd suggestrunning the configure with the --enable-depend option, so it picks upchanges in header files better.Also take a look at ccache ( http://ccache.samba.org/). And if you havemore PCs to spare, you might want to set up distcc.--Heikki LinnakangasEnterpriseDB http://www.enterprisedb.com-- Luis D. García M.Telf: 0414-3482018- FACYT - UC -- Computación -
Re: [HACKERS] Row IS NULL question
Teodor Sigaev [EMAIL PROTECTED] writes: % echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow SET count --- 0 (1 row) Hm, it turns out that this works: select * from int8_tbl x where row(x.q1,x.q2) is null; but not this: select * from int8_tbl x where row(x.*) is null; EXPLAIN tells the tale: regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is null; QUERY PLAN --- Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) Filter: ((q1 IS NULL) AND (q2 IS NULL)) (2 rows) regression=# explain select * from int8_tbl x where row(x.*) is null; QUERY PLAN --- Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) Filter: (x.* IS NULL) (2 rows) Apparently what's happening is that gram.y's makeRowNullTest() bursts the RowExpr apart into individual isnull tests. Now that RowExpr expansion can change the number of items in the row, it's clearly premature to do that processing in gram.y --- we should move it to parse analysis. Part of the issue is that ExecEvalNullTest simply tests for whether the presented Datum is null, which I think is impossible for a whole-row Var coming from a table (but it could happen for a row-returning function's result, for example). I think that according to the letter of the spec, an IS [NOT] NULL test should drill down into rowtype datums and check nullness of the individual row fields. Probably the same is true for array datums. Moving makeRowNullTest() doesn't seem like a big deal, but changing ExecEvalNullTest would take some added code. Do we want to tackle that during beta, or hold off till 8.3? An argument for doing it now is that we just added nulls-in-arrays in 8.2, and it'd be good if the semantics of that were right the first time rather than changing later. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Faster StrNCpy
Strong, David [EMAIL PROTECTED] writes: Just wondering - are any of these cases where a memcpy() would work just as well? Or are you not sure that the source string is at least 64 bytes in length? In most cases, we're pretty sure that it's *not* --- it'll just be a palloc'd C string. I'm disinclined to fool with the restriction that namestrcpy zero-pad Name values, because they might end up on disk, and allowing random memory contents to get written out is ungood from a security point of view. However, it's entirely possible that it'd be a bit faster to do a MemSet followed by strlcpy than to use strncpy for zero-padding. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Row IS NULL question
Please excuse me for jumping in like this... but just for my understanding... Does this have anything to do with ExecEvalWholeRowVar? On Thu, 2006-09-28 at 11:45 -0400, Tom Lane wrote: Teodor Sigaev [EMAIL PROTECTED] writes: % echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow SET count --- 0 (1 row) Hm, it turns out that this works: select * from int8_tbl x where row(x.q1,x.q2) is null; but not this: select * from int8_tbl x where row(x.*) is null; EXPLAIN tells the tale: regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is null; QUERY PLAN --- Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) Filter: ((q1 IS NULL) AND (q2 IS NULL)) (2 rows) regression=# explain select * from int8_tbl x where row(x.*) is null; QUERY PLAN --- Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) Filter: (x.* IS NULL) (2 rows) Apparently what's happening is that gram.y's makeRowNullTest() bursts the RowExpr apart into individual isnull tests. Now that RowExpr expansion can change the number of items in the row, it's clearly premature to do that processing in gram.y --- we should move it to parse analysis. Part of the issue is that ExecEvalNullTest simply tests for whether the presented Datum is null, which I think is impossible for a whole-row Var coming from a table (but it could happen for a row-returning function's result, for example). I think that according to the letter of the spec, an IS [NOT] NULL test should drill down into rowtype datums and check nullness of the individual row fields. Probably the same is true for array datums. Moving makeRowNullTest() doesn't seem like a big deal, but changing ExecEvalNullTest would take some added code. Do we want to tackle that during beta, or hold off till 8.3? An argument for doing it now is that we just added nulls-in-arrays in 8.2, and it'd be good if the semantics of that were right the first time rather than changing later. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Regards, Gevik Babakhani http://www.postgresql.nl http://www.truesoftware.nl ---(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] New version of money type
D'Arcy, On 9/28/06 8:43 AM, D'Arcy J.M. Cain darcy@druid.net wrote: On Thu, 28 Sep 2006 10:35:01 -0500 Jim C. Nasby [EMAIL PROTECTED] wrote: Floating point math and hard-earned money are two things that don't mix well. :) Using FP to track money is a good way to stop making any. :-) Understood - a cent here and there in rounding can add up, as the earliest hackers found out. What I meant is the use of the point that floats, is there an exponent stored as part of the format? If so, then the integer arithmetic is not standard integer operands, right? Which routines implement the money arithmetic? - Luke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Row IS NULL question
Gevik Babakhani [EMAIL PROTECTED] writes: Does this have anything to do with ExecEvalWholeRowVar? Yeah, the construct Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) Filter: (x.* IS NULL) is really ExecEvalNullTest applied to the result of ExecEvalWholeRowVar. If we simply push makeRowNullTest() to later in the parser, this case will work as expected, but there is still the issue of IS [NOT] NULL applied to rowtype values that are not coming from ROW() constructs, such as the result of a rowtype-returning function. Likewise, null tests on arrays really would have to be handled in the executor to work per spec --- we can hardly break them down into scalar isnull tests at parse time, which is what makeRowNullTest() is trying to do. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Another idea for dealing with cmin/cmax
In addition to/instead of abstracting cmin/cmax to a phantom ID, what about allowing for two versions of the tuple header, one with cid info and one without? That would allow for cid info to be stripped out when pages were written to disk. The downside to this is that we'd have to be able to deal with pages in-memory potentially being larger than pages on-disk. Since there's been discussion of separating on-disk and in-memory page formats, maybe that doesn't kill the proposal outright. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Another idea for dealing with cmin/cmax
Jim C. Nasby wrote: In addition to/instead of abstracting cmin/cmax to a phantom ID, what about allowing for two versions of the tuple header, one with cid info and one without? That would allow for cid info to be stripped out when pages were written to disk. How exactly would that help? You can't just strip out cid info when writing to disk, if you don't want to lose the information. And it's certainly a lot more complicated than the phantom id thing. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] -HEAD planner issue wrt hash_joins on dbt3 ?
Matteo Beccati wrote: Stefan Kaltenbrunner wrote: too bad - however any idea on one of the other troubling querys (q21) I mentioned in the mail I resent to the list (after the original one got lost)? http://archives.postgresql.org/pgsql-hackers/2006-09/msg02011.php What happens if you increase statistics for l_orderkey? statistic target is already at 1000 ... Stefan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] 'pg_ctl -w' times out when unix_socket_directory is
On Thu, 2006-09-28 at 10:05 -0400, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: I have attached a patch. I wrote it very quickly, but it seems to work as I expect. I don't think this is very workable as a postgresql.conf parser ... at minimum it needs to handle quoted strings correctly, and really it ought to deal with file inclusions. There are other reasons why pg_ctl needs to look at postgresql.conf --- it currently fails to cope with config-file-outside-the-datadir cases, and that can't be fixed except by extracting the data_directory setting from the config file. What we probably need to do is port guc-file.l into the pg_ctl environment. (Not sure if it's feasible to use a single source file for both cases, though that would be nice if not too messy.) And same for reading the port out of postgresql.conf, right? I was a little skeptical that the function would always work. However, to the extent that it looked for the correct port, it makes sense it should look at the correct unix_socket_directory also. I think my patch handles the simplest cases correctly (a simple single-quoted string), and I don't think it breaks anything. I can understand that we want a more correct solution to search for both parameters. I could make an attempt to port guc-file.l. However, if you were planning on fixing this for 8.2, I can't make any guarantees. It does seem like a bug to me, especially since the FreeBSD port of postgres uses pg_ctl -w in the startup script (and probably a lot of other platforms). Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Another idea for dealing with cmin/cmax
On Thu, Sep 28, 2006 at 05:13:11PM +0100, Heikki Linnakangas wrote: Jim C. Nasby wrote: In addition to/instead of abstracting cmin/cmax to a phantom ID, what about allowing for two versions of the tuple header, one with cid info and one without? That would allow for cid info to be stripped out when pages were written to disk. How exactly would that help? You can't just strip out cid info when writing to disk, if you don't want to lose the information. Erm, sorry, brainfart... yeah, we'd need to be able to write the info out to disk. The reason I thought of this is because once the transaction commits, we have no use for the cid info. So we could do something like have bgwriter look for tuples that belong to committed transactions before it writes a page, and strip the cid out of them. The problem with *that* is that (AFAIK) you'll need cid info again once you go to update or delete that tuple. And that might obviously need to spill to disk before the transaction commits. Back to the drawing board... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] New version of money type
D'Arcy, On 9/28/06 9:00 AM, Luke Lonergan [EMAIL PROTECTED] wrote: Which routines implement the money arithmetic? Ok - so now having read the old documentation and the routine backend/utils/adt/cash.c and the type definition for Cash in backend/include/utils/adt/cash.h I can see that it's: - Fixed point at NNN.MM - Stored as an INT32 (or your proposed INT64) - Operations use native operands (=+*/) The disappointing thing is that there is always a function call involved in any arithmetic operation. An even larger benefit could probably be gained by inlining the routines in cash.c, which is probably inhibited by the FUNCTIONCALLxxx indirections in the executor for operators (different topic). So, the NUMERIC arithmetic must be really slow to get 10% improvements in computational speed. Based on all of this, I know I would use the 64 bit money type for things like the TPC-H benchmark... - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] New version of money type
* Luke Lonergan ([EMAIL PROTECTED]) wrote: Though this may be the kiss of death, I favor a 64 bit float version of money. It's more terse than numeric and a *lot* faster when performing numeric operations because it would use a cpu intrinsic operand. What about just having a numeric64, or changing numeric to support moving to 64bit sizes when necessary and supported by the platform? Exactly how much faster would it *really* be? Have you tested it? At what point does it become a 'winning' change? I'm not sure about 'money' in general but these claims of great performance improvments over numeric just don't fly so easily with me. numeric isn't all *that* much slower than regular old integer in the tests that I've done. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Bitmap index status
Luke Lonergan wrote: Mark, On 9/25/06 11:32 AM, Mark Wong [EMAIL PROTECTED] wrote: Yeah, basically gather as many stats as I can to accurately profile the overall system performance. I thought it would be appropriate to use a TPC-H based workload as one measuring stick to use for bitmap indexes. Note that the TPC-H queries don't follow the typical good use case for bitmap indexes. You'd like to see queries that use multiple AND and OR clauses, otherwise there may be no benefit. Oh right, people keep telling me that and it keeps going in one ear and out the other... Also, DBT-3/TPC-H on Postgres right now does not benefit from indices overall. The planner has limitations WRT selectivity estimates and other limitations that cause it to choose index access poorly for the query workload. We have two new features coming (for 8.3) that fix this, but for now we find that indexes are a net loss, in some queries a huge loss. Great, I'll keep my eye for those. :) If you look at the whitepaper that Ayush Parashar published, he uses the TPC-H data with some targeted queries that showcase the best use-cases for bitmap index. Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New version of money type
Stephen, On 9/28/06 9:44 AM, Stephen Frost [EMAIL PROTECTED] wrote: I'm not sure about 'money' in general but these claims of great performance improvments over numeric just don't fly so easily with me. numeric isn't all *that* much slower than regular old integer in the tests that I've done. Part of the problem is the *size* of Numeric. I've just looked for something that describes the size of a Numeric and I saw an old post that says: 10 + x/2 bytes So, a minimum of 10 bytes (compared to the 8 proposed for money64) plus scale (x) divided by two. Currently on the TPC-H benchmark, Postgres requires 1.7 times the amount of internal database storage as what is in the ASCII data file representation. Oracle and MSFT SQLServer are almost 1:1. Part of this fluff is the 24 bytes of tuple header, part of it is in the Numeric. - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Row IS NULL question
On Thu, Sep 28, 2006 at 11:45:32AM -0400, Tom Lane wrote: Teodor Sigaev [EMAIL PROTECTED] writes: % echo 'SELECT count(*) FROM tst WHERE ROW(tst.*) IS NULL;' | psql wow SET count --- 0 (1 row) Hm, it turns out that this works: select * from int8_tbl x where row(x.q1,x.q2) is null; but not this: select * from int8_tbl x where row(x.*) is null; EXPLAIN tells the tale: regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is null; QUERY PLAN --- Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) Filter: ((q1 IS NULL) AND (q2 IS NULL)) (2 rows) regression=# explain select * from int8_tbl x where row(x.*) is null; QUERY PLAN --- Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) Filter: (x.* IS NULL) (2 rows) Apparently what's happening is that gram.y's makeRowNullTest() bursts the RowExpr apart into individual isnull tests. Now that RowExpr expansion can change the number of items in the row, it's clearly premature to do that processing in gram.y --- we should move it to parse analysis. Part of the issue is that ExecEvalNullTest simply tests for whether the presented Datum is null, which I think is impossible for a whole-row Var coming from a table (but it could happen for a row-returning function's result, for example). I think that according to the letter of the spec, an IS [NOT] NULL test should drill down into rowtype datums and check nullness of the individual row fields. Probably the same is true for array datums. Moving makeRowNullTest() doesn't seem like a big deal, but changing ExecEvalNullTest would take some added code. Do we want to tackle that during beta, or hold off till 8.3? An argument for doing it now is that we just added nulls-in-arrays in 8.2, and it'd be good if the semantics of that were right the first time rather than changing later. I think this qualifies as a bug fix and should go in 8.2 :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] New version of money type
On Thu, 28 Sep 2006 12:44:24 -0400 Stephen Frost [EMAIL PROTECTED] wrote: I'm not sure about 'money' in general but these claims of great performance improvments over numeric just don't fly so easily with me. numeric isn't all *that* much slower than regular old integer in the tests that I've done. Numeric has been shown to be as good or better than money in I/O operations. Where money shines is in internal calculations. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Coding style for emacs
Andrew Dunstan wrote: Bruce Momjian wrote: Andrew Dunstan wrote: I will try to draw all this together today or tomorrow. It's not only the FAQ that should be patched - the docs and the FAQ should agree with each other. Right. In fact, this info arguably belongs in one place only. Which should it be? Uh, if you put it in the docs, I can reference it from the FAQ, but not the other way around, so I think the documentation is best. Looking at this further, I am wondering if it would not be better to put sample .emacs and .vimrc files in the source (in, say, src.tools). The docs/FAQ would just say that we use BSD style with tab space 4 and refer to the sample files. Andrew, I am still waiting for the emacs startup script modification patch. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] TM modificator don't work? to_char
Hello, I try 8.2 features. I tested to_char from doc, but without success. postgres=# select to_char(now(), 'TMDay, DD TMMonth '); to_char - Thursday, 28 September 2006 (1 row) I expected translated names :-(. What can be wrong. lc_collate | cs_CZ.UTF-8 | Shows the collation order locale. lc_ctype| cs_CZ.UTF-8 | Shows the character classification and case conversion locale. lc_messages | cs_CZ.UTF-8 | Sets the language in which messages are displayed. lc_monetary | cs_CZ.UTF-8 | Sets the locale for formatting monetary amounts. lc_numeric | cs_CZ.UTF-8 | Sets the locale for formatting numbers. lc_time | cs_CZ.UTF-8 Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Coding style for emacs
Bruce Momjian wrote: Looking at this further, I am wondering if it would not be better to put sample .emacs and .vimrc files in the source (in, say, src.tools). The docs/FAQ would just say that we use BSD style with tab space 4 and refer to the sample files. Andrew, I am still waiting for the emacs startup script modification patch. Sorry, found it applied. My mistake. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] JAVA Support
It appears that the JDBC client doesn't include the Kerberos support that the C clients do. So, two questions: 1) Is there an alternative JDBC client that's just a glue layer instead of a complete re-implementation? 2) If I were willing to add a GSSAPI or SASL layer as an alternative to the bare Krb 5 support would anyone be willing to help with the supporting mods to the pg_hba.conf parsing, and configure? The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] JAVA Support
On Thu, 28 Sep 2006, Henry B. Hotz wrote: It appears that the JDBC client doesn't include the Kerberos support that the C clients do. Java doesn't have accessible Kerberos support. It wraps Kerberos in GSSAPI which requires the server to support GSSAPI instead of plain Kerberos. So, two questions: 1) Is there an alternative JDBC client that's just a glue layer instead of a complete re-implementation? No, there aren't any Type 2 drivers around. Requiring native code is a giant pain. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] contrib uninstall scripts need some love
Joshua D. Drake wrote: Guido Barosio wrote: Let me know if you need an extra pair of eyes. O.k. I do :) Writing the scripts up are easy enough, but I am unsure how the whole make file foo works... OK, are all the uninstall scripts done? --- Joshua D. Drake G.- On 9/10/06, Joshua D. Drake [EMAIL PROTECTED] wrote: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Seems like this area needs more attention ... anyone want to work on it? I'll take it. How long do I have? Since it's contrib, I don't think we need to hold you to being done before beta1. But the sooner the better of course. O.k., I will start working through it and report at the end of the week. Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] New version of money type
* D'Arcy J.M. Cain (darcy@druid.net) wrote: On Thu, 28 Sep 2006 12:44:24 -0400 Stephen Frost [EMAIL PROTECTED] wrote: I'm not sure about 'money' in general but these claims of great performance improvments over numeric just don't fly so easily with me. numeric isn't all *that* much slower than regular old integer in the tests that I've done. Numeric has been shown to be as good or better than money in I/O operations. Where money shines is in internal calculations. Which may be an area which could be improved on for numeric, or even a numeric64 type added for it. I'm not entirely sure there's a huge amount to gain there either though... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] contrib uninstall scripts need some love
Bruce Momjian wrote: Joshua D. Drake wrote: Guido Barosio wrote: Let me know if you need an extra pair of eyes. O.k. I do :) Writing the scripts up are easy enough, but I am unsure how the whole make file foo works... OK, are all the uninstall scripts done? Crap... yes they are, at least the ones I know about. I will submit a patch again -HEAD today. Joshua D. Drake --- Joshua D. Drake G.- On 9/10/06, Joshua D. Drake [EMAIL PROTECTED] wrote: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Seems like this area needs more attention ... anyone want to work on it? I'll take it. How long do I have? Since it's contrib, I don't think we need to hold you to being done before beta1. But the sooner the better of course. O.k., I will start working through it and report at the end of the week. Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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] Row IS NULL question
I wrote: Moving makeRowNullTest() doesn't seem like a big deal, but changing ExecEvalNullTest would take some added code. Do we want to tackle that during beta, or hold off till 8.3? An argument for doing it now is that we just added nulls-in-arrays in 8.2, and it'd be good if the semantics of that were right the first time rather than changing later. Now that I look more closely, makeRowNullTest() is actually WRONG even for the cases it handles. SQL99/SQL2003 define null predicate thus: 1) Let R be the value of the row value expression. 2) If every value in R is the null value, then R IS NULL is true; otherwise, it is false. 3) If no value in R is the null value, then R IS NOT NULL is true; otherwise, it is false. makeRowNullTest() is set up to return TRUE for an IS NOT NULL test if *any* element of R is non null: regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is not null; QUERY PLAN --- Seq Scan on int8_tbl x (cost=0.00..1.05 rows=5 width=16) Filter: ((q1 IS NOT NULL) OR (q2 IS NOT NULL)) (2 rows) So this is clearly a bug and clearly one of long standing --- we've been getting this wrong since PG 7.3 :-( regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] New version of money type
D'Arcy, On 9/28/06 10:12 AM, D'Arcy J.M. Cain darcy@druid.net wrote: Numeric has been shown to be as good or better than money in I/O operations. What exactly does that mean in the context of a Datum: I/O operations? - Luke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] JAVA Support
On Sep 28, 2006, at 10:52 AM, Kris Jurka wrote: On Thu, 28 Sep 2006, Henry B. Hotz wrote: It appears that the JDBC client doesn't include the Kerberos support that the C clients do. Java doesn't have accessible Kerberos support. It wraps Kerberos in GSSAPI which requires the server to support GSSAPI instead of plain Kerberos. Looks like Kerberos is the only GSSAPI mechanism supported in Java. OK by me, but that's not the point of the standard (or the SASL standard). So, two questions: 1) Is there an alternative JDBC client that's just a glue layer instead of a complete re-implementation? No, there aren't any Type 2 drivers around. Requiring native code is a giant pain. Kris Jurka Requiring JAVA support for everything you can do with C is also a pain, isn't it? (This incompatibility being an example.) I take it you're not volunteering to help with my second request. ;-) The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Paging Phil Dodderidge
There's an old post from Phil about having GSSAPI support almost working with PostgreSQL. I'd like to ask him about his work, but the email link in the archives doesn't work. The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Row IS NULL question
Thank you for the explanation. On Thu, 2006-09-28 at 12:06 -0400, Tom Lane wrote: Gevik Babakhani [EMAIL PROTECTED] writes: Does this have anything to do with ExecEvalWholeRowVar? Yeah, the construct Seq Scan on int8_tbl x (cost=0.00..1.05 rows=1 width=16) Filter: (x.* IS NULL) is really ExecEvalNullTest applied to the result of ExecEvalWholeRowVar. If we simply push makeRowNullTest() to later in the parser, this case will work as expected, but there is still the issue of IS [NOT] NULL applied to rowtype values that are not coming from ROW() constructs, such as the result of a rowtype-returning function. Likewise, null tests on arrays really would have to be handled in the executor to work per spec --- we can hardly break them down into scalar isnull tests at parse time, which is what makeRowNullTest() is trying to do. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Regards, Gevik Babakhani http://www.postgresql.nl http://www.truesoftware.nl ---(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] New version of money type
On Thu, Sep 28, 2006 at 11:39:31AM -0700, Luke Lonergan wrote: D'Arcy, On 9/28/06 10:12 AM, D'Arcy J.M. Cain darcy@druid.net wrote: Numeric has been shown to be as good or better than money in I/O operations. What exactly does that mean in the context of a Datum: I/O operations? Converting to/from text format for when dealing with client applications. Numeric can convert faster than plain integers sometimes. Numeric isn't that slow really... Have a ncie 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. signature.asc Description: Digital signature
[HACKERS] Numeric overflow problem + patch
Folks, Dennis Björklund and I discovered a little problem with how CVS TIP reports overflows on cast. Please find enclosed a patch which fixes it. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Index: src/backend/utils/adt/numeric.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/numeric.c,v retrieving revision 1.94 diff -c -r1.94 numeric.c *** src/backend/utils/adt/numeric.c 14 Jul 2006 05:28:28 - 1.94 --- src/backend/utils/adt/numeric.c 28 Sep 2006 18:47:31 - *** *** 3217,3227 ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), errmsg(numeric field overflow), !errdetail(A field with precision %d, scale %d must have an absolute value less than %s%d., precision, scale, /* Display 10^0 as 1 */ maxdigits ? 10^ : , ! maxdigits ? maxdigits : 1))); break; } ddigits -= DEC_DIGITS; --- 3217,3230 ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), errmsg(numeric field overflow), !errdetail(A field with precision %d, scale %d must have an absolute value less than %s%d - 5 * %s%d., precision, scale, /* Display 10^0 as 1 */ maxdigits ? 10^ : , ! maxdigits ? maxdigits : 1, ! 10^-, ! scale + 1 ! ))); break; } ddigits -= DEC_DIGITS; Index: src/test/regress/expected/numeric.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/numeric.out,v retrieving revision 1.18 diff -c -r1.18 numeric.out *** src/test/regress/expected/numeric.out 25 Jan 2006 18:20:22 - 1.18 --- src/test/regress/expected/numeric.out 28 Sep 2006 18:47:31 - *** *** 688,699 INSERT INTO fract_only VALUES (2, '0.1'); INSERT INTO fract_only VALUES (3, '1.0'); -- should fail ERROR: numeric field overflow ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. INSERT INTO fract_only VALUES (4, '-0.'); INSERT INTO fract_only VALUES (5, '0.4'); INSERT INTO fract_only VALUES (6, '0.5'); -- should fail ERROR: numeric field overflow ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. INSERT INTO fract_only VALUES (7, '0.1'); INSERT INTO fract_only VALUES (8, '0.00017'); SELECT * FROM fract_only; --- 688,699 INSERT INTO fract_only VALUES (2, '0.1'); INSERT INTO fract_only VALUES (3, '1.0'); -- should fail ERROR: numeric field overflow ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. INSERT INTO fract_only VALUES (4, '-0.'); INSERT INTO fract_only VALUES (5, '0.4'); INSERT INTO fract_only VALUES (6, '0.5'); -- should fail ERROR: numeric field overflow ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. INSERT INTO fract_only VALUES (7, '0.1'); INSERT INTO fract_only VALUES (8, '0.00017'); SELECT * FROM fract_only; ---(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] New version of money type
Martijn, On 9/28/06 11:53 AM, Martijn van Oosterhout kleptog@svana.org wrote: Converting to/from text format for when dealing with client applications. Numeric can convert faster than plain integers sometimes. Numeric isn't that slow really... Got it - so the performance benefits of the fixed point versus Numeric are: - Smaller size of fixed point (less than half) - Faster arithmetic operations These should be quantified, so that we can evaluate Money64 as a proposal and to understand Numeric better. - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] JAVA Support
On Thu, 28 Sep 2006, Henry B. Hotz wrote: I take it you're not volunteering to help with my second request. ;-) I would if we could get some -hackers buy in on the idea. Adding more and more auth methods is something they're not excited about unless there's a good reason (which I think this is). Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] New version of money type
On Thu, Sep 28, 2006 at 11:57:10AM -0700, Luke Lonergan wrote: Got it - so the performance benefits of the fixed point versus Numeric are: - Smaller size of fixed point (less than half) - Faster arithmetic operations These should be quantified, so that we can evaluate Money64 as a proposal and to understand Numeric better. However, none of this seems to deal with the major problems with the money type right now: - Only supports one currency (dollars) - Only supports one scale (yen has no decimal normally, but what if you want to track hundredths of a dollar-cent?) My question, what is this Money64 type buying you over just storing an integer in your database? There should be some value-add somewhere, but what is it? I've written applications tracking money using just an integer, if there were a special money type, I'd expect it to do something more. Have a ncie 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. signature.asc Description: Digital signature
Re: [HACKERS] JAVA Support
2) If I were willing to add a GSSAPI or SASL layer as an alternative to the bare Krb 5 support would anyone be willing to help with the supporting mods to the pg_hba.conf parsing, and configure? Sure, I can help out with that. I've done a bunch of work on the current kerberos stuff (tohugh I'm by no means the author) in order to make it work on win32, so I have a little bit of a clue around that code ATM. As for the other part - will core accept this - I can't answer that. I do beleive that there is a point to it, given that Java will then support it natively, but I'm not core. I'm unsure if there is a clear view on the merits of adding more authentication options.. //Magnus ---(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] New version of money type
Martijn, On 9/28/06 12:42 PM, Martijn van Oosterhout kleptog@svana.org wrote: - Only supports one currency (dollars) What are the manifestations of this? - Only supports one scale (yen has no decimal normally, but what if you want to track hundredths of a dollar-cent?) So, without a quantified benefit, this is certainly a non-starter. - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New version of money type
On Thu, Sep 28, 2006 at 01:29:57PM -0700, Luke Lonergan wrote: Martijn, On 9/28/06 12:42 PM, Martijn van Oosterhout kleptog@svana.org wrote: - Only supports one currency (dollars) What are the manifestations of this? test=# select '100'::money; money - $100.00 (1 row) The use of the dollar sign and the two decimal places make it wrong for the vast majority of the world's population. Now, there is some localization involved, so you can play tricks like: test=# set lc_monetary ='nl_NL'; SET test=# select '100'::money; money --- EUR100,00 (1 row) Oops, by changing a GUC variable we just changed the semantic value of every currency field in the database. You still can't change the number of decimal places though. - Only supports one scale (yen has no decimal normally, but what if you want to track hundredths of a dollar-cent?) So, without a quantified benefit, this is certainly a non-starter. Every new type needs to have a well-defined use-case before it can be considered for includion. Currently we have: - Is possibly faster than numeric - Takes less space than numeric - Customisable output (only one currency at a time though) - Fixed number of decimal places What else? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] Darwin stuff is getting deprecated
With the latest Apple developers' tools, I get some warnings that weren't there before: dynloader.c: In function 'pg_dlsym': dynloader.c:44: warning: 'NSIsSymbolNameDefined' is deprecated (declared at /usr/include/mach-o/dyld.h:150) dynloader.c:46: warning: 'NSLookupAndBindSymbol' is deprecated (declared at /usr/include/mach-o/dyld.h:158) postmaster.c: In function 'PostmasterMain': postmaster.c:779: warning: 'DNSServiceRegistrationCreate' is deprecated (declared at /usr/include/DNSServiceDiscovery/DNSServiceDiscovery.h:114) Anyone want to look into this and see what we're supposed to do instead? The postmaster.c message is in the USE_BONJOUR code. There was already some discussion about how we were using an obsolete Bonjour API and we ought to rewrite to support using Bonjour on non-Darwin platforms. Perhaps Apple is now using a compatible API? 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] JAVA Support
On Sep 28, 2006, at 12:42 PM, Magnus Hagander wrote: 2) If I were willing to add a GSSAPI or SASL layer as an alternative to the bare Krb 5 support would anyone be willing to help with the supporting mods to the pg_hba.conf parsing, and configure? Sure, I can help out with that. I've done a bunch of work on the current kerberos stuff (tohugh I'm by no means the author) in order to make it work on win32, so I have a little bit of a clue around that code ATM. As for the other part - will core accept this - I can't answer that. I do beleive that there is a point to it, given that Java will then support it natively, but I'm not core. I'm unsure if there is a clear view on the merits of adding more authentication options.. From the lack of traffic on this list I gather that the core developers no longer hang out here. I've been gone for a few years. For the record here's the arguments: SASL is a standards track RFC (I saw those snide comments in the record, Mr. Lane ;-) which allows you to plug in authentication mechanisms, much like PAM allows you to plug in password checkers. It is well adopted, since it forms the basis of most email protocols' authentication, as well as LDAP and Jabber. SASL provides a unified way for code to support all the authentication options you're likely to want. a) In the absence of OS-provided SASL libraries a simple password- checking mechanism could be implemented as a wire-compatible fallback with less code than the framework would take. (I won't write this, but you could probably steal code from jabberd.) b) SASL includes simple password checking mechanisms. In principle we could use these to check the local postgres passwords. Not sure how much customization that would require. c) If you are using SSL/TLS for client/server connections (or it's a local on-machine connection) you can use the SASL_EXTERNAL mechanism to pick up the identity from the connection, without imposing extra overhead. d) SASL includes enterprise-class authentication support, such as GSSAPI (and Kerberos via GSSAPI). If an enterprise has some unique authentication infrastructure it can be implemented as a SASL (or GSSAPI) plug-in without the need to customize PostgreSQL. e) After the initial connection, SASL can be configured to run the connection fully encrypted, integrity protected, or unprotected. f) SASL support is available in current Java as well as C. SASL libraries are included (or at least loadable) on MacOS, Solaris 10+, and Linux. (I don't do windows, so I can't say there.) While it has a reputation for complexity, that complexity is in building the libraries, not in using them. It can be used to provide most (all?) of the functionality now provided by the assortment of existing mechanisms. If provided as an alternative, it could eventually allow decommissioning of a lot of the other mechanisms. If the number of existing mechanisms is an issue, then this could be a big long-term win. I'll assume the ball is in my court now, unless someone wants to claim I should just do GSSAPI and not bother with the higher level. The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] New version of money type
On Thu, 28 Sep 2006 11:39:31 -0700 Luke Lonergan [EMAIL PROTECTED] wrote: Numeric has been shown to be as good or better than money in I/O operations. What exactly does that mean in the context of a Datum: I/O operations? It means that numeric is better and parsing/storing/displaying than money. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] JAVA Support
As for the other part - will core accept this - I can't answer that. I do beleive that there is a point to it, given that Java will then support it natively, but I'm not core. I'm unsure if there is a clear view on the merits of adding more authentication options.. From the lack of traffic on this list I gather that the core developers no longer hang out here. I've been gone for a few years. Oh, they most definitly do. Every one of them. They just don't write in every single thread (though sometimes I wonder about Tom..) I noticed you copied Tom Lockhart - he isn't core anymore, so don't expect a response there. Tom Lane is, though, and I'm sure he'll respond. For the record here's the arguments: snip a bunch of SASL arguments that I'm sure are perfectly valid f) SASL support is available in current Java as well as C. SASL libraries are included (or at least loadable) on MacOS, Solaris 10+, and Linux. (I don't do windows, so I can't say there.) While it has a reputation for complexity, that complexity is in building the libraries, not in using them. It can be used to provide most (all?) of the functionality now provided by the assortment of existing mechanisms. Well, it's still a complexity you need to deal with. Plus, just Java and C is far from enough, if you are intending to suggest we replace some of what we have now with it (like passwords and other such things). For example, you need things like perl, python, ruby, C#, etc etc. not sure how many of those would be fine with a C wrapper, I know for a fact that C# (or other .net languages) wouldn't, they need it natively. There also used to be some bad portability issues wrt at least some of the SASL libraries (if there is more than one). I know I tried to make it work on win32 once and failed miserably. (Then again, I've failed on Linux as well, but not quite as bad. And it's not included in all Linux distributions, at least it wasn't when I checked a while back) And finally, there's backwards compatibility. We're still going to have to support all the existing ones for the forseeable future unless you want to prevent all older clients from connecting (hint: you don't). If provided as an alternative, it could eventually allow decommissioning of a lot of the other mechanisms. If the number of existing mechanisms is an issue, then this could be a big long-term win. Me, I think providing it as an alternative is the path to go. Which also means that I think implementing GSSAPI for that (probably in long-term to *replace* our current Kerberos authentication, in short-term to complement it) rather than SASL, because it's significantly simpler. I'll assume the ball is in my court now, unless someone wants to claim I should just do GSSAPI and not bother with the higher level. That would be my suggestion - do GSSAPI only and leave the current methods the way they are. This should be doable without a huge amount of code, and without affecting the other well-working mechanisms. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Darwin stuff is getting deprecated
The regular dlfcn family of functions are now properly implemented on Darwin (10.4+). Re: Zeroconf- Avahi is API+ABI compatible with the Apple API, so its use would be preferable on Linux (for cross-platform considerations). http://avahi.org/browser/trunk/avahi-compat-libdns_sd [Warning: Avahi-GPL] -M On Sep 28, 2006, at 17:02 , Tom Lane wrote: With the latest Apple developers' tools, I get some warnings that weren't there before: dynloader.c: In function 'pg_dlsym': dynloader.c:44: warning: 'NSIsSymbolNameDefined' is deprecated (declared at /usr/include/mach-o/dyld.h:150) dynloader.c:46: warning: 'NSLookupAndBindSymbol' is deprecated (declared at /usr/include/mach-o/dyld.h:158) postmaster.c: In function 'PostmasterMain': postmaster.c:779: warning: 'DNSServiceRegistrationCreate' is deprecated (declared at /usr/include/DNSServiceDiscovery/ DNSServiceDiscovery.h:114) Anyone want to look into this and see what we're supposed to do instead? The postmaster.c message is in the USE_BONJOUR code. There was already some discussion about how we were using an obsolete Bonjour API and we ought to rewrite to support using Bonjour on non-Darwin platforms. Perhaps Apple is now using a compatible API? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] New version of money type
On Thu, 28 Sep 2006 22:53:34 +0200 Martijn van Oosterhout kleptog@svana.org wrote: Every new type needs to have a well-defined use-case before it can be considered for includion. Well, it is already included. The current proposal is simply to improve the existing type. I guess you are arguing a different proposal altogether - to remove the existing type. Currently we have: - Is possibly faster than numeric I suppose I should quantify this but it's hard to get motivated after the many man-hours (mine and my staff) I had to spend on code and schema optimizations I needed to do just to get closer to the previous speed our aps had before we converted from money to numeric. I will try to find time to put together a test that appoximates that real world example. - Takes less space than numeric Never really considered this a major improvement over numeric given the cost of disk these days. I suppose it could be contributing to the speed increase. - Customisable output (only one currency at a time though) - Fixed number of decimal places The original code actually handled number of decimal places. It tended to cause problems though. These are areas that the existing type, as well as the proposed change, could be worked on. I would hesitate to work on both together though and going to 64bit will probably add more value right now than those things, certainly for existing users of the type. By the way, the current proposal actually removes the currency symbol but I have received complaints about that. It should probably go back just because it is outside of the scope of the primary change. That can be dealt with later. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] JAVA Support
Magnus Hagander [EMAIL PROTECTED] writes: As for the other part - will core accept this - I can't answer that. It would depend in part on the size of the patch, and on whether there are any arguments for supporting GSSAPI besides Java can't do Kerberos. What would it buy for a libpq user? 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] JAVA Support
As for the other part - will core accept this - I can't answer that. It would depend in part on the size of the patch, and on whether there are any arguments for supporting GSSAPI besides Java can't do Kerberos. What would it buy for a libpq user? I don't know, really ;-) It seems we're fairly alone in *not* doing GSSAPI (given for example the MIT Kerberos bug I uncovered when working on it, that was at the very core of the codepath we're using, which shows that others arne't using that). We'd be using a much better tested code, I think. It *may* be that life on win32 would be much easier, given that Windows SSPI is supposed to be compatible with GSSAPI when used in the right way. I don't know any details about this, though. If it does, it would likely make life easier for .NET applications as well, not just Java. I'll leave it to Henry to add some more arguments :-) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] JAVA Support
Kris, I would if we could get some -hackers buy in on the idea. Adding more and more auth methods is something they're not excited about unless there's a good reason (which I think this is). Actually, I've been trying to get some of the Sun engineers to contribute patches for Solaris authentication methods, of which GSSAPI is one. So in theory someone from Sun should be looking at coding this. Josh Berkus PostgreSQL @ Sun San Francisco 415-752-2500 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] JAVA Support
Tom, It would depend in part on the size of the patch, and on whether there are any arguments for supporting GSSAPI besides Java can't do Kerberos. What would it buy for a libpq user? According to the Solaris Security engineers, GSSAPI is more secure than using the Kerberos headers. Also, in theory GSSAPI is supposed to support multiple authentication back-ends (ldap, liberty, etc.), but I personally have never seen support for anything but Kerberos. Josh Berkus PostgreSQL @ Sun San Francisco 415-752-2500 ---(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
[HACKERS] Patch: Tie stats options to autovacuum in postgresql.conf
PostgreSQLers, I just ran into an issue where a client thought that autovacuum was running but it wasn't. This is because it's not fatal when autovacuum is on but stats_start_collector and/or stats_row_level is off. I suspect that there's a reason that it's not fatal, so I thought that it might be useful to give folks just a little bit of help by telling them in postgresql.conf that they need to enable them in order for autovacuum to work. If this patch is not correctly formatted or against the proper file, please let me know and I'll make the necessary modifications. Thanks, David vacuam_stats.patch Description: Binary data ---(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] Row IS NULL question
I wrote: Moving makeRowNullTest() doesn't seem like a big deal, but changing ExecEvalNullTest would take some added code. Do we want to tackle that during beta, or hold off till 8.3? An argument for doing it now is that we just added nulls-in-arrays in 8.2, and it'd be good if the semantics of that were right the first time rather than changing later. Now that I look more closely, makeRowNullTest() is actually WRONG even for the cases it handles. SQL99/SQL2003 define null predicate thus: 1) Let R be the value of the row value expression. 2) If every value in R is the null value, then R IS NULL is true; otherwise, it is false. 3) If no value in R is the null value, then R IS NOT NULL is true; otherwise, it is false. makeRowNullTest() is set up to return TRUE for an IS NOT NULL test if *any* element of R is non null: regression=# explain select * from int8_tbl x where row(x.q1,x.q2) is not null; QUERY PLAN --- Seq Scan on int8_tbl x (cost=0.00..1.05 rows=5 width=16) Filter: ((q1 IS NOT NULL) OR (q2 IS NOT NULL)) (2 rows) So this is clearly a bug and clearly one of long standing --- we've been getting this wrong since PG 7.3 :-( regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Would it be correct to do the null test in ExecEvalNullTest (I would like to learn what happens but somehow I just cannot get the debugger on makeRowNullTest in gram.c) On Thu, 2006-09-28 at 14:38 -0400, Tom Lane wrote: -- Regards, Gevik Babakhani http://www.postgresql.nl http://www.truesoftware.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] JAVA Support
On Sep 28, 2006, at 2:24 PM, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: As for the other part - will core accept this - I can't answer that. It would depend in part on the size of the patch, and on whether there are any arguments for supporting GSSAPI besides Java can't do Kerberos. What would it buy for a libpq user? Everything that the current Kerberos support provides plus Java. Ability to encrypt or integrity protect the client/server connection (without SSL/TLS tunnels). In theory, you get to plug in other mechanisms than Kerberos. In practice I think this only worked on Solaris, until very recently. The free gssapi implementations in Java, Solaris, MIT Kerberos, and Heimdal Kerberos only supported Kerberos. Sun open sourced their mechanism glue code and it's being incorporated into both MIT (now) and Heimdal (0.8). Entrust sells a PKI mechanism for Solaris. Wire compatibility with a native Windows API (the SSPI), if it's used correctly. (Google for posts by Jeffrey Altman for references to example code.) The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] JAVA Support
I cc'ed Tom Lockhart because he *used* to be core, and I know where he works. No response expected. On Sep 28, 2006, at 2:11 PM, Magnus Hagander wrote: f) SASL support is available in current Java as well as C. SASL libraries are included (or at least loadable) on MacOS, Solaris 10+, and Linux. (I don't do windows, so I can't say there.) While it has a reputation for complexity, that complexity is in building the libraries, not in using them. It can be used to provide most (all?) of the functionality now provided by the assortment of existing mechanisms. Well, it's still a complexity you need to deal with. Plus, just Java and C is far from enough, if you are intending to suggest we replace some of what we have now with it (like passwords and other such things). For example, you need things like perl, python, ruby, C#, etc etc. not sure how many of those would be fine with a C wrapper, I know for a fact that C# (or other .net languages) wouldn't, they need it natively. OK, point taken. OTOH how many of those have GSSAPI support? I don't know, but I'd guess that only going as far as GSSAPI gets you C# (and .net), and Java of course. Perl probably isn't a big deal just using glue for either SASL or GSSAPI. Python and Ruby I don't know. There also used to be some bad portability issues wrt at least some of the SASL libraries (if there is more than one). There's more than one, since the Java one is different from Cyrus. I've seen references to others, but I think they qualify as obscure. The Sun one is related to Cyrus. I know I tried to make it work on win32 once and failed miserably. (Then again, I've failed on Linux as well, but not quite as bad. And it's not included in all Linux distributions, at least it wasn't when I checked a while back) Well, I know Redhat has RPM's that look reasonable. I'm not a big Linux user myself. (More a BSD bigot, to be honest.) And finally, there's backwards compatibility. We're still going to have to support all the existing ones for the forseeable future unless you want to prevent all older clients from connecting (hint: you don't). No question. Just a thought for the future. The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] JAVA Support
On Sep 28, 2006, at 3:03 PM, Josh Berkus wrote: Tom, It would depend in part on the size of the patch, and on whether there are any arguments for supporting GSSAPI besides Java can't do Kerberos. What would it buy for a libpq user? According to the Solaris Security engineers, GSSAPI is more secure than using the Kerberos headers. Also, in theory GSSAPI is supposed to support multiple authentication back-ends (ldap, liberty, etc.), but I personally have never seen support for anything but Kerberos. I think that GSSAPI is more tolerant of connections through NAT's. I think it's more robust to current network reality, but I'm not aware it's actually more secure if you're using comparable verification options. As noted elsewhere on this thread it's more available. The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(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] Numeric overflow problem + patch
David Fetter [EMAIL PROTECTED] writes: ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. [ becomes ] ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. This strikes me as overly pedantic. The message needs to be clear, and the proposed change will just confuse people. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Numeric overflow problem + patch
On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. [ becomes ] ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. This strikes me as overly pedantic. The message needs to be clear, and the proposed change will just confuse people. I don't know if the code can detect the difference, but a message like: A field with precision 4, scale 4 must *round to* an absolute value less than 1 Since that more accurately describes the actual problem. Have a ncie 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. signature.asc Description: Digital signature
Re: [HACKERS] Numeric overflow problem + patch
On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. [ becomes ] ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. This strikes me as overly pedantic. The message needs to be clear, and the proposed change will just confuse people. It might, but the error that's currently in there is wrong. With the patch applied, you get: postgres=# SELECT .5::NUMERIC(4,4); ERROR: numeric field overflow DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. postgres=# SELECT .4999::NUMERIC(4,4); numeric - 0. (1 row) I'd thought of changing it to the corresponding numeric piece, but this doesn't work so well for NUMERIC(16,8) and the like. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Numeric overflow problem + patch
On Thu, Sep 28, 2006 at 11:16:56PM +0200, Martijn van Oosterhout wrote: On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. [ becomes ] ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. This strikes me as overly pedantic. The message needs to be clear, and the proposed change will just confuse people. I don't know if the code can detect the difference, but a message like: A field with precision 4, scale 4 must *round to* an absolute value less than 1 What does .999 round to? How about .5? Since that more accurately describes the actual problem. I'd say it doesn't, as worded. Maybe some other wording would be clearer. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] New version of money type
On Thu, Sep 28, 2006 at 05:19:47PM -0400, D'Arcy J.M. Cain wrote: On Thu, 28 Sep 2006 22:53:34 +0200 Martijn van Oosterhout kleptog@svana.org wrote: Every new type needs to have a well-defined use-case before it can be considered for includion. Well, it is already included. The current proposal is simply to improve the existing type. I guess you are arguing a different proposal altogether - to remove the existing type. The existing type is depricated and has been since at least 8.1; so yes, it's slated for removal. Currently we have: - Is possibly faster than numeric I suppose I should quantify this but it's hard to get motivated after the many man-hours (mine and my staff) I had to spend on code and schema optimizations I needed to do just to get closer to the previous speed our aps had before we converted from money to numeric. I will try to find time to put together a test that appoximates that real world example. - Takes less space than numeric Never really considered this a major improvement over numeric given the cost of disk these days. I suppose it could be contributing to the speed increase. Less space == more speed - Customisable output (only one currency at a time though) - Fixed number of decimal places The original code actually handled number of decimal places. It tended to cause problems though. These are areas that the existing type, as well as the proposed change, could be worked on. I would hesitate to work on both together though and going to 64bit will probably add more value right now than those things, certainly for existing users of the type. By the way, the current proposal actually removes the currency symbol but I have received complaints about that. It should probably go back just because it is outside of the scope of the primary change. That can be dealt with later. Perhaps a good compromise would be to call your type 'USD' or something similar. I can see where there's use for it, but it seems too limited to consider it a generic money type. What would be ideal is a money type that stored what currency was used and let you change precision (within reason). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Patch: Tie stats options to autovacuum in postgresql.conf
On Thu, Sep 28, 2006 at 03:07:39PM -0700, David Wheeler wrote: PostgreSQLers, I just ran into an issue where a client thought that autovacuum was running but it wasn't. This is because it's not fatal when autovacuum is on but stats_start_collector and/or stats_row_level is off. I suspect that there's a reason that it's not fatal, so I thought that it might be useful to give folks just a little bit of help by telling them in postgresql.conf that they need to enable them in order for autovacuum to work. +1. I was just at a client today that had run into this problem. Actually, I'm in favor of refusing to start if autovac is on but the proper stats settings aren't. I'd rather that then people ending up with bloated databases and crappy performance. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Patch: Tie stats options to autovacuum in postgresql.conf
On Sep 28, 2006, at 16:39, Jim C. Nasby wrote: +1. I was just at a client today that had run into this problem. Actually, I'm in favor of refusing to start if autovac is on but the proper stats settings aren't. I'd rather that then people ending up with bloated databases and crappy performance. I agree, but I figured that this was a start, at least. Best, David ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] JAVA Support
Henry, Sun demonstrated that you could build the existing Kerberos support with the current Solaris 11 beta's. They opened the native MIT Kerberos API for outside use. Yes, and this will be available via the supported version in Solaris 10 Update 4. However, that doesn't change that some people would like us to support GSSAPI, and there may be some benefit (additional applications, better network authentication, etc.) for doing so. If we can get additional programmers to code the support (i.e. Sun, JPL) I don't see any reason not to support the *additional* authentication methods. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] JAVA Support
On Sep 28, 2006, at 3:01 PM, Josh Berkus wrote: Kris, I would if we could get some -hackers buy in on the idea. Adding more and more auth methods is something they're not excited about unless there's a good reason (which I think this is). Actually, I've been trying to get some of the Sun engineers to contribute patches for Solaris authentication methods, of which GSSAPI is one. So in theory someone from Sun should be looking at coding this. Sun demonstrated that you could build the existing Kerberos support with the current Solaris 11 beta's. They opened the native MIT Kerberos API for outside use. See posts on the [ports] list. The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(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] Patch: Tie stats options to autovacuum in postgresql.conf
Jim C. Nasby wrote: On Thu, Sep 28, 2006 at 03:07:39PM -0700, David Wheeler wrote: PostgreSQLers, I just ran into an issue where a client thought that autovacuum was running but it wasn't. This is because it's not fatal when autovacuum is on but stats_start_collector and/or stats_row_level is off. I suspect that there's a reason that it's not fatal, so I thought that it might be useful to give folks just a little bit of help by telling them in postgresql.conf that they need to enable them in order for autovacuum to work. +1. I was just at a client today that had run into this problem. Actually, I'm in favor of refusing to start if autovac is on but the proper stats settings aren't. I'd rather that then people ending up with bloated databases and crappy performance. If think that setting autovacuum to on should even force stats_collector and stats_row_level to on - together with a warning if they would otherwise be off. The risk of autovacuum being disabled by accident seems to risk a much worse performance penatly then having the statistics collector running by accident. Additionally, the statistics collector can easily be turned off within seconds even _if_ it was on accidentally, but if vacuuming was disabled by accident, the user might have to run vacuum full - with all the concurrency issues that this implies.. greetings, Florian flug ---(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] New version of money type
Jim C. Nasby [EMAIL PROTECTED] writes: On Thu, Sep 28, 2006 at 05:19:47PM -0400, D'Arcy J.M. Cain wrote: Well, it is already included. The current proposal is simply to improve the existing type. I guess you are arguing a different proposal altogether - to remove the existing type. The existing type is depricated and has been since at least 8.1; so yes, it's slated for removal. Well, my perception of that has always been it needs to be upgraded or removed. So if D'Arcy wants to work on the improvement angle, I have no problem with him doing so. The thing we need to negotiate is how much improvement is needed to keep it in core. 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] JAVA Support
Josh Berkus josh@agliodbs.com writes: However, that doesn't change that some people would like us to support GSSAPI, and there may be some benefit (additional applications, better network authentication, etc.) for doing so. If we can get additional programmers to code the support (i.e. Sun, JPL) I don't see any reason not to support the *additional* authentication methods. Well, as I said already, a lot depends on the size of the patch. As a reductio ad absurdum, if they drop 100K lines of code on us, it *will* get rejected, no matter how cool it is. The current Kerberos support seems to require about 50 lines in configure.in and circa 200 lines of C code in each of the backend and libpq. Plus a dependency on an outside library that happens to be readily available and compatibly licensed. What amount of code are we talking about adding here, and what dependencies exactly? What portability and license hazards will be added? 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] New version of money type
On Thu, Sep 28, 2006 at 11:23:30PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Thu, Sep 28, 2006 at 05:19:47PM -0400, D'Arcy J.M. Cain wrote: Well, it is already included. The current proposal is simply to improve the existing type. I guess you are arguing a different proposal altogether - to remove the existing type. The existing type is depricated and has been since at least 8.1; so yes, it's slated for removal. Well, my perception of that has always been it needs to be upgraded or removed. So if D'Arcy wants to work on the improvement angle, I have no problem with him doing so. The thing we need to negotiate is how much improvement is needed to keep it in core. I think it's also important to protect for the possibility of a more complete (and probably incompatible) type in the future, such as one that stores what currency a value is in. Hrm... does ANSI say anything about money types? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] New version of money type
Jim C. Nasby [EMAIL PROTECTED] writes: I think it's also important to protect for the possibility of a more complete (and probably incompatible) type in the future, such as one that stores what currency a value is in. Well, such a type could be called currency, cash, forex or several other possibilities, so I don't see any particular argument that money has to be removed before something better can exist. The tightrope that D'Arcy has to walk is different: improving money without making it so incompatible as to break existing apps that use it. Hrm... does ANSI say anything about money types? No. 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] Another idea for dealing with cmin/cmax
Jim C. Nasby [EMAIL PROTECTED] wrote: The reason I thought of this is because once the transaction commits, we have no use for the cid info. So we could do something like have bgwriter look for tuples that belong to committed transactions before it writes a page, and strip the cid out of them. Your concept is just like as the experimental method that I suggested before in http://archives.postgresql.org/pgsql-hackers/2005-08/msg01193.php We can remove cmin and cmax from commited tuples and xmin from frozen tuples and we might save some bytes in tuple headers. However, I think our next goal to shrink the headers is 16 bytes. The headers become 23 bytes using phantom cids and we are limited by alignments, so we will have no more advantages unless we delete extra 7 bytes in the headers. ...and it seems to be very difficult. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] JAVA Support
Josh Berkus wrote: Henry, Sun demonstrated that you could build the existing Kerberos support with the current Solaris 11 beta's. They opened the native MIT Kerberos API for outside use. Yes, and this will be available via the supported version in Solaris 10 Update 4. However, that doesn't change that some people would like us to support GSSAPI, and there may be some benefit (additional applications, better network authentication, etc.) for doing so. If we can get additional programmers to code the support (i.e. Sun, JPL) I don't see any reason not to support the *additional* authentication methods. Is there any reason why we haven't built a generic authentication API? Something like PAM, except cross platform? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] JAVA Support
Joshua D. Drake [EMAIL PROTECTED] writes: Is there any reason why we haven't built a generic authentication API? Something like PAM, except cross platform? We're database geeks, not security/crypto/authentication geeks. What makes you think we have any particular competence to do the above? Actually, the part of this proposal that raised my hackles the most was the claim that GSSAPI provides a generic auth API, because that was exactly the bill of goods we were sold in connection with PAM. (So why is this our problem at all --- can't you make a PAM plugin for it??) It didn't help any that that was shortly followed by the lame admission that no one has ever implemented anything except Kerberos underneath it. Word to the wise, guys: go *real* soft on vaporware claims for auth stuff, because we've seen enough of those before. 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] JAVA Support
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Is there any reason why we haven't built a generic authentication API? Something like PAM, except cross platform? We're database geeks, not security/crypto/authentication geeks. What makes you think we have any particular competence to do the above? Well that is a valid point :). I was just asking. Joshua D. Drake Actually, the part of this proposal that raised my hackles the most was the claim that GSSAPI provides a generic auth API, because that was exactly the bill of goods we were sold in connection with PAM. (So why is this our problem at all --- can't you make a PAM plugin for it??) It didn't help any that that was shortly followed by the lame admission that no one has ever implemented anything except Kerberos underneath it. Word to the wise, guys: go *real* soft on vaporware claims for auth stuff, because we've seen enough of those before. regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
