Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
[EMAIL PROTECTED] wrote: One of my friend lost data with mysql yesterday.. The machine was taken down for disk upgrade and mysql apperantly did not commit the last insert.. OK he was using myisam but still..:-) It sounds like that is more a problem with improper operating protocols than with the underlying database. No. Problem is machine was shutdown with shutdown -h. It sends sigterm to everybody. A good process would flsuh the buffers to disk before finishing. Mysql didn't on that occasion. Transactions or not, this behaviour is unacceptable for any serious app. Would PG know enough to do a commit regardless of how the database was shut down? A second question is whether doing a commit is what the user or application would always want to have happen, as it could result in a half-completed transaction. Do a shutdown -h on a live database machine with pg. It will gracefully shut itself down. Shridhar ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] "select count(*) from contacts" is too slow!
Christopher Browne wrote: A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] wrote: MySQL can tell you from it's index because it doesn't care if it gives you the right number or not. Under what circumstances would MySQL give the wrong number? It would give the wrong number under _every_ circumstance where there are uncommitted INSERTs or DELETEs. Give them some credit. I just double checked: Using mysql 4.0.14 + innodb and transactions, select count(*) from foo; does not count uncommited INSERTs. Heck, even using myisam, mysql's count(*)'s still accurate, since all INSERTs, etc are autocommitted. -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 12:00pm up 287 days, 3:33, 7 users, load average: 6.93, 6.31, 6.16 pgp0.pgp Description: PGP signature
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
On Wed, Oct 08, 2003 at 07:03:19PM -0500, [EMAIL PROTECTED] wrote: > Would PG know enough to do a commit regardless of how the database was > shut down? A second question is whether doing a commit is what the user > or application would always want to have happen, as it could result in a > half-completed transaction. If an open transaction is not explicitly committed and the client disconnects, it is automatically rolled back. -- Alvaro Herrera () Essentially, you're proposing Kevlar shoes as a solution for the problem that you want to walk around carrying a loaded gun aimed at your foot. (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] "select count(*) from contacts" is too slow!
Ang Chin Han <[EMAIL PROTECTED]> writes: > Heck, even using myisam, mysql's count(*)'s still accurate, since all INSERTs, > etc are autocommitted. That's sort of true, but not the whole story. Even autocommitted transactions can be pending for a significant amount of time. The reason it's accurate is because with mysql isam tables all updates take a table level lock. So there's never a chance to select the count while an uncommitted transaction is pending, even if the update takes a long time. This is simple and efficient when you have low levels of concurrency. But when you have 4+ processors or transactions involving lots of disk i/o it kills scalability. I'm curious how it's implemented with innodb tables. Do they still take a table-level lock when committing to update the counters? What happens to transactions that have already started, do they see the new value? Actually it occurs to me that that might be ok for read-committed. Is there ever a situation where a count(*) needs to represent an old snapshot in read-committed? It has to for long-running selects, but if the count(*) itself is always fast that need should never arise, just shared-lock and read the value and unlock. In order words, imagine if you had every transaction keep a net delta of rows for every table and at commit time locked the entire table and updated the count. The lock would be a point of contention but it would be very fast since it would only have to update an integer with a precalculated adjustment. In read-committed mode that would always be a valid value. (The transaction would have to apply its own deltas I guess.) -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Generating a SQL Server population routine
> In article <[EMAIL PROTECTED]>, > Mike Mascari <[EMAIL PROTECTED]> writes: > >> [EMAIL PROTECTED] wrote: >>> Has some one come up with a similar type script that could be used >>> in a Postgresql database? > >>> The script below was created for a SQLServer database. >>> Thx, >>> -Martin > >> I haven't. But I was wondering if a general purpose tuple-generating >> function, which would be trivial to implement, might be worthwhile in >> PostgreSQL or perhaps added to Joe Conway's tablefunc module. >> Something like: > >> tuple_generator(integer) > >> which returns a set of numbers whose elements are the integer values >> between 1 and the number supplied. > > How about this? > > CREATE OR REPLACE FUNCTION enum (INT) RETURNS SETOF INT AS ' > DECLARE > numvals ALIAS FOR $1; > BEGIN > FOR currval IN 0 .. numvals - 1 LOOP > RETURN NEXT currval; > END LOOP; > RETURN; > END; > ' LANGUAGE 'plpgsql'; > > CREATE OR REPLACE FUNCTION enum (INT, INT) RETURNS SETOF INT AS ' > DECLARE > numvals ALIAS FOR $1; > minval ALIAS FOR $2; > BEGIN > FOR currval IN 0 .. numvals - 1 LOOP > RETURN NEXT minval + currval; > END LOOP; > RETURN; > END; > ' LANGUAGE 'plpgsql'; > > CREATE OR REPLACE FUNCTION enum (INT, INT, INT) RETURNS SETOF INT AS > ' DECLARE > numvals ALIAS FOR $1; > minval ALIAS FOR $2; > maxval ALIAS FOR $3; > BEGIN > FOR currval IN 0 .. numvals - 1 LOOP > RETURN NEXT currval % (maxval - minval + 1) + minval; > END LOOP; > RETURN; > END; > ' LANGUAGE 'plpgsql'; > > Usage: SELECT * FROM enum (numvals [, minval [, maxval]]) > Returns numvals consecutive numbers, beginning with 0 or minval > Wraps around to minval if maxval is reached > Or a little different, with the over-loaded functions relying on the original: CREATE OR REPLACE FUNCTION public.enum(int4) RETURNS SETOF int4 AS ' DECLARE numvals ALIAS FOR $1; BEGIN FOR currval IN 0 .. numvals - 1 LOOP RETURN NEXT currval; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION public.enum(int4, int4) RETURNS SETOF int4 AS ' DECLARE numvals ALIAS FOR $1; minval ALIAS FOR $2; currval RECORD; BEGIN FOR currval IN SELECT minval + enum AS enum FROM enum(numvals) LOOP RETURN NEXT currval.enum; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION public.enum(int4, int4, int4) RETURNS SETOF int4 AS ' DECLARE numvals ALIAS FOR $1; minval ALIAS FOR $2; maxval ALIAS FOR $3; currval RECORD; /* From: Harald Fuchs Date: Wed, October 8, 2003 5:53 To: [EMAIL PROTECTED] tuple_generator(integer) which returns a set of numbers whose elements are the integer values between 1 and the number supplied. Usage: SELECT * FROM enum (numvals [, minval [, maxval]]) Returns numvals consecutive numbers, beginning with 0 or minval Wraps around to minval if maxval is reached */ BEGIN FOR currval IN SELECT * FROM enum(numvals, minval) LOOP RETURN NEXT currval.enum % maxval; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql' VOLATILE; But, as interesting as these look, what would you actually use them for? ~Berend Tober ---(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]
[EMAIL PROTECTED] writes: > We are using Postgresql as the backend database for our upcoming software > application. We would like Postgresql to have the following functionalities : > > 1. Standard Error Codes along with textual error messages including error > codes for errors while dumping/restoring database/s. This will be in 7.4, which is in late beta--should be released in another month or so. If you have a test platform for your application you might want to install 7.4beta4 and see if it works for you. > 2. Native Windows Support. This was postponed until 7.5 or later, so it will be six to eight months before a production release. -Doug ---(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] Updated plPerl with trigger support available
Hello, There is now an updated version of plPerl with trigger support available at the Command Prompt community page. It can be viewed with documentation here: https://www.commandprompt.com/entry.lxp?lxpe=285 Sincerely, Joshua Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Cross database foreign key workaround?
David Busby wrote: List, What are the recommended work arounds for cross database foreign keys? As I understand it transactions are not atomic with the TCL method. I have a situation that requires a master database and then a separate database for every subscriber. Subscribers need read/write to both databases. I chose separate databases because there are 20+ large tables that would require uid/gid columns, indexes and where conditions to separate information by subscriber. I thought that was too much overhead. Should I just use my application to make changes and ensure references that need to take place across databases? Or should I add a uid/gid to all necessary tables, create indexes and update all necessary where clauses? Ideas? What about using schemas? Joe ---(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] Call for Speakers / Presenters
Joshua D. Drake writes: > Master of Ceremonies is a new position that was created by the core (at > least Josh Berkus) > for dealing with speakers. > > There are others, such as Editor-n-chief which is myself, and I am in > charge of soliciting writers > and working with publishers. I find these titles confusing, comical, and presumptuous. There are no "ceremonies"; PostgreSQL is not a circus. And since Jillian is not actually going to (most of) the events she coordinates, she's not the "master", which would be the person that runs the event. As to yourself, are you actually editing anything, and are you the chief of a group of people? Are you actually filling the role of an editor-in-chief at, say, a newspaper, that is, are you the one that gets to approve what is published and do you take the responsibility for it? I'm not sure. Next time I talk to my publisher, do I have to check with you first? Call yourselves "Coordinator of Events" and "Coordinator of Publishing" or something along these lines, and people will know what you actually do, and they will see that your tasks are analogous. I find it peculiar and disconcerting that the advocacy group appears to organize itself by assigning all available tasks to individual people. Whatever happened to the well-established and successful method of providing a mailing list as the point of contact and solving tasks as a group? You will notice that there is no "Coordinator of Development", "Doc-Writer-in-Chief", "Master of the Makefiles", or even a single webmaster, notwithstanding the fact that there are de-facto experts in these fields. The method you are choosing might be a good way to get things done now and quickly, but it's not scalable. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Displaying a float8 as a full-length number
Hello, I need to get a float8 to display as a number db=# SELECT CAST('0.1' AS float8); float8 1e-05 (1 row) I need this to return 0.1 instead. I know about the to_char function, but this seems to truncate the number after fourteen digits of precision: db=# SELECT CAST('0.001' AS float8); float8 1e-15 (1 row) db=# SELECT to_char(CAST('0.001' AS float8), CAST('0.9' AS text)); to_char --- 0.00 (1 row) Does anyone have any suggestions? Thanks, Grant -- Grant Rutherford Iders Incorporated 600A Clifton Street Winnipeg, MB R3G 2X6 http://www.iders.ca tel: 204-779-5400 ext 36 fax: 204-779-5444 Iders Incorporated: Confidential Note: This message is intended solely for the use of the designated recipient(s) and their appointed delegates, and may contain confidential information. Any unauthorized disclosure, copying or distribution of its contents is strictly prohibited. If you have received this message in error, please destroy it and advise the sender immediately by phone, Email or facsimile. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
Yes, I know you've seen the above subject before, so please be gentle with the flamethrowers. I'm preparing to enter a discussion with management at my company regarding going forward as either a MySql shop or a Postgresql shop. It's my opinion that we should be using PG, because of the full ACID support, and the license involved. A consultant my company hired before bringing me in is pushing hard for MySql, citing speed and community support, as well as ACID support. My biggest concern with MySQL is licensing. We need to keep costs low, and last I remember the parent company was being pretty strict on "fair use" under the GPL. If I recall, they even said a company would have to license the commercial version if it were simply used operationally within the company. Also, I was under the impression that Postgresql had pretty much caught up with MySql in the speed category...is this not the case? Finally, ACID support in mysql always seemed kind of a hackperhaps this has changed? Thanks for any input (armament ;) ) you can provide. John ---(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
[GENERAL] problems retrieving images in 7.3.4
Greetings, I am new to this list. I have a problem with BLOBS using PostgreSQL7.3.4. I used the lo_export utility to insert an image into a trial database. I have not been able to use the lo_export utility to retrieve it. I have done this successfully before using PostgreSQL7.2. Assuming I have a table name TABLE with columns named TITLE and IMAGE. I ( working as postgres ) issued the following select statement : SELECT lo_export(table.image('/tmp/outimage.jpg') from TABLE where TITLE ='First Picture'; I get in response 'cant open unix file system "/tmp/outimage.jpg" no such file or directory. The problem seems to be the filename. So I tried different filenames such as image2.jpg, etc with the same result. I looked at the /tmp directory and I did not see any jpegs. I also looked at the postgres/data directory to see if there is a directory labelled /tmp. I was unsuccessful. I am using a PC running SuSE8.2. Some help will be appreciated. Yours sincerely Sibu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
Sorry for the repost again. I emailed the Admin asking to cancel it (I originally posted from a non-subscribed address), but perhaps he missed it. John John Wells said: > Yes, I know you've seen the above subject before, so please be gentle with > the flamethrowers. > > I'm preparing to enter a discussion with management at my company > regarding going forward as either a MySql shop or a Postgresql shop. > > It's my opinion that we should be using PG, because of the full ACID > support, and the license involved. A consultant my company hired before > bringing me in is pushing hard for MySql, citing speed and community > support, as well as ACID support. > > My biggest concern with MySQL is licensing. We need to keep costs low, > and last I remember the parent company was being pretty strict on "fair > use" under the GPL. If I recall, they even said a company would have to > license the commercial version if it were simply used operationally within > the company. > > Also, I was under the impression that Postgresql had pretty much caught up > with MySql in the speed category...is this not the case? > > Finally, ACID support in mysql always seemed kind of a hackperhaps > this has changed? > > Thanks for any input (armament ;) ) you can provide. > > John > > ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
On Wed, 8 Oct 2003 11:28:00 -0400 (EDT) John Wells <[EMAIL PROTECTED]> wrote: > It's my opinion that we should be using PG, because of the full ACID > support, and the license involved. A consultant my company hired before > bringing me in is pushing hard for MySql, citing speed and community > support, as well as ACID support. you should also cite conformity to standards. MySQL is significantly different from SQL standards in a number of regards. not that any are fully conformant, but there are spots where MySQL is seriously "out there". richard -- Richard Welty [EMAIL PROTECTED] Averill Park Networking 518-573-7592 Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Possible bug on insert
Rick Gigger wrote: No offense taken. I am very meticulous about any software upgrades that I do on my production systems. I'm not quite sure what you mean by "this business since you have no idea what I am actually doing. For all you know I am a boy scout developing a public service web site to earn a merit badge and am not actually in any business. ;-) I suppose that I shouldn't have Well.. Let's leave that part out to keep this discussion technical.:-) said that I don't want to have to test my apps to upgrade to a new version of postgres. I am used to having to do this for any of the software on my servers. What I should have said is that I would prefer not to have to go any change a bunch of code on my production applications immediately. I realize that in order to make progress it is sometimes neccesary to break compatibility with old stuff. That being said it would have been nice in my opinion if there was an option to revert to the old behavior for at least a while so that I can upgrade sooner rather than later. It is not going to be a huge problem for me to update the apps but I am probably going to wait until I am already making other changes and going through a full testing cycle before I do the upgrade. The whole proccess would just be a lot smoother if I had the option of using the old behavior with 7.3 for while. Well.. I haven't work enough on pg to make a migration across versions. But the app. I work on regularly is routinely one year behind a major oracle release and we spend good deal of our time testing oracle bug/feature compatibility and making any changes required. I hope that answers your question. It really depends upon how much mission critical your app. is. If its something that monitors heart beats of patients in a 1 patient hospital in a central fashion (Just making it up) or controlling a nuke plant, I would rather test everything I can. Plus if I have to tell people that we have to spend time and money retesting all of our apps just to not get stuck on an old version of the database that's one more thing they might bring up when making the case to switch to something else. This is why I would like to know about other systems maintaining backwards compatibility. If I had the option to use the old behavior it would be a lot easier to make the transition without anyone noticing. Once again not an insurmountable obstacle but it would be nice. Retesting with application with version upgrade in dependent components is fact of life. It is a must. Its upto you to decide whether you can afford to bypass it, fully or partially. Of course there are business constraints that decides where the trade off settles. If you are happy with the way pg upgrade, good for you. But you should read release notes carefully and work on removing/changing any deprecated features in time. Usually pg makes a feature non-default in one major version and removes it completely in next major version. So you have time of two major releases to take care of any issues. I think that is more than what anybody else can give across versions. Shridhar ---(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] Humor me: Postgresql vs. MySql (esp. licensing)
> "Shridhar" == Shridhar Daithankar <[EMAIL PROTECTED]> writes: Shridhar> Yeah.. like inserting a biiig number in integer field in a transaction Shridhar> without error and not getting it back after commit.. or accepting Shridhar> '00-00-00 00:00:00' as a valid datetime stamp.. something like that.. Shridhar> How much deviation is that from ACID? 180 degrees...:-) Unverified, but you can apparently try to store a huge number into a short integer, and MySQL silently truncates to maxint. No error. No warning. No place for it in a real business environment. :( -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <[EMAIL PROTECTED]> http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] "select count(*) from contacts" is too slow!
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] wrote: >> MySQL can tell you from it's index because it doesn't care if it gives you the >> right number or not. > > Under what circumstances would MySQL give the wrong number? It would give the wrong number under _every_ circumstance where there are uncommitted INSERTs or DELETEs. -- select 'cbbrowne' || '@' || 'ntlug.org'; http://www3.sympatico.ca/cbbrowne/sap.html Appendium to the Rules of the Evil Overlord #1: "I will not build excessively integrated security-and-HVAC systems. They may be Really Cool, but are far too vulnerable to breakdowns." ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
Yes, I know you've seen the above subject before, so please be gentle with the flamethrowers. I'm preparing to enter a discussion with management at my company regarding going forward as either a MySql shop or a Postgresql shop. It's my opinion that we should be using PG, because of the full ACID support, and the license involved. A consultant my company hired before bringing me in is pushing hard for MySql, citing speed and community support, as well as ACID support. My biggest concern with MySQL is licensing. We need to keep costs low, and last I remember the parent company was being pretty strict on "fair use" under the GPL. If I recall, they even said a company would have to license the commercial version if it were simply used operationally within the company. Also, I was under the impression that Postgresql had pretty much caught up with MySql in the speed category...is this not the case? Finally, ACID support in mysql always seemed kind of a hackperhaps this has changed? Thanks for any input (armament ;) ) you can provide. John ---(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] Humor me: Postgresql vs. MySql (esp. licensing)
Oliver Elphick said: > Unless they actually attach extra conditions to the GPL (i.e. "This > product is licensed under GPL with the following extra conditions...") > this is rubbish. The GPL allows you to do what you like with the > software internally; its restrictions only apply to further > distribution. MySQL would _like_ you to pay for support or buy a > commercial licence, but you only _need_ to buy a licence if you want to > distribute a modified or linked MySQL without distributing your own > source code. > > If that position changes, we (Debian) will need to move MySQL from > Debian's main archive to its non-free section, or even drop it > altogether. So do let me know if that becomes necessary! Then read this page: http://www.mysql.com/products/licensing.html Very carefully. Particularly the sentence "As long as you never distribute (internally or externally)"... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
Hi John, I've been thinking about this for sometime, since a couple of my associates are looking to build a commercial app based around JDBC. The difficulty came when we looked at redistributing a MySQL JDBC driver .jar with the application. From what I can tell, since you distribute the JDBC driver they assume that somewhere it's going to be used with a MySQL server and therefore requires licensing. It wasn't exactly clear IMHO or straightforward. So they've decided to go with PG only for the time being, which makes running the server on Windows a little more difficult, but who wants to run windows anyway! As far as speed goes, I think that there isn't enough in it anymore to comment about. Sure MySQL is faster in XYZ scenario, but PG is faster in ABC scenario, swings and roundabouts. IMHO the only place where MySQL has a clear advantage is the fact that it replicates right out of the box, with very little difficulty (see my earlier post today) and is quite robust. Just my 2 Cents Cheers T. John Wells wrote: Yes, I know you've seen the above subject before, so please be gentle with the flamethrowers. I'm preparing to enter a discussion with management at my company regarding going forward as either a MySql shop or a Postgresql shop. It's my opinion that we should be using PG, because of the full ACID support, and the license involved. A consultant my company hired before bringing me in is pushing hard for MySql, citing speed and community support, as well as ACID support. My biggest concern with MySQL is licensing. We need to keep costs low, and last I remember the parent company was being pretty strict on "fair use" under the GPL. If I recall, they even said a company would have to license the commercial version if it were simply used operationally within the company. Also, I was under the impression that Postgresql had pretty much caught up with MySql in the speed category...is this not the case? Finally, ACID support in mysql always seemed kind of a hackperhaps this has changed? Thanks for any input (armament ;) ) you can provide. John ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
Richard Welty wrote: On Wed, 8 Oct 2003 11:28:00 -0400 (EDT) John Wells <[EMAIL PROTECTED]> wrote: It's my opinion that we should be using PG, because of the full ACID support, and the license involved. A consultant my company hired before bringing me in is pushing hard for MySql, citing speed and community support, as well as ACID support. you should also cite conformity to standards. MySQL is significantly different from SQL standards in a number of regards. not that any are fully conformant, but there are spots where MySQL is seriously "out there". Yeah.. like inserting a biiig number in integer field in a transaction without error and not getting it back after commit.. or accepting '00-00-00 00:00:00' as a valid datetime stamp.. something like that.. How much deviation is that from ACID? 180 degrees...:-) One of my friend lost data with mysql yesterday.. The machine was taken down for disk upgrade and mysql apperantly did not commit the last insert.. OK he was using myisam but still..:-) Shridhar ---(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] Humor me: Postgresql vs. MySql (esp. licensing)
On Wed, 2003-10-08 at 16:28, John Wells wrote: > Yes, I know you've seen the above subject before, so please be gentle with > the flamethrowers. > > I'm preparing to enter a discussion with management at my company > regarding going forward as either a MySql shop or a Postgresql shop. > > It's my opinion that we should be using PG, because of the full ACID > support, and the license involved. A consultant my company hired before > bringing me in is pushing hard for MySql, citing speed and community > support, as well as ACID support. Speed depends on the nature of use and the complexity of queries. If you are doing updates of related tables, ACID is of vital importance and MySQL doesn't provide it. > My biggest concern with MySQL is licensing. We need to keep costs low, > and last I remember the parent company was being pretty strict on "fair > use" under the GPL. If I recall, they even said a company would have to > license the commercial version if it were simply used operationally within > the company. Unless they actually attach extra conditions to the GPL (i.e. "This product is licensed under GPL with the following extra conditions...") this is rubbish. The GPL allows you to do what you like with the software internally; its restrictions only apply to further distribution. MySQL would _like_ you to pay for support or buy a commercial licence, but you only _need_ to buy a licence if you want to distribute a modified or linked MySQL without distributing your own source code. If that position changes, we (Debian) will need to move MySQL from Debian's main archive to its non-free section, or even drop it altogether. So do let me know if that becomes necessary! > Also, I was under the impression that Postgresql had pretty much caught up > with MySql in the speed category...is this not the case? MySQL is very good for simple queries by not too many users at a time. Otherwise it's no better than PostgreSQL and often worse to unusable, according to other posts I've seen. > Finally, ACID support in mysql always seemed kind of a hackperhaps > this has changed? Not that I know of. > Thanks for any input (armament ;) ) you can provide. I took over maintenance of a project written in MySQL. It is a total nightmare. No triggers, foreign keys or constraints, and bizarre timestamp handling. Given the choice, I wouldn't touch it with a ten foot pole. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let no man say when he is tempted, I am tempted of God; for God cannot be tempted with evil, neither tempteth he any man; But every man is tempted, when he is drawn away of his own lust, and enticed." James 1:13,14 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] UPDATE and outer joins
On Wed, Oct 08, 2003 at 15:40:13 +0100, Harry Broomhall <[EMAIL PROTECTED]> wrote: > Bruno Wolff III writes: > > On Wed, Oct 08, 2003 at 12:23:04 +0100, > > Harry Broomhall <[EMAIL PROTECTED]> wrote: > > >I wonder if anybody could give me a few pointers on a problem I face. > > > > > >I need to do an UPDATE on table A, from an effective left outer join > > > on A and another table B. (This is trying to perform a number translation, > > > where the items that need it are rare.) > > > > > >The following points *I think* are relevant: > > > > > > 1) The FROM clause in UPDATE should *only* show additional tables, > > > otherwise I'll get an extra join I didn't want! (IMHO this could do > > > with being emphasised in the docs.) > > > > But that might be the best approach. If you do a left join of A with B in > > the where clause and then an inner join of that result with A you should > > get what you want. If the optimizer does a good job, it may not even be > > much of a hit to do that. > > Er - I though that was one of the points I made - you can't get a > left join in a WHERE clause? If I am wrong about that then could you > indicate how I might do it? I slipped on that. I did mean that you could do left join in the from item list and then join that to the table be updated by using an appropiate where condition. > > I presumed that the left join would have to be in the FROM clause, i.e.: > > UPDATE A set cli = num FROM A left join B on (details) WHERE (etc) > > I tried this approach early on, and now I think about it I realize I > didn't have a WHERE clause - which would have done a cross join which would > have taken forever! Someone else responded with the same suggestion, but a bit more fleshed out. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] UPDATE and outer joins
Bruno Wolff III writes: > On Wed, Oct 08, 2003 at 12:23:04 +0100, > Harry Broomhall <[EMAIL PROTECTED]> wrote: > >I wonder if anybody could give me a few pointers on a problem I face. > > > >I need to do an UPDATE on table A, from an effective left outer join > > on A and another table B. (This is trying to perform a number translation, > > where the items that need it are rare.) > > > >The following points *I think* are relevant: > > > > 1) The FROM clause in UPDATE should *only* show additional tables, > > otherwise I'll get an extra join I didn't want! (IMHO this could do > > with being emphasised in the docs.) > > But that might be the best approach. If you do a left join of A with B in > the where clause and then an inner join of that result with A you should > get what you want. If the optimizer does a good job, it may not even be > much of a hit to do that. Er - I though that was one of the points I made - you can't get a left join in a WHERE clause? If I am wrong about that then could you indicate how I might do it? I presumed that the left join would have to be in the FROM clause, i.e.: UPDATE A set cli = num FROM A left join B on (details) WHERE (etc) I tried this approach early on, and now I think about it I realize I didn't have a WHERE clause - which would have done a cross join which would have taken forever! Regards, Harry. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] UPDATE and outer joins
On Wed, Oct 08, 2003 at 12:23:04 +0100, Harry Broomhall <[EMAIL PROTECTED]> wrote: >I wonder if anybody could give me a few pointers on a problem I face. > >I need to do an UPDATE on table A, from an effective left outer join > on A and another table B. (This is trying to perform a number translation, > where the items that need it are rare.) > >The following points *I think* are relevant: > > 1) The FROM clause in UPDATE should *only* show additional tables, > otherwise I'll get an extra join I didn't want! (IMHO this could do > with being emphasised in the docs.) But that might be the best approach. If you do a left join of A with B in the where clause and then an inner join of that result with A you should get what you want. If the optimizer does a good job, it may not even be much of a hit to do that. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] install; readline error with 7.3.4
On Wed, Oct 08, 2003 at 05:34:32PM +0900, Jean-Christian Imbeault wrote: > On my RH 9.0 linux system when ./configuring 7.3.4 I get an error about > not finding the readline libs, however both the readline and > readline-devel rpms are installed: On some linux redhat-ish distros I think you also need ncurses-devel and/or termcap-devel for configure to find readline capabilities. -- Alvaro Herrera () "Los dioses no protegen a los insensatos. Éstos reciben protección de otros insensatos mejor dotados" (Luis Wu, Mundo Anillo) ---(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] using copy to load odd characters
Title: RE: [GENERAL] using copy to load odd characters --howdy: --to reiterate the problem, i am simply doing --a copy of a text file into a table: [snip example] testdb> \copy t_test1 from '/var/tmp/results.txt' with delimiter as '|' [/snip example] --the problem was that there were ^@ characters in the file --and PostgreSQL didn't / couldn't load the data and terminated --the process. --my solution was to split the large files and, with VI, --removed the ^@ characters by hand. the copy went --as expected. --i expected PostgreSQL to load the data, regardless --of what was in the file. i'm only saying i 'expected' --this because i've loaded the same file into the same --table structure as DB2 (also my source for this file) --and Oracle 9 with no problem. my apologies for assuming. --i suppose my next question could be 'why couldn't --PostgreSQL load characters like this into a table?' --but it seems like the best thing for future events --is to write a perl script to scan the text files and --remove them before loading the data. --thanks! >Totally impossible to tell unless you tell us >exactly what you are doing >and what you expected out of it. -X
[GENERAL] install; readline error with 7.3.4
On my RH 9.0 linux system when ./configuring 7.3.4 I get an error about not finding the readline libs, however both the readline and readline-devel rpms are installed: # rpm -qa | grep readline readline-4.3-5 readline-devel-4.3-5 What library/rpm am I missing? The config.log file has this to say: configure:5786: result: no configure:5800: checking for readline configure:5837: gcc -o conftest -O2 conftest.c -lreadline -lcrypt -lresolv -lnsl -ldl -lm -lbsd >&5 /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetnum' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgoto' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetflag' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `BC' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tputs' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `PC' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetent' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `UP' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetstr' collect2: ld returned 1 exit status Thanks, Jean-Christian Imbeault ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] UPDATE and outer joins
I wonder if anybody could give me a few pointers on a problem I face. I need to do an UPDATE on table A, from an effective left outer join on A and another table B. (This is trying to perform a number translation, where the items that need it are rare.) The following points *I think* are relevant: 1) The FROM clause in UPDATE should *only* show additional tables, otherwise I'll get an extra join I didn't want! (IMHO this could do with being emphasised in the docs.) 2) If a FROM clause is present, but no WHERE clause, a cross join is performed. 3) It is possible to 'emulate' an inner join with statements in a WHERE clause, but is *not* possible to do so for an outer join. (4.2.2 in the User's Guide) If I have the above correct then it seems that there is no way to do an UPDATE in the way I want in a single statement? I am currently doing a left outer join into a temporary file, then the UPDATE, but this involves two joins! Is there a better way of doing this, or do I have to keep using the temporary file? Regards, Harry. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] refential integrity to multiple tables ??
No, you don't need a NULL entry in "table_type1" or "table_type2". When inserting NULL into a column which REFERENCES another table, there is simply no referencing done. /Mattias Nagib Abi Fadel wrote: What u suggest here is having a null entry in table TABLE_TYPE1 and TABLE_TYPE2. (This does not seem to be right.) That way i would be able to make a referential integrity to each table by creating the table transaction like follows: CREATE TABLE transaction ( transaction_id, amount, type1_id default null REFERENCES TABLE_TYPE1 (type1_id) on delete cascade, type2_id default null REFERENCES TABLE_TYPE2 (type2_id) on delete cascade, CONSTRAINT (type1_id IS NULL OR type2_id IS NULL) ) If someone deletes the null row in either table (TABLE_TYPE1 or TABLE_TYPE2) this would be a disaster. (Someone who replaced me in my post for instance) But in other hand i will make a join between two tables instead of three if i want to retrieve some informations for a specific type. Or i could create the table without referential integrity ??? The decision is confusing a little bit ... What should i choose ?? Thx for your help.Mattias Kregert <[EMAIL PROTECTED]> wrote: Maybe you should skip the "type" field and instead have id columns for each of the types and then on insert set the id for only one of the types. You could also make a constraint to make sure only one of the type id's can be specified: CREATE TABLE transaction ( transaction_id, amount, type1_id default null, type2_id default null, CONSTRAINT (type1_id IS NULL OR type2_id IS NULL) ) I have done something like this, myself... /Mattias - Original Message - From: Nagib Abi Fadel To: [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 7:53 AM Subject: [GENERAL] refential integrity to multiple tables ?? HI, let's say i have a tansaction table called TRANSACTION (transaction_id,amount,type,type_id) Let's say a transaction can have multiple types: TYPE1, TYPE2 for example. EACH type has his own definition and his own table. Every transaction has a type that could be type1 or type2 that's why if the type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and if the type is TYPE2 i want to make a referential integrity to the TYPE2_TABLE. IS IT POSSIBLE TO DO THAT??? I made a turn around to this problem by creating two tables: - table TYPE1_TRANSACTION (type1_id,transaction_id) - table TYPE2_TRANSACTION (type2_id,transaction_id) But this does not seem so right for me ?? thx for any help Do you Yahoo!?The New Yahoo! Shopping - with improved product search Do you Yahoo!?The New Yahoo! Shopping - with improved product search
[GENERAL] Replication Bundled with Main Source.
Hi All, Firstly I've gotta say that I think that PostgreSQL is one of the finest OSS projects out there and full credit to all of those involved. After talking to a couple of other consultants who use Pg, and fully encourage their clients in the enterprise that Pg is a perfectly viable solution for a variety of scenarios, the question seems to crop up quite often: "What About Replication?". Whilst I understand that the eRServer project is a fine project, and more than capable, and rapidly reaching the point of having minimal bugginess, I have to wonder why there is no talk of including replication capability within the main source tree. After all in todays RDMS arena, it would seem almost like it is an after thought, as CTOs expect replication to be a feature of the server, rather than seeming to be an afterthought. Almost like having an transactional engine bolted on after the fact. Are there likely to be any plans to integrate a replication engine into the main code which could be switchable at compile time '--with-replication' for instance. I beleive that this would encourage acceptance within the corporate environment and lead to a more well-rounded offering. Just my 2 cents (or tuppence-ha'penny for those in blighty) Regards Tony ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] refential integrity to multiple tables ??
What u suggest here is having a null entry in table TABLE_TYPE1 and TABLE_TYPE2. (This does not seem to be right.) That way i would be able to make a referential integrity to each table by creating the table transaction like follows: CREATE TABLE transaction ( transaction_id, amount, type1_id default null REFERENCES TABLE_TYPE1 (type1_id) on delete cascade, type2_id default null REFERENCES TABLE_TYPE2 (type2_id) on delete cascade, CONSTRAINT (type1_id IS NULL OR type2_id IS NULL) ) If someone deletes the null row in either table (TABLE_TYPE1 or TABLE_TYPE2) this would be a disaster. (Someone who replaced me in my post for instance) But in other hand i will make a join between two tables instead of three if i want to retrieve some informations for a specific type. Or i could create the table without referential integrity ??? The decision is confusing a little bit ... What should i choose ?? Thx for your help.Mattias Kregert <[EMAIL PROTECTED]> wrote: Maybe you should skip the "type" field and instead have id columns for each of the types and then on insert set the id for only one of the types. You could also make a constraint to make sure only one of the type id's can be specified: CREATE TABLE transaction ( transaction_id, amount, type1_id default null, type2_id default null, CONSTRAINT (type1_id IS NULL OR type2_id IS NULL) ) I have done something like this, myself... /Mattias - Original Message - From: Nagib Abi Fadel To: [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 7:53 AM Subject: [GENERAL] refential integrity to multiple tables ?? HI, let's say i have a tansaction table called TRANSACTION (transaction_id,amount,type,type_id) Let's say a transaction can have multiple types: TYPE1, TYPE2 for example. EACH type has his own definition and his own table. Every transaction has a type that could be type1 or type2 that's why if the type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and if the type is TYPE2 i want to make a referential integrity to the TYPE2_TABLE. IS IT POSSIBLE TO DO THAT??? I made a turn around to this problem by creating two tables: - table TYPE1_TRANSACTION (type1_id,transaction_id) - table TYPE2_TRANSACTION (type2_id,transaction_id) But this does not seem so right for me ?? thx for any help Do you Yahoo!?The New Yahoo! Shopping - with improved product search Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: [GENERAL] refential integrity to multiple tables ??
--- Richard Huxton <[EMAIL PROTECTED]> wrote: > On Wednesday 08 October 2003 06:53, Nagib Abi Fadel > wrote: > > HI, > > > > let's say i have a tansaction table called > TRANSACTION > > (transaction_id,amount,type,type_id) > > > > Let's say a transaction can have multiple types: > TYPE1, TYPE2 for example. > > > > EACH type has his own definition and his own > table. > > > > Every transaction has a type that could be type1 > or type2 that's why if the > > type is TYPE1 i want to make a referential > integrity to the TYPE1_TABLE and > > if the type is TYPE2 i want to make a referential > integrity to the > > TYPE2_TABLE. > > > > IS IT POSSIBLE TO DO THAT??? > > You're looking at it the wrong way around, but in > any case there are still > problems. > > transaction_core(trans_id, trans_name, trans_type) > transaction_type1(tt1_core_id, tt1_extra1, > tt1_extra2...) > transaction_type2(tt2_core_id, tt2_extra1, > tt2_extra2...) > > And have tt1_core reference trans_id (not the other > way around). Do the same > for tt2_core and we can guarantee that the two > transaction types refer to a > valid trans_id in transaction_core. > > Now, what gets trickier is to specify that tt1_core > should refer to a row in > transaction_core where trans_type=1. > Ideally, we could have a foreign-key to a view, or > specify a constant in the > FK definition. We can't so you have to repeat the > type field in > transaction_type1/2 and keep it fixed for every row. > > HTH > -- > Richard Huxton > Archonet Ltd Actually a type1_id can have mutiple corresponding transaction_ids (same thing for type2) that's why i created the tables as follows: create table transaction( transaction_id serial P K, amount int,...) create table TABLE_TYPE1( type1_id serial P K, ... ) create table transaction_type1( type1_id int, transaction_id int ) for example we can have the following possible entries in table transaction_type1: type1_id,transaction_id 100,101 100,102 100,103 200,312 200,313 200,314 200,315 Same thing for type 2. I can also add that a transaction id can be of type1 or (exclusive) of type2 and never of two types at the same time. __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] How to delete unclosed connections?
Hi All, I've a problem with unclosed connections. Once a client aborts a connection accidentelly (client crash or power failure eg.), it stucks in and postgres won't restart or stop. Is there any way to close unused (dead) connections. I'd guess that some kind of connection timeout option should do this. Aren't I right? Tank you in advance. Best Regards, -- Együd Csaba --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.522 / Virus Database: 320 - Release Date: 2003. 09. 29. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] refential integrity to multiple tables ??
On Wednesday 08 October 2003 06:53, Nagib Abi Fadel wrote: > HI, > > let's say i have a tansaction table called TRANSACTION > (transaction_id,amount,type,type_id) > > Let's say a transaction can have multiple types: TYPE1, TYPE2 for example. > > EACH type has his own definition and his own table. > > Every transaction has a type that could be type1 or type2 that's why if the > type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and > if the type is TYPE2 i want to make a referential integrity to the > TYPE2_TABLE. > > IS IT POSSIBLE TO DO THAT??? You're looking at it the wrong way around, but in any case there are still problems. transaction_core(trans_id, trans_name, trans_type) transaction_type1(tt1_core_id, tt1_extra1, tt1_extra2...) transaction_type2(tt2_core_id, tt2_extra1, tt2_extra2...) And have tt1_core reference trans_id (not the other way around). Do the same for tt2_core and we can guarantee that the two transaction types refer to a valid trans_id in transaction_core. Now, what gets trickier is to specify that tt1_core should refer to a row in transaction_core where trans_type=1. Ideally, we could have a foreign-key to a view, or specify a constant in the FK definition. We can't so you have to repeat the type field in transaction_type1/2 and keep it fixed for every row. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] refential integrity to multiple tables ??
Almost everything is possible if you accept to write your own triggers and trigger functions. But there is no standard solution to this problem. There are of course inherited tables, but in the current implementation, foreign key constraints doesn't work very well with them (making them completly worthless IMO). That is going to change in a future release according to the docs, but for now, table inheritance is not the way to go in your case. Triggers are. Regards Erik > let's say i have a tansaction table called TRANSACTION > (transaction_id,amount,type,type_id) > > Let's say a transaction can have multiple types: TYPE1, TYPE2 for > example. > > EACH type has his own definition and his own table. > > Every transaction has a type that could be type1 or type2 that's why > if the type is TYPE1 i want to make a referential integrity to the > TYPE1_TABLE and if the type is TYPE2 i want to make a referential > integrity to the TYPE2_TABLE. > > IS IT POSSIBLE TO DO THAT??? > > I made a turn around to this problem by creating two tables: > - table TYPE1_TRANSACTION (type1_id,transaction_id) > - table TYPE2_TRANSACTION (type2_id,transaction_id) > > But this does not seem so right for me ?? > > thx for any help Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] refential integrity to multiple tables ??
HI, let's say i have a tansaction table called TRANSACTION (transaction_id,amount,type,type_id) Let's say a transaction can have multiple types: TYPE1, TYPE2 for example. EACH type has his own definition and his own table. Every transaction has a type that could be type1 or type2 that's why if the type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and if the type is TYPE2 i want to make a referential integrity to the TYPE2_TABLE. IS IT POSSIBLE TO DO THAT??? I made a turn around to this problem by creating two tables: - table TYPE1_TRANSACTION (type1_id,transaction_id) - table TYPE2_TRANSACTION (type2_id,transaction_id) But this does not seem so right for me ?? thx for any help Do you Yahoo!? The New Yahoo! Shopping - with improved product search
[GENERAL] Does postgresql support HKSCS ?
Hi all, I would like to setup a database which can accept HKSCS - "Hong Kong Supplementary Character Set". Here is the reference: http://www.info.gov.hk/digital21/eng/hkscs/introduction.html Can somebody tell me how to do it? I've try database encoding with EUC_TW, SQL_ASCII and UNICODE, all failed. These are my configure: RH9 + Postgresql 7.3.4 + Tomcat 4.1.24 + JSP. Do I need to update RH9 also? Looking forward for you reply. Thank you very much. Best Regards, Eric Tan 「向左走 向右走」趣怪 VoiceMail 歡迎詞 http://voicemail.yahoo.com.hk