Thread: passing resource id's through functions

I seem to have figured out that pg_connect returns a resource ID that will
not pass into another function, and that pg_pconnect does.  I'm not too
keen on the persistent connections.  Can someone confirm this belief?  I'm
trying to remember where on php.net I read how to pass pg_connect
resources to other functions, and haven't found it at functions,
persistent connections, pg_connect or pg_pconnect.

Any help would be appreciated, and helpful :)

Chadwick Rolfs - cmr@gis.net
Cleveland State University - Student
Music Major - The Holden Arboretum Volunteer
Computer Programmer - Student Employee
--*I finally found powdered water;
I just can't figure out what to add to it*--

I never had any problems with pg_pconnect either.  When I switched to
pg_connect I only had isolated problems, most of the time I was able to
pass it to functions, but sometime the script would die.

I use the PHPLIB DB abstraction classes rather than pg_connect directly,
but I don't think that's a factor here.

    $dbc = new DB_Example    /* calls pg_connect() */
    $func1($dbc);
    $func2($dbc);

If func1 contains two calls to $dbc->query() (invokes pg_exec($dbc,..))
then func2 will fail ($dbc not a resource), otherwise func2 works properly.

However, I have not yet been able to properly resolve the problem.

Frank

At 10:44 AM 1/11/02 -0500, Chadwick Rolfs wrote:
>I seem to have figured out that pg_connect returns a resource ID that will
>not pass into another function, and that pg_pconnect does.  I'm not too
>keen on the persistent connections.  Can someone confirm this belief?  I'm
>trying to remember where on php.net I read how to pass pg_connect
>resources to other functions, and haven't found it at functions,
>persistent connections, pg_connect or pg_pconnect.
>
>Any help would be appreciated, and helpful :)
>
>Chadwick Rolfs - cmr@gis.net
>Cleveland State University - Student
>Music Major - The Holden Arboretum Volunteer
>Computer Programmer - Student Employee
>--*I finally found powdered water;
>I just can't figure out what to add to it*--
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

On Fri, 11 Jan 2002, Frank Bax wrote:

> I never had any problems with pg_pconnect either.  When I switched to

Thanks for the tip!  I think we'll eventually get around to writing our
own libs for php/postgresql, I'll check out PHPLIB DB for examples (or
convince them to use it:))
I've had problems with people making too many pconnections, and couldn't
even psql into postgresql.  Apache needs to be reset at that point, and
there doesn't seem to be any new function to kill pconnections....you've
never had problems with that?

Chadwick Rolfs - cmr@gis.net
Cleveland State University - Student
Music Major - The Holden Arboretum Volunteer
Computer Programmer - Student Employee
--*I finally found powdered water;
I just can't figure out what to add to it*--

On Fri, Jan 11, 2002 at 10:44:05AM -0500, Chadwick Rolfs wrote:
> I seem to have figured out that pg_connect returns a resource ID that will
> not pass into another function, and that pg_pconnect does.  I'm not too
> keen on the persistent connections.  Can someone confirm this belief?  I'm
> trying to remember where on php.net I read how to pass pg_connect
> resources to other functions, and haven't found it at functions,
> persistent connections, pg_connect or pg_pconnect.

    Hmm--I've never had any problems with passing the database connection
handle from pg_connect into pg_exec (the only place you can really use it,
as far as I know).

    On the other hand, I did have some problems with a multi-virtualhosted
