Lanzar un Store Procedure cuando se detecta inactividad

[Neftali [Germán.Estévez]]

Empezado por afxe

El tema es... ¿Cómo detecto inactividad en Firebird? o por lo menos... ¿Cómo detecto que en un determinado tiempo no se han añadido artículos a esta tabla?

Yo tal vez intentaría pensarlo al revés.
Creo recordar que Firebird posee Jobs. Crea un job programado (por ejemplo) cada 5 minutos. Cuando lo lances, si detectas que en los últimos 5 minutos se han añadido artículos, sales sin hacer nada.
Para detectar eso, basta con crear un campo Timestamp/fecha si no lo tienes ya que se rellene con cada operación.

[[duilioisola]]

Una tercera vía es utilizar semáforos o un campo que indique que no se desea calcular el stock.

Por ejemplo:

update semaforos set calcular_stock = 0

-- insert de 6000 pedidos.

update semaforos set calcular_stock = 1

Y en los triggers o procedimientos que tocan el stock

Código SQL [-]
create trigger detalle_pedido_bi0 before insert
declare variable calcular_stock smallint;
begin
    -- Obtengo valor de semaforo
    select calcular_stock from semaforos
    into : calcular_stock;

    -- Calculo stock si el semaforo esta establecido
    if (calcular_stock = 1) then
    begin
        update stock ...
    end
end

Podría ser relativamente peligroso si el último UPDATE donde vuelves a poner el semáforo no se ejecuta por algún error.

[[duilioisola]]

Una tercera vía es utilizar semáforos o un campo que indique que no se desea calcular el stock.

Por ejemplo:

insert into cabecera_pedido(...) values(...);
insert into detalle_pedido(id_cabecera, articulo, unidades, ..., calcular_stock)
values(: id, : articulo, : unidades, ..., 0);

Y en los triggers o procedimientos que tocan el stock

Código SQL [-]
create trigger detalle_pedido_bi0 before insert
declare variable calcular_stock smallint;
begin
    -- Calculo stock si se desea
    if (new.calcular_stock = 1) then
    begin
        update stock ...
    end

    -- Fuerzo CALCULAR_STOCK por si en el futuro se retoca este registro
    new.calular_stock = 1;
end

[afxe]

Gracias a todos. La idea de Neftali me pareció buena, aunque no he encontrado la manera de que el Firebird ejecute una tarea programada. Os comento cómo ha quedado, por si en posteriores búsquedas alguien lo necesita.
Idea base: Mantener disponibilidad y estadística de un artículo por cada delegación sin saturar el servidor:

Primero, crear una tabla con los artículos que se han vendido, pedido, comprado, traspasado, presupuestado, ajustado:

Código SQL [-]
CREATE TABLE Arti_Actu_Dispon (
  cod_articulo INTEGER NOT NULL,
  cod_delegacion Integer not Null,
  modo INTEGER not null,
  Registrado TIMESTAMP,
  CONSTRAINT PK_ARTI_ACTU_DISPON PRIMARY KEY (COD_ARTICULO, COD_DELEGACION, MODO)
);

CREATE INDEX IDX_ARTI_ACTU_DISPON_1 ON ARTI_ACTU_DISPON (REGISTRADO);

Segundo, meto en todos los triggers de las tablas que provoquen movimiento del artículo (preventas, pedidos, presupuestos, traspasos, compras, ventas...) indicado que ese artículo debe recalcularse sus parámetros de disponibilidad, pongo ejemplo de uno de estos triggers:

Código SQL [-]
ALTER TRIGGER AIUD_EXTRACTO ACTIVE
AFTER insert OR update OR delete POSITION 5
AS
BEGIN
  IF (DELETING) THEN
    In autonomous transaction do update or INSERT INTO ARTI_ACTU_DISPON VALUES(OLD.COD_ARTICULO, OLD.COD_DELEGACION, 1, current_timestamp)  matching (cod_articulo, cod_delegacion, modo);

  IF (UPDATING AND ( (OLD.COD_ARTICULO <> NEW.COD_ARTICULO) OR (NEW.COD_DELEGACION <> OLD.COD_DELEGACION) ) ) THEN
    In autonomous transaction do update or INSERT INTO ARTI_ACTU_DISPON VALUES(OLD.COD_ARTICULO, OLD.COD_DELEGACION, 1, current_timestamp)  matching (cod_articulo, cod_delegacion, modo);
    
  IF (INSERTING OR UPDATING) THEN
    In autonomous transaction do update or INSERT INTO ARTI_ACTU_DISPON VALUES(NEW.COD_ARTICULO, new.COD_DELEGACION, 1, current_timestamp) matching (cod_articulo, cod_delegacion, modo);
