Re: Q: Executing functions at connect/disconnect? - Mailing list pgsql-general

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 unavailable
toother 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.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
>
>