system where persistent connections were being passed back and forth between
two different hosts with different databases, which confused some of the
transaction-handling bits.

    (of course, I'm still not sure how much of a benefit there is with
persistant connections if you db is local [like most people's are], but that's
one to argue about somewhere else).


--
Adam Haberlach         | Who buys an eight-processor machine and then
adam@newsnipple.com    | watches 30 movies on it all at the same time?
http://newsnipple.com  | Beats me.  They told us they could sell it, so
                       | we made it.       -- George Hoffman, Be Engineer

When I say I didn't have problems with pconnect; I meant I never had the
resource-id problem with pg_pconnect that you described in your initial
message.  I still have the resource-id problem when using pg_connect
(called via phplib), but I work around it for now by making my $dbc
variable global.  I would still prefer to see a resolution to the problem
as you initially described it.

I had the "too many connections" problem with pconnect under apache as my
number of databases increased.  Each apache child maintains it's own list
of permanent connections (also see below).  I have a database for each
domain being hosted on my server, so number of databases multipled by
number of children finally got up to the max number of connections in
postgres/apache.  At this point, I changed the phplib code to use
pg_connect instead of pg_pconnect.

pconnect will save you time making connection to psql, that much is true
and stated in all kinds of docs.  What they don't tell you is that the
saving when pgsql is on the same box is marginal.  In a case where you have
a huge site and a single database on a separate database server, then
pconnect makes sense to save the connect time between the two boxes.  At
the other end of the scale is many databases on the same server as your web
server; in this case pg_connect makes more sense to reduce total number of
connections.  In between is the usual grey area.

What a minute...  When I said each apache child process keeps it's own list
of p-connections, I should also add that a new connection is created
whenever the database name or userid changes.  I tend to forget this part,
because I create a userid per database and all accesses from apache to a
database uses that userid.  In order words, I never have the situation
where a single database is accessable by two users.

Although there is no utility to 'delete' permanent connections, they will
go away when not used, I don't how how long exacly, but it was longer than
a hour and less than a day.

Frank

At 11:25 AM 1/11/02 -0500, Chadwick Rolfs wrote:
>On Fri, 11 Jan 2002, Frank Bax wrote:
>> I never had any problems with pg_pconnect either.  When I switched to
>
>Thanks for the tip!  I think we'll eventually get around to writing our
>own libs for php/postgresql, I'll check out PHPLIB DB for examples (or
>convince them to use it:))
>I've had problems with people making too many pconnections, and couldn't
>even psql into postgresql.  Apache needs to be reset at that point, and
>there doesn't seem to be any new function to kill pconnections....you've
>never had problems with that?
>
>Chadwick Rolfs - cmr@gis.net
>Cleveland State University - Student
>Music Major - The Holden Arboretum Volunteer
>Computer Programmer - Student Employee
>--*I finally found powdered water;
>I just can't figure out what to add to it*--

At 10:33 AM 1/11/02 -0800, Adam Haberlach wrote:
>On Fri, Jan 11, 2002 at 10:44:05AM -0500, Chadwick Rolfs wrote:
>> I seem to have figured out that pg_connect returns a resource ID that will
>> not pass into another function, and that pg_pconnect does.  I'm not too
>> keen on the persistent connections.  Can someone confirm this belief?  I'm
>> trying to remember where on php.net I read how to pass pg_connect
>> resources to other functions, and haven't found it at functions,
>> persistent connections, pg_connect or pg_pconnect.
>
>    Hmm--I've never had any problems with passing the database connection
>handle from pg_connect into pg_exec (the only place you can really use it,
>as far as I know).

The problem is not with passing the connection hanlde to pg_exec exactly.
If you create the connection hanlde in one function, then pass that handle
to another function that actually does the call to pg_exec, then sometimes
there are problems.  In my own case, I figured out if after creating the
connection hanlde, I call two functions that each call pg_exec, then this
normally works, but when a function makes two calls to pg_exec, then the
NEXT function has a problem.

$dbc = pg_connect(); func1($dbc); func2($dbc);
This works fine if func1() and func2() each contain a single call to pg_exec.
If func1() contains two calls to pg_exec(), then func2 fails on call to
pg_exec.

I should also point out, that this was never a problem before I did
upgrades in October.  Unfortunately, I upgraded OpenBSD, pgsql, php(3->4)
and phplib all during the same week.

Frank

On Sat, 2002-01-12 at 04:44, Chadwick Rolfs wrote:
> I seem to have figured out that pg_connect returns a resource ID that will
> not pass into another function, and that pg_pconnect does.  I'm not too
> keen on the persistent connections.  Can someone confirm this belief?  I'm
> trying to remember where on php.net I read how to pass pg_connect
> resources to other functions, and haven't found it at functions,
> persistent connections, pg_connect or pg_pconnect.
>
> Any help would be appreciated, and helpful :)

