Can you identify if a PostgreSQL command can crash before it runs?

Question

Can you identify if a PostgreSQL command can crash before it runs?

Navigation

#1 by (1 votes)

0

Sometimes I need to insert a field or an index or make some more critical modification and often ask everyone to leave the base before I execute the command.

This command on my machine even with the application executes normally, but when I run the server the command hangs until all exit, I saw that depends on the tables in use.

I can not tell which users are using which tables, even because I use a single user to connect and the application is very dynamic.

I know that some commands will not work because they require exclusive access to the table.

How can I check if a particular table is in use and the command I'm about to execute can get stuck?

For example in a table with a field type Bytea I tried to add another field with the table in use, even though it is a simple field varchar being null it does not leave, even leaving the screen that uses the table and closing the table the command continues hanging until it leaves the application, that is, disconnecting even.

postgresql

asked by anonymous 02.05.2018 / 17:40

1 answer

React Native Maps How to exchange content without changing structure?

score 1 · Answer 1

1) You can use the environment variable statement_timeout , which limits the maximum execution time of operations in the database.

This ensures that no user will hang running database operations indefinitely or incompatible with the dynamics of your model / system.

In the following example, if the runtime exceeds 60 segundos , the operation will be canceled:

SET statement_timeout = '60s';

Alternatively, this environment variable can be permanently set up in the ROLE of the user.

In the following example, the fulano user will be limited to executing only operations that do not take more than 120 segundos into database dbfoobar , see:

ALTER ROLE fulano IN DATABASE dbfoobar SET statement_timeout TO '120s';

2) To identify which operations are being performed by the database at any given time, you can query the% system% VIEW .

The following query displays the detailed listing of all operations running in the database, ordered by runtime:

SELECT
  pid AS identificador,
  usename AS nome_usuario,
  datname AS nome_database,
  client_addr AS ip_origem,
  now() - query_start AS tempo_em_execucao,
  query AS operacao,
  state AS estado
FROM
   pg_stat_activity
ORDER BY
  query_start;

3) When you identify a running operation for a long period of time, you can force its cancellation through the pg_stat_activity , which receives as argument pg_cancel_backend() , or unique identifier of the operation to be canceled:

SELECT pg_cancel_backend( pid );