The pg_terminate_backend() function sends a signal directly to the operating system process for that session.
It's possible that the session may have closed by the time pg_terminate_backend() is named. As PID numbers are assigned by the operating system, it could even happen that you try to terminate a given session (call it "session A"), but you actually terminate another session (call it "session B").
Here is how it could happen. Suppose you take note of the PID of session A and decide to disconnect it. Before you actually issue pg_terminate_backend(), session A disconnects, and right after, a new session B is given exactly the same PID. So, when you terminate that PID, you hit session B instead.
On the one hand, you need to be careful. On the other hand, this case is really unlikely, and is only mentioned for completeness. For it to happen, all the following events must happen as well:
- One of the sessions you are trying to close must terminate independently in the very short interval between the moment pg_stat_activity is read and the moment pg_terminate_backend() is executed
- Another session on the same database server must be started in the even shorter interval between the old session closing and the execution of pg_terminate_backend()
- The new session must get exactly the same PID value as the old session, which is less than one chance in 32,000 on a 32-bit Linux machine
Nonetheless, probability theory is tricky, even for experts. Therefore, it's better to be aware that there is a tiny risk, especially if you use the query many times per day over a long period of time, in which case the probability of getting caught at least once builds up.
It's also possible that new sessions could start after we get the list of active sessions. There's no way to prevent this other than by following the Preventing new connections recipe.
Finally, remember that superusers can terminate any session, whilst a non-superuser can only terminate a session which belongs to the same user.