Re: [GENERAL] Executing Dynamic DDL
That was very helpful, many thanks About timestamp, I understood that as long as the DB server is the one to log times it is safe to use it without timezones?? (I am not using any of the different timezoned clients??) am i missing the point? about all_table_columns, it is just a simple view to have the table name and column name in the same view, (wasn't satisfied in pg_attribute only :), am i duplicating anything? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] How can I write trigger on a columns insert/update?
How can I write trigger on a columns insert/update? CREATE TRIGGER mytrigger BEFORE INSERT OR UPDATE OF mycolumn ON mytable FOR EACH ROW EXECUTE PROCEDURE myfunction(); I am getting error syntax error at or near OF Thanks
[GENERAL] error explanation
I deleted all the rows, approximately 3 million, in a table earlier in the day, so during the nightly routine vacuum, I received the following: dbname=# vacuum verbose analyze table; INFO: vacuuming public.table INFO: index table_attempt_idx now contains 3895682 row versions in 103829 pages DETAIL: 0 index row versions were removed. 766 index pages have been deleted, 766 are currently reusable. CPU 8.97s/1.13u sec elapsed 592.94 sec. INFO: index table_search_idx now contains 3383634 row versions in 19422 pages DETAIL: 0 index row versions were removed. 3105 index pages have been deleted, 3105 are currently reusable. CPU 0.61s/1.05u sec elapsed 33.01 sec. INFO: table: removed 1397790 row versions in 34649 pages DETAIL: CPU 2.44s/1.60u sec elapsed 32.39 sec. INFO: index table_attempt_idx now contains 3147492 row versions in 103830 pages DETAIL: 748304 index row versions were removed. 2906 index pages have been deleted, 766 are currently reusable. CPU 12.00s/6.42u sec elapsed 599.79 sec. INFO: index table_search_idx now contains 2723871 row versions in 19422 pages DETAIL: 659880 index row versions were removed. 6138 index pages have been deleted, 3102 are currently reusable. CPU 0.85s/1.86u sec elapsed 33.22 sec. INFO: table: removed 1397782 row versions in 34513 pages DETAIL: CPU 2.67s/1.30u sec elapsed 31.22 sec. INFO: index table_attempt_idx now contains 2405128 row versions in 103830 pages DETAIL: 742508 index row versions were removed. 5721 index pages have been deleted, 765 are currently reusable. CPU 11.65s/6.36u sec elapsed 603.68 sec. INFO: index table_search_idx now contains 2089828 row versions in 19422 pages DETAIL: 634181 index row versions were removed. 9067 index pages have been deleted, 3101 are currently reusable. CPU 0.79s/1.68u sec elapsed 34.13 sec. INFO: table: removed 1397765 row versions in 34588 pages DETAIL: CPU 2.49s/1.47u sec elapsed 31.33 sec. INFO: index table_attempt_idx now contains 1690401 row versions in 103830 pages DETAIL: 714856 index row versions were removed. 9965 index pages have been deleted, 764 are currently reusable. CPU 11.54s/6.26u sec elapsed 590.81 sec. INFO: index table_search_idx now contains 1474467 row versions in 19422 pages DETAIL: 615463 index row versions were removed. 11971 index pages have been deleted, 3101 are currently reusable. CPU 0.87s/1.60u sec elapsed 49.51 sec. INFO: table: removed 1397795 row versions in 34603 pages DETAIL: CPU 2.39s/1.23u sec elapsed 23.45 sec. INFO: index table_attempt_idx now contains 954707 row versions in 103830 pages DETAIL: 735785 index row versions were removed. 19001 index pages have been deleted, 764 are currently reusable. CPU 12.05s/6.34u sec elapsed 602.31 sec. INFO: index table_search_idx now contains 836578 row versions in 19422 pages DETAIL: 637964 index row versions were removed. 14946 index pages have been deleted, 3101 are currently reusable. CPU 0.76s/1.38u sec elapsed 34.35 sec. INFO: table: removed 1397798 row versions in 34600 pages DETAIL: CPU 2.72s/1.46u sec elapsed 80.80 sec. PANIC: right sibling is not next child server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ! My logs displayed the following: May 19 04:09:28 db1 /kernel: pid 66449 (postgres), uid 70: exited on signal 6 (core dumped) May 19 04:09:28 db1 postgres[81721]: [5-1] LOG: server process (PID 66449) was terminated by signal 6 May 19 04:09:28 db1 postgres[81721]: [6-1] LOG: terminating any other active server processes May 19 04:09:28 db1 postgres[77806]: [5-1] WARNING: terminating connection because of crash of another server process May 19 04:09:28 db1 postgres[77806]: [5-2] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server May 19 04:09:28 db1 postgres[77806]: [5-3] process exited abnormally and possibly corrupted shared memory. May 19 04:09:28 db1 postgres[77806]: [5-4] HINT: In a moment you should be able to reconnect to the database and repeat your command. May 19 04:09:28 db1 postgres[78345]: [7-1] FATAL: the database system is in recovery mode May 19 04:09:28 db1 postgres[81721]: [7-1] LOG: all server processes terminated; reinitializing May 19 04:09:28 db1 postgres[78346]: [8-1] LOG: database system was interrupted at 2005-05-19 04:09:22 EDT May 19 04:09:28 db1 postgres[78346]: [9-1] LOG: checkpoint record is at 16C/247F5468 May 19 04:09:28 db1 postgres[78346]: [10-1] LOG: redo record is at 16C/1FA441B4; undo record is at 0/0; shutdown FALSE May 19 04:09:28 db1 postgres[78346]: [11-1] LOG: next transaction ID: 410726590; next OID: 143676535 May 19 04:09:28 db1 postgres[78346]: [12-1] LOG: database system was not properly shut down; automatic recovery in progress May 19 04:09:28 db1 postgres[78346]: [13-1] LOG: redo starts at
Re: [GENERAL] How can I write trigger on a columns insert/update?
Dinesh Pandey wrote: How can I write trigger on a columns insert/update? CREATE TRIGGER mytrigger BEFORE INSERT OR UPDATE OF mycolumn ON mytable You don't I'm afraid. It's not a feature that's supported yet. You have to check which column(s) were changed in your functions. It is on the TODO list though: http://www.postgresql.org/docs/faqs.TODO.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] CREATE TABLE problem in plpgsql trigger
James Croft wrote: The problems seems to be with the table_name arg being a variable and not a literal but can't see how to fix this. If anyone knows what's going on here or has any pointers it would be appreciated. Thanks, James Sorry, I'm running PgSQL 7.4.7 on RedHat Enterprise Linux 3. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] numeric precision when raising one numeric to another.
On Wed, May 18, 2005 at 11:32:40PM -0400, Alvaro Herrera wrote: On Wed, May 18, 2005 at 10:46:50PM -0400, John Burger wrote: For one thing. For another, I believe the standard C library only has floating point exponentiation functions, not that there aren't plenty of numeric libraries with integral ones. Finally, exponentiated numbers get real big, real fast, and the floating point types can hold much larger magnitudes than the integer types, albeit inexactly. For example, on the Mac I'm using now, long long ints max out at about 10^19, while long doubles can represent 10^308. Well, we already have an interesting library of mathematical functions for NUMERIC (which is an arbitrary precision type, so it wouldn't matter how big the result would get). I think the only reason we don't have a NUMERIC exponentiation function is that nobody has implemented it. The prerequisites for such a function would be a log() and exp() function for numeric. And the real question there would be, what's a sufficient accuracy? Numbers people actually use rarely have even rational logarithms, so there is no way to store them 100% accurate. As long as you're using integral exponents you can get away with multiplication. BTW, the commandline utility bc has arbitrary number arithmatic, maybe we can see how they do it? It defaults to 20 digits precision, which is obviously not enough for large exponents. Hmm, it looks like even they don't support raising to fractional powers. When calculating 2^100, you need a precision of at least 35 decimal places to get in the ballpark of the correct figure using log/exp, 30 isn't enough. Maybe do exact for integer exponents and approx for non-integer? [EMAIL PROTECTED]:~$ bc -l bc 1.06 Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc. 2^100 1267650600228229401496703205376 2^100.1 Runtime warning (func=(main), adr=11): non-zero scale in exponent 1267650600228229401496703205376 e(l(2)*100) 1267650600228229400579922894637.90158245154400629512 scale=30 e(l(2)*100) 1267650600228229401496703205353.61733731135194699059124092 scale=35 e(l(2)*100) 1267650600228229401496703205375.99897630874075350752485091801369515 Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpbjMR5RDaJl.pgp Description: PGP signature
[GENERAL] analyze at startup?
Title: analyze at startup? Analyze command helps increase performance. Suppose i have n connections to the database..and each connection i query. So does this mean that i need to do an analyse over each of those connections. Cant analyze be done one time... and also please tell if there is any way in postgresql to tell it to perform analyze periodically after some time gap. Thank You Surabhi
Re: [GENERAL] analyze at startup?
Surabhi Ahuja wrote: Analyze command helps increase performance. More exactly, it updates statistics so the planner knows that (e.g.) you have lots of different values in the invoice_date column. Suppose i have n connections to the database..and each connection i query. So does this mean that i need to do an analyse over each of those connections. No. You only need to analyse if your data changes greatly. Cant analyze be done one time... and also please tell if there is any way in postgresql to tell it to perform analyze periodically after some time gap. Haven't used the autovacuum add-on myself, but probably worth looking at it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] analyze at startup?
See here for more details: http://www.postgresql.org/docs/8.0/static/maintenance.html In short, you don't need to do a vacuum analyze for each connection--it is a database-specific or table-specific task, not a connection-specific one. You can use vacuum analyze when you think things have changed enough in one or all tables to justify its use. What justifies enough change is a bit of an open question, although I think a rough number is if a table changes by about 15%, it then needs a vacuum analyze. In practice, you can just use cron (see man cron on a unix-like system) to set up a vacuum for nightly, weekly, or something like that. Sean On May 19, 2005, at 6:20 AM, Surabhi Ahuja wrote: Analyze command helps increase performance. Suppose i have n connections to the database..and each connection i query. So does this mean that i need to do an analyse over each of those connections. Cant analyze be done one time... and also please tell if there is any way in postgresql to tell it to perform analyze periodically after some time gap. Thank You Surabhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
On May 19, 2005, at 12:21 PM, Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: On May 19, 2005, at 10:27 AM, Matthew Hixson wrote: 8.0.3 does not build for me on Mac OS X 10.4. Anyone else seeing this error, and will it be fixed in the next release? I just built 8.0.3 on Mac OS X 10.4.1 just fine. IIRC this isn't the first report of build problems on 10.4, and not the first report of success either. Somebody needs to look into exactly what's going on. (Don't look at me, I have not bought 10.4 yet...) For the record, my machine is a clean install of 10.4.0 + Xcode, then upgraded to 10.4.1. I installed Bison 1.875 (as previously mentioned), but otherwise it's a stock system. No fink, no Darwinports, nothing but PostgreSQL. laughter:~ glaesema$ which gcc /usr/bin/gcc laughter:~ glaesema$ gcc -v Reading specs from /usr/lib/gcc/powerpc-apple-darwin8/4.0.0/specs Configured with: /private/var/tmp/gcc/gcc-4061.obj~8/src/configure -- disable-checking --prefix=/usr --mandir=/share/man --enable- languages=c,objc,c++,obj-c++ --program-transform-name=/^[cg][^+.-]*$/ s/$/-4.0/ --with-gxx-include-dir=/include/gcc/darwin/4.0/c++ -- build=powerpc-apple-darwin8 --host=powerpc-apple-darwin8 -- target=powerpc-apple-darwin8 Thread model: posix gcc version 4.0.0 20041026 (Apple Computer, Inc. build 4061) Possibly there's a issue with upgrading from 10.3.x to 10.4? Maybe using a different gcc? I'm not very experienced reading installation messages, so I'm not really sure what error Matthew's getting. I've attached what I think is the corresponding section from my installation. I've saved the whole terminal session, so if anyone needs anything else, just let me know. Michael Glaesemann grzm myrealbox com - gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wdeclaration-after-statement -Wold-style-definition -Wendif-labels - fno-strict-aliasing -DFRONTEND -I. -I../../../src/include -I/usr/ include/ -I../../../src/port -c -o thread.o thread.c ar crs libpq.a `lorder fe-auth.o fe-connect.o fe-exec.o fe-misc.o fe- print.o fe-lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o fe-secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o | tsort` tsort: cycle in data tsort: fe-exec.o tsort: fe-protocol2.o tsort: fe-connect.o tsort: fe-protocol3.o tsort: cycle in data tsort: fe-exec.o tsort: fe-protocol2.o tsort: cycle in data tsort: fe-exec.o tsort: fe-misc.o tsort: cycle in data tsort: fe-auth.o tsort: fe-connect.o ranlib libpq.a gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wdeclaration-after-statement -Wold-style-definition -Wendif-labels - fno-strict-aliasing -dynamiclib -install_name /usr/local/pgsql/ pgsql-8.0.3/lib/libpq.4.dylib -compatibility_version 4 - current_version 4.0 -multiply_defined suppress fe-auth.o fe- connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o fe-protocol2.o fe- protocol3.o pqexpbuffer.o pqsignal.o fe-secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o -L../../../src/port -L/ usr/lib/ -lresolv -o libpq.4.0.dylib rm -f libpq.4.dylib ln -s libpq.4.0.dylib libpq.4.dylib rm -f libpq.dylib ln -s libpq.4.0.dylib libpq.dylib make -C include all make[4]: Nothing to be done for `all'. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Call dynamic PL/PGSQL function
Hi, what's the best way in pl/pgsql to call a function with a dynamic name from inside another pl/plsql function? like.. SELECT INTO function_name name FROM functions WHERE bar; EXECUTE function_name..?? Thanks in advance ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
On May 19, 2005, at 1:34 AM, Matthew Hixson wrote: /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) After upgrading to 10.4.1 and using Michael's parameters to ./configure I still get the exact same build error. Any Postgres hackers out there interested in logging into my machine and seeing what they can do to fix this? [Wild stabs in the dark to follow] What kind of Mac is it? The error had something to do with cputype. When I installed XCode 2.0 yesterday I noticed it installs gcc 3 and 4. Maybe you could try using gcc 3 and see if it makes a difference. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Call dynamic PL/PGSQL function
Hannes Dorbath wrote: Hi, what's the best way in pl/pgsql to call a function with a dynamic name from inside another pl/plsql function? like.. SELECT INTO function_name name FROM functions WHERE bar; EXECUTE function_name..?? Almost my_query = ''SELECT '' || function_name ''()''; EXECUTE my_query; Also check out the quote_ functions in case you have mixed-case function names etc. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Ayuda con postgresql
Hola! buenos días! Estoy usando su base de datos y quisiera saber como creo un campo que sea auntonumérico o lo que en sql server es identiti, también quisiera como pudiera hacer una copia de toda mi base de datos (restore) para copiarla igual en otra computadora. Saludos Maribel Correo Yahoo!Comprueba qué es nuevo, aquíhttp://correo.yahoo.es
[GENERAL] Callable Statements are not supported at this time
Hi, I am getting an exception when I try to use JDBC CallableStatement for executing a stored procedure. Exception - Callable Statements not supported at this time. Postgres version - 7.3.2 OS - Linux It will be very useful for me if you could help me in cracking this. Regards, RamaMohan.T Yahoo! India Matrimony: Find your life partner online Go to: http://yahoo.shaadi.com/india-matrimony ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] need clarification
i need a clarification in the pypgsql... iam executing the statement like result=connection.query(Update emp set empname=Don where empid=15) what will be result value if the empid 15 doesn't exists. vel Important Email Information :- The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. If you are not the intended addressee please contact the sender and dispose of this e-mail immediately. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Postgres in government
Hello, Sorry for the cross post. I am working on a requirements and recommendation document for a division of the State of Hawaii Attorney General's office. We are proposing that Postgres be used for the application database. Not too surprisingly we are being asked for additional information because Postgres is open source. We all know that Postgres is good and given the requirements and scale of this project (fairly small) it is a very good fit. I have looked at the case studies listed on postgresql.org and searched the mailing list archives. I have also scrounged the Internet looking for examples of Postgres being used in government, preferably in 24x7 capacities. Overall, not a lot of examples out there, which seems strange because I know it is being used by various government agencies. Its the communities of users and developers that make open source software so powerful, I decided that the best course of action was to leverage the community. So, if you have used Postgres (or know that it has been used) for a government project, especially in a 24x7 environment, I would greatly appreciate hearing about it. Ideally, I need more than just the project name. Specifically, A brief description of the project, number of users/transactions as day/week/month, etc, whatever details you have and can share. Please CC me as I'm not subscribed to the list. And since I'm leaving on vacation in 3 days, please CC my coworkers at [EMAIL PROTECTED] Thanks in advance Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] CREATE TABLE problem in plpgsql trigger
Hi all, I'm trying to create a trigger function for a few tables that will store old versions of rows prior to any update on them. Part of the function needs to creates other tables (the table to store these snapshots in). When this trigger runs I get the and error of 'syntax error at or near $1 at character 15' which is the CREATE TABLE line. snip DECLARE rec RECORD; snapshottable TEXT; originaltable TEXT; BEGIN SELECT INTO rec count(*) AS num FROM pg_tables WHERE schemaname = ''table_snapshots'' AND tablename = TG_RELNAME; IF rec.num 1 THEN snapshottable := ''table_snapshots.'' || TG_RELNAME; originaltable := TG_RELNAME; CREATE TABLE snapshottable (LIKE originaltable); ALTER TABLE snapshottable ADD COLUMN snapshottime date; ALTER TABLE snapshottable ALTER COLUMN snapshottime SET DEFAULT CURRENT_TIMESTAMP; END IF; /snip The problems seems to be with the table_name arg being a variable and not a literal but can't see how to fix this. If anyone knows what's going on here or has any pointers it would be appreciated. Thanks, James ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Count and Results together
I am using libpg.so. I tryed find solution for this problem in internet but, I don't find nothing yet. I have idea get rowcount throught some function write in C. Or is there any plan add this feature into PostgreSQL? JOhn On Wed, 18 May 2005 17:37:09 +0200, Sean Davis [EMAIL PROTECTED] wrote: On May 18, 2005, at 3:07 AM, Jan Sunavec wrote: Hi Is possible use SELECT NUM_ROWS, a, b, c FROM blabla WHERE x =id; I mean it's much more faster than SELECT a, b, c FROM blabla WHERE x =id; and then SELECT count(*) FROM blabla WHERE x =id; Can you do this on the client side? What client/interface are you using? There is a nice archive of the postgres mailing lists here: http://archives.postgresql.org/ A quick search for ranks (not exactly what you want, but I think it has a useable solution in one of the posts) gets: http://archives.postgresql.org/pgsql-general/2005-05/msg00157.php ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] CREATE TABLE problem in plpgsql trigger
On Thu, 19 May 2005, James Croft wrote: Hi all, I'm trying to create a trigger function for a few tables that will store old versions of rows prior to any update on them. Part of the function needs to creates other tables (the table to store these snapshots in). When this trigger runs I get the and error of 'syntax error at or near $1 at character 15' which is the CREATE TABLE line. Yes, I don't think support statements like CREATE TABLE currently work with variables directly. You probably can use EXECUTE however by generating a string containing the command you want to run first. Something like: EXECUTE ''CREATE TABLE '' || snapshottable || '' (LIKE '' || originaltable || '')''; excepting that you'd need to be more careful with quoting. snip DECLARE rec RECORD; snapshottable TEXT; originaltable TEXT; BEGIN SELECT INTO rec count(*) AS num FROM pg_tables WHERE schemaname = ''table_snapshots'' AND tablename = TG_RELNAME; IF rec.num 1 THEN snapshottable := ''table_snapshots.'' || TG_RELNAME; originaltable := TG_RELNAME; CREATE TABLE snapshottable (LIKE originaltable); ALTER TABLE snapshottable ADD COLUMN snapshottime date; ALTER TABLE snapshottable ALTER COLUMN snapshottime SET DEFAULT CURRENT_TIMESTAMP; END IF; /snip The problems seems to be with the table_name arg being a variable and not a literal but can't see how to fix this. If anyone knows what's going on here or has any pointers it would be appreciated. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Ayuda con postgresql
Si necesitás una lista en español, podés ir a http://archives.postgresql.org/pgsql-es-ayuda y suscribirte, los suscriptores de esta lista hablan inglés y no te va a resultar fácil conseguir ayuda :( El tipo de datos que buscas se llama SERIAL (http://www.postgresql.org/docs/8.0/interactive/datatype.html#DATATYPE-SERIAL) La utilidad para generar un vuelco de la base de datos es pg_dump (http://www.postgresql.org/docs/8.0/interactive/backup.html#BACKUP-DUMP) y el proceso inverso lo podes realizar directamente con el psql como se explica en http://www.postgresql.org/docs/8.0/interactive/backup.html#BACKUP-DUMP-RESTORE. La documentacion de la base de datos está en http://www.postgresql.org/docs/8.0/interactive/index.html. Espero te sirva. El día 19/05/05, Maribel Pérez Engroñatt [EMAIL PROTECTED] escribió: Hola! buenos días! Estoy usando su base de datos y quisiera saber como creo un campo que sea auntonumérico o lo que en sql server es identiti, también quisiera como pudiera hacer una copia de toda mi base de datos (restore) para copiarla igual en otra computadora. Saludos Maribel Correo Yahoo!Comprueba qué es nuevo, aquíhttp://correo.yahoo.es
[GENERAL] Preserving data after updates
A few months ago, a question by Scott Frankel produced a suggestion from
Greg Patnude which I found very exciting that had to do with using pg
table inheritance to maintain an audit or row change history table. I've
been testing Patnude's idea and ran into a problem, described below, and
wanted to ask about work-around suggestions.
Testing had so far entailed occasionally dumping the production data
base, restoring to DEV, and then modifying DEV to include several
history tables, using a script similar to that which I documented on
the PG web site. So today, I tried for the first time dumping DEV after
making the history table additions and then testing the restore from the
dump script so produced. The restore failed.
The problem is that one of my parent tables has table constraints:
CREATE TABLE person
(
person_pk int4 NOT NULL DEFAULT nextval('person_person_pk_seq'::text),
last_name varchar(24),
first_name varchar(24),
middle_name varchar(24),
e_mail_address name,
social_security_no varchar(11),
CONSTRAINT person_e_mail_address CHECK
(check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::character varying)),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'::character
varying))
)
WITHOUT OIDS;
I create the history table with
CREATE TABLE person_change_history(
action VARCHAR(6),
update_date TIMESTAMP NOT NULL DEFAULT NOW(),
update_user NAME NOT NULL DEFAULT CURRENT_USER
) INHERITS (person) WITHOUT OIDS;
CREATE RULE person_ru AS ON UPDATE TO person
DO INSERT INTO person_change_history
SELECT *, 'UPDATE' FROM ONLY person WHERE person_pk = old.person_pk;
CREATE RULE person_rd AS ON DELETE TO person
DO INSERT INTO person_change_history
SELECT *, 'DELETE' FROM ONLY person WHERE person_pk = old.person_pk;
But after doing a dump of the modified data base, the script created by
pg dump wants to recreate the history table as
CREATE TABLE person_change_history
(
person_pk int4 NOT NULL DEFAULT nextval('person_person_pk_seq'::text),
last_name varchar(24),
first_name varchar(24),
middle_name varchar(24),
e_mail_address name,
social_security_no varchar(11),
action varchar(6),
update_date timestamp NOT NULL DEFAULT now(),
update_user name NOT NULL DEFAULT current_user(),
CONSTRAINT person_e_mail_address CHECK
(check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::character varying)),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'::character
varying))
) INHERITS (person)
WITHOUT OIDS;
When I run the script to restore the dumped, modified, data base, psql
raises an error when creating the history table because the table
constraints already exist
psql:paid-5434.sql:7678: ERROR: constraint person_e_mail_address
already exists for relation person_change_history
Any suggestion on how to get around this problem?
I don't want to have to manually modified the pg_dump output script so
as to delete the constraint definitions from the history table
definition, because that sort of manual intervention really gets in the
way of good administrative procedures for disaster recovery if this
scheme were to be implemented in the production data base.
-- BMT
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] Postgresql 7.4.7 docs(PDF)
Hi All, If someone can email Postgresql 7.4.7 docs(PDF). It will be a big help. I am unable to get pdf docs for this version on debian system. Please help. Thanks, Hrishi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Shared memory and FreeBSD's jail()
At the BSDCan tutorial last week on jails (and several other times) there was discussion regarding Postgres's use of system V style shared memory, and an unfortunate side effect of making jail() less secure. Specifically, to allow Postgres to operate in a jail()ed environment, the sysctl : jail.sysvipc_allowed=1 has to be set. This allows ALL jails to access the memory, at the least leaving Postgres open to attack, at the worst allowing a door into who knows what security breach. Question : is there any way to run Postgres securely in a jail? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] unique index with bool
CREATE UNIQUE INDEX name on table(param1,param2); How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ? I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true')); but it's not working. Regards, Mac ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] TSearch2 Phonemes
Hi, I hope someone can help me out... I read some research a little while ago about someone using TSearch2 but searching using phonemes; but I cannot find that article anymore. Does anyone remember it can point me to a link of the article? Thanks in advance. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] error explanation
Omachonu Ogali [EMAIL PROTECTED] writes: PANIC: right sibling is not next child Apparently you have a corrupted index --- REINDEX should fix it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Postgresql 7.4.7 docs(PDF)
Hrishikesh Deshmukh wrote: Hi All, If someone can email Postgresql 7.4.7 docs(PDF). It will be a big help. I am unable to get pdf docs for this version on debian system. Please help. Available from here: http://www.postgresql.org/docs/manuals/ See the links on the right-hand-side of the page. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] unique index with bool
[EMAIL PROTECTED] wrote: CREATE UNIQUE INDEX name on table(param1,param2); How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ? I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true')); but it's not working. Something like: CREATE UNIQUE INDEX my_uniq_idx ON table(param1,param2) ALTER TABLE table ADD CONSTRAINT my_constraint CHECK (param2='' OR param1=true); So - separate out the test linking param1/param2 from your uniqueness requirement. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Count and Results together
On Thu, May 19, 2005 at 08:58:13AM +0200, Jan Sunavec wrote: I am using libpg.so. I tryed find solution for this problem in internet but, I don't find nothing yet. I have idea get rowcount throught some function write in C. Or is there any plan add this feature into PostgreSQL? You're missing the point. When you do a query in PostgreSQL you know how many rows were returned. The interface tells you. For example in Perl it's $sth-rows for DBI or $res-ntuples for Pg, in pl/PgSQL it's GET DIAGNOSTICS, in C it's PQntuples(). The rowcount is metadata and is passed seperately from the data. The interface you're using should tell you how many rows there are... Perhaps you should tell us what language/interface you are using so we can give a complete answer. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpUeaGxCWwTJ.pgp Description: PGP signature
Re: [GENERAL] Shared memory and FreeBSD's jail()
lister [EMAIL PROTECTED] writes: At the BSDCan tutorial last week on jails (and several other times) there was discussion regarding Postgres's use of system V style shared memory, and an unfortunate side effect of making jail() less secure. Specifically, to allow Postgres to operate in a jail()ed environment, the sysctl : jail.sysvipc_allowed=1 has to be set. This allows ALL jails to access the memory, at the least leaving Postgres open to attack, at the worst allowing a door into who knows what security breach. Question : is there any way to run Postgres securely in a jail? By your definition, not unless you remove the dependence on SysV shmem, which would be a lot of work. -Doug ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] unique index with bool
[EMAIL PROTECTED] wrote: CREATE UNIQUE INDEX name on table(param1,param2); How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ? I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true')); but it's not working. CREATE UNIQUE INDEX name ON table(param1, param2) WHERE param2 = true; Regards, -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgres in government
On Wed, 2005-05-18 at 21:24, Mark Steckel wrote: Hello, Sorry for the cross post. I am working on a requirements and recommendation document for a division of the State of Hawaii Attorney General's office. We are proposing that Postgres be used for the application database. Not too surprisingly we are being asked for additional information because Postgres is open source. We all know that Postgres is good and given the requirements and scale of this project (fairly small) it is a very good fit. I have looked at the case studies listed on postgresql.org and searched the mailing list archives. I have also scrounged the Internet looking for examples of Postgres being used in government, preferably in 24x7 capacities. Overall, not a lot of examples out there, which seems strange because I know it is being used by various government agencies. Its the communities of users and developers that make open source software so powerful, I decided that the best course of action was to leverage the community. So, if you have used Postgres (or know that it has been used) for a government project, especially in a 24x7 environment, I would greatly appreciate hearing about it. Ideally, I need more than just the project name. Specifically, A brief description of the project, number of users/transactions as day/week/month, etc, whatever details you have and can share. There are probably thousands of 24/7 operations running postgresql. None of them have to tell anyone, so it's no surprise you haven't heard about them. One of the more interesting projects running on postgresql is the .org domain. Do a search for postgresql and afilias and .org or whatnot. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Shared memory and FreeBSD's jail()
On Thu, 2005-05-19 at 09:46, lister wrote: At the BSDCan tutorial last week on jails (and several other times) there was discussion regarding Postgres's use of system V style shared memory, and an unfortunate side effect of making jail() less secure. Specifically, to allow Postgres to operate in a jail()ed environment, the sysctl : jail.sysvipc_allowed=1 has to be set. This allows ALL jails to access the memory, at the least leaving Postgres open to attack, at the worst allowing a door into who knows what security breach. Question : is there any way to run Postgres securely in a jail? I'm note sure that this is an actual security issue. Assuming that the processes running each jail are running under a different UID, they shouldn't be anymore able to access each other's shared memory than they would be able to share each others files. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] unique index with bool
On Thu, 2005-05-19 at 09:49, [EMAIL PROTECTED] wrote: CREATE UNIQUE INDEX name on table(param1,param2); How to create such unique index when param2 is bool type, and this param2 should be accepted only in case of true ? I tried: CREATE UNIQUE INDEX name on table(param1,(param2 = 'true')); but it's not working. Not sure if this is what you want: create unique index on table(param1, param2) where param2 is true; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Shared memory and FreeBSD's jail()
lister [EMAIL PROTECTED] writes: At the BSDCan tutorial last week on jails (and several other times) there was discussion regarding Postgres's use of system V style shared memory, and an unfortunate side effect of making jail() less secure. Specifically, to allow Postgres to operate in a jail()ed environment, the sysctl : jail.sysvipc_allowed=1 has to be set. This allows ALL jails to access the memory, at the least leaving Postgres open to attack, at the worst allowing a door into who knows what security breach. This claim is really pretty bogus, since there is still standard file-permission-like security on the shared memory. Only if you give usage of the postgres account to processes running in other jails is there any risk. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Preserving data after updates
Berend Tober [EMAIL PROTECTED] writes: But after doing a dump of the modified data base, the script created by pg dump wants to recreate the history table as ... CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR (first_name IS NOT NULL))), Hmm, it shouldn't do that ... and in a quick test here I couldn't reproduce any such bug. What version of pg_dump are you using? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
On May 19, 2005, at 5:42 AM, John DeSoi wrote: On May 19, 2005, at 1:34 AM, Matthew Hixson wrote: /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) After upgrading to 10.4.1 and using Michael's parameters to ./ configure I still get the exact same build error. Any Postgres hackers out there interested in logging into my machine and seeing what they can do to fix this? [Wild stabs in the dark to follow] What kind of Mac is it? The error had something to do with cputype. When I installed XCode 2.0 yesterday I noticed it installs gcc 3 and 4. Maybe you could try using gcc 3 and see if it makes a difference. My machine is a 1Ghz G4 Powerbook. This machine was upgraded from 10.3.9 to 10.4 and then brought up to 10.4.1 through software update. I have Xcode 1.1 installed and my gcc is: [EMAIL PROTECTED]:~/$ gcc -v Reading specs from /usr/libexec/gcc/darwin/ppc/3.3/specs Thread model: posix gcc version 3.3 20030304 (Apple Computer, Inc. build 1495) So perhaps I should just upgrade XCode and that'll fix my problem. -M@ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres in government
Scott Marlowe wrote: On Wed, 2005-05-18 at 21:24, Mark Steckel wrote: ...We are proposing that Postgres be used for the application database. Not too surprisingly we are being asked for additional information because Postgres is open source. So is the implication that they think open source is a bad thing? I would think they would question a recommendation for using proprietory products! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Shared memory and FreeBSD's jail()
Scott Marlowe wrote: On Thu, 2005-05-19 at 09:46, lister wrote: At the BSDCan tutorial last week on jails (and several other times) there was discussion regarding Postgres's use of system V style shared memory, and an unfortunate side effect of making jail() less secure. Specifically, to allow Postgres to operate in a jail()ed environment, the sysctl : jail.sysvipc_allowed=1 has to be set. This allows ALL jails to access the memory, at the least leaving Postgres open to attack, at the worst allowing a door into who knows what security breach. Question : is there any way to run Postgres securely in a jail? I'm note sure that this is an actual security issue. Assuming that the processes running each jail are running under a different UID, they shouldn't be anymore able to access each other's shared memory than they would be able to share each others files. In a strict definition of 'issue' you may be right (I am not a security officer) but speaing from a practically perspective : 1) One of the purposes of jail is to contain a breach, making a compromised server a matter of restoring a directory, not a system rebuild. A break-in is often not the result of one software fault, but a set of steps. If one jail is rooted, the postgres jail can be abused. 2) Many hosting companies use jail() to deliver a pseudo machine to customers, with root privs. This effectively bars postgres from this senerio. This was the topic of 20 minutes of conversation in 2 tutorials at BSDCan. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Preserving data after updates
Tom Lane wrote: Berend Tober [EMAIL PROTECTED] writes: But after doing a dump of the modified data base, the script created by pg dump wants to recreate the history table as ... CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR (first_name IS NOT NULL))), Hmm, it shouldn't do that ... and in a quick test here I couldn't reproduce any such bug. What version of pg_dump are you using? Sorry I failed to specify. Production version is 7.3.1 (change is hard!), although I origianally worked out the implementation on version 8. I bet that is the problem. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Shared memory and FreeBSD's jail()
On Thu, 2005-05-19 at 10:49, lister wrote: Scott Marlowe wrote: On Thu, 2005-05-19 at 09:46, lister wrote: At the BSDCan tutorial last week on jails (and several other times) there was discussion regarding Postgres's use of system V style shared memory, and an unfortunate side effect of making jail() less secure. Specifically, to allow Postgres to operate in a jail()ed environment, the sysctl : jail.sysvipc_allowed=1 has to be set. This allows ALL jails to access the memory, at the least leaving Postgres open to attack, at the worst allowing a door into who knows what security breach. Question : is there any way to run Postgres securely in a jail? I'm note sure that this is an actual security issue. Assuming that the processes running each jail are running under a different UID, they shouldn't be anymore able to access each other's shared memory than they would be able to share each others files. In a strict definition of 'issue' you may be right (I am not a security officer) but speaing from a practically perspective : 1) One of the purposes of jail is to contain a breach, making a compromised server a matter of restoring a directory, not a system rebuild. A break-in is often not the result of one software fault, but a set of steps. If one jail is rooted, the postgres jail can be abused. 2) Many hosting companies use jail() to deliver a pseudo machine to customers, with root privs. This effectively bars postgres from this senerio. This was the topic of 20 minutes of conversation in 2 tutorials at BSDCan. Ahh, ok. Seems to me the bug here is that jails can't provide localized shared memory implementations. If jails provided local virtual shared memory, there would be no problem. But the real solution to me is to move right on to actual server virtualization. There are quite a number of open source virtualization projects out there, and once they reach maturity, I would use them. Til then, someone might want to fix the jail implementation to enclose the shared memory it uses in something similar to the rest of the jail. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Count and Results together
On 5/19/05, Jan Sunavec [EMAIL PROTECTED] wrote:
I am using libpg.so. I tryed find solution for this problem in internet
but, I don't find nothing yet. I have idea get rowcount throught some
function write in C. Or is there any plan add this feature into PostgreSQL?
Theoretically you could declare a cursor, count the rows (from plpgsql
preferably, to avoid sending all the data to the client), rewind the
cursor (MOVE) and return it. The problem is that I think it is not possible
for PL/pgsql to return both integer (row count) and a cursor (for the
query rewound) at the same time...
Alternatively you could make such a trick, but this won't work (moving
inside cursor seems to cause the nextval() to reevaluate. Anyway
the idea would be:
BEGIN;
CREATE TEMPORARY SEQUENCE rowcount;
DECLARE thedata SCROLL CURSOR FOR SELECT nextval('rowcount'), * FROM tbl;
MOVE ABSOLUTE -2 IN thedata; -- get the last row...
FETCH thedata; -- the nextval column should contain the row count.
MOVE ABSOLUTE 1 IN thedata; -- rewind the cursor
FETCH, fetch, fetch or FETCH ALL...
DROP SEQUENCE...
COMMIT or maybe even ROLLBACK;
Though looking promising, the problem is that nextval() seems to
be reevaluated for each row... [ Is it the way it should be? ]
Other rather silly ideas:
create temporary table (preferably within transaction with on commit
drop or truncate) with index on rowcount column. select results into
this table (with row counter done with help of the sequence). select
rowcount from temptable order by rowcount desc limit 1; select * from
temptable;
Overkill but for complex queries it might do a trick. Then again,
such caching-table might be useful for serving search results by
many Apache daemons...
HTH,
Daiwd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Count and Results together
On 5/19/05, Dawid Kuroczko [EMAIL PROTECTED] wrote: On 5/19/05, Jan Sunavec [EMAIL PROTECTED] wrote: I am using libpg.so. I tryed find solution for this problem in internet but, I don't find nothing yet. I have idea get rowcount throught some function write in C. Or is there any plan add this feature into PostgreSQL? Theoretically you could declare a cursor, count the rows (from plpgsql preferably, to avoid sending all the data to the client), rewind the cursor (MOVE) and return it. The problem is that I think it is not possible for PL/pgsql to return both integer (row count) and a cursor (for the query rewound) at the same time... I stand corrected. GET DIAGNOSTICS, PQntuples(), etc. are the way to do it. Regards, Dawid ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres in government
Mark Steckel wrote: I have also scrounged the Internet looking for examples of Postgres being used in government, preferably in 24x7 capacities. I find googling for vendors who are known to use PostgreSQL and searching for people's resumes is a good way to find descriptions of Government projects along with contacts for references. Here's a couple that look interesting. US Navy, Northrop Grumman Use PostgreSQL in the Navy Enterprise Portal and the Fleet Numerical Meteorology and Oceanography Center(FNMOC) Portal: http://kennethbowen.com/kbresume.html Develop J2EE application to store user profiles for the Navy Enterprise Portal and the Fleet Numerical Meteorology and Oceanography Center(FNMOC) Portal using JBoss application server and PostgreSQL database. DOD, USGS, USDA, Army Corp of Engineers, Navy, through a company called Sanz: http://postgis.refractions.net/pipermail/postgis-users/2005-March/007399.html Sanz manages tens of terrabyte datasets of raster and vector data for the DOD, USGS, USDA, Army Corp of Engineers, Navy, etc. using postgresql and postgis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Shared memory and FreeBSD's jail()
lister [EMAIL PROTECTED] writes: This was the topic of 20 minutes of conversation in 2 tutorials at BSDCan. Well, if the BSD people are so concerned about it, why don't they fix their bleedin' OS? It's inexcusable to have a jail feature that doesn't cover such a major part of Unix as the SysV IPC facilities. Of course, it's a lot easier to just blame the messenger. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
On May 18, 2005, at 9:27 PM, Matthew Hixson wrote: /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) I had some issues with this error on a few other things on my Tiger box. After I installed Xcode2 things worked fine. Try installing xcode2 and let us know. (And I compiled and run Tiger on my tiger machine here, with xcode2, gcc 4) -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres in government
...We are proposing that Postgres be used for the application database. Not too surprisingly we are being asked for additional information because Postgres is open source. So is the implication that they think open source is a bad thing? I would think they would question a recommendation for using proprietory products! It's all about covering their butts... If they buy SQLServer and it goes bad, they can sue Microsoft. Or at least they like to think they can. If PostgreSQL goes bad, who are they going to sue? No one... which means the guy who approved it is the scape goat -- which is why he wants proof that others have found it worthy... Silly, but that's probably what's happening. -philip ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres in government
On Thu, 2005-05-19 at 11:35, Philip Hallstrom wrote: ...We are proposing that Postgres be used for the application database. Not too surprisingly we are being asked for additional information because Postgres is open source. So is the implication that they think open source is a bad thing? I would think they would question a recommendation for using proprietory products! It's all about covering their butts... If they buy SQLServer and it goes bad, they can sue Microsoft. Or at least they like to think they can. Ummm. No, they can't. But they can BLAME microsoft to their bosses. Microsoft's EULA, like most for commercial software, makes it clear that all you'll ever get out of them is your money back. And even that is a stretch. Read up on their indemnity. It's basically a money back guarantee, and nothing more. If PostgreSQL goes bad, who are they going to sue? The same people they would sue if MSSQL went south... No one... Correct. which means the guy who approved it is the scape goat Sadly, lots of people in positions of power still want scape goats, rather than proven results. -- which is why he wants proof that others have found it worthy... When what he should want is proof that it will work for HIS situation, since other's use may or may not reflect his. Silly, but that's probably what's happening. Most surely. It's common when people with business degrees but poor understanding of the scientific method get involved. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] CREATE TABLE problem in plpgsql trigger
Stephan Szabo wrote: On Thu, 19 May 2005, James Croft wrote: Hi all, I'm trying to create a trigger function for a few tables that will store old versions of rows prior to any update on them. Part of the function needs to creates other tables (the table to store these snapshots in). When this trigger runs I get the and error of 'syntax error at or near $1 at character 15' which is the CREATE TABLE line. Yes, I don't think support statements like CREATE TABLE currently work with variables directly. You probably can use EXECUTE however by generating a string containing the command you want to run first. Something like: EXECUTE ''CREATE TABLE '' || snapshottable || '' (LIKE '' || originaltable || '')''; excepting that you'd need to be more careful with quoting. Thanks Stephanm, that fixed it. -- James Croft Lumison t: 0845 1199 911 f: 0845 1199 901 d: 0131 5144 022 begin:vcard fn:James Croft n:Croft;James org:Lumision Ltd adr:;;12 Dock Place;Edinburgh;;EH6 6LU;UK email;internet:[EMAIL PROTECTED] title:Systems Developer tel;work:0131 514 4022 tel;fax:0845 1199 900 x-mozilla-html:TRUE url:http://www.lumison.net version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
On May 19, 2005, at 9:27 AM, Jeff Trout wrote: On May 18, 2005, at 9:27 PM, Matthew Hixson wrote: /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) I had some issues with this error on a few other things on my Tiger box. After I installed Xcode2 things worked fine. Try installing xcode2 and let us know. (And I compiled and run Tiger on my tiger machine here, with xcode2, gcc 4) I just installed Xcode 2 and postgres built successfully. A 'make check' also says all 96 tests passed. Thanks for the help everyone. Keep up the good work. -M@ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Preserving data after updates
Berend Tober [EMAIL PROTECTED] writes: Tom Lane wrote: Hmm, it shouldn't do that ... and in a quick test here I couldn't reproduce any such bug. What version of pg_dump are you using? Sorry I failed to specify. Production version is 7.3.1 (change is hard!), although I origianally worked out the implementation on version 8. I bet that is the problem. The case I tested seems to work in 7.3 as well: CREATE TABLE person (last_name varchar(24), first_name varchar(24), CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR (first_name IS NOT NULL; CREATE TABLE person_change_history( action VARCHAR(6), update_date TIMESTAMP NOT NULL DEFAULT NOW(), update_user NAME NOT NULL DEFAULT CURRENT_USER ) INHERITS (person); pg_dump puts the CONSTRAINT only on person, as it should. I'm testing 7.3.10 but I don't see any changes in the 7.3 CVS log that look related. Can you put together a reproducible test case? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Temp tables as session var containers
Hi, I've seen the session variable question pop up a fair bit on this list. The temporary table solution seems good but I've got a question before using it... - My app creates a temp table for session vars - UPDATE, INSERT and DELETE triggers on tables use this data My question is: If I run a query directly through the psql command line tool (or another app that doesn't setup this temp table) that temp table wont exist. How can I write the trigger function to detect the absence of the temp table and deal with it gracefully? I think I need some SQL to determine which pg_temp_N schema belongs to my session. Is this possible? TIA, James ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] numeric precision when raising one numeric to another.
PostgreSQL has a numeric exp() function and a numeric ln() function, so a numeric pow() function is trivial. pow(A,z) = exp(z*ln(A)) Probably, it could be made a bit more efficient if specially tuned so as to not require these functions. Newton's method (or something of that nature) could obviously be used to write a more generic version. The double C function can provide the starting estimate. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Alvaro Herrera Sent: Wednesday, May 18, 2005 8:33 PM To: John Burger Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] numeric precision when raising one numeric to another. On Wed, May 18, 2005 at 10:46:50PM -0400, John Burger wrote: Considering that the SQL spec says the result of multiplication of exact numeric types is exact numeric types of precision S1+S2, and exponentiation is nothing more than repeated multiplication, ... not when the exponent is non-integral. For one thing. For another, I believe the standard C library only has floating point exponentiation functions, not that there aren't plenty of numeric libraries with integral ones. Finally, exponentiated numbers get real big, real fast, and the floating point types can hold much larger magnitudes than the integer types, albeit inexactly. For example, on the Mac I'm using now, long long ints max out at about 10^19, while long doubles can represent 10^308. Well, we already have an interesting library of mathematical functions for NUMERIC (which is an arbitrary precision type, so it wouldn't matter how big the result would get). I think the only reason we don't have a NUMERIC exponentiation function is that nobody has implemented it. -- Alvaro Herrera (alvherre[a]surnet.cl) People get annoyed when you try to debug them. (Larry Wall) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Temp tables as session var containers
James Croft wrote:
Hi,
I've seen the session variable question pop up a fair bit on this list.
The temporary table solution seems good but I've got a question before
using it...
Another option is to use one of the procedural languages that provide
global variable storage. The attached examples are in TCL.
Set the user-id
SELECT app_session('UID', 'ABC1234');
Get the user-id
SELECT app_session('UID');
--
Richard Huxton
Archonet Ltd
-- app_session(VARNAME, VALUE)
-- Defines a text variable and sets its value.
-- If you try to set the same VARNAME twice in one session, an error is
returned.
-- If VALUE is null, just returns the value.
--
CREATE OR REPLACE FUNCTION app_session(text, text) RETURNS text AS '
upvar app_sess_vars a
if {![ argisnull 2 ]} {
if {[ info exists a($1) ]} {
elog ERROR app_session(): Already set var $1 this
session
}
set a($1) $2
}
return $a($1)
' LANGUAGE pltcl;
-- app_session(VARNAME)
-- Returns the value of VARNAME (if set) or UNDEFINED
-- NOTE - this function is marked IMMUTABLE - do not use as a DEFAULT
source
-- in a table definition
--
CREATE OR REPLACE FUNCTION app_session(text) RETURNS text AS '
upvar app_sess_vars a
if {![ info exists a($1) ]} {
return UNDEFINED
}
return $a($1)
' LANGUAGE pltcl IMMUTABLE;
-- app_session_int(VARNAME)
-- Returns the value of VARNAME (if set) or 0
-- NOTE - this function is marked IMMUTABLE - do not use as a DEFAULT
source
-- in a table definition
--
CREATE OR REPLACE FUNCTION app_session_int(text) RETURNS int4 AS '
upvar app_sess_vars a
if {![ info exists a($1) ]} {
return 0
}
return $a($1)
' LANGUAGE pltcl IMMUTABLE;
-- app_session_vol(VARNAME)
-- Returns the value of VARNAME (if set) or UNDEFINED
-- NOTE - this function is marked IMMUTABLE - do not use as a DEFAULT
source
-- in a table definition
--
CREATE OR REPLACE FUNCTION app_session_vol(text) RETURNS text AS '
upvar app_sess_vars a
if {![ info exists a($1) ]} {
return UNDEFINED
}
return $a($1)
' LANGUAGE pltcl VOLATILE;
-- app_session_int_vol(VARNAME)
-- Returns the value of VARNAME (if set) or 0
-- NOTE - this function is marked VOLATILE
--
CREATE OR REPLACE FUNCTION app_session_int_vol(text) RETURNS int4 AS '
upvar app_sess_vars a
if {![ info exists a($1) ]} {
return 0
}
return $a($1)
' LANGUAGE pltcl VOLATILE;
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Postgres in government
[EMAIL PROTECTED] wrote on 05/19/2005 11:35:07 AM: ...We are proposing that Postgres be used for the application database. Not too surprisingly we are being asked for additional information because Postgres is open source. So is the implication that they think open source is a bad thing? I would think they would question a recommendation for using proprietory products! It's all about covering their butts... If they buy SQLServer and it goes bad, they can sue Microsoft. Or at least they like to think they can. If PostgreSQL goes bad, who are they going to sue? No one... which means the guy who approved it is the scape goat -- which is why he wants proof that others have found it worthy... Silly, but that's probably what's happening. Exactly that has happened to me. Indemnification is the term. I was also told that before using any opensource project I had to locally configuration control the product and perform a complete review of the source. I don't have to do that with Oracle because they've got lawyers, and we've got lawyers, and they know each other's phone numbers. Anybody have a phone number for PostgreSQL's lawyer? Don't flame me, please, I'm really kidding about the lawyer thing, but the rest is true. Rick -philip ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] numeric precision when raising one numeric to another.
We use Moshier's excellent qfloat numbers. http://www.moshier.net/qlib.zip Documentation: http://www.moshier.net/qlibdoc.html So, if you do the following query using CONNX: select convert(pow(9.5,5.9), varchar) You will get: 586906.97548405202106027547827738573075504470845684721318303336760202394 5916438064873363100477233500417619 select pow(9.5,5.9) will return 586906.975484052 Since we bind to double by default. Correct answer is (1000+ digits correct): 586906.97548405202106027547827738573075504470845684721318303336760202394 591643806487336310047723350041762446340060298807517843626920535883745120 98626411010308125070048988991029963307831015812131852033741567043945 026243178422915290830477381800527219457732229115168020868495354958648414 971711685840852684310130094029132142016389076807514261122763703528030232 527888410105794936941873557344173381053429729906642653004811669321631656 412265025095200907690509153627646726650174318576911125609483654656735531 730688699016039020145753010069585349923506043259767525488453544723589880 427675085429230106535405724821481118286775763085905255396545439080913364 233329975992733986721408870779427889446166143315004295671202526112889352 04340305995808257911277403826735005243749050919501832287479909523379 14526128215203402442260653013983173651648948479379642961647792197822 118268619926636309476522424825736766449170308662847527591516245860159270 335785812239686778074630519049627528571047048724459826189283691382474184 22032503387712889 It might seem like overkill, but (for instance) we have customers who measure every toll on toll roads for large states in the eastern US. If they want to calculate 5 years of interest on the current balance, accurate to the penny, at small interest rates, such precision is very helpful. His (Moshier's) math stuff is really top-notch. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Martijn van Oosterhout Sent: Thursday, May 19, 2005 2:14 AM To: Alvaro Herrera Cc: John Burger; pgsql-general@postgresql.org Subject: Re: [GENERAL] numeric precision when raising one numeric to another. On Wed, May 18, 2005 at 11:32:40PM -0400, Alvaro Herrera wrote: On Wed, May 18, 2005 at 10:46:50PM -0400, John Burger wrote: For one thing. For another, I believe the standard C library only has floating point exponentiation functions, not that there aren't plenty of numeric libraries with integral ones. Finally, exponentiated numbers get real big, real fast, and the floating point types can hold much larger magnitudes than the integer types, albeit inexactly. For example, on the Mac I'm using now, long long ints max out at about 10^19, while long doubles can represent 10^308. Well, we already have an interesting library of mathematical functions for NUMERIC (which is an arbitrary precision type, so it wouldn't matter how big the result would get). I think the only reason we don't have a NUMERIC exponentiation function is that nobody has implemented it. The prerequisites for such a function would be a log() and exp() function for numeric. And the real question there would be, what's a sufficient accuracy? Numbers people actually use rarely have even rational logarithms, so there is no way to store them 100% accurate. As long as you're using integral exponents you can get away with multiplication. BTW, the commandline utility bc has arbitrary number arithmatic, maybe we can see how they do it? It defaults to 20 digits precision, which is obviously not enough for large exponents. Hmm, it looks like even they don't support raising to fractional powers. When calculating 2^100, you need a precision of at least 35 decimal places to get in the ballpark of the correct figure using log/exp, 30 isn't enough. Maybe do exact for integer exponents and approx for non-integer? [EMAIL PROTECTED]:~$ bc -l bc 1.06 Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc. 2^100 1267650600228229401496703205376 2^100.1 Runtime warning (func=(main), adr=11): non-zero scale in exponent 1267650600228229401496703205376 e(l(2)*100) 1267650600228229400579922894637.90158245154400629512 scale=30 e(l(2)*100) 1267650600228229401496703205353.61733731135194699059124092 scale=35 e(l(2)*100) 1267650600228229401496703205375.99897630874075350752485091801369515 Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
For what it is worth... I have 10.4.1 (Tiger) (archived and ) installed on my old 933MHZ QuickSiver. I also have Xcode 2.0 installed. When I compiled 8.0.3 it seemed to generated many more warnings with 4.0 gcc than the older 3.3 gcc. At least that is my recollection... I have installed the rascal and it appears to be working... Jerry ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Postgres in government
* Mark Steckel ([EMAIL PROTECTED]) wrote: So, if you have used Postgres (or know that it has been used) for a government project, especially in a 24x7 environment, I would greatly appreciate hearing about it. Ideally, I need more than just the project name. Specifically, A brief description of the project, number of users/transactions as day/week/month, etc, whatever details you have and can share. Please CC me as I'm not subscribed to the list. And since I'm leaving on vacation in 3 days, please CC my coworkers at [EMAIL PROTECTED] Sorry about the ugly links, but... GSA Networx Acquisition: http://www.gsa.gov/Portal/gsa/ep/channelView.do?pageTypeId=8199channelId=-16201 GSA Networx Hosting Center: http://www.gsa.gov/Portal/gsa/ep/contentView.do?programId=11454channelId=-16201ooid=16100contentId=18739pageTypeId=8199contentType=GSA_BASICprogramPage=%2Fep%2Fprogram%2FgsaBasic.jspP=TOS3 GSA Networx Hosting Center User Instructions: http://www.gsa.gov/gsa/cm_attachments/GSA_BASIC/NHC%20User%20Instructions%20v1.4_R2-wT3-j_0Z5RDZ-i34K-pR.pdf Check out the bottom of page 2. :) Enjoy... :) Stephen signature.asc Description: Digital signature
Re: [GENERAL] Postgres in government
The NOAA National Data Buoy Center is a government customer (there are many commercial customers) for our wXstation(R) product, which uses PostgreSQL as its database. The number of government customers may increase dramatically in the near future. -- P. J. Josh Rovero Sonalysts, Inc. Email: [EMAIL PROTECTED]www.sonalysts.com215 Parkway North Work: (860)326-3671 or 442-4355 Waterford CT 06385 *** ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Postgresql 7.4.7 docs(PDF)
What they have is for version 7.2 and i want for 7.4.7 only, i have tried to follow instructions on how to make the pdf/ps version and all i get is errors. If someone already has a pdf/ps and can email it, will be a big help. Thanks, Hrishi On 5/19/05, Richard Huxton dev@archonet.com wrote: Hrishikesh Deshmukh wrote: Hi All, If someone can email Postgresql 7.4.7 docs(PDF). It will be a big help. I am unable to get pdf docs for this version on debian system. Please help. Available from here: http://www.postgresql.org/docs/manuals/ See the links on the right-hand-side of the page. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Postgres in government
Sadly, lots of people in positions of power still want scape goats, rather than proven results. No, it could be that the OP's organization is looking for some proof of postgresql's results. A lot of people are familiar with the criteria for evaluating a company or commercial product. Open source products have a different set of criteria that are sometimes a bit harder to pin down. An advantage of a commercial product is that you can ask the vendor for references from people doing similar work to what you are doing. As we can see here, that's a bit more difficult for open source projects. It's frustrating, but just one of the hurdles you have to face when advocating open source software. My first experience with this was in 1991, when I introduced Tcl and Perl into NEC. It did not happen until Larry Wall's book came out and I could answer the question why are these guys doing this? how are they going to make money to continue to do this? with hey they're giving away the software and making a fortune on the books! Most surely. It's common when people with business degrees but poor understanding of the scientific method get involved. As opposed to people with science degrees but poor understanding of business methods? :-) Cheers, Mark -- Mark Harrison Pixar Animation Studios ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
I also thought I saw quite a few more warnings this time than in the past. -M@ On May 19, 2005, at 11:05 AM, Jerry LeVan wrote: For what it is worth... I have 10.4.1 (Tiger) (archived and ) installed on my old 933MHZ QuickSiver. I also have Xcode 2.0 installed. When I compiled 8.0.3 it seemed to generated many more warnings with 4.0 gcc than the older 3.3 gcc. At least that is my recollection... I have installed the rascal and it appears to be working... Jerry ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] numeric precision when raising one numeric to another.
On Thu, May 19, 2005 at 10:41:51AM -0700, Dann Corbit wrote: We use Moshier's excellent qfloat numbers. http://www.moshier.net/qlib.zip Documentation: http://www.moshier.net/qlibdoc.html So, if you do the following query using CONNX: select convert(pow(9.5,5.9), varchar) You will get: 586906.97548405202106027547827738573075504470845684721318303336760202394 5916438064873363100477233500417619 But it's not accurate enough with the default settings. For example 2^100: # select exp( ln(2::numeric) * 100 ); exp -- 1267650600228229400579922894637.9015824515440063 (1 row) The answer should be: 1267650600228229401496703205376 So it's wrong from the 14th digit onwards. If that's the case you may as well stick to using floating point. It does however appear you can influence the precision, See: # select exp( ln(2::numeric(50,30)) * 100 ); exp 1267650600228229401496703205375.991370405139384131115870698781 (1 row) Using numeric(50,25) gets you only 28 correct digits. So, if you know how big your result is going to be you can adjust the types to match and get whatever precision you want. Given that you can estimate the number of digits easily enough (it's linear with the value before the exp()) maybe you can get it to automatically choose the right precision? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpcFDZZc5mcf.pgp Description: PGP signature
Re: [GENERAL] preserving data after updates
Greg Patnude wrote: Yeah this is where the inheritance model gets a little funky What do you have SQL_INEHERITANCE set to when you dump the database ? Ive never tested this so I dont know if it makes a difference being on or off when you dump a table. You might try it and compare the two versions of the DDL for your inherited tables I set SQL_INEHERITANCE to OFF because I have lots of existing queries in an application that do not include the ONLY option. I did try setting it back on the default ON, and the problem remained.. Note: postgreSQL recommends leaving SQL_INHERITANCE at ON and using the keyword ONLY Ive seen that before The problem is that pg_dump creates the person_history table as a standalone table (look at the DDL) with the keyword INHERITS My gut feeling is that this is probably a bug in pg_dump I dont think pg_dump really knows how to dump just the additional fields specified in an inherited table so it dumps the actual definition it finds in the system catalogs If you poke around in pg_catalog, youll find that the catalog definition is a combination of pointers to the parent table and any additional fields, constraints, rules, etc you defined when you created the inherited table. My work-around has been to drop and recreate the history tables using the original SQL I used to create the inherited table in the first place ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Preserving data after updates
Tom Lane wrote:
The case I tested seems to work in 7.3 as well:
CREATE TABLE person (last_name varchar(24),
first_name varchar(24),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL;
CREATE TABLE person_change_history(
action VARCHAR(6),
update_date TIMESTAMP NOT NULL DEFAULT NOW(),
update_user NAME NOT NULL DEFAULT CURRENT_USER
) INHERITS (person);
pg_dump puts the CONSTRAINT only on person, as it should. I'm testing
7.3.10 but I don't see any changes in the 7.3 CVS log that look related.
Can you put together a reproducible test case?
I tried a simpler example than my original, as you have, and the problem
bahavior didn't manifest, but it still happens in my dev copy of my
production database. The immediately obvious difference between the
simpler example, like yours, and the actual case in which the problem
manifests is that the problem case to of the table constraints call a
user-defined function check_pattern() (which tests the column value
against a regular expression), i.e.
CREATE OR REPLACE FUNCTION public.check_pattern(varchar, varchar)
RETURNS bool AS
'
DECLARE
l_value ALIAS FOR $1;
l_pattern ALIAS FOR $2;
l_row RECORD;
BEGIN
IF (l_value IS NOT NULL) AND (LENGTH(l_value) 0) THEN
IF EXISTS(SELECT 1 FROM public.regular_expression WHERE
UPPER(description) = UPPER(l_pattern)) THEN
SELECT INTO l_row regular_expression, user_message FROM
public.regular_expression WHERE UPPER(description) = UPPER(l_pattern);
IF NOT (l_value ~ l_row.regular_expression) THEN
RAISE EXCEPTION \'Invalid %. %\', l_pattern, l_row.user_message;
END IF;
END IF;
END IF;
RETURN TRUE;
END;' LANGUAGE 'plpgsql' VOLATILE;
in the definition:
CREATE TABLE person
(
person_pk int4 NOT NULL DEFAULT nextval('person_person_pk_seq'::text),
last_name varchar(24),
first_name varchar(24),
middle_name varchar(24),
e_mail_address name,
social_security_no varchar(11),
CONSTRAINT person_pkey PRIMARY KEY (person_pk),
CONSTRAINT person_e_mail_address CHECK
(check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::character varying)),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'::character
varying))
)
WITHOUT OIDS;
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] numeric precision when raising one numeric to another.
If you want to create a pow() function for numeric using existing numeric functions, it [the new function] should be aware of the precision of the inputs, and the precision of the output should be their product. So, if you do pow(numeric(10,5), numeric(10,5)) then the result column should be numeric(100,25) if you want to retain full precision. -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Thursday, May 19, 2005 2:02 PM To: Dann Corbit Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org Subject: Re: [GENERAL] numeric precision when raising one numeric to another. On Thu, May 19, 2005 at 10:41:51AM -0700, Dann Corbit wrote: We use Moshier's excellent qfloat numbers. http://www.moshier.net/qlib.zip Documentation: http://www.moshier.net/qlibdoc.html So, if you do the following query using CONNX: select convert(pow(9.5,5.9), varchar) You will get: 586906.97548405202106027547827738573075504470845684721318303336760202394 5916438064873363100477233500417619 But it's not accurate enough with the default settings. For example 2^100: # select exp( ln(2::numeric) * 100 ); exp -- 1267650600228229400579922894637.9015824515440063 (1 row) The answer should be: 1267650600228229401496703205376 So it's wrong from the 14th digit onwards. If that's the case you may as well stick to using floating point. It does however appear you can influence the precision, See: # select exp( ln(2::numeric(50,30)) * 100 ); exp 1267650600228229401496703205375.991370405139384131115870698781 (1 row) Using numeric(50,25) gets you only 28 correct digits. So, if you know how big your result is going to be you can adjust the types to match and get whatever precision you want. Given that you can estimate the number of digits easily enough (it's linear with the value before the exp()) maybe you can get it to automatically choose the right precision? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] numeric precision when raising one numeric to another.
Hmmm I underestimated. pow(9.9,9.9) = 9.998748785736894607828527462269893046126336085 91664915498635306081273911645075964079222720857427 35641018572673827935330501923067157794798212338823 24997145234949798725508071849154834025252682619864 09675931105114160107573542813573334036043627693673 32584230414090115274301822704676399594689777183090 95124350838052746795283582659784697437868624515447 84308955024802754764364277858847454870139679632204 93566098207186651878539285222697852739872657689082 7774052846676926385269704577829403518386946691 11157539964528436618742040945886361696712501785143 49612003446329175703756667138162553151705912580792 12331560317684418171064195077598932031644579554853 98595138860229023469055949001949521877405516916475 97554564462253024119778312344592336542732038212175 43130812948451126588746192211036266786198594583755 89036373827433475892132965189682874790600247279436 07120265912512012429492123644988164587146533255393 93335345599658088256314460922495519381049143246081 37075434256493449284197921246089978660147299071527 8174795070535064342859550611e49 So the precision calculation would be much more complicated. -Original Message- From: Dann Corbit Sent: Thursday, May 19, 2005 2:20 PM To: 'Martijn van Oosterhout' Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org Subject: RE: [GENERAL] numeric precision when raising one numeric to another. If you want to create a pow() function for numeric using existing numeric functions, it [the new function] should be aware of the precision of the inputs, and the precision of the output should be their product. So, if you do pow(numeric(10,5), numeric(10,5)) then the result column should be numeric(100,25) if you want to retain full precision. -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Thursday, May 19, 2005 2:02 PM To: Dann Corbit Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org Subject: Re: [GENERAL] numeric precision when raising one numeric to another. On Thu, May 19, 2005 at 10:41:51AM -0700, Dann Corbit wrote: We use Moshier's excellent qfloat numbers. http://www.moshier.net/qlib.zip Documentation: http://www.moshier.net/qlibdoc.html So, if you do the following query using CONNX: select convert(pow(9.5,5.9), varchar) You will get: 586906.97548405202106027547827738573075504470845684721318303336760202394 5916438064873363100477233500417619 But it's not accurate enough with the default settings. For example 2^100: # select exp( ln(2::numeric) * 100 ); exp -- 1267650600228229400579922894637.9015824515440063 (1 row) The answer should be: 1267650600228229401496703205376 So it's wrong from the 14th digit onwards. If that's the case you may as well stick to using floating point. It does however appear you can influence the precision, See: # select exp( ln(2::numeric(50,30)) * 100 ); exp 1267650600228229401496703205375.991370405139384131115870698781 (1 row) Using numeric(50,25) gets you only 28 correct digits. So, if you know how big your result is going to be you can adjust the types to match and get whatever precision you want. Given that you can estimate the number of digits easily enough (it's linear with the value before the exp()) maybe you can get it to automatically choose the right precision? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Preserving data after updates
Berend Tober [EMAIL PROTECTED] writes: I tried a simpler example than my original, as you have, and the problem bahavior didn't manifest, but it still happens in my dev copy of my production database. The immediately obvious difference between the simpler example, like yours, and the actual case in which the problem manifests is that the problem case to of the table constraints call a user-defined function check_pattern() (which tests the column value against a regular expression), i.e. Nope, that's not it. Still works fine here. What do you get from select conname, consrc from pg_catalog.pg_constraint where contype = 'c' and conrelid = 'person'::regclass; select conname, consrc from pg_catalog.pg_constraint where contype = 'c' and conrelid = 'person_change_history'::regclass; AFAICS from looking at the 7.3 pg_dump source, it should suppress any constraint on person_change_history that looks identical to one of the parent table's constraints in this query. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] numeric precision when raising one numeric to another.
On Thu, May 19, 2005 at 02:25:58PM -0700, Dann Corbit wrote: Hmmm I underestimated. pow(9.9,9.9) = Yeah, a number with x digits raised to the power with something y digits long could have a length approximating: x * (10^y) digits So two numbers both 4 digits long can have a result of upto 40,000 digits. You're only going to be able to them represent exactly for cases where y is small and integer. What's a meaningful limit? Do we simply say, you get upto 100 digits and that's it? Or an extra parameter so you can specify directly? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpDnhNeHqXL1.pgp Description: PGP signature
Re: [GENERAL] numeric precision when raising one numeric to another.
Probably, the important meaningful cases are ones that have small exponents (HOPEFULLY less than 25) used in interest calculations. Million digit numbers are really only interesting in the field of pure mathematics, since the number of elementary particles in the universe is well under a googol (10^100). But if someone has a billion dollars (and some do, of course -- even potentially trillions if it is a government) and they want to do a long term interest calculation accurate to the penny, then we should be careful to get that answer right. The calculation pow(huge,huge) will result in a big pile of fascinating digits that won't really have much physical meaning. -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Thursday, May 19, 2005 2:48 PM To: Dann Corbit Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org Subject: Re: [GENERAL] numeric precision when raising one numeric to another. On Thu, May 19, 2005 at 02:25:58PM -0700, Dann Corbit wrote: Hmmm I underestimated. pow(9.9,9.9) = Yeah, a number with x digits raised to the power with something y digits long could have a length approximating: x * (10^y) digits So two numbers both 4 digits long can have a result of upto 40,000 digits. You're only going to be able to them represent exactly for cases where y is small and integer. What's a meaningful limit? Do we simply say, you get upto 100 digits and that's it? Or an extra parameter so you can specify directly? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] bulk loader
Hi All, Is there a bulk loader in postgresql with which one can read in say a tab delimited format text file. Before one does all one has to do is create the table with text file column names as attributes, once it is on DBMS world it will be a simple table (non-relational) Thanks, Hrishi ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] bulk loader
Hrishikesh Deshmukh wrote: Hi All, Is there a bulk loader in postgresql with which one can read in say a tab delimited format text file. Before one does all one has to do is create the table with text file column names as attributes, once it is on DBMS world it will be a simple table (non-relational) See the COPY command. Tab is the default delimiter in text mode. -- Guy Rouillier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Image storage questions
Joshua D. Drake wrote: External storing is useful but I prefer LO because all my data (binary and meta) is all in the same place for management. But if that's a big L in LO, performance and maintenance will be negatively affected, perhaps significantly. The DBMS will have to scan over all that large binary data to extract text or numeric data. And backups will copy that static binary data repeatedly. For those reasons, if I'm storing very large objects, like images, that I know I'll never search or update, I prefer to manage them externally. -- Guy Rouillier ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] bulk loader
Hrishikesh Deshmukh [EMAIL PROTECTED] writes: Hi All, Is there a bulk loader in postgresql with which one can read in say a tab delimited format text file. Before one does all one has to do is create the table with text file column names as attributes, once it is on DBMS world it will be a simple table (non-relational) Read up on the COPY command. -Doug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Image storage questions
Guy Rouillier [EMAIL PROTECTED] writes: Joshua D. Drake wrote: External storing is useful but I prefer LO because all my data (binary and meta) is all in the same place for management. But if that's a big L in LO, performance and maintenance will be negatively affected, perhaps significantly. The DBMS will have to scan over all that large binary data to extract text or numeric data. And backups will copy that static binary data repeatedly. For those reasons, if I'm storing very large objects, like images, that I know I'll never search or update, I prefer to manage them externally. Large objects (and reasonably large text/bytea columns as well) are stored out-of-line, so normal table scans don't have to read them unnecessarily. -Doug ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Locale C?
I'm installing PG8 for Windows and the default locale is C in the installer. What is locale C? CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Preserving data after updates
Tom Lane wrote: What do you get from select conname, consrc from pg_catalog.pg_constraint where contype = 'c' and conrelid = 'person'::regclass; conname | consrc ---+- person_e_mail_address | public.check_pattern((e_mail_address)::character varying, 'Internet E-Mail Address'::character varying) person_name_check | ((last_name IS NOT NULL) OR (first_name IS NOT NULL)) person_social_security_no | public.check_pattern(social_security_no, 'Social Security Number'::character varying) (3 rows) select conname, consrc from pg_catalog.pg_constraint where contype = 'c' and conrelid = 'person_change_history'::regclass; conname | consrc ---+-- person_social_security_no | check_pattern(social_security_no, 'Social Security Number'::character varying) person_name_check | ((last_name IS NOT NULL) OR (first_name IS NOT NULL)) person_e_mail_address | check_pattern((e_mail_address)::character varying, 'Internet E-Mail Address'::character varying) (3 rows) AFAICS from looking at the 7.3 pg_dump source, it should suppress any constraint on person_change_history that looks identical to one of the parent table's constraints in this query. Interesting. The consrc column values differ in that the explicit schema qualification on the function calls is missing for the descendent table. So, you think maybe if I remove the explicit schema qualification from the function calls in the constraint declarations on the person table that that might fix it? Yup! That does it! Thanks for your help. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Preserving data after updates
Berend Tober [EMAIL PROTECTED] writes: Interesting. The consrc column values differ in that the explicit schema qualification on the function calls is missing for the descendent table. So, you think maybe if I remove the explicit schema qualification from the function calls in the constraint declarations on the person table that that might fix it? Yup! That does it! OK. This is a variant of the old problems that we had with relying on consrc to dump constraints. pg_dump hasn't done that for awhile, so I expect the problem is gone in more recent releases. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Preserving data after updates
Tom Lane wrote: What do you get from select conname, consrc from pg_catalog.pg_constraint where contype = 'c' and conrelid = 'person'::regclass; conname | consrc ---+- person_e_mail_address | public.check_pattern((e_mail_address)::character varying, 'Internet E-Mail Address'::character varying) person_name_check | ((last_name IS NOT NULL) OR (first_name IS NOT NULL)) person_social_security_no | public.check_pattern(social_security_no, 'Social Security Number'::character varying) (3 rows) select conname, consrc from pg_catalog.pg_constraint where contype = 'c' and conrelid = 'person_change_history'::regclass; conname | consrc ---+-- person_social_security_no | check_pattern(social_security_no, 'Social Security Number'::character varying) person_name_check | ((last_name IS NOT NULL) OR (first_name IS NOT NULL)) person_e_mail_address | check_pattern((e_mail_address)::character varying, 'Internet E-Mail Address'::character varying) (3 rows) AFAICS from looking at the 7.3 pg_dump source, it should suppress any constraint on person_change_history that looks identical to one of the parent table's constraints in this query. Interesting. The consrc column values differ in that the explicit schema qualification on the function calls is missing for the descendent table. So, you think maybe if I remove the explicit schema qualification from the function calls in the constraint declarations on the person table that that might fix it? Yup! That does it! Thanks for your help! But now, however, when restoring from the pg_dump output the script gets hung up over the fact that when the CREATE TABLE statements are executed the raw script can't find the check_pattern function, since it is declared in the public schema and these application-specific tables are (being tried to be) declared in a different schema. That is, the pg_dump output has lots of SET search_path = public, pg_catalog; and SET search_path = paid, pg_catalog; statements sprinkled throughout, and when a table is declared having the check_pattern function call constraint after the latter statement, then the function can't be found. I had to manually edit the pg_dump output script search path statements to read SET search_path = paid, public, pg_catalog; in order to make this all work right. Again, too much manual editing to tolerate for disaster recovery and for my frequent refresh of DEV and QAT from PRD for development and testing purposes. Now what, oh most wise one? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Locale C?
CSN wrote: I'm installing PG8 for Windows and the default locale is C in the installer. What is locale C? No locale. :) Sincerely, Joshua D. Drake CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Inherited constraints and search paths (was Re: [GENERAL] Preserving data after updates)
Berend Tober [EMAIL PROTECTED] writes: Now what, oh most wise one? OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. This creates a problem because reverse-listing of the constraints varies depending on what the search path is. An example in CVS tip is: create function foo(text) returns bool as 'select true' language sql; create table t1(f1 text constraint c1 check (foo(f1))); create schema s1; create table s1.t2() inherits(public.t1); pg_dump yields this: SET search_path = public, pg_catalog; CREATE TABLE t1 ( f1 text, CONSTRAINT c1 CHECK (foo(f1)) ); ... SET search_path = s1, pg_catalog; CREATE TABLE t2 (CONSTRAINT c1 CHECK (public.foo(f1)) ) INHERITS (public.t1); It's the same constraint, but the different reverse-listing fools pg_dump into assuming that it's different. At the moment I'm not seeing any really nice way to fix this. A short-term workaround is to hack pg_dump so that it doesn't compare the constraint expressions at all, but just assumes that a child table's constraint is the same as the parent's if the constraint name matches. You can of course break this by manually dropping the child constraint and creating a different one of the same name --- but does anyone do that in practice? (Note: the code in pg_dump seems to think that there is something special about constraint names beginning with '$', but in quick tests I don't see the system generating constraint names of that kind as far back as 7.0, which is the oldest server version pg_dump now claims to support. So I think that is long-dead code, and that a comparison of constraint names is probably sufficient in practice.) It can be argued that we should actually prohibit dropping inherited constraints, which'd eliminate that problem. I seem to recall that this has come up before and we explicitly decided against making such a restriction ... but given that a dump/restore will cause the inherited constraint to come back anyway, it can hardly be claimed that we really support dropping them. Comments anyone? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Image storage questions
Guy Rouillier wrote: Joshua D. Drake wrote: External storing is useful but I prefer LO because all my data (binary and meta) is all in the same place for management. But if that's a big L in LO, performance and maintenance will be negatively affected, perhaps significantly. How? The DBMS will have to scan over all that large binary data to extract text or numeric data. Ahhh now I see, are you expecting to be able to query your LOs? We only use Large Objects as a storage mechanism. And backups will copy that static binary data repeatedly. Not unless you tell it to. If you don't pass the -b option you are not going to end up backing up your large objects anyway. For those reasons, if I'm storing very large objects, like images, that I know I'll never search or update, I prefer to manage them externally. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
hi all, reading here, it seems builds on 'virgin' Tiger are behaving themselves ... just fyi fwiw, a pgsql v803 build on OSX 10.4.1 is also very nicely tolerant of a /usr/local mod'd system, resulting in: % otool -L /usr/local/pgsql/bin/postgres /usr/local/pgsql/bin/postgres: /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 88.0.0) /usr/local/ssl/lib/libssl.0.9.7.dylib (compatibility version 0.9.0, current version 0.9.7) /usr/local/ssl/lib/libcrypto.0.9.7.dylib (compatibility version 0.9.0, current version 0.9.7) /Library/Frameworks/Tcl.framework/Versions/8.5/Tcl (compatibility version 8.5.0, current version 8.5.0) /usr/local/lib/libreadline.5.0.dylib (compatibility version 5.0.0, current version 5.0.0) /usr/local/lib/libintl.3.dylib (compatibility version 8.0.0, current version 8.3.0) /usr/lib/libpam.1.dylib (compatibility version 1.0.0, current version 1.0.0) /usr/lib/libz.1.dylib (compatibility version 1.0.0, current version 1.2.2) /usr/lib/libresolv.9.dylib (compatibility version 1.0.0, current version 365.0.0) /usr/lib/libmx.A.dylib (compatibility version 1.0.0, current version 92.0.0) where env: Mac OSX 10.4.1 TclTkAquaBI-8.4.9.1.dmg Berkeley-DB v4.3.28 w/ strong crypto Gettext v0.14.4 % perl -V Summary of my perl5 (revision 5 version 8 subversion 6) configuration: Platform: osname=darwin, osvers=8.1.0, archname=darwin-thread-multi-2level uname='darwin devbox 8.1.0 darwin kernel version 8.1.0: tue may 10 18:16:08 pdt 2005; root:xnu-792.1.5.obj~4release_ppc power macintosh powerpc ' Characteristics of this binary (from libperl): Compile-time options: MULTIPLICITY USE_ITHREADS USE_LARGE_FILES PERL_IMPLICIT_CONTEXT Built under darwin Compiled at May 18 2005 22:18:53 %gcc --version powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 20041026 (Apple Computer, Inc. build 4061) % glibtool --version ltmain.sh (GNU libtool) 1.5.18 (1.1220.2.245 2005/05/16 08:55:27) % automake --version automake (GNU automake) 1.9.5 % autoconf --version autoconf (GNU Autoconf) 2.59 % openssl version OpenSSL 0.9.7g 11 Apr 2005 and, libreadline (v5.0) is picked up from my external build: -r-xr-xr-x 1 root staff 416616 May 18 11:12 /usr/local/lib/libreadline.5.0.dylib lrwxr-xr-x 1 root staff 21 May 18 11:12 /usr/local/lib/libreadline.dylib - libreadline.5.0.dylib rather than from Tiger's native link to libedit: lrwxr-xr-x 1 root wheel 13 May 10 09:40 /usr/lib/libreadline.dylib - libedit.dylib and building as: cd /usr/ports/postgresql-8.0.3 ln -sf /usr/include/pam /usr/include/security perl -pi -e 's/AC_PROG_RANLIB/AC_PROG_LIBTOOL/g' /usr/ports/postgresql-8.0.3/configure.in setenv CPPFLAGS -I/usr/local/ssl/include -I/Library/Frameworks/Tcl.framework/Headers -I/usr/local/include setenv LDFLAGS -bind_at_load -ldl -L/usr/local/ssl/lib -lssl -lcrypto -F/Library/Frameworks -framework Tcl -L/usr/local/lib -lreadline glibtoolize --force --copy aclocal -I config autoconf ./configure \ --prefix=/usr/local/pgsql \ --sysconfdir=/var/Settings/PgSQL \ --localstatedir=/var/Process \ --with-docdir=/var/Documentation/html/PgSQL \ --mandir=/var/man \ --enable-shared --disable-static \ --disable-debug --enable-cassert \ --with-template=darwin \ --with-pgport=5432 \ --enable-thread-safety \ --with-maxbackends=1024 \ --with-perl \ --with-java \ --with-pam \ --with-python \ --with-rendezvous \ --with-openssl \ --with-includes=/usr/local/ssl/include /usr/local/include \ --with-libraries=/usr/local/ssl/lib /usr/local/lib \ --with-tcl \ --with-tclconfig=/Library/Frameworks/Tcl.framework \ --with-tkconfig=/Library/Frameworks/Tk.framework \ --enable-nls make make install cheers, richard ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
OpenMacNews [EMAIL PROTECTED] writes: reading here, it seems builds on 'virgin' Tiger are behaving themselves ... The short answer seems to be when you update to 10.4, don't forget to update to Xcode 2 as well. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
hi tom, OpenMacNews [EMAIL PROTECTED] writes: reading here, it seems builds on 'virgin' Tiger are behaving themselves ... The short answer seems to be when you update to 10.4, don't forget to update to Xcode 2 as well. regards, tom lane does it make sense to have configure check for min req'ts of xcode2 if os = v10.4? cheers, richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] bulk loader
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Hrishikesh
Deshmukh) belched out:
Is there a bulk loader in postgresql with which one can read in say
a tab delimited format text file. Before one does all one has to do is
create the table with text file column names as attributes, once it is
on DBMS world it will be a simple table (non-relational)
There is the built in COPY command which can do this sort of thing.
Jan Wieck wrote a load tool that does a more sophisticated job of
slicing up the data. Look at pgFoundry.org for pgloader. There are
thoughts of trying to make this work much like Oracle's SQL*Loader
product.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/postgresql.html
FLORIDA: We've been Gored by the bull of politics and we're Bushed.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
OpenMacNews [EMAIL PROTECTED] writes: The short answer seems to be when you update to 10.4, don't forget to update to Xcode 2 as well. does it make sense to have configure check for min req'ts of xcode2 if os = v10.4? No, I don't think so. There is such an astonishing variety of ways to break your system, on so many different platforms, that we cannot hope to check them all. If this failure were Postgres-specific then yes, but the previous report is that it breaks lots of things. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] numeric precision when raising one numeric to another.
At CONNX, we just do 100 digits using qfloat (about 104 actually). Internally, all math is done using this type. Then we convert to the smaller types [or character types] as requested. I don't think that there is any business need for more than that. A package like Maple might need to worry about it, or a theoretical mathematician looking for patterns in digits or something like that. But you can't please everybody. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Martijn van Oosterhout Sent: Thursday, May 19, 2005 2:48 PM To: Dann Corbit Cc: Alvaro Herrera; John Burger; pgsql-general@postgresql.org Subject: Re: [GENERAL] numeric precision when raising one numeric to another. On Thu, May 19, 2005 at 02:25:58PM -0700, Dann Corbit wrote: Hmmm I underestimated. pow(9.9,9.9) = Yeah, a number with x digits raised to the power with something y digits long could have a length approximating: x * (10^y) digits So two numbers both 4 digits long can have a result of upto 40,000 digits. You're only going to be able to them represent exactly for cases where y is small and integer. What's a meaningful limit? Do we simply say, you get upto 100 digits and that's it? Or an extra parameter so you can specify directly? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.0.3 build error on Mac OS X 10.4
OpenMacNews [EMAIL PROTECTED] writes: The short answer seems to be when you update to 10.4, don't forget to update to Xcode 2 as well. does it make sense to have configure check for min req'ts of xcode2 if os = v10.4? No, I don't think so. There is such an astonishing variety of ways to break your system, on so many different platforms, that we cannot hope to check them all. If this failure were Postgres-specific then yes, true enuf in general ... but _this_ proposed check would really be just a 'did you do a complete OS install (including XCode 2)?' check, rather than any one particular 'widget' etc. have no idea HOW to check for XCode2 install other than looking in /Library/Receipts ... then again, a comment i the INSTALL/README would/should go just as far! but the previous report is that it breaks lots of things. all in all, it's been relatively well behaved for me so far ... keeping fingers crossed! cheers, richard ---(end of broadcast)--- TIP 8: explain analyze is your friend
