Thread: Best WebInterface...?
I'm think of using PHP with Postgresql. Are there any limitations to PHP that will keep me from using PGsql to it fullest? Any recommendations? Herbie Pabst
* Herb Pabst <herbie@nextearth.com> [010129 14:00] wrote: > I'm think of using PHP with Postgresql. Are there > any limitations to PHP that will keep me from > using PGsql to it fullest? Any recommendations? I'm not sure if this is true, but I think php doesn't support cursors, so large queiries may have to be sucked into the webserver instead of processed one row at a time. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
On Monday 29 January 2001 17:13, Alfred Perlstein wrote: > * Herb Pabst <herbie@nextearth.com> [010129 14:00] wrote: > > I'm think of using PHP with Postgresql. Are there > > any limitations to PHP that will keep me from > > using PGsql to it fullest? Any recommendations? > > I'm not sure if this is true, but I think php doesn't support > cursors, so large queiries may have to be sucked into the > webserver instead of processed one row at a time. I've used cursors in php with no problems. -- -------- Robert B. Easter reaster@comptechnews.com --------- -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- ---------- http://www.comptechnews.com/~reaster/ ------------
"Best" is a squishy term. It all depends on your application, and the alternatives you are considering. It seems to me PHP won't support all of PostgreSQL's funky datatypes, and the object-relational model while the JDBC driver does, for instance. I use PHP for my own work, with transactions and large objects working fine. Ciao --Louis <louis@bertrandtech.on.ca> On Mon, 29 Jan 2001, Herb Pabst wrote: > I'm think of using PHP with Postgresql. Are there > any limitations to PHP that will keep me from > using PGsql to it fullest? Any recommendations? > > Herbie Pabst > >
On Mon, 29 Jan 2001, Herb Pabst wrote:
> I'm think of using PHP with Postgresql. Are there
> any limitations to PHP that will keep me from
> using PGsql to it fullest? Any recommendations?
It all depends on what you are planning to do. There really is no single
'best interface', IMHO.
-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
I think a relationship is like a shark. It has to constantly move forward
or it dies. Well, what we have on our hands here is a dead shark.
-- Woody Allen
Running Red Hat 7.0 kernel 2.2.16
PostgreSQL 7.0.3
Front End App is Cold Fusion
We only have about 22000 rows in the article table and it will grow quite a bit
larger.
When I turn debugging on (cold fusion) this query takes a while. Almost 3
sec...
rstDispThread (Records=6, Time=2982ms)
SQL =
SELECT a.id_article,
a.subject,
getNumPostsInThread(a.id_article,0) AS numPosts,
isThreadNew(a.id_article,'2001-01-2617:28:37',0) AS intThreadNew,
a.is_hidden,
a.is_locked,
a.reply_to
FROM ARTICLE a, USER_TAB ut
WHERE a.id_article IN (75254,76255,79262,84264,94273,94277)
AND a.author = ut.id_user
ORDER BY a.date_create ASC
I have tried numerous indexing techniques and starting up the database with
larger Buffer size, and even the dreaded -F option, but nothing seems to help.
The two functions getNumPostsInThread and isThreadNew are pretty quick. I have
found the date comparison to be slow and was wondering if that could be sped
up?
This is part of the isThreadNew function, when I strip everything out and only
leave the WHERE date_create >=
TO_TIMESTAMP('2001-01-2617:28:37',YYYY-MM-DDHH24:MI:SS) clause and only pass
one article id to it - the query still takes .5 sec!! Is there any known
slowness attributed with date comparisons, etc??
SELECT COUNT(id_article)
FROM ARTICLE
WHERE date_create >= TO_TIMESTAMP('2001-01-2617:28:37',YYYY-MM-DDHH24:MI:SS)
AND (id_article = 79262
OR reply_to = 79262);
Does anyone have any ideas how to speed this up??
I also read something about being able to create an index based on a function
or am I way off base again?
Thanks in Advance,
Rudy Laczkovich
* Robert McGinnis <fprefect@elite.net> [010130 07:45] wrote: > On Mon, 29 Jan 2001, Alfred Perlstein wrote: > > > I'm not sure if this is true, but I think php doesn't support > > cursors, so large queiries may have to be sucked into the > > webserver instead of processed one row at a time. > > > > > I have used cursors with php for over a year. The problem is that is does > not support them natively; any code that tests the call for pg_exec may > fail since the return is undefined. Here's how I write the code: > //Note this is off the top of my head, you code may vary :-) > [snip] > > Move forward and backward works as before the fetch. The part that I > don't like is the inability to check whether the transaction block querys > work; I had to console myself with counting the number of results I should > get for the same query. The bad part is that there is WAY to many queries > being executed; the good part is it save a great many lines of > quasi-convoluted logic whilst incrementing to the n or n+1 row of an > answer. I hope this helps, It's nice but not as nice as having true support so that one doesn't need to do all those redundant checks and queries. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
rudy wrote:
>
> Running Red Hat 7.0 kernel 2.2.16
> PostgreSQL 7.0.3
> Front End App is Cold Fusion
>
> We only have about 22000 rows in the article table and it will grow quite a bit
> larger.
> When I turn debugging on (cold fusion) this query takes a while. Almost 3
> sec...
>
> rstDispThread (Records=6, Time=2982ms)
> SQL =
> SELECT a.id_article,
> a.subject,
> getNumPostsInThread(a.id_article,0) AS numPosts,
> isThreadNew(a.id_article,'2001-01-2617:28:37',0) AS intThreadNew,
> a.is_hidden,
> a.is_locked,
> a.reply_to
> FROM ARTICLE a, USER_TAB ut
> WHERE a.id_article IN (75254,76255,79262,84264,94273,94277)
> AND a.author = ut.id_user
> ORDER BY a.date_create ASC
Don't use IN ( ... ), use EXISTS.
See the FAQ for more details, but IN won't use an index.
Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
On Mon, 29 Jan 2001, Alfred Perlstein wrote:
> Date: Mon, 29 Jan 2001 14:13:38 -0800
> From: Alfred Perlstein <bright@wintelcom.net>
> To: Herb Pabst <herbie@nextearth.com>
> Cc: pgsql-list <pgsql-novice@postgresql.org>
> Subject: Re: Best WebInterface...?
>
> * Herb Pabst <herbie@nextearth.com> [010129 14:00] wrote:
> > I'm think of using PHP with Postgresql. Are there
> > any limitations to PHP that will keep me from
> > using PGsql to it fullest? Any recommendations?
>
> I'm not sure if this is true, but I think php doesn't support
> cursors, so large queiries may have to be sucked into the
> webserver instead of processed one row at a time.
>
>
I have used cursors with php for over a year. The problem is that is does
not support them natively; any code that tests the call for pg_exec may
fail since the return is undefined. Here's how I write the code:
//Note this is off the top of my head, you code may vary :-)
$q = "begin";
$count = "select count (*) from clients where active='t'";
$qry = "declare fred cursor for select * from clients where active='t';
$fetch = "fetch 20 from fred";
$fini = "commit";
if(! $res1 = pg_exec( $connect, $count ) ){
return 0;
}
if ( 0 < pg_numrows( $res1 ) ){
pg_exec( $connect, $q ); //undefined behavior
pg_exec( $connect, $qry ); //undefined behavior
if (! $res= pg_exec( $connect, $q ) ){
return 0;
}
pg_exec($connect, $fini); //undefined bevhavior
if ( 0 >= pg_numrows( $res ) ){
return 0;
}
do_something ( $res )
}
pg_free_result( $res );
pg_free_result( $res1);
Move forward and backward works as before the fetch. The part that I
don't like is the inability to check whether the transaction block querys
work; I had to console myself with counting the number of results I should
get for the same query. The bad part is that there is WAY to many queries
being executed; the good part is it save a great many lines of
quasi-convoluted logic whilst incrementing to the n or n+1 row of an
answer. I hope this helps,
--+*==#==*+----+*==#==*+----+*==#==*+----+*==#==*+----+*==#==*+----+*==#==*+--
fprefect@dustpuppy.net Currently doing fieldwork as:
Elite.net Support Robert McGinnis
"So an object class contains the data, plus everything the class can do.
But is it a direct object, indirect object, or object of a preposition?"6/7/97