How to force a session to end when close_session does not work.

I have sessions that are showing as still active and holding memory, via resource_acquisitions and sessions.  However, there are no corresponding rows in transactions for these sessions.  I have tried using interrupt_statement and close_session many times, but the sessions will not go away.  The sessions are all coming in via JDBC connections. The problem is that as more of these sessions accumulate, I end up getting resource allocation errors.  I've also tried a force shutdown on the database but that never completes either.   So far, the only way I have found to get rid of them is to hard-kill the unix os vertica process itself - which is most definitely not a preferred way of dealing with things.
Is there any other method of force killing a session and getting the resources released?






  • Options

    I normally identify the pid of the sesison and kill it the OS.

  • Options

    When I log in to the Vertica server - RHEL - there are no processes for individual vertica connections. The only pid I could kill would be the database itself.

  • Options

    That is strange ! 

  • Options



    I just wanted to know whether when you perform close_session('session_id')  does it shows socket errors in the vertica.log files like "socket shutdown error: Transport endpoint is not connected"?


    If this is the case then in such scenerio I would suggest you to perform either of the below workarounds:

    1. You can use execute the following SQL as superuser (dbadmin):

    vsql => select close_all_sockets('waiting_sends');

    If above option doesn't work then we have only option restart that initiator node.

    2. You need reboot the initiator node of those zombie sessions. This will clear these sessions.


    Let us know if it helps in anyways.



    Rahul Choudhary



  • Options

    Yes, the socket message is written to the vertica.log when I try to close the session.    The close_all_sockets did not work, so I had to restart things.


    I'll make note of the close_all_sockets for future reference.   Thanks much.



  • Options

    Can you tell us what options come with thie "new" close_all_sockets() function ?


  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited July 2017

    Has there been a better solution found for this issue? Having clients restart initiator nodes is not very appealing to them. I think we need a "force" option on the CLOSE_SESSION function...

  • Options

    Hey is a better solution available now.

  • Options
    josefpjosefp Vertica Customer

    Is there a better solution now how to force terminate the session which is persistent even CLOSE_SESSION() command was executed?

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited August 2019

    @josefp - Which version of Vertica are you running?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file