Re: [GENERAL] Scripting function definitions as SQL?
On Mon, May 12, 2008 at 10:57 AM, Reece Hart [EMAIL PROTECTED] wrote: On Sun, 2008-05-11 at 06:12 -0700, Postgres User wrote: Has anyone written a function that scripts out all the functions in a database as full SQL statements (Create Function.) You could pg_dump the schema in the custom format (-Fc), then call pg_restore with -l to get the TOC, grep the TOC for functions, and feed that back into pg_restore with -L. It sounds like a lot, but it's pretty easy in practice, like so: $ sudo -u postgres pg_dump -Fc -s mydb mydb.pgdfc $ pg_restore -l mydb.pgdfc mydb.toc $ grep -E '^[0-9]+; [0-9]+ [0-9]+ FUNCTION' mydb.toc mydb-fx.toc $ pg_restore -L mydb-fx.toc mydb.pgdfc The output of pg_restore is sql. This technique is extremely useful for other kinds of schema elements as well. It's a bit too much for the task at hand. Recently I was also faced with a situation where I wanted to dump a few functions (only), but pg_dump does not have any option to do so!! Can we have an option to dump function? Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
[GENERAL] ORDER BY FIELD feature
Does postgresql have something similar to the ORDER BY FIELD feature found in MySQL? Something like ORDER BY FIELD(ID, 10, 2, 56, 40); It is listed here: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html I am using the Sphinx Search program, www.sphinxsearch.com, and it returns a list of IDs ordered by relevance. I take the array it gives me and create a comma separated list which is used in a postgres query using the WHERE ID IN (10, 2, 56, 40) statement. I would like to order the result by the order of the list. Thanks. - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
Re: [GENERAL] Returning RECORD from PGSQL without custom type?
Hello 2008/5/10 D. Dante Lorenso [EMAIL PROTECTED]: Instead of doing this: CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint) RETURNS SETOF record AS $body$ ... $body$ LANGUAGE 'plpgsql' VOLATILE; I'd like to be able to do this: CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint) RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS $body$ ... $body$ LANGUAGE 'plpgsql' VOLATILE; Standard syntax via ANSI SQL is CREATE FUNCTION foo(params) RETURNS TABLE(fields of output table) AS $$ ... $$ Because this is the only function that will be returning that TYPE and I don't want to have to create a separate type definition just for the return results of this function. Maybe even more cool would be if the OUT record was already defined so that I could simply select into that record to send our new rows: RETURN NEXT OUT; OUT.col1name := 12345; RETURN NEXT OUT; SELECT 12345, 'sample' INTO OUT.col1name, OUT.col2name; RETURN NEXT OUT; it's good idea - it was probably main problem of last patch in plpgsql. In this syntax is clear what is output, so RETURN NEXT statement can be without params. I am only not sure about name of default variable - maybe result is better. Regards Pavel Stehule Just as you've allowed me to define the IN variable names without needing the legacy 'ALIAS $1 ...' format, I'd like to name the returned record column names and types in a simple declaration like I show above. Does this feature request make sense to everyone? It would make programming set returning record functions a lot easier. -- Dante -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ORDER BY FIELD feature
Kevin Reynolds a écrit : Does postgresql have something similar to the ORDER BY FIELD feature found in MySQL? Something like ORDER BY FIELD(ID, 10, 2, 56, 40); It is listed here: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html I am using the Sphinx Search program, www.sphinxsearch.com http://www.sphinxsearch.com, and it returns a list of IDs ordered by relevance. I take the array it gives me and create a comma separated list which is used in a postgres query using the WHERE ID IN (10, 2, 56, 40) statement. I would like to order the result by the order of the list. ORDER BY ID=10 DESC, ID=2 DESC, ID=56 DESC, ID=40 DESC is the only way, AFAIK. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Returning RECORD from PGSQL without custom type?
Instead of doing this: CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint) RETURNS SETOF record AS $body$ ... $body$ LANGUAGE 'plpgsql' VOLATILE; I'd like to be able to do this: CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint) RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS $body$ ... $body$ LANGUAGE 'plpgsql' VOLATILE; Because this is the only function that will be returning that TYPE and I don't want to have to create a separate type definition just for the return results of this function. Maybe even more cool would be if the OUT record was already defined so that I could simply select into that record to send our new rows: RETURN NEXT OUT; OUT.col1name := 12345; RETURN NEXT OUT; SELECT 12345, 'sample' INTO OUT.col1name, OUT.col2name; RETURN NEXT OUT; Just as you've allowed me to define the IN variable names without needing the legacy 'ALIAS $1 ...' format, I'd like to name the returned record column names and types in a simple declaration like I show above. Does this feature request make sense to everyone? It would make programming set returning record functions a lot easier. -- Dante -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:
PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: I have setup PHP/Postgres and is running fine upto document root i.e. /var/www/html, but when i am calling it through a cgi-bin php file it is giving log error as : [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF; what may be the solution. --Abdus Samad Ansari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:
Hi, Abdus Samad Ansari wrote: PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: I have setup PHP/Postgres and is running fine upto document root i.e. /var/www/html, but when i am calling it through a cgi-bin php file it is giving log error as : [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF; well there are a couple of checks you could do to find out: 1) decide how to connect - via IP or via unix socket 2) in each of the above see if postmaster is indeed listening to that socket (netstat is your friend) - adjust postgresql.conf and/or start options accordingly. depending on your distribution you might want to enable logging 3) try connection via psql command line client - see if you have matching pg_hba.conf entries 4) try to connect via psql command line client with the same user your script runs (apache in your case) - same as (3) but ident vs. md5 (password) in pg_hba.conf after all you might want to use some pooling layer because pg_connect from cgi can get very expensive (also note that outside of PHP there are a lot possible elegant solutions to that) Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:
hello, most crucial information is missing, like: - your operating system - your postgresql version - on which computer is your database server running - is your database server running But most likely your problem is that you did not configure PostgreSQL to listen to TCP/IP-requests. Which again makes it most likely that you are using a way outdated PostgreSQL Version (that ist 8.x) Please update to 8.3.1, and adjust your postgresql.conf, parameter listen_addresses (string) accordingly. Best wishes, Harald On Mon, May 12, 2008 at 11:58 AM, Abdus Samad Ansari [EMAIL PROTECTED] wrote: PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: I have setup PHP/Postgres and is running fine upto document root i.e. /var/www/html, but when i am calling it through a cgi-bin php file it is giving log error as : [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF; what may be the solution. --Abdus Samad Ansari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:
Abdus Samad Ansari [EMAIL PROTECTED] writes: I have setup PHP/Postgres and is running fine upto document root i.e. /var/www/html, but when i am calling it through a cgi-bin php file it is giving log error as : [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF; Hmm, so the most useful part of the error message is getting trashed :-( One possible explanation for that is that you're using a non-thread-safe libpq in a threaded environment. What PG version is your libpq, exactly, and how did you build or obtain it? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:
Abdus Samad Ansari [EMAIL PROTECTED] writes: I have setup PHP/Postgres and is running fine upto document root i.e. /var/www/html, but when i am calling it through a cgi-bin php file it is giving log error as : [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF; I don't know how the error message is structured, but if \x04\xe6\xe3\xbfP refers to the host, perhaps the connection string you are handing to pg_connect() is trashed. What is the actual code you are using for this call? -- Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to create a function with multiple RefCursor OUT parameters
On Sun, May 11, 2008 at 2:43 PM, Chuck Bai [EMAIL PROTECTED] wrote: CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT o_user refcursor, OUT o_name refcursor) RETURNS record AS $BODY$ BEGIN tcount := tcount + 1; OPEN o_user FOR SELECT * FROM user_table; OPEN o_name FOR SELECT * FROM name_table; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE Question 1: The function is not working with Npgsql .NET data provider. It did not return a valid .NET DataSet. But the INOUT parameter tcount works fine. How could I test the above function with SQL in pgAdmin III? I want to find out if problem is in the function or in the Npgsql. You can test from pgAdmin by simply running queries in the query window. This sort of thing however might be a better fit for psql (pasting your queries in the query window). You need to use transactions since refcursors only only good inside a transaction. Question 2: pgAdmin III automatically added RETURNS record in the above function when RETURNS clause is not specified initially. Why is that? Is this the problem since it returns only single data table with the following value? How to fix it? For a function with 1 out parameters, the output type is a record. Your function returns (int, refcursor, refcursor) as defined. SELECT * FROM test_refcursor(7); would returns a row with three variables ( a record). tcount o_user o_name 23 unnamed portal 1 unnamed portal 2 You probably want to name your refcursors. The way to do this is simply o_user := 'something'; inside your pl/pgsql function. -- inside pl/pgsql_function refcur_variable := 'mycursor' -- outside function, but in same transaction FETCH ALL FROM mycursor -- or, mycursor So, it would at least take a few 'queries' from the perppective of the client to do what you are attempting. However, all the data is 'set up' for return to the client by the server in the main function. The server will hang on to it as long as the current transaction is valid and then release it. Question 3: I want to return a single DataSet with each OUT RefCursor map to a DataTable within the DataSet, plus extra OUT parameters for individual OUT values. How could I create such a function? Your question is a little opaque to me. A refcursor is in PostgreSQL terms a 'hande' to a set, not a DataTable the way you are thinking...it's really a fancy string. so, (INOUT int, OUT refcursor, OUT refcursor) returns takes an 'int' in and returns an int and two refcursors (strings), with extra work to return this to the client, at least in terms of SQL statements. I haven't used .net for a while but IIRC it's probably not possible to 'fill' multiple data tables in a single query without at least some manual work. Some of the npgsql experts might have some suggestions however. It really depends on how the code operates inside the npgsql library. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_standby / WAL archive-restore through system restarts
Just wanted to see if others would confirm my impressions about running WAL archiving and pg_standby restore. Server A (Primary): Runs PG 8.3 with WAL archiving enabled. Each WAL is copied over the network to Server B. (A previous 'tar backup' of the database along with the requisite psql commands pg_start_backup()/pg_stop_backup() was done and restored on Server B.) Server B (Backup): Runs PG 8.3 in 'recovery mode' (recovery.conf), with pg_standby feeding the WAL archives. The idea is that if Server A dies, Server B will have a relatively close snapshot of the most recent database changes and could take over as the new primary database server. During regular operations, Server A and Server B may independently need to be rebooted or postgresql restarted, etc. Is there any sync-ing between Server A and Server B I have to worry about when doing this? That is, will Server B need to know anything about the fact that the postmaster on Server A going up and down? Will it just run normally, waiting for the restarted Server A to send over new WAL archives? Can I shutdown Server B simply by killing the postmaster and restart it back in recovery mode to continue re-syncing where it left off? Or does stopping Server B in recovery mode require any manual re-sync steps before it can resume recovery? If I ever tell Server B to stop recovery (assuming I didn't intend for it to become the new primary), I cannot just resume recovery mode unless I do the full data backup again from Server A and restore on Server B in start in recovery mode. That is, once you tell PG to end recovery, it becomes a primary and no longer can resume recovery mode without redoing the recovery setup steps. Are these correct? Thanks, David -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] choiche of function language was: Re: dynamic procedure call
[EMAIL PROTECTED] (Ivan Sergio Borgonovo) writes: On Sat, 10 May 2008 07:35:36 +0200 Pavel Stehule [EMAIL PROTECTED] wrote: your application different execution paths. Generally I can say, so plpgsql isn't well language for this games, and better is using plperl, plpython or other external language. It is clear what is at least one of the advantage of plpython or plperl over plpgsql, but then what are the advantages of plpgsql over the rest of the crowd other than resembling the language used in Oracle? Well, plpgsql has the merit that its operations and control structures are directly oriented towards database stuff, so there's no need to (for instance) invoke functions (e.g. - running queries via spi_exec(), spi_query(), ...) in order to perform database operations. One of the salutory effects is that there is a pl/pgsql debugger that can automatically handle things like single-stepping, and it does not need to be aware of any Perl/Python internals. -- cbbrowne,@,linuxfinances.info http://linuxfinances.info/info/sap.html ...you might as well skip the Xmas celebration completely, and instead sit in front of your linux computer playing with the all-new-and-improved linux kernel version. -- Linus Torvalds -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Returning RECORD from PGSQL without custom type?
Pavel Stehule wrote: Hello 2008/5/10 D. Dante Lorenso [EMAIL PROTECTED]: Instead of doing this: CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint) RETURNS SETOF record AS $body$ ... $body$ LANGUAGE 'plpgsql' VOLATILE; I'd like to be able to do this: CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint) RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS $body$ ... $body$ LANGUAGE 'plpgsql' VOLATILE; Standard syntax via ANSI SQL is CREATE FUNCTION foo(params) RETURNS TABLE(fields of output table) AS $$ ... $$ Ah, this sound almost exactly like what I'm wanting! So ... you are saying that developers are working on something like? I'm running 8.3 ... would I find this feature in 8.4 or is it still not included in any release? Because this is the only function that will be returning that TYPE and I don't want to have to create a separate type definition just for the return results of this function. Maybe even more cool would be if the OUT record was already defined so that I could simply select into that record to send our new rows: RETURN NEXT OUT; OUT.col1name := 12345; RETURN NEXT OUT; SELECT 12345, 'sample' INTO OUT.col1name, OUT.col2name; RETURN NEXT OUT; it's good idea - it was probably main problem of last patch in plpgsql. In this syntax is clear what is output, so RETURN NEXT statement can be without params. I am only not sure about name of default variable - maybe result is better. Yeah, RESULT works too. I'm not particular about what it has to be ... just that something like that might exist. Where can I go to follow development of this or test it out? I see some old threads now that I know what to look for: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php http://archives.postgresql.org/pgsql-patches/2007-02/msg00216.php http://archives.postgresql.org/pgsql-patches/2007-02/msg00341.php http://archives.postgresql.org/pgsql-hackers/2007-09/msg01079.php I want to make sure this patch/proposal covers my needs and expectations. Specifically I want to return records that are not simple a straight query: CREATE OR REPLACE FUNCTION foo(f integer) RETURNS TABLE(a int, b int) AS $$ DECLARE my_a INT; my_b INT; BEGIN -- 1) perhaps like this SELECT 1, 2 INTO RESULT.a, RESULT.b; RETURN NEXT RESULT; -- 2) maybe like this RETURN NEXT 3, 4; -- a=3, b=4 -- 3) how about like this my_a := 5; my_b := 6; RETURN NEXT my_a, my_b; -- 4) maybe like this RETURN NEXT QUERY SELECT a, b FROM sometable x WHERE x.f = f; -- done RETURN; END; $$ LANGUAGE plpgsql; Usage: SELECT a, b FROM foo(20); Results: a | b ---+ 1 | 2 -- 1) 3 | 4 -- 2) 5 | 6 -- 3) ... -- 4) results from sometable WHERE x.f = 20 What do you think, will I be able to do all of this? -- Dante Regards Pavel Stehule Just as you've allowed me to define the IN variable names without needing the legacy 'ALIAS $1 ...' format, I'd like to name the returned record column names and types in a simple declaration like I show above. Does this feature request make sense to everyone? It would make programming set returning record functions a lot easier. -- Dante -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1
Craig Vosburgh craig(dot)vosburgh(at)cassatt(dot)com writes: We've dumped the locks and it shows that all locks have been granted so it appears that it is not a lock that is standing in our way. We've also gone in via psql while the update is hung and were able to perform an update on the offending table without issue. Finally, we have also enabled the statement_timeout and set it to five minutes and it does timeout the hung update and return to normal processing by rolling back the offending transaction but that's not a viable solution for us. Anyone have any words of wisdom on how to track this down? For starters, attach to the hung backend with gdb and get a stack trace ... regards, tom lane Tom, Got GDB installed on the errant node and did a back trace call (I'm guessing that is what you were looking for when you said stack trace) on the process that shows in the process table as executing the hung SQL command. The backtrace is: (gdb) bt #0 0x0088b7a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2 #1 0x0096f3ab in semop () from /lib/tls/libc.so.6 #2 0x081ba8f8 in PGSemaphoreLock () #3 0x081e4d9e in ProcSleep () #4 0x081e1db3 in GrantAwaitedLock () #5 0x0832f984 in twophase_recover_callbacks () #6 0x0006 in ?? () #7 0x0003 in ?? () #8 0x091c3770 in ?? () #9 0x091c3758 in ?? () #10 0xbfe63158 in ?? () #11 0xbfe634a0 in ?? () #12 0xbfe634b0 in ?? () #13 0x0832f984 in twophase_recover_callbacks () #14 0x08e8dc40 in ?? () #15 0x0006 in ?? () #16 0xbff32c72 in ?? () #17 0x08e68758 in ?? () #18 0xb792a9f8 in ?? () #19 0xbfe631b8 in ?? () #20 0xbfe630d0 in ?? () #21 0x081e1d88 in GrantAwaitedLock () #22 0x081d6722 in MarkBufferDirty () (gdb) Thanks for the help, -Craig -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] rounding problems
I have very annoying problem that i would like to get a work around in place so the data entry people stop trying to kill me. Normally people give quotes out of the price book which was done in Excel like 15 years ago and just has been updated over the years. the problem is excel is rounding differently than postgres 8.3.1 (Yes i know Excel rounds incorrectly) which results in normally being pennies off but on large qty its usually under a few bucks on the postgresql side. We internally don't care but those annoying customers scream bloody murder if the quote don't agree to the penny on the invoice Even when its to their benefit . Has anyone every got Postgresql and Excel to agree on rounding. I have checked excel up to Office XP and its still wrong. (open office was looked out and the people screamed really loudly NO ) Another annoying thing is the calculators on everyones desk get it wrong to if the rounding is turned to 2 places. Although my TI-89, and TI-36X calculators agree perfectly with postgresql . -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Returning RECORD from PGSQL without custom type?
D. Dante Lorenso [EMAIL PROTECTED] writes: I'd like to be able to do this: CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint) RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS You realize of course that you can do this *today* if you use OUT parameters? CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint, OUT col1name BIGINT, OUT col2name TEXT, ...) RETURNS SETOF RECORD AS The TABLE syntax is a bit more standards-compliant maybe, but it's not offering any actual new functionality. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
At 01:48 AM 5/13/2008, Justin wrote: I have very annoying problem that i would like to get a work around in place so the data entry people stop trying to kill me. Normally people give quotes out of the price book which was done in Excel like 15 years ago and just has been updated over the years. the problem is excel is rounding differently than postgres 8.3.1 (Yes i know Excel rounds incorrectly) which results in normally being pennies off but on large qty its usually under a few bucks on the postgresql side. We internally don't care but those annoying customers scream bloody murder if the quote don't agree to the penny on the invoice Even when its to their benefit . Has anyone every got Postgresql and Excel to agree on rounding. I have checked excel up to Office XP and its still wrong. (open office was looked out and the people screamed really loudly NO ) Another annoying thing is the calculators on everyones desk get it wrong to if the rounding is turned to 2 places. Although my TI-89, and TI-36X calculators agree perfectly with postgresql . Bad news, the Excel thing is probably doing math very wrong. Also, my guess is you're treating one penny as 0.01, which is also wrong. When you do financial calculations you should avoid floating point where possible. Floating point is really tricky to get right. There are scary books on it. I'm no expert in financial calculations and floating point stuff, my _guess_ is a good start is probably treating one penny as 1, instead of 0.01. But better wait for the experts to chime in. That said, if you're going to insist on using the wrong numbers from the Excel Invoice, can't you work some way of getting them into Postgresql and stored as is, rather than having Postgresql calculate them differently ( I suspect you're using floating point in postgresql and so it'll be wrong too, just maybe a bit less wrong than Excel ;) ). Regards, Link. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1
Craig Vosburgh [EMAIL PROTECTED] writes: Got GDB installed on the errant node and did a back trace call (I'm guessing that is what you were looking for when you said stack trace) on the process that shows in the process table as executing the hung SQL command. The backtrace is: (gdb) bt #0 0x0088b7a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2 #1 0x0096f3ab in semop () from /lib/tls/libc.so.6 #2 0x081ba8f8 in PGSemaphoreLock () #3 0x081e4d9e in ProcSleep () #4 0x081e1db3 in GrantAwaitedLock () #5 0x0832f984 in twophase_recover_callbacks () #6 0x0006 in ?? () Hmm. gdb is lying to you, because GrantAwaitedLock doesn't call ProcSleep --- probably line 4 should refer to WaitOnLock instead. You could try installing a non-symbol-stripped postgres executable (or in RPM environments, install the matching debuginfo RPM) to get a more trustworthy backtrace. However, what I suspect you are looking at is just a run-of-the-mill lock wait. You *sure* there's no ungranted locks showing in pg_locks? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Can you be more explicit about the rounding that's wrong in Excel? Are you talking about the n5 round-up to n+1 that Excel uses vs. n5 round-to-even n (sometimes called Banker's Rounding)? -- Andy On May 12, 2008, at 1:48 PM, Justin wrote: I have very annoying problem that i would like to get a work around in place so the data entry people stop trying to kill me. Normally people give quotes out of the price book which was done in Excel like 15 years ago and just has been updated over the years. the problem is excel is rounding differently than postgres 8.3.1 (Yes i know Excel rounds incorrectly) which results in normally being pennies off but on large qty its usually under a few bucks on the postgresql side. We internally don't care but those annoying customers scream bloody murder if the quote don't agree to the penny on the invoice Even when its to their benefit . Has anyone every got Postgresql and Excel to agree on rounding. I have checked excel up to Office XP and its still wrong. (open office was looked out and the people screamed really loudly NO ) Another annoying thing is the calculators on everyones desk get it wrong to if the rounding is turned to 2 places. Although my TI-89, and TI-36X calculators agree perfectly with postgresql . -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Returning RECORD from PGSQL without custom type?
2008/5/12 Tom Lane [EMAIL PROTECTED]: D. Dante Lorenso [EMAIL PROTECTED] writes: I'd like to be able to do this: CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint) RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS You realize of course that you can do this *today* if you use OUT parameters? CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint, OUT col1name BIGINT, OUT col2name TEXT, ...) RETURNS SETOF RECORD AS The TABLE syntax is a bit more standards-compliant maybe, but it's not offering any actual new functionality. it should minimalize columns and variables collision (for beginer users).There isn't new functionality, but it can be more accessible for new users. What I know, current syntax is for some people curios . Regars Pavel Stehule regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Andy Anderson wrote: Can you be more explicit about the rounding that's wrong in Excel? Are you talking about the n5 round-up to n+1 that Excel uses vs. n5 round-to-even n (sometimes called Banker's Rounding)? Yes i'm talking about difference between bankers rounding verse Excels crappy math. I have dealt with excels crappy math skills in scientific measurements dumped from AD cards, the simply solution was increase the decimal range to 1 more than i needed. But in this case it won't work sense this published material will disagree with how postgresql rounds. We take (List Price * discount Percent) * Number of Pieces = net price.List Prices is stored as numeric (16,4) discount is stored as numeric(10,4) the result is numeric (16,4). On the UI its rounded to 2 and displays correctly and agrees with my TI-89 The problem is the price book which is used to quotes is almost always 0.01 to 0.015 pennies higher. Net result the invoices are almost always lower than Quoted price. (yet customers still through a fit.) -- Andy On May 12, 2008, at 1:48 PM, Justin wrote: I have very annoying problem that i would like to get a work around in place so the data entry people stop trying to kill me. Normally people give quotes out of the price book which was done in Excel like 15 years ago and just has been updated over the years. the problem is excel is rounding differently than postgres 8.3.1 (Yes i know Excel rounds incorrectly) which results in normally being pennies off but on large qty its usually under a few bucks on the postgresql side. We internally don't care but those annoying customers scream bloody murder if the quote don't agree to the penny on the invoice Even when its to their benefit . Has anyone every got Postgresql and Excel to agree on rounding. I have checked excel up to Office XP and its still wrong. (open office was looked out and the people screamed really loudly NO ) Another annoying thing is the calculators on everyones desk get it wrong to if the rounding is turned to 2 places. Although my TI-89, and TI-36X calculators agree perfectly with postgresql . -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Lincoln Yeoh wrote: At 01:48 AM 5/13/2008, Justin wrote: I have very annoying problem that i would like to get a work around in place so the data entry people stop trying to kill me. Normally people give quotes out of the price book which was done in Excel like 15 years ago and just has been updated over the years. the problem is excel is rounding differently than postgres 8.3.1 (Yes i know Excel rounds incorrectly) which results in normally being pennies off but on large qty its usually under a few bucks on the postgresql side. We internally don't care but those annoying customers scream bloody murder if the quote don't agree to the penny on the invoice Even when its to their benefit . Has anyone every got Postgresql and Excel to agree on rounding. I have checked excel up to Office XP and its still wrong. (open office was looked out and the people screamed really loudly NO ) Another annoying thing is the calculators on everyones desk get it wrong to if the rounding is turned to 2 places. Although my TI-89, and TI-36X calculators agree perfectly with postgresql . Bad news, the Excel thing is probably doing math very wrong. Also, my guess is you're treating one penny as 0.01, which is also wrong. The fields are numeric(12,4) and numeric(10,2) . I'm in process of extending the precision out on the acounting side because its causing problems with inventory costing, as we have raw material priced in $50 to $100 a pound but only consume .000235 lbs per part. so we can getting some funky results. I did not layout the database. The person who laid out the database knows even less math than i do, we have numeric fields (20,10) to (10,4) and everything in between. it creates some funky results due to truncating and rounding in the different fields. You have raw material priced as high as thing are today it starts adding up to some major issues. Multiply that by thousands of transactions it just way wrong. I learned long ago make sure every field in the database have the same precision and deal with the rounding at the UI side. I learned this because of my work in low resistance measurements taken at the ppm scale. When you do financial calculations you should avoid floating point where possible. Floating point is really tricky to get right. There are scary books on it. I know this and experienced it before. Again someone did not know what they where doing and i got left picking up the pieces. Not to say my first time through i did not make all kind of mistakes but i fixed my. To add further murky the water for the users our last ERP packaged used round to next highest number which trashed cost accounting as it used more raw material than it should have. I'm no expert in financial calculations and floating point stuff, my _guess_ is a good start is probably treating one penny as 1, instead of 0.01. But better wait for the experts to chime in. That said, if you're going to insist on using the wrong numbers from the Excel Invoice, can't you work some way of getting them into Postgresql and stored as is, rather than having Postgresql calculate them differently ( I suspect you're using floating point in postgresql and so it'll be wrong too, just maybe a bit less wrong than Excel ;) ). No floating point is being used every variable is declared as numeric on the Postgresql side and in the C++ which is the UI side everything is double. Regards, Link. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1
Tom, Yea, about that lock that I *said* I didn't have... We had been joining through the pg_class and pg_tables tables to get some additional data and it turns out the row in the pg_locks that shows as locked doesn't have a relation so it was filtered out due to the join. So, now that I have accurate data (just doing a select * from pg_locks where granted = 'f') I indeed have an outstanding lock that isn't granted that is causing the holdup. However, now I have a couple of other questions. First, I thought I've seen in the past where Postgres will recognize a deadlock and will log an error and then move on. Do I need to do something with the more recent code base to get that behavior? I think the last time I saw that was back on an 8.1.X code base. Second, any words of wisdom to help run to ground who's keeping me from getting the lock on the offending row? The row in the lock table that shows granted false does not show as belonging to a database or relation (both null) so I can't join through to get the table info from pg_table. Thanks for all the help, -Craig On 5/12/08 12:16 PM, Tom Lane [EMAIL PROTECTED] wrote: Craig Vosburgh [EMAIL PROTECTED] writes: Got GDB installed on the errant node and did a back trace call (I'm guessing that is what you were looking for when you said stack trace) on the process that shows in the process table as executing the hung SQL command. The backtrace is: (gdb) bt #0 0x0088b7a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2 #1 0x0096f3ab in semop () from /lib/tls/libc.so.6 #2 0x081ba8f8 in PGSemaphoreLock () #3 0x081e4d9e in ProcSleep () #4 0x081e1db3 in GrantAwaitedLock () #5 0x0832f984 in twophase_recover_callbacks () #6 0x0006 in ?? () Hmm. gdb is lying to you, because GrantAwaitedLock doesn't call ProcSleep --- probably line 4 should refer to WaitOnLock instead. You could try installing a non-symbol-stripped postgres executable (or in RPM environments, install the matching debuginfo RPM) to get a more trustworthy backtrace. However, what I suspect you are looking at is just a run-of-the-mill lock wait. You *sure* there's no ungranted locks showing in pg_locks? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
thats how i loaded the price list to start with. The problems with sales orders are entered and the automatic pricing kicks in ( the discounts are calculated * the number or pieces ordered) it goes to down the tubes. I could just rewrite the pricing stored procedures to call a rounding procedure that would make the results agree with stupid excel :-\ Not the preferred way but it would make data entry people leave me alone. Thanks for the idea. Christophe wrote: Rather than try to recreate Excel's rounding algorithm, perhaps use Excel to create a table of input values and results, and load that into the database? It might be easier than trying to back-engineer Excel's broken math. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1
Craig Vosburgh [EMAIL PROTECTED] writes: Second, any words of wisdom to help run to ground who's keeping me from getting the lock on the offending row? The row in the lock table that shows granted false does not show as belonging to a database or relation (both null) so I can't join through to get the table info from pg_table. What's the locktype? If (as I suspect) it's a transaction or virtualtransaction lock, then which process holds that lock and what's it doing? For that matter, what is the stuck process doing? pg_stat_activity output should be sufficient here. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Returning RECORD from PGSQL without custom type?
Tom Lane wrote: D. Dante Lorenso [EMAIL PROTECTED] writes: I'd like to be able to do this: CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint) RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS You realize of course that you can do this *today* if you use OUT parameters? No, I didn't realize. I always assumed OUT parameters were like return values from a function ... like: (out1, out2, out3) = somefunction (in1, in2, in3); I never realized you could return a SETOF those OUT parameters. I guess it wasn't intuitive, but I'm learning this now. I think all the functionality I want DOES already exist. Let me go work with it. Thanks. -- Dante CREATE OR REPLACE FUNCTION my_custom_func (in_value bigint, OUT col1name BIGINT, OUT col2name TEXT, ...) RETURNS SETOF RECORD AS The TABLE syntax is a bit more standards-compliant maybe, but it's not offering any actual new functionality. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Andy Anderson wrote: Can you be more explicit about the rounding that's wrong in Excel? Are you talking about the n5 round-up to n+1 that Excel uses vs. n5 round-to-even n (sometimes called Banker's Rounding)? On May 12, 2008, at 2:38 PM, Justin wrote: Yes i'm taking about difference between bankers rounding verse Excels crappy math. I have dealt with excels crappy math skills in scientific measurements dumped from AD cards the simply solution was increase the decimal range to 1 more than i needed. But in this case it won't work sense this published material will disagree with how postgresql rounds. Well, I won't call it crappy, just different; it depends on your purpose. I learned round-even in grade school, but I've seen many college students in the last two decades who learned round-up. Microsoft actually explains these two and several other ways to implement rounding on this page: http://support.microsoft.com/kb/196652 (But they don't justify their choice for Excel, very odd given its extensive financial use.) Anyway, I would imagine you could implement a custom function to replace Postgres' round(n, i) along the lines of: function roundup(n, i) { factor = power(10.0, i); nd = n * factor; ni = trunc(nd); fraction = nd - ni; if (fraction = 0.5) return (ni + 1)/factor; if (fraction = -0.5) return (ni - 1)/factor; return ni/factor; } Apologies for using C and warnings that I haven't thoroughly tested this. P.S. You could also write a round-even function for Excel and get them to use it on their next printout! :-) -- Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] changing the endianness of a database
Hi, We'd like to ship PostgreSQL as part of a product that runs on both PPC and Intel Macs, but the database files are tied to the build settings and endianness of the computer that the database was initialized on. Is there any way to cause the server to modify the database files in-place for endianness issues? I know that a backup-then-restore process would fix the data, but on Macs, many users use Apple's computer migration tools to copy all their programs/data/users/etc.. to new Macs. If the user moves from a PPC to an Intel Mac, for instance, the database would be copied over, but the data would be for the old computer, and the database won't start. The backup/restore process is hard for end users, since they don't understand it -- and they won't contact us until after the migration is done, and often not until they've discarded the old computer. It would be nice if there was a way to recover the data from the existing database files. I found this old thread on a related topic, and it seems that this cannot be done... http://archives.postgresql.org/pgsql-general/2008-01/msg00635.php Thanks, Chris -- Chris Saldanha Parliant Corporation http://www.parliant.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] changing the endianness of a database
On Mon, May 12, 2008 at 4:02 PM, Chris Saldanha [EMAIL PROTECTED] wrote: We'd like to ship PostgreSQL as part of a product that runs on both PPC and Intel Macs, but the database files are tied to the build settings and endianness of the computer that the database was initialized on. Is there any way to cause the server to modify the database files in-place for endianness issues? I know that a backup-then-restore process would fix the data, but on Macs, many users use Apple's computer migration tools to copy all their programs/data/users/etc.. to new Macs. If the user moves from a PPC to an Intel Mac, for instance, the database would be copied over, but the data would be for the old computer, and the database won't start. The backup/restore process is hard for end users, since they don't understand it -- and they won't contact us until after the migration is done, and often not until they've discarded the old computer. It would be nice if there was a way to recover the data from the existing database files. I found this old thread on a related topic, and it seems that this cannot be done... http://archives.postgresql.org/pgsql-general/2008-01/msg00635.php Surely it's easier just to have your application dump on schedule and add some front end GUI import feature to your app? It looks like you are maybe trying to solve the wrong problem...namely that it is too difficult for your users to do backup/restore themselves. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Justin wrote: No floating point is being used every variable is declared as numeric on the Postgresql side and in the C++ which is the UI side everything is double. `double' in C++ refers to double precision floating point. `double' is subject to all the usual fun with rational decimals being irrational binary floats (and vice versa). One of the reasons I chose Java for my current work is that it has a built-in decimal type (like `numeric') called BigDecimal . This makes working with exact quantities a lot easier as there's no conversion and rounding occurring each time data goes to/from the database. Are there any particular decimal/numeric libraries people here like to use with C++ ? Or do you just use double precision floats and a good deal of caution? I'd expect that using double would be OK so long as the scale of your numeric values never approaches the floating point precision limit of the double type. I'm far from sure about that, though, and it'd be handy to hear from people who're doing it. Personally I like to stick to numeric/decimal types. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recovering database after disk crash
Hello A disk hosting an instance of 8.2 crashed on me (hardware failure). I was able to pull most of the data off the drive, but the one database that I need the most is corrupt. I'm not really sure where to start... so here are some error messages: [EMAIL PROTECTED]:/var/log/postgresql$ pg_dump EPC pg_dump: SQL command failed pg_dump: Error message from server: ERROR: index pg_opclass_am_name_nsp_index contains unexpected zero page at block 0 HINT: Please REINDEX it. pg_dump: The command was: SELECT tableoid, oid, *, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = '10') as lanowner FROM pg_language WHERE lanispl ORDER BY oid [EMAIL PROTECTED]:/var/log/postgresql$ reindexdb EPC NOTICE: table pg_class was reindexed reindexdb: reindexing of database EPC failed: ERROR: catalog is missing 4 attribute(s) for relid 10762 Almost every operation I attempt ends in a similar error message: EPC=# \dt ERROR: index pg_opclass_am_name_nsp_index contains unexpected zero page at block 0 HINT: Please REINDEX it. EPC=# select * from siemr_sys.zip_codes; ERROR: catalog is missing 10 attribute(s) for relid 1337158 EPC=# select * from siemr_sys.sex_list; ERROR: catalog is missing 2 attribute(s) for relid 1337150 EPC=# select * from siemr_data.visits; ERROR: catalog is missing 7 attribute(s) for relid 1337095 Is there some way I can extract the data from the tables even if the metadata is corrupt? Thanks Vic -- Men never do evil so completely and cheerfully as when they do it from religious conviction. -Blaise Pascal -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] changing the endianness of a database
Merlin Moncure wrote: Surely it's easier just to have your application dump on schedule and add some front end GUI import feature to your app? It looks like you are maybe trying to solve the wrong problem...namely that it is too difficult for your users to do backup/restore themselves. Maybe it's an opportunity to introduce the users to backups. Honestly, though, PostgreSQL doesn't seem to be designed for application bundling and embedding, where the user never knows there's a database engine present. I'm under the impression that there's no consideration of what happens if you move from 32 to 64 bit hosts, big endian to little endian, etc; it's expected that you'll dump and reload. The ability to build a custom standalone backend that could read (and only read) a foreign database structure would be pretty handy in this sort of situation and other cases of poorly planned disaster recovery or migration. Of course, it's much better to avoid such situations, but where end users are involved they're always going to arise. It's a pity the system cloning/migration tools don't have hooks for applications to do pre-migration and post-migration tasks, so you could just dump then initdb and reload. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Craig Ringer wrote: Justin wrote: No floating point is being used every variable is declared as numeric on the Postgresql side and in the C++ which is the UI side everything is double. `double' in C++ refers to double precision floating point. `double' is subject to all the usual fun with rational decimals being irrational binary floats (and vice versa). One of the reasons I chose Java for my current work is that it has a built-in decimal type (like `numeric') called BigDecimal . This makes working with exact quantities a lot easier as there's no conversion and rounding occurring each time data goes to/from the database. Not according to MS specific if i'm reading it correctly *Microsoft Specific * The double type contains 64 bits: 1 for sign, 11 for the exponent, and 52 for the mantissa. Its range is +/--1.7E308 with at least 15 digits of precision Are there any particular decimal/numeric libraries people here like to use with C++ ? Or do you just use double precision floats and a good deal of caution? I'd expect that using double would be OK so long as the scale of your numeric values never approaches the floating point precision limit of the double type. I'm far from sure about that, though, and it'd be handy to hear from people who're doing it. Personally I like to stick to numeric/decimal types. -- Craig Ringer
Re: [GENERAL] rounding problems
As i'm playing around with rounding and the numeric field precision ran into a odd set of results i don't understand here is the sql i wrote the first four inserts are calculations we run everyday and they make sense but if division is used the results are not right or am i missing something create table test_num ( num1 numeric(20,1), num2 numeric(20,2), num3 numeric(20,3), num4 numeric(20,4), num5 numeric(20,5), num6 numeric(20,6), num7 numeric(20,7), num8 numeric(20,8), num9 numeric(20,9)); delete from test_num; insert into test_num values ( (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05)); insert into test_num values ( (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05)); insert into test_num values( (.5/.03), (.5/.3), (.5/3), (.5/30), (.5/300), (.5/3000), (.5/3), (.5/3), (.5/3)); insert into test_num values( (.5/.03)*.9975, (.5/.3)*.9975, (.5/3)*.9975, (.5/30)*.9975, (.5/300)*.9975, (.5/3000)*.9975, (.5/3)*.9975, (.5/3)*.9975, (.5/3)*.9975); insert into test_num values( (9*.1), (9*.01), (9*.001), (9*.0001), (9*.1), (9*.01), (9*.001), (9*.0001), (9*.1)); insert into test_num values ( (9/10), (9/100), (9/1000), (9/1), (9/10), (9/100), (9/1000), (9/1), (9/10)); insert into test_num values( (1*.1), (1*.01), (1*.001), (1*.0001), (1*.1), (1*.01), (1*.001), (1*.0001), (1*.1)); insert into test_num values ( (1/10), (1/100), (1/1000), (1/1), (1/10), (1/100), (1/1000), (1/1), (1/10)); select * from test_num ; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] changing the endianness of a database
Maybe it's an opportunity to introduce the users to backups. Yes, we do backups for the user, but the problem with Apple's migration is that it happens not on a schedule that meshes with the backup schedule. Our applications have fairly frequently changing data. Honestly, though, PostgreSQL doesn't seem to be designed for application bundling and embedding, where the user never knows there's a database engine present. I'm under the impression that there's no consideration of what happens if you move from 32 to 64 bit hosts, big endian to little endian, etc; it's expected that you'll dump and reload. Agreed that PGSQL isn't designed for embedding, but it's actually very close to being supportable in that kind of use model. The binaries and database files are nicely contained, the server/libraries can be easily built as a Universal (i.e. multi-architecture) binary for Macs, and the server is actually quite small (26MB for a complete install as PPC/Intel binaries not stripped) compared to commercial databases. If the data files themselves were portable or convertible, then it would be an almost perfect solution. It's a pity the system cloning/migration tools don't have hooks for applications to do pre-migration and post-migration tasks, so you could just dump then initdb and reload. Yes, that's exactly the problem. For the migration, you actually shut down the old Mac that's the source of the data and boot it in a special FireWire disk mode, and connect it like a hard disk to the new Mac. As a result, there's no code able to run on the source computer during the migration. For our kind of users (non-technical often), it's almost impossible to have them plan out stuff or even consider what needs to be done in terms of advance tasks. I had hoped that there would be a way to rescue the database, even if it took a lot of processing... Chris -- Chris Saldanha Parliant Corporation http://www.parliant.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Making sure \timing is on
On Sun, May 11, 2008 at 11:48:29PM -0400, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: Is it reasonable behavior to have \timing along toggle and \timing on / \timing off be a forced switch? Just thinking of other scripts where this isn't a problem and having to update them. The command without an argument should certainly keep the old toggle behavior, for backwards compatibility. Attached patch does some of the right thing, but doesn't yet handle error cases. How liberal should we be about capitalization, spelling, etc.? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate Index: src/bin/psql/command.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v retrieving revision 1.188 diff -r1.188 command.c 887c887,897 pset.timing = !pset.timing; --- char *opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); if (opt) { if (strcmp(opt, on) == 0) pset.timing = true; else if (strcmp(opt, off) == 0) pset.timing = false; } else pset.timing = !pset.timing; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovering database after disk crash
After doing some more reading I've come to the conclusion that I'm in completely over my head. I got a fresh copy of the corrupt data and am starting from the very beginning. Here's the error I get on startup: [EMAIL PROTECTED]:/usr/lib/postgresql/8.2/bin$ ./postgres -D /var/lib/postgresql/8.2/main 2008-05-12 16:16:02 CDT LOG: could not load root certificate file root.crt: no SSL error reported 2008-05-12 16:16:02 CDT DETAIL: Will not verify client certificates. 2008-05-12 16:16:02 CDT LOG: database system was interrupted at 2008-04-16 09:28:36 CDT 2008-05-12 16:16:02 CDT LOG: unexpected pageaddr 0/1114A000 in log file 0, segment 17, offset 14295040 2008-05-12 16:16:02 CDT LOG: invalid primary checkpoint record 2008-05-12 16:16:02 CDT LOG: unexpected pageaddr 0/1114A000 in log file 0, segment 17, offset 14295040 2008-05-12 16:16:02 CDT LOG: invalid secondary checkpoint record 2008-05-12 16:16:02 CDT PANIC: could not locate a valid checkpoint record 2008-05-12 16:16:02 CDT LOG: startup process (PID 8359) was terminated by signal 6 2008-05-12 16:16:02 CDT LOG: aborting startup due to startup process failure pg_control output: ./pg_controldata /var/lib/postgresql/8.2/main pg_control version number:822 Catalog version number: 200611241 Database system identifier: 5126483664462806975 Database cluster state: in production pg_control last modified: Wed 16 Apr 2008 09:28:36 AM CDT Current log file ID: 0 Next log file segment:18 Latest checkpoint location: 0/11DA3C38 Prior checkpoint location:0/11DA3BF0 Latest checkpoint's REDO location:0/11DA3C38 Latest checkpoint's UNDO location:0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/908105 Latest checkpoint's NextOID: 2001259 Latest checkpoint's NextMultiXactId: 7 Latest checkpoint's NextMultiOffset: 13 Time of latest checkpoint:Wed 16 Apr 2008 09:13:56 AM CDT Minimum recovery ending location: 0/0 Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Date/time type storage: 64-bit integers Maximum length of locale name:128 LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8 I ran pg_resetxlog in my previous mucking around, but that's when all hell broke loose, so I'm holding off until someone more qualified advises me to do so. TIA Vic -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
I tried casting them to numeric and it was still wrong OK i just added decimal point after the 9 and 1 it work at that point. Thats an odd result i would not have expected it to do that. This prompts another question how does postgres figure out the data types passed in an SQL string??? Andy Anderson wrote: I would guess the issue is that 9/10 is an integer calculation, with result 0. Use instead 9./10 or 9/10. or 9./10. with result 0.9. -- Andy On May 12, 2008, at 5:09 PM, Justin wrote: As i'm playing around with rounding and the numeric field precision ran into a odd set of results i don't understand here is the sql i wrote the first four inserts are calculations we run everyday and they make sense but if division is used the results are not right or am i missing something create table test_num ( num1 numeric(20,1), num2 numeric(20,2), num3 numeric(20,3), num4 numeric(20,4), num5 numeric(20,5), num6 numeric(20,6), num7 numeric(20,7), num8 numeric(20,8), num9 numeric(20,9)); delete from test_num; insert into test_num values ( (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05), (0.70 *1.05)); insert into test_num values ( (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05), (0.709 *1.05)); insert into test_num values( (.5/.03), (.5/.3), (.5/3), (.5/30), (.5/300), (.5/3000), (.5/3), (.5/3), (.5/3)); insert into test_num values( (.5/.03)*.9975, (.5/.3)*.9975, (.5/3)*.9975, (.5/30)*.9975, (.5/300)*.9975, (.5/3000)*.9975, (.5/3)*.9975, (.5/3)*.9975, (.5/3)*.9975); insert into test_num values( (9*.1), (9*.01), (9*.001), (9*.0001), (9*.1), (9*.01), (9*.001), (9*.0001), (9*.1)); insert into test_num values ( (9/10), (9/100), (9/1000), (9/1), (9/10), (9/100), (9/1000), (9/1), (9/10)); insert into test_num values( (1*.1), (1*.01), (1*.001), (1*.0001), (1*.1), (1*.01), (1*.001), (1*.0001), (1*.1)); insert into test_num values ( (1/10), (1/100), (1/1000), (1/1), (1/10), (1/100), (1/1000), (1/1), (1/10)); select * from test_num ; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Justin wrote: Craig Ringer wrote: `double' in C++ refers to double precision floating point. `double' is subject to all the usual fun with rational decimals being irrational binary floats (and vice versa). Not according to MS specific if i'm reading it correctly *Microsoft Specific * The double type contains 64 bits: 1 for sign, 11 for the exponent, and 52 for the mantissa. Its range is +/--1.7E308 with at least 15 digits of precision I take it you're referring to: http://msdn.microsoft.com/en-us/library/e02ya398(VS.80).aspx ? See how it says The format is similar to the float format ? As you can see from: http://msdn.microsoft.com/en-us/library/hd7199ke(VS.80).aspx the `double' type is a binary floating point representation, just like float. It just has a bigger exponent and a bigger mantissa, so it can represent more extreme values and do so with more precision. Being a binary floating point representation it's subject to all the usual problems with comparison for equality, rounding oddities, etc. Here's one of the many explanations out there on the 'net. I haven't read this particular one, it's just a viable looking Google hit: http://www.cprogramming.com/tutorial/floating_point/understanding_floating_point.html By the way, there was at least a proposal for a numeric/decimal type for C++0x . It doesn't seem to have made the cut. http://209.85.173.104/search?q=cache:D0Iqhgz7X1QJ:www.open-std.org/jtc1/sc22/wg21/docs/papers/2006/n2041.pdf+%22c%2B%2B0x%22+decimal+OR+numerichl=enct=clnkcd=1gl=auclient=firefox-a http://en.wikipedia.org/wiki/C%2B%2B0x http://www.open-std.org/jtc1/sc22/wg21/docs/papers/ It looks like ISO C might adopt a decimal type or library though: http://www2.hursley.ibm.com/decimal/ Note in particular the support in gcc 4.2 or newer. There's also a library: http://www2.hursley.ibm.com/decimal/dfpal/ that might be useful. -- Craig Ringe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Making sure \timing is on
David Fetter wrote: On Sun, May 11, 2008 at 11:48:29PM -0400, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: Is it reasonable behavior to have \timing along toggle and \timing on / \timing off be a forced switch? Just thinking of other scripts where this isn't a problem and having to update them. The command without an argument should certainly keep the old toggle behavior, for backwards compatibility. Attached patch does some of the right thing, but doesn't yet handle error cases. How liberal should we be about capitalization, spelling, etc.? Please try ParseVariableBool() in psql/variables.c, and use diff -c. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Making sure \timing is on
David Fetter escribió: On Sun, May 11, 2008 at 11:48:29PM -0400, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: Is it reasonable behavior to have \timing along toggle and \timing on / \timing off be a forced switch? Just thinking of other scripts where this isn't a problem and having to update them. The command without an argument should certainly keep the old toggle behavior, for backwards compatibility. Attached patch does some of the right thing, but doesn't yet handle error cases. How liberal should we be about capitalization, spelling, Hmm, there's already code for parsing boolean variables in psql, see ParseVariableBool. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Justin wrote: I tried casting them to numeric and it was still wrong How do the results differ from what you expect? You've posted a bunch of code, but haven't explained what you think is wrong with the results. Can you post a couple of SMALL examples and explain how the results are different from what you expect them to be? Try the example using the following formats for the literals in your test: 2.0 '2.0'::numeric (this is a BCD decimal) '2.0'::float4 (this is a C++/IEEE float) '2.0'::float8 (this is a C++/IEEE double) and see how the results differ. -- Craig Riniger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Making sure \timing is on
On Mon, May 12, 2008 at 05:30:48PM -0400, Bruce Momjian wrote: David Fetter wrote: On Sun, May 11, 2008 at 11:48:29PM -0400, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: Is it reasonable behavior to have \timing along toggle and \timing on / \timing off be a forced switch? Just thinking of other scripts where this isn't a problem and having to update them. The command without an argument should certainly keep the old toggle behavior, for backwards compatibility. Attached patch does some of the right thing, but doesn't yet handle error cases. How liberal should we be about capitalization, spelling, etc.? Please try ParseVariableBool() in psql/variables.c, and use diff -c. Thanks for the heads-up :) Second patch attached, this time with some docs. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate Index: doc/src/sgml/ref/psql-ref.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.203 diff -c -r1.203 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 8 May 2008 17:04:26 - 1.203 --- doc/src/sgml/ref/psql-ref.sgml 12 May 2008 21:56:59 - *** *** 1867,1876 varlistentry !termliteral\timing/literal/term listitem para ! Toggles a display of how long each SQL statement takes, in milliseconds. /para /listitem /varlistentry --- 1867,1879 varlistentry !termliteral\timing /literal [replaceable ! class=parameterON/replaceable | replaceable ! class=parameterOFF/replaceable] /term listitem para ! Without parameter, toggles a display of how long each SQL ! statement takes, in milliseconds. With parameter, sets same. /para /listitem /varlistentry Index: src/bin/psql/command.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v retrieving revision 1.188 diff -c -r1.188 command.c *** src/bin/psql/command.c 8 May 2008 17:04:26 - 1.188 --- src/bin/psql/command.c 12 May 2008 21:57:01 - *** *** 884,890 /* \timing -- toggle timing of queries */ else if (strcmp(cmd, timing) == 0) { ! pset.timing = !pset.timing; if (!pset.quiet) { if (pset.timing) --- 884,895 /* \timing -- toggle timing of queries */ else if (strcmp(cmd, timing) == 0) { ! char *value = psql_scan_slash_option(scan_state, ! OT_NORMAL, NULL, false); ! if (value) ! pset.timing = ParseVariableBool(value); ! else ! pset.timing = !pset.timing; if (!pset.quiet) { if (pset.timing) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] changing the endianness of a database
On May 12, 2008, at 4:02 PM, Chris Saldanha wrote: Hi, We'd like to ship PostgreSQL as part of a product that runs on both PPC and Intel Macs, but the database files are tied to the build settings and endianness of the computer that the database was initialized on. Is there any way to cause the server to modify the database files in- place for endianness issues? I know that a backup-then-restore process would fix the data, but on Macs, many users use Apple's computer migration tools to copy all their programs/data/users/etc.. to new Macs. If the user moves from a PPC to an Intel Mac, for instance, the database would be copied over, but the data would be for the old computer, and the database won't start. The backup/restore process is hard for end users, since they don't understand it -- and they won't contact us until after the migration is done, and often not until they've discarded the old computer. It would be nice if there was a way to recover the data from the existing database files. I found this old thread on a related topic, and it seems that this cannot be done... http://archives.postgresql.org/pgsql-general/2008-01/msg00635.php You know that you don't have to compile postgresql as Universal, right? If you have separate PPC and Intel versions (not lipo'd together), then, presumably, you should be able to figure out which one needs to run. The PPC postgresql would then run on the Macintel under Rosetta and you would then have control to proceed with an automatic dump/restore. However, this would not work for someone moving the database from an Intel machine to a PPC machine. Postgresql is simply not well-suited for such uncontrolled environments. What happens when you upgrade postgresql? Do you then ship with 4 version of the db (Intel/PPC * 8.2/83)? Perhaps you should dump all the non-transient data whenever the application is shut down (in anticipation of an upgrade)? Cheers, M -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1
What's the locktype? Yep, locktype is transaction. If (as I suspect) it's a transaction or virtualtransaction lock, then which process holds that lock and what's it doing? As for which process owns that lock, I'm not sure how to find that out (sorry newbie). I can find the PID that is waiting for that lock and I can find the table/row that appears to be waiting for the lock to perform the action but I can't figure out which process actually owns the lock that is causing the issue. For that matter, what is the stuck process doing? pg_stat_activity output should be sufficient here. regards, tom lane I've enabled stats tracking and the process that is hung has the following data in the pg_stat_activity table datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port ---+-+-+--+--+-- -+-+ ---+---+ ---+-+- 16384 | collage | 20938 | 10 | postgres | update ips set address=$1, usage=$2, subnet_rips_id=$3, hostname=$4, errored=$5, errorReason=$6, modinfo=$7, name=$8, description=$9 where bmo_id=$10 | t | 2008-05-11 17:25:04.484224-06 | 2008-05-11 17:25:04.528319-06 | 2008-05-11 17:02:00.016083-06 | 127.0.0.1 | 49056 Everything else in the table is either IDLE or IDLE in transaction. Thanks again, -Craig On 5/12/08 1:11 PM, Tom Lane [EMAIL PROTECTED] wrote: -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
I guess i have not been very clear. lets take this select (9/10), (9/10)::numeric, (9::numeric/10::numeric), (9./10), (9*.1) With the given select statement i expected the results all to be same, especially sense it cast 4 of the 5 to numeric either with explicit cast or by containing a decimal. Instead postgresql cast the first 2 calculations to integer, it then uses integer math so the result is 0. To Add further conversion to my small brain there is a specific type cast to the second calculation but it still returned 0. Not what i would have expected. After thinking about it for say 10 seconds, i see that Postgresql is following the order of operation in the 2nd calculation where it does integer math then cast the results to numeric. I made the incorrect assumption Postgresql would have casted all the arguments to numeric then done the math. After thinking this through for a short bit i see why postgresql is casting the arguments to integer type as numeric/floating point math can be a pretty heavy hit performance wise. So this prompts the question how does postgresql decide what types to cast arguments to. It seems thus far if a decimal is found in the argument its numeric and everything else is assumed to be integer if it does not contain a decimal point. Craig Ringer wrote: Justin wrote: I tried casting them to numeric and it was still wrong How do the results differ from what you expect? You've posted a bunch of code, but haven't explained what you think is wrong with the results. Can you post a couple of SMALL examples and explain how the results are different from what you expect them to be? Try the example using the following formats for the literals in your test: 2.0 '2.0'::numeric (this is a BCD decimal) '2.0'::float4 (this is a C++/IEEE float) '2.0'::float8 (this is a C++/IEEE double) and see how the results differ. -- Craig Riniger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Yet another option, of course, is to simply not do any calculations in PostgreSQL, and accept the results from Excel as definitive... which seems to be what is desired, anyway. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hung SQL Update Linux Redhat 4U5 Postgres 8.3.1
Craig Vosburgh [EMAIL PROTECTED] writes: As for which process owns that lock, I'm not sure how to find that out (sorry newbie). I can find the PID that is waiting for that lock and I can find the table/row that appears to be waiting for the lock to perform the action but I can't figure out which process actually owns the lock that is causing the issue. It's whichever one actually holds that same lock according to pg_locks (ie, there's an entry that matches on locktype and transactionid but has granted = 't'; then join the pid of that entry against pg_stat_activity). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Server not listening
If this isn't the right place to post this, please advise. I've spent a week trying to get PostgreSQL 8.3 to install correctly on WIN2K. Server will load I see the server processes loaded but none of them have any open ports. I keep getting the message the server isn't listening. Server set up to connect to default port 5432 on localhost but doesn't appear to be opening the port when it loads. Any help would be welcome. Thanks, Dennis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
On May 12, 2008, at 6:37 PM, Justin wrote: lets take this select (9/10), (9/10)::numeric, (9::numeric/10::numeric), (9./10), (9*.1) With the given select statement i expected the results all to be same, especially sense it cast 4 of the 5 to numeric either with explicit cast or by containing a decimal. Instead postgresql cast the first 2 calculations to integer, it then uses integer math so the result is 0. Putting a decimal on a string of digits is the standard way to specify that it's numeric rather than integer; see 4.1.2.4. Numeric Constants: http://www.postgresql.org/docs/8.3/interactive/sql-syntax- lexical.html#AEN1276 In other words, 9. is equivalent to 9::numeric, though the latter involves an operation on an integer. If a calculation contains a numeric value, any integers involved will be cast to a numeric value first, and then the calculation will proceed numerically. 9/10 = 0 (a purely integer calculation, division truncates the fractional part) (9/10)::numeric = 0::numeric = 0. (using parentheses forces the integer calculation to occur *before* the cast) 9::numeric/10::numeric = 9./10. = 0.9 (using one or two casts forces a numeric calculation) 9./10 = 9./10. = 0.9(specifying a numeric value forces the integer to be cast to numeric) To Add further conversion to my small brain there is a specific type cast to the second calculation but it still returned 0. Not what i would have expected. After thinking about it for say 10 seconds, i see that Postgresql is following the order of operation in the 2nd calculation where it does integer math then cast the results to numeric. I made the incorrect assumption Postgresql would have casted all the arguments to numeric then done the math. Not when you change the order of evaluation by using parentheses. See the precedence table in 4.1.6. Lexical Precedence: http://www.postgresql.org/docs/8.3/interactive/sql-syntax- lexical.html#SQL-PRECEDENCE After thinking this through for a short bit i see why postgresql is casting the arguments to integer type as numeric/floating point math can be a pretty heavy hit performance wise. So this prompts the question how does postgresql decide what types to cast arguments to. It starts with operator precedence to determine the order of operation, and then for each operator it decides how it will cast arguments for the best results. -- Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovering database after disk crash
Vic Simkus [EMAIL PROTECTED] writes: [EMAIL PROTECTED]:/var/log/postgresql$ reindexdb EPC NOTICE: table pg_class was reindexed reindexdb: reindexing of database EPC failed: ERROR: catalog is missing 4 attribute(s) for relid 10762 If you're really lucky, doing the reindex with ignore_system_indexes enabled will go through. Otherwise, it's time to go back to your last backups :-( regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] changing the endianness of a database
A.M. wrote: You know that you don't have to compile postgresql as Universal, right? If you have separate PPC and Intel versions (not lipo'd together), then, presumably, you should be able to figure out which one needs to run. The PPC postgresql would then run on the Macintel under Rosetta and you would then have control to proceed with an automatic dump/restore. However, this would not work for someone moving the database from an Intel machine to a PPC machine. That would be my suggestion - run a ppc version to dump then restore with an intel version. Maybe a startup script can detect when to do this. Maybe this is an argument against making universal postgres binaries. Postgresql is simply not well-suited for such uncontrolled environments. What happens when you upgrade postgresql? Do you then ship with 4 version of the db (Intel/PPC * 8.2/83)? Perhaps you should dump all the non-transient data whenever the application is shut down (in anticipation of an upgrade)? As far as upgrades that could/should be handled in the installer script. Dump from the installed version then install the new one and restore. That is - using Apple's installer setup. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovering database after disk crash
If I'm understanding the errors correctly it seems that the corruption is in the system catalogs (metadata). The database does not use any fancy datatypes. Is there any way for me to rebuild the metadata manually? If I can see the leftover metadata and the data minus the missing metadata I can probably piece everything together enough to get the data out. Does that make sense or am I off my rocker? On 5/12/08, Tom Lane [EMAIL PROTECTED] wrote: Vic Simkus [EMAIL PROTECTED] writes: [EMAIL PROTECTED]:/var/log/postgresql$ reindexdb EPC NOTICE: table pg_class was reindexed reindexdb: reindexing of database EPC failed: ERROR: catalog is missing 4 attribute(s) for relid 10762 If you're really lucky, doing the reindex with ignore_system_indexes enabled will go through. Otherwise, it's time to go back to your last backups :-( regards, tom lane -- Men never do evil so completely and cheerfully as when they do it from religious conviction. -Blaise Pascal -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovering database after disk crash
Vic Simkus [EMAIL PROTECTED] writes: If I'm understanding the errors correctly it seems that the corruption is in the system catalogs (metadata). The database does not use any fancy datatypes. Is there any way for me to rebuild the metadata manually? If I can see the leftover metadata and the data minus the missing metadata I can probably piece everything together enough to get the data out. Based on the evidence so far, the disk failure has zeroed out multiple, randomly-chosen pages of your system catalogs. I'd think it very likely indeed that random pages of your table files got the same favor. You won't have any way to know what is missing ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovering database after disk crash
During the initial ill-educated messing around I had set the zero_damaged_pages to yes, but I'm guessing that the end result is the same... Ill try it with the fresh copy of [the corrupt] data What kind of a database can't deal with a bit of random values injected into its sytem files anyways? :) On 5/12/08, Tom Lane [EMAIL PROTECTED] wrote: Vic Simkus [EMAIL PROTECTED] writes: If I'm understanding the errors correctly it seems that the corruption is in the system catalogs (metadata). The database does not use any fancy datatypes. Is there any way for me to rebuild the metadata manually? If I can see the leftover metadata and the data minus the missing metadata I can probably piece everything together enough to get the data out. Based on the evidence so far, the disk failure has zeroed out multiple, randomly-chosen pages of your system catalogs. I'd think it very likely indeed that random pages of your table files got the same favor. You won't have any way to know what is missing ... regards, tom lane -- Men never do evil so completely and cheerfully as when they do it from religious conviction. -Blaise Pascal -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
On Mon, May 12, 2008 at 06:37:02PM -0400, Justin wrote: I guess i have not been very clear. lets take this select (9/10), (9/10)::numeric, (9::numeric/10::numeric), (9./10), (9*.1) With the given select statement i expected the results all to be same, especially sense it cast 4 of the 5 to numeric either with explicit cast or by containing a decimal. Instead postgresql cast the first 2 calculations to integer, it then uses integer math so the result is 0. To Add further conversion to my small brain there is a specific type cast to the second calculation but it still returned 0. Not what i would have expected. After thinking about it for say 10 seconds, i see that Postgresql is following the order of operation in the 2nd calculation where it does integer math then cast the results to numeric. PG does very similar things to what C does. '9' is an integer literal, and so is '10', there is a '/' operator that takes two integers and returns an integer and this gets used, resulting in an integer. If you happen to cast the thing to a value of numeric type this will happen after the division (i.e. it follows the syntax, like C does). Casting the integers to values of numeric type is similar, just the numeric version of the division operator gets used. The last example exercises a different code path, in that '9.' is a value of numeric type and '10' is still of integer type. There's some magic somewhere in PG that says that values of numeric type are more expressive than values of integer type causing the parser (I'm guessing here) to insert a cast to numeric type. The types now unify and one value can be divided by the other. The magic seems somewhat arbitrary; what if I wanted to go to the less precise type or generally be told when things didn't unify. I made the incorrect assumption Postgresql would have casted all the arguments to numeric then done the math. After thinking this through for a short bit i see why postgresql is casting the arguments to integer type as numeric/floating point math can be a pretty heavy hit performance wise. I don't think it's accurate to say the behaviour is there because of performance reasons, it's just evaluating your code as you've written it. The behaviour you describe is closer to an untyped (i.e. dynamically checked, or as they seem to be popularly known weakly typed) scripting language. Either that or something like Haskell which treats types much more rigorously than PG, where the expression (9.0 / (10::Int)) would fail to type check, and 9.0/10 (or even 9/10) would do what you wanted and parse 10 as any value that implements the fractional type class (probably a floating point number). The easiest way to understand what's going on is generally playing with a single expression, then changing the literals to represent values of different types and seeing how the result changes. You may get some mileage out of using EXPLAIN VERBOSE (you can see the cast being inserted in the 9./10 case, when compared to 9/10---function OID 1740 takes an int4 and returns a numeric) but it's somewhat difficult to read. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to create a function with multiple RefCursor OUT parameters
The following is a function from PosgreSQL documentation to return multiple cursors from a single function: CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$ BEGIN OPEN $1 FOR SELECT * FROM table_1; RETURN NEXT $1; OPEN $2 FOR SELECT * FROM table_2; RETURN NEXT $2; END; $$ LANGUAGE plpgsql; -- need to be in a transaction to use cursors. BEGIN; SELECT * FROM myfunc('a', 'b'); FETCH ALL FROM a; FETCH ALL FROM b; COMMIT; What I want to achieve is to modify the function to take an INOUT parameter. For example: myfunc(INOUT tcount integer, refcursor, refcursor). I want to add logic to my INOUT parameter inside the function and return it back to client, as well as returning the two refcursor results. How to modify this function and how to test it in SQL to achieve my goal? Merlin Moncure wrote: On Sun, May 11, 2008 at 2:43 PM, Chuck Bai [EMAIL PROTECTED] wrote: CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT o_user refcursor, OUT o_name refcursor) RETURNS record AS $BODY$ BEGIN tcount := tcount + 1; OPEN o_user FOR SELECT * FROM user_table; OPEN o_name FOR SELECT * FROM name_table; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE Question 1: The function is not working with Npgsql .NET data provider. It did not return a valid .NET DataSet. But the INOUT parameter tcount works fine. How could I test the above function with SQL in pgAdmin III? I want to find out if problem is in the function or in the Npgsql. You can test from pgAdmin by simply running queries in the query window. This sort of thing however might be a better fit for psql (pasting your queries in the query window). You need to use transactions since refcursors only only good inside a transaction. Question 2: pgAdmin III automatically added RETURNS record in the above function when RETURNS clause is not specified initially. Why is that? Is this the problem since it returns only single data table with the following value? How to fix it? For a function with 1 out parameters, the output type is a record. Your function returns (int, refcursor, refcursor) as defined. SELECT * FROM test_refcursor(7); would returns a row with three variables ( a record). tcount o_user o_name 23 unnamed portal 1 unnamed portal 2 You probably want to name your refcursors. The way to do this is simply o_user := 'something'; inside your pl/pgsql function. -- inside pl/pgsql_function refcur_variable := 'mycursor' -- outside function, but in same transaction FETCH ALL FROM mycursor -- or, mycursor So, it would at least take a few 'queries' from the perppective of the client to do what you are attempting. However, all the data is 'set up' for return to the client by the server in the main function. The server will hang on to it as long as the current transaction is valid and then release it. Question 3: I want to return a single DataSet with each OUT RefCursor map to a DataTable within the DataSet, plus extra OUT parameters for individual OUT values. How could I create such a function? Your question is a little opaque to me. A refcursor is in PostgreSQL terms a 'hande' to a set, not a DataTable the way you are thinking...it's really a fancy string. so, (INOUT int, OUT refcursor, OUT refcursor) returns takes an 'int' in and returns an int and two refcursors (strings), with extra work to return this to the client, at least in terms of SQL statements. I haven't used .net for a while but IIRC it's probably not possible to 'fill' multiple data tables in a single query without at least some manual work. Some of the npgsql experts might have some suggestions however. It really depends on how the code operates inside the npgsql library. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
thats what i'm trying to get a grasp on, what postgres is doing with calculation as it truncates or rounds the number when committing the records to the physical table. I just start digging into this as we are having problems where some fields in the database are precision of 2 and other go all the way to 10 decimal places. The table layout we have is not consistent and the result are hundred to thousandths of pennies off but those pennies start become dollars every 100 to 1000 transactions. It seems the pg rounding is favoring the lower side of the number when being committed to the table. I've been going over transactions in WIP and compared to values in the Generial Ledger i'm off 6 cents and thats only on 36 transactions that i have handcheck.GL has a lower value compared to the records in WIP tables which have 4 and 6 decimals precision versues GL 2 decimal precision in the tables I going through the tables and making all the numeric fields all the same. I have run into problems as some of columns are referenced by views and other constraints and its not letting me change them. :'( WE have several columns in table defined with numeric (20,10) thats is just insanity. Unless your doing scientific calculations which we do, do. Having that many decimal points for an accounting package is just nonsense and then its rounded to 4 or 6 in Inventory and Wip tables then 2 when the numbers finally hit the GL tables.Who ever laid these tables out has never had to try and get numbers to balance and agree across tables :-( . Every time i dig a little deeper i keep finding stupid things like this. Some people may think i'm crazy trying to track this down but when you're only consume 0.003186 lbs of a metal per part that cost 22.7868 per lb and the work order calls fro 1148 parts. how the machine rounds becomes a big problem (.00318611*1148) = 3.65765 lbs consumed * 22.7868 = $83.3462 . Sense GL tables are 2 decimal the value is stored as $83.35 But the problem is far worse than that. BOM allows for greater precision of 8 wip Inventory Movements shows only 6, Wip tables has 6 and 4. The question quickly becomes what number is the correct number. Wip truncates the material consumed to .003186*1148 = 3.6575 * 22.7868 = 83.3434 which is rounded = 83.34 Multiply this by 1000 transactions a day and we start having major problems. Sam Mason wrote: On Mon, May 12, 2008 at 06:37:02PM -0400, Justin wrote: I guess i have not been very clear. lets take this select (9/10), (9/10)::numeric, (9::numeric/10::numeric), (9./10), (9*.1) With the given select statement i expected the results all to be same, especially sense it cast 4 of the 5 to numeric either with explicit cast or by containing a decimal. Instead postgresql cast the first 2 calculations to integer, it then uses integer math so the result is 0. To Add further conversion to my small brain there is a specific type cast to the second calculation but it still returned 0. Not what i would have expected. After thinking about it for say 10 seconds, i see that Postgresql is following the order of operation in the 2nd calculation where it does integer math then cast the results to numeric. PG does very similar things to what C does. '9' is an integer literal, and so is '10', there is a '/' operator that takes two integers and returns an integer and this gets used, resulting in an integer. If you happen to cast the thing to a value of numeric type this will happen after the division (i.e. it follows the syntax, like C does). Casting the integers to values of numeric type is similar, just the numeric version of the division operator gets used. The last example exercises a different code path, in that '9.' is a value of numeric type and '10' is still of integer type. There's some magic somewhere in PG that says that values of numeric type are more expressive than values of integer type causing the parser (I'm guessing here) to insert a cast to numeric type. The types now unify and one value can be divided by the other. The magic seems somewhat arbitrary; what if I wanted to go to the less precise type or generally be told when things didn't unify. I made the incorrect assumption Postgresql would have casted all the arguments to numeric then done the math. After thinking this through for a short bit i see why postgresql is casting the arguments to integer type as numeric/floating point math can be a pretty heavy hit performance wise. I don't think it's accurate to say the behaviour is there because of performance reasons, it's just evaluating your code as you've written it. The behaviour you describe is closer to an untyped (i.e. dynamically checked, or as they seem to be popularly known weakly typed) scripting language. Either that or something like Haskell which treats types much more rigorously than PG, where the expression (9.0 / (10::Int)) would fail
Re: [GENERAL] rounding problems
Justin wrote: WE have several columns in table defined with numeric (20,10) thats is just insanity. Not necessarily. I have a few places where a monetary value is mulitiplied by a ratio quantity. For some of the historical data imported from another system the ratio can be irrational or at least not representable in any small precision value. I ended up needing a precision of 8 numeric digits to acceptably represent these ratios, resulting in a numeric(16,8) type to permit ratio values up to . . I probably could've got away with numeric(13,8) or even numeric(12,8) but as space and performance aren't utterly critical it didn't seem to be worth the risk of hitting limits and overflows later. As it is I'm tempted to go to 10 digits of precision, as there's still a 3 cent difference between the totals from the old system and the same data imported into the new system. You'll encounter similar situations in your materials consumption tracking (as you detailed below) and other places. So don't discount the use of high precision numeric values just yet. Personally I'd be tempted to use a `double precision' (float8) for things like materials consumption. Materials consumed in huge quantities will have lower price rates, and materials consumed in tiny quantities will often be priced higher. With wally-numbers: You're not going to care about the 0.0003 kg of steel consumed at a price of $0.1 , but the same amount of something valuable might have a detectable (if still sub-cent) value. Floating point numbers are IMO better for that than BCD numeric. However, since the float will just get converted to numeric during multiplication with a numeric price-per-mass ratio it may well not be worth worrying about it. There's a use for that numeric(20,10). Unless your doing scientific calculations which we do, do. Having that many decimal points for an accounting package is just nonsense and then its rounded to 4 or 6 in Inventory and Wip tables then 2 when the numbers finally hit the GL tables. Who ever laid these tables out has never had to try and get numbers to balance and agree across tables :-( . Every time i dig a little deeper i keep finding stupid things like this. It sounds like you might have quite a bit of compounded rounding error from the successive stages of rounding as data moves through the system. Maybe you're rounding too aggressively? I like to store a bit more precision than I have to, unless there's a business rule that requires rounding to a particular precision. For example, if your invoice items are rounded to whole cents you'd probably round the calculated invoice item price when inserting into an invoice item table. Of course, that means that sum(calculation of invoice item price) sum(rounded price of invoice items) because of rounding. That's fine; you can't balance the two things exactly because they're actually subtly different things. If you're using an appropriate rounding method for financial data, like round-to-even, you'll only ever get a couple of cents difference and that should be expected and ignored. Some people may think i'm crazy trying to track this down but when you're only consume 0.003186 lbs of a metal per part that cost 22.7868 per lb and the work order calls fro 1148 parts. how the machine rounds becomes a big problem (.00318611*1148) = 3.65765 lbs consumed * 22.7868 = $83.3462 . Sense GL tables are 2 decimal the value is stored as $83.35 Thinking about correct rounding and precision is very important, and far from crazy. The question quickly becomes what number is the correct number. Sometimes the answer is both of them - even though they are different. See the example above with rounded invoice items. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rounding problems
Craig Ringer wrote: Justin wrote: WE have several columns in table defined with numeric (20,10) thats is just insanity. Not necessarily. I have a few places where a monetary value is mulitiplied by a ratio quantity. For some of the historical data imported from another system the ratio can be irrational or at least not representable in any small precision value. I ended up needing a precision of 8 numeric digits to acceptably represent these ratios, resulting in a numeric(16,8) type to permit ratio values up to . . I probably could've got away with numeric(13,8) or even numeric(12,8) but as space and performance aren't utterly critical it didn't seem to be worth the risk of hitting limits and overflows later. As it is I'm tempted to go to 10 digits of precision, as there's still a 3 cent difference between the totals from the old system and the same data imported into the new system. That 3 cent difference is over how many transactions ??? The differences i'm seeing are getting into the hundreds of dollars in 1 quarter within this stupid application. The person/persons who laid this database out do not or did not understand the compound rounding errors. I'm just trying to figure out how best to fix it. You'll encounter similar situations in your materials consumption tracking (as you detailed below) and other places. So don't discount the use of high precision numeric values just yet. Personally I'd be tempted to use a `double precision' (float8) for things like materials consumption. Materials consumed in huge quantities will have lower price rates, and materials consumed in tiny quantities will often be priced higher. With wally-numbers: You're not going to care about the 0.0003 kg of steel consumed at a price of $0.1 , but the same amount of something valuable might have a detectable (if still sub-cent) value. Floating point numbers are IMO better for that than BCD numeric. However, since the float will just get converted to numeric during multiplication with a numeric price-per-mass ratio it may well not be worth worrying about it. There's a use for that numeric(20,10). I'm moving all the numeric fields to numeric(20,8) . I feel its pretty safe with that scale setting. I agree data storage and performance aren't critical concerns as they once were Unless your doing scientific calculations which we do, do. Having that many decimal points for an accounting package is just nonsense and then its rounded to 4 or 6 in Inventory and Wip tables then 2 when the numbers finally hit the GL tables. Who ever laid these tables out has never had to try and get numbers to balance and agree across tables :-( . Every time i dig a little deeper i keep finding stupid things like this. It sounds like you might have quite a bit of compounded rounding error from the successive stages of rounding as data moves through the system. Maybe you're rounding too aggressively? Thats the problem the database layout is crap. I like to store a bit more precision than I have to, unless there's a business rule that requires rounding to a particular precision. For example, if your invoice items are rounded to whole cents you'd probably round the calculated invoice item price when inserting into an invoice item table. Of course, that means that sum(calculation of invoice item price) sum(rounded price of invoice items) because of rounding. That's fine; you can't balance the two things exactly because they're actually subtly different things. If you're using an appropriate rounding method for financial data, like round-to-even, you'll only ever get a couple of cents difference and that should be expected and ignored. I normally would but given all the tables are showing different values when summed over a Accounting period its adding up to significant differences between all the tables. Some people may think i'm crazy trying to track this down but when you're only consume 0.003186 lbs of a metal per part that cost 22.7868 per lb and the work order calls fro 1148 parts. how the machine rounds becomes a big problem (.00318611*1148) = 3.65765 lbs consumed * 22.7868 = $83.3462 . Sense GL tables are 2 decimal the value is stored as $83.35 Thinking about correct rounding and precision is very important, and far from crazy. The question quickly becomes what number is the correct number. Sometimes the answer is both of them - even though they are different. See the example above with rounded invoice items. -- Craig Ringer
Re: [GENERAL] Server not listening
On Mon, May 12, 2008 at 4:53 PM, D Galen [EMAIL PROTECTED] wrote: If this isn't the right place to post this, please advise. I've spent a week trying to get PostgreSQL 8.3 to install correctly on WIN2K. Server will load I see the server processes loaded but none of them have any open ports. I keep getting the message the server isn't listening. Server set up to connect to default port 5432 on localhost but doesn't appear to be opening the port when it loads. Any help would be welcome. Thanks, Dennis Well, easy ones first, I guess: what does postgresql.conf look like, specifically listen_addresses and port; what are their values and are they commented out? - Josh / eggyknap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general