Re: [GENERAL] Schemas and access
I am just beginning to investigate schemas, and have run into a
problem. Searching the mailing lists and documentation doesn't help.
This is 7.3.3 on Linux, kernel 2.4.21, good ole Slackware, compiled
from source.
Two users, JoeBob and MaryJo. JoeBob has schema xyzzy with table
plugh. JoeBob has run
GRANT SELECT on xyzzy.plugh TO public;
JoeBob and MaryJo can both show permissions:
joebob= \z zyzzy.*
Access privileges for database joebob
Schema | Table | Access privileges
++
xyzzy | plugh | {=r,joebob=arwdRxt}
JoeBob can do anything with the table, that works. But MaryJo can't
even select:
joebob= select * from xyzzy.plugh;
ERROR: xyzzy: permission denied
It sure looks like the schema itself is invisible to MaryJo, but
MaryJo can run \dt xyzzy.* perfectly fine.
I think there is a greant usage on schema type statement that you
probably need.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Schemas and access
[EMAIL PROTECTED] writes: joebob= select * from xyzzy.plugh; ERROR: xyzzy: permission denied It sure looks like the schema itself is invisible to MaryJo, but MaryJo can run \dt xyzzy.* perfectly fine. You need to grant USAGE permission on the schema to MaryJo. We've seen this mistake often enough that I wonder if maybe schemas should default to granting USAGE permission to public ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Aggregate functions on ordered data?
Andreas Lehrbaum [EMAIL PROTECTED] writes: Is it possible in _any_ way to control the order of the rows passed to an aggregate function? For simple aggregation you can do SELECT myagg(col) FROM (SELECT col FROM ... ORDER BY foo) ss; If you wanted to group, you could try SELECT gcol, myagg(col) FROM (SELECT gcol, col FROM ... ORDER BY gcol, foo) ss GROUP BY gcol; but this does *not* work reliably in current releases (it will work in 7.4 though). See the archives. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] A creepy story about dates. How to prevent it?
On Sun, 2003-06-22 at 15:47, Bruce Momjian wrote: Ron Johnson wrote: On Sun, 2003-06-22 at 12:46, Bruce Momjian wrote: Ron Johnson wrote: On Sun, 2003-06-22 at 00:05, Bruce Momjian wrote: Bruce Momjian wrote: Reading the subject, creepy ... dates, that is exactly how I feel about the described current date behavior --- creepy. Because I have only seen one person defend our current behavior, and many object, I am going to add to TODO: * Allow current datestyle to restrict dates; prevent month/day swapping from making invalid dates valid? * Prevent month/day swapping of ISO dates to make invalid dates valid I added a question mark to the first item so we can consider it later. Most agreed on the second item, but a few thought the first one might be OK as is. How about situations where reversing the month and date would still have valid but wrong dates, based upon the LOCALE mask? I.e., 05/04/2003 is 05-April-2003 or 04-May-2003, depending on whether the LOCALE implies DD/MM/ or MM/DD/. My assumption is that we already handlle these OK because we base it on datestyle. According to the OP, no. Oh, you are right. We base it on datestyle, rather than locale. Is it desiarable to default postgresql.conf datestyle to match the locale? # # Locale settings # # (initialized by initdb -- may be changed) LC_MESSAGES = 'C' LC_MONETARY = 'C' LC_NUMERIC = 'C' LC_TIME = 'C' As long as it's overridable by a masking set statement, does it matter? Well, it probably does, for consistency's sake. P.S. - candle.pha.pa.us rejects email from smtp.east.cox.net because Delivery blocked --- Previous SPAM received from your mail server. That's blocking a *lot* of valid email, since cox.net is pretty large. -- +---+ | Ron Johnson, Jr. Home: [EMAIL PROTECTED] | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | Oh, great altar of passive entertainment, bestow upon me | | thy discordant images at such speed as to render linear | | thought impossible (Calvin, regarding TV) | +--- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
I think most people would agree that a large part of MySQL's audience has come from the bundling of MySQL libraries with PHP. Getting PostgreSQL to fill this void would be a very positive development. If concerns about licensing are a major driver here, I would think that PostgreSQL's simple Berkeley license would compare very favorably to the tangled web of Interbase history of corporate intrigue and community forking. - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Josh Berkus [EMAIL PROTECTED] Cc: Joe Conway [EMAIL PROTECTED]; Advocacy (PostgreSQL) [EMAIL PROTECTED]; PostgreSQL-general [EMAIL PROTECTED] Sent: Sunday, June 22, 2003 5:59 PM Subject: Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread Josh Berkus wrote: Joe, Interesting thread (php-dev subj: removing bundled libmysql): http://marc.theaimsgroup.com/?l=php-devm=105621066832429w=2 I particularly liked this post: (http://marc.theaimsgroup.com/?l=php-devm=105621207500778w=2) Boy, Monty's making friends all over, ain't he? [ CC to general.] [ MySQL changes client library to GPL.] This is _very_ interesting, and not surprising. MySQL got users to adopt MySQL, then they change the client license to get users to purchase commercial, non-GPL licenses. We need to use this opportunity to encourage PHP folks to switch to PostgreSQL. Notice the second URL mentions Interbase before PostgreSQL, which I find curious. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] PlPython
Kevin Jacobs [EMAIL PROTECTED] writes: Attached is a patch that removes all of the RExec code from plpython from the current PostgreSQL CVS. In addition, plpython needs to be changed to an untrusted language in createlang. I am inclined to rename plpython to plpythonu, by analogy to pltclu. The advantage of doing so is that (a) the name change makes it somewhat more obvious that there's a fundamental behavioral change, and (b) assuming that the Python folk someday figure out a secure version of RExec, we'd want to reinstitute the trusted version of plpython, but perhaps not take away the untrusted one. On the other hand, this would create headaches for people who are trying to load dump files that declare plpython or contain plpython-language functions. I can't think of any non-kluge solution to this (kluge solutions would include putting special-case code into CREATE FUNCTION to change 'plpython' to 'plpythonu' ...) Comments? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
Bruce Momjian wrote: Josh Berkus wrote: Joe, Interesting thread (php-dev subj: removing bundled libmysql): http://marc.theaimsgroup.com/?l=php-devm=105621066832429w=2 I particularly liked this post: (http://marc.theaimsgroup.com/?l=php-devm=105621207500778w=2) Boy, Monty's making friends all over, ain't he? [ CC to general.] [ MySQL changes client library to GPL.] This is _very_ interesting, and not surprising. MySQL got users to adopt MySQL, then they change the client license to get users to purchase commercial, non-GPL licenses. We need to use this opportunity to encourage PHP folks to switch to PostgreSQL. Not surprising at all. And I think to make the big touting that MySQL will continue to support open source and bla, bla, and then putting this pretty severe change into the better hidden fine print is a good example how $19.5M affect someones ethics. To clearify, we need to encourage the PHP developer community to encourage the PHP user community to switch to PostgreSQL. What I'm worried about is exactly the people who adopted MySQL already. The change to another database will be painfull no matter what. How many of them will be willing to give another open source database a shot? Notice the second URL mentions Interbase before PostgreSQL, which I find curious. That's simply alphabetical, don't try to interpret something into it. 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 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
Bruce Momjian [EMAIL PROTECTED] writes: Interesting thread (php-dev subj: removing bundled libmysql): http://marc.theaimsgroup.com/?l=php-devm=105621066832429w=2 Hoo boy. Did you catch the part about and the MySQL 3.2.23 library can't connect to MySQL 4.1 servers, rendering it broken. Backwards compatibility must not be a consideration over there... We need to use this opportunity to encourage PHP folks to switch to PostgreSQL. Indeed. What can we do exactly? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
To clearify, we need to encourage the PHP developer community to encourage the PHP user community to switch to PostgreSQL. What I'm worried about is exactly the people who adopted MySQL already. The change to another database will be painfull no matter what. How many of them will be willing to give another open source database a shot? If you assume that cost is one of the factors in going with an open source database, what are their choices? I know it took me a while to convince the CIO on the project I'm working on that PostgreSQL was an improvement over MySQL. He's slowly coming around as I start to show him what I am doing with the much richer PostgreSQL feature set, but the performance of 7.3 compared to MySQL is likely to remain a bit of a sticking point, because some queries are taking 2-3 times as long on the same platform with the same data. If the data entry folks, who are probably about to get a look at a portion of the application that is still using the MySQL engine, get used to the search times there, when we switch the whole thing over to PostgreSQL we may get complaints if searches that used to take 3-4 seconds are now taking 10-12 seconds. (Have others noticed that 7 seconds seems to be a threshold point for users reacting to query times?) MySQL also does case independent text comparisions, and apparently ONLY case-insensitive comparisons. -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
On Sun, 22 Jun 2003 [EMAIL PROTECTED] wrote: MySQL also does case independent text comparisions, and apparently ONLY case-insensitive comparisons. Is this a good thing? Doesn't sound like it to me, but figured I'd ask :) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
I know it took me a while to convince the CIO on the project I'm working on that PostgreSQL was an improvement over MySQL. He's slowly coming around as I start to show him what I am doing with the much richer PostgreSQL feature set, but the performance of 7.3 compared to MySQL is likely to remain a bit of a sticking point, because some queries are taking 2-3 times as long on the same platform with the same data. If the data entry folks, who are probably about to get a look at a portion of the application that is still using the MySQL engine, get used to the search times there, when we switch the whole thing over to PostgreSQL we may get complaints if searches that used to take 3-4 seconds are now taking 10-12 seconds. (Have others noticed that 7 seconds seems to be a threshold point for users reacting to query times?) Whoa, something's not right. Could you please send along an EXPLAIN ANALYZE after doing a VACUUM ANALYZE of your query that's taking 3-4x longer? Something smells very strange here because my experience has been quite the opposite... I can understand 0.05ms longer per connection in setup overhead (fork() vs new thread) , but this seems like way too much... I wonder if you couldn't benefit from the use of a cursor if you're returning a large dataset. -sc http://developer.postgresql.org/docs/postgres/sql-declare.html http://developer.postgresql.org/docs/postgres/sql-fetch.html http://developer.postgresql.org/docs/postgres/sql-close.html -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
On Sun, Jun 22, 2003 at 10:19:20PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Interesting thread (php-dev subj: removing bundled libmysql): http://marc.theaimsgroup.com/?l=php-devm=105621066832429w=2 Note the comment on http://marc.theaimsgroup.com/?l=php-devm=105624024116515w=2 : Georg Richter wrote: Unbelievable. Guess the postgres guys are licking their chops over this. We need to use this opportunity to encourage PHP folks to switch to PostgreSQL. Indeed. What can we do exactly? Probably the Postgres client library (that'd be libpq) can be included as part of the PHP distribution. With that, according to the thread, the Postgres support could be built in by default. I can't find the PHP license on their website though. Better hurry. Sterling Hughes is proposing to enable SQlite support by default; that move could be bad for the lobbying of activating Pg support. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) The Postgresql hackers have what I call a NASA space shot mentality. Quite refreshing in a world of weekend drag racer developers. (Scott Marlowe) ---(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] [pgsql-advocacy] interesting PHP/MySQL thread
On Sun, 22 Jun 2003 [EMAIL PROTECTED] wrote: MySQL also does case independent text comparisions, and apparently ONLY case-insensitive comparisons. Is this a good thing? Doesn't sound like it to me, but figured I'd ask :) I think it is a classic case of thinking 'small'. :-) The CIO on the project I'm working on thinks it is a good thing, but he's coming from a MySQL environment, which he only learned in the last year or so and he does not appear to have a lot of familiarity with larger databases. Personally, if I want case insensitivity, I'll WRITE IT INTO THE CODE, but I can see how some people might think that 'NOLAN', 'Nolan' and 'nolan' should be considered as the same data. BTW, I just tested it and MySQL does case folding on values in unique indexes, too. (Well, at least it is consistent.) -- Mike Nolan ---(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] [pgsql-advocacy] interesting PHP/MySQL thread
Whoa, something's not right. Could you please send along an EXPLAIN ANALYZE after doing a VACUUM ANALYZE of your query that's taking 3-4x longer? As luck would have it, I just finished the latest 'emergency' part of the project, so I may have a day or so to play with this before the CIO figures out I'm done. :-) I'm hoping this turns out to be a tuning issue, as I'm still very much of a rookie at tuning PostgreSQL. I'll see if I can work something up. Should this go to the general list or somewhere else? -- Mike Nolan ---(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] [pgsql-advocacy] interesting PHP/MySQL thread
Alvaro Herrera [EMAIL PROTECTED] writes: Better hurry. Sterling Hughes is proposing to enable SQlite support by default; that move could be bad for the lobbying of activating Pg support. SQlite? Sure, give it a try. (I was slightly astonished to compare these two pages: http://www.hwaci.com/sw/sqlite/omitted.html http://www.hwaci.com/sw/sqlite/datatypes.html At the very least, one would have to say that the author feels free to define those parts of SQL he doesn't like as not features. There sure isn't anything on the former page to suggest that vast parts of the SQL spec are being ignored per the latter page.) SQlite is even less competition from our point of view than MySQL is ... if the PHP guys think their users will be satisfied with SQlite, let them try it for awhile. I'd be happy if PHP would adopt a database-neutral stance, ie, nothing in particular bundled into their core distribution. That might not be compatible with their project goals though. Anyone have a feeling about how important it is to them to have bundled DB support? Maybe we could talk them into bundling more than one DB interface --- if they put both PG and SQlite support into their distro, that'd be fine with me too. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
Hi, 3.2.23 was a LONG time ago. One thing I like about mysql is that they are constantly making major improvements. I have asked this before, where is Replication with PostgreSQL? If there was a system that could handle more than one master without hacking, I would seriously look into switching to PostgreSQL again. Currently mysql can't handle more than one master cleanly. Lack of built in Replication is the main thing that continues to keep us from using PostgreSQL. All of the little, baby can't learn how to program or write SQL functions don't do crap for me, but Replication is a large part of our network structure, we can't do without it and we certainly don't want to use a third party product. Patching together tools like what happens with a qmail install is not a system I want to be responsible for. And yet I like qmail a great deal, and I like what I have seen of PostgreSQL. Thanks, Eric At 10:19 PM 6/22/03 -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Interesting thread (php-dev subj: removing bundled libmysql): http://marc.theaimsgroup.com/?l=php-devm=105621066832429w=2 Hoo boy. Did you catch the part about and the MySQL 3.2.23 library can't connect to MySQL 4.1 servers, rendering it broken. Backwards compatibility must not be a consideration over there... We need to use this opportunity to encourage PHP folks to switch to PostgreSQL. Indeed. What can we do exactly? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
Whoa, something's not right. Could you please send along an EXPLAIN ANALYZE after doing a VACUUM ANALYZE of your query that's taking 3-4x longer? As luck would have it, I just finished the latest 'emergency' part of the project, so I may have a day or so to play with this before the CIO figures out I'm done. :-) I'm hoping this turns out to be a tuning issue, as I'm still very much of a rookie at tuning PostgreSQL. I'll see if I can work something up. Should this go to the general list or somewhere else? Definitely [EMAIL PROTECTED] -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
On Sun, Jun 22, 2003 at 11:57:10PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Better hurry. Sterling Hughes is proposing to enable SQlite support by default; that move could be bad for the lobbying of activating Pg support. SQlite? Sure, give it a try. Actually, I read the website right after I sent the email and I was... surprised. I am still wondering if it allows some kind of concurrent access. (I was slightly astonished to compare these two pages: http://www.hwaci.com/sw/sqlite/omitted.html http://www.hwaci.com/sw/sqlite/datatypes.html The omitted features I can understand. But his take on datatypes is really simplistic (read: absurd.) I'd be happy if PHP would adopt a database-neutral stance, ie, nothing in particular bundled into their core distribution. What probably won't do. If they are desperate enough to activate SQLite by default, it means they want to have at least database support at all times. Maybe we could talk them into bundling more than one DB interface --- if they put both PG and SQlite support into their distro, that'd be fine with me too. Sure, because users are very likely to use Postgres if support is readily available (and it is already installed by default or at least included in most Linux distributions). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La persona que no quería pecar / estaba obligada a sentarse en duras y empinadas sillas/ desprovistas, por cierto de blandos atenuantes (Patricio Vogel) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
On Sun, 22 Jun 2003 [EMAIL PROTECTED] wrote: On Sun, 22 Jun 2003 [EMAIL PROTECTED] wrote: MySQL also does case independent text comparisions, and apparently ONLY case-insensitive comparisons. Is this a good thing? Doesn't sound like it to me, but figured I'd ask :) I think it is a classic case of thinking 'small'. :-) The CIO on the project I'm working on thinks it is a good thing, but he's coming from a MySQL environment, which he only learned in the last year or so and he does not appear to have a lot of familiarity with larger databases. Personally, if I want case insensitivity, I'll WRITE IT INTO THE CODE, but I can see how some people might think that 'NOLAN', 'Nolan' and 'nolan' should be considered as the same data. Oh, you mean like SELECT * FROM table WHERE field ~* 'nolan';? ---(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] [pgsql-advocacy] interesting PHP/MySQL thread
On Sun, 22 Jun 2003, Eric Frazier wrote: constantly making major improvements. I have asked this before, where is Replication with PostgreSQL? Replication for PostgreSQL has been available, and in production use, for at least two years now ... the .ORG registry is using eRServer ... there was also a patch for contrib/rserv submit'd just the other week to make it multi-slave ... there is also pgreplication (on gborg) and dbmirror ... all of which have been around for ages now ... ---(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] [pgsql-advocacy] interesting PHP/MySQL thread
Sterling Hughes [EMAIL PROTECTED] writes: Many sites don't require a relational database system. Agreed. If SQlite gets the job done for some folk, then that's the tool they ought to use. I was just a tad bemused by the apparent claim that it could be considered a substitute for Postgres (or even MySQL) in general. I think there's some merit in the idea of bundling both PG and SQlite support in future PHP releases. You'd have both a high-end and a low-end solution available. There are probably some people in the middle who'd complain that neither solution quite hits their sweet spot like MySQL did, but I'd bet that overall this'd cover quite a nice range of problems. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
On 6/22/03 10:57 PM, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Better hurry. Sterling Hughes is proposing to enable SQlite support by default; that move could be bad for the lobbying of activating Pg support. SQlite? Sure, give it a try. (I was slightly astonished to compare these two pages: http://www.hwaci.com/sw/sqlite/omitted.html http://www.hwaci.com/sw/sqlite/datatypes.html At the very least, one would have to say that the author feels free to define those parts of SQL he doesn't like as not features. There sure isn't anything on the former page to suggest that vast parts of the SQL spec are being ignored per the latter page.) SQlite is even less competition from our point of view than MySQL is ... if the PHP guys think their users will be satisfied with SQlite, let them try it for awhile. No, with all due respect, don't. These battles aren't won by being a better product. They're won by being used by more people. And generally speaking, one thing tends to win, whether it's the best or not. If you want to exploit this opportunity (which I fervently recommend) than you should make a big push to have postgres be THE database for PHP. People latch onto MySQL because it's joined at the hip with PHP. The way to replace it in that position is, well, by replacing it. MySQL wins, in part, by piggybacking on the ubiquity of PHP. Let's just replace it with Postgres in that role, if possible. I'd be happy if PHP would adopt a database-neutral stance, ie, nothing in particular bundled into their core distribution. That might not be compatible with their project goals though. Anyone have a feeling about how important it is to them to have bundled DB support? Maybe we could talk them into bundling more than one DB interface --- if they put both PG and SQlite support into their distro, that'd be fine with me too. Again, I think a bit of ruthlessness is called for here. You don't want to coexist. You want to be the default, period. I mean, assuming that IS what you want :- It's certainly what I'd like to see, as a heavy user of both PHP and Postgres. I'd recommend a semi-formal approach of the Postgres core team to the PHP core team, asking, in effect, what does the Postgres group need to do to get pgsql bundled up tight with PHP. If there's discontent there, now's the time to capitalize on it. Imagine this press release: PHP team 'unbundles' MySQL in favor of Postgres. Game over. I'm trying to get lined up to give a talk on Postgres at the next PHPCon. From what I understand, PHPers are eager to hear more about it. This seems like a huge opportunity that should be seized with both hands (heck, ALL available hands). -- sgl === Steve Lane Vice President The Moyer Group 14 North Peoria St Suite 2H Chicago, IL 60607 Voice: (312) 433-2421 Email: [EMAIL PROTECTED] Fax: (312) 850-3930 Web: http://www.moyergroup.com === ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
Personally, if I want case insensitivity, I'll WRITE IT INTO THE CODE, but I can see how some people might think that 'NOLAN', 'Nolan' and 'nolan' should be considered as the same data. Oh, you mean like SELECT * FROM table WHERE field ~* 'nolan';? No, I mean as in SELECT * FROM table WHERE field = 'nolan'; That will match values with any combination of upper and lower case letters that fold to 'nolan': 'Nolan', 'NOLAN', etc. Also, unlike PostgreSQL (at least in 7.3), if you define an index on the column, mysql appears to use it for LIKE queries. SELECT * FROM table WHERE field LIKE 'nolan%'; is very fast in mysql but not in 7.3, and even non-anchored LIKE searches in mysql appear to be using the index. SELECT * FROM table WHERE field LIKE '%nolan%'; executes considerably faster with an index on field than without one. -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread
[ Please stop cross posting emails between mailing lists! ]
Personally, if I want case insensitivity, I'll WRITE IT INTO THE CODE,
but I can see how some people might think that 'NOLAN', 'Nolan' and
'nolan' should be considered as the same data.
Oh, you mean like SELECT * FROM table WHERE field ~* 'nolan';?
No, I mean as in SELECT * FROM table WHERE field = 'nolan';
CREATE INDEX table_field_lower_idx ON table (LOWER(field));
VACUUM ANALYZE table;
SELECT * FROM table WHERE LOWER(field) = LOWER('nolan');
SELECT * FROM table WHERE field ILIKE 'nolan%';
ILIKE is case insensitive LIKE.
http://developer.postgresql.org/docs/postgres/functions-matching.html#FUNCTIONS-LIKE
This is likely an FAQ for new MySQL users at this point, or should be
if it's not.
If you are still having performance problems with the above solutions,
please send an EXPLAIN ANALYZE to the performance@ list so we can
figure out what's going on. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Thousands of semops for every i/o
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Jeffrey W. Baker wrote: On Mon, 2003-06-09 at 23:08, Tom Lane wrote: Jeffrey W. Baker [EMAIL PROTECTED] writes: This is the strace of a process which is deleting rows from four tables, inside a transaction, one row at a time. There are a lot of semops for every i/o. There are about 30 connections to this database currently. I thought deletes in a transaction just flew along in Pg, because they simply wrote the deleted transaction ID on the record. It used to work fine in my previous locally-built 7.2 on Debian, but this is 7.2.2 on SuSE Enterprise Server 8.2. The first thing that comes to mind is that the thing is using SysV semaphores as a substitute for spinlocks. If this is on a hardware platform that is supposed to have TAS() support in s_lock.h or s_lock.c, then it's a configuration or build error. If it's on some heretofore unknown platform, you need to write some TAS() code to get decent performance. It looks like a simple change in s_lock.h from #if defined(__i386__) to #if defined(__i386__) || defined(__x86_64__) Will be necessary for this platform. Thanks, jwb ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
