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

TwoSteppinTwoSteppin Registered User

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?





  • Adrian_Oprea_1Adrian_Oprea_1 Registered User

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

  • TwoSteppinTwoSteppin Registered User

    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.

  • Adrian_Oprea_1Adrian_Oprea_1 Registered User

    That is strange ! 

  • Rahul_ChoudharyRahul_Choudhary Registered User



    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



  • TwoSteppinTwoSteppin Registered User

    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.



  • Adrian_Oprea_1Adrian_Oprea_1 Registered User

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


  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert admin
    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...

  • ashendraashendra Registered User

    Hey is a better solution available now.

Leave a Comment

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