Re: Persistent data per connection

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