postgresql - PostgesSQL 9.2 - deleting record in master table -

first, sorry bad english.

i creating new application in use postgresql 9.2. i'm trying use same "logic" used in firebird, apparently not work on postgresql.

i have table master called "albaran" , other detail table called "albamov". have defined triggers corresponding duties master table updated when modify record in detail table. works except when want delete record in master table.

when delete record in master table deletes records detail , update field "total" 0 in master table, not delete master table record. if delete record master table without records in detail table removed smoothly.

i've been testing , have seen problem in update master table done in function call calculoalbaranventa.

this same system works in firebird.

this function returns variable of type% rowtype use update php screen.

here leave definition of tables triggers , functions.

where can problem?

greetings , in advance.

create or replace function public."calculoalbaranventa" (   in  "cserie"      public."serie",   in  "nnumerodoc"  public."numerodocumento" ) returns setof public."totales" $$ 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;    rrow in select "totalunidades",                       "precio",                       "porcentajeiva",                       "porcentajere",                       "descuentobase",                       "descuentoresto"               "albamov"               ("serie" = "cserie") , ("numerodoc" = "nnumerodoc") ,                     ("referencia" 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   ("serie" = "cserie") , ("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 $$ begin   delete "albamov"   ("serie" = old."serie") , ("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 $$ 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 $$ 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" ccliente "albaran"   ("serie" = old."serie") , ("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") ) (     oids = false   );  create index "idx_albaran_nombre"   on public."albaran"   ("nombre");  create trigger "albaran_bd"   before delete   on public."albaran"   each row   execute procedure public."albaranbeforedelete"();  create trigger "albaran_bu"   before update   on public."albaran"   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") ) (     oids = false   );  create index "idx_albamov_serienumerodoc"   on public."albamov"   ("serie", "numerodoc", "recno");  create trigger "albamov_ad"   after delete   on public."albamov"   each row   execute procedure public."albamovafterdelete"(); 

doing tests, have found if delete master table record function, perfectly, why not broken?, can not understand it.

create or replace function public."albaran2factura" (   in  "cseriealbaran"   public."seriedocumento",   in  "nnumeroalbaran"  public."numerodocumento" ) returns setof public."serienumerodocumento" $$ 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 * rconfig "empresa" limit 1;    select "porcentajeiva", "porcentajere" riva "iva"   "tipo" = rconfig."tipoivaportes";    nporcentajeivaportes := riva."porcentajeiva";   nporcentajereportes  := riva."porcentajere";    update "numera"    set "numerodoc" = "numerodoc" + 1   ("tipodocumento" = 'fv') , ("serie" = "cseriealbaran");    select "numerodoc" nnumerofactura "numera"   ("tipodocumento" = 'fv') , ("serie" = "cseriealbaran");    select * rmaster "albaran"   ("serie" = "cseriealbaran") , ("numerodoc" = "nnumeroalbaran");    select "regimeniva" cregimeniva "clientes"   "codigo" = rmaster."cliente";    if ("cseriealbaran" <> 'zzz')      if (cregimeniva = 'g')       nporcentajereportes := 0;     elsif (cregimeniva = 'e')       nporcentajeivaportes := 0;       nporcentajereportes  := 0;     end if; /* if (cregimeniva = 'g') */    else     nporcentajeivaportes := 0;     nporcentajereportes  := 0;   end if; /* if ("cseriealbaran" <> 'zzz') */    insert "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');    rdetail in select * "albamov"                  ("serie" = "cseriealbaran") , ("numerodoc" = "nnumeroalbaran")                  order "recno"   loop        insert "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 "albaran"   ("serie" = "cseriealbaran") , ("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 $$ begin   delete "albaran"   ("serie" = "cserie") , ("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 $$ begin   delete "albamov"   ("serie" = "cserie") , ("numerodoc" = "nnumerodoc");    delete "albaran"   ("serie" = "cserie") , ("numerodoc" = "nnumerodoc"); end $$ language 'plpgsql' volatile called on null input security invoker cost 100; 

when delete record in master table deletes records detail , update field "total" 0 in master table, not delete master table record. if delete record master table without records in detail table removed smoothly.

this sign trigger cascade re-inserting row in before trigger side effects.

in postgres, update delete followed insert. once before triggers have done work, old row/ctid marked dead , new row/ctid created -- both txid_current() onward. , after triggers kick in.

the point understand here you're not manipulating row itself. rather, you're manipulating snapshot of row @ given time, , latter referenced ctid , sorts of meta information:

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

anyway, took cursory look, guess albaranbeforedelete() culprit.

before row/ctid1 gets deleted, cascade delete rows in child table. when do, row/ctid1 still marked live, rather marked dead... reasons, too: row won't deleted if return null in before delete trigger.

at point, subtable's after delete triggers kicks in , updates row/ctid1. statement marks row/ctid1 dead while creating new, live , updated row/ctid2.

your initial statement resumes. postgres marks row/ctid1 dead (incidentally, is), , after triggers fire. then, you're still left live row/ctid2, because original statement, worked out affected rows/ctids before firing triggers on each of latter, did not know about. , row/ctid2 stays around live.

the fix change flow in such way no before triggers has side effects. side effects belong in after triggers.

admittedly, 1 argue it's bug in postgres. bit me years ago, , got dismissed feature when did.

---

btw, in case above isn't 100% clear, here's canonical example of 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 $$ begin     return null; end; $$ language plpgsql;  create trigger break_pgsql before delete on subtest     each row execute procedure break_pgsql();  insert test default values; insert subtest (test_id) select id test; delete test; select * test;    -- empty select * subtest; -- not empty 

in above code, postgres' built-in triggers cascade delete relevant rows. resulting delete statement issued, before trigger side effects messes around it, yielding row invalid foreign key.