Thread: Persistent data per connection
We've been struggling for several days now to come up with a mechanism that allows us to establish a mechanism to store data that remains persistent for the life of the connection. Essentially we have a web based application that utilizes a connection pool (using one single 'super' postgresql database user). At time of login by a user, we are capturing a variety of user demographics (user ID, IP address the are coming from, etc). When any action is performed by the user (a service makes a database call), we have audit triggers that fire on update/delete/inserts. We'd like the triggers themselves to have access to that demographic data so the audit records are correctly stamped (at least with the correct user ID). We attempted some techniques whereby we create a table and at the time that each connection is requested from the pool, we extract the PID for that connection and store it in a table (associating the logged in user with it). Then our triggers could access this table (and the PID associated with the current connection) to marry them up and audit appropriately. As you can imagine, this was fraught with issues of stale entries (if a PID was reused and a stale entry with the same PID was never removed from the table) Last thing we wanted was audit activity incorrectly denoting that user A did something that they didnt do. We also thought about hijacking one of the session runtime variables (via the SET command), but aside from not finding a suitable one that wouldn't cause issues, it sounded dangerous. 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?
Jeff Amiel <jamiel@istreamimaging.com> writes:
> We've been struggling for several days now to come up with a mechanism
> that allows us to establish a mechanism to store data that remains
> persistent for the life of the connection.
Why don't you just store it in a temporary table? Goes away at
connection drop by definition ... and each connection can use the same
name for the temp table, so coding is easy.
regards, tom lane
because the connection is never really dropped... using a connection pool....so it's just reclaimed by the pool on a connection.close() or after a timeout period Tom Lane wrote: >Jeff Amiel <jamiel@istreamimaging.com> writes: > > >>We've been struggling for several days now to come up with a mechanism >>that allows us to establish a mechanism to store data that remains >>persistent for the life of the connection. >> >> > >Why don't you just store it in a temporary table? Goes away at >connection drop by definition ... and each connection can use the same >name for the temp table, so coding is easy. > > regards, tom lane > >
> 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@postgresql.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/
On Fri, Mar 25, 2005 at 10:56:50AM -0600, Jeff Amiel wrote: > because the connection is never really dropped... > using a connection pool....so it's just reclaimed by the pool on a > connection.close() or after a timeout period Then you don't really want per-connection state, you want per-client-session state. Presumably you're generating a unique identifier for each client session somewhere in the client app? You could use that unique identifier to store whatever information you need for that session in a table. This sounds like a pretty normal "keep state based on a cookie" problem, as solved by a bunch of web apps. If you're looking for higher performance you might want to look at memcached and the postgresql interface to it (pgmemcache?). > Tom Lane wrote: > > >Jeff Amiel <jamiel@istreamimaging.com> writes: > > > > > >>We've been struggling for several days now to come up with a mechanism > >>that allows us to establish a mechanism to store data that remains > >>persistent for the life of the connection. > >> > >> > > > >Why don't you just store it in a temporary table? Goes away at > >connection drop by definition ... and each connection can use the same > >name for the temp table, so coding is easy. > > > > regards, tom lane > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Steve Atkins wrote: >On Fri, Mar 25, 2005 at 10:56:50AM -0600, Jeff Amiel wrote: > > >>because the connection is never really dropped... >>using a connection pool....so it's just reclaimed by the pool on a >>connection.close() or after a timeout period >> >> > >Then you don't really want per-connection state, you want per-client-session >state. Presumably you're generating a unique identifier for each client >session somewhere in the client app? You could use that unique identifier >to store whatever information you need for that session in a table. > > Yes....but inside a trigger function, how do I know which 'row' to look at in the table that matches up with the session/connection I am currently "in"? That's the trick. I guess temporary tables are the answer here (because you can have a unique one per connection)....the only issue we had was that inside the trigger, if the table didn't exist, the exception killed the entire function.....have to hit the pg_classes table I guess to see if temp table exists first (temporary tables do show up in pg_classes, don't they?)
On Fri, Mar 25, 2005 at 11:46:03AM -0600, Jeff Amiel wrote: > Steve Atkins wrote: > > >On Fri, Mar 25, 2005 at 10:56:50AM -0600, Jeff Amiel wrote: > > Yes....but inside a trigger function, how do I know which 'row' to look > at in the table that matches up with the session/connection I am > currently "in"? That's the trick. I guess temporary tables are the > answer here (because you can have a unique one per connection)....the > only issue we had was that inside the trigger, if the table didn't > exist, the exception killed the entire function.....have to hit the > pg_classes table I guess to see if temp table exists first (temporary > tables do show up in pg_classes, don't they?) Using a temporary table to store that unique token is how I'd do it. Rather than grovel through the system tables during the trigger I'd do all the work at the client session setup. As the first thing, write the token into the temporary table. If that fails, create the temporary table and retry. Then create the transaction you want to use for all the other operations in the session. I'm using pretty much this approach in one of my applications and it works pretty well. I have control over the connection pool, though, and can tell it to do things like create new temporary tables every time it creates a new connection to the DB avoiding some of the hackery involved with conditionally creating it at the start of each session. If you absolutely don't need the data to persist between client sessions then you can just smash it all into a temporary table at the beginning of each client session and read it directly from there rathe than indirecting through a second table. Cheers, Steve
Steve Atkins wrote: >Using a temporary table to store that unique token is how I'd do it. > >Rather than grovel through the system tables during the trigger I'd do >all the work at the client session setup. As the first thing, write >the token into the temporary table. If that fails, create the >temporary table and retry. Then create the transaction you want to use >for all the other operations in the session. > > > Our problem is that we have batch/standalone applications that will not be using the connection pool (as well as developers that may directly modify production tables in rare instances). In those cases (heck, all cases) , the audit triggers themselves (when an update/insert/delete takes place) have to look to see if a temp table exists to pull the user data from. It wont exist in tese cases, but the triggers dont know this.....
On Fri, Mar 25, 2005 at 12:21:28PM -0600, Jeff Amiel wrote: > Steve Atkins wrote: > > >Rather than grovel through the system tables during the trigger I'd do > >all the work at the client session setup. As the first thing, write > >the token into the temporary table. If that fails, create the > >temporary table and retry. Then create the transaction you want to use > >for all the other operations in the session. > > Our problem is that we have batch/standalone applications that will not > be using the connection pool (as well as developers that may directly > modify production tables in rare instances). In those cases (heck, all > cases) , the audit triggers themselves (when an update/insert/delete > takes place) have to look to see if a temp table exists to pull the > user data from. It wont exist in tese cases, but the triggers dont know > this..... A temporary table will mask the underlying table with the same name, so you can have a generic user in a permanent table, then create a new temporary table with the same name at the beginning of each session. So in your schema you'd have a normal table called, say, "sessiondata" with a single column "id" and a single row containing "not-in-a-session". At the beginning of every client session you'd select id from sessiondata and look at the value. If the value is "not-in-a-session" then you create a temporary table called "sessiondata" with the same structure and INSERT your sessionid. Otherwise, UPDATE the id in sessiondata to your session id. Then you'll have a valid session id in that table at all times. If you're in a client session, it'll be the sessionid for that client session. If not, it'll be "not-in-a-session". (Obviously you can have more columns in the table and keep other session data in it as well). Cheers, Steve
Jeff Amiel wrote: > (heck, all cases) , the audit triggers themselves (when an > update/insert/delete takes place) have to look to see if a temp table > exists to pull the user data from. It wont exist in tese cases, but > the triggers dont know this..... Can't you catch the undefined_table exception in your trigger functions, then create the tables if they don't exist? We do that and it seems to work fine. -- Guy Rouillier