I run all my calls to pg_Exec inside a wrapper function which logs the
query to syslog if it fails for some reason, or if it takes unreasonably
long to execute (this has been a great aid to debugging and performance
improvement).  This means that _every_ time I want to execute a query I
am passing the appropriate database connection into my wrapper function.

I don't see any difference here between using pg_connect vs. pg_pconnect
for connecting to the database, other than performance.  I have recently
started to migrate back from pg_pconnect to pg_connect because I can get
the same performance without the resource overhead now that I use
DBBalancer to provide connection pooling.

It might be that I don't see any difference because connecting to the
database(s) is something I do globally, rather than inside a function,
or through instantiating an object.

If you use persistent connections, Apache has a "MaxRequestsPerChild"
setting, after which the Apache client process will exit, closing any
PHP persistent PostgreSQL connections as well.  The trouble is, that
setting this to an arbitrarily low number will reduce the benefit from
persistent connections in the first place, setting it arbitrarily high
can cause a problem if your application tickles any memory leaks in
Apache or PHP.

Cheers,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?

--- Culley Harrelson <Culley_Harrelson@pgn.com> wrote:
> DBBalancer looks interesting.  Looks like you are part of the
> development team with this project?  What sort of performance
> difference to you see?  When I made the decision to go with
> php/postgres as my primary development technologies I never even
> started using persistant connections because of all the mixed
> reviews.  The database is local to the web server and performance
> is fine.  I would be interested in hearing more about this
> connection pooling though...

There is another connection pooling software piece that supports many
more databases that you might want to take a look at.  It is called
SQL Relay and it works with PHP, C++, Perl, Java, etc:

http://www.firstworks.com/sqlrelay.html

Brent

__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/

Yes, that's it.  Using the $GLOBALS array works just fine, and I don't
have a bunch of pconnections cluttering up the process list, apache, or
postgresql.  I don't think persistent connections are meant to be _faster_
as much as they are meant for cutting down on redundant connections.
Unfortunately, I'm getting a bunch of redundant connections when pconnect
is used.  Since I don't administer the server, and the ones who do are too
busy to experement, I'll have to deal with it.  I'd like to know if
anyone else is having the same problem with pconnect:

pg_pconnect is in ONE file referred to by all of my other files.  So there
is only one connect string.  It can't change unless I edit the connection
file between invocations (let's just say it's STATIC)

when I go to pg_exec with the pg_pconnect resource, I get a seperate
connection for each exec!  This is clearly not supposed to be the case
acoording to http://www.php.net/manual/en/function.pg-pconnect.php

setting apache to only accept a certain amount of links is a good work
around, but what about everyone else?  Won't my connections just get
kicked off if other pconnections take over?  when I look at the postgres
processes, I should see _ONE_ persistent connection, not nine

The max conncection set is 64, so that won't be a problem, except that
each persistent connection takes a little bit of memory (2.5%), and enough
of them will actually slow the sever down.

Thanks for the great suggestion on using php's global namespace!

Regards,

On 12 Jan 2002, Andrew McMillan wrote:

> On Sat, 2002-01-12 at 04:44, Chadwick Rolfs wrote:
> > I seem to have figured out that pg_connect returns a resource ID that will
> > not pass into another function, and that pg_pconnect does.  I'm not too
> >
> > Any help would be appreciated, and helpful :)
>
> It might be that I don't see any difference because connecting to the
> database(s) is something I do globally, rather than inside a function,
> or through instantiating an object.

Chadwick Rolfs - cmr@gis.net
Cleveland State University - Student
Music Major - The Holden Arboretum Volunteer
Computer Programmer - Student Employee
--*I finally found powdered water;
I just can't figure out what to add to it*--

On Tue, 2002-01-15 at 06:27, Culley Harrelson wrote:
> DBBalancer looks interesting.  Looks like you are part of the development
> team with this project?

I am packaging it for Debian, and I am using it.  All the C++ coding is
being done by Daniel Varela.


> What sort of performance difference to you see?

For most things the performance is roughly equivalent to using
persistent connections.  If your resultsets are very large you will see
a performance impact because of the extra layer.

When I first started using it I benchmarked one of the applications I
use and decided it was around 5-10% faster than persistent connections,
but that was with small recordsets.

