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(at)kregert(dot)se>
> To: "Darko Prenosil" <darko(dot)prenosil(at)finteh(dot)hr>
> Cc: <pgsql-general(at)postgresql(dot)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 backend in 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 unavailable to other clients for some time... I would have to run this frequently to be sure that no objects are left behind for too long. Besides, it is an ugly solution. :/
> >
> > /Mattias
> >
> > >From: "Darko Prenosil" <darko(dot)prenosil(at)finteh(dot)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(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073