I'm using PostgreSQL and need to get the process identifier from PostgreSQL. Every running query in PostgreSQL has it's own PID which we can get via pg_stat_activity.

SELECT * FROM pg_catalog.pg_stat_activity;

This returns a PID, which we can use to stop executing queries in database using

SELECT pg_terminate_backend(<pid of the process>);

The application is Spring Boot / Java and I make some huge queries using NamedParameterJdbcTemplate. They can take a long time, and in some cases I need to terminate executing queries.

The question is how can I get the PID from the generated SQL query?

CodePudding user response：

The PID is not related to a query, but to the connection (=session). Once you established a connection (through the JDBC driver), the PID for that connection (session) won't change.

So you could use:

select pg_backend_pid();

to obtain the the PID of your session at any time.

As you are most likely using a connetion pool, you need to make sure that calling pg_backend_pid() is done using the same connection that your NamedParameterTemplate uses (e.g. by putting both statements into a single transaction)