The main performance advantage for me is that I can have fewer database
connections than apache processes.  Each page served on my sites usually
has a mix of database content and static (image) content, and apache
doesn't need to have a database operating for the static stuff, so I
only need around 30% database clients vs apache clients.  Persistent
connections would have a 1 for 1 match, after they have been running for
a while.  This means less memory pressure on the system, which is the
big win for me.


> When I made the decision to go with php/postgres as my primary
> development technologies I never even started using persistant
> connections because of all the mixed reviews.  The database is local
> to the web server and performance is fine.  I would be interested in
> hearing more about this connection pooling though...

There are some things you have to watch out for:
 - use of temp tables
 - transactions that you forgot to commit / rollback

That's the price you pay :-)

The biggest advantage of DBBalancer is probably that it doesn't require
you to make any changes to your PHP, other than in the pg_connect
statement - it's just as if you were connecting to a different database
server, because it supports the PostgreSQL interface natively.  Other
connection pooling approaches may want you to load a different module
into PHP, or use a different set of PHP functions.  This means that the
layer is very thin, as well.

Cheers,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?

On Tue, 2002-01-15 at 11:15, Chadwick Rolfs wrote:
> Yes, that's it.  Using the $GLOBALS array works just fine, and I don't
> have a bunch of pconnections cluttering up the process list, apache, or
> postgresql.  I don't think persistent connections are meant to be _faster_
> as much as they are meant for cutting down on redundant connections.
> Unfortunately, I'm getting a bunch of redundant connections when pconnect
> is used.  Since I don't administer the server, and the ones who do are too
> busy to experement, I'll have to deal with it.  I'd like to know if
> anyone else is having the same problem with pconnect:
>
> pg_pconnect is in ONE file referred to by all of my other files.  So there
> is only one connect string.  It can't change unless I edit the connection
> file between invocations (let's just say it's STATIC)
>
> when I go to pg_exec with the pg_pconnect resource, I get a seperate
> connection for each exec!  This is clearly not supposed to be the case
> acoording to http://www.php.net/manual/en/function.pg-pconnect.php
>
> setting apache to only accept a certain amount of links is a good work
> around, but what about everyone else?  Won't my connections just get
> kicked off if other pconnections take over?  when I look at the postgres
> processes, I should see _ONE_ persistent connection, not nine
>
> The max conncection set is 64, so that won't be a problem, except that
> each persistent connection takes a little bit of memory (2.5%), and enough
> of them will actually slow the sever down.
>
> Thanks for the great suggestion on using php's global namespace!

With persistent connections you will (eventually) end up with one
postgresql client process for each apache server process.  If you set
the max connection limit below the max client limit of apache you will
end up with a machine that doesn't spiral out of control, but above that
limit your database driven content simply won't work.

Yes, the reason for using persistent connections definitely _is_
performance, but I don't think it is just connection time performance.
Longer running connections will have an element of caching which will
give further speed advantages.

Cheers,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?

On Tue, 2002-01-15 at 09:54, Brent R. Matzelle wrote:
> --- Culley Harrelson <Culley_Harrelson@pgn.com> wrote:
> > DBBalancer looks interesting.  Looks like you are part of the
> > development team with this project?  What sort of performance
> > difference to you see?  When I made the decision to go with
> > php/postgres as my primary development technologies I never even
> > started using persistant connections because of all the mixed
> > reviews.  The database is local to the web server and performance
> > is fine.  I would be interested in hearing more about this
> > connection pooling though...
>
> There is another connection pooling software piece that supports many
> more databases that you might want to take a look at.  It is called
> SQL Relay and it works with PHP, C++, Perl, Java, etc:
>
> http://www.firstworks.com/sqlrelay.html

SQL Relay looks pretty polished, but the problem I have in my case is
that it requires me to go back and change my application a bit too much.
The web page suggests it doesn't support PostgreSQL functions, for
example, which I use extensively.

I've spent all this time learning the PHP/PostgreSQL API, and writing my
application to use it, I'm not going to go back and rewriet to use SQL
Relay instead.  DBBalancer is completely transparent to PHP - change
your connect string (or not - you can make the change to your database
server instead) and you are off.

OTOH if I were developing a new site from scratch it would deserve much
more consideration.

Cheers,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?