PostgesSQL 9.2 - deleting record in master table

Question

First, sorry for my bad english.

I am creating a new application in which I use PostgreSQL 9.2. I'm trying to use the same "logic" used in Firebird, but apparently does not work on PostgreSQL.

I have a table Master called "Albaran" and other Detail table called "AlbaMov". I have defined some triggers with corresponding duties which Master table updated when you modify a record in the Detail table. Everything works perfectly except when I want to delete a record in the Master table.

When you delete a record in the Master table deletes all records from the Detail and I update a field "Total" to 0 in the Master table, but does not delete the Master table record. If I delete the record from the Master table without records in Detail table is removed smoothly.

I've been testing and have seen that the problem is in the UPDATE to the Master table is done in a function that I call CalculoAlbaranVenta.

This same system works perfectly in Firebird.

This function returns a variable of type% ROWTYPE which I use to update a PHP screen.

Here I leave the definition of the tables with triggers and functions.

Where can be the problem?

Greetings and thanks in advance.

CREATE OR REPLACE FUNCTION public."CalculoAlbaranVenta"
(
  IN  "cSerie"      public."Serie",
  IN  "nNumeroDoc"  public."NumeroDocumento"
)
RETURNS SETOF public."Totales" AS
$$
declare nBasImp "Importes";
declare nIva "Importes";
declare nRE "Importes";
declare nTotalBase "Importes";
declare nTotalIVA "Importes";
declare nTotalRE "Importes";
declare nTotalDtoBase "Importes";
declare nTotalDtoResto "Importes";
declare nTotalDtos "Importes";
declare nTotalLinea "Importes";
declare rRow RECORD;
declare rTotales "Totales"%ROWTYPE;

begin
  nBasImp        := 0;
  nIva           := 0;
  nRE            := 0;
  nTotalBase     := 0;
  nTotalIVA      := 0;
  nTotalRE       := 0;
  nTotalDtoBase  := 0;
  nTotalDtoResto := 0;
  nTotalDtos     := 0;
  nTotalLinea    := 0;

  FOR rRow IN SELECT "TotalUnidades", 
                     "Precio", 
                     "PorcentajeIVA", 
                     "PorcentajeRE", 
                     "DescuentoBase", 
                     "DescuentoResto"
              FROM "AlbaMov"
              WHERE ("Serie" = "cSerie") AND ("NumeroDoc" = "nNumeroDoc") AND
                    ("Referencia" IS NOT NULL)
  LOOP
    nTotalLinea    := Round((rRow."TotalUnidades" * rRow."Precio")::numeric, 3);
    nTotalDtoBase  := Round((nTotalLinea * (rRow."DescuentoBase" / 100))::numeric, 3);
    nTotalLinea    := nTotalLinea - nTotalDtoBase; 
    nTotalDtoResto := Round((nTotalLinea * (rRow."DescuentoResto" / 100))::numeric, 3);
    nTotalLinea    := nTotalLinea - nTotalDtoResto;
    nTotalDtos     := nTotalDtos + nTotalDtoBase + nTotalDtoResto; 

    nBasImp := Round(nTotalLinea::numeric, 2);

    nTotalBase := nTotalBase + nBasImp;
    nTotalIVA  := nTotalIVA  + (nBasImp * rRow."PorcentajeIVA" / 100);
    nTotalRE   := nTotalRE   + (nBasImp * rRow."PorcentajeRE" / 100);

  END LOOP;

  nTotalIVA  := Round(nTotalIVA::numeric, 2);
  nTotalRE   := Round(nTotalRE::numeric, 2);
  nTotalDtos := Round(nTotalDtos::numeric, 2);

  UPDATE "Albaran"
  SET "BaseImponible" = nTotalBase,
      "TotalDescuentos" = nTotalDtos,
      "IVA" = nTotalIVA,
      "RE" = nTotalRE,
      "Total" = nTotalBase + nTotalIVA + nTotalRE
  WHERE ("Serie" = "cSerie") AND ("NumeroDoc" = "nNumeroDoc");

  rTotales."TotalDescuentos" := nTotalDtos;
  rTotales."BaseImponible"   := nTotalBase;
  rTotales."TotalIVA"        := nTotalIVA;
  rTotales."TotalRE"         := nTotalRE;
  rTotales."Total"           := nTotalBase + nTotalIVA + nTotalRE;

  RETURN NEXT rTotales;

end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1;

