Thread: Killing process through of a function

Hi,


Can I kill a postgres user process inside a function (SP)?

Sometimes, the java program that our company uses stay in "idle in
transaction" and every day, I need to import a text data to some tables
in DB.
This import make a TRUNCATE in this tables and I need to kill the
remaining process before execute this, to avoid the lock wait.

Thanks for any help


Alexandre de Arruda Paes

On Thu, Feb 17, 2005 at 10:14:15AM -0200, alexandre::aldeia digital wrote:
>
> Can I kill a postgres user process inside a function (SP)?

Functions written in C, PL/Perl, PL/Tcl, etc., can use whatever
facilities those languages provide, such as sending signals to
processes.  Whether that's a good idea is another matter.

> Sometimes, the java program that our company uses stay in "idle in
> transaction" and every day, I need to import a text data to some tables
> in DB.
> This import make a TRUNCATE in this tables and I need to kill the
> remaining process before execute this, to avoid the lock wait.

Why does the Java program stay idle while it's in a transaction?
Are you able to fix the application?  If it's not doing anything
then it should probably COMMIT or ROLLBACK its transactions to
release any locks they hold.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hi Michael,

The software was generated by a CASE tool (genexus) and it's does not
permit any modification.

IBM DB2 have functions that allow the DBA to disconnect some clients.
This is very usefull for database maintenance without giving a shell to him.

VACUUM FULL is other "application" thats needs to run "isolated", and a
function to kill anybody is very usefull.

Thanks for your help.

Alexandre

> Functions written in C, PL/Perl, PL/Tcl, etc., can use whatever
> facilities those languages provide, such as sending signals to
> processes.  Whether that's a good idea is another matter.
>
>>Sometimes, the java program that our company uses stay in "idle in
>>transaction" and every day, I need to import a text data to some tables
>>in DB.
>>This import make a TRUNCATE in this tables and I need to kill the
>>remaining process before execute this, to avoid the lock wait.
>
>
> Why does the Java program stay idle while it's in a transaction?
> Are you able to fix the application?  If it's not doing anything
> then it should probably COMMIT or ROLLBACK its transactions to
> release any locks they hold.
>

On Fri, Feb 18, 2005 at 10:05:25AM -0200, alexandre::aldeia digital wrote:

> IBM DB2 have functions that allow the DBA to disconnect some clients.
> This is very usefull for database maintenance without giving a shell to him.

As I mentioned, you could write a function in C, PL/Perl, etc.,
that sends a signal to a client's backend process, but I think
there's concern about how robust that is, which is why it hasn't
become a standard function.  See the following message and the
messages it references:

http://archives.postgresql.org/pgsql-general/2005-02/msg00426.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/