Postgres UPDATE ... LIMIT 1

Sem acesso de gravação simultâneo

Materialize uma seleção em um CTE (Common Table Expressions) e una-o na FROMcláusula do UPDATE.

WITH cte AS (
   SELECT server_ip          -- pk column or any (set of) unique column(s)
   FROM   server_info
   WHERE  status = 'standby'
   LIMIT  1                  -- arbitrary pick (cheapest)
   )
UPDATE server_info s
SET    status = 'active' 
FROM   cte
WHERE  s.server_ip = cte.server_ip
RETURNING s.server_ip;

Eu originalmente tinha uma subconsulta simples aqui, mas isso pode evitar LIMITcertos planos de consulta, como Feike apontou:

O planejador pode optar por gerar um plano que execute um loop aninhado sobre a LIMITingsubconsulta, causando mais UPDATEsde LIMIT, por exemplo:

 Update on buganalysis [...] rows=5

   ->  Nested Loop
         ->  Seq Scan on buganalysis
         ->  Subquery Scan on sub [...] loops=11
               ->  Limit [...] rows=2
                     ->  LockRows
                           ->  Sort
                                 ->  Seq Scan on buganalysis

Reproduzindo caso de teste

A maneira de corrigir o acima foi envolver a LIMITsubconsulta em seu próprio CTE, pois o CTE é materializado, ele não retornará resultados diferentes em diferentes iterações do loop aninhado.

Ou use uma subconsulta pouco correlacionada para o caso simples comLIMIT 1. Mais simples, mais rápido:

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         )
RETURNING server_ip;

Com acesso de gravação simultâneo

Assumindo o nível de isolamento padrãoREAD COMMITTED para tudo isso. Níveis de isolamento mais rigorosos ( REPEATABLE READe SERIALIZABLE) ainda podem resultar em erros de serialização. Ver:

• SELECT … FOR UPDATE SKIP LOCKED em transações REPEATABLE READ

Sob carga de gravação simultânea, adicione FOR UPDATE SKIP LOCKEDpara bloquear a linha para evitar condições de corrida. SKIP LOCKEDfoi adicionado no Postgres 9.5 , para versões mais antigas veja abaixo. O manual:

Com SKIP LOCKED, todas as linhas selecionadas que não podem ser bloqueadas imediatamente são ignoradas. Ignorar linhas bloqueadas fornece uma exibição inconsistente dos dados, portanto, isso não é adequado para trabalhos de uso geral, mas pode ser usado para evitar a contenção de bloqueios com vários consumidores acessando uma tabela semelhante a uma fila.

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE SKIP LOCKED
         )
RETURNING server_ip;

Se não houver nenhuma linha qualificada e desbloqueada, nada acontece nesta consulta (nenhuma linha é atualizada) e você obtém um resultado vazio. Para operações não críticas, isso significa que você terminou.

No entanto, transações simultâneas podem ter linhas bloqueadas, mas não concluem a atualização ( ROLLBACKou outros motivos). Para ter certeza , execute uma verificação final:

SELECT NOT EXISTS (
   SELECT FROM server_info
   WHERE  status = 'standby'
   );

SELECTtambém vê linhas bloqueadas. Wile que não retorna true, uma ou mais linhas ainda estão inacabadas e as transações ainda podem ser revertidas. (Ou novas linhas foram adicionadas enquanto isso.) Espere um pouco, então faça um loop nas duas etapas: ( UPDATEaté que você não receba nenhuma linha de volta; SELECT...) até que você obtenha true.

Relacionado:

• Atomic UPDATE .. SELECT no Postgres

Sem SKIP LOCKEDno PostgreSQL 9.4 ou anterior

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;

As transações simultâneas que tentam bloquear a mesma linha são bloqueadas até que a primeira libere seu bloqueio.

Se a primeira foi revertida, a próxima transação pega o bloqueio e prossegue normalmente; outros na fila continuam esperando.

Se o primeiro foi confirmado, a WHEREcondição é reavaliada e, se não for TRUEmais ( statusmudou), o CTE (surpreendentemente) não retornará nenhuma linha. Nada acontece. Esse é o comportamento desejado quando todas as transações desejam atualizar a mesma linha .
Mas não quando cada transação deseja atualizar a próxima linha . E como queremos apenas atualizar uma linha arbitrária (ou aleatória ) , não faz sentido esperar.

Podemos desbloquear a situação com a ajuda de bloqueios consultivos :

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         AND    pg_try_advisory_xact_lock(id)
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;

Desta forma, a próxima linha ainda não bloqueada será atualizada. Cada transação recebe uma nova linha para trabalhar. Eu tive ajuda do Wiki Postgres tcheco para esse truque.

idsendo qualquer bigintcoluna exclusiva (ou qualquer tipo com uma conversão implícita como int4ou int2).

_{Se bloqueios consultivos estiverem em uso para várias tabelas em seu banco de dados simultaneamente, desambiguar com pg_try_advisory_xact_lock(tableoid::int, id)- idsendo exclusivo integeraqui.
Como tableoidé uma bigintquantidade, teoricamente pode transbordar integer. Se você é paranóico o suficiente, use (tableoid::bigint % 2147483648)::int- deixando uma "colisão de hash" teórica para o verdadeiramente paranóico ...}

Além disso, o Postgres é livre para testar as WHEREcondições em qualquer ordem. Ele pode testar pg_try_advisory_xact_lock()e adquirir um bloqueio antes status = 'standby' de , o que pode resultar em bloqueios consultivos adicionais em linhas não relacionadas, onde status = 'standby'não é true. Pergunta relacionada no SO:

• Postgres pg_try_advisory_lock bloqueia todos os registros

Normalmente, você pode simplesmente ignorar isso. Para garantir que apenas as linhas qualificadas sejam bloqueadas, você pode aninhar o(s) predicado(s) em um CTE como acima ou uma subconsulta com o OFFSET 0hack (impede o inlining) . Exemplo:

• Colocar pg_try_advisory_xact_lock() em uma subconsulta aninhada?

Ou (mais barato para varreduras sequenciais) aninhe as condições em uma CASEdeclaração como:

WHERE  CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

No entanto , o CASEtruque também impediria o Postgres de usar um índice no status. Se esse índice estiver disponível, você não precisará de aninhamento extra para começar: apenas as linhas qualificadas serão bloqueadas em uma verificação de índice.

Como você não pode ter certeza de que um índice é usado em todas as chamadas, você pode apenas:

WHERE  status = 'standby'
AND    CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

O CASEé logicamente redundante, mas atende ao propósito discutido.

Se o comando fizer parte de uma transação longa, considere os bloqueios em nível de sessão que podem ser (e devem ser) liberados manualmente. Assim, você pode desbloquear assim que terminar com a linha bloqueada: pg_try_advisory_lock()epg_advisory_unlock() . O manual:

Uma vez adquirido no nível da sessão, um bloqueio consultivo é mantido até que seja liberado explicitamente ou a sessão termine.

Relacionado:

• Otimizando atualizações simultâneas no Postgres