Thread: Re: [pgsql-advocacy] interesting PHP/MySQL thread
Josh Berkus wrote: > Joe, > > > Interesting thread (php-dev subj: removing bundled libmysql): > > http://marc.theaimsgroup.com/?l=php-dev&m=105621066832429&w=2 > > > > I particularly liked this post: > > (http://marc.theaimsgroup.com/?l=php-dev&m=105621207500778&w=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 pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
I think most people would agree that a large part of MySQL's audience has come from the bundling of MySQL libraries withPHP. 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 comparevery favorably to the tangled web of Interbase history of corporate intrigue and community forking. ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: "Josh Berkus" <josh@agliodbs.com> Cc: "Joe Conway" <mail@joeconway.com>; "Advocacy (PostgreSQL)" <pgsql-advocacy@postgresql.org>; "PostgreSQL-general" <pgsql-general@postgresql.org> 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-dev&m=105621066832429&w=2 > > > > > > I particularly liked this post: > > > (http://marc.theaimsgroup.com/?l=php-dev&m=105621207500778&w=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 > pgman@candle.pha.pa.us | (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 > >
Bruce Momjian wrote: > Josh Berkus wrote: >> Joe, >> >> > Interesting thread (php-dev subj: removing bundled libmysql): >> > http://marc.theaimsgroup.com/?l=php-dev&m=105621066832429&w=2 >> > >> > I particularly liked this post: >> > (http://marc.theaimsgroup.com/?l=php-dev&m=105621207500778&w=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. # #================================================== JanWieck@Yahoo.com #
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Interesting thread (php-dev subj: removing bundled libmysql): > http://marc.theaimsgroup.com/?l=php-dev&m=105621066832429&w=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
> 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
On Sun, 22 Jun 2003 nolan@celery.tssi.com 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 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
On Sun, Jun 22, 2003 at 10:19:20PM -0400, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Interesting thread (php-dev subj: removing bundled libmysql): > > http://marc.theaimsgroup.com/?l=php-dev&m=105621066832429&w=2 Note the comment on http://marc.theaimsgroup.com/?l=php-dev&m=105624024116515&w=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)
> On Sun, 22 Jun 2003 nolan@celery.tssi.com 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
> 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
Alvaro Herrera <alvherre@dcc.uchile.cl> 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
On Sun, 22 Jun 2003 23:57:10 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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?
i'm not really a member of the "php community", but i have used it quite a
lot, and the database features are a key component, they're a big part of
why you use php. i'd think that having some db support bundled in their
core is very important to them.
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security
> > 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 performance@. -sc -- Sean Chittenden
On Sun, Jun 22, 2003 at 11:57:10PM -0400, Tom Lane wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> 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)
On Sun, 22 Jun 2003 nolan@celery.tssi.com wrote: > > On Sun, 22 Jun 2003 nolan@celery.tssi.com 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';"?
On 6/22/03 10:57 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> 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: slane@moyergroup.com Fax: (312) 850-3930 Web: http://www.moyergroup.com =======================================================
> > 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
[ 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
On Monday 23 June 2003 07:33, nolan@celery.tssi.com wrote: > > > 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';" bad memories ... of website login system ... using MySQL ... returning several results for a unique login / pw combination ... To stop this behaviour in MySQL one needs to define CHAR and VARCHAR types with a "BINARY" attribute, e.g.: CREATE TABLE tbl (field VARCHAR(24) BINARY) My fault though for not reading the manual attentively enough ;-). -> http://www.mysql.com/doc/en/CHAR.html Ian Barwick barwick@gmx.net
> Sean Chittenden wrote: > > 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 > Apparently it's hard to believe, but yes that's quite possible. And if your query takes only 0.01ms than the 0.05 is quite a difference ;) I've tested and compared both mysql and postgresql a few times and saw both databases beat each other largely on performance. If the query was "more complicated", postgresql is probably the winner. But if it's a simple query (for instance a index-lookup) than mysql beats postgres, yes 3-4x faster is very possible in such a case. At least from my experience, but then again I'm not _that_ well educated on the performance-tuning-options of postgres, I just improve the sortmem and sharedmem and hope that's all to it. (actually, I left mysql in more or less the default settings...) There isn't any real documentation on the performance gains and losses on all those options anyway, is there? So don't expect people to have the best tuned database there is, that's relatively impossible if you don't read this mailinglist 24/7 :) Regards, Arjen
> nolan@celery.tssi.com wrote: > MySQL also does case independent text comparisions, and > apparently ONLY case-insensitive comparisons. It's not very clearly described in the manual. But if you either specify your textual fields 'binary' or use some form of 'like ... binary' query it'll compare them case-sensitive, (apart from the strcmp-functions). And in the case of a binary textual field the indices are also case-sensitive :) Regards, Arjen
On Mon, 23 Jun 2003 nolan@celery.tssi.com wrote: > > > 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';" no, my point was wasn't that the same as what we do with ~*?
Looks like there's some parts of that that would make a good todo. nolan@celery.tssi.com wrote: >>>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 >
At 12:33 AM 6/23/2003 -0500, nolan@celery.tssi.com wrote:
> >
> > 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.
For me that's a matter of taste. I prefer to use = for case sensitive and
lower(field)=lower('data') for case insensitive. I wonder if there is a
difference between using lower vs upper for case insensitivity but I've
never bothered to look deeply into it.
>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.
The versions of Postgresql I've used since I can remember (e.g. at least
v6.5.3 some years ago) use indexes for anchored LIKE searches.
I vaguely recall some people having this "not using index" behaviour when
they are using various locales.
> "SELECT * FROM table WHERE field LIKE '%nolan%';"
>
>executes considerably faster with an index on field than without one.
I think MySQL wins in this one. Just wondering how they do it. And whether
it's a good idea to do it that way.
Regards,
Link.
On Sunday 22 June 2003 11:22 pm, Alvaro Herrera wrote: > On Sun, Jun 22, 2003 at 10:19:20PM -0400, Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > Interesting thread (php-dev subj: removing bundled libmysql): > > > http://marc.theaimsgroup.com/?l=php-dev&m=105621066832429&w=2 > > Note the comment on > http://marc.theaimsgroup.com/?l=php-dev&m=105624024116515&w=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. I don't quite understand this. I thought PostgreSQL library _is_ included with PHP distribution. All I needed to do to compile is put --with-pgsql in configuring php. -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN
Dennis Gearon wrote: > Looks like there's some parts of that that would make a good todo. > > nolan@celery.tssi.com wrote: > > >>>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. We require ~* syntax for that, or upper()/lower(). > > 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. I would love to know how they can use an index for a non-anchored query, i.e. what string are they indexing? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
lower's probably faster, since most characters are lower already.
Lincoln Yeoh wrote:
> At 12:33 AM 6/23/2003 -0500, nolan@celery.tssi.com wrote:
>
>> >
>> > 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.
>
>
> For me that's a matter of taste. I prefer to use = for case sensitive
> and lower(field)=lower('data') for case insensitive. I wonder if there
> is a difference between using lower vs upper for case insensitivity but
> I've never bothered to look deeply into it.
>
>
>> 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.
>
>
> The versions of Postgresql I've used since I can remember (e.g. at least
> v6.5.3 some years ago) use indexes for anchored LIKE searches.
>
> I vaguely recall some people having this "not using index" behaviour
> when they are using various locales.
>
>
>> "SELECT * FROM table WHERE field LIKE '%nolan%';"
>>
>> executes considerably faster with an index on field than without one.
>
>
> I think MySQL wins in this one. Just wondering how they do it. And
> whether it's a good idea to do it that way.
>
> Regards,
> Link.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
On Monday 23 June 2003 12:46 am, Alvaro Herrera wrote: > On Sun, Jun 22, 2003 at 11:57:10PM -0400, Tom Lane wrote: > > 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). AFAIK, if you choose to install "Database Server" in Redhat Linux, it will install PostgreSQL, and have PHP supports PostgreSQL by default. IIRC, it won't install MySQL by default, although the RPMs are included in the distro and you can install it your self. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN
On Mon, 23 Jun 2003, Reuben D. Budiardja wrote: > On Sunday 22 June 2003 11:22 pm, Alvaro Herrera wrote: > > On Sun, Jun 22, 2003 at 10:19:20PM -0400, Tom Lane wrote: > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > > Interesting thread (php-dev subj: removing bundled libmysql): > > > > http://marc.theaimsgroup.com/?l=php-dev&m=105621066832429&w=2 > > > > Note the comment on > > http://marc.theaimsgroup.com/?l=php-dev&m=105624024116515&w=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. > > I don't quite understand this. I thought PostgreSQL library _is_ included with > PHP distribution. All I needed to do to compile is put --with-pgsql in > configuring php. With PHP, when you type --with-pgsql it looks for the libs that postgresql installed for connection. With the way MySQL support was installed, the connection lib files were actually in the PHP source tree, and it by default didn't use the ones installed by MySQL. Note that this could actually cause issues if you compiled Apache with the right setup to support mysql database authentication and it used the installed mysql libs and php used its built in libs and they were different versions you'd have a version of apache / PHP that would crash randomly. For this reason and many others, it is considered a bad design decision to have included the connect libs in the PHP's source tree. It basically made it easier for junior sys admins to shoot themselves in the foot. :-)
Lincoln Yeoh wrote:
> At 12:33 AM 6/23/2003 -0500, nolan@celery.tssi.com wrote:
>> >
>> > 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.
>
> For me that's a matter of taste. I prefer to use = for case sensitive and
> lower(field)=lower('data') for case insensitive. I wonder if there is a
> difference between using lower vs upper for case insensitivity but I've
> never bothered to look deeply into it.
In some character sets there might be. The German sz-ligature for
example has no upper case equivalent, and in turkish the upper cases of
i and y with two dots are exchanged.
I prefer your example too and since we have functional indexes, it
doesn't affect performance at all.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Tom Lane wrote: <snip> >>We need to use this opportunity to encourage PHP folks to switch to >>PostgreSQL. > > Indeed. What can we do exactly? Hmmm... something along the lines of: "PostgreSQL v7.4 released, including enhanced MySQL->PostgreSQL migration tools". Would make for an interesting move... not playing friendly at all. Muaaa Haaaa Haaaa ;-> Regards and best wishes, Justin Clift > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On Tue, 24 Jun 2003, Justin Clift wrote: > Tom Lane wrote: > <snip> > >>We need to use this opportunity to encourage PHP folks to switch to > >>PostgreSQL. > > > > Indeed. What can we do exactly? > > Hmmm... something along the lines of: > > "PostgreSQL v7.4 released, including enhanced MySQL->PostgreSQL > migration tools". > > Would make for an interesting move... not playing friendly at all. > > Muaaa Haaaa Haaaa Postgresql, busily keeping our hands out of your wallet since (insert date here.) :-)
On 24 Jun 2003 at 1:02, Justin Clift wrote: > "PostgreSQL v7.4 released, including enhanced MySQL->PostgreSQL > migration tools". On this very note, I'm attempting to migrate MySQL database to PostgreSQL right now. I'm getting stuck on data such as this: (155,'<HTML>tr -d \"\015\" < dosfile.txt\r\n</HTML>',155) Where can I find this migration tool? The one I was using was pgAdmin II via ODBC. -- Dan Langille : http://www.langille.org/
On Mon, 23 Jun 2003, Dan Langille wrote: > On 24 Jun 2003 at 1:02, Justin Clift wrote: > > > "PostgreSQL v7.4 released, including enhanced MySQL->PostgreSQL > > migration tools". > > On this very note, I'm attempting to migrate MySQL database to > PostgreSQL right now. I'm getting stuck on data such as this: > > (155,'<HTML>tr -d \"\015\" < dosfile.txt\r\n</HTML>',155) > > Where can I find this migration tool? The one I was using was pgAdmin > II via ODBC. It's in the contrib/mysql directory in the source file. the name of the script is mysql2pgsql. Don't kow how well it will work, as I've never migrated from MySQL to Postgresql myself.
Hey!
You stole my favorite laugh!
My second favortie one is 'Nya, Nya, Nyaaaa' (Snidely Whiplash of Bulwinkle fame)
Justin Clift wrote:
> Tom Lane wrote:
> <snip>
>
>>> We need to use this opportunity to encourage PHP folks to switch to
>>> PostgreSQL.
>>
>>
>> Indeed. What can we do exactly?
>
>
> Hmmm... something along the lines of:
>
> "PostgreSQL v7.4 released, including enhanced MySQL->PostgreSQL
> migration tools".
>
> Would make for an interesting move... not playing friendly at all.
>
> Muaaa Haaaa Haaaa
>
> ;->
>
> Regards and best wishes,
>
> Justin Clift
>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that your
>> message can get through to the mailing list cleanly
>
>
>
>
> ---------------------------(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
>
> We require ~* syntax for that, or upper()/lower(). Slowly the light dawns! If I anchor a ~ search on both ends, it is the same search as =. Duh! I converted the prototype over to use ~ and it is running much faster. I'll try to do some detailed timings against MySQL tonight. -- Mike Nolan
See the FAQ item about index usage. You have to anchor the start only. --------------------------------------------------------------------------- nolan@celery.tssi.com wrote: > > We require ~* syntax for that, or upper()/lower(). > > Slowly the light dawns! > > If I anchor a ~ search on both ends, it is the same search as =. > > Duh! > > I converted the prototype over to use ~ and it is running much faster. > I'll try to do some detailed timings against MySQL tonight. > -- > Mike Nolan > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Tom Lane wrote: > 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. On that note, last I read, MySQL is planning to offer PHP as a language for writing stored procedures when the features is made available in the 5.0 release. Erik
Tom Lane wrote: > >>We need to use this opportunity to encourage PHP folks to switch to >>PostgreSQL. > > > Indeed. What can we do exactly? Write a migration tutorial and post it to the major web development sites (webmonkey, sitepoint.com, devshed.com, phpbuilder.com, etc). That's where PHP users are most-influenced. These tutorials have enabled so many people to get into server-side scripting, and most of the tutorials use MySQL. (I speak as a former full-time PHP programmer who did use MySQL for PHP app development, because that's how I learned it.) And, to avoid the connotation of bias, whomever writes such a migration tutorial might want to suggest using the PEAR:DB abstraction layer to avoid migration hassles in the future. http://pear.php.net/ Erik
Nolan, > 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? Send it to PGSQL-PERFORMANCE. I'll be very surprised if we can't beat MySQL's time; we usually can on anything but aggregates. -- -Josh Berkus Aglio Database Solutions San Francisco
In all this talk I haven't seen one post on the PHP-Dev mailing list about getting PostgreSQL enabled by default. If you want it on, you need to talk to them about it not amongst each other. >Tom Lane wrote: > >> >>>We need to use this opportunity to encourage PHP folks to switch to >>>PostgreSQL. >> >>Indeed. What can we do exactly? Chris.
OK, who is active in that community? --------------------------------------------------------------------------- Chris Smith wrote: > In all this talk I haven't seen one post on the PHP-Dev mailing list about > getting PostgreSQL enabled by default. > > If you want it on, you need to talk to them about it not amongst each other. > > > >Tom Lane wrote: > > > >> > >>>We need to use this opportunity to encourage PHP folks to switch to > >>>PostgreSQL. > >> > >>Indeed. What can we do exactly? > > Chris. > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Mike, I'd be thrilled to answer all of your performance/indexing questions .... on an appropriate list (PERFORMANCE or SQL). -- -Josh Berkus Aglio Database Solutions San Francisco
Erik Price wrote: > > Tom Lane wrote: > >> 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. > > On that note, last I read, MySQL is planning to offer PHP as a language > for writing stored procedures when the features is made available in the > 5.0 release. On that note, last I read, MySQL is planning to develop a completely new enterprise level database system that will be named MySQL again (for confusions sake) and include code from what's known so far as SAPDB. My question is, will MySQL 5.0 be based on MySQL 4.x and include code taken from SAPDB, will it be based on SAPDB with code snippets the other way around or will it be started from scratch and include one or the other piece from both? And, if MySQL 5.0 *might* not be based on MySQL 4.x, what happens to that codebase? Will the current MySQL core development team continue to add all the promised features to the old product line, or will the existing users have to migrate to a completely different MySQL system anyway? Note that we have seen that sort of "redo from scratch" before. Microsoft SQL Server is a really reliable database system after they got rid of the old crap, so it's not a bad decision per se. And if you don't play Crimson Skies on your database server, the Win2K + SQLServer combo makes a pretty decent production system. It's just that Microsoft had the "paying" user base that justifies to write useful conversion tools and that the old code base wasn't "that" extreme about violating standards. The future MySQL is supposed to support SAP's application platform, so it has to fail crash-me in order to be a little bit more spec compliant. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Mon, 23 Jun 2003 nolan@celery.tssi.com wrote: > > We require ~* syntax for that, or upper()/lower(). > > Slowly the light dawns! > > If I anchor a ~ search on both ends, it is the same search as =. 'K, now I'm confused ... what does your SELECT look like now?
On Mon, 23 Jun 2003, scott.marlowe wrote: > On Mon, 23 Jun 2003, Dan Langille wrote: > > > On 24 Jun 2003 at 1:02, Justin Clift wrote: > > > > > "PostgreSQL v7.4 released, including enhanced MySQL->PostgreSQL > > > migration tools". > > > > On this very note, I'm attempting to migrate MySQL database to > > PostgreSQL right now. I'm getting stuck on data such as this: > > > > (155,'<HTML>tr -d \"\015\" < dosfile.txt\r\n</HTML>',155) > > > > Where can I find this migration tool? The one I was using was pgAdmin > > II via ODBC. > > It's in the contrib/mysql directory in the source file. the name of the > script is mysql2pgsql. Don't kow how well it will work, as I've never > migrated from MySQL to Postgresql myself. But, if you happen to find problems, let us know so that we can strengthen it before v7.4 goes out ... :)
On 23 Jun 2003 at 22:19, The Hermit Hacker wrote: > On Mon, 23 Jun 2003, scott.marlowe wrote: > > > On Mon, 23 Jun 2003, Dan Langille wrote: > > > > > On 24 Jun 2003 at 1:02, Justin Clift wrote: > > > > > > > "PostgreSQL v7.4 released, including enhanced MySQL->PostgreSQL > > > > migration tools". > > > > > > On this very note, I'm attempting to migrate MySQL database to > > > PostgreSQL right now. I'm getting stuck on data such as this: > > > > > > (155,'<HTML>tr -d \"\015\" < dosfile.txt\r\n</HTML>',155) > > > > > > Where can I find this migration tool? The one I was using was pgAdmin > > > II via ODBC. > > > > It's in the contrib/mysql directory in the source file. the name of the > > script is mysql2pgsql. Don't kow how well it will work, as I've never > > migrated from MySQL to Postgresql myself. > > But, if you happen to find problems, let us know so that we can strengthen > it before v7.4 goes out ... :) Well, for what it's worth, I only need to convert the data, the tables will be created by the application. background: I'm converted an installation of http://www.phorum.org/ from mysql to pg. I'd rather Phorum create the PG tables, and just copy the data over. So far, the proof-of-concept has gone well. -- Dan Langille : http://www.langille.org/
> -----Original Message----- > From: Steve Lane [mailto:slane@moyergroup.com] > Sent: Monday, June 23, 2003 1:25 AM > To: Advocacy (PostgreSQL); PostgreSQL-general > Subject: Re: [pgsql-advocacy] interesting PHP/MySQL thread > > On 6/22/03 10:57 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > > > 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. Sad but true... > > 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. > Can I also suggest updating the documentation... Adding more prominent links to Bruce's online book, some simple "Get started with PostgreSQL" or maybe "Using PostgreSQL and PHP" type tutorials. My issues with the online (idoc and static) documentation is that it's hard for me to find what I want because of the separation into different "guides". The search feature is not very robust and I've gone so far as to download the docs and index them with ht://dig to get better results. The quality of the material is very good, so please don't get me wrong, I just think it's hard to find stuff. Both PHP and MySQL have well laid out docs, with PHP being the better of the two. I too started doing php stuff because of the need to do a web front end for a database. Prior to that I had no database experience at all and the wealth of PHP/MySQL tutorials made it easy for me to learn both. I hadn't even ever considered MySQL's lack of features as compared to higher end databases until I'd read an article from Tim Perdue on phpbuilder. A good candidate for a tutorial would be something that showed the use of simple PHP and SQL but containing examples with a sub-select and maybe a transaction. I like Bruce's examples for procedures/UDFs, but adding some SRF examples would be very useful. Bruce also has some great trigger/view/rule stuff that could be highlighted and possibly added to. > > > ======================================================= > Steve Lane > > Vice President > The Moyer Group > 14 North Peoria St Suite 2H > Chicago, IL 60607 > > Voice: (312) 433-2421 Email: slane@moyergroup.com > Fax: (312) 850-3930 Web: http://www.moyergroup.com > ======================================================= > -- Matthew Nuzum www.bearfruit.org cobalt@bearfruit.org
On Mon, 23 Jun 2003, Matthew Nuzum wrote: > The quality of the material is very good, so please don't get me wrong, > I just think it's hard to find stuff. Both PHP and MySQL have well laid > out docs, with PHP being the better of the two. Like everything else with this project, having ppl volunteer to fix deficiencies with the documentation is as important as improved testing or new features ...
Matt, > The quality of the material is very good, so please don't get me wrong, I > just think it's hard to find stuff. Both PHP and MySQL have well laid out > docs, with PHP being the better of the two. I certainly agree ... one of my goals (shared with some other people) is to eventually migrate all of the *accessory* documentation (techdocs, etc.) to a searchable system that's easy for non-programmers to contribute to and edit (i.e. SGML and CVS not required). Item #87 on Josh's ToDo list ... -- Josh Berkus Aglio Database Solutions San Francisco
so,
if Postgres were to have a manual like PHP's OLD manual(more next),
that would be a worthwhile contribution?
the new manuals seems to be drifting to using only GOOGLE listings.
MUCH less information on one page, not nearly as good search results as
the old one. I don't know why they are switching.
If google is going to do web searches for technical sites, it nees to
change the format.
> the new manuals seems to be drifting to using only GOOGLE listings. > MUCH less information on one page, not nearly as good search results as > the old one. I don't know why they are switching. I think I must be old-fashioned, or perhaps just getting old, I think online manuals are moving in the direction of becoming much harder for me to use. (I have yet to master the 'info' format that Linux has gone to, for example.) And my pet peeve of the month is software source distributions that include the documentation ONLY in HTML, which is OK IF you have Apache running on the system you're building the sources on and are willing to make the documentation directory available to Apache, but otherwise they're very hard to use. And while i'm on the subject, the only book (hard copy) I've got on PostgreSQL is the O'Reilly 'Practical PostgreSQL' book, now a bit dated, which has one of the worst indexes I've seen in a computer manual in years. It may be the worst index I've ever experienced in an O'Reilly book. (Sorry if Worsley or Drake are reading this, but that's my opinion.) The O'Reilly Reese/Yarger/King/Williams MySQL book has a much more comprehensive index, 23 pages compared to 7 for Worsley/Drake. I know that indexes are the last thing authors want to do (both literally and figuratively), but a good index makes the rest of the book much better. I've had a series of paper clips, bulldog clips and post-it notes marking the sections I tend to reuse frequently, because the index doesn't get you there. Most of the time I use the online manual, and I've got a few pages that aren't obvious from the table of contents bookmarked for the online manual, too. -- Mike Nolan
On Tue, 2003-06-24 at 04:58, Matthew Nuzum wrote: > I too started doing php stuff because of the need to do a web front end for > a database. Prior to that I had no database experience at all and the > wealth of PHP/MySQL tutorials made it easy for me to learn both. Then there are those of us who don't use PHP and don't think that it is the right choice for enterprise class database front ends... The force of PostgreSQL is that you have the right to choose the front end for webapps. I do not think that it is a good idea to tie just one to the database. PHP does not have a very good image when it comes to stability or security for example. It may be better marketing to say "you can use PostgreSQL with Zope, Tcl/tk, Python, JSP or even PHP". Rather than "you _HAVE_ to use PHP as a front end if you want to run MySQL because, if you want to use anything else, tough..." Cheers Tony Grant -- www.tgds.net Library management software toolkit, redhat linux on Sony Vaio C1XD, Dreamweaver MX with Tomcat and PostgreSQL
Nolan, > And my pet peeve of the month is software source distributions that > include the documentation ONLY in HTML, which is OK IF you have Apache > running on the system you're building the sources on and are willing to > make the documentation directory available to Apache, but otherwise > they're very hard to use. ??? You can look at an HTML file directy with any browser. If you're SSH-ing in to a remote system, use Lynx. Though I agree that providing both man and html would be nicer. > And while i'm on the subject, the only book (hard copy) I've got on > PostgreSQL is the O'Reilly 'Practical PostgreSQL' book, now a bit dated, > which has one of the worst indexes I've seen in a computer manual in years. > It may be the worst index I've ever experienced in an O'Reilly book. O'Reilly seems to be pretty hit-and-miss on this account. The Perl books are well-indexed, but "SQL in a Nutshell" has *no* index, perhaps because O'Reilly thought (wrongly) that it didn't need one because of the dictionary-like format. The O'Reilly label is not a guarentee of quality, just a general indicator. > I know > that indexes are the last thing authors want to do (both literally and > figuratively), but a good index makes the rest of the book much better. Authors seldom do the indexes themselves, as indexing is a black art known to few (and I have yet to see a really good index prepared by the author -- sorry, Bruce) Most frequently, the publisher hires a professional indexer and takes the cost out of the author's advance. When you find a really good index, you know that either: a) the author really cares about indexes; b) the publisher offered to pay for or split the cost of indexing, or at least made it a requirement of the book contract. Obviously, the publisher can really influence things through (b), so if I find a badly indexed book (and in my estimate 70% of tech books are badly indexed) I blame the publisher first. -- Josh Berkus Aglio Database Solutions San Francisco
Hello, > And, to avoid the connotation of bias, whomever writes such a > migration > tutorial might want to suggest using the PEAR:DB abstraction layer to > avoid migration hassles in the future. http://pear.php.net/ I don't like very much PEAR::DB since they have a HUGE lack in the errors messages accuracy... I've lost time due to an "Unknown error" displayed by PEAR::DB which was in fact a "Permission Denied" from PostgreSQL... :-/ I've already tell them about this problem, but they seemed to don't care about that. I'm waiting PHP5 (which should have a better object model with the possibility to throws some exceptions) and newer PEAR::DB that uses the PHP5 possibilities. So, I will still use the pg_ functions for several years again before having a new look on that ! :-) Cheers, --------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
Re: [pgsql-advocacy] Documentation quality WAS: interesting PHP/MySQL thread
From
nolan@celery.tssi.com
Date:
> ??? You can look at an HTML file directy with any browser. If you're SSH-ing > in to a remote system, use Lynx. Though I agree that providing both man and > html would be nicer. Try accessing a HTML file on a Linux system from a PC-based browser. Unless you have some kind of file sharing software running, which I generally don't because the only times I've ever been hacked into they got in through file sharing ports, you can't get there from here. > O'Reilly seems to be pretty hit-and-miss on this account. The Perl books are > well-indexed, but "SQL in a Nutshell" has *no* index, perhaps because > O'Reilly thought (wrongly) that it didn't need one because of the > dictionary-like format. The O'Reilly label is not a guarentee of quality, > just a general indicator. I think the 'Nutshell' books are a different breed of cat, none of them have ever had indexes worth mentioning. > Authors seldom do the indexes themselves, as indexing is a black art known to > few (and I have yet to see a really good index prepared by the author -- I've been somewhat involved in three book projects (two textbooks and one rule book), in all three case the authors did their own index. Maybe I've just had a good run of luck on the O'Reilly books I've bought, or maybe I haven't bought as many of them in the last three or four years as I used to. -- Mike Nolan
Dennis Gearon wrote: > so, > if Postgres were to have a manual like PHP's OLD manual(more next), > that would be a worthwhile contribution? > > the new manuals seems to be drifting to using only GOOGLE listings. > MUCH less information on one page, not nearly as good search results as > the old one. I don't know why they are switching. > > If google is going to do web searches for technical sites, it nees to > change the format. I think they are having performance problems and they are using google to shift the load...
Josh Berkus wrote: > Matt, > > >>The quality of the material is very good, so please don't get me wrong, I >>just think it's hard to find stuff. Both PHP and MySQL have well laid out >>docs, with PHP being the better of the two. > > > I certainly agree ... one of my goals (shared with some other people) is to > eventually migrate all of the *accessory* documentation (techdocs, etc.) to a > searchable system that's easy for non-programmers to contribute to and edit > (i.e. SGML and CVS not required). Yep. The present Techdocs site is kind of unmaintained, and the Plone area isn't being worked on either presently (lack of time). Finally got Bricolage installed on a system here at work to play around with. Reckon Josh'll be interested in that... :-) Regards and best wishes, Justin Clift > Item #87 on Josh's ToDo list ... >
I'm a Postgres and PHP newbie. I'm having a great deal of success with
my latest development effort having moved most of the logic from a
perl/php logic 'core' to postgres using plpgsql functions. (Thanks for
all that help, Josh).
I have a few comments to make on the idea of introducing people, PHP
developers especially, to postgresql. I'm not commenting here on how
easy it is to use PHP with postgres (it was transparent for me using
Debian) or whether or not to advocate the use of advanced features to
general users. Rather, it appears to me, that the PHP/Postgres
documentation and feature set should be improved.
1) PHP Documentation
The postgresql "write up" in the PHP html documentation doesn't give
a very good picture of the capabilities of postgres. While the PHP
docs aren't obviously a good place to write up the benefits of
plpgsql functions, some mention should be made to help differentiate
between the capabilities of MySQL and Postgres.
PHP documents:
ref.pgsql.html; ref.mysql.html
The MySQL examples given for database specific functions are useful
and to the point. The page on most of the Postgres functions are
sketchy. (No error number in Postgres...)
PHP documents:
function.mysql-errno.html; function.pg-result-error.html
PHP/Postgres provides a set of predefined constants, eg
PGSQL_COMMAND_OK and PGSQL_FATAL_ERROR. The use and parameters of
these constants is not described. The latter appears to provide
inconsistent results under my PHP 4.2.3 install.
2) PHP<->Postgres bugs
Apart from the PGSQL_FATAL_ERROR problem above, it would be good to
find a more simple, PHP-like, approach to catch exceptions and the
like. At the moment I believe one has to do something like:
function test () {
$sql = "
SELECT
count(n_id) as number
FROM
people
";
ob_start();
$result = pg_exec ($this->conn, $sql);
$this->status = pg_result_status($result);
ob_end_clean();
$this->result_checker();
if ($this->error != 0) {
echo "An error occured.\n";
exit;
}
...
return $this;
}
function result_checker () {
// horrible code to check for postgres exceptions
// status numbers sometimes show up
// ghosts of PGSQL_FATAL_ERROR?
if (! isset($this->status) or
($this->status == 5 or $this->status == 7)) {
$this->error = 1;
// wierdly, this always works
$this->error_msg = pg_last_error($this->conn);
return 1;
} else {
return 0;
}
}
On 22/06/03, Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> We need to use this opportunity to encourage PHP folks to switch to
> PostgreSQL.
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
nolan@celery.tssi.com wrote: >> ??? You can look at an HTML file directy with any browser. If you're SSH-ing >> in to a remote system, use Lynx. Though I agree that providing both man and >> html would be nicer. > > Try accessing a HTML file on a Linux system from a PC-based browser. > > Unless you have some kind of file sharing software running, which I > generally don't because the only times I've ever been hacked into they > got in through file sharing ports, you can't get there from here. If you work on Unix systems remotely on a regular base, you should have a Unix system as a workstation too. That way you can use ssh(1) to forward your X11 connections through a secure channel. A "second" PC can be implemented as a memory+disk upgrade together with a VMware license. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> Jan Wieck wrote: > If you work on Unix systems remotely on a regular base, you > should have > a Unix system as a workstation too. That way you can use ssh(1) to > forward your X11 connections through a secure channel. > > A "second" PC can be implemented as a memory+disk upgrade > together with > a VMware license. There also ssh clients which support X11 forwarding on a windows machine and since there are X11 servers for windows... You don't necessarily need a unix workstation. Apart from that, a (tight)vnc server might be less bandwidth consuming. Arjen
nolan@celery.tssi.com writes:
> And while i'm on the subject, the only book (hard copy) I've got on
> PostgreSQL is the O'Reilly 'Practical PostgreSQL' book, now a bit dated,
> which has one of the worst indexes I've seen in a computer manual in years.
> It may be the worst index I've ever experienced in an O'Reilly book.
> I've had a series of paper clips, bulldog clips and post-it notes marking
> the sections I tend to reuse frequently, because the index doesn't get
> you there. Most of the time I use the online manual, and I've got a
> few pages that aren't obvious from the table of contents bookmarked for
> the online manual, too.
The online manuals have an index. Could you write up a list of proposed
index additions for us? A few quick <indexentry> commands would be easy
enough to add to the doc sources --- the hard part is knowing what to
index.
regards, tom lane
Arjen van der Meijden wrote: >> Jan Wieck wrote: >> If you work on Unix systems remotely on a regular base, you >> should have >> a Unix system as a workstation too. That way you can use ssh(1) to >> forward your X11 connections through a secure channel. >> >> A "second" PC can be implemented as a memory+disk upgrade >> together with >> a VMware license. > > There also ssh clients which support X11 forwarding on a windows machine > and since there are X11 servers for windows... > You don't necessarily need a unix workstation. Apart from that, a > (tight)vnc server might be less bandwidth consuming. There are all kinds of stuff that works. VPN's, VNC's, you name it. I just have the best experience with having a Unix workstation when administering/working on remote Unix systems. Plus, banning your workstation(s) into virtual machines has another, not so obvious advantage. A backup of the workstation not only get's reduce to copying the files that make up the virtual disk ... you can restore it onto different hardware without confusing the device manager or going through config hassles. Ever restored a Windows backup onto a replacement notebook? Don't risk that "fun". Right now I have 1 Linux and 2 Win2K "systems" running inside of VMware on my notebook. With FreeBSD and Minix standing by. They are a happy little virtual network. But I think we're going a bit off topic here ... Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
The xserver in cygwin works just fine on all the systems I have tested, I have several linux boxen at home all headless, and I use Cygwin and XDMP to select which box I what to connect to and manage, seems as fast as using a local screen etc. Webmin is also a good tool, as it also has a POSTGRESQL managemnt module in it. ----- Original Message ----- From: "Jan Wieck" <JanWieck@Yahoo.com> To: "Arjen van der Meijden" <acm@tweakers.net> Cc: <nolan@celery.tssi.com>; "'Advocacy PostgreSQL'" <pgsql-advocacy@postgresql.org>; "'PostgreSQL-general'" <pgsql-general@postgresql.org> Sent: Tuesday, June 24, 2003 3:22 PM Subject: Re: [GENERAL] [pgsql-advocacy] Documentation quality WAS: interesting > Arjen van der Meijden wrote: > >> Jan Wieck wrote: > >> If you work on Unix systems remotely on a regular base, you > >> should have > >> a Unix system as a workstation too. That way you can use ssh(1) to > >> forward your X11 connections through a secure channel. > >> > >> A "second" PC can be implemented as a memory+disk upgrade > >> together with > >> a VMware license. > > > > There also ssh clients which support X11 forwarding on a windows machine > > and since there are X11 servers for windows... > > You don't necessarily need a unix workstation. Apart from that, a > > (tight)vnc server might be less bandwidth consuming. > > There are all kinds of stuff that works. VPN's, VNC's, you name it. I > just have the best experience with having a Unix workstation when > administering/working on remote Unix systems. > > Plus, banning your workstation(s) into virtual machines has another, not > so obvious advantage. A backup of the workstation not only get's reduce > to copying the files that make up the virtual disk ... you can restore > it onto different hardware without confusing the device manager or going > through config hassles. Ever restored a Windows backup onto a > replacement notebook? Don't risk that "fun". > > Right now I have 1 Linux and 2 Win2K "systems" running inside of VMware > on my notebook. With FreeBSD and Minix standing by. They are a happy > little virtual network. > > But I think we're going a bit off topic here ... > > > Jan > > -- > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
There are MANY browsers that come with Linux nowadays. the one mentioned, Lynx, is text based, so you don't even have tohave X running, or installed. nolan@celery.tssi.com wrote: >>??? You can look at an HTML file directy with any browser. If you're SSH-ing >>in to a remote system, use Lynx. Though I agree that providing both man and >>html would be nicer. > > > Try accessing a HTML file on a Linux system from a PC-based browser. > > Unless you have some kind of file sharing software running, which I > generally don't because the only times I've ever been hacked into they > got in through file sharing ports, you can't get there from here. > > >>O'Reilly seems to be pretty hit-and-miss on this account. The Perl books are >>well-indexed, but "SQL in a Nutshell" has *no* index, perhaps because >>O'Reilly thought (wrongly) that it didn't need one because of the >>dictionary-like format. The O'Reilly label is not a guarentee of quality, >>just a general indicator. > > > I think the 'Nutshell' books are a different breed of cat, none of them > have ever had indexes worth mentioning. > > >>Authors seldom do the indexes themselves, as indexing is a black art known to >>few (and I have yet to see a really good index prepared by the author -- > > > I've been somewhat involved in three book projects (two textbooks and > one rule book), in all three case the authors did their own index. Maybe > I've just had a good run of luck on the O'Reilly books I've bought, or maybe > I haven't bought as many of them in the last three or four years as I used > to. > -- > Mike Nolan > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Tue, 24 Jun 2003, Jan Wieck wrote: > nolan@celery.tssi.com wrote: > >> ??? You can look at an HTML file directy with any browser. If you're SSH-ing > >> in to a remote system, use Lynx. Though I agree that providing both man and > >> html would be nicer. > > > > Try accessing a HTML file on a Linux system from a PC-based browser. > > > > Unless you have some kind of file sharing software running, which I > > generally don't because the only times I've ever been hacked into they > > got in through file sharing ports, you can't get there from here. > > If you work on Unix systems remotely on a regular base, you should have > a Unix system as a workstation too. That way you can use ssh(1) to > forward your X11 connections through a secure channel. > > A "second" PC can be implemented as a memory+disk upgrade together with > a VMware license. I gave up on windows as a workable solution for interfacing to Unix systems long ago. It's like driving a mini-cooper around a track full of formula-1 cars.
> There also ssh clients which support X11 forwarding on a windows machine > and since there are X11 servers for windows... > You don't necessarily need a unix workstation. Apart from that, a > (tight)vnc server might be less bandwidth consuming. I've always figured X11 was proof that you can never have a fast enough CPU or enough memory. :-) I've tried X11 across a DSL line, it gives a whole new meaning to the word slow. I wound up creating a SSH tunnel to a browser inside the client's firewall, a solution neither of us are entirely happy with, as it may have some security concerns. We're probably setting up a VPN connection, but that's hung up in accounting. My point is that solutions which require other technology just to install or use, whether that be a simple browser or X11, can be self-defeating. If we're trying to sell ourselves to the PHP and/or MySQL communities, needing or even recommending X11 isn't the direction we should be going. -- Mike Nolan
Justin, > Yep. The present Techdocs site is kind of unmaintained, and the Plone > area isn't being worked on either presently (lack of time). Yeah. Thanks for installing Plone -- it gave me the chance to find out I didn't like it. <grin> > Finally got Bricolage installed on a system here at work to play around > with. Reckon Josh'll be interested in that... Really, Elein and I are going to build a Bric system for techdocs, Any Day Now ... -- Josh Berkus Aglio Database Solutions San Francisco
Folks, Can I propose that we move this thread to PGSQL-DOCS? And that you all join PGSQL-DOCS? I really would like to see more people formally involved in writing documentation for PostgreSQL, and will work hard to make it easier for people to contribute. -- Josh Berkus Aglio Database Solutions San Francisco
[OT] Windows v/s Unix [was Re: [pgsql-advocacy] Documentation quality WAS: interesting]
From
"Shridhar Daithankar"
Date:
On 24 Jun 2003 at 9:08, scott.marlowe wrote: > I gave up on windows as a workable solution for interfacing to Unix > systems long ago. It's like driving a mini-cooper around a track full of > formula-1 cars. I agree. But I would like to point out two thinks why unix lovers hate windows. 1. Pathetic text processing tools. 2. Pathetic kernel. 3. Pathetic GUI. Last comes from KDE. So not too much relevant to really old unix guys. But I miss KATE with multisession abilities. Konqueror when I select a URL to open it automatically, no matter source of it and all kind of other goodies..multiple desktop is one of them.. For 1, cygwin could do it's best. I use mksnt as it is required by my day job. Anything is almost OK compared to windows command line. But 2. is killer. I can do a grep search in a 120MB dump in less than 3-4 min. on linux if files aren't cached and in less than 30 sec. if they are cached. Nothing on windows comes anywhere close to it even on logarithmic scale. I miss linux..:-( really bad..:-( Bye Shridhar -- "On a normal ascii line, the only safe condition to detect is a 'BREAK'- everything else having been assigned functions by Gnu EMACS."(By Tarl Neustaedter)
On Tue, 24 Jun 2003 nolan@celery.tssi.com wrote: > > There also ssh clients which support X11 forwarding on a windows machine > > and since there are X11 servers for windows... > > You don't necessarily need a unix workstation. Apart from that, a > > (tight)vnc server might be less bandwidth consuming. > > I've always figured X11 was proof that you can never have a fast enough > CPU or enough memory. :-) > > I've tried X11 across a DSL line, it gives a whole new meaning to the > word slow. I wound up creating a SSH tunnel to a browser inside the > client's firewall, a solution neither of us are entirely happy with, > as it may have some security concerns. We're probably setting up a VPN > connection, but that's hung up in accounting. > > My point is that solutions which require other technology just to install > or use, whether that be a simple browser or X11, can be self-defeating. > > If we're trying to sell ourselves to the PHP and/or MySQL communities, > needing or even recommending X11 isn't the direction we should be going. No argument there. It would be nice to have a standard text formatted set of docs for users who need a lowest common denominator type setup.
On Tuesday 24 June 2003 11:47 am, Josh Berkus wrote: > Folks, > > Can I propose that we move this thread to PGSQL-DOCS? And that you all > join PGSQL-DOCS? I really would like to see more people formally > involved in writing documentation for PostgreSQL, and will work hard to > make it easier for people to contribute. Agreed. I am starting a list of what can/need to be indexed on pgsql docs site as I'm working to build a new site with pgsql as a backend. I think Tom Lane suggested that.. I would like to know where I can send the list. It'll probably somekind of a work in progress. So if everyone else can contribute, it'll be so much faster. Probably a semi-automated system for handling those lists? RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN ------------------------------------------------- /"\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ ------------------------------------------------- Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. -------------------------------------------------
On Tue, 2003-06-24 at 12:40, Reuben D. Budiardja wrote: > On Tuesday 24 June 2003 11:47 am, Josh Berkus wrote: > > Folks, > > > > Can I propose that we move this thread to PGSQL-DOCS? And that you all > > join PGSQL-DOCS? I really would like to see more people formally > > involved in writing documentation for PostgreSQL, and will work hard to > > make it easier for people to contribute. > > Agreed. I am starting a list of what can/need to be indexed on pgsql docs site > as I'm working to build a new site with pgsql as a backend. I think Tom Lane > suggested that.. I would like to know where I can send the list. It'll Best place to send the list / discuss it would be the pgsql-docs@ mailing list. If nobody else picks it up, I would be happy to integrate the additional index entries -- but I won't be much of a source to confirm they're correct or worth while. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Hi Rory,
Do you want to whip up an initial "improved" set of info to include in
the PHP docs as you're mentioning?
At least one of the PostgreSQL Community members (Conni) has write
access to the official PHP Docs repository and has volunteered to
commit improvements like this for us.
Regards and best wishes,
Justin Clift
Rory Campbell-Lange wrote:
> I'm a Postgres and PHP newbie. I'm having a great deal of success with
> my latest development effort having moved most of the logic from a
> perl/php logic 'core' to postgres using plpgsql functions. (Thanks for
> all that help, Josh).
>
> I have a few comments to make on the idea of introducing people, PHP
> developers especially, to postgresql. I'm not commenting here on how
> easy it is to use PHP with postgres (it was transparent for me using
> Debian) or whether or not to advocate the use of advanced features to
> general users. Rather, it appears to me, that the PHP/Postgres
> documentation and feature set should be improved.
>
> 1) PHP Documentation
>
> The postgresql "write up" in the PHP html documentation doesn't give
> a very good picture of the capabilities of postgres. While the PHP
> docs aren't obviously a good place to write up the benefits of
> plpgsql functions, some mention should be made to help differentiate
> between the capabilities of MySQL and Postgres.
>
> PHP documents:
> ref.pgsql.html; ref.mysql.html
>
> The MySQL examples given for database specific functions are useful
> and to the point. The page on most of the Postgres functions are
> sketchy. (No error number in Postgres...)
>
> PHP documents:
> function.mysql-errno.html; function.pg-result-error.html
>
> PHP/Postgres provides a set of predefined constants, eg
> PGSQL_COMMAND_OK and PGSQL_FATAL_ERROR. The use and parameters of
> these constants is not described. The latter appears to provide
> inconsistent results under my PHP 4.2.3 install.
>
> 2) PHP<->Postgres bugs
>
> Apart from the PGSQL_FATAL_ERROR problem above, it would be good to
> find a more simple, PHP-like, approach to catch exceptions and the
> like. At the moment I believe one has to do something like:
>
> function test () {
> $sql = "
> SELECT
> count(n_id) as number
> FROM
> people
> ";
>
> ob_start();
> $result = pg_exec ($this->conn, $sql);
> $this->status = pg_result_status($result);
> ob_end_clean();
>
> $this->result_checker();
> if ($this->error != 0) {
> echo "An error occured.\n";
> exit;
> }
> ...
> return $this;
> }
>
> function result_checker () {
> // horrible code to check for postgres exceptions
> // status numbers sometimes show up
> // ghosts of PGSQL_FATAL_ERROR?
> if (! isset($this->status) or
> ($this->status == 5 or $this->status == 7)) {
> $this->error = 1;
> // wierdly, this always works
> $this->error_msg = pg_last_error($this->conn);
> return 1;
> } else {
> return 0;
> }
> }
>
>
> On 22/06/03, Bruce Momjian (pgman@candle.pha.pa.us) wrote:
>
>>We need to use this opportunity to encourage PHP folks to switch to
>>PostgreSQL.
>
>
From the SQLite FAQ Item (7)
(7) Can multiple applications or multiple instances of the same application access a single database file at the same time?
(7) Can multiple applications or multiple instances of the same application access a single database file at the same time?
Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at once.
Alvaro Herrera wrote:
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.