Thread: Q: Executing functions at connect/disconnect?
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 theconnection is lost. The client could exit nicely or crash or have a power failure or whatever. I don't expect bad thingsto happen very often, but if/when they do i want to be sure that no rows are "in use". Any ideas? /Mattias
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?
>
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 !
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? > >
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 > >
----- 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 backend in some way, but I don't know
how to do that.
You can get backend PID using:
SELECT pg_backend_pid();
and see all active connections using:
SELECT * FROM pg_stat_activity;
Hope that You are using 7.x version of PostgreSQL, I do not know exactly
which version introduced statistic collector.
Regards !
----- Original Message ----- From: "Mattias Kregert" <mattias@kregert.se> To: <pgsql-general@postgresql.org> Cc: "Darko Prenosil" <darko.prenosil@finteh.hr> Sent: Friday, July 04, 2003 7:11 PM Subject: Re: [GENERAL] Q: Executing functions at connect/disconnect? 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?? This is an interesting idea ! But unfortunately, I think that triggers and rules are not working on system objects. However, I'll try it myself. Regards !
On Thu, Jul 03, 2003 at 20:37:08 +0200, Darko Prenosil <Darko.Prenosil@finteh.hr> wrote: > > But unfortunately, I think that triggers and rules are not working on system > objects. You can't put triggers on system tables. I am not sure about rules, but I would expect the same limitation. You probably need a monitoring process that notices when backends go away. At worst you could have it check every so often. I don't know if notify works with system tables. If it does you could have the process wake up on the events of interest.
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