Faça ALTER TABLE esperar pelo bloqueio sem bloquear mais nada

Muitos ALTER TABLEcomandos do PostgreSQL, como adicionar uma nova coluna com um valor padrão , têm otimizações inteligentes nas versões mais recentes do PostgreSQL que permitem que eles sejam executados basicamente instantaneamente, mesmo em tabelas grandes, uma vez que o Postgres adquiriu brevemente um bloqueio na tabela .

Infelizmente, essa advertência final é importante. Um comando como este da postagem do blog vinculada

ALTER TABLE users ADD COLUMN credits bigint NOT NULL DEFAULT 0;

ainda precisa esperar por um bloqueio exclusivo na userstabela antes de poder ser executado, mesmo que seja executado instantaneamente assim que o bloqueio for adquirido. Pior, enquanto espera por esse bloqueio, ele bloqueia todas as gravações e leituras que envolvem a tabela.

Alguns passos simples para reproduzir isso (testado no Postgres 13.3):

1. Em um psqlshell, crie uma tabela, inicie uma transação, faça uma leitura da tabela e não confirme:

   CREATE TABLE users (id SERIAL, name TEXT);
   INSERT INTO users (name) VALUES ('bob'), ('fred');
   START TRANSACTION;
   SELECT * FROM users WHERE id = 1;
   

2. Deixe o primeiro shell aberto, abra um segundo e tente alterar a tabela:

   ALTER TABLE users ADD COLUMN credits bigint NOT NULL DEFAULT 0;
   

   Observe que esta consulta trava, esperando que a transação no primeiro shell seja confirmada.

3. Abra um terceiro terminal e tente executar

   SELECT * FROM users WHERE id = 2;
   

   Observe que isso também trava; agora está bloqueado aguardando ALTER TABLEa conclusão do comando, que por sua vez é bloqueado aguardando a conclusão da primeira transação.

Parece que a maioria ou todos os ALTER TABLEcomandos se comportam assim. Mesmo que a operação em si seja muito rápida ou possa ser executada sem manter um bloqueio para toda a operação, ALTER TABLEainda precisa adquirir brevemente um bloqueio exclusivo na tabela antes de iniciar seu trabalho e, enquanto espera por esse bloqueio, todas as outras instruções que tocar na mesa - até lê! - estão bloqueados.

Desnecessário dizer que esse comportamento é bastante problemático se você deseja fazer alterações em uma tabela que ocasionalmente está envolvida em transações de longa duração. Se a ALTER TABLEinstrução for bloqueada por uma transação de longa duração que esteja mantendo qualquer tipo de bloqueio envolvendo a tabela no momento em que a ALTER TABLEinstrução for executada, todas as interações com essa tabela serão bloqueadas até o final de qualquer transação aleatória de longa duração. , e qualquer coisa que dependa dessa tabela provavelmente passa por um tempo de inatividade.

Existe uma solução canônica para este problema?

Uma solução grosseira que tentei é usar um script wrapper que tenta repetidamente executar a ALTER TABLEinstrução por meio de uma conexão com lock_timeoutum valor pequeno (por exemplo, 5 segundos). Se ALTER TABLEfalhar devido ao tempo limite de bloqueio, a transação é abortada e o script detecta o erro, aguarda um ou dois minutos e tenta todo o processo novamente. Isso evita o tempo de inatividade total, mas ainda tem implicações de desempenho, pois cada tentativa fracassada de executar a ALTER TABLEinstrução ainda bloqueia as consultas por alguns segundos.

O que eu realmente gostaria de fazer é, de alguma forma, dizer ao Postgres que quero que a ALTER TABLEinstrução aguarde um momento em que possa adquirir o bloqueio na tabela sem bloquear outras consultas nesse meio tempo. (Não me importo se isso significa esperar horas até finalmente chegar a um momento em que nenhuma outra consulta esteja tocando a mesa; se evitar bloquear outras consultas, é uma troca absolutamente aceitável.) Existe alguma maneira de fazer isso - talvez algum encantamento que posso incluir na ALTER TABLEdeclaração ou algum parâmetro de configuração que posso definir para alterar esse comportamento?