Date: Fri, 16 Jun 2006 22:21:06 +0200
To: "John DeSoi" <desoi@pgedit.com>,
	"chester c young" <chestercyoung@yahoo.com>
Subject: Re: sessions and prepared statements
Cc: pgsql-sql@postgresql.org
References: <20060615154930.44620.qmail@web54312.mail.yahoo.com>
	<4B156402-1B7D-46E2-8A4F-9DBD3F119CB7@pgedit.com>
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.ta89hgofcigqcu@apollo13>
In-Reply-To: <4B156402-1B7D-46E2-8A4F-9DBD3F119CB7@pgedit.com>
User-Agent: Opera M2/8.54 (Linux, build 1745)


>> 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 some form of  
compiled code caching ?
	- use a lighttpd with php running as a fastcgi process ?
	The second option ensures that the PHP code execution 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.