Обсуждение: interesting PHP/MySQL thread
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) > There are rumours that MySQL AB does not want to provide a > license exemption. The only consequence is that we should > not steer users into their hands through simplified > deployment. > > Actually, we should warn users of MySQL 3 that they won't be > able to use new versions of MySQL under the same conditions, > and that they should better look at Interbase/PostgreSQL. > > - Sascha Joe
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? -- Josh Berkus Aglio Database Solutions San Francisco
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
> > We need to use this opportunity to encourage PHP folks to switch to > > PostgreSQL. > > Indeed. What can we do exactly? I think we need to use this opportunity to convince the PHP folks to level the playing field, rather than playing favourites with ANY one database... Chris
> 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 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. The only conceivable reason for that is poor query optimisation on the part of the database and query designers... > 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?) Huh? Tsearch in postgresql's contrib dir is as fast as if not faster than MySQL's FULLTEXT indexes in my experience... Don't tell me you're just using LIKE comparisions? I have searches using tsearch over 30000 food brands and decriptions that take about 100ms. > MySQL also does case independent text comparisions, and apparently ONLY > case-insensitive comparisons. What do you mean 'independent test comparisons'? Chris
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
> > 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
> 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%';" Nonsense. PostgreSQL indexes such a thing quite happily. I've used that a lot. > 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. Hmmm - that seems iffy to me - have they invented a new field of index theory? Chris
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 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
[ killing cross posting between general@ and advocacy@ ] > 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. :-) Or for Jr. Admins at www virtual hosting ISPs to setup PHP pizza boxes without having to install PostgreSQL. PostgreSQL needs a --client-only option to ./configure to be competitive in terms of cost of installation in that dept. -sc -- Sean Chittenden
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
>
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
I can pose the questions to the PHP guys and respond as best I can. I'll probably keep coming back here for answers so if that's no problem then I can tentatively stick my hand up and do it. At 06:59 PM 6/23/2003 -0400, Bruce Momjian wrote: >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 > >---------------------------(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 Chris Smith >> 92 Jarrett St Leichhardt, Sydney, NSW 2040 ...> T: + 61 2 9568 6866 F: + 61 2 9568 6733 W: http://www.squiz.net/ .....>> Open Source - Own it - Squiz.net ...../>
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, 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
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: [GENERAL] Documentation quality WAS: interesting PHP/MySQL thread
От
nolan@celery.tssi.com
Дата:
> ??? 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
Hi Bruce > OK, who is active in that community? You will see a lot of php-developers at LinuxTag to talk with ;-) The PHP-PostgreSQL-extension is maintained by Yasuo Ohgaki. Aren't you in Japan? So maybe you could meet him? His email is yohgaki@php.net. Regards Conni
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 #
Cornelia Boenigk wrote: > Hi Bruce > > > OK, who is active in that community? > > You will see a lot of php-developers at LinuxTag to talk with ;-) > > The PHP-PostgreSQL-extension is maintained by Yasuo Ohgaki. Aren't you > in > Japan? So maybe you could meet him? His email is yohgaki@php.net. No, I only visit Japan occasionally. -- 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
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
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
Вложения
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.
Hi Justin I'm writing to let you know that my availability to work on some new documentation has moved somewhat, from now to the middle of August. I have made contact with Connie (conversation noted at the end of this mail) and it appears that work could usefully be done on providing better examples and noting bugs on PHP postgres functions in the PHP documentation. I am happy to do this, and it might be the best way for me to contribute to the Postgres advocacy initiative, particularly as I have little experience of using MySQL and can hardly comment authoritatively on the differences between the systems. Another alternative is that I could write a newbie's gentle introduction to using pl/pgsql. With Josh's initial encouragement I have recently delivered a very successful project using Postgres functions. Development speed was greatly assisted by providing consistent database interfaces while the operations behind those interfaces could change. The system uses a combination of php and postgres, so I could hit some advocacy targets by describing the integration here! I'd be grateful to know if one or other of these pieces of work are desired/needed. Knowing who would review my work would also be useful. Kind regards Rory On 25/06/03, Justin Clift (justin@postgresql.org) wrote: > 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. > 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. ... > >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.... Conversation with Connie: Date: Tue, 1 Jul 2003 08:12:32 +0100 From: Rory Campbell-Lange <rory@campbell-lange.net> To: Cornelia Boenigk <c@cornelia-boenigk.de> Subject: Re: PostgreSQL - PHP manual Thanks for your mail. I hope to put together some suggestions at the end of July. I would be very grateful if you would have a look at them to see if there is anything useful there. All the best! Rory On 30/06/03, Cornelia Boenigk (c@cornelia-boenigk.de) wrote: > > Are you a contributor to the PostgreSQL/PHP > > documentation? > Something like that ;-) > I am a member of the translation team but have write access to the > whole repository. > > > My comments come from my > > perspective of trying to explain clearly the advantages of Postgres, > > while retaining the technical nature of the documentation. > I share your ideas ;-) > However if you are going to read the php-manual I think the decision > which database to use is done. > > > What sort of hint? ;) > e.g. for the description of the predefined constants. > > > I would be very happy if any of this > > work would benefit the PHP documentation. > Well there are a lot of really poorly documented functions. To improve > the manual it is fine to get some examples or experiences from users. > Or even bugs ;-) -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
Hi Rory, My feeling on this is that the most effective thing to do here would be to add information about PostgreSQL functions tothe PHP manual, and - perhaps even more importantly - to add information if possible to the PHP manual mentioning PostgreSQL in key places. For one small example, in the "Getting Started"->"Installation"->"Unix/Solaris install" section/page it mentions: "In addition, you will need to install (and possibly compile) any additional software specific to your configuration, suchas Oracle or MySQL." Updating small points like this to specifically mention PostgreSQL in the same place that others are mentioned should beaffective advocacy/promotion to newbies that read through these sections. i.e.: "In addition, you will need to install (and possibly compile) any additional software specific to your configuration, suchas Oracle, PostgreSQL or MySQL." An area of the PHP manual that seems very MySQL specific is the "FAQ: Frequently Asked Questions"->"Database issues" page,where it goes into detail on how to access MySQL from Windows. It would probably be worthwhile adapting that to also cover PostgreSQL properly too. Perhaps doing a search for "MySQL" and/or "Oracle" and/or "SQL" in the existing PHP manual, then compiling a hit list ofplaces where it will be effective to add mention of PostgreSQL, then doing it, would be the way to go? As another thought, maybe adding a "Using databases" section, with PostgreSQL examples, to the "Features" section of thePHP manual would be worthwhile too. What do you think? Regards and best wishes, Justin Clift Rory Campbell-Lange wrote: > Hi Justin > > I'm writing to let you know that my availability to work on some new > documentation has moved somewhat, from now to the middle of August. > > I have made contact with Connie (conversation noted at the end of this > mail) and it appears that work could usefully be done on providing > better examples and noting bugs on PHP postgres functions in the PHP > documentation. > > I am happy to do this, and it might be the best way for me to contribute > to the Postgres advocacy initiative, particularly as I have little > experience of using MySQL and can hardly comment authoritatively on the > differences between the systems. > > Another alternative is that I could write a newbie's gentle introduction > to using pl/pgsql. With Josh's initial encouragement I have recently > delivered a very successful project using Postgres functions. > Development speed was greatly assisted by providing consistent database > interfaces while the operations behind those interfaces could change. > The system uses a combination of php and postgres, so I could hit some > advocacy targets by describing the integration here! > > I'd be grateful to know if one or other of these pieces of work are > desired/needed. Knowing who would review my work would also be useful. > > Kind regards > Rory
Hi altogether > My feeling on this is that the most effective thing to do here would > be to add information about PostgreSQL functions to the PHP manual Maybe PHP-Group doesn't allow this, because it is not the purpose of the PHP manual. > perhaps even more > importantly - to add information if possible to the PHP manual > mentioning PostgreSQL in key places. Okay > An area of the PHP manual that seems very MySQL specific is the > "FAQ: Frequently Asked Questions"->"Database issues" page, where it > goes into detail on how to access MySQL from Windows. It would > probably be worthwhile adapting that to also cover PostgreSQL properly > too. I will take a look at it. > As another thought, maybe adding a "Using databases" section, with > PostgreSQL examples, to the "Features" section of the PHP manual would > be worthwhile too. I started to add examples for PostgreSQL. I fyou download the last english .chm-manual you will see them. The online manual isn't as new as the chm build. I will continue as soon as I get some answers from the developers because some things are not clear and I dont't want to write documentation and make examples of things I don't understand. > and it appears that work could usefully be done on providing > better examples and noting bugs on PHP postgres functions in the PHP > documentation. Better use the bugs interface at php.net and write bug-reports. Regards Conni
Rory, > Another alternative is that I could write a newbie's gentle introduction > to using pl/pgsql. With Josh's initial encouragement I have recently > delivered a very successful project using Postgres functions. > Development speed was greatly assisted by providing consistent database > interfaces while the operations behind those interfaces could change. > The system uses a combination of php and postgres, so I could hit some > advocacy targets by describing the integration here! I would be delighted to co-write an article on PHP + Data-Push Functions in PL/pgSQL architecture with you. For example, a fun one would be to do "Powerful search screens with PHP, and PL/PgSQL Set Returning Functions" Also, several magazines ... such as Linux Magazine and Linux Journal, and possible Open ... would be happy to print a multi-part series on a simple PHP/PostgreSQL application. Either way, I'll be happy to participate. Plus, we're starting the overhaul of Techdocs so that posting articles will be easier. -- Josh Berkus Aglio Database Solutions San Francisco
Hi altogether > My feeling on this is that the most effective thing to do here would > be to add information about PostgreSQL functions to the PHP manual Maybe PHP-Group doesn't allow this, because it is not the purpose of the PHP manual. > perhaps even more > importantly - to add information if possible to the PHP manual > mentioning PostgreSQL in key places. Okay > An area of the PHP manual that seems very MySQL specific is the > "FAQ: Frequently Asked Questions"->"Database issues" page, where it > goes into detail on how to access MySQL from Windows. It would > probably be worthwhile adapting that to also cover PostgreSQL properly > too. I will take a look at it. > As another thought, maybe adding a "Using databases" section, with > PostgreSQL examples, to the "Features" section of the PHP manual would > be worthwhile too. I started to add examples for PostgreSQL. I fyou download the last english .chm-manual you will see them. The online manual isn't as new as the chm build. I will continue as soon as I get some answers from the developers because some things are not clear and I dont't want to write documentation and make examples of things I don't understand. > and it appears that work could usefully be done on providing > better examples and noting bugs on PHP postgres functions in the PHP > documentation. Better use the bugs interface at php.net and write bug-reports. Regards Conni
Hi Josh On 31/07/03, Josh Berkus (josh@agliodbs.com) wrote: > > Another alternative is that I could write a newbie's gentle > > introduction to using pl/pgsql. With Josh's initial encouragement I > > have recently delivered a very successful project using Postgres > > functions. Development speed was greatly assisted by providing > > consistent database interfaces while the operations behind those > > interfaces could change. The system uses a combination of php and > > postgres, so I could hit some advocacy targets by describing the > > integration here! > > I would be delighted to co-write an article on PHP + Data-Push > Functions in PL/pgSQL architecture with you. > > For example, a fun one would be to do "Powerful search screens with > PHP, and PL/PgSQL Set Returning Functions" I have a little writing experience, although on the subject of architecture! I would be more than delighted to collaborate on writing something along the lines of what you suggest. A serious caveat is that I don't consider myself a good programmer. A possibly elegant approach to what you suggest is for us to dream up a PHP/Postgres task and for me as a relative newbie to provide a solution. You could then "crit" it (i.e. point out flaws and superior approaches), and this could become part of the basis for the article, or for explaining to readers why certain approaches have advantages over others. All the best, Rory -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>