Thread: sessions and prepared statements

in PHP for example, where there are multiple sessions and which you get is random:<br /><br />how do you know if the
sessionyou're in has prepared a particular statement?<br /><br />and/or how do you get a list of prepared
statements?<br/><br />last, is there any after login trigger that one could use to prepare statements the session would
need?or is this a dumb idea?<br /><br />thanks<br />chester<br /><p>
__________________________________________________<br/>Do You Yahoo!?<br />Tired of spam? Yahoo! Mail has the best spam
protectionaround <br />http://mail.yahoo.com  

On Jun 15, 2006, at 11:49 AM, chester c young wrote:

> in PHP for example, where there are multiple sessions and which you  
> get is random:
>
> how do you know if the session you're in has prepared a particular  
> statement?
>
> and/or how do you get a list of prepared statements?
>
> last, is there any after login trigger that one could use to  
> prepare statements the session would need? or is this a dumb idea?

If you are using pooled connections, I don't think there is a  
reasonable way you could managed prepared statements across requests.  
You'll probably want to just prepare the ones you need for the  
current request and discard them when the request ends.

I have a short article where you might find some useful information  
for managing prepared statements:

http://pgedit.com/resource/php/pgfuncall

You might also post your question to PostgreSQL PHP list -- probably  
more PHP expertise there.

Best,

John




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Fri, 2006-06-16 at 08:27 -0400, John DeSoi wrote:
> On Jun 15, 2006, at 11:49 AM, chester c young wrote:
> 
> > in PHP for example, where there are multiple sessions and which you  
> > get is random:
> >
> > how do you know if the session you're in has prepared a particular  
> > statement?
> >
> > and/or how do you get a list of prepared statements?
> >
> > last, is there any after login trigger that one could use to  
> > prepare statements the session would need? or is this a dumb idea?
> 
> If you are using pooled connections, I don't think there is a  
> reasonable way you could managed prepared statements across requests.  
> You'll probably want to just prepare the ones you need for the  
> current request and discard them when the request ends.

Temporary tables.

BEGIN;
SAVEPOINT;
SELECT * FROM temporary_prepared_statement;
ROLLBACK TO SAVEPOINT < on failure>;
CREATE TEMPORARY TABLE temporary_prepared_statement ...;
COMMIT;

Now you have a place to store and retrieve prepared connection state for
the lifetime of the database backend provided PHP doesn't remove
temporary tables on the connection.

> I have a short article where you might find some useful information  
> for managing prepared statements:
> 
> http://pgedit.com/resource/php/pgfuncall
> 
> You might also post your question to PostgreSQL PHP list -- probably  
> more PHP expertise there.
> 
> Best,
> 
> John
> 
> 
> 
> 
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 
-- 

On Fri, Jun 16, 2006 at 08:55:16AM -0400, Rod Taylor wrote:
> BEGIN;
> SAVEPOINT;
> SELECT * FROM temporary_prepared_statement;
> ROLLBACK TO SAVEPOINT < on failure>;
> CREATE TEMPORARY TABLE temporary_prepared_statement ...;
> COMMIT;
> 
> Now you have a place to store and retrieve prepared connection state for
> the lifetime of the database backend provided PHP doesn't remove
> temporary tables on the connection.

This doesn't help today, but 8.2 will have a pg_prepared_statements
view.

http://archives.postgresql.org/pgsql-committers/2006-01/msg00143.php
http://developer.postgresql.org/docs/postgres/view-pg-prepared-statements.html

test=> PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1;
test=> \x
Expanded display is on.
test=> SELECT * FROM pg_prepared_statements;
-[ RECORD 1 ]---+----------------------------------------------------------
name            | stmt
statement       | PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1;
prepare_time    | 2006-06-16 07:07:41.682999-06
parameter_types | {integer}
from_sql        | t

-- 
Michael Fuhr

If you are using pooled connections, doesn't PostgreSQL manage the prepared statements for you?  I would expect that, once I prepare a statement, if I attempt to do it again, PostgreSQL would say, "righty then, already done it, here you go".  Then again, I don't know what PostgreSQL does under the covers.

This seems like a lot of work to squeek out a small amount of efficiency.  Would it really give you much value?

-Aaron Bono

>> in PHP for example, where there are multiple sessions and which you get  
>> is random:
>>
>> how do you know if the session you're in has prepared a particular  
>> statement?
>>
>> and/or how do you get a list of prepared statements?
>>
>> last, is there any after login trigger that one could use to prepare  
>> statements the session would need? or is this a dumb idea?
Ahem, if you're concerned about performance, you probably...- don't really use PHP ? Or at least use eaccelerator or
someform of  
 
compiled code caching ?- use a lighttpd with php running as a fastcgi process ?The second option ensures that the PHP
codeexecution is less likely to  
 
be interrupted by a client disconnection ; however it can still crash or  
exceed the time or memory limit.
Thus, when using the persistent connections of PHP :

- use pg_pconnect to connect, this will give you a connection from a pool
- Make sure the connection does not contain a pending transaction, by  
chosing one of these options :- Trust the PHP designers (ahem)- Issue a ROLLBACK as your first query-
register_shutdown_function('pg_query', 'ROLLBACK' ); This issues a  
 
ROLLBACK as the last query even if your script is interrupted, but not if  
the PHP interpreter crashes (happens...).
As for preparing the statements only once, I would do the following :
 Have a SQL script which prepares all statements, creates temp tables etc,  
and whose last command is :
PREPARE connection_status_test AS SELECT 1;

Then, you can start your script by EXECUTE connection_status_test; if it  
fails complaining that the prepared statement is not found, execute the  
SQL script ; else resume normal operations.