使用异常处理来修复PL / pgsql中的外键约束（PostgreSQL）

Trying to learn exception handling in pgSQL (PostgreSQL 9.1). The following SP fails with

试图学习pgSQL中的异常处理(PostgreSQL 9.1)。以下SP失败

ERROR: insert or update on table "dx" violates foreign key constraint "fk_icd9"
SQL state: 23503
Detail: Key (cicd9, cdesc)=(244.9, testing1) is not present in table "icd9".

fk_icd9 is defined from table dx as:

fk_icd9从表dx定义为:

CONSTRAINT fk_icd9 FOREIGN KEY (cicd9, cdesc)
  REFERENCES icd9 (cicd9, cdesc) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED

My attempt at the SP is:

我对SP的尝试是:

CREATE OR REPLACE FUNCTION g_test() RETURNS void AS $$
DECLARE
    r View_dx%rowtype;
BEGIN
  r.cicd9 := '244.9';
  r.groupid := 'BBBB      CCCC        199971230';  
  r.tposted := '2013-08-30 17:45:45'::timestamp;
  r.cdesc := 'testing1';

  LOOP
    BEGIN
      UPDATE dx SET cdesc = r.cdesc
      WHERE cicd9 = r.cicd9 AND groupid = r.groupid AND tposted = r.tposted;
    EXCEPTION 
      WHEN others THEN
        INSERT INTO icd9(cicd9, cdesc) VALUES (r.cicd9, r.cdesc);
    END;
    IF FOUND THEN
      RETURN;
    END IF; 
  END LOOP;
END; $$ LANGUAGE plpgsql;

I am trying to update a table, dx, that has a foreign key constraint in a second table, icd9. If update of the dx table fails because of this constraint, then I would like to insert the new record in the parent icd9 table then loop back to the first table, dx, for update.

我试图在第二个表icd9中更新一个具有外键约束的表dx。如果由于此约束而导致dx表更新失败,那么我想在父icd9表中插入新记录,然后循环回第一个表dx进行更新。

What am I doing wrong? How is this done?

我究竟做错了什么?这是怎么做到的?

Edit #1: Editing the code as shown below to:

编辑#1:编辑如下所示的代码:

 create or replace function g_savedx3() returns void as
$$
 DECLARE

_cicd9 character varying(8);
_groupid character varying(33);
_tposted timestamp without time zone;
_cdesc character varying(80); 


BEGIN
_cicd9 := '244.9';
_groupid := 'BBBBB        AAAAA        199998';  
_tposted := '2013-08-30 17:45:45'::timestamp;
_cdesc := 'testing109';

LOOP
    BEGIN
        RAISE NOTICE 'About to update ';

        UPDATE dx SET cdesc = _cdesc
            WHERE 
                cicd9 = _cicd9 and 
                groupid = _groupid and tposted = _tposted;

        RAISE NOTICE 'Updated in g_saveDx3';

        IF FOUND THEN
            RETURN;
        END IF;


        EXCEPTION 
            WHEN others THEN
                RAISE NOTICE 'In exception g_saveDx3, about to insert';

                INSERT INTO icd9(cicd9,cdesc) VALUES (_cicd9, _cdesc);

                RAISE NOTICE 'In exception inserted';
    END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

select g_savedx3();

Yields the following messages:

产生以下消息:

NOTICE: About to update NOTICE: Updated in g_saveDx3

注意:即将更新注意:已在g_saveDx3中更新

ERROR: insert or update on table "dx" violates foreign key constraint "fk_icd9" DETAIL: Key (cicd9, cdesc)=(244.9, testing109) is not present in table "icd9". ********** Error **********

错误:在表“dx”上插入或更新违反外键约束“fk_icd9”DETAIL:表“icd9”中不存在键(cicd9,cdesc)=(244.9,testing109)。 **********错误**********

ERROR: insert or update on table "dx" violates foreign key constraint "fk_icd9" SQL state: 23503 Detail: Key (cicd9, cdesc)=(244.9, testing109) is not present in table "icd9".

错误:表“dx”上的插入或更新违反外键约束“fk_icd9”SQL状态:23503详细信息:表“icd9”中不存在键(cicd9,cdesc)=(244.9,testing109)。

