Postgresql - Foreign Keys Failing

Question

Postgresql - Foreign Keys Failing

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)
#4 by (-1 votes)

0

Hello,

I have a SALES table and a SALES_ITEM, the connection between them is done via a Cascade deleting Foreign Key and cascade update.

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

The trigger works fine, trying to give a delete from VENDA_ITEM with trigger enabled the bank does not delete the item, however when trying to give a delete in the sale the bank deletes the header but leaves the items, thus violating the idea of the foreign key, that is, I have items without a 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 trigger snippet that is (correctly) preventing the deletion is the raise exception 'can not delete an item with deliveries';

I'm currently implementing my system in PostgreSQL, it currently runs on Firebird and the final idea of the project is that it runs on both banks (one or the other and not in parallel).

PS: I'm using PostgreSQL 10.1 on Windows 10 x64 Home

Att, Mark

postgresql

asked by anonymous 17.11.2017 / 14:05

4 answers

1

I found the problem, in fact the system did not enter the raise exception, but it was returning NULL, since it always returned new in the trigger, while DELETE should return old.

However, this raised a concern for me, because a developer failure broke the bank's integrity with the FKs.

Att, Mark

17.11.2017 / 22:02

0

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;

A: DELETE 0 Query returned successfully in 165 msec.

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

delete from cabecalho where cab_id = 1

A: DELETE 1 Query returned successfully in 72 msec.

When selecting we have header 2 and 3, but we continue to have items for header 1, 2 and 3, ie violating the integrity of FK.

18.11.2017 / 16:31

-1

Frames,

Could you put together a shorter test case (reducing the model) with what is strictly necessary to evidence the failure? It would be important in the case of tests to have the DML (INSERT / UPDATE / DELETE) that lead us to this failure because you can yes be facing a bug.

With a test case we can run it in different versions so we can understand when and how the crash came about.

Thank you!

18.11.2017 / 15:14

Do not specify the path to a batch file How to print list of JSON objects

score 2 · Accepted Answer

Frames,

Analyzing your test case I see that although contradictory is expected behavior. See what's 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 (ie, 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 different value from the original value of the new row will be inserted or updated. (or a value equal to that) has to be returned. To change 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 , the usual idiom. m in DELETE triggers is to return OLD. "

I have tested in all supported versions including the development (master) and the behavior is the same.

Note that when you return NEW in a BEFORE DELETE trigger, NEW is NULL and when you do this you are telling the trigger manager to "skip" the other checks ... this has been used a lot in PostgreSQL because of old schema of partitioning of tables (from the 10 we have declarative partitioning). Yes, it's a not-so-pretty feature until 9.6 but if it's well used it helps a lot.

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

Att,

[1] link