Postgresql - Foreign Keys failing

Question

Hello,

I have a SELL table and a VENDA_ITEM, the link between them is made via a Foreign Key that cascades and the update cascades.

Well, in the VENDA_ITEM table there is a Trigger that runs before delete that prevents the item from being deleted if it already has delivery, this happens with a RAISE EXCEPTION.

Trigger works well, when trying to delete from VENDA_ITEM with Trigger enabled the database does not delete the item, however when trying to delete the sale the database deletes the header but leaves the items, thus violating the idea of Foreign key, that is, I have items without header.

The structure:

    create table venda (
    ven_codigo       integer       not null, 
    emp_codigo       integer       not null, 
    usu_codigo       integer       not null, 
    ven_tipo         integer       default 0 not null, 
    vds_codigo       integer       not null, 
    ven_data         date          default current_date not null,
    ven_hora         time          default current_time not null, 
    cli_codigo       integer       not null, 
    fcb_codigo       integer       not null, 
    cpg_codigo       integer       not null, 
    ven_observacao   text, 
    ven_finalizado   integer       default 0, 
    ven_cancelado    integer       default 0, 
    ven_usu_cancel   integer, 
    nf_numero        integer, 
    nf_serie         integer, 
    ven_pdv          integer       default 0 not null, 
    ven_codigo_pdv   integer       default 0 not null, 
    ven_exportadopdv integer       default 0 not null, 
    cliaut_codigo    integer,
    constraint pk_venda primary key (ven_codigo, emp_codigo), 
    constraint fk_venda_empresa  foreign key (emp_codigo) references empresa(emp_codigo)  on update no action on delete restrict, 
    constraint fk_venda_usuario  foreign key (usu_codigo) references usuario(usu_codigo)  on update no action on delete restrict,
    constraint fk_venda_vendedor foreign key (vds_codigo) references vendedor(vds_codigo) on update no action on delete restrict, 
    constraint fk_venda_cliente  foreign key (cli_codigo) references cliente(cli_codigo)  on update no action on delete restrict, 
    constraint fk_venda_forma_cobranca foreign key (fcb_codigo) references forma_cobranca (fcb_codigo) on update no action on delete restrict, 
    constraint fk_venda_condicao_pagamento foreign key (cpg_codigo) references condicao_pagamento (cpg_codigo) on update no action on delete restrict, 
    constraint fk_venda_cliente_autorizado foreign key (cliaut_codigo) references cliente_autorizado (cliaut_codigo) on update no action on delete restrict
    );

    create or replace function sptg_venda_after() returns trigger as $$
declare _sist_sistema integer;
declare _cnf_gerar_carga_web integer;
begin
    if (new.ven_finalizado = 1 or new.ven_cancelado = 1) then
        if (exists(select vi_codigo from venda_itens where ven_codigo = new.ven_codigo limit 1)) then   
            select sist_sistema from sistema limit 1 into _sist_sistema;
            select cnf_gerar_carga_web from configuracao limit 1 into _cnf_gerar_carga_web;

            if (_sist_sistema = 1 and _cnf_gerar_carga_web = 1) then
                insert into carga_web(cargaweb_id, cargaweb_datahora, cargaweb_tabela, cargaweb_codigo, cargaweb_processado)
                values(gen_id(gera_id_carga_web,1), current_timestamp, 'VENDA', new.ven_codigo, 0);
            end if;

        end if;
    end if;

    return new;
end;
$$ language 'plpgsql';


create trigger tg_venda_after after insert or update on venda
    for each row execute procedure sptg_venda_after();




create table venda_itens (
    vi_codigo            integer        not null, 
    ven_codigo           integer        not null, 
    emp_codigo           integer        not null, 
    pro_codigo           integer        not null, 
    vi_qtde              numeric(18,6)  default 0 not null, 
    vi_valor_vista       numeric(18,6)  default 0 not null, 
    vi_vlr_com_juros     numeric(18,6)  default 0 not null, 
    vi_valor_venda       numeric(18,6)  default 0 not null, 
    vi_comissao          numeric(18,6)  default 0 not null,  
    uni_venda            integer        not null, 
    uni_compra           integer        not null, 
    vi_valor_compra      numeric(18,6)  default 0 not null, 
    vi_conversao_und     numeric(18,6)  default 0 not null, 
    pgr_codigo           integer, 
    vi_qtde_entregue     numeric(18,6)  default 0 not null, 
    vi_qtde_devolvida    numeric(18,6)  default 0 not null, 
    vi_qtde_a_entregar   numeric(18,6)  default 0 not null, 
    vi_descricao_produto varchar(80)    default '' not null, 
    constraint pk_venda_itens primary key(vi_codigo),
    constraint fk_venda_itens_venda foreign key (ven_codigo, emp_codigo) references venda (ven_codigo, emp_codigo) on update cascade on delete cascade, 
    constraint fk_venda_itens_produto foreign key (pro_codigo) references produto (pro_codigo) on update cascade on delete restrict, 
    constraint fk_venda_itens_unidade_venda foreign key (uni_venda) references unidade (uni_codigo) on update cascade on delete restrict, 
    constraint fk_venda_itens_unidade_compra foreign key (uni_compra) references unidade (uni_codigo) on update cascade on delete restrict, 
    constraint fk_venda_itens_unidade_produto_grade foreign key (pgr_codigo) references produto_grade (pgr_codigo) on update cascade on delete restrict 
);


create or replace function sptg_venda_itens_before() returns trigger as $$
declare _ven_cancelado integer; declare _ven_pdv integer;
declare _forma_comissao integer; declare _vds_codigo integer;
begin
    if (TG_OP = 'DELETE') then
        select ven_cancelado, ven_pdv from venda where ven_codigo = old.ven_codigo  and emp_codigo = old.emp_codigo into _ven_cancelado, _ven_pdv;
        if (_ven_cancelado <> 1 and _ven_pdv = 0 and old.vi_qtde_entregue > 0) then
           raise exception 'nao e possivel excluir um item com entregas';
        end if;
    end if;

    if (TG_OP = 'INSERT' or TG_OP = 'UPDATE') then
        select uni_venda, uni_compra, pro_compra, pro_conversao, pro_comissao from produto where pro_codigo = new.pro_codigo
            into new.uni_venda, new.uni_compra, new.vi_valor_compra, new.vi_conversao_und, new.vi_comissao;         
        select vds_codigo from venda where ven_codigo = new.ven_codigo and emp_codigo = new.emp_codigo into  _vds_codigo;
        select cnf_forma_calc_comissao from configuracao limit 1 into _forma_comissao;

        if (_forma_comissao = 0) then
            select cnf_aliq_comissao from configuracao limit 1 into new.vi_comissao;
        else 
            if (_forma_comissao = 1) then
                select vds_comissao from vendedor where vds_codigo = _vds_codigo into new.vi_comissao;
            end if;
        end if;

        new.vi_qtde_a_entregar = new.vi_qtde - new.vi_qtde_entregue;
        if (TG_OP = 'UPDATE' and new.vi_qtde < new.vi_qtde_a_entregar) then                                                
            raise exception 'a quantidade deve ser maior ou igual a quantidade entregue somada a quantidade devolvida';
        end if;
    end if;

    return new;
end;
$$ language 'plpgsql';



create trigger tg_venda_itens_before before insert or update or delete  on venda_itens
    for each row execute procedure sptg_venda_itens_before();

The section of Trigger that is preventing (correctly) the exclusion is the raise Exception 'it is not possible to delete an item with deliveries';

I am currently implementing my system in Postgresql, currently it runs in Firebird and the final idea of the project is to run in both banks (one or the other and not in parallel).

PS: I’m using version 10.1 of Postgresql in Windows 10 x64 Home

Att, Marcos

Answer 1

Marcos,

Analyzing your test case I see that although contradictory is an expected behavior. See what is in the documentation [1]

"Row-level triggers Fired BEFORE can Return null to Signal the Trigger manager to Skip the Rest of the Operation for this Row (i.e., subsequent triggers are not Fired, and the INSERT/UPDATE/DELETE does not occur for this Row). If a nonnull value is returned then the Operation proceeds with that Row value. Returning a Row value Different from the original value of NEW alters the Row that will be inserted or updated. Thus, if the Trigger Function wants the Triggering action to Succeed normally without Altering the Row value, NEW (or a value Equal thereto) has to be returned. To alter the Row to be stored, it is possible to replace single values directly in NEW and Return the modified NEW, or to build a complete new record/Row to Return. In the case of a before-Trigger on DELETE, the returned value has no direct Effect, but it has to be nonnull to allow the Trigger action to proceed. Note that NEW is null in DELETE triggers, so returning that is usually not sensible. The usual idiom in DELETE triggers is to Return OLD."

I did tests on all supported verses including the development (master) and the behavior is the same.

Note that when returning NEW in a Trigger BEFORE DELETE the NEW is NULL and when this is done you are telling "Trigger manager" to "skip" the other checks... this has been widely used in Postgresql because of the old table partitioning scheme (from 10 we have declarative partitioning). Yes, it is a functionality not so beautiful until the 9.6 but if well used helps a lot.

The next step would be to report on -hackers this test case of yours to better deepen the understanding, because if it really is an expected behavior then at least we need to improve the documentation, but correct if it really is a mistake indeed. Would you like to do it or do you want me to??? After all you discovered...

Att,

[1] https://www.postgresql.org/docs/current/static/plpgsql-trigger.html

Answer 2

I found the problem, actually the system did not enter the raise Exception, but rather was returning NULL, because it always returned new in Trigger, Forwarding DELETE should return old.

However this raised a certain concern, as a failure of the developer broke the integrity of the bank with the Fks.

Att, Marcos

Answer 3

Simplified example:

=== Bank structure ===

create table cabecalho(
   cab_id          integer     not null,
   cab_nomecliente varchar(50) not null,
   constraint pk_cabecalho primary key(cab_id)
);


create table cabecalho_item(
   cabitem_id          integer      not null,
   cab_id              integer      not null,
   cabitem_nomeproduto varchar(50)  not null,
   constraint pk_cabecalho_item primary key(cabitem_id),
   constraint fk_cabecalho_item_cabecalho foreign key(cab_id) references cabecalho(cab_id) on delete cascade on update cascade
);


create or replace function sptg_cabecalho_item() returns trigger as $$
begin
  return new; --aqui deveria retornar old quando a trigger for delete, ou seja o progrador errou
end;
$$ language 'plpgsql';



create trigger tg_cabecalho_item before delete on cabecalho_item
   for each row execute procedure sptg_cabecalho_item();


insert into cabecalho values(1,'MARCOS');
insert into cabecalho values(2,'JOAO');
insert into cabecalho values(3,'FABIO');

insert into cabecalho_item values(1, 1, 'PRODUTO MARCOS 1');
insert into cabecalho_item values(2, 1, 'PRODUTO MARCOS 2');
insert into cabecalho_item values(3, 1, 'PRODUTO MARCOS 3');


insert into cabecalho_item values(4, 2, 'PRODUTO JOAO 1');
insert into cabecalho_item values(5, 2, 'PRODUTO JOAO 2');
insert into cabecalho_item values(6, 2, 'PRODUTO JOAO 3');

insert into cabecalho_item values(7, 3, 'PRODUTO FABIO 1');
insert into cabecalho_item values(8, 3, 'PRODUTO FABIO 2');
insert into cabecalho_item values(9, 3, 'PRODUTO FABIO 3');

---

delete from cabecalho_item where cab_id = 2;

R: DELETE 0 Query returned successfully in 165 msec.

That is, did not delete the items but did not return any error.

---

delete from cabecalho where cab_id = 1

A: DELETE 1 Query returned successfully in 72 msec.

---

When selecting we have heading 2 and 3, but we still have items for heading 1, 2 and 3, that is, violating the integrity of FK.

Answer 4

Marcos,

Could you put together a more brief test case (reducing the model) with what is strictly necessary to prove the failure? It would be important in the case of tests having the proper DML (INSERT/UPDATE/DELETE) that lead us to this failure because you may be facing a bug.

With a test case we can run it in different versions to be able to understand when and how the failure arose.

Thank you!