Re: Q: Executing functions at connect/disconnect?

I think the rule on pg_stat_activity doesn't work because it isn't a
real table.

One idea is to create a temp table, which is deleted automatically when
the session ends.  Try a rule on pg_class.  That might work.

---------------------------------------------------------------------------

Mattias Kregert wrote:
> Is it possible to use pg_backend_pid() or pg_stat_activity in some way?
>
> I tried to put a rule on pg_stat_activity:
>   create rule cleanup as on delete to pg_stat_activity do ... <cleanup code>
>
> ...but it does not work... i disconnected and the backend was removed from pg_stat_activity, but the rule did not
execute...does anybody know why?? 
>
> /Mattias
>
>
> ----- Original Message -----
> From: "Mattias Kregert" <mattias@kregert.se>
> To: "Darko Prenosil" <darko.prenosil@finteh.hr>
> Cc: <pgsql-general@postgresql.org>
> Sent: Friday, July 04, 2003 6:08 PM
> Subject: Re: [GENERAL] Q: Executing functions at connect/disconnect?
>
>
> > Just as i thought...
> > I have only one solution then; to have a cleanup function which is executed every now and then...
> >
> > Cleanup function:
> > 1. Look in the Session table to find out which sessions are registered.
> > 2. Determine if some of these sessions are "dead".
> > 3. Update all those items/objects/rows held by the dead session(s) as "unused".
> > 4. Unregister the session.
> >
> > I see two problems with this:
> > A. How do I know if a session is dead or still connected? I need to get that information from the postmaster or my
backendin some way, but I don't know how to do that. 
> > B. If this function is exeuted by a cron job or something, then some rows will be marked as "in  use" and
unavailableto other clients for some time... I would have to run this frequently to be sure that no objects are left
behindfor too long. Besides, it is an ugly solution. :/ 
> >
> > /Mattias
> >
> > >From: "Darko Prenosil" <darko.prenosil@finteh.hr>
> > > I had same question before about two months, but I'm afraid, there is no
> > > simple way. I tried to add some code to the "backend", but have broken all my
> > > teeth trying to get it work. Such code goes deeply into PostgreSQL internals,
> > > and I'm afraid only few people can do it(Tom,Bruce,Jan...).
> > > Hoverer if You succeed ,please let me know because I'm interested in this
> > > feature too.
> > >
> > > Regards !
> >
> > > On Thursday 03 July 2003 17:28, Mattias Kregert wrote:
> > > > Is there a way to automagically execute a function at connect/disconnect?
> > > > I was thinking about the possibility to have some kind of automatic cleanup
> > > > when the client disconnects.
> > > >
> > > > I would like to:
> > > > - Register a session id at connect time. (INSERT into sessions(sessId,
> > > > user, host, ...) ...) - Use the session id to mark some objects as "in use"
> > > > (UPDATE tbl SET usedBy=<sessionid> WHERE...). - Automatically update all
> > > > rows with this session-id as "unused" when the client disconnects from the
> > > > backend.
> > > >
> > > > I can not trust the client to exit cleanly all the time, so the cleanup
> > > > function must be executed by the backend when the connection is lost. The
> > > > client could exit nicely or crash or have a power failure or whatever. I
> > > > don't expect bad things to happen very often, but if/when they do i want to
> > > > be sure that no rows are "in use".
> > > >
> > > > Any ideas?
> > > >
> >
> >
> > ---------------------------(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
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073