Reaproveite Senhas de Atendimento com o PostgreSQL

Voltei a ensinar a disciplina de banco de dados e uma dúvida inteligente de um aluno aplicado ajudou a animar o final da última aula. Respondi à dúvida e aparentemente ele ficou satisfeito, mas aproveitei a ideia dele para compor este post. O problema a ser resolvido é manter uma lista de códigos com valores entre 1 e N para serem utilizados por um sistema ou aplicação. Os códigos são reaproveitados, isto é, caso um código não esteja mais disponível, pode ser reutilizado, como por exemplo, no caso de uma senha para atendimento em uma fila. Como manter e tratar este problema com os recursos do banco de dados postgres?

Em primeiro lugar, deixo para os comentários dos interessados maiores discussões sobre as possíveis alternativas. Apresentarei uma solução simples que utiliza triggers, mas espero que a mesma possa ser melhorada e questionada por você, caro leitor.

* Solução de lista de códigos

Permite o controle da utilização e reutilização de códigos através de uma lista com dois campos, um sendo o primeiro o valor do código e outro a indicação de sua utilização ou não.
A lista é armazenada em uma tabela e inicializada na sua criação. Esta lista é atualizada por uma trigger na tabela "usuária" dos códigos. O gatilho atualiza a tabela de código em dois momentos: na utilização de um código e na liberação do código para reuso.

* Tabela de Lista de Códigos

Abaixo, coloco o script da nossa lista de códigos. Coloquei a cláusula UNIQUE para o campo de código para evitar repetições que violassem a integridade da lista e ao tempo gerar um índice implícido para o campo de código, a ser utilizado nas consultas. Vamos inicializar a nossa lista com 10 códigos, inteiros com valor entre 1 e 10, com o valor false, equivalente a "desalocado".

CREATE TABLE lista_codigos (codigo integer UNIQUE, flag_utilizado boolean);
INSERT INTO lista_codigos (codigo, flag_utilizado) VALUES (1,false), (2,false), (3,false), (4,false), (5,false), (6,false), (7,false), (8,false), (9,false), (10,false); --INICIALIZACAO
* Tabela Usuária de Códigos

A tabela em que serão armazenados os códigos utilizados apresentará dois gatilhos, sendo um em caso de inserção de código, e outra para exclusão de código. Adicionei também uma restrição UNIQUE no campo código para não ter de validar tentativas de utilizar o código mais de uma vez.

CREATE TABLE usa_codigos (codigo integer UNIQUE);
Abaixo, os códigos das trigers e trigger functions associadas.

CREATE OR REPLACE FUNCTION pegar_codigo() RETURNS trigger AS $pegar_codigo$
BEGIN
    UPDATE lista_codigos SET flag_utilizado = true WHERE codigo = new.codigo;
    RETURN new;
END;
$pegar_codigo$ LANGUAGE plpgsql;

CREATE TRIGGER pega_codigo BEFORE INSERT ON usa_codigos FOR EACH ROW EXECUTE PROCEDURE pegar_codigo ();

CREATE OR REPLACE FUNCTION liberar_codigo() RETURNS trigger AS $liberar_codigo$
BEGIN
    UPDATE lista_codigos SET flag_utilizado = false WHERE lista_codigos.codigo = old.codigo;
    RETURN old;
END;
$liberar_codigo$ LANGUAGE plpgsql;

CREATE TRIGGER libera_codigo AFTER DELETE ON usa_codigos FOR EACH ROW EXECUTE PROCEDURE liberar_codigo ();

* Testes da Lista

CENÁRIO 1: Reservando códigos. Neste cenário, basta inserir um registro na tabela usa_codigos e consultar a tabela lista_codigos para verificar se foram reservados os códigos.

INSERT INTO usa_codigos (codigo) VALUES (1), (3), (4), (9), (10);

O resultado obtido está correto.

SELECT codigo, CASE
     WHEN flag_utilizado = true THEN 'SIM'
     ELSE 'NÃO'
END AS reservado FROM lista_codigos ORDER BY codigo;

 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | SIM
      4 | SIM
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | SIM
     10 | SIM
(10 registros)
CENÁRIO 2: Liberando códigos. Inicialmente, libero o código 3. A liberação é a exclusão da tabela de códigos.

DELETE FROM usa_codigos WHERE codigo = 3;
O resultado obtido está de acordo com o desejado.

 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | NÃO
      4 | SIM
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | SIM
     10 | SIM
(10 registros)

CENÁRIO 3: Liberando todos os códigos de uma só vez. O resultado corresponde à expectativa.

DELETE FROM usa_codigos;
 codigo | reservado
--------+-----------
      1 | NÃO
      2 | NÃO
      3 | NÃO
      4 | NÃO
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | NÃO
     10 | NÃO
(10 registros)

CENÁRIO 4: Testando reservar código já reservado. A restrição CHECK garante a integridade e o código é reservado, como esperado.

postgres=# INSERT INTO usa_codigos (codigo) VALUES (1);
INSERT 0 1
postgres=# INSERT INTO usa_codigos (codigo) VALUES (1);
ERRO:  duplicar valor da chave viola a restrição de unicidade "usa_codigos_codigo_key"
DETALHE:  Chave (codigo)=(1) já existe.

 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | NÃO
      4 | NÃO
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | NÃO
     10 | NÃO
(10 registros)
CENÁRIO 5: Testando liberar código já liberado. Não ocorre erro e a alocação dos códigos permanece inalterada. Ao menos neste teste inicial, as operações foram realizadas a contento!

postgres=# DELETE FROM usa_codigos WHERE codigo = 3;
DELETE 0
postgres=# SELECT codigo, CASE
     WHEN flag_utilizado = true THEN 'SIM'
     ELSE 'NÃO'
END AS reservado FROM lista_codigos ORDER BY codigo;
 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | NÃO
      4 | NÃO
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | NÃO
     10 | NÃO
(10 registros)

* Adicionando Funcionalidades

Podem ser implementadas consultas ou funções que ajudem a emitir as senhas e liberar as senhas para reaproveitamento, além de consultas básicas para:

-  Consultar o primeiro código disponível

-  Consultar o último código disponível
-  Consultar a quantidade de códigos disponíveis
-  Consultar a quantidade de códigos não disponíveis

Mas deixo aos leitores estas tarefas como exercício.

* Notas conclusivas

- Uma trigger ou restrição de acesso pode evitar que se utilize a cláusula UPDATE para a tabela de códigos, ou tratar o update, o que eu acho mais trabalhoso!
- Deixo para os leitores a tarefa de implementar esta solução utilizando arrays, e empregando outras formas alternativas e talvez mais eficientes.