Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Chris Travers wrote: Here is a paper I have written for the purposes of providing some additional educational material for the MySQL crowd. Here's my contribution: Why I choose PostgreSQL (PostgreSQL in 21 Seconds) I choose referential integrity, meaning my lookups always work. I choose stored procedures, meaning all my developers - Windows or Unix, Perl, C++ or Java - can access the database in the same way, using the same locking, with the same checking and cleaning I choose subselects and outer joins, which allow me to build complex queries to get exactly the information I want from the database, rather than wasting my time munging data in my code. Even better, I can put those common queries into stored procedures, so other developers can get the same results as I do! I choose partial indexes, so lookups on NULL fields are just as fast if not faster. I choose a user community that believes getting the results right is more important than getting them quickly. I choose getting the right results, right now! I choose funny capitalisation, and a name that can't be pronounced! I choose PostgreSQL. ---(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] Transaction Question
Hi Scott Firstly, your name rings a bell. I'm sure you're a 4D developer - I was one too (once, but no more sadly)... You imply that it should be possible to create an embedded C function that can access the DB in its own thread, and therefore its own transaction. Is there a document that would explain how this is done? Also do you know if you can embedded java functions - I'm more comfortable with java than C these days? I think that for this problem, I will use SEQUENCE objects who name is stored within the WDVolume table. It will allow me to avoid the row level lock bottleneck, and my counters won't be rolled back on a transaction rollback. There are only going to be less than 100 Volumes - so this approach will be fine. I'm amazed that nested transactions are still not yet supported. Postgres is a mature product (from my understanding), and I can see loads of messages asking about nested transaction support. Thanks for your feedback. John Scott Ribe said: You could do something with threads on the backend, invisible to your Java middleware. I don't have enough experience to feel confident about trying to evaluate the pros and cons of (possibly) different ways of doing this. But given that you can write functions in C and load them into Postgres so that they can be called from plpgsql, I think you could in essence add to Postgres a function which when called would hand off the sequence load update on a separate thread/connection, wait for its commit and completion, and return the value. Of course you still have to be careful about concurrency issues with this approach, so that you don't wind up with the 2 threads deadlocked. That may well strike you as a gross hack. I don't particularly like it either, but I think it would get the job done without requiring any changes to your current code base except for the rewrite of GetVolumeFileReference. BTW, in reference to other suggestions: I believe that a sequence name is indeed just a string, so you can build the name and pass it to sequence functions on the fly; I know that sequences do not roll back, once a value is issued it is burned regardless of whether the enclosing transaction commits or not. So you should be able to have a trigger that on insert of a WDVolume row creates a corresponding sequence, then use that sequence within GetVolumeFileReference. Whether this is a good idea depends I'm sure on how many WDVolumes you'll have. I feel confident that dozens or hundreds would be no problem; thousands I don't have any idea; millions I wouldn't try. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(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 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] Transaction Question
On Thursday 04 December 2003 07:57, John Sidney-Woollett wrote: I'm amazed that nested transactions are still not yet supported. Postgres is a mature product (from my understanding), and I can see loads of messages asking about nested transaction support. Loads of messages asking for many other features too ;-) It will get done sooner or later - the more assistance people offer the core the sooner it'll be. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] postgresql locks the whole table!
Help! I have a table that multiple processes must be able to write to concurrently. However, it for some reason gets locked in exclusive mode. I narrowed it down to one SQL statement + some weirdness with foreign keys. To debug this, I opened two psql sessions and typed in the sql statements manually. Here is the situation: CREATE TABLE take2 ( id serial not null, timestamp timestamp NOT NULL DEFAULT now(), description text, iteration smallint, asset_id integer, -- FOREIGN KEY (asset_id) REFERENCES public.asset (id), -- ON UPDATE CASCADE ON DELETE CASCADE, primary key(id) ); (notice that the foreign key statement is commented out). Just to make sure I am not causing excessive locking unintentionally, I did set transaction isolation level read committed in both psql shells (default was serializable). Now I type the following commands: shell 1: 1. BEGIN 2. insert into take2 values(default, 'now()', 't1', 1, 1); shell 2: 1. BEGIN 2. insert into take2 values(default, 'now()', 't2', 1, 1); this works. However, if I uncomment the foreign key statement and recreate the table, then the second shell blocks on the insert statement. As soon as the first transaction is either committed or rolled back, the insert statement goes through. My question is why??? The two insert operations do not conflict with each other (at least not in the real-world situation). Also, why does the foreign key make a difference? looking at pg_locks, I see the following: relation | database | transaction | pid | mode | granted --+--+-+---+--+- 39356 |34862 |NULL | 18671 | AccessShareLock | t 39356 |34862 |NULL | 18671 | RowExclusiveLock | t NULL | NULL |9914 | 18671 | ExclusiveLock| t 39354 |34862 |NULL | 18671 | AccessShareLock | t 34886 |34862 |NULL | 18671 | AccessShareLock | t 34886 |34862 |NULL | 18671 | RowShareLock | t 16759 |34862 |NULL | 18671 | AccessShareLock | t (7 rows) Where does the ExclusiveLock come from? What is being locked? It is critical for us to run multiple transactions concurrently -- in fact that was one of the reasons for choosing PostgreSQL over MySQL. There are a lot of file system operations and other processing that need to happen along side the DB transaction. Those things take a long time, so there is typically up to a 5-minute span between BEGIN and COMMIT. We cannot block the production floor for 5 minutes when a user tries to run a transaction, so as a temporary fix, we got rid of the begin/commit. But obviously we would rather not lose the atomicity. So, in summary: why does PostgreSQL lock the entire table? what can we do about it? This was tested on PostgreSQL 7.4.0 and 7.3.2. thanks in advance, Eugene __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Equivalent to DBMS_JOB
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Alvaro Herrera) wrote: On Sun, Nov 30, 2003 at 12:19:34AM -0500, Arturo Pérez wrote: Does postgreSQL offer anything similar? And please don't say use cron. Cron has issues that I'd like to avoid. Huh, just curious: what issues does cron have? It does have the demerit that it isn't terribly transactional. And the tools for managing jobs are likewise not much transactional. -- let name=cbbrowne and tld=ntlug.org in name ^ @ ^ tld;; http://www.ntlug.org/~cbbrowne/spiritual.html Rules of the Evil Overlord #35. I will not grow a goatee. In the old days they made you look diabolic. Now they just make you look like a disaffected member of Generation X. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Shared hosting configuration advice requested
Greetings to pgsql-general list members, I am configuring a server for my hosting company on which I would like to offer postgres shared hosting as an option. Postgres has been installed already but I'm having difficulty sequestering server access by shared accounts and generally could use some help in setting this up properly. I would greatly appreciate any advice from list members who might have experience doing this on a RH9/Apache server, this one having Cpanel administrative software installed to complicate matters. So as not to instigate commercial trespass on this list I would welcome private responses at dholeman ~at~ alpheta ~dot~ net. Many thanks in advance, Don Holeman ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Teach Yourself PostgreSQL book
Oops! [EMAIL PROTECTED] (Bret Busby) was seen spray-painting on a wall: In searching on the Internet, I found various references to the book Teach Yourself PostgreSQL In 21 Days, written by Chris Smith, published by SAMS, December 2002 (from the Barnes and Noble web site), ISBN 0672324830, 600 pages (from http://202.113.6.251/cat/books/0672324830.html). Late 2002 was not a good time for technical book publishing. WROX went out of business at about that time, and it would be unsurprising for the project to have been cancelled before the book reached general release. (I had a book Fail Utterly at about that time, when WROX went under...) -- If this was helpful, http://svcs.affero.net/rm.php?r=cbbrowne rate me http://www3.sympatico.ca/cbbrowne/publications.html A VAX is virtually a computer, but not quite. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] transaction in progress
Is there a convenient way to tell in postgres if a transaction has been started or not? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] EMBEDDED VACUUM SOLVED!!!!!!!!!!!
Michael, The -t option for ecpg DOES work like a charm THANK YOU!! I am also posting this to GENERAL, I noticed 2-3 other people who have been having the same exact problem! Thanks Again, Lynn Quoting Michael Meskes [EMAIL PROTECTED]: On Sun, Nov 30, 2003 at 11:08:51PM -0700, [EMAIL PROTECTED] wrote: Here is my previous post... Please let me know what you find. Thanks, I tried reproducing your problem with pgsql 7.4 but couldn't. The only thing I changed was that I ran ecpg with option -t and your test program works like a charm. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Storing passwords
Can Postgres JDBC driver encrypt a password before sending and inserting it into the password column? -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2003 1:32 PM To: Oleg Lebedev Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Storing passwords Oleg Lebedev writes: My application needs to store user names and passwords in the database via JDBC connection. What is the right way to do this? One table, one column for the name, one column for the password. What should be the database type of the password column? text or bytea, depending on how you encrypt it. How do I encrypt the password before sending it to the database? Check out contrib/pgcrypto. What other database settings need to be enabled for this to work? None. -- Peter Eisentraut [EMAIL PROTECTED] * This e-mail may contain privileged or confidential material intended for the named recipient only. If you are not the named recipient, delete this message and all attachments. Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited. We reserve the right to monitor e-mail sent through our network. * ---(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] embedded postgresql + C++ IDE
[sNip] Thanks for all the answers. It seems if I wish to make my idea into reality I will have to invest in the Profesional Microsoft Visual C++ studio. [sNip] What about OpenWatcom? Open Watcom - Portable Compilers and Tools http://www.openwatcom.org/ It's a C++ compiler, linker, etc., that's now open source and free. It used to be very expensive and has been around for a long time, but previously known as just Watcom before it was upgraded to open source. -- Randolf Richardson - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Storing passwords
You can create MD5 or SHA-1 digests with java.security.MessageDigest. They would be stored as text On Dec 1, 2003, at 4:01 PM, Oleg Lebedev wrote: Can Postgres JDBC driver encrypt a password before sending and inserting it into the password column? -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2003 1:32 PM To: Oleg Lebedev Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Storing passwords Oleg Lebedev writes: My application needs to store user names and passwords in the database via JDBC connection. What is the right way to do this? One table, one column for the name, one column for the password. What should be the database type of the password column? text or bytea, depending on how you encrypt it. How do I encrypt the password before sending it to the database? Check out contrib/pgcrypto. What other database settings need to be enabled for this to work? None. -- Peter Eisentraut [EMAIL PROTECTED] * This e-mail may contain privileged or confidential material intended for the named recipient only. If you are not the named recipient, delete this message and all attachments. Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited. We reserve the right to monitor e-mail sent through our network. * ---(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 Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] query and pg_dump problem on my postgresql 6.5.3/Redhat
I would say you're loosing your disk drive. Have you lately checked for bad blocks? Jan wrote: Help! A few days ago, my php page began to complain this: -- Warning: PostgresSQL query failed: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. -- The SQL string in php page is: -- $sql.='Select news_id,title,summary,publish_time,is_html,if_use_url,url,news_pri '; $sql.='From newses N,classes C '; $sql.=Where N.class_id = C.class_id AND C.classname='$class' ; $sql.='Order by publish_time Desc,news_id Desc Limit '.$Nlimit; -- NOTE: I'm on Redhat 6.2 with Postgresql 6.5.3, the database named news, and the table is newses, looks like this (dumped from pg_dump -s -t newses news): CREATE TABLE newses ( news_id int4 DEFAULT nextval ( 'newses_news_id_seq' ) NOT NULL, title character varying(100) NOT NULL, class_id int4 NOT NULL, summary text DEFAULT '', user_id int4 NOT NULL, url character varying(100), img_url character varying(100), publish_time date NOT NULL, if_show_news bool DEFAULT bool 'f' NOT NULL, if_use_url bool DEFAULT bool 'f' NOT NULL, is_html bool DEFAULT bool 'f' NOT NULL, view_count int4 DEFAULT 0 NOT NULL, news_pri int4); CREATE UNIQUE INDEX newses_pkey on newses using btree ( news_id int4_ops ); This table has 243 records, the max news_id is 253. Later I found queries like these fails in psql: select news_id,title from newses order by news_id desc limit 10; select count(news_id) from newses; But thess works fine: select * from newses where news_id 300; select count(*) from newses where news_id 300; select count(news_id) from newses where news_id 300; A simple rule is if I'm running query over the whole table without condition, I get same error message mentioned above. I thought my postgresql should be patch or upgrade, so I began to backup the database on it. But I found that pg_dump sometimes does not work on that very table, and sometimes work with a long long time then error. following are the error message of pg_dump news -t newses -f newses-data.sql: -- pqWait() -- connection not open PQendcopy: resetting connection SQL query to dump the contents of Table 'newses' did not execute correctly. After we read all the table contents from the backend, PQendcopy() failed. Explanation from backend: ''. The query was: 'COPY newses TO stdout; '. -- I read the file(14M) generated and found that after the normally record(91K) there are many character like these: -- \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N 280368896 \N \N 0 \N f f f 0 0 280368896 \N \N 0 \N f f f 0 0 280368896 \N \N 0 \N f f f 0 0 -- And end with -- \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \. -- It is my nightmare now for I can't get back my data. I googled around with no luck. Anyone help me to get back the data and tell me what was going on? Yours Wind Wood [EMAIL PROTECTED] 2003-12-04 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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] pg_hba.conf change in 7.4
Title: Re: [GENERAL] pg_hba.conf change in 7.4 Hi Bruce, I wonder if there is any recommendation to this ? Is there a way to configure PostgreSQL to not use IPv6 ? We are also wonder if there is a version of Ident server that the PostgreSQL community knows that will work with IPv6. Thanks. Gan At 11:37 am -0600 2003/11/20, Seum-Lim Gan wrote: Hi Bruce, We are using Sun Solaris 9 on Sparc. uname -a : SunOS test01 5.9 Generic_112233-04 sun4u sparc SUNW,Ultra-80 Gan At 12:29 pm -0500 2003/11/20, Bruce Momjian wrote: I think what happens is that when we listen on IPv4 and IPv6, that all connections get IPv6. What OS are you using? --- Seum-Lim Gan wrote: Hi Bruce, Thanks for the info. I captured the netstat output below. Looks like there is a bunch of IPv4 being used. Any idea how this can be resolved ? Thanks. Gan UDP: IPv6 Local Address Remote Address State If - - -- - localhost.35847 localhost.35847 Connected TCP: IPv4 Local Address Remote Address Swind Send-Q Rwind Recv-Q State - -- - -- --- localhost.32906 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32906 49152 0 49152 0 ESTABLISHED localhost.32908 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32908 49152 0 49152 0 ESTABLISHED localhost.32910 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32910 49152 0 49152 0 ESTABLISHED localhost.32911 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32911 49152 0 49152 0 ESTABLISHED localhost.32913 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32913 49152 0 49152 0 ESTABLISHED localhost.32915 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32915 49152 0 49152 0 ESTABLISHED localhost.32917 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32917 49152 0 49152 0 ESTABLISHED localhost.32919 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32919 49152 0 49152 0 ESTABLISHED localhost.32920 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32920 49152 0 49152 0 ESTABLISHED localhost.32922 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32922 49152 0 49152 0 ESTABLISHED localhost.32923 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32923 49152 0 49152 0 ESTABLISHED localhost.32924 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32924 49152 0 49152 0 ESTABLISHED localhost.32926 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32926 49152 0 49152 0 ESTABLISHED localhost.32927 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.32927 49152 0 49152 0 ESTABLISHED localhost.33086 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.33086 49152 0 49152 0 ESTABLISHED localhost.33087 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.33087 49152 0 49152 0 ESTABLISHED localhost.50882 localhost.14502 49152 0 49152 0 ESTABLISHED localhost.14502 localhost.50882 49152 0 49152 0 ESTABLISHED localhost.50883 localhost.14500 49152 0 49152 0 ESTABLISHED localhost.14500 localhost.50883 49152 0 49152 0 ESTABLISHED At 12:11 pm -0500 2003/11/20, Bruce Momjian wrote: Seum-Lim Gan wrote: Hi, In 7.4, I noticed there is this ::1 and : (x8 of them) for IPv6. I looked at the documentation and there is nothing that says what the ::1 is for. The ::1 is a IPv6 shorthand for 127.0.0.1 (localhost). Commenting out that line will prevent access to PostgreSQL from psql unless I put trust for that line. This is what I had in 7.3.4: host all all 127.0.0.1 255.255.255.255 ident pspmap local all all password host all all 0.0.0.0 0.0.0.0 reject But in 7.4, it does not work anymore. It seems to want ::1 to be somewhere. If I change the line with ::1 from trust to ident pspmap, it complains that the user cannot be found. But it is in the pspmap. Message fromm psql: Seems you have an OS that makes all connections IPv6, even IPv4 ones. That is why we had to have that line in there. Seems ::1 controls your local connections on that platform. Some platforms have distinct IPv4 and IPv6 connections, so we have to include both lines in the file. Right now, I have it set to trust to work around. Any idea what to do about this ? host all all 127.0.0.1 255.255.255.255 ident pspmap local all all password host all all 0.0.0.0 0.0.0.0 reject # IPv4-style local connections: #host all all 127.0.0.1 255.255.255.255 trust # IPv6-style local connections: host all all ::1 ::::::: trust Yea, that's about it. My guess is that nothing is coming in via IPv4 on
Re: [GENERAL] Transaction Question
On Thu, 4 Dec 2003 17:56:33 - (GMT), John Sidney-Woollett [EMAIL PROTECTED] wrote: It would be nice if nested transactions could be (optionally) decoupled from their enclosing transaction. While I see your point how this could be useful for certain use cases, unfortunately I don't have any idea how it could be implemented with respect to MVCC. Servus Manfred ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql locks the whole table!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - -- Dr NoName [EMAIL PROTECTED] wrote: Help! you may look in the archives; one day ago there was the same question. Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.org/ ** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html ** ODEM.org-Tour: http://tour.odem.org/ ** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQE/z51FOndlH63J86wRAooRAKCDV1tex7pn1XBDaphLP2ub5UBMEACfUPcg ewedvwMydauojzsrwQXddfo= =mLXm -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Pronouncing PostgreSQL
Tempest in a teapot. It's just trivia IMO. But, YMMV. -Original Message- From: John Wells [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2003 6:51 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Pronouncing PostgreSQL Devrim GUNDUZ said: BTW, this mp3 was on the old web site, AFAIR. Seems like there should be a *prominent* link on the site describing just how to pronounce it. From a marketing standpoint, having everyone uncomfortably stumbling over themselves while struggling to pronounce it in meetings with management is not a good way to gain widespread adoption, collaboration, etc. John ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Making a tree with millions and millions of dynamic
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, - -- Joe \Nuke Me Xemu\ Foster [EMAIL PROTECTED] wrote: Here's an alternative which may not perform very well but may still be better than risking a full table-scan... I use exactly this method in a forum software, and it performs VERY good. I tested it with about a million of rows, and it is really very fast. Even with deep trees (1000 sub branches). The only difference is that I use a base 255 encoded text column instead of only 0-9. But attention: the character set must be ASCII (ordering!) ... I want to change this to bytea to avoid base255 encoding and the character set problems, but there are still some Bugs with bytea and the like operator. :-( Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.org/ ** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html ** ODEM.org-Tour: http://tour.odem.org/ ** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (FreeBSD) iD4DBQE/z59nOndlH63J86wRAsrEAJ9OjO5fXhnw2mmLoB7YNHJFYO/X8QCXc31M FWdV8T92N3kzctSgOOkVMw== =Uwtm -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] query and pg_dump problem on my postgresql 6.5.3/Redhat 6.2
On Thursday 04 December 2003 14:55, wrote: Help! A few days ago, my php page began to complain this: -- Warning: PostgresSQL query failed: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. [snip] NOTE: I'm on Redhat 6.2 with Postgresql 6.5.3, the database named news, and the table is newses, looks like this (dumped from pg_dump -s -t newses news): One of the developers will probably be able to help, but bear in mind many are in the USA/Canada and so you might have time-zone delays. It will be suggested you upgrade to 7.3.5 or 7.4.0 as soon as possible. That might mean upgrading from RedHat 6.2 as well. At present: 1. Dump all the other tables, if you can 2. Stop PostgreSQL 3. make a file backup of /var/data (or wherever your data is stored) OK - now at least you know things can't get any worse. In psql you can use \a to set unaligned output and \o filename to output query results to a file. You can then try SELECT * FROM newses WHERE news_id BETWEEN 1 AND 100, then 101-200 etc. This should let you recover a great deal of your data if only one disk-block is damaged. From what you say, you should be able to recover your table's data. Then, I'd recreate the database from your dumps. But I found that pg_dump sometimes does not work on that very table, and sometimes work with a long long time then error. This sounds like either a disk or memory error. I'd guess disk. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Money data type in PostgreSQL?
Christopher Browne wrote: But to get the pennies right on a $10,000 USD transaction converted into GBP (UK Pounds), you need all the official precision that there is. And if your calculation is off by 4 cents, some of those accounting folk are liable to thrash you mercilessly over it. If you get calculations WRONG, they get really uncomfortable, and want to know why. What I have done is store the currency amounts as bigints, at the same precision defined for the currency (ie cents for dollars, pence for pounds, etc). This guarantees that you don't get any rounding errors when storing the figures as a floating point type. When manipulating the numbers, I use Java BigDecimals, which don't lose any precision either, and convert back to bigints to store in the database. YMMV. Regards, Graham -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] bug in 7.4
Alexander S [EMAIL PROTECTED] writes: Data in mytable doesn t order in alphabetical russian order It won't order in russian order unless the database was initdb'd with a russian locale. For db in KOI8 encoding - all looks right. The encoding is irrelevant. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Invalid page header
Looks bad. Have you got backups. Seriously! REINDEX works on system indexes but you have to drop to single user mode in postgres to do it. Check out the -P option in the manpage. Good luck! Hope this helps, On Thu, Dec 04, 2003 at 12:54:07PM -0700, Ed L. wrote: I have a server with 20 pgsql clusters on it. I am seeing a slew of errors like the following across 5 of these clusters, all running 7.3.4 on Linux with kernel 2.4.18-4bigmem: ERROR: Invalid page header in block N of R N and R vary among block numbers and relation names, respectively. Looking at the archives, it sounds like a possible hardware issue, and we're investigating that. In the meantime, every query that attempts to access those blocks errors out. Taking the server down or losing data is very expensive, so I'm looking for some short-term band-aids to re-enable full production access and recover data while we sort out the hardware issues. When the corrupted block is within a user index, I've been able to fix the corruption by dropping and recreating the index. Not sure what I can do for user tables yet. Preparing to run e2fsck/badblocks after I'm convinced there's nothing more to salvage. Is there anything I can do for invalid page header in a block of pg_depend_reference_index? Other suggestions? TIA. Ed ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ All that is needed for the forces of evil to triumph is for enough good men to do nothing. - Edmond Burke The penalty good people pay for not being interested in politics is to be governed by people worse than themselves. - Plato pgp0.pgp Description: PGP signature
Re: [GENERAL] Misplaced modifier in Postgresql license
On Wed, 2003-12-03 at 17:06, Bruce Momjian wrote:
Tom Lane wrote:
Chris Travers [EMAIL PROTECTED] writes:
Also, I am a little confused by Tom's statement that we don't have the right
to modify the license.
I don't see what's confusing about it. Our implicit contract with
contributors (past and present) is that we'd distribute their work under
the terms of the same license they saw when they contributed. Altering
the license without their agreement is breaking faith.
All of the arguments about license changes have been gone over in great
detail in the archives (I think the last major go-round on the topic was
in the summer of 2000). No one who has been around long enough to
remember those flame wars is interested in re-opening the topic. Not
even just to move a comma.
What we could do is add a blurb on our web site or in the FAQ clarifying
this issue.
Oh, you mean like this paragraph
The above is the BSD license, the classic open-source license. It has
no restrictions on how the source code may be used. We like it and have
no intention of changing it.
Which you added to the FAQ damn near two years ago !?!
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/doc/FAQ.diff?r1=1.139r2=1.140
Quite frankly I think the lawyer they spoke with was... well, this is a
family newsgroup so let's just say I don't feel his opinion is very
credible. It would have been very easy for them to indemnify themselves
had they wanted to, to not even try to sort this out speaks of some
other agenda on their part IMHO.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] Making a tree with millions and millions of dynamic
Alvar Freude wrote: I want to change this to bytea to avoid base255 encoding and the character set problems, but there are still some Bugs with bytea and the like operator. :-( Be patient, I'm working on it. Apparently you are the first person to ever really try to *use* the like operator for bytea, because the bug has been there since the feature was originally committed. Joe ---(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] Making a tree with millions and millions of dynamic nodes
Christian Fowler wrote: So it seems Materialized Path is my only option, however I am concerned about LIKE performance for the right hand side of the tree, where the path is 8digits x 6 levels = 48 chars. Should I be concerned? I need split-second real-time performance, and can't imagine it will be as fast the Nested Set arithmatic approach. Perhaps the contrib/ltree-stuff is interesting if you're going to do materialized paths. It's an indexable tree-format for postgresql based on the materialized paths (so it seems at least). I haven't really tested it, but wanted to point it out to you. Regards, Arjen van der Meijden ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] are cursors necessary?
In the program testlibpq.c, these five SQL statements are executed: res = PQexec(conn, BEGIN); res = PQexec(conn, DECLARE myportal CURSOR FOR select * from pg_database); res = PQexec(conn, FETCH ALL in myportal); res = PQexec(conn, CLOSE myportal); res = PQexec(conn, END); Is this just to illustrate how to create transactions and cursors, or is there some material difference between trimming the program down to just: res = PQexec(conn, select * from pg_database); Is there any value in my own query-only programs to declaring the cursor for each search? Many TIA, Mark -- Mark Harrison Pixar Animation Studios ---(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] Triggers, Stored Procedures, PHP. was: Re: PostgreSQL
On Tue, 2 Dec 2003, Randolf Richardson wrote: In dealing with web applications and frontends to database or even just a dynamic web site PHP has every bit the power and ability that Java does and the development time is way down. Uh, how about threads. I know that you don't need them much but it sure would be nice to be able to do background processing. [sNip] PHP doesn't support threads? I've always thought of Forking as overkill where threads are light and elegant. True on some platforms (Windows, Solaris) untrue on others (Linux, possibly BSD) where threads are not much lighter than processes. PHP doesn't have thread support. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] are cursors necessary?
On Thursday 04 December 2003 22:46, Mark Harrison wrote: res = PQexec(conn, BEGIN); res = PQexec(conn, DECLARE myportal CURSOR FOR select * from pg_database); res = PQexec(conn, FETCH ALL in myportal); res = PQexec(conn, CLOSE myportal); res = PQexec(conn, END); Is there any value in my own query-only programs to declaring the cursor for each search? Well - if you want to scroll forward/backward through the resultset, you'd want a cursor. Or, if your client had limited memory and the resultset was large you might want to do so. PG will return all rows at once, so if your SELECT returns 5 million rows you'll use a lot of RAM on the client side. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Money data type in PostgreSQL?
Graham Leggett wrote: Christopher Browne wrote: But to get the pennies right on a $10,000 USD transaction converted into GBP (UK Pounds), you need all the official precision that there is. And if your calculation is off by 4 cents, some of those accounting folk are liable to thrash you mercilessly over it. If you get calculations WRONG, they get really uncomfortable, and want to know why. What I have done is store the currency amounts as bigints, at the same precision defined for the currency (ie cents for dollars, pence for pounds, etc). This guarantees that you don't get any rounding errors when storing the figures as a floating point type. When manipulating the numbers, I use Java BigDecimals, which don't lose any precision either, and convert back to bigints to store in the database. You won't get any rounding errors in NUMERIC either. What people should be concerned of is to find an arbitrary precision package for the frontend programming language they're using. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] are cursors necessary?
Richard Huxton wrote: On Thursday 04 December 2003 22:46, Mark Harrison wrote: res = PQexec(conn, BEGIN); res = PQexec(conn, DECLARE myportal CURSOR FOR select * from pg_database); res = PQexec(conn, FETCH ALL in myportal); res = PQexec(conn, CLOSE myportal); res = PQexec(conn, END); Is there any value in my own query-only programs to declaring the cursor for each search? Well - if you want to scroll forward/backward through the resultset, you'd want a cursor. Or, if your client had limited memory and the resultset was large you might want to do so. PG will return all rows at once, so if your SELECT returns 5 million rows you'll use a lot of RAM on the client side. You really think people would ever want to store more than 640 rows? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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