END

El autonomous transaction es porque he tenido frecuentes deadlocks (mucha intensidad de entrada de datos). Sólo se inserta una única vez cada artículo. Si se ha movido 300 veces el artículo, no tengo que calcular 300 veces su disponibilidad, sólo una única vez cuando termine de moverlo 300 veces.

El siguiente paso ha sido crear el STORE PROCEDURE que se dedica a actualizar la disponibilidad por delegaciones de los artículos que estén contenidos en esta tabla. El bucle se detendrá cuando se hayan actualizado todos los artículos o se detecte que empiezan a entrar nuevos artículos en la tabla.

Código SQL [-]
CREATE OR ALTER PROCEDURE proc_ARTI_ACTU_DISPON
AS
  DECLARE VARIABLE CodArt integer;
  Declare Variable CodDlg Integer;
  Declare variable vModo Integer;
  Declare Variable ultima_entrada TimeStamp;

BEGIN
  WHILE (EXISTS(SELECT COD_ARTICULO FROM ARTI_ACTU_DISPON)) DO begin
    select cod_articulo, cod_delegacion, modo from ARTI_ACTU_DISPON ROWS 1 TO 1 into :CodArt, :CodDlg, :vModo;
    in autonomous transaction do begin
      EXECUTE PROCEDURE PROC_ACTU_DISPON (:CodDlg, :CodArt, :vModo);
      DELETE FROM ARTI_ACTU_DISPON WHERE COD_ARTICULO = :CodArt and cod_delegacion = :CodDlg and modo = :vModo;
    end
    SELECT MAX(REGISTRADO) FROM ARTI_ACTU_DISPON INTO :Ultima_Entrada;
    if (DateAdd(30 SECOND TO Ultima_entrada) > CURRENT_TIMESTAMP) THEN BREAK;
  end
 
 when any DO 
   insert INTO LOG (FECHA, COD_USUARIO, ACCION, FICHERO, COD_DOCUMENTO, REFERENCIA) 
   VALUES (current_timestamp, 1, 'EXEC', 'ARTI_ACTU_DISPON', :CodArt, 'PROC_ACTU_DISPON ') ;
   
END

Este proceso debe estar especialmente protegido, ya que se ha de ejecutar en modo desatendido, y si da un problema, no debe bloquearse el proceso, de ahí el autonomous transaction (por si el proceso se lanzara por error varias veces) y el When Any, para que registrar las incidencias.

Por último, he creado otro Store Procedure que ponga en marcha el bucle si hace más de 30 segundos que no se actualiza la tabla de articulos a procesar:

Código SQL [-]
CREATE OR ALTER PROCEDURE TIMER_execute
AS
  Declare Variable ultima_entrada TimeStamp;
  Declare Variable nReg Integer;
BEGIN
  nReg = (Select cod_articulo from arti_actu_dispon where cod_articulo > 0 rows 1 to 1);
  if (nReg > 0) then 
  BEGIN
     /* llevamos mas de 30 segundos sin hacer entradas */ 
    SELECT MAX(REGISTRADO) FROM ARTI_ACTU_DISPON INTO :Ultima_Entrada;
    if (DateAdd(30 SECOND TO Ultima_entrada) < CURRENT_TIMESTAMP) THEN
      EXECUTE PROCEDURE PROC_ARTI_ACTU_DISPON;  
  END
END

Tengo un programa en delphi funcionando sólo en el servidor dedicado ha lanzar sincronizadores, actualizadores, chequeadores... y he metido un timer para que cada 60 segundos se lance el TIMER_EXECUTE.

En hora punta (08:00-09:00) se han producido 7345 líneas de venta, en los que se ha vendido 720 productos diferentes, mientras se está generando líneas de venta el proceso de actualización de disponibilidad (stock, pedidos, fechas prevista de entrega, activación de punto de pedido, descuento de depositos...) no se ejecuta... cuando lleva 30 segundos sin que se muevan productos, se procesa los archivos de disponibilidad para esos 720 productos, y no se pasa el proceso 7345 veces, como ocurría antes.

Lleva un par de días en funcionamiento y por ahora bien, me ha dado un sólo DeadLock porque creo que tres procesos han ido a actualizar el mismo artículo en el mismo milisegundo... y a petado el AIUD_EXTRACTO (no entiendo porqué, ya que está en una transacción autónoma y no debería estar bloqueado el update en ninguna trasacción).

Lo único que me preocupa es que el ejecutable que lo lanza, a veces se cae... falla algún proceso, se reinicia el servidor, alguien lo para para mantemiento, pierde la conexión con alguna bases de datos...