CREATE OR REPLACE FUNCTION public."AlbaranBeforeDelete"()
RETURNS trigger AS
$$
begin
  DELETE FROM "AlbaMov"
  WHERE ("Serie" = OLD."Serie") AND ("NumeroDoc" = OLD."NumeroDoc");

  RETURN OLD;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public."AlbaranBeforeUpdate"()
RETURNS trigger AS
$$
begin

  NEW."Total" := Round((NEW."BaseImponible" + NEW."IVA" + NEW."RE")::numeric, 2);

  RETURN NEW;

end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public."AlbaMovAfterDelete"()
RETURNS trigger AS
$$
declare nTotalBase "Importes";
declare nTotalIVA "Importes";
declare nTotalRE "Importes";
declare nTotalDtoBase "Importes";
declare nTotalDtoResto "Importes";
declare nTotalDtos "Importes";
declare nTotalLinea "Importes";
declare cCliente "CodigoCliente";

begin
  PERFORM "CalculoAlbaranVenta"(OLD."Serie", OLD."NumeroDoc");

  nTotalLinea    := Round((OLD."TotalUnidades" * OLD."Precio")::numeric, 3);
  nTotalDtoBase  := Round((nTotalLinea * (OLD."DescuentoBase" / 100))::numeric, 3);
  nTotalLinea    := nTotalLinea - nTotalDtoBase; 
  nTotalDtoResto := Round((nTotalLinea * (OLD."DescuentoResto" / 100))::numeric, 3);
  nTotalLinea    := nTotalLinea - nTotalDtoResto;
  nTotalDtos     := nTotalDtos + nTotalDtoBase + nTotalDtoResto; 

  nTotalBase := Round(nTotalLinea::numeric, 2);
  nTotalIVA  := (nTotalBase * OLD."PorcentajeIVA" / 100);
  nTotalRE   := (nTotalBase * OLD."PorcentajeRE" / 100);

  nTotalIVA  := Round(nTotalIVA::numeric, 2);
  nTotalRE   := Round(nTotalRE::numeric, 2);
  nTotalDtos := Round(nTotalDtos::numeric, 2);

  PERFORM "SumaArticulo"(OLD."Referencia", OLD."TotalUnidades");

  SELECT "Cliente" INTO cCliente FROM "Albaran"
  WHERE ("Serie" = OLD."Serie") AND ("NumeroDoc" = OLD."NumeroDoc");

  PERFORM "RestaCliente"(cCliente, nTotalBase + nTotalIVA + nTotalRE);

  RETURN OLD;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;


CREATE TABLE public."Albaran" (
  "NumeroDoc"        public."NumeroDocumento" NOT NULL,
  "Serie"            public."Serie" NOT NULL,
  "Fecha"            date NOT NULL,
  "Cliente"          public."CodigoCliProv" NOT NULL,
  "Nombre"           public."RazonSocial",
  "BaseImponible"    public."Importes",
  "IVA"              public."Importes",
  "RE"               public."Importes",
  "Notas"            public."Memo",
  "CodigoDir"        public."CodigoDireccion",
  "Direccion"        public."Direccion",
  "Poblacion"        public."Poblacion",
  "CodigoPostal"     public."CodigoPostal",
  "Provincia"        public."Provincia",
  "Pais"             public."Pais",
  "CIF"              public."CIF",
  "Total"            public."Importes",
  "Agente"           public."CodigoAgente",
  "SuNumeroPedido"   public."SuNumeroPedido",
  "Telefono"         public."Telefono",
  "Fax"              public."Telefono",
  "FormaPago"        public."FormaPago",
  "Transportista"    public."CodigoTransporte",
  "Repartidor"       public."CodigoRepartidor",
  "Portes"           public."Importes",
  "DebidosPagados"   public."Boolean",
  "Gastos"           public."Importes",
  "TotalDescuentos"  public."Importes",
  "TotalPesoNeto"    public."Peso",
  "TotalPesoBruto"   public."Peso",
  "Facturado"        public."Boolean",
  "Modificado"       public."Boolean"
  /* Llaves */
  CONSTRAINT "PK_Albaran"
    PRIMARY KEY ("Serie", "NumeroDoc")
) WITH (
    OIDS = FALSE
  );

CREATE INDEX "IDX_Albaran_Nombre"
  ON public."Albaran"
  ("Nombre");

CREATE TRIGGER "Albaran_BD"
  BEFORE DELETE
  ON public."Albaran"
  FOR EACH ROW
  EXECUTE PROCEDURE public."AlbaranBeforeDelete"();

CREATE TRIGGER "Albaran_BU"
  BEFORE UPDATE
  ON public."Albaran"
  FOR EACH ROW
  EXECUTE PROCEDURE public."AlbaranBeforeUpdate"();

CREATE TABLE public."AlbaMov" (
  "RecNo"              serial NOT NULL,
  "Serie"              public."Serie" NOT NULL,
  "NumeroDoc"          public."NumeroDocumento" NOT NULL,
  "Referencia"         public."CodigoArticulo" NOT NULL,
  "Descripcion"        public."Descripcion",
  "Cantidad"           public."Cantidad",
  "Precio"             public."Importes",
  "PrecioCosto"        public."Importes",
  "PorcentajeIVA"      public."Porcentaje",
  "PorcentajeRE"       public."Porcentaje",
  "Almacen"            public."CodigoAlmacen",
  "Lote"               public."Lote",
  "Unidades"           public."Cantidad",
  "TotalUnidades"      public."Cantidad",
  "CodigoPromocion"    public."CodigoArticuloOpcional",
  "Promocion"          public."Cantidad",
  "DescuentoBase"      public."Porcentaje",
  "DescuentoResto"     public."Porcentaje",
  "PesoNeto"           public."Peso",
  "PesoBruto"          public."Peso",
  "ReferenciaCliente"  public."CodigoArticuloOpcional",
  "Modificado"         public."Boolean",
  "FechaCaducidad"     date,
  "TotalLinea"         public."Importes",
  "SeriePedido"        public."Serie",
  "NumeroPedido"       public."NumeroDocumento",
  /* Llaves */
  CONSTRAINT "PK_AlbaMov"
    PRIMARY KEY ("RecNo")
) WITH (
    OIDS = FALSE
  );

CREATE INDEX "IDX_AlbaMov_SerieNumeroDoc"
  ON public."AlbaMov"
  ("Serie", "NumeroDoc", "RecNo");

CREATE TRIGGER "AlbaMov_AD"
  AFTER DELETE
  ON public."AlbaMov"
  FOR EACH ROW
  EXECUTE PROCEDURE public."AlbaMovAfterDelete"();

Doing tests, I have found that if I delete the master table record from this function, does it perfectly, why is not broken?, I can not understand it.

CREATE OR REPLACE FUNCTION public."Albaran2Factura"
(
  IN  "cSerieAlbaran"   public."SerieDocumento",
  IN  "nNumeroAlbaran"  public."NumeroDocumento"
)
RETURNS SETOF public."SerieNumeroDocumento" AS
$$
declare rDocumento "SerieNumeroDocumento"%ROWTYPE;
declare rMaster RECORD;
declare rDetail RECORD;
declare rConfig RECORD;
declare rIVA RECORD;
declare cRegimenIVA CHAR;
declare nNumeroFactura "NumeroDocumento";
declare nPorcentajeIVAPortes "Importes";
declare nPorcentajeREPortes "Importes";

begin
  rDocumento."Serie"     := '';
  rDocumento."NumeroDoc" := -1;

  SELECT * INTO rConfig FROM "Empresa" LIMIT 1;

  SELECT "PorcentajeIVA", "PorcentajeRE" INTO rIVA FROM "Iva"
  WHERE "Tipo" = rConfig."TipoIVAPortes";

  nPorcentajeIVAPortes := rIVA."PorcentajeIVA";
  nPorcentajeREPortes  := rIVA."PorcentajeRE";

  UPDATE "Numera" 
  SET "NumeroDoc" = "NumeroDoc" + 1
  WHERE ("TipoDocumento" = 'FV') AND ("Serie" = "cSerieAlbaran");

  SELECT "NumeroDoc" INTO nNumeroFactura FROM "Numera"
  WHERE ("TipoDocumento" = 'FV') AND ("Serie" = "cSerieAlbaran");

  SELECT * INTO rMaster FROM "Albaran"
  WHERE ("Serie" = "cSerieAlbaran") AND ("NumeroDoc" = "nNumeroAlbaran");

  SELECT "RegimenIVA" INTO cRegimenIVA FROM "Clientes"
  WHERE "Codigo" = rMaster."Cliente";

  IF ("cSerieAlbaran" <> 'ZZZ') THEN

    IF (cRegimenIVA = 'G') THEN
      nPorcentajeREPortes := 0;
    ELSIF (cRegimenIVA = 'E') THEN
      nPorcentajeIVAPortes := 0;
      nPorcentajeREPortes  := 0;
    END IF; /* IF (cRegimenIVA = 'G') */

  ELSE
    nPorcentajeIVAPortes := 0;
    nPorcentajeREPortes  := 0;
  END IF; /* IF ("cSerieAlbaran" <> 'ZZZ') */

  INSERT INTO "Factura" ("NumeroDoc",            
                         "Serie",                
                         "Fecha",                
                         "Cliente",              
                         "Nombre",               
                         "BaseImponible",        
                         "IVA",                  
                         "RE",                   
                         "Notas",                
                         "Direccion",            
                         "Poblacion",            
                         "CodigoPostal",         
                         "Provincia",            
                         "CIF",                  
                         "Total",                
                         "Agente",
                         "CodigoDir",            
                         "Pais",                 
                         "SuNumeroPedido",       
                         "Telefono",             
                         "Fax",                  
                         "FormaPago",            
                         "Transportista",        
                         "Repartidor",           
                         "Portes",               
                         "DebidosPagados",       
                         "Gastos",               
                         "TotalDescuentos",      
                         "TotalPesoNeto",        
                         "TotalPesoBruto",
                         "PorcentajeIVAPortes",  
                         "PorcentajeREPortes",   
                         "Albaranes",            
                         "Exportada",            
                         "Rapel",                
                         "Cobrada",              
                         "Modificado") 
  VALUES (nNumeroFactura,            
          "cSerieAlbaran",                
          current_date,                
          rMaster."Cliente",              
          rMaster."Nombre",               
          rMaster."BaseImponible",        
          rMaster."IVA",                  
          rMaster."RE",                   
          rMaster."Notas",                
          rMaster."Direccion",            
          rMaster."Poblacion",            
          rMaster."CodigoPostal",         
          rMaster."Provincia",            
          rMaster."CIF",                  
          rMaster."Total",                
          rMaster."Agente",
          rMaster."CodigoDir",            
          rMaster."Pais",                 
          rMaster."SuNumeroPedido",       
          rMaster."Telefono",             
          rMaster."Fax",                  
          rMaster."FormaPago",            
          rMaster."Transportista",        
          rMaster."Repartidor",           
          rMaster."Portes",               
          rMaster."DebidosPagados",       
          rMaster."Gastos",               
          rMaster."TotalDescuentos",      
          rMaster."TotalPesoNeto",        
          rMaster."TotalPesoBruto",
          nPorcentajeIVAPortes,  
          nPorcentajeREPortes,   
          'Albaran ' || "nNumeroAlbaran" || '/' || "cSerieAlbaran",            
          '0',            
          '0',                
          '0',              
          '1');

  FOR rDetail IN SELECT * FROM "AlbaMov"
                 WHERE ("Serie" = "cSerieAlbaran") AND ("NumeroDoc" = "nNumeroAlbaran")
                 ORDER BY "RecNo"
  LOOP

      INSERT INTO "FacMov" ("Serie",            
                            "NumeroDoc",        
                            "Referencia",       
                            "Descripcion",      
                            "Cantidad",         
                            "Precio",           
                            "PorcentajeIVA",    
                            "PorcentajeRE",     
                            "NumeroAlbaran",    
                            "SerieAlbaran",     
                            "FechaAlbaran",     
                            "NumeroPedido",     
                            "SeriePedido",      
                            "PrecioCosto",      
                            "Almacen",          
                            "Lote",             
                            "Unidades",         
                            "TotalUnidades",    
                            "CodigoPromocion",  
                            "Promocion",        
                            "DescuentoBase",    
                            "DescuentoResto",   
                            "PesoNeto",         
                            "PesoBruto",        
                            "ReferenciaCliente",
                            "Modificado",       
                            "FechaCaducidad",   
                            "NoDescontar",      
                            "Agente",           
                            "Repartidor")
             VALUES ("cSerieAlbaran",            
                     nNumeroFactura,        
                     rDetail."Referencia",       
                     rDetail."Descripcion",      
                     rDetail."Cantidad",         
                     rDetail."Precio",           
                     rDetail."PorcentajeIVA",    
                     rDetail."PorcentajeRE",     
                     rMaster."NumeroDoc",    
                     rMaster."Serie",     
                     rMaster."Fecha",     
                     rDetail."NumeroPedido",     
                     rDetail."SeriePedido",      
                     rDetail."PrecioCosto",      
                     rDetail."Almacen",          
                     rDetail."Lote",             
                     rDetail."Unidades",         
                     rDetail."TotalUnidades",    
                     rDetail."CodigoPromocion",  
                     rDetail."Promocion",        
                     rDetail."DescuentoBase",    
                     rDetail."DescuentoResto",   
                     rDetail."PesoNeto",         
                     rDetail."PesoBruto",        
                     rDetail."ReferenciaCliente",
                     '1',       
                     rDetail."FechaCaducidad",   
                     '0',      
                     rMaster."Agente",           
                     rMaster."Repartidor");

  END LOOP;


  /********************** Deleting master record work ****************/

  DELETE FROM "Albaran"
  WHERE ("Serie" = "cSerieAlbaran") AND ("NumeroDoc" = "nNumeroAlbaran");

  /**************************************/

  rDocumento."Serie"     := "cSerieAlbaran";
  rDocumento."NumeroDoc" := nNumeroFactura;

  RETURN NEXT rDocumento;


end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

This don´t work :

CREATE OR REPLACE FUNCTION public."BorrarAlbaran"
(
  IN  "cSerie"      public."SerieDocumento",
  IN  "nNumeroDoc"  public."NumeroDocumento"
)
RETURNS void AS
$$
begin
  DELETE FROM "Albaran"
  WHERE ("Serie" = "cSerie") and ("NumeroDoc" = "nNumeroDoc");
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Workaround :

CREATE OR REPLACE FUNCTION public."BorrarAlbaranVenta"
(
  IN  "cSerie"      public."SerieDocumento",
  IN  "nNumeroDoc"  public."NumeroDocumento"
)
RETURNS void AS
$$
begin
  DELETE FROM "AlbaMov"
  WHERE ("Serie" = "cSerie") and ("NumeroDoc" = "nNumeroDoc");

  DELETE FROM "Albaran"
  WHERE ("Serie" = "cSerie") and ("NumeroDoc" = "nNumeroDoc");
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Answer 1

When you delete a record in the Master table deletes all records from the Detail and I update a field "Total" to 0 in the Master table, but does not delete the Master table record. If I delete the record from the Master table without records in Detail table is removed smoothly.

This is usually a sign that the trigger is cascade re-inserting the row in a before trigger with side effects.

In Postgres, an update is actually a delete followed by an insert. Once all the before triggers have done their work, the old row/ctid is marked as dead and a new row/ctid is created -- both from txid_current() onward. And then the after triggers kick in.

The point to understand here is that you're not manipulating the row itself. Rather, you're manipulating a snapshot of the row at a given time, and the latter is referenced by its ctid and all sorts of meta information:

http://www.postgresql.org/docs/9.2/static/ddl-system-columns.html

Anyway, I only took a cursory look, but my guess is that AlbaranBeforeDelete() is the culprit.

Before the row/ctid1 gets deleted, you cascade delete rows in your child table. When you do, row/ctid1 is still marked as live, rather than already marked as dead... For good reasons, too: the row won't get deleted if you return null in a before delete trigger.

At this point, your subtable's after delete triggers then kicks in and updates row/ctid1. This statement marks row/ctid1 as dead while creating a new, live and updated row/ctid2.

Your initial statement then resumes. Postgres marks row/ctid1 as dead (incidentally, it already is), and after triggers fire. But then, you're still left with a live row/ctid2, because your original statement, which worked out affected rows/ctids before firing triggers on each of the latter, did not know about. And thus row/ctid2 stays around to live.

The fix is to change your flow in such a way that no before triggers has any side effects. Side effects belong in after triggers.

Admittedly, one could argue that it's a bug in Postgres. It bit me years ago, and it got dismissed as a feature when I did.

---

Btw, in case the above isn't 100% clear, here's another canonical example of what is happening:

create table if not exists test (
    id serial primary key
);

create table if not exists subtest (
    id serial primary key,
    test_id int references test(id) on delete cascade
);

create function break_pgsql() returns trigger as $$
begin
    return null;
end;
$$ language plpgsql;

create trigger break_pgsql before delete on subtest
    for each row
execute procedure break_pgsql();

insert into test default values;
insert into subtest (test_id) select id from test;
delete from test;
select * from test;    -- empty
select * from subtest; -- not empty

In the above code, Postgres' built-in triggers cascade delete the relevant rows. The resulting delete statement is issued, but the before trigger with side effects messes around with it, thus yielding a row with an invalid foreign key.