pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsessions and prepared statements
6+ messages / 6 participants
[nested] [flat]
* sessions and prepared statements
@ 2006-06-15 15:49 chester c young <chestercyoung@yahoo.com>
2006-06-16 12:27 ` Re: sessions and prepared statements John DeSoi <desoi@pgedit.com>
0 siblings, 1 reply; 6+ messages in thread
From: chester c young @ 2006-06-15 15:49 UTC (permalink / raw)
To: pgsql-sql
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?
thanks
chester
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sessions and prepared statements
2006-06-15 15:49 sessions and prepared statements chester c young <chestercyoung@yahoo.com>
@ 2006-06-16 12:27 ` John DeSoi <desoi@pgedit.com>
2006-06-16 12:55 ` Re: sessions and prepared statements Rod Taylor <pg@rbt.ca>
2006-06-16 20:21 ` Re: sessions and prepared statements PFC <lists@peufeu.com>
0 siblings, 2 replies; 6+ messages in thread
From: John DeSoi @ 2006-06-16 12:27 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sessions and prepared statements
2006-06-15 15:49 sessions and prepared statements chester c young <chestercyoung@yahoo.com>
2006-06-16 12:27 ` Re: sessions and prepared statements John DeSoi <desoi@pgedit.com>
@ 2006-06-16 12:55 ` Rod Taylor <pg@rbt.ca>
2006-06-16 13:09 ` Re: sessions and prepared statements Michael Fuhr <mike@fuhr.org>
1 sibling, 1 reply; 6+ messages in thread
From: Rod Taylor @ 2006-06-16 12:55 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: chester c young <chestercyoung@yahoo.com>; pgsql-sql
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
>
--
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sessions and prepared statements
2006-06-15 15:49 sessions and prepared statements chester c young <chestercyoung@yahoo.com>
2006-06-16 12:27 ` Re: sessions and prepared statements John DeSoi <desoi@pgedit.com>
2006-06-16 12:55 ` Re: sessions and prepared statements Rod Taylor <pg@rbt.ca>
@ 2006-06-16 13:09 ` Michael Fuhr <mike@fuhr.org>
2006-06-16 14:29 ` Re: sessions and prepared statements Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2006-06-16 13:09 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: John DeSoi <desoi@pgedit.com>; chester c young <chestercyoung@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sessions and prepared statements
2006-06-15 15:49 sessions and prepared statements chester c young <chestercyoung@yahoo.com>
2006-06-16 12:27 ` Re: sessions and prepared statements John DeSoi <desoi@pgedit.com>
2006-06-16 12:55 ` Re: sessions and prepared statements Rod Taylor <pg@rbt.ca>
2006-06-16 13:09 ` Re: sessions and prepared statements Michael Fuhr <mike@fuhr.org>
@ 2006-06-16 14:29 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 6+ messages in thread
From: Aaron Bono @ 2006-06-16 14:29 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Rod Taylor <pg@rbt.ca>; John DeSoi <desoi@pgedit.com>; chester c young <chestercyoung@yahoo.com>; pgsql-sql
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
On 6/16/06, Michael Fuhr <mike@fuhr.org> wrote:
>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sessions and prepared statements
2006-06-15 15:49 sessions and prepared statements chester c young <chestercyoung@yahoo.com>
2006-06-16 12:27 ` Re: sessions and prepared statements John DeSoi <desoi@pgedit.com>
@ 2006-06-16 20:21 ` PFC <lists@peufeu.com>
1 sibling, 0 replies; 6+ messages in thread
From: PFC @ 2006-06-16 20:21 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
>> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-06-16 20:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-15 15:49 sessions and prepared statements chester c young <chestercyoung@yahoo.com>
2006-06-16 12:27 ` John DeSoi <desoi@pgedit.com>
2006-06-16 12:55 ` Rod Taylor <pg@rbt.ca>
2006-06-16 13:09 ` Michael Fuhr <mike@fuhr.org>
2006-06-16 14:29 ` Aaron Bono <postgresql@aranya.com>
2006-06-16 20:21 ` PFC <lists@peufeu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox