Can you identify whether a Postgresql command can lock before it is executed?

Asked

Viewed 408 times

0

Sometimes I need to enter a field or 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 runs normally, but when I run on the server the command hangs until everyone leaves, I saw that it depends on the tables in use.

You can not know which users are using which tables, even because I use a single user for connection and the application is very dynamic.

I know that some commands will not be good because it requires exclusive access to table.

Have a check if a certain table is in use and the command I’m about to execute may get stuck?

For example in a table with a Bytea type field I tried to add another field with the table in use, even though a simple varchar field may be null it does not let, even leaving the screen that uses said table and closing the table the command continues hanging until you exit the application, ie disconnecting even.

postgresql
  • 1

    Related: https://pt.wikipedia.org/wiki/Problema_da_parada, https://en.wikipedia.org/wiki/Halting_problem

  • 1

    Vichi, rsrsr, is @Miguel, sometimes we think like the user, "this problem seems to me simple to solve is just to do this or that", when in fact we are creating a monster, in my head maybe something like "prepare" would solve, but I saw that the thing is more ugly than it seems.

  • I launched the question because I also use Mysql and in it I can give the commands quietly that it does not lock, but in a forum explained to me that Mysql works differently from Postgresql, so I did not launch the comparison here.

  • Basically, you can’t predict the outcome of a program without running it. But I don’t know if the solution to this is there, maybe going another way

  • You’d have to see how you’re doing it. Here in mine, I use only one user too, and even with connected clients, I make table changes, no problems

  • I can make changes, the problem is being when the table has a bytea field, then it does not let do... I assume that it should read tables with bytea differently so the restriction, for example Firebird seems to me to store Blob fields in separate file so reading is different. The command is simple: ALTER TABLE public.table ADD COLUMN logo Character Varying(200);

  • yes, blob and text fields, not stored in the tuple, are pointers that point to the data... but in my tables I have bytea fields too, and the changes I do normally... so I found it strange

  • You work with Delphi, use Cacheupdates = True ? If yes can be this, but I have no way to work with Cacheupdate

  • no... have application in C# (winforms), ASP.NET and ASP.NET Core

  • Anyway, you could force the termination of the user session: select pg_terminate_backend(PID) from pg_stat_activity where datname = 'banco'

Show 5 more comments

1 answer

1

1) You can use the environment variable statement_timeout, limiting the maximum execution time of transactions in the database.

This ensures that no user will stay dangling performing operations in the database for an indefinite period or incompatible with the dynamics of its model/system.

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

SET statement_timeout = '60s';

Alternatively, this environment variable can be configured permanently in the ROLE user’s.

In the following example, the user fulano will be limited to executing only operations that no longer lead to 120 segundos in database dbfoobar, look at you:

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

2) To identify which operations are being executed by the database at a given time, you can make a query on VIEW system pg_stat_activity.

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

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) By identifying an operation running for a long period of time, you can force its cancellation through the function pg_cancel_backend(), which receives as an argument pid, or unique identifier of the operation to be cancelled:

SELECT pg_cancel_backend( pid );

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.