persistent postgres connections in PHP

[lawpoop]

Hello all!

I'm working on a PHP site using Postgres as a back-end. I have an
include at the top of each page that runs the pg_connect function.

I'm implementing some temporary tables, which I understand are
destroyed automatically at the end of the session. It seems to me
that
when I navigate to a new page, or re-load my existing page, I lose my
temporary tables. Is this because I am creating a new session at the
beginning of each page with my pg_connect include?

Also, are temporary tables globally visible, or only within that
session? I mean, can one user see another user's temporary tables?

[C.]

On 15 Jun, 15:53, [e-mail] wrote:

I'm implementing some temporary tables, which I understand are
destroyed automatically at the end of the session. It seems to me
that
when I navigate to a new page, or re-load my existing page, I lose my
temporary tables. Is this because I am creating a new session at the
beginning of each page with my pg_connect include?

I'm not specifically familiar with PostgreSQL, but persistent
connections are not going to solve your problem - connections are held
in a pool and allocated on a first come, first served basis - there's
no reason to expect that your PHP session will connect back to the
same DBMS session.

Also, are temporary tables globally visible, or only within that
session? I mean, can one user see another user's temporary tables?

They should only be visible within the current DBMS session - whether
persistent connections preserve the DBMS session....I don't know, but
(see above) the question is somewhat academic.

One solution would be to create a real table using a session
identifier (e.g. PHP session id) in the name. Of course you'd need to
handle the clean up from your own session handler.

HTH

C.

[lawpoop]

On Jun 15, 5:15 pm, "C." <[e-mail]:

On 15 Jun, 15:53, [e-mail] wrote:

I'm implementing some temporary tables, which I understand are
destroyed automatically at the end of the session. It seems to me
that
when I navigate to a new page, or re-load my existing page, I lose my
temporary tables. Is this because I am creating a new session at the
beginning of each page with my pg_connect include?

I'm not specifically familiar with PostgreSQL, but persistent
connections are not going to solve your problem - connections are held
in a pool and allocated on a first come, first served basis - there's
no reason to expect that your PHP session will connect back to the
same DBMS session.

I'm a little confused then. In my MySQL sites, I have an include at
the beginning of each page that connects to the MySQL server. I have
not had a problem connecting to temporary tables that I created on
other pages, which means that my access to them has persisted over
several 'mysql_connects'. Have I just been lucky that I maintained the
connection/session that had my temporary tables when I got a
connection from the pool? I just always happened to get the one that I
wanted?

However, in this postgres site that I am developing, I seem to lose my
connection to the temporary tables after I traverse another
pg_connect. I don't know if this is part of my php.ini, or what. But
it does seem to show different behavior between mysql_connect and
pg_connect.

[C.]

On 17 Jun, 15:46, [e-mail] wrote:

Have I just been lucky that I maintained the
connection/session that had my temporary tables when I got a
connection from the pool? I just always happened to get the one that I
wanted?

Yes
RTFM:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html
and
http://www.php.net/manual/en/feature...onnections.php

C.

[lawpoop]

On Jun 17, 3:03 pm, "C." <[e-mail]:

On 17 Jun, 15:46, [e-mail] wrote:

Have I just been lucky that I maintained the
connection/session that had my temporary tables when I got a
connection from the pool? I just always happened to get the one that I
wanted?

Yes
RTFM:http://dev.mysql.com/doc/refman/5.0/...ate-table.html
andhttp://www.php.net/manual/en/features.persistent-connections.php

C.

Then why would persistent connections seem to be failing in this case
with postgres?

[C.]

On 18 Jun, 14:41, [e-mail] wrote:

On Jun 17, 3:03 pm, "C." <[e-mail]:

Then why would persistent connections seem to be failing in this case
with postgres?

This is just conjecture - but maybe mysql uses first come/first served
while PG does round-robin.

Regardless, the way you are using persistent connections is
innapropriate.

C.