Re: Persistent data per connection

> Our latest scheme involves giving each web system user a postgresql user
> account and when we grab a connection from the connection pool, we SET
> SESSION AUTHORIZATION for that user. We can then access the user info
> from the trigger.
>
> But is there a better/different way to persist data at the connection level?

You could create a series of events that happen on the initial
connection object. For example:

New Connection
Check if new connection
select foo from table
if foo
continue

If foo returns you already have all your predefined information such as
cursors etc...

If foo does not return

New Connection
Check if new connection
select foo from table
!if foo
create temp table foo
insert into foo
declare cursor
set
etc...
continue

The table would be a temp table which will automatically drop if the
connection drops so you know if it is a new connection.

You could use pgPool to manage the persistent connections themselves.

This will put more overhead on the startup of new connections but
as the site because busier you will have less and less new connections
and more re-used connections.

Sincerely,

Joshua D. Drake

>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
--
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/