Note: I found an old entry on updates violating foreign constraints by Tom Lane (2004)

注意:我在Tom Lane(2004)发现了一个违反外部约束的更新旧条目

Yes it is ... you're expecting the RI triggers to fire during the plpgsql function, but in fact they fire at completion of the outer statement that called the plpgsql function.

是的......你期望在plpgsql函数期间触发RI触发器,但实际上它们在调用plpgsql函数的外部语句完成时触发。

There's been some debate about whether this is really the most desirable behavior, but that's how it is at the moment.

关于这是否真的是最理想的行为一直存在争议,但目前的情况就是如此。

If this is still the case, it might explain the problem. Any ideas how to fix my code? (It should have worked??) Thanks.

如果仍然如此,它可能会解释这个问题。任何想法如何修复我的代码? (应该有用吗?)谢谢。

***As mentioned below, I'm guessing this is the default behavior which, in my case, causes the exception to be called after completion of the plpgsql function. This behavior can be changed (PostgreSQL 9.1) with:

***如下所述,我猜这是默认行为,在我的情况下,导致在完成plpgsql函数后调用异常。可以使用以下命令更改此行为(PostgreSQL 9.1):

SET CONSTRAINTS ALL IMMEDIATE;

设置约束所有立即;

WHICH IS NEEDED TO MAKE THIS WORK

需要做这项工作

If it has any bearing, here is the definition of the ICD9 table:

如果它有任何方位,这里是ICD9表的定义:

 CREATE TABLE icd9
(
 recid serial NOT NULL,
 cicd9 character varying(8),
 cdesc character varying(80) NOT NULL,
 "timestamp" timestamp without time zone DEFAULT now(),
 modified timestamp without time zone DEFAULT now(),
 chronic boolean NOT NULL DEFAULT false,
 CONSTRAINT pk_icd9_recid PRIMARY KEY (recid),
 CONSTRAINT constraint_cdesc UNIQUE (cicd9, cdesc),
 CONSTRAINT desccheck CHECK (cdesc::text <> ''::text)
)
WITH (
 OIDS=FALSE
);

2 个解决方案

#1

Switch to old-fashioned debugging. Here is my code, it does insert.

切换到老式的调试。这是我的代码,它确实插入。

create or replace function f () returns void as

$$
DECLARE
newval integer :=3 ;
BEGIN
LOOP
BEGIN
    RAISE NOTICE 'About to update ';
    UPDATE B SET ID2 = newval;
    RAISE NOTICE 'Updated ';
    IF FOUND THEN
      RETURN;
    END IF;
EXCEPTION 
    WHEN others THEN
    RAISE NOTICE 'In exception , about to insert';
      INSERT INTO a VALUES (newval);
RAISE NOTICE 'In exception inserted';
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

Execution:

select f();
NOTICE:  About to update 
NOTICE:  In exception , about to insert
NOTICE:  In exception inserted
NOTICE:  About to update 
NOTICE:  Updated

Table definitions:

test=# \d+ a
                           Table "w2gi.a"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer | not null  | plain   |              | 
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "b" CONSTRAINT "b_id2_fkey" FOREIGN KEY (id2) REFERENCES a(id)
Has OIDs: no
test=# \d+ b
                           Table "w2gi.b"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id1    | integer |           | plain   |              | 
 id2    | integer |           | plain   |              | 
Foreign-key constraints:
    "b_id2_fkey" FOREIGN KEY (id2) REFERENCES a(id)
Has OIDs: no

#2

In your loop you have

在你的循环中你有

IF FOUND THEN
  RETURN;
END IF;

This ends the function before the loop goes into the next iteration after an INSERT because FOUND is set by that command too.

这在INSERT之后循环进入下一次迭代之前结束该函数,因为FOUND也由该命令设置。

What you want is:

你想要的是:

LOOP
  BEGIN
    UPDATE dx SET cdesc = r.cdesc
    WHERE cicd9 = r.cicd9 AND groupid = r.groupid AND tposted = r.tposted;
    IF FOUND THEN
      RETURN;
    END IF;
  EXCEPTION 
    WHEN others THEN
      INSERT INTO icd9(cicd9, cdesc) VALUES (r.cicd9, r.cdesc);
  END;
END LOOP;

#1