[HACKERS] tabcompletition and schema
Hi, I just installed 7.3b3 on my server (SuSE linux 8.1 on x86 with gcc 3.2) and discovered a problem with tabcompletition in pgsql. It doesn't work with schema. I created a schema 's' and a table 'tab' in this schema. When typing 'select * from s.' followed by tab nothing happens. A second tab should list me all possible values, but the list ist empty. Without schema it works fine. Tommi -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PG, Crystal Reports, Stored Procedures and User Defined Functions
People, I posted this to the general list with no response so this is the next step . . I have a client that I have just changed over to PG 7.2.3 and despite the early teething problems things are going pretty well. There are, however, a few more difficult problems to do with reporting via Crystal Reports that could easily be solved by Stored Procedures but these don't exist in PG. The User Definable Functions, which would be able to do the job, are not visible to CR. There is a white paper from Crystal Decisions about how Oracle Stored Procedures are packaged to enable them to be used by CR. http://support.crystaldecisions.com/library/kbase/articles/c2010910.asp Maybe the issue really lies with psqlODBC? Does anyone know anything about this area? If you do, do you think it might be possible to also package PG functions to look like stored procedures to CR? If, so is anyone interested in doing the (paid) work? I am quite happy for the result to be GPLd. Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Mobile: +61:0411-185-652 Fax: +61:2:8923-5363 E-mail: [EMAIL PROTECTED] - End Forwarded Message - -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Mobile: +61:0411-185-652 Fax: +61:2:8923-5363 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] calculated fields are not seen in the WHERE clause
Hello, OK select 1 as ccc where 1=1 ERROR select 1 as ccc where ccc=1 PostgreSQL said: ERROR: Attribute 'ccc' not found Is there any way to set conditions on calculated fields values? Best regards, Yar ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Is regress/report.php in use?
Hi, I am not subscribed (and I would prefer to continue read the list through web interface only). Does the developers team follow the http://developer.postgresql.org/regress/report.php ? May be it's worthless to report there? Best regards, Vladimir Chukharev ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Strange cost computation?
Hi, Can anybody explain the following results when using EXPLAIN, one time with enable_seqscan=on and one time with enable_seqscan=off. What I don't understand is that the nodes created are the same (index scan, seq scan), but the costs differ. set enable_seqscan=on; explain (SELECT alias96.ID FROM JOB AS alias96 WHERE NOT EXISTS ((SELECT alias97.DEPENDENT_ID FROM JOB_MISSING_DEPENDENCIES AS alias97 WHERE alias97.DEPENDENT_ID = alias96.ID)) AND NOT EXISTS ((SELECT alias98.JOB_ID FROM ACTIVE_JOBS AS alias98 WHERE alias98.JOB_ID = alias96.ID)) AND ((alias96.STATUS IN (-4, -1))) FOR UPDATE OF alias96 OFFSET 10 LIMIT 10); NOTICE: QUERY PLAN: Limit (cost=458.18..916.35 rows=10 width=10) - Seq Scan on job alias96 (cost=0.00..2185013.05 rows=47689 width=10) SubPlan - Index Scan using job_mis6970_dependent_id_idx on job_missing_dependencies alias97 (cost=0.00..893.88 rows=288 width=4) - Index Scan using active_jobs_job_id_key on active_jobs alias98 (cost=0.00..4.48 rows=1 width=4) set enable_seqscan=off; explain (SELECT alias96.ID FROM JOB AS alias96 WHERE NOT EXISTS ((SELECT alias97.DEPENDENT_ID FROM JOB_MISSING_DEPENDENCIES AS alias97 WHERE alias97.DEPENDENT_ID = alias96.ID)) AND NOT EXISTS ((SELECT alias98.JOB_ID FROM ACTIVE_JOBS AS alias98 WHERE alias98.JOB_ID = alias96.ID)) AND ((alias96.STATUS IN (-4, -1))) FOR UPDATE OF alias96 OFFSET 10 LIMIT 10); NOTICE: QUERY PLAN: Limit (cost=10458.18..10916.35 rows=10 width=10) - Seq Scan on job alias96 (cost=1.00..102185013.05 rows=47689 width=10) SubPlan - Index Scan using job_mis6970_dependent_id_idx on job_missing_dependencies alias97 (cost=0.00..893.88 rows=288 width=4) - Index Scan using active_jobs_job_id_key on active_jobs alias98 (cost=0.00..4.48 rows=1 width=4) Ives ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3b3 on MacOSX 10.2.1
On 29/10/02 6:02 pm, Tom Lane [EMAIL PROTECTED] wrote: Adam Witney [EMAIL PROTECTED] writes: Just to update the list of supported platforms, 7.3b3 compiles and passes all the regression tests on MacOSX 10.2.1 Although don't know if this is relevant but this appears when running the tests: parallel group (20 tests): ./pg_regress: fork: Resource temporarily unavailable ./pg_regress: fork: Resource temporarily unavailable comments lseg box path timetz point circle reltime tinterval date inet interval timestamp time abstime polygon timestamptz oidjoins This suggests that you are hitting the per-user limit on the number of processes you can have; try raising that limit. I'd expect one of the tests not to have been run when that message appears; did you really get successful matches for all tests? regards, tom lane It appears that my ignorance got the better of me It was the first time I had run the regression tests on any PostgreSQL installation. But I think I am getting the same problems as others. below is the last part of the regression tests (I had taken the All 15 tests passed as a success!) Let me know if I can be of any assistance in further checking this out == running regression test queries== parallel group (13 tests): char int4 boolean name varchar float8 bit text int2 oid int8 float4 numeric boolean ... ok char ... ok name ... ok varchar ... ok text ... ok int2 ... ok int4 ... ok int8 ... ok oid ... ok float4 ... ok float8 ... ok bit ... ok numeric ... ok test strings ... ok test numerology ... ok parallel group (20 tests): ./pg_regress: fork: Resource temporarily unavailable ./pg_regress: fork: Resource temporarily unavailable comments lseg box path timetz point circle reltime tinterval date inet interval timestamp time abstime polygon timestamptz oidjoins== shutting down postmaster == == All 15 tests passed. == rm regress.o -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Strange cost computation?
On 29 Oct 2002, Ives Landrieu wrote: Hi, Can anybody explain the following results when using EXPLAIN, one time with enable_seqscan=on and one time with enable_seqscan=off. What I don't understand is that the nodes created are the same (index scan, seq scan), but the costs differ. Enable seq scan actually just sets a large cost disbenefit to seq scans. Is alias96.STATUS indexed? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Strange cost computation?
On Wed, 30 Oct 2002, Stephan Szabo wrote: On 29 Oct 2002, Ives Landrieu wrote: Hi, Can anybody explain the following results when using EXPLAIN, one time with enable_seqscan=on and one time with enable_seqscan=off. What I don't understand is that the nodes created are the same (index scan, seq scan), but the costs differ. Enable seq scan actually just sets a large cost disbenefit to seq scans. Boy, that sentence sure didn't make sense. enable_seqscan=off actually gives a large cost disbenefit to seqscans. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OO future
There's a nice simple book from 1999 by Stonebreaker and a technologist form Informix about object-relational features. PostgreSQL has definately started to lag on that front, while shoring up other aspects of the RDBMS. A simple (simple?) start might just be supporting dot notation and other syntactical niceties around the table-as-column concept. We have recently had an opportunity to evaluate the object relational capabilities of Oracle9i, and sad to say, they have finally surpassed PgSQL on the OO front in this release. Very easy composite type creation on the command line, composite types are easily indexable based on their attributes (a custom type which wraps a spatial type can be indexed spatially, etc), function adding is easy too. Now, adding completely new types in C might be hell, but we haven't checked that yet :) P. Karel Zak wrote: Hi, I read a presentation about Object-Oriented features in relation DBs. The nice are UDT (user defined type): CREATE TABLE person ( name varchar(32), address ROW( street varchar(32), town varchar(32)), age int ); INSERT INTO person VALUES ('Bill', ('Somestreet', 'Sometown'), 33); SELECT name, address.town FROM person; We have composite types in PostgreSQL and I think we can use it for this: CREATE TYPE addr AS (street varchar(32), town varchar(32)); CREATE TABLE person ( name varchar(32), address addr, age int ); Comments? I nothinig found about OO in the current TODO. BTW, my examples are only small part of possible OO features, the others ideas are for example define PRIVATE/PUBLIC attributes in composite types and methods, SELECT p.name FROM person p WHERE p.pay-tax() 100; Karel -- __ / | Paul Ramsey | Refractions Research | Email: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.3b3 on MacOSX 10.2.1
Adam Witney [EMAIL PROTECTED] writes: It appears that my ignorance got the better of me It was the first time I had run the regression tests on any PostgreSQL installation. But I think I am getting the same problems as others. below is the last part of the regression tests (I had taken the All 15 tests passed as a success!) No, there should be ~80 tests in all. I'm not sure why the pg_regress script is failing to process the remaining tests when this happens; any ideas out there? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3b3 passes on MacOSX 10.2.1
Peter Bierman [EMAIL PROTECTED] writes: Ok. After upgrading to bison-1.75, all regression tests pass except: *** ./expected/geometry-powerpc-darwin.out Mon Dec 11 08:45:16 2000 Perhaps the change from gcc2.x to 3.x changed floats a bit? Could be. We had previous reports of the same diff on OSX 10.2 with a G4 processor, so I was wondering if it was hardware or software differences (geometry-powerpc-darwin.out matches exactly on my G3 laptop running OSX 10.1). I have a 10.2 CD and am planning to update sometime soon to verify this by experiment. Or, if you can confirm that you had passes before on the same hardware you're using, we could conclude that it's the OS update that counts. In any case, what's the output from config.guess on your machine? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.3b3 ok on unixware 71[12] here
On Wed, 2002-10-30 at 12:16, Olivier PRENANT wrote: On 30 Oct 2002, Larry Rosenman wrote: Date: 30 Oct 2002 12:14:01 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] 7.3b3 ok on unixware 71[12] here I've got it running with 5.8.0 of Perl. Is there anything holding you back from updating to 5.8.0 of PERL? Unfortunatly yes! Majordomo2 doesn't work quit well with 580. Is there anyway to ake 561 compile the way 580 does for dynaloader? I had zero luck... Sorry. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] move 0 behaviour
Bruce Momjian writes: So, that is why MOVE 0 goes to the end of the cursor. One idea would be for MOVE 0 to actually move nothing, but jdbc and others need the ability to move the end of the cursor, perhaps to then back up a certain amount and read from there. Seems MOVE 0 is the logical way to do that. (I can't think of another reasonable value). It would seem more logical and reasonable for MOVE 0 to do nothing and have some special syntax such as MOVE LAST to move to the end. (MOVE LAST would actually be consistent with the standard syntax FETCH LAST.) -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [CYGWIN] [HACKERS] Request for supported platforms
Dave Page writes: Hackers: As the Cygwin release that is actively supported is the binary distribution that Jason builds, I would think this is OK to be listed as supported if no-one disagrees... I disagree. We document as supported those platforms that build out of the box, not those that build somehow, somewhere, by someone. Rather than advocating methods to manually edit your system headers we should try to find out what the problem really is, such as by analyzing config.log. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] float output precision questions
On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: In C this is possible: #include stdio.h #include stdlib.h int main(void) { double v; char a[30]; v=1.79769313486231571e+308; printf( Stored double number: %25.18g\n,v); sprintf(a,%25.18g,v); printf(Converted to string: %s\n,a); v=atof(a); printf(Converted from string to double: %25.18g\n,v); } AFAICT, this is not guaranteed to give you the same representation that you're using in the initializer however. Using standard PostgreSQL query output it would not be possible to get this number, which has representation as a double. I fetched the PostgreSQL source from Debian, changed src/backend/utils/adt/float.c to do sprintf(ascii, %25.18g, num) instead of sprintf(ascii, %.*g, DBL_DIG, num), compiled and installed. Now I can get the number as is. I understand that if people insert a value of 1.1 in a double, they want to get 1.1 without knowing that in fact the stored number is 1.10009. But do you understand that if some people insert, for example, a value of 1.79769313486231571e+308 they dont want to get 1.79769313486232e+308 which does not compare equal (in Matlab or C) to the first ? This is a bug. I disagree to some extent. I'm not sure it's meaningful to expect that (what if the database and the client are on different architectures) in general. In any case, you're effectively going from decimal representation to double to decimal representation (the string you used to insert it - internal representation - string used to output it) and that's only guaranteed to be correct up to DBL_DIG digits as far as I can tell. I think it'd be nice to have an option to get more digits for those sorts of applications, however. This would probably make sense as an option, so why don't you look at the past discussions and see if you can come up with a solution that keeps everyone happy (and preferably implement it, but...) :) but ??? , but I realize that you might not be interested in doing such. (I figured the last part was implied) I have a sugestion: To have parameters, say DOUBLE_FORMAT and FLOAT_FORMAT, which could have option values of 'SHORT' and 'LONG'. Option 'SHORT' would be default and produce the standard sprintf(ascii,... Option 'LONG' would produce sprintf(ascii, %25.18g, num). Other way would be to have number parameters to be used in the sprintf calls, in place of 25 and 18, in the format string. From what Tom said, something similar was suggested and there were issues brought up. I don't know what they were, since I wasn't personally terribly interested, but it should be in the archives. If there were any concerns, you'll probably need to deal with those as well. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] float output precision questions
On Wed, Oct 30, 2002 at 19:27:57 +0100, Peter Eisentraut [EMAIL PROTECTED] wrote: The printf(%A) output is not system-specific. Just out of curiosity, can you tell me a web page or keywords to use in a search to see what that format does? I tried using google, but searching for printf with and A format didn't go to well. I only found one correct reference and it didn't explain what %A did. Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.3b3 ok on unixware 71[12] here
Well, I think I just found it!! I adeed -Kpic in Dynaloader's Makefile. I haven't test plperl itself, but at least it compiles. Regards, On 30 Oct 2002, Larry Rosenman wrote: Date: 30 Oct 2002 12:23:06 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] 7.3b3 ok on unixware 71[12] here On Wed, 2002-10-30 at 12:16, Olivier PRENANT wrote: On 30 Oct 2002, Larry Rosenman wrote: Date: 30 Oct 2002 12:14:01 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] 7.3b3 ok on unixware 71[12] here I've got it running with 5.8.0 of Perl. Is there anything holding you back from updating to 5.8.0 of PERL? Unfortunatly yes! Majordomo2 doesn't work quit well with 580. Is there anyway to ake 561 compile the way 580 does for dynaloader? I had zero luck... Sorry. LER -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3b3 on MacOSX 10.2.1
Tom Lane writes: No, there should be ~80 tests in all. I'm not sure why the pg_regress script is failing to process the remaining tests when this happens; any ideas out there? It appears that the shell simply aborts on fork failure. Example: peter ~$ cat test.sh ulimit -u 30 for i in $(seq 1 25); do echo $i ( sleep 5 ) echo ok done wait echo all done peter ~$ sh test.sh 1 ok 2 test.sh: fork: Resource temporarily unavailable peter ~$ In pg_regress, the big loop is probably done in a subshell, because it's in a pipe, so the outer script can complete. Detecting failures in commands in a pipe is of course a bit tricky. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PG functions in Java: maybe use gcj?
I had an interesting conversation today with Tom Tromey and Andrew Haley of Red Hat about how to implement pljava for Postgres. Rather than futzing with an external JVM, their thought is to use gcj (gcc compiling Java). It sounds like this approach would mostly just work, modulo needing to use a small amount of C++ code to call the defined APIs for gcj. This would not be a perfect solution: gcj isn't yet ported everywhere, and it would probably not play nice on machines where the standard C library isn't thread-safe. But it seems a lot more within reach than the approaches we've discussed in the past. I'm not volunteering to try to do this, but I wanted to toss the idea up in the air and see if anyone wants to try it. Tom and Andrew indicated they'd be willing to help out with advice etc for anyone who wants to take on the project. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Concerns about statement-timeout patch
I've been trying to do some code review of the recent statement-timeout feature addition, and I've got some fairly serious concerns about it. One problem that needs discussion is that the enable_sig_alarm and disable_sig_alarm calls were dropped into postgres.c at rather randomly chosen places. The disable in particular is wrong because in the normal case it will occur after we have done transaction commit. This creates a window between committing a command and reaching the disable call wherein the timeout interrupt could happen. If it does happen, the net result will be that the client receives an error message, making it look like the command failed --- when in fact it was committed. The simplest solution would be to move the calls into start_xact_command and finish_xact_command respectively. However that would affect the semantics a little, in that for a querystring containing BEGIN and/or COMMIT commands, the timeout would be measured across subsets of the query string, not the whole string as now. I am not sure if this is a problem or not; the existing semantics don't exactly match my idea of a statement timeout anyway. Another possible objection is that end-of-transaction cleanup would not be counted in the statement timeout. This does not bother me, since the cleanup should be quick, but maybe it would bother someone. (I would place the disable after DeferredTriggerEndQuery(), so that RI triggers are run before we disable the timeout.) I am also concerned about the fact that the feature requires assuming that setitimer(ITIMER_REAL) plays nicely with sleep(). The documents I have say that on some platforms sleep() destroys any pending ITIMER_REAL setting. We could perhaps fix that by adding a call to reset the end-of-statement timeout after every sleep() in the backend ... but that's obviously a fragile way to proceed. Comments? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] 7.2.3 vacuum bug
ERROR: RelationClearRelation: relation 11584078 deleted while still in use I've been unable to come up with a test case that will cause the problem, seems to be timing related. The queries that are currently running when these errors occur do a lot or work with temp tables that are frequently truncated. -- Rod Taylor ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] What user to defaults execute as?
On Wed, Oct 30, 2002 at 14:03:21 -0600, While I am not sure about triggers, it certainly is possible to get a similar effect be having the referenced function run with the security of the definer. I read some more on triggers and found that according to the documentation, they appear to run as the user doing the insert, update or delete and are specifically noted to be dangerous. And while using the execute as definer can allow a trigger writer to provide limited access to the invoker, it doesn't protect the invoker from the trigger writer. It seems unlikely that triggers should be doing things to objects that the trigger owner doesn't have rights to. And this might be another place where using the access of the owner would be better than using that of the invoker. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.2.3 vacuum bug
Rod Taylor [EMAIL PROTECTED] writes: ERROR: RelationClearRelation: relation 11584078 deleted while still in use I was going to report a similar error that arises in a different situation: client 1: CREATE TABLE a (b int); BEGIN; DROP TABLE a; -- wait client 2: SELECT * FROM a; client 1: COMMIT; Now, client 2 will receive RelationClearRelation: relation 25172 deleted while still in use, rather than Relation a does not exist, as you might expect. Not sure if it's the same bug, or just a different problem... Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.2.3 vacuum bug
On Wed, 2002-10-30 at 15:38, Neil Conway wrote: Rod Taylor [EMAIL PROTECTED] writes: ERROR: RelationClearRelation: relation 11584078 deleted while still in use I was going to report a similar error that arises in a different situation: Probably a different look at the same problem. -- Rod Taylor ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Swedish version of the PostgreSQL Advocacy and Marketing site is ready
Hi everyone, Thanks to Henrik Edlund [EMAIL PROTECTED], the Swedish translation of the PostgreSQL Advocacy and Marketing site is now completed and ready for public use: http://advocacy.postgresql.org/?lang=sv :-) Spanish should available pretty soon too (next few days at least). Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] float output precision questions
Stephan Szabo wrote: On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: I understand that if people insert a value of 1.1 in a double, they want to get 1.1 without knowing that in fact the stored number is 1.10009. But do you understand that if some people insert, for example, a value of 1.79769313486231571e+308 they dont want to get 1.79769313486232e+308 which does not compare equal (in Matlab or C) to the first ? This is a bug. I disagree to some extent. I'm not sure it's meaningful to expect that (what if the database and the client are on different architectures) in general. In any case, you're effectively going from decimal representation to double to decimal representation (the string you used to insert it - internal representation - string used to output it) and that's only guaranteed to be correct up to DBL_DIG digits as far as I can tell. I think it'd be nice to have an option to get more digits for those sorts of applications, however. In the previous email example, in C, I was going from decimal to double and so on, but this is not the case when I do some simulation. In this case it will allways be from Matlab double to PostgreSQL float8 and from libpq PQgetvalue() string to Matlab double. The example was just a x86 number example where (got the string from Matlab double) query output would fail. This would probably make sense as an option, so why don't you look at the past discussions and see if you can come up with a solution that keeps everyone happy (and preferably implement it, but...) :) but ??? , but I realize that you might not be interested in doing such. (I figured the last part was implied) ok! :) No problem. I have seen the GUC thing in the source (guc.c etc...) and it does not look too dificult. It has lots of examples in the code itself. What I am saying is that I can do it if pg-people agree on the (some) way to do it. To have parameters, say DOUBLE_FORMAT and FLOAT_FORMAT, which could have option values of 'SHORT' and 'LONG'. Option 'SHORT' would be default and produce the standard sprintf(ascii,... Option 'LONG' would produce sprintf(ascii, %25.18g, num). Other way would be to have number parameters to be used in the sprintf calls, in place of 25 and 18, in the format string. From what Tom said, something similar was suggested and there were issues brought up. I don't know what they were, since I wasn't personally terribly interested, but it should be in the archives. If there were any concerns, you'll probably need to deal with those as well. I looked at some of these emails and it seemed to me that the problem was that Tom did'nt want a parameter that would force people to know about printf number formatting. I think the first solution above (the SHORT and LONG way) is simple, maintains usual output as default and enables 'maximum' precision at request. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- -- Pedro Miguel Frazao Fernandes Ferreira Universidade do Algarve Faculdade de Ciencias e Tecnologia Campus de Gambelas 8000-117 Faro Portugal Tel./Fax: (+351) 289 800950 / 289 819403 http://w3.ualg.pt/~pfrazao ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgAdmin III (Was: Request for supported platforms)
Since you're using wxWindows, I *HIGHLY* recommend obtaining a license to wxDesigner from http://www.roebling.de/. It allows for very rapid GUI design. It also understands various sizers and makes it SOOO much easier to make use of them. Once you understand sizers, you'll love them but they are somewhat hard to use without a tool to assist. Also, for the about box, I also suggest that you make use of a wxHTML dialog. The cool thing about doing this is that you can create links and even place nice images within the about box. Plus, maintaining the about box can be easily done via an HTML resource file versus having to update code and recompile. If you have questions on wxWindows and/or wxPython, please let me know. I've been a long time user. Also, if you're willing, I also have some constructive criticism on the code. Since this is obviously going off topic as it relates to PostgreSQL, it probably wouldn't be appropriate to followup on the mailing list. Best Regards, Greg Copeland On Wed, 2002-10-30 at 02:19, Dave Page wrote: -Original Message- From: Greg Copeland [mailto:greg;copelandconsulting.net] Sent: 30 October 2002 01:08 To: Dave Page Subject: Re: [HACKERS] Request for supported platforms C++? Really? What GUI toolkit is being used? Just curious. wxWindows. The CVS is online if anyone is interested in taking a peek - it's at http://cvs.pgadmin.org/. The code is in the pgadmin3 module. Please bear in mind though, that Mark I have only been using C++ for about a month (though things are coming along very quickly), so there may be some nasties in our code. Any constructive comments from any of the -hackers would be more than welcome. Regards, Dave. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PG functions in Java: maybe use gcj?
I am not sure I follow. Are you suggesting: 1) create function takes java source and then calls gcj to compile it to native and build a .so from it that would get called at runtime? or 2) create function takes java source and just compiles to java .class files and the runtime invokes the gcj java interpreter. or I guess you could do both at the same time. In either case I am concerned about licensing issues. gcj is not under a BSD style license. Depending on what you need you are either dealing with regular GPL, LGPL, or LGPL with a special java exception. I beleive (without giving it too much thought) that doing either 1 or 2 above would end up linking GPL code into postgres. This can be worked around by requiring the the necessary gcj libraries be installed separately and detected at configure time (like is done elsewhere). But is does (I think) present a problem for commercial products that would like to redistribute postgres with pljava. Another challenge here it that the java code is going to want to use the jdbc api when communicating with the database. One difficulty here is getting jdbc to be part of the same transaction as the calling java function. Such that if the java stored procedure selects or updates data it is doing it in the same transaction as the caller of the function. Today the jdbc driver only knows how to communicate via the FE/BE protocol which will end up creating a new process and transaction. The jdbc driver would need to not use the FE/BE protocol but instead probably use jni calls. thanks, --Barry Tom Lane wrote: I had an interesting conversation today with Tom Tromey and Andrew Haley of Red Hat about how to implement pljava for Postgres. Rather than futzing with an external JVM, their thought is to use gcj (gcc compiling Java). It sounds like this approach would mostly just work, modulo needing to use a small amount of C++ code to call the defined APIs for gcj. This would not be a perfect solution: gcj isn't yet ported everywhere, and it would probably not play nice on machines where the standard C library isn't thread-safe. But it seems a lot more within reach than the approaches we've discussed in the past. I'm not volunteering to try to do this, but I wanted to toss the idea up in the air and see if anyone wants to try it. Tom and Andrew indicated they'd be willing to help out with advice etc for anyone who wants to take on the project. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PG functions in Java: maybe use gcj?
Barry Lind [EMAIL PROTECTED] writes: I am not sure I follow. Are you suggesting: 1) create function takes java source and then calls gcj to compile it to native and build a .so from it that would get called at runtime? or 2) create function takes java source and just compiles to java .class files and the runtime invokes the gcj java interpreter. or I guess you could do both at the same time. The impression I had (after not looking very closely) was that you could expect to compile to bytecodes on the fly and then run the gcj interpreter. But the .so alternative might be a good fallback if that doesn't work. In either case I am concerned about licensing issues. gcj is not under a BSD style license. Depending on what you need you are either dealing with regular GPL, LGPL, or LGPL with a special java exception. I beleive (without giving it too much thought) that doing either 1 or 2 above would end up linking GPL code into postgres. This can be worked around by requiring the the necessary gcj libraries be installed separately and detected at configure time (like is done elsewhere). But is does (I think) present a problem for commercial products that would like to redistribute postgres with pljava. Good point, but unless you want to build a BSD-license Java implementation, there will never be a pljava that doesn't have different licensing restrictions than PG itself does. gcj is at least more free than either Sun's or IBM's JVM ... Another challenge here it that the java code is going to want to use the jdbc api when communicating with the database. Yes. I think we'd need a new implementation of jdbc that sits atop SPI (invoked via jni I guess) rather than a FE/BE connection. How well layered is our jdbc code --- would this mean a large rewrite, or just rolling in a new bottom layer? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.2.3 vacuum bug
Neil Conway [EMAIL PROTECTED] writes: client 1: CREATE TABLE a (b int); BEGIN; DROP TABLE a; -- wait client 2: SELECT * FROM a; client 1: COMMIT; Now, client 2 will receive RelationClearRelation: relation 25172 deleted while still in use, rather than Relation a does not exist, as you might expect. But relation a *does* exist at the start of client 2's operation. While I'm not here to defend the exact phrasing of this error message, it does seem to me that it's appropriate to give a different error message than what appears when the table wasn't found at all. An example of why the two cases shouldn't be folded together: suppose that client 2's schema search path is myschema, public, and that client 1 creates/drops myschema.a while there is also a public.a. client 2 will locate myschema.a as the meaning of a, and one way or another it is going to error out when myschema.a gets dropped from underneath it --- it will not (and shouldn't IMHO) go back and repeat the schema search to find public.a. But a user who gets a Relation a does not exist error message in such a scenario would be justifiably confused. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.2.3 vacuum bug
Tom Lane [EMAIL PROTECTED] writes: But relation a *does* exist at the start of client 2's operation. While I'm not here to defend the exact phrasing of this error message, it does seem to me that it's appropriate to give a different error message than what appears when the table wasn't found at all. Ok, fair enough -- I agree that we should treat the two cases differently. But one thing I think we should do in any case is improve the wording of the error message. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.2.3 vacuum bug
Rod Taylor [EMAIL PROTECTED] writes: ERROR: RelationClearRelation: relation 11584078 deleted while still in use I've been unable to come up with a test case that will cause the problem, seems to be timing related. The queries that are currently running when these errors occur do a lot or work with temp tables that are frequently truncated. Hm. vacuum.c tries to avoid this class of problem: /* * Race condition -- if the pg_class tuple has gone away since the * last time we saw it, we don't need to vacuum it. */ if (!SearchSysCacheExists(RELOID, ObjectIdGetDatum(relid), 0, 0, 0)) { CommitTransactionCommand(true); return true;/* okay 'cause no data there */ } ... onerel = relation_open(relid, lmode); but on reflection it's clear that this doesn't really prevent a race condition. If the table is already exclusive-locked by a DROP TABLE that hasn't committed yet (eg, the implicit DROP that happens when temp tables are cleared out at backend exit), then the syscache lookup will go fine, but the relation_open() routine blocks waiting for lock and eventually fails. What would probably work better is to first lock the relation OID, then see if we can open the relation or not. Thinking further, it's really kinda bogus that LockRelation() works on an already-opened Relation; if possible we should acquire the lock before attempting to create a relcache entry. (We only need to know the OID and the relisshared status before we can make a locktag, so it'd be possible to acquire the lock using only the contents of the pg_class row.) Not sure how much code restructuring might be involved to make this happen, but it'd be worth thinking about for 7.4. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.2.3 vacuum bug
Neil Conway [EMAIL PROTECTED] writes: Ok, fair enough -- I agree that we should treat the two cases differently. But one thing I think we should do in any case is improve the wording of the error message. Got a suggestion? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] OO future
Hi, I read a presentation about Object-Oriented features in relation DBs. The nice are UDT (user defined type): CREATE TABLE person ( name varchar(32), address ROW( street varchar(32), town varchar(32)), age int ); INSERT INTO person VALUES ('Bill', ('Somestreet', 'Sometown'), 33); SELECT name, address.town FROM person; We have composite types in PostgreSQL and I think we can use it for this: CREATE TYPE addr AS (street varchar(32), town varchar(32)); CREATE TABLE person ( name varchar(32), address addr, age int ); Comments? I nothinig found about OO in the current TODO. BTW, my examples are only small part of possible OO features, the others ideas are for example define PRIVATE/PUBLIC attributes in composite types and methods, SELECT p.name FROM person p WHERE p.pay-tax() 100; Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pgAdmin III (Was: Request for supported platforms)
-Original Message- From: Greg Copeland [mailto:greg;copelandconsulting.net] Sent: 30 October 2002 01:08 To: Dave Page Subject: Re: [HACKERS] Request for supported platforms C++? Really? What GUI toolkit is being used? Just curious. wxWindows. The CVS is online if anyone is interested in taking a peek - it's at http://cvs.pgadmin.org/. The code is in the pgadmin3 module. Please bear in mind though, that Mark I have only been using C++ for about a month (though things are coming along very quickly), so there may be some nasties in our code. Any constructive comments from any of the -hackers would be more than welcome. Regards, Dave. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OO future
Karel Zak kirjutas K, 30.10.2002 kell 10:08: Hi, I read a presentation about Object-Oriented features in relation DBs. The nice are UDT (user defined type): CREATE TABLE person ( name varchar(32), address ROW( street varchar(32), town varchar(32)), age int ); INSERT INTO person VALUES ('Bill', ('Somestreet', 'Sometown'), 33); SELECT name, address.town FROM person; We have composite types in PostgreSQL and I think we can use it for this: CREATE TYPE addr AS (street varchar(32), town varchar(32)); CREATE TABLE person ( name varchar(32), address addr, age int ); Comments? I nothinig found about OO in the current TODO. I'm writing a small proposal for evoving inheritance and other OO features in 7.4 and beyond. Will post once 7.3 is out. BTW, my examples are only small part of possible OO features, the others ideas are for example define PRIVATE/PUBLIC attributes in composite types At least the Third Manifesto by Date et.al. claims that PRIVATE/PUBLIC is better left to standard access control mechanisms (GRANT/REVOKE). I agree to that. and methods, SELECT p.name FROM person p WHERE p.pay-tax() 100; The methods will probably have problems with syntax clashes with existing stuff. -- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OO future
On Wed, Oct 30, 2002 at 08:35:09AM +0200, Hannu Krosing wrote: Comments? I nothinig found about OO in the current TODO. I'm writing a small proposal for evoving inheritance and other OO features in 7.4 and beyond. Will post once 7.3 is out. Good! I look forward. BTW, my examples are only small part of possible OO features, the others ideas are for example define PRIVATE/PUBLIC attributes in composite types At least the Third Manifesto by Date et.al. claims that PRIVATE/PUBLIC is better left to standard access control mechanisms (GRANT/REVOKE). I agree to that. Yes, but it expect access control pre-column and for per composite type attribute. I understand PRIVATE as some internal data for methods and it needn't a speciffic access control, because control must be define for methods, and other way access PRIVATE data is not possible. IMHO it's better. The other important thing is possibility create table from type: CREATE TABLE adresses AS address_t; and methods, SELECT p.name FROM person p WHERE p.pay-tax() 100; The methods will probably have problems with syntax clashes with existing stuff. Hmm, p.pay.tax() ? Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] float output precision questions
Peter Eisentraut wrote: Matlab has a toolbox fucntion, claiming maximum precision, to convert from its double type (PostgreSQL float8) to string which does a sprintf('%25.18g',number). Do we have some mathematical guarantee that this is sufficient and necessary? If so, then it might do. It is necessary if you want to do this type of (huge amount of number storage) application: [Some client] (insert) [PostgreSQL] (query) [Some client] (double number a)(float8 number)---(double number b) In order for a=b this is necessary. With current float8 output this is not allways true. Here is the help for that particular Matlab function: NUM2MSTR Convert number to string in maximum precision. S = NUM2MSTR(N) converts real numbers of input matrix N to string output vector S, in maximum precision. See also NUM2STR. If you want I can try to contact the guys who coded this function to know if this is sufficient. Thanks, Pedro M. Ferreira -- -- Pedro Miguel Frazao Fernandes Ferreira Universidade do Algarve Faculdade de Ciencias e Tecnologia Campus de Gambelas 8000-117 Faro Portugal Tel./Fax: (+351) 289 800950 / 289 819403 http://w3.ualg.pt/~pfrazao ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] float output precision questions
Stephan Szabo wrote: On Tue, 29 Oct 2002, Peter Eisentraut wrote: Pedro Miguel Frazao Fernandes Ferreira writes: Is there a way to set query output precision to maximum precision ? For the type of application I mentioned this is crucial. People want to get the 'same' numbers, from querys or dumps, as they inserted them. How do you define maximum precision and same? With simple test programs in C, using two digits more than DBL_DIG for printf specifier, it's easy for me to find numbers that change decimal string representation in the decimal representation - double - decimal representation conversion(*). The final double you get from the second conversion should be the same as the first, but is that what you need or do you need a stronger guarantee than that? When I say same I am talking about having a number 'stored' in double format in some client, inserting it in PostgreSQL float8 field and get it to the client as it was before: [Some client] (insert) [PostgreSQL] (query) [Some client] (double number a)(float8 number)---(double number b) same is so that a==b is true. With current float8 output this is not allways true. I believe this should allways be true for numbers which are originally stored in double format. Thanks, Pedro M. Ferreira -- -- Pedro Miguel Frazao Fernandes Ferreira Universidade do Algarve Faculdade de Ciencias e Tecnologia Campus de Gambelas 8000-117 Faro Portugal Tel./Fax: (+351) 289 800950 / 289 819403 http://w3.ualg.pt/~pfrazao ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] float output precision questions
Bruno Wolff III wrote: On Tue, Oct 29, 2002 at 23:19:05 +0100, Peter Eisentraut [EMAIL PROTECTED] wrote: There isn't a way right now, but it's planned to be able to dump floating-point numbers in some binary form (like printf(%A)) to be able to restore them exactly. Not sure how that would satisfy the needs of client interfaces, though. Why not print it as a floating binary number instead of a floating decimal number? I would think that would give you better portability than a system specific binary representation. Having a way to get the binary storage representation for float numbers would be good and would suffice within the same float number standard. Thanks, Pedro M. Ferreira -- -- Pedro Miguel Frazao Fernandes Ferreira Universidade do Algarve Faculdade de Ciencias e Tecnologia Campus de Gambelas 8000-117 Faro Portugal Tel./Fax: (+351) 289 800950 / 289 819403 http://w3.ualg.pt/~pfrazao ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [Fwd: Re: [HACKERS] UnixWare 7.1.3 (BETA), C99 compiler,
Tom Lane wrote: static inline int32 StaticApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, Datum datum1, bool isNull1, Datum datum2, bool isNull2) { //etc. } int32 ApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, Datum datum1, bool isNull1, Datum datum2, bool isNull2) Grumble. I suppose we have to do it that way, but it's really pretty stupid. Also, won't this fall foul of the original restriction (ApplySortFunction referencing the static function myFunctionCall2)? If not, why not? The change is that the inline function referencing the identifiers with internal linkage also has internal linkage -- i.e., they're both static. Since there can be only one definition of StaticApplySortFunction(), there's no reason to restrict it's contents. Until the open source base (and GCC) get around to matching the C99 inline model, Don't hold your breath... it looks like a net loss in functionality for no gain, from where I sit. The GCC owners are now much more standards-aware and -driven than they were 10+ years ago. I believe that they are going to (if not already) have a way of interpreting inline according to the C99 standard. It will probably not be the default, given the incompatibilities, but it'll be there, somehow, sometime. As far as I'm concerned, the C99 inline doesn't do me much good either, as I cannot use it in headers to take the place of function-like macros in standard headers, either. But, this is what you get with Committees-- specifications that make everyone unhappy at roughly equivalent levels. :-) Since inline (as specified in C99) is pretty much just a hint to the compiler in much the same way that register was in the good old days, it really shouldn't matter too much one way or another, but feel free to use it as you desire. -- Dave Prosser [EMAIL PROTECTED] (908)790-2358 The SCO Group, Murray Hill, NJ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Request for supported platforms
Bruce, Not sure. I know Tom Lane ran a check on 10.1.x and didn't have any errors, so I figured the differences I saw reflected changes made by Apple between 10.1.x and 10.2.x. Then, I was surprised when I saw Adam's report, so I thought perhaps he was running on a PPC G3 and there were differences between G3 and G4 floating point handling (I had run the original regression tests on a PowerBook G4/400 MHz). So, I ran the same exact scenario on my aging iMac (G3/266 Mhz). Those results corroborated my original test (geometry failed; horology cleared up, though, as expected). So, something is different between Adam's system and the two I have easy access to. I have confirmed that both my systems are running 10.2.1. Similarly, the tests were both run on 7.3b3 from the full tarball on ftp.us.postgresql.org. The new iMac G3 regression.diffs output is immediately below. Regardless, it would appear that Mac OS X 10.2.x is well supported. Tara *** ./expected/geometry-powerpc-darwin.out Mon Dec 11 11:45:16 2000 --- ./results/geometry.out Tue Oct 29 07:59:59 2002 *** *** 127,133 | (-5,-12) | [(10,-10),(-3,-4)]| (-1.60487804878049,-4.64390243902439) | (10,10)| [(10,-10),(-3,-4)]| (2.39024390243902,-6.48780487804878) | (0,0) | [(-100,200),(30,-40)] | (0.0028402365895872,15.384614860264) ! | (-10,0)| [(-100,200),(30,-40)] | (-9.99715942258202,15.3864610140472) | (-3,4) | [(-100,200),(30,-40)] | (-2.99789812267519,15.3851688427303) | (5.1,34.5) | [(-100,200),(30,-40)] | (5.09647083221496,15.3836744976925) | (-5,-12) | [(-100,200),(30,-40)] | (-4.99494420845634,15.3855375281616) --- 127,133 | (-5,-12) | [(10,-10),(-3,-4)]| (-1.60487804878049,-4.64390243902439) | (10,10)| [(10,-10),(-3,-4)]| (2.39024390243902,-6.48780487804878) | (0,0) | [(-100,200),(30,-40)] | (0.0028402365895872,15.384614860264) ! | (-10,0)| [(-100,200),(30,-40)] | (-9.99715942258202,15.3864610140473) | (-3,4) | [(-100,200),(30,-40)] | (-2.99789812267519,15.3851688427303) | (5.1,34.5) | [(-100,200),(30,-40)] | (5.09647083221496,15.3836744976925) | (-5,-12) | [(-100,200),(30,-40)] | (-4.99494420845634,15.3855375281616) == Bruce Momjian wrote: Strange. I just got report from another OSX 10.2.1 user saying regression tests passed: 10.2.1, Adam Witney ([EMAIL PROTECTED] The proper value seems to be: 15.3864610140472 or 15.3864610140473 in ./expected/geometry-powerpc-darwin.out. Which is it, folks? --- Tara Piorkowski wrote: Tom Lane wrote: Bruce Momjian writes: Folks. start sending in those plaform reports, OS name and version number please. I've checked CVS tip on: HPUX 10.20, using both gcc and vendor's cc PPC Linux Mac OS X 10.1 Here's the regression.diffs file from Mac OS X 10.2.1 (Jaguar). It failed geometry (rounding) and horology (not sure). gcc -v returns Apple Computer, Inc. GCC version 1161, based on gcc version 3.1 20020420 (prerelease). *** ./expected/geometry-powerpc-darwin.out Mon Dec 11 11:45:16 2000 --- ./results/geometry.out Sun Oct 27 21:27:11 2002 *** *** 127,133 | (-5,-12) | [(10,-10),(-3,-4)]| (-1.60487804878049,-4.64390243902439) | (10,10)| [(10,-10),(-3,-4)]| (2.39024390243902,-6.48780487804878) | (0,0) | [(-100,200),(30,-40)] | (0.0028402365895872,15.384614860264) ! | (-10,0)| [(-100,200),(30,-40)] | (-9.99715942258202,15.3864610140472) | (-3,4) | [(-100,200),(30,-40)] | (-2.99789812267519,15.3851688427303) | (5.1,34.5) | [(-100,200),(30,-40)] | (5.09647083221496,15.3836744976925) | (-5,-12) | [(-100,200),(30,-40)] | (-4.99494420845634,15.3855375281616) --- 127,133 | (-5,-12) | [(10,-10),(-3,-4)]| (-1.60487804878049,-4.64390243902439) | (10,10)| [(10,-10),(-3,-4)]| (2.39024390243902,-6.48780487804878) | (0,0) | [(-100,200),(30,-40)] | (0.0028402365895872,15.384614860264) ! | (-10,0)| [(-100,200),(30,-40)] | (-9.99715942258202,15.3864610140473) | (-3,4) | [(-100,200),(30,-40)] | (-2.99789812267519,15.3851688427303) | (5.1,34.5) | [(-100,200),(30,-40)] | (5.09647083221496,15.3836744976925) | (-5,-12) | [(-100,200),(30,-40)] | (-4.99494420845634,15.3855375281616) == ***
Re: [HACKERS] Request for supported platforms
On 29/10/02 1:50 pm, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: Strange. I just got report from another OSX 10.2.1 user saying regression tests passed: 10.2.1, Adam Witney ([EMAIL PROTECTED] The proper value seems to be: 15.3864610140472 or 15.3864610140473 in ./expected/geometry-powerpc-darwin.out. Which is it, folks? The existing geometry file is exactly correct on my laptop (Powerbook G3 using OSX 10.1). I am not sure whether the differences some users have reported are due to hardware or OS version differences. We need to figure that out and refine the resultmap, not just change the existing file. I don't have a lot of experience with this stuff, but let me know what to try and I will try it. (Using a Powerbook G4 OSX 10.2.1) Cheers adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Database replication... - Mission Critical DBMS's -- Taking postgreSQL to the Enterprise Computing level...
I have a client that I am developing a web-based business application for using Perl / PHP, DHTML, and postgreSQL on a FreeBSD server with Apache... The client realized (or hopes) that this application may become mission critical (to his clients / end-users) and was asking about the possibility of having a replicated database using postgreSQL in case the database server takes a dive (paranoia). He is somewhat familiar with MS Access (I gathered he was thinking of the MS Access replication / front-end / back-end / synchronized database concept). I have read through all 7 chapters of the PG documentation and didn't see anything about replicating a postgreSQL database on a secondary database server and having the primary server push all data manipulations on through to the secondary server. The secondary server would be available (through the connect script) if the primary server died. Does anyone know of a reasonable means of synchronizing two (or more) postgreSQL database servers in order to maintain two COMPLETE concurrent copies of the same database ? Since I can already see REFINT problems in maintaining the integrity of serial values, trigger problems, and stored procedure problems (UDF's), perhaps I should just suggest a robust database backup and restore scheme with a standby postgreSQL server ready to accept a pgdumpall. Although the documentation claims that CREATE DATABASE is not intended as a copy database utility, maybe a CRON event (say every 4 hours) on the secondary server to: DROP database 'backupdatabase'; CREATE DATABASE 'backupdatabase' WITH TEMPLATE = 'livedatabase'; would do the trick I am completely open to suggestions here... TIA. GP ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] calculated fields are not seen in the WHERE clause
Yaroslav Dmitriev kirjutas K, 30.10.2002 kell 13:48: Hello, OK select 1 as ccc where 1=1 ERROR select 1 as ccc where ccc=1 PostgreSQL said: ERROR: Attribute 'ccc' not found Is there any way to set conditions on calculated fields values? You could try using a subquery select * from (select 1 as ccc) sub where ccc=1 --- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Database replication... - Mission Critical DBMS's --
You might want to check out http://gborg.postgresql.org/genpage?replication_research which has information and links to several replication solutions for postgresql. the techdocs.postgresql.org website also has a good number of papers regarding replication, as well as a high availability how-to that would probably be worth reading. Robert Treat On Wed, 2002-10-30 at 09:24, Bill Gribble wrote: On Tue, 2002-10-29 at 17:56, Greg Patnude wrote: Does anyone know of a reasonable means of synchronizing two (or more) postgreSQL database servers in order to maintain two COMPLETE concurrent copies of the same database ? I'm not sure how complete it is (just starting to look at this myself) but 'dbbalancer' at least makes some claims to this. It acts as (basically) a postgresql proxy server, and can send queries to multiple servers. My presumption would be that if you initialize 2 databases to a known identical start, have all the same triggers and rules on both, then send all queries to both databases, you will have 2 identical databases at the end. Don't know how well that will work in practice tho. I should know more in the next couple of weeks. b.g. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Database replication... - Mission Critical DBMS's -- Taking postgreSQL to the Enterprise Computing level...
On Tue, Oct 29, 2002 at 03:56:46PM -0800, Greg Patnude wrote: I have read through all 7 chapters of the PG documentation and didn't see anything about replicating a postgreSQL database on a secondary database server and having the primary server push all data manipulations on through to the secondary server. The secondary server would be available (through the connect script) if the primary server died. Does anyone know of a reasonable means of synchronizing two (or more) postgreSQL database servers in order to maintain two COMPLETE concurrent copies of the same database ? I think what you want is that any change, including schema changes, c., on the master database are echoed to the slave. I know rserv can't do that, and I don't _think_ dbmirror can, either. But both of those are possibilities. AFAIK, the contrib/rserv code is completely broken in the 7.3 series, so dbmirror might be the free answer to pick; rserv has a commercial cousin which we use. The problem with these is that they send _data_ to the slave, and use standard SQL statements. So sequences c. are not carried over. You need a script to do that. It's not an instantaneous failover. On the other hand, if your client is comparing with Access, the reliability will be so much better that perhaps some extra work in the rare case of failure will be acceptable. It is for us, and we have extremely stringent SLAs which constrain how long we could be down if (heaven forfend!) our master database ever died. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] Database replication... - Mission Critical DBMS's --
On Wed, Oct 30, 2002 at 10:18:47AM -0500, Robert Treat wrote: regarding replication, as well as a high availability how-to that would probably be worth reading. The high availability howto suggests using rsync to synchronise the data areas of two data servers. That is an _extremely bad_ idea. I've suggested before that the link be removed, because it recommends something almost guaranteed to introduce massive database corruption at some point. If there's no load and you have a fast network, you might get lucky. But it is an extremely dangerous plan. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] float output precision questions
On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: Stephan Szabo wrote: On Tue, 29 Oct 2002, Peter Eisentraut wrote: Pedro Miguel Frazao Fernandes Ferreira writes: Is there a way to set query output precision to maximum precision ? For the type of application I mentioned this is crucial. People want to get the 'same' numbers, from querys or dumps, as they inserted them. How do you define maximum precision and same? With simple test programs in C, using two digits more than DBL_DIG for printf specifier, it's easy for me to find numbers that change decimal string representation in the decimal representation - double - decimal representation conversion(*). The final double you get from the second conversion should be the same as the first, but is that what you need or do you need a stronger guarantee than that? When I say same I am talking about having a number 'stored' in double format in some client, inserting it in PostgreSQL float8 field and get it to the client as it was before: [Some client] (insert) [PostgreSQL] (query) [Some client] (double number a)(float8 number)---(double number b) same is so that a==b is true. With current float8 output this is not allways true. I believe this should allways be true for numbers which are originally stored in double format. The problem is that there are two competing needs here. One is the above, the other other is that you get something that has the same decimal representation (within the float's ability to store the number). Right now the system does the latter since for most people, that seems to be the guarantee they want. This would probably make sense as an option, so why don't you look at the past discussions and see if you can come up with a solution that keeps everyone happy (and preferably implement it, but...) :) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] float output precision questions
Stephan Szabo wrote: On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: Stephan Szabo wrote: On Tue, 29 Oct 2002, Peter Eisentraut wrote: Pedro Miguel Frazao Fernandes Ferreira writes: Is there a way to set query output precision to maximum precision ? For the type of application I mentioned this is crucial. People want to get the 'same' numbers, from querys or dumps, as they inserted them. How do you define maximum precision and same? With simple test programs in C, using two digits more than DBL_DIG for printf specifier, it's easy for me to find numbers that change decimal string representation in the decimal representation - double - decimal representation conversion(*). The final double you get from the second conversion should be the same as the first, but is that what you need or do you need a stronger guarantee than that? When I say same I am talking about having a number 'stored' in double format in some client, inserting it in PostgreSQL float8 field and get it to the client as it was before: [Some client] (insert) [PostgreSQL] (query) [Some client] (double number a)(float8 number)---(double number b) same is so that a==b is true. With current float8 output this is not allways true. I believe this should allways be true for numbers which are originally stored in double format. The problem is that there are two competing needs here. One is the above, the other other is that you get something that has the same decimal representation (within the float's ability to store the number). Right now the system does the latter since for most people, that seems to be the guarantee they want. Look at this example: 1.79769313486231571e+308 is the largest floating point number representable by a C double in x86. In C this is possible: #include stdio.h #include stdlib.h int main(void) { double v; char a[30]; v=1.79769313486231571e+308; printf( Stored double number: %25.18g\n,v); sprintf(a,%25.18g,v); printf(Converted to string: %s\n,a); v=atof(a); printf(Converted from string to double: %25.18g\n,v); } Using standard PostgreSQL query output it would not be possible to get this number, which has representation as a double. I fetched the PostgreSQL source from Debian, changed src/backend/utils/adt/float.c to do sprintf(ascii, %25.18g, num) instead of sprintf(ascii, %.*g, DBL_DIG, num), compiled and installed. Now I can get the number as is. I understand that if people insert a value of 1.1 in a double, they want to get 1.1 without knowing that in fact the stored number is 1.10009. But do you understand that if some people insert, for example, a value of 1.79769313486231571e+308 they dont want to get 1.79769313486232e+308 which does not compare equal (in Matlab or C) to the first ? This is a bug. This would probably make sense as an option, so why don't you look at the past discussions and see if you can come up with a solution that keeps everyone happy (and preferably implement it, but...) :) but ??? I have a sugestion: To have parameters, say DOUBLE_FORMAT and FLOAT_FORMAT, which could have option values of 'SHORT' and 'LONG'. Option 'SHORT' would be default and produce the standard sprintf(ascii,... Option 'LONG' would produce sprintf(ascii, %25.18g, num). Other way would be to have number parameters to be used in the sprintf calls, in place of 25 and 18, in the format string. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- -- Pedro Miguel Frazao Fernandes Ferreira Universidade do Algarve Faculdade de Ciencias e Tecnologia Campus de Gambelas 8000-117 Faro Portugal Tel./Fax: (+351) 289 800950 / 289 819403 http://w3.ualg.pt/~pfrazao ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Trigger on 'create table' ?
Paul Ramsey [EMAIL PROTECTED] writes: Now, if we changed the geometry type so that when one defined a geometry column, one had to include info about what SRID and what dimension it was, (ala varchar(243)) maybe the whole schmeer could reside in pg_class and geometry_columns would be a trivial view? atttypmod would be a natural place for that stuff, if you can fit it into 31 bits. The bigger problem is how are you going to get type-specific syntax through the Postgres parser? Currently, all the types that use typmod have to have special-purpose syntax to set it. I'm not eager to see extension datatypes trying to do the same thing... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 7.3b3 ok on unixware 71[12] here
I'm glad to tell that I compiled and run regression tests ok on unixware 711 and 712 (Openunix 800) as well as on mac OS X 10.2.1 (execpt for float8) However, I'm still struggling to make plperl work on perl 561 and Unixware. I've scanned te Web and every thing without comming to a clue of what to do. It DID work on pg 723 though... Regards, -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.3b3 ok on unixware 71[12] here
On Wed, 2002-10-30 at 12:08, Olivier PRENANT wrote: I'm glad to tell that I compiled and run regression tests ok on unixware 711 and 712 (Openunix 800) as well as on mac OS X 10.2.1 (execpt for float8) However, I'm still struggling to make plperl work on perl 561 and Unixware. I've scanned te Web and every thing without comming to a clue of what to do. It DID work on pg 723 though... I've got it running with 5.8.0 of Perl. Is there anything holding you back from updating to 5.8.0 of PERL? Regards, -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.3b3 ok on unixware 71[12] here
On 30 Oct 2002, Larry Rosenman wrote: Date: 30 Oct 2002 12:14:01 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] 7.3b3 ok on unixware 71[12] here On Wed, 2002-10-30 at 12:08, Olivier PRENANT wrote: I'm glad to tell that I compiled and run regression tests ok on unixware 711 and 712 (Openunix 800) as well as on mac OS X 10.2.1 (execpt for float8) However, I'm still struggling to make plperl work on perl 561 and Unixware. I've scanned te Web and every thing without comming to a clue of what to do. It DID work on pg 723 though... I've got it running with 5.8.0 of Perl. Is there anything holding you back from updating to 5.8.0 of PERL? Unfortunatly yes! Majordomo2 doesn't work quit well with 580. Is there anyway to ake 561 compile the way 580 does for dynaloader? Regards, -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] move 0 behaviour
Bruce Momjian [EMAIL PROTECTED] writes: I did some research on this. It turns out the parser uses 0 for ALL, so when you do a FETCH ALL it is passing zero. Now, when you do MOVE 0, you are really asking for FETCH ALL and all the tuples are thrown away because of the MOVE. Yeah. I think this is a bug and MOVE 0 ought to be a no-op ... but changing it requires a different parsetree representation for MOVE ALL, which is tedious enough that it hasn't gotten done yet. I have the following patch which just documents the fact that MOVE 0 goes to the end of the cursor. It does not change any behavior, just document it. It should be documented as behavior that is likely to change. Also, I believe FETCH 0 has the same issue. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] float output precision questions
Bruno Wolff III writes: On Tue, Oct 29, 2002 at 23:19:05 +0100, Peter Eisentraut [EMAIL PROTECTED] wrote: There isn't a way right now, but it's planned to be able to dump floating-point numbers in some binary form (like printf(%A)) to be able to restore them exactly. Not sure how that would satisfy the needs of client interfaces, though. Why not print it as a floating binary number instead of a floating decimal number? I would think that would give you better portability than a system specific binary representation. The printf(%A) output is not system-specific. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] What user to defaults execute as?
On Wed, Oct 30, 2002 at 08:27:37 -0600, Bruno Wolff III [EMAIL PROTECTED] wrote: Do default expressions execute with access of the user doing the insert or the owner of the table? What I was thinking was that the owner of a table may want to allow people to do inserts into a table and update a sequence through a default expression, but not let people change the sequence directly. A quick look under create table (in the 7.3b3 docs) didn't turn up any information and I don't remember running accross anything covering this previously. OK, I went and tested this and I found that default expressions (at least nextval) are executed with the permissions of the user doing the insert and not the owner of the table. I would like to suggest this be changed for two reasons: It may not be obvious to the end user that inserting a row into a table may result in other things being done with their access. I.e. you can use a default expression as a trojan and by getting someone to do an insert into a table you created can be used to do bad things. I think that the table owner may want to let be insert rows into a table with a serial column, but not want to also let people reset the sequence number to arbitrary values. This would be similar to rules working with the access of the rule owner. While I am not sure about triggers, it certainly is possible to get a similar effect be having the referenced function run with the security of the definer. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] float output precision questions
On Wed, 30 Oct 2002, Pedro M. Ferreira wrote: Stephan Szabo wrote: On Wed, 30 Oct 2002, Pedro Miguel Frazao Fernandes Ferreira wrote: I understand that if people insert a value of 1.1 in a double, they want to get 1.1 without knowing that in fact the stored number is 1.10009. But do you understand that if some people insert, for example, a value of 1.79769313486231571e+308 they dont want to get 1.79769313486232e+308 which does not compare equal (in Matlab or C) to the first ? This is a bug. I disagree to some extent. I'm not sure it's meaningful to expect that (what if the database and the client are on different architectures) in general. In any case, you're effectively going from decimal representation to double to decimal representation (the string you used to insert it - internal representation - string used to output it) and that's only guaranteed to be correct up to DBL_DIG digits as far as I can tell. I think it'd be nice to have an option to get more digits for those sorts of applications, however. In the previous email example, in C, I was going from decimal to double and so on, but this is not the case when I do some simulation. In this case it will allways be from Matlab double to PostgreSQL float8 and from libpq PQgetvalue() string to Matlab double. The example was just a x86 number example where (got the string from Matlab double) query output would fail. I was just responding to it being a bug. I don't think that expecting a float-db-float (double-db-double) giving the same double is always safe when you assume that the PostgreSQL machine might be on a system with different guarantees about precision. In practice, it's probably not a big deal. , but I realize that you might not be interested in doing such. (I figured the last part was implied) ok! :) No problem. I have seen the GUC thing in the source (guc.c etc...) and it does not look too dificult. It has lots of examples in the code itself. What I am saying is that I can do it if pg-people agree on the (some) way to do it. Yeah, I didn't think it'd be hard, but sometimes people are unable or unwilling to do C code for things. I looked at some of these emails and it seemed to me that the problem was that Tom did'nt want a parameter that would force people to know about printf number formatting. I think the first solution above (the SHORT and LONG way) is simple, maintains usual output as default and enables 'maximum' precision at request. That seems reasonable then, Tom'll probably give any other objections he might have if he has any. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] 7.3b3 Regression tests passed on i386 Debian
Debian GNU/Linux unstable version, build from source on i386 SMP (dual Athlon MP): all regression tests passed. Change to source required: add CFLAGS += -D_GNU_SOURCE in src/pl/plperl/GNUMakefile. ./configure --enable-recode --with-pgport=5678 --with-tcl --with-perl --with-python --with-pam --with-openssl --with-gnu-ld --with-tclconfig=/usr/lib/tcl8.3 --with-tkconfig=/usr/lib/tk8.3 --with-includes=/usr/include/tcl8.3 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Every good gift and every perfect gift is from above, and cometh down from the Father of lights, with whom is no variableness, neither shadow of turning. James 1:17 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Request for supported platforms
I have reviewed your diff and found that it was either timezone changes or join.sql which I have recently fixed. Can you grab current CVS snapshot and try again? --- Ian Barwick wrote: On Tuesday 29 October 2002 01:56, Bruce Momjian wrote: Sorry, Ian, here is the patch I applied. You can apply this to whatever version you are using and test Irix with that, rather than having to grab CVS. OK, I have carried out make check with the updated tests but got FAILED on the same four tests (abstime, tinterval, horology, join). The failures are subtly different (see here for new regression diff): http://home.akademie.de/~IBarwick/IRIX_65_1.regression.diffs Having looked at them again I see the following: - in horology the timestamp tests seem to have succeeded (presumably the previous failures were triggered by the change to winter time); - the tests which are still failing in abstime, tinterval and horology all refer to dates before 1970, where AFAICS they are all out by one hour; possibly this explanation?: Some systems using older time zone libraries fail to apply daylight-saving corrections to dates before 1970, causing pre-1970 PDT times to be displayed in PST instead. This will result in localized differences in the test results. (cf. http://www.postgresql.org/idocs/index.php?regress-evaluation.html ); - the join tests are failing slightly differently; I would suggest that this is because the ORDER BY is still not explicit enough, and for what ever reason under IRIX the undefined result row orderings are in a different order to every other platform... e.g. with this statement: SELECT '' AS xxx, J1_TBL.i, j, t, k FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i) ORDER BY i; xxx | i | j | t | k -+---+---+---+ | 0 | | zero | | 1 | 4 | one | -1 | 2 | 3 | two | 2 | 2 | 3 | two | 4 | 3 | 2 | three | -3 | 4 | 1 | four | | 5 | 0 | five | -5 | 5 | 0 | five | -5 | 6 | 6 | six | | 7 | 7 | seven | | 8 | 8 | eight | | | 0 | zero | | | | null | (13 rows) the order of the last two rows is not defined. The expected order according to the regression tests is: | | | null | | | 0 | zero | Ian Barwick [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Turning the PLANNER off
Ah, so Ben finally got around to posting here. Ben's a CS Grad student here at Rice. His (current) project involves taking some interesting results from constraint satisfaction and implementing them on a database: one of the CS faculty has demonstrated that one class of highly joined DB queries maps to a solved problem in constraint satisfaction. The end goal would be an optimizer module or setting that recognizes this class of query, and spits out a mathematically optimized join order. So, in the interim, Ben's trying to do it by hand: preorder the joins and demonstrate that the 'best' order is in fact the best. Then move on to looking into integrating this, if possible: part of the problem is recognizing the structure of the query, of course. Right now, the planner is getting in the way - although he can extract the needed timing info, he's wasting CPU cycles planning things that don't need it, limiting the number of cases he can try. As mentioned before, there's currently no interface to feed in a Plan, so he's out of luck. Is there, programmatically, a way to do it? Serialize a plan tree to a file, and feed it in latter, purely for development purposes. How painful would that be? Should I send him in to see if he can implement one quickly, or are there dragons hiding in there? Ross On Mon, Oct 28, 2002 at 07:55:02PM -0500, Bruce Momjian wrote: That is a good question. The planner does more than just analyse the query. It generates the Plan used by the executor, so that can't be removed. It is always a pain when the optimizer/planner takes longer than the executor. We do have PREPARE/EXECUTE in 7.3beta for you to use. --- Ben McMahan wrote: I'm looking at different ways of optimizing queries with a large number of joins. I write the same query in a number of different ways and compare the running times. Now the problem is I do not want the optimizer changing the queries. So I explicit state the order of the joins in the FROM clause. I also turn off everything I can except for one type of join (say hash join), and I've turned off geqo. But I find that the PLANNER still takes an enormous amount of time for some queries. It doesn't look like the Planner is actually optimizing (changing) anything, but just in case, I was wondering if there was a way to turn off the PLANNER. Note, when I say an enormous amount of time, I mean at least double the time the EXECUTOR takes to actually answer the query. Thanks for your help, Ben McMahan ps. here is a small example of what my queries look like (so you can see if there is something else it might be deciding on): SELECT DISTINCT c0.x1 , c1.x2 , c0.x3 , c0.x4 , c2.x5 FROM r1 c4 (x4,x2,x5) JOIN (r0 c3 (x2,x3,x5) JOIN (r2 c2 (x3,x1,x5) JOIN (r1 c1 (x4,x1,x2) JOIN r1 c0 (x1,x3,x4) ON ( c0.x4 = c1.x4 AND c0.x1 = c1.x1 )) ON ( c0.x3 = c2.x3 AND c0.x1 = c2.x1 )) ON ( c1.x2 = c3.x2 AND c0.x3 = c3.x3 AND c2.x5 = c3.x5 )) ON ( c0.x4 = c4.x4 AND c1.x2 = c4.x2 AND c2.x5 = c4.x5 ); A quick reminder, FROM r1 c4 (x4,x2,x5) just renames a table r1 into c4 where it also renames the columns to x4, x2, and x5 respectively. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] float output precision questions
Stephan Szabo [EMAIL PROTECTED] writes: On Wed, 30 Oct 2002, Pedro M. Ferreira wrote: I looked at some of these emails and it seemed to me that the problem was that Tom did'nt want a parameter that would force people to know about printf number formatting. I think the first solution above (the SHORT and LONG way) is simple, maintains usual output as default and enables 'maximum' precision at request. That seems reasonable then, Tom'll probably give any other objections he might have if he has any. My recollection is that other people (perhaps Peter?) were the ones objecting before. However I'd be somewhat unhappy with the proposal as given: Option 'SHORT' would be default and produce the standard sprintf(ascii,... Option 'LONG' would produce sprintf(ascii, %25.18g, num). since this seems to me to hardwire inappropriate assumptions about the number of significant digits in a double. (Yes, I know practically everyone uses IEEE floats these days. But it's inappropriate for PG to assume that.) AFAICT the real issue here is that binary float representations will have a fractional decimal digit of precision beyond what DBL_DIG claims. I think I could support adding an option that switches between the current output format: sprintf(ascii, %.*g, DBL_DIG, num); and: sprintf(ascii, %.*g, DBL_DIG+1, num); and similarly for float4. Given carefully written float I/O routines, reading the latter output should reproduce the originally stored value. (And if the I/O routines are not carefully written, you probably lose anyway.) I don't see a need for allowing more flexibility than that. Comments? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Turning the PLANNER off
Ross J. Reedstrom [EMAIL PROTECTED] writes: So, in the interim, Ben's trying to do it by hand: preorder the joins and demonstrate that the 'best' order is in fact the best. Then move on to looking into integrating this, if possible: part of the problem is recognizing the structure of the query, of course. Right now, the planner is getting in the way - although he can extract the needed timing info, he's wasting CPU cycles planning things that don't need it, limiting the number of cases he can try. What's the basis for your assertion that it's planning things that don't need it? Given a JOIN-constrained query I do not believe the planner will look at any cases other than the intended join order. As mentioned before, there's currently no interface to feed in a Plan, so he's out of luck. Is there, programmatically, a way to do it? Serialize a plan tree to a file, and feed it in latter, purely for development purposes. How painful would that be? Should I send him in to see if he can implement one quickly, or are there dragons hiding in there? He can do whatever he wants, as long as he has no illusions about getting it accepted back into the sources ;-). What would probably be more useful is to do some profiling to understand why the planner is taking longer than he wants even with a JOIN-constrained query. I should think this would be pretty quick. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] move 0 behaviour
Peter Eisentraut wrote: Bruce Momjian writes: So, that is why MOVE 0 goes to the end of the cursor. One idea would be for MOVE 0 to actually move nothing, but jdbc and others need the ability to move the end of the cursor, perhaps to then back up a certain amount and read from there. Seems MOVE 0 is the logical way to do that. (I can't think of another reasonable value). It would seem more logical and reasonable for MOVE 0 to do nothing and have some special syntax such as MOVE LAST to move to the end. (MOVE LAST would actually be consistent with the standard syntax FETCH LAST.) Yea, I started thinking and we need to get MOVE/FETCH to make sense. The following patch makes FETCH/MOVE 0 do nothing, and FETCH LAST move to the end. I was going to use the word END, but if LAST is more standard, we will use that. It uses INT_MAX in the grammar for FETCH ALL/MOVE LAST, but maps that to zero so it is consistent in the /executor code. I will keep this patch for 7.4. JDBC folks, I realize you need this. Seems you will have to use MOVE 0 for 7,3 and MOVE LAST for 7.4. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/ref/move.sgml === RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/move.sgml,v retrieving revision 1.13 diff -c -c -r1.13 move.sgml *** doc/src/sgml/ref/move.sgml 21 Apr 2002 19:02:39 - 1.13 --- doc/src/sgml/ref/move.sgml 31 Oct 2002 01:15:42 - *** *** 21,27 date1999-07-20/date /refsynopsisdivinfo synopsis ! MOVE [ replaceable class=PARAMETERdirection/replaceable ] [ replaceable class=PARAMETERcount/replaceable ] { IN | FROM } replaceable class=PARAMETERcursor/replaceable /synopsis /refsynopsisdiv --- 21,28 date1999-07-20/date /refsynopsisdivinfo synopsis ! MOVE [ replaceable class=PARAMETERdirection/replaceable ] ! {replaceable class=PARAMETERcount/replaceable | LAST } { IN | FROM } replaceable class=PARAMETERcursor/replaceable /synopsis /refsynopsisdiv Index: src/backend/commands/portalcmds.c === RCS file: /cvsroot/pgsql-server/src/backend/commands/portalcmds.c,v retrieving revision 1.3 diff -c -c -r1.3 portalcmds.c *** src/backend/commands/portalcmds.c 4 Sep 2002 20:31:15 - 1.3 --- src/backend/commands/portalcmds.c 31 Oct 2002 01:15:44 - *** *** 15,20 --- 15,22 #include postgres.h + #include limits.h + #include commands/portalcmds.h #include executor/executor.h *** *** 55,61 * *name: name of portal *forward: forward or backward fetch? ! *count: # of tuples to fetch (0 implies all) *dest: where to send results *completionTag: points to a buffer of size COMPLETION_TAG_BUFSIZE *in which to store a command completion status string. --- 57,63 * *name: name of portal *forward: forward or backward fetch? ! *count: # of tuples to fetch *dest: where to send results *completionTag: points to a buffer of size COMPLETION_TAG_BUFSIZE *in which to store a command completion status string. *** *** 100,105 --- 102,115 return; } + /* If zero count, we are done */ + if (count == 0) + return; + + /* Internally, zero count processes all portal rows */ + if (count == INT_MAX) + count = 0; + /* * switch into the portal context */ Index: src/backend/executor/execMain.c === RCS file: /cvsroot/pgsql-server/src/backend/executor/execMain.c,v retrieving revision 1.180 diff -c -c -r1.180 execMain.c *** src/backend/executor/execMain.c 14 Oct 2002 16:51:30 - 1.180 --- src/backend/executor/execMain.c 31 Oct 2002 01:15:50 - *** *** 1119,1125 /* * check our tuple count.. if we've processed the proper number !* then quit, else loop again and process more tuples.. */ current_tuple_count++; if (numberTuples == current_tuple_count) --- 1119,1126 /* * check our tuple count.. if we've processed the proper number !* then quit, else loop again and process more tuples. Zero !* number_tuples means no limit. */ current_tuple_count++; if (numberTuples == current_tuple_count) Index: src/backend/parser/gram.y
Re: [HACKERS] move 0 behaviour
Bruce Momjian [EMAIL PROTECTED] writes: The following patch makes FETCH/MOVE 0 do nothing, and FETCH LAST move to the end. Do not hack up PerformPortalFetch; put the special case for INT_MAX in utility.c's FetchStmt code, instead. As-is, you probably broke other callers of PerformPortalFetch. BTW, there's a comment in parsenodes.h that needs to be fixed too: inthowMany;/* amount to fetch (ALL -- 0) */ regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PostgreSQL Installation on SCO
Dear Sir, I use SCO Open Server 5.0.5 on an intel box. Although I have installed and used PostgreSQL on Linux, setting it on SCO has not been smooth :) I have downloaded the latest version ie Postgresql-7.2.3. I also installed ant package for using java. I have tcl8.0, tk8.0, itclsh3.0 and itkwish3.0 installed in my system. gcc version 2.7.2.1 Java 2 SDK, Standard Edition, v. 1.2.1 My configure command was as following ... ./configure --prefix=/data/pgsql --with-perl --with-tcl --with-tclconfig=/data/tcl/lib --with-tkconfig=/data/tk/lib --with-java The last output were ... updating cache ./config.cache creating ./config.status creating GNUmakefile creating src/GNUmakefile creating src/Makefile.global creating src/backend/port/Makefile creating src/include/pg_config.h linking ./src/backend/port/dynloader/sco.c to src/backend/port/dynloader.c linking ./src/backend/port/dynloader/sco.h to src/include/dynloader.h linking ./src/include/port/sco.h to src/include/pg_config_os.h linking ./src/makefiles/Makefile.sco to src/Makefile.port linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.s Then i give the gmake command. The compiling stops on an error and exits after some time. The messages in this step 2 (step2mesg.txt) and the errors (step2err.txt) are reproduced in the 2 text files attached. I'd be thankful to you if you can help me out sort the problem. I got your mail-ids from the net and came to know that you are working on the similar lines. Waiting for a quick response from your end. kindly inform me if you have already solved the problem, or whether any patch is available. Any documentation or url will be highly helpful. Thanking You in anticipation. with regards Shibashish Satpathy [EMAIL PROTECTED] Software Engineer IIT Bombay, India gmake -C doc all gmake[1]: Entering directory `/data/postgres/postgresql-7.2.3/doc' gmake[1]: Nothing to be done for `all'. gmake[1]: Leaving directory `/data/postgres/postgresql-7.2.3/doc' gmake -C src all gmake[1]: Entering directory `/data/postgres/postgresql-7.2.3/src' gmake -C backend all gmake[2]: Entering directory `/data/postgres/postgresql-7.2.3/src/backend' prereqdir=`cd parser/ /dev/null pwd` \ cd ../../src/include/parser/ rm -f parse.h \ ln -s $prereqdir/parse.h . gmake -C utils fmgroids.h gmake[3]: Entering directory `/data/postgres/postgresql-7.2.3/src/backend/utils' CPP='gcc -E' AWK='nawk' /bin/sh Gen_fmgrtab.sh ../../../src/include/catalog/pg_proc.h gmake[3]: Leaving directory `/data/postgres/postgresql-7.2.3/src/backend/utils' cd ../../src/include/utils/ rm -f fmgroids.h \ ln -s ../../../src/backend/utils/fmgroids.h . gmake -C access all gmake[3]: Entering directory `/data/postgres/postgresql-7.2.3/src/backend/access' gmake -C common SUBSYS.o gmake[4]: Entering directory `/data/postgres/postgresql-7.2.3/src/backend/access/common' gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c heaptuple.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c indextuple.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c indexvalid.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c printtup.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c scankey.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c tupdesc.c /usr/ccs/bin/ld -r -o SUBSYS.o heaptuple.o indextuple.o indexvalid.o printtup.o scankey.o tupdesc.o gmake[4]: Leaving directory `/data/postgres/postgresql-7.2.3/src/backend/access/common' gmake -C gist SUBSYS.o gmake[4]: Entering directory `/data/postgres/postgresql-7.2.3/src/backend/access/gist' gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c gist.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c gistget.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c gistscan.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c giststrat.c /usr/ccs/bin/ld -r -o SUBSYS.o gist.o gistget.o gistscan.o giststrat.o gmake[4]: Leaving directory `/data/postgres/postgresql-7.2.3/src/backend/access/gist' gmake -C hash SUBSYS.o gmake[4]: Entering directory `/data/postgres/postgresql-7.2.3/src/backend/access/hash' gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c hash.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c hashfunc.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c hashinsert.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c hashovfl